oracle PL/SQL 中的嵌套游标

Nested Cursors in PL/SQL

提问by user3364656

I am working with Oracle PL/SQL. I′m trying to define Nested Cursors, which means that the output from the first Cursor should be the input for the second. To be more specific: the first one should store tables with a specific prefix in it. The second one should store all values from an attribute from all the tables which are in the first one.

我正在使用 Oracle PL/SQL。我正在尝试定义嵌套游标，这意味着第一个游标的输出应该是第二个游标的输入。更具体地说：第一个应该存储带有特定前缀的表。第二个应该存储来自第一个表中所有表的属性的所有值。

Here is my code snippet. I hope it makes my problem a little more clear:

这是我的代码片段。我希望它能让我的问题更清楚一点：

DECLARE
    var_table_name  VARCHAR2(30);
    var_dimension_key  VARCHAR2(30);

CURSOR cur_all_dim IS  
    SELECT 
        table_name
        FROM  dba_tables
        WHERE dba_tables.tablespace_name = 'USERS'
        AND dba_tables.owner = 'DWH_CORE'
        AND UPPER (dba_tables.table_name) LIKE ('%DIM%%')
        AND UPPER (dba_tables.table_name) NOT LIKE ('%TEMP%')
        AND UPPER (dba_tables.table_name) NOT LIKE ('%DEBUG%')
        AND UPPER (dba_tables.table_name) NOT LIKE ('%LOG%');

CURSOR cur_dimension_key IS
    SELECT dimension_key FROM var_table_name;


BEGIN
OPEN cur_all_dim;

LOOP
EXIT WHEN cur_all_dim%NOTFOUND;

    FETCH cur_all_dim INTO var_table_name;

    OPEN cur_dimensions_key;
    LOOP
    EXIT WHEN cur_dimensions_key%NOTFOUND;
    FETCH cur_dimensions_key INTO var_dimension_key;
    dbms_output.put_line (var_table_name);
    dbms_output.put_line (var_dimension_key);


    END LOOP;
    CLOSE cur_dimension_key;
END LOOP;
CLOSE cur_all_dim;
END;

回答by Vincent Malgrat

Static cursors can only access static objects. In other words, static cursors work only if all tables and columns are known at compile time.

静态游标只能访问静态对象。换句话说，只有在编译时已知所有表和列时，静态游标才起作用。

If you need to access a table whose name will only be known during execution, you'll have to use dynamic SQL. For instance, you could use a REF CURSORin your case:

如果您需要访问其名称仅在执行期间已知的表，则必须使用动态 SQL。例如，您可以REF CURSOR在您的情况下使用 a ：

DECLARE
   var_table_name    VARCHAR2(30);
   var_dimension_key VARCHAR2(30);
   cur_dimension_key SYS_REFCURSOR;
BEGIN
   FOR cur_all_dim IN (SELECT table_name
                         FROM dba_tables
                        WHERE dba_tables.tablespace_name = 'USERS'
                          AND dba_tables.owner = 'DWH_CORE'
                          AND UPPER(dba_tables.table_name) LIKE ('%DIM%%')
                          AND UPPER(dba_tables.table_name) NOT LIKE ('%TEMP%')
                          AND UPPER(dba_tables.table_name) NOT LIKE ('%DEBUG%')
                          AND UPPER(dba_tables.table_name) NOT LIKE ('%LOG%')) 
   LOOP      
      OPEN cur_dimension_key 
       FOR 'SELECT dimention_key 
              FROM ' || cur_all_dim.table_name;
      LOOP
         FETCH cur_dimensions_key INTO var_dimension_key;
         EXIT WHEN cur_dimensions_key%NOTFOUND;
         dbms_output.put_line(cur_all_dim.table_name);
         dbms_output.put_line(var_dimension_key);      
      END LOOP;
      CLOSE cur_dimension_key;
   END LOOP;
END;

回答by Raj Sharma

    create or replace PROCEDURE PROC_NESTED_CURSOR 
    AS 
    CUR1 SYS_REFCURSOR;
    CUR2 SYS_REFCURSOR;
    LV_DEPTID NUMBER;
    LV_DEPTNAME VARCHAR2(200);
    LV_EMPID NUMBER;
    LV_FSTNAME VARCHAR2(200);
    LV_SALARY NUMBER(8,2);
    LV_JOBID VARCHAR2(20);
    BEGIN
      OPEN CUR1 FOR 
        select department_id, department_name from departments;

      LOOP 
        FETCH CUR1 INTO LV_DEPTID,LV_DEPTNAME; 
        EXIT WHEN CUR1%NOTFOUND;    
        dbms_output.put_line('                                                                                                   ');
        dbms_output.put_line('DEPARTMENT ID  '||LV_DEPTID ||'                               '|| 'DEPARTMENT NAME  '||LV_DEPTNAME);
        dbms_output.put_line('---------------------------------------------------------------------------------------------------');
        dbms_output.put_line(RPAD('EMPLOYEE NUMBER',20)||RPAD('EMPLOYEE NAME',20) ||RPAD('SALARY',20)||RPAD('JOB',20) );
          OPEN CUR2 FOR SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,JOB_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = LV_DEPTID;
          LOOP 
            FETCH CUR2 INTO LV_EMPID,LV_FSTNAME,LV_SALARY,LV_JOBID;
            EXIT WHEN CUR2%NOTFOUND;
             dbms_output.put_line(RPAD( LV_EMPID,20)|| RPAD(LV_FSTNAME,20)||RPAD(LV_SALARY,20)||LV_JOBID);

            END LOOP;
       END LOOP; 
    END PROC_NESTED_CURSOR;

/**This will print like this:-
    Department Number :10               Department Name : XXXX
    ________________________________________________________
    EMPLOYEE NUMBER    EMPLOYEE NAME      SALARY         JOB

    XXXXXX                  XXXXXX                  XXXX            XXXXX
    XXXXXX                  XXXXXX                  XXXX            XXXXX
    .........

**/