oracle 如何在pl/sql中另一个游标的select语句中使用游标中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4072992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a variable from a cursor in the select statement of another cursor in pl/sql
提问by VT_programmerWanneBe
I want to run a query, get the results and then iterate through the results of that query with another select statement using the values of the first statement in my 2nd statement (cursor).
我想运行一个查询，获取结果，然后使用我的第二个语句（游标）中的第一个语句的值，使用另一个 select 语句迭代该查询的结果。
I have 40 users in my db. All the users have the same db schema structure. I want to get the username via :
我的数据库中有 40 个用户。所有用户都具有相同的数据库架构结构。我想通过以下方式获取用户名：
SELECT distinct username
from all_users
then use the user name to run a query like this:
然后使用用户名运行如下查询：
Select lastname, firstname, email, email2 from username.member.
My results set will return multiple rows so I need a row type as well.
我的结果集将返回多行，所以我也需要一个行类型。
I have tried many different pl/sql combinations:
我尝试了许多不同的 pl/sql 组合：
DECLARE
CURSOR client_cur IS
SELECT distinct username
from all_users
where length(username) = 3;
-- client cursor
CURSOR emails_cur (cli all_users.username%TYPE) IS
SELECT id, name
FROM cli.org;
BEGIN
FOR client IN client_cur LOOP
dbms_output.put_line('Client is '|| client.username);
FOR email_rec in client_cur(client.username) LOOP
dbms_output.put_line('Org id is ' ||email_rec.id || ' org nam ' || email_rec.name);
END LOOP;
END LOOP;
END;
/
and
和
DECLARE
CURSOR c1 IS
SELECT distinct username from all_users where length(username) = 3;
client c1%rowtype;
cursor c2 is Select id, name, allow_digest_flg from c1.username.org;
digest c2%rowtype;
-- declare record variable that represents a row fetched from the employees table
-- employee_rec c1%ROWTYPE;
BEGIN
-- open the explicit cursor and use it to fetch data into employee_rec
OPEN c1;
loop
FETCH c1 INTO client;
open c2;
loop
fetch c2 into digest;
DBMS_OUTPUT.PUT_LINE('digest is : ' || c2.id || ' and name is ' || c2.name || ' flg is ' || c2.allow_digest_flg );
end loop;
end loop;
END;
/
AND MANY VARIATIONS OF THESE.
以及这些的许多变化。
Can someone help me. THANKS
有人能帮我吗。谢谢
回答by Alex Poole
You need to use dynamic SQL to achieve this; something like:
您需要使用动态 SQL 来实现这一点；就像是：
DECLARE
TYPE cur_type IS REF CURSOR;
CURSOR client_cur IS
SELECT DISTING username
FROM all_users
WHERE length(username) = 3;
emails_cur cur_type;
l_cur_string VARCHAR2(128);
l_email_id <type>;
l_name <type>;
BEGIN
FOR client IN client_cur LOOP
dbms_output.put_line('Client is '|| client.username);
l_cur_string := 'SELECT id, name FROM '
|| client.username || '.org';
OPEN emails_cur FOR l_cur_string;
LOOP
FETCH emails_cur INTO l_email_id, l_name;
EXIT WHEN emails_cur%NOTFOUND;
dbms_output.put_line('Org id is ' || l_email_id
|| ' org name ' || l_name);
END LOOP;
CLOSE emails_cur;
END LOOP;
END;
/
Editedto correct two errors, and to add links to 10g documentation for OPEN-FORand an example.
Editedto make the inner cursor query a string variable.
回答by Justin Cave
You can certainly do something like
你当然可以做类似的事情
SQL> ed
Wrote file afiedt.buf
1 begin
2 for d in (select * from dept)
3 loop
4 for e in (select * from emp where deptno=d.deptno)
5 loop
6 dbms_output.put_line( 'Employee ' || e.ename ||
7 ' in department ' || d.dname );
8 end loop;
9 end loop;
10* end;
SQL> /
Employee CLARK in department ACCOUNTING
Employee KING in department ACCOUNTING
Employee MILLER in department ACCOUNTING
Employee smith in department RESEARCH
Employee JONES in department RESEARCH
Employee SCOTT in department RESEARCH
Employee ADAMS in department RESEARCH
Employee FORD in department RESEARCH
Employee ALLEN in department SALES
Employee WARD in department SALES
Employee MARTIN in department SALES
Employee BLAKE in department SALES
Employee TURNER in department SALES
Employee JAMES in department SALES
PL/SQL procedure successfully completed.
Or something equivalent using explicit cursors.
或者使用显式游标等效的东西。
SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor dept_cur
3 is select *
4 from dept;
5 d dept_cur%rowtype;
6 cursor emp_cur( p_deptno IN dept.deptno%type )
7 is select *
8 from emp
9 where deptno = p_deptno;
10 e emp_cur%rowtype;
11 begin
12 open dept_cur;
13 loop
14 fetch dept_cur into d;
15 exit when dept_cur%notfound;
16 open emp_cur( d.deptno );
17 loop
18 fetch emp_cur into e;
19 exit when emp_cur%notfound;
20 dbms_output.put_line( 'Employee ' || e.ename ||
21 ' in department ' || d.dname );
22 end loop;
23 close emp_cur;
24 end loop;
25 close dept_cur;
26* end;
27 /
Employee CLARK in department ACCOUNTING
Employee KING in department ACCOUNTING
Employee MILLER in department ACCOUNTING
Employee smith in department RESEARCH
Employee JONES in department RESEARCH
Employee SCOTT in department RESEARCH
Employee ADAMS in department RESEARCH
Employee FORD in department RESEARCH
Employee ALLEN in department SALES
Employee WARD in department SALES
Employee MARTIN in department SALES
Employee BLAKE in department SALES
Employee TURNER in department SALES
Employee JAMES in department SALES
PL/SQL procedure successfully completed.
However, if you find yourself using nested cursor FOR loops, it is almost always more efficient to let the database join the two results for you. After all, relational databases are really, really good at joining. I'm guessing here at what your tables look like and how they relate based on the code you posted but something along the lines of
但是，如果您发现自己使用嵌套游标 FOR 循环，则让数据库为您连接两个结果几乎总是更有效。毕竟，关系数据库真的非常擅长加入。我在这里猜测你的表格是什么样子以及它们是如何根据你发布的代码相关联的，但有些类似
FOR x IN (SELECT *
FROM all_users,
org
WHERE length(all_users.username) = 3
AND all_users.username = org.username )
LOOP
<<do something>>
END LOOP;
回答by René Nyffenegger
Use alter session set current_schema = <username>, in your case as an execute immediate.
alter session set current_schema = <username>在您的情况下使用, 作为立即执行。
See Oracle's documentationfor further information.
有关详细信息，请参阅Oracle 的文档。
In your case, that would probably boil down to (untested)
在您的情况下，这可能归结为（未经测试）
DECLARE
CURSOR client_cur IS
SELECT distinct username
from all_users
where length(username) = 3;
-- client cursor
CURSOR emails_cur IS
SELECT id, name
FROM org;
BEGIN
FOR client IN client_cur LOOP
-- ****
execute immediate
'alter session set current_schema = ' || client.username;
-- ****
FOR email_rec in client_cur LOOP
dbms_output.put_line(
'Org id is ' || email_rec.id ||
' org nam ' || email_rec.name);
END LOOP;
END LOOP;
END;
/
