如何在 pl/sql 中的另一个游标的 select 语句中使用游标中的变量

Question

我想运行一个查询，获取结果，然后使用第二个语句（光标）中第一个语句的值与另一个 select 语句迭代该查询的结果。

我的数据库中有 40 个用户。 所有用户都具有相同的数据库模式结构。 我想通过 : 获取用户名，

SELECT  distinct username 
   from all_users 

然后使用用户名运行如下查询：

Select lastname, firstname, email, email2 from username.member.

我的结果集将返回多行，因此我还需要一个行类型。

我尝试了许多不同的 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;
/

以及

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;
/

这些组合的许多变体。

有人可以帮助我吗？ 谢谢

Answer 1

你需要使用动态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;
/

编辑以更正两个错误，并添加指向 OPEN-FOR 和 示例。
编辑以使内部游标查询字符串变量。

Answer 2

您当然可以使用显式游标执行类似

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.

或等效的操作。

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.

但是，如果您发现自己使用嵌套游标 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;

Answer 3

使用 alter session set current_schema =，在您的情况下作为立即执行。

有关详细信息，请参阅 Oracle 文档。

在你的情况下，这可能会归结为（未经测试）

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;
/