读取不同游标的Oracle泛型函数

Question

是否可以将游标数据提取到具有动态游标参数的数组或表中？

示例:我们有3个函数fct1()、fct2()、fct3()。它们都返回具有不同数据的游标(来自不同的表)，它们的大小介于20到100之间。我想用一个泛型函数调用这个3函数，填充一个VARCHAR2数组，例如打印这个数组。

我已经了解了如何将游标提取到VARRAY或VARCHAR2中，但是需要指定将哪个游标值添加到数组中。

CREATE OR REPLACE PACKAGE BODY CURSOR_EXAMPLE AS


    FUNCTION fct1 (param01 IN VARCHAR2) RETURN SYS_REFCURSOR AS
    my_cursor SYS_REFCURSOR;
    BEGIN
        OPEN my_cursor FOR 
            SELECT a, b, c FROM table1, table2, table3;
        RETURN my_cursor;
    END fct1;


    FUNCTION fct2 (param01 IN VARCHAR2) RETURN SYS_REFCURSOR AS
        my_cursor SYS_REFCURSOR;
    BEGIN
        OPEN my_cursor FOR 
            SELECT q, w, e, r, t, y FROM table4, table5;
        RETURN my_cursor;
    END fct2;
    
    
    FUNCTION fct3 (param01 IN VARCHAR2, param02 IN VARCHAR2, param03 IN VARCHAR2) RETURN SYS_REFCURSOR AS
        my_cursor SYS_REFCURSOR;
    BEGIN
        OPEN my_cursor FOR 
            SELECT x, y, z FROM table6, table7, table8, table8;
        RETURN my_cursor;
    END fct3;
    
    
    PROCEDURE generic_function (fct IN NUMBER, param01 IN VARCHAR2, param02 IN VARCHAR2, param03 IN VARCHAR2, cursor_out OUT SYS_REFCURSOR)
    AS
    BEGIN
        IF fct = 1
            cursor_out := fct1(param01);
        ELSE IF fct = 2
            cursor_out := fct2(param01);
        ELSE IF
            cursor_out := fct3(param01, param02, param03);
        END IF;
        
        LOOP
            FETCH cursor_out INTO
                -- HERE DEPENDING ON fct1, 2 and 3 add cursor_out variable into a VARRAY (extend it as needed)
                
            
            EXIT WHEN cursor_out%NOTFOUND;
            
        END LOOP;
        
        -- PRINT VARRAY
        
    END generic_function;


END CURSOR_EXAMPLE;

Answer 1

似乎您正在尝试设计一种动态方法来发现游标中包含哪些列。

有一种方法，但它涉及使用DBMS_SQL，一旦您将游标转换为DBMS_SQL游标，您就必须在处理记录时始终使用DBMS_SQL。

下面是一个测试函数的示例，它可以返回四个不同游标中的一个，而不需要静态定义类型：

CREATE OR REPLACE FUNCTION get_test_cursor (n in number default 1) return SYS_REFCURSOR
IS
  c SYS_REFCURSOR;
BEGIN
   IF n = 1
   THEN
      OPEN c FOR SELECT 'A' f1, 'B' f2 FROM DUAL;
   END IF;

   IF n = 2
   THEN
      OPEN c FOR SELECT cast(1.234 as number(6,2)) n1, cast(12 as number(8)) n2, 'C' f3 FROM DUAL;
   END IF;

   IF n = 3
   THEN
      OPEN c FOR select * from all_objects;
   END IF;
   
   if n = 4
   then
      OPEN c FOR select sysdate as current_dt from dual;
   end if;
   return c;
END;
/

现在，我们可以使用DBMS_SQL包首先使用dbms_sql.to_cursor_number将游标从REF游标转换为游标号。一旦我们这样做了，我们就可以使用DBMS_SQL API的其余部分来检查游标并处理它的数据。

declare
  cur sys_refcursor;
  c number;
  col_cnt     INTEGER;
  rec_tab     DBMS_SQL.DESC_TAB;
  --
  -- This is a crude helper procedure to display one line of "DESCRIBE" output
  --
  PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(rpad(rec.col_name, 41) || ' ' ||
    rpad(case when rec.col_null_ok then ' ' else 'NOT NULL' end, 8) || ' ' ||
    case when rec.col_type = DBMS_TYPES.TYPECODE_VARCHAR or rec.col_type = DBMS_TYPES.TYPECODE_VARCHAR2 then
      'VARCHAR2(' || rec.col_max_len || ')'
    when rec.col_type = DBMS_TYPES.TYPECODE_CHAR then
      'CHAR(' || rec.col_max_len || ')'
    when rec.col_type = DBMS_TYPES.TYPECODE_NUMBER then
      case when rec.col_precision = 0 and rec.col_scale = -127 then 'NUMBER'
      when rec.col_scale = 0 then 'NUMBER('||rec.col_precision||')'
      else 'NUMBER(' || rec.col_precision || ', ' || rec.col_scale || ')'
      end
    when rec.col_type = DBMS_TYPES.TYPECODE_DATE then
      'DATE'
    else
      'UNKNOWN'
    end);
  END;
begin
  cur := test_cursor(3);
  --
  -- Convert the REF_CUR to a cursor number
  --
  c := dbms_sql.to_cursor_number(cur);
  --
  -- Use an API call to describe the columns
  --
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  --
  -- Now loop through the columns and show them
  --
  dbms_output.put_line('Name                                      Null?    Type');
  dbms_output.put_line('----------------------------------------- -------- ----------------------------');
  for j in 1..col_cnt loop
    print_rec(rec_tab(j));
  end loop;
  --
  -- We can do other things at this point. 
  -- When done, close the cursor.
  --
  DBMS_SQL.CLOSE_CURSOR(c);
end;
/

下面是传入3的输出，它查询ALL_OBJECTS视图：

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(128)
OBJECT_NAME                               NOT NULL VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(128)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(23)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NAMESPACE                                 NOT NULL NUMBER
EDITION_NAME                                       VARCHAR2(128)
SHARING                                            VARCHAR2(13)
EDITIONABLE                                        VARCHAR2(1)
ORACLE_MAINTAINED                                  VARCHAR2(1)

一旦您能够看到游标的列，就可以解决创建一个使用随机游标和填充数组的动态过程的问题。