Can a nested select be passed as a parameter? [message #448338] |
Mon, 22 March 2010 17:02 |
jagilbert
Messages: 12 Registered: March 2010 Location: Atlanta
|
Junior Member |
|
|
Hello,
Is it possible to pass a nested select statement as a parameter to a procedure? In other words, I am currently successfully doing this:
SELECT BASE_PAY INTO N FROM EMPLOYEES WHERE ID=p_ID;
SetPackageValueProc(PkgID,'YearlyBasePay',N);
And I'd like to do something along the lines of this:
SetPackageValueProc(PkgID,'YearlyBasePay',(SELECT BASE_PAY FROM EMPLOYEES WHERE ID=p_ID));
Tia,
Cheers
|
|
|
|
Re: Can a nested select be passed as a parameter? [message #448340 is a reply to message #448339] |
Mon, 22 March 2010 17:32 |
jagilbert
Messages: 12 Registered: March 2010 Location: Atlanta
|
Junior Member |
|
|
re: Duck
so sorry, I was assuming intelligence on the part of the reader.
re: use guidelines: 1. Be polite: Doctor heal thyself. What part of the guidelines are you suggesting that I have not followed?
The first code works. Therefore N is compatible. You don't need my tables and data to figure that one out, and I'm not asking you to improve my code.
The second code doesn't work. Of course I tried it. Hence the statement: I would like to do something along the lines of...
If you don't understand the question, there are other possibilities than the OP is a lazy idiot.
This was my first and probably last post here I guess.
Sheesh.
|
|
|
|
|
|
|
|
|
Re: Can a nested select be passed as a parameter? [message #448425 is a reply to message #448423] |
Tue, 23 March 2010 05:50 |
cookiemonster
Messages: 13960 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sen.suvro wrote on Tue, 23 March 2010 05:38if it is a scalar variable then "Yes" it can be done, But you have to consider the datatypes one from the Select statement and one that is accepting the value into the procedure.
What can be done exactly?
|
|
|
|
|
|
|
|
Re: Can a nested select be passed as a parameter? [message #448467 is a reply to message #448464] |
Tue, 23 March 2010 10:53 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If the set of results is relatively small (a few thousand rows) then you could execute the original query, store the results in a collection, and pass the collection around.
As long as the collection is defined in SQL, you can then use the collection as a table in other queries:
Script:create or replace type ty_154_obj as object(col_1 number);
/
create or replace type ty_154 as table of ty_154_obj;
/
create table t_154 (col_1 number);
create table t_155 (col_1 number, col_2 varchar2(10));
insert into t_154 (select level from dual connect by level <=20);
insert into t_155 (select level,'Row '||level from dual connect by level <=20);
declare
t_numbers ty_154 := ty_154();
begin
select ty_154_obj(col_1)
bulk collect into t_numbers
from t_154
where mod(col_1,2)=0;
for rec in (select t.col_2
from t_155 t
,table(t_numbers) n
where t.col_1 = n.col_1) loop
dbms_output.put_line(rec.col_2);
end loop;
end;
/
Output:SQL> declare
2 t_numbers ty_154 := ty_154();
3 begin
4
5 select ty_154_obj(col_1)
6 bulk collect into t_numbers
7 from t_154
8 where mod(col_1,2)=0;
9
10 for rec in (select t.col_2
11 from t_155 t
12 ,table(t_numbers) n
13 where t.col_1 = n.col_1) loop
14 dbms_output.put_line(rec.col_2);
15 end loop;
16 end;
17 /
Row 2
Row 4
Row 6
Row 8
Row 10
Row 12
Row 14
Row 16
Row 18
Row 20
PL/SQL procedure successfully completed.
|
|
|
Re: Can a nested select be passed as a parameter? [message #448470 is a reply to message #448464] |
Tue, 23 March 2010 10:57 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
May be I misunderstand Your question, but You can pass NT from/to procedures;
CREATE OR REPLACE TYPE
numlist IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE listman (liste IN numlist)
AS
BEGIN
FOR i IN liste.first .. liste.last LOOP
dbms_output.put_line('i='||liste(i));
END LOOP;
END;
/
SHOW ERRORS
SET SERVEROUTPUT ON SIZE 1000000;
EXEC listman(numlist(1,2,3,4));
/
i=1
i=2
i=3
i=4
DECLARE
l1 numlist;
BEGIN
SELECT LEVEL lv BULK COLLECT INTO l1 FROM dual CONNECT BY LEVEL<=4;
listman(l1);
END;
i=1
i=2
i=3
i=4
|
|
|
|
Re: Can a nested select be passed as a parameter? [message #448474 is a reply to message #448464] |
Tue, 23 March 2010 11:30 |
cookiemonster
Messages: 13960 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
jagilbert wrote on Tue, 23 March 2010 10:30
If I understand Frank's suggestion, I could also: Perform the first query and put the result in the refcursor, then refer to that refcursor in the proc.
I think you misunderstand ref cursors. They're not result sets, they're cursors (well pointers to cursors). When you open a ref cursor, oracle doesn't query anything, it only does that when you fetch the results. What you pass around is a pointer to the query.
|
|
|
|
Re: Can a nested select be passed as a parameter? [message #448482 is a reply to message #448476] |
Tue, 23 March 2010 12:04 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
This is correct (if we except the parsing phase).
For instance:
SQL> var c refcursor
SQL> set timing on
SQL> begin open :c for select count(*) from dba_objects, dba_objects; end;
2 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
SQL> print c
COUNT(*)
----------
2367017104
1 row selected.
Elapsed: 00:02:54.81
As you can see opening is immediate when fetching the count takes time.
Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
[Updated on: Tue, 23 March 2010 12:05] Report message to a moderator
|
|
|
Re: Can a nested select be passed as a parameter? [message #448485 is a reply to message #448338] |
Tue, 23 March 2010 12:09 |
cookiemonster
Messages: 13960 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That said ref cursors are primarily used for passing complicated queries back to client programs - web front ends for example.
You don't normally use them to pass stuff between procedures - you can do of course but usually there are better ways, depends what you're trying to do.
|
|
|