Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Tables
PL/SQL Tables [message #257253] Tue, 07 August 2007 21:47 Go to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member
Hi Team,

I want to get into a PL/SQL table the deptno and
the salary total of ech dept.

I have created the following PL/SQL code for the same
which will only disply the salary toltal or either the depno.

is it possible to populate both the vcalues into a
PL/SQL Table


DECLARE


/*
Creating a PL/SQL table which returns the saltotal of dept
to aPL/SQL table declaring a cursor
with the FOR stmt.

*/

TYPE emp_tab is table of number
INDEX BY BINARY_INTEGER;

emp_tabnam emp_tab;
CURSOR emp_cur is
select deptno, sum(sal)saltot FROM emp group by deptno;

i BINARY_INTEGER:=0;

BEGIN

FOR emp_rec in emp_cur

LOOP
i:=i+1;
dbms_output.put_line('Value of i '||i);

emp_tabnam(i):=emp_rec.deptno;
dbms_output.put_line('Elements are '||emp_tabnam(i));
END LOOP;
END;


Thanks In Advance
Binuja
Re: PL/SQL Tables [message #257256 is a reply to message #257253] Tue, 07 August 2007 21:56 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>I want to get into a PL/SQL table the deptno and the salary total of each dept.
Why?
Re: PL/SQL Tables [message #257258 is a reply to message #257256] Tue, 07 August 2007 22:05 Go to previous messageGo to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member
Just want to display in a PL/SQL table
Re: PL/SQL Tables [message #257260 is a reply to message #257253] Tue, 07 August 2007 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Just want to display in a PL/SQL table
So you want to extract values out of a real table, place the extracted value into a PL/SQL table, so you can the take the value out of the PL/SQL table & present it to the client via DBMS_OUTPUT.

What benefit does the PL/SQL table provide?
Is the use of a PL/SQL table part of your homework assigment?
Re: PL/SQL Tables [message #261248 is a reply to message #257260] Wed, 22 August 2007 03:51 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi You can achive that by using table of record.

Here is the modified example:

DECLARE


/*
Creating a PL/SQL table which returns the saltotal of dept
to aPL/SQL table declaring a cursor
with the FOR stmt.

*/

Type dep_rec is record of(
dno emp.deptno%type,sum_sal emp.sal%type)
TYPE emp_tab is table of dep_rec
INDEX BY BINARY_INTEGER;

emp_tabnam emp_tab;
CURSOR emp_cur is
select deptno, sum(sal)saltot FROM emp group by deptno;

i BINARY_INTEGER:=0;

BEGIN

FOR emp_rec in emp_cur

LOOP
i:=i+1;
dbms_output.put_line('Value of i '||i);

emp_tabnam(i).dno:=emp_rec.deptno;

emp_tabnam(i).sum_sal:=emp_rec.saltot;

dbms_output.put_line('Elements are '||emp_tabnam(i));
END LOOP;
END;


To make it fatser yyou could use forall and bulk collect statement rather than for loop.Hope it will clear things for you.
Re: PL/SQL Tables [message #261278 is a reply to message #261248] Wed, 22 August 2007 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read and follow How to format your posts Make sure that lines of code do not exceed 80 or 100 characters when you format.

Regards
Michel
Re: PL/SQL Tables [message #261282 is a reply to message #261278] Wed, 22 August 2007 04:52 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Michel

I copied the code from the post as it is and added my comment thats it.

I am sorry if i am offending you at all.
Re: PL/SQL Tables [message #261290 is a reply to message #261282] Wed, 22 August 2007 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I copied the code from the post as it

Many of us did this and we still format the code we posted even if the original post was not formated.
Read the guidelines.

Regards
Michel
Re: PL/SQL Tables [message #261294 is a reply to message #257253] Wed, 22 August 2007 05:02 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Michel

Better i shuld not reply at all.
Re: PL/SQL Tables [message #261307 is a reply to message #261294] Wed, 22 August 2007 05:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
darshanmeel wrote on Wed, 22 August 2007 12:02
Michel

Better i shuld not reply at all.

Nah, you can reply but when you post code put your code in these tags:
[code] and [/code]. It preserves formatting. A simple look at the links Michel posted would have revealed that.

MHE
Re: PL/SQL Tables [message #262564 is a reply to message #257253] Mon, 27 August 2007 09:54 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
btw the term "PL/SQL Table" is obsolete.

Also INDEX BY PLS_INTEGER is not only shorter than INDEX BY BINARY_INTEGER, but also shows you've been paying attention Wink
Previous Topic: PLS-00123 Error
Next Topic: Sql qyery
Goto Forum:
  


Current Time: Mon Dec 05 06:58:25 CST 2016

Total time taken to generate the page: 0.09465 seconds