Re: Newbie PL*SQL question

xylem wrote:
> 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?

I don't see any value in any cursors or loops. Why not:

INSERT INTO target_table
SELECT a.col1, b.col2, c.col3, d.col4, e.col5 FROM tab1 a, tab2 b, tab3 c, tab4 d, tab5e WHERE <join_condition_is_valid>;

--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)