Re: referring to a temp table in a procedure?

From: Sridhar Subramaniam <avion_at_ozemail.com.au>
Date: 1996/11/16
Message-ID: <328CDA54.47CC_at_ozemail.com.au>#1/1

L. Tseng wrote:
>
> If I declare a RECORD type and populate it, how can I loop
> thru it? For example:
>
> DECLARE
>
> TYPE EmpRecType IS RECORD
> (empno NUMBER,
> ename VARCHAR2(15),
> sal NUMBER(10,2));
>
> EmployeeRec EmpRecType;
>
> BEGIN
> SELECT empno, ename, sal INTO EmployeeRec FROM mydept;
>
> --- then DBMS_OUTPUT all the rows
> --- please fill in...
> END;
>
> Thank you very much!!!
>
> In article <32899BF5.6323_at_ozemail.com.au>,
> Sridhar Subramaniam <avion_at_ozemail.com.au> wrote:
> >L. Tseng wrote:
> >>
> >> I don't know if this is a trivial question but I am hitting
> >> the wall now. Any help will be very appreciated!!!
> >>
> >> What I want to do is
> >>
> >> In a strored procedure,
> >>
> >> create a temp table;
> >>
> >> insert some rows to the temp table;
> >>
> >> create a cursor on the temp table;
> >>
> >> Loop thru the temp table with the DBMS_OUTPUT;
> >>
> >> It seems to me DBMS_SQL is the only way to go but...
> >>
> >> Can what I want be achieved? how?
> >>
> >> Thank you very much.
> >>
> >> Leslie
> >Leslie,
> >The way you are going, dbms_sql seems to be the only way since ddls are
> >not supported in pl/sql.
> >
> >A better way is to create pl/sql table and loop thro' it. Depending on
> >the version of oracle you use, pl/sql support for table-type variables
> >differ. In ver 2.0 and 2.1 pl/sql supports one dimensional array-like
> >tables. 2.3 supports table of records and table-type attributes.
> >
> >Check your pl/sql manual for more details
> >
> >--
> >Cheers
> >
> >Sridhar Subramaniam
> >Avion Consulting Services
> >Sydney - Australia
> >Email : avion_at_ozemail.com.au
> >
> >Disclaimer : All opinions are truly and just mine.
Leslie,

Check this out :

DECLARE
    type emp_tab_type is table of emp%rowtype     index by binary integer;

    emp_tab emp_tab_type;
    i binary_integer;
begin

    for c_rec in ( select * from emp ) loop

        i := i + 1;
        emp_tab(i) := c_rec;

    end loop;
end;

Another example :
create or replace function xxx
return emp_tab_type
is

    type emp_rec_type is record( empno number,

                                 deptno number,
                                 sal number,
                                 deptname varchar2(30) );

    type emp_tab_type is table of emp_rec_type     index by binary integer;

    emp_tab emp_tab_type;
    i binary_integer;
begin

    for c_rec in ( select e.*, d.dept_name from emp e, dept d where d.deptno = e.deptno ) loop

        i := i + 1;
        emp_tab(i).empno := c_rec.empno;
        emp_tab(i).deptno := c_rec.empno;
        emp_tab(i).sal := c_rec.sal;
        emp_tab(i).dname := c_rec.dname;

    end loop;

    return(emp_tab);
end;

The above is possible only in 2.3 ver of pl/sql.

Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.

--

Received on Sat Nov 16 1996 - 00:00:00 CET