Newbie PL*SQL question

From: xylem <me_at_icq_at_yahoo.co.uk>
Date: 9 Dec 2005 06:35:49 -0800
Message-ID: <1134138949.248162.50760@o13g2000cwo.googlegroups.com>

Hi all,

I am learning PL*SQL and wanted to populate a new table with values from four tables in HR database. I the process, I encountered some very interesting things. For example, the following did not work;

create or replace package body populate_new_tbl is

procedure put_values is

        cursor c1 is select e.employee_id, e.first_name, e.last_name, d.department_id,

	l.street_address, l.postal_code, l.city, c.country_name
	from employees e, departments d, locations l, countries c;
	emp_rec c1%ROWTYPE;

begin
		insert into my_employees


		(employeeid,

		 firstname,
		 lastname,
		 departno,
		 streetaddr,
		 postaladdr,
		 city,
		 country)
		 		 values
				 (emp_rec.employee_id,
				  emp_rec.first_name,
				  emp_rec.last_name,
				  emp_rec.department_id,
				  emp_rec.street_address,
				  emp_rec.postal_code,
				  emp_rec.city,
				  emp_rec.country_name);

end put_values;
end populate_new_tbl;

The following worked,

CREATE OR REPLACE package body populate_new_tbl is procedure put_values is

	cursor c1 is
	select distinct e.employee_id, e.first_name, e.last_name,

d.department_name,l.street_address, l.postal_code, l.city, c.country_name

	from employees e, departments d, locations l, countries c
	where e.department_id = d.department_id
	and d.LOCATION_ID = l.LOCATION_ID
	and l.COUNTRY_ID = c.COUNTRY_ID
	order by e.employee_id;

refEmployees c1%rowtype;
ref_employee_iid NUMBER(8);

begin

	 ref_employee_iid := 99;
	 for refEmployees in c1 loop
	 ref_employee_iid := ref_employee_iid + 1;

		insert into my_employees


		(employeeid,

		 firstname,
		 lastname,
		 departno,
		 streetaddr,
		 postaladdr,
		 city,
		 country)
		 		 values
				 (refEmployees.employee_id,
				  refEmployees.first_name,
				  refEmployees.last_name,
				  refEmployees.department_name,
				  refEmployees.street_address,
				  refEmployees.postal_code,
				  refEmployees.city,
				  refEmployees.country_name);

end loop;
end put_values;
end populate_new_tbl;

.. and so did the followin,

CREATE OR REPLACE package body populate_new_tbl is procedure put_values is

	cursor c1 is
	select distinct e.employee_id, e.first_name, e.last_name,

d.department_name,l.street_address, l.postal_code, l.city, c.country_name

	from employees e, departments d, locations l, countries c
	where e.department_id = d.department_id
	and d.LOCATION_ID = l.LOCATION_ID
	and l.COUNTRY_ID = c.COUNTRY_ID
	order by e.employee_id;

refEmployees c1%rowtype;
ref_employee_id NUMBER(8);

begin

	 ref_employee_id := 99;
	 for refEmployees in c1 loop
	 ref_employee_id := ref_employee_id + 1;

		insert into my_employees


		(employeeid,

		 firstname,
		 lastname,
		 departno,
		 streetaddr,
		 postaladdr,
		 city,
		 country)
		 		 values
				 (refEmployees.employee_id,
				  refEmployees.first_name,
				  refEmployees.last_name,
				  refEmployees.department_name,
				  refEmployees.street_address,
				  refEmployees.postal_code,
				  refEmployees.city,
				  refEmployees.country_name);

end loop;
end put_values;
end populate_new_tbl;

Note that loop above works regarless of whether I use the variable ref_employee_id in the values to ne inserted or not. Is the loop really necessary here? Received on Fri Dec 09 2005 - 08:35:49 CST