如何在pl/sql中的另一个游标的select语句中使用游标中的变量 [英] How to use a variable from a cursor in the select statement of another cursor in pl/sql

问题描述

我想运行一个查询，获取结果，然后使用第二个语句(光标)中第一个语句的值，使用另一个select语句遍历该查询的结果.

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).

我的数据库中有40个用户. 所有用户都具有相同的数据库架构结构. 我想通过以下方式获取用户名:

I have 40 users in my db. All the users have the same db schema structure. I want to get the username via :

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.

我尝试了许多不同的pl/sql组合:

I have tried many different pl/sql combinations:

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

其中的许多变化.

有人可以帮我吗. 谢谢

Can someone help me. THANKS

推荐答案

您需要使用动态SQL来实现；像这样:

You need to use dynamic SQL to achieve this; something like:

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

已编辑以更正两个错误，并为

Edited to correct two errors, and to add links to 10g documentation for OPEN-FOR and an example. Edited to make the inner cursor query a string variable.

这篇关于如何在pl/sql中的另一个游标的select语句中使用游标中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！