Oracle PL/SQL-对本地定义表的列名进行迭代

如果您使用在SQL作用域中创建的对象,而不是在PL/SQL作用域中生成的记录,那么您可以使用 ANYDATA 找出属性及其值。

例如,如果您的类型是:

CREATE TYPE book is OBJECT(
  title           varchar(50),
  author          varchar(50),
  subject         varchar(100),
  book_id         number,
  first_published DATE
);

CREATE TYPE table_of_books IS TABLE OF book;

然后,您可以创建包:

CREATE PACKAGE reflection IS
  TYPE type_info IS RECORD(
    prec        PLS_INTEGER,
    scale       PLS_INTEGER,
    len         PLS_INTEGER,
    csid        PLS_INTEGER,
    csfrm       PLS_INTEGER,
    schema_name VARCHAR2(30),
    type_name   VARCHAR2(30),
    version     VARCHAR2(100),
    count       PLS_INTEGER
  );

  TYPE attr_info IS RECORD(
    prec           PLS_INTEGER,
    scale          PLS_INTEGER,
    len            PLS_INTEGER,
    csid           PLS_INTEGER,
    csfrm          PLS_INTEGER,
    attr_elt_type  ANYTYPE,
    aname          VARCHAR2(30)
  );

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER;

  FUNCTION get_attr_name_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2;

  FUNCTION get_attr_value_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2;
END;
/

对于包装体:

CREATE PACKAGE BODY reflection IS
  DEBUG BOOLEAN := FALSE;

  FUNCTION get_type(
    p_anydata IN ANYDATA
  ) RETURN ANYTYPE
  IS
    v_typeid    PLS_INTEGER;
    v_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
  BEGIN
    v_typeid := p_anydata.GetType( typ => v_anytype );
    RETURN v_anytype;
  END;

  FUNCTION get_info(
    p_anytype IN ANYTYPE
  ) RETURN type_info
  IS
    v_typeid    PLS_INTEGER;
    v_type_info REFLECTION.TYPE_INFO;
  BEGIN
    v_typeid := p_anytype.GetInfo (
      v_type_info.prec, 
      v_type_info.scale,
      v_type_info.len, 
      v_type_info.csid,
      v_type_info.csfrm,
      v_type_info.schema_name, 
      v_type_info.type_name, 
      v_type_info.version,
      v_type_info.count
    );

    IF v_typeid <> DBMS_TYPES.TYPECODE_OBJECT THEN
      RAISE_APPLICATION_ERROR( -20000, 'Not an object.' );
    END IF;

    RETURN v_type_info;
  END;

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER
  IS
  BEGIN
    RETURN Get_Info( Get_Type( p_anydata ) ).COUNT;
  END;
  
  FUNCTION get_attr_name_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2
  IS
    v_anydata     ANYDATA := p_anydata;
    v_anytype     ANYTYPE;
    v_type_info   REFLECTION.TYPE_INFO;
    v_output      VARCHAR2(4000);
    v_attr_typeid PLS_INTEGER;
    v_attr_info   REFLECTION.ATTR_INFO;
  BEGIN
    v_anytype := Get_Type( v_anydata );
    v_type_info := Get_Info( v_anytype );
    
    IF p_index < 1 OR p_index > v_type_info.COUNT THEN
      RETURN NULL;
    END IF;
    
    v_anydata.PIECEWISE;
    v_attr_typeid := v_anytype.getAttrElemInfo(
      pos            => p_index,
      prec           => v_attr_info.prec,
      scale          => v_attr_info.scale,
      len            => v_attr_info.len,
      csid           => v_attr_info.csid,
      csfrm          => v_attr_info.csfrm,
      attr_elt_type  => v_attr_info.attr_elt_type,
      aname          => v_attr_info.aname
    );
    RETURN v_attr_info.aname;
  END;
        
  FUNCTION get_attr_value_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2
  IS
    v_anydata   ANYDATA := p_anydata;
    v_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
    v_output    VARCHAR2(4000);
  BEGIN
    v_anytype := Get_Type( v_anydata );
    v_type_info := Get_Info( v_anytype );
    
    IF p_index < 1 OR p_index > v_type_info.COUNT THEN
      RETURN NULL;
    END IF;
    
    v_anydata.PIECEWISE;
    
    FOR i IN 1 .. p_index LOOP
      DECLARE
        v_attr_typeid PLS_INTEGER;
        v_attr_info   REFLECTION.ATTR_INFO;
        v_result_code PLS_INTEGER;
      BEGIN
        v_attr_typeid := v_anytype.getAttrElemInfo(
          pos            => i,
          prec           => v_attr_info.prec,
          scale          => v_attr_info.scale,
          len            => v_attr_info.len,
          csid           => v_attr_info.csid,
          csfrm          => v_attr_info.csfrm,
          attr_elt_type  => v_attr_info.attr_elt_type,
          aname          => v_attr_info.aname
        );

        IF DEBUG THEN
          DBMS_OUTPUT.PUT_LINE(
            'Attribute ' || i || ': '
            || v_attr_info.aname
            || ' (type ' || v_attr_typeid || ')'
          );
        END IF;

        CASE v_attr_typeid
        WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
          DECLARE
            v_value NUMBER;
          BEGIN
            v_result_code := v_anydata.GetNumber( v_value );
            IF i = p_index THEN
              RETURN TO_CHAR( v_value );
            END IF;
          END;
         WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
          DECLARE
            v_value VARCHAR2(4000);
          BEGIN
            v_result_code := v_anydata.GetVarchar2( v_value );
            IF i = p_index THEN
              RETURN v_value;
            END IF;
          END;
         WHEN DBMS_TYPES.TYPECODE_DATE THEN
          DECLARE
            v_value DATE;
          BEGIN
            v_result_code := v_anydata.GetDate( v_value );
            IF i = p_index THEN
              RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
            END IF;
          END;
        ELSE
          NULL;
        END CASE;
      END;
    END LOOP;
    RETURN NULL;
  END;
END;
/

那么,获取值的代码可以是:

DECLARE
   list_of_books table_of_books;
   idx           PLS_INTEGER := 1;
   p_anydata     ANYDATA;
   p_attr_name   VARCHAR2(30);
   p_attr_value  VARCHAR2(4000);
BEGIN
  dbms_output.enable;
  list_of_books := table_of_books(
    book(
      'First book',
      'Me',
      'Simple Ones',
      94321,
      DATE '1970-01-01'
    ),
    book(
      'Second book',
      'You',
      'Intermediate Ones',
      55555,
      DATE '2020-01-01'
    ),
    book(
      'Third book',
      NULL,
      'Advanced Ones',
      77777,
      DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND
    )
  );
  
  FOR book_no IN 1 .. list_of_books.COUNT LOOP
    p_anydata := ANYDATA.ConvertObject( list_of_books(book_no) );
    DBMS_OUTPUT.PUT_LINE( 'Book ' || book_no || ':' );
    FOR attr_no IN 1 .. REFLECTION.get_size( p_anydata ) LOOP
      p_attr_name  := REFLECTION.get_attr_name_at( p_anydata, attr_no );
      p_attr_value := REFLECTION.get_attr_value_at( p_anydata, attr_no );
      DBMS_OUTPUT.PUT_LINE( '  ' || p_attr_name || ': ' || p_attr_value );
    END LOOP;
  END LOOP;
END;
/

哪些输出:

Book 1:
  TITLE: First book
  AUTHOR: Me
  SUBJECT: Simple Ones
  BOOK_ID: 94321
  FIRST_PUBLISHED: 1970-01-01 00:00:00
Book 2:
  TITLE: Second book
  AUTHOR: You
  SUBJECT: Intermediate Ones
  BOOK_ID: 55555
  FIRST_PUBLISHED: 2020-01-01 00:00:00
Book 3:
  TITLE: Third book
  AUTHOR: 
  SUBJECT: Advanced Ones
  BOOK_ID: 77777
  FIRST_PUBLISHED: 2099-12-31 23:59:59

db<>小提琴 here

简短的回答:你不能。在PL/SQL中没有这样的迭代记录属性的工具。

答案很长:你可以使用 table 运算符使用SQL读取数组。所以你可以 unpivot 结构,将列名作为行返回。

为此,您需要定义记录和数组:

• 在包装规范中
• 作为SQL对象类型

坚持使用PL/SQL类型会得到如下结果:

create or replace package pkg as 

   type books is record
        (title   varchar(50)  := 'First Book'
        ,author  varchar(50)  := 'Me'
        ,subject varchar(100) := 'Simple ones'
        ,book_id number       := 94321
        );      
   type table_of_books is 
      table of books
      index by pls_integer;

end;
/

declare
   
   list_of_books pkg.table_of_books;
   cursor c is
     select * from table ( list_of_books )
     unpivot (
       val for c in ( 
         title, author, subject
       )
     );
begin
  list_of_books := pkg.table_of_books (
    1 => pkg.books ( 
      'First book', 'me', 'this', 100
    )
  );

  for current_field in c loop 
    dbms_output.put_line(current_field.c); 
  end loop;
end;
/

TITLE
AUTHOR
SUBJECT

一些注意事项:

• 您需要知道要成为行的属性的名称 unpivot 条款
• 致 unpivot 列,它们需要具有相同的数据类型,因此您需要一个子查询 to_char 一切

如果你使用的是19c,你也可以考虑 converting the objects to JSON

在PL/SQL作用域中声明的类型的属性不会作为元数据存储在数据字典中。

一种可能的解决方案是通过以下方式获取列名 取消发送查询 与 TABLE 操作员。SQL解析器必须知道该集合,即至少在包中声明。在这种情况下,不需要集合中的数据,只需用空集合打开光标:

create or replace package libapi as 
    type book is record (
        title varchar(50),
        author varchar(50),
        subject varchar(100),
        book_id number);      
    type books is table of book index by pls_integer;
end;
/
declare
    lib libapi.books;
    nc number; 
    cols number;
    ds dbms_sql.desc_tab;
    rc sys_refcursor;
begin
    open rc for select * from table (lib);
    nc := dbms_sql.to_cursor_number (rc);
    dbms_sql.describe_columns (nc, cols, ds);
    dbms_sql.close_cursor (nc);
    for i in 1..cols loop dbms_output.put_line (ds(i).col_name);
    end loop;
end;
/

结果:

TITLE
AUTHOR
SUBJECT
BOOK_ID

使用JSON_OBJECT_T的解决方案:

DECLARE
  books CONSTANT JSON_OBJECT_T := JSON_OBJECT_T.parse(
  '{
    "title":   "First Book", 
    "author":  "Me",
    "subject": "Simple ones",
    "book_id": 94321
  }');

  book_keys JSON_KEY_LIST := books.get_keys;

BEGIN
  FOR i IN 1..book_keys.COUNT LOOP
     DBMS_OUTPUT.put_line( book_keys(i) || ': ' ||  books.get_string(book_keys(i)) );
  END LOOP;
END;
/