Oracle 数据库类型与PL/SQL类型的批量收集

            CREATE OR REPLACE TYPE typ_rowid IS record (
            TABLE_NAME VARCHAR2(100),
            COL_VALUE NUMBER(38),
            ROW_ID VARCHAR2(100)
            );

           

            CREATE OR REPLACE TYPE t_typ_rowid IS TABLE OF TYP_rowid;

 

Can you please help to understand this?

How to used Oracle types in below case instead of PL/SQL type?

           

create table tb_load_stats
(
TABLE_NAME    VARCHAR2(30 CHAR),
COL_VALUE     NUMBER(38),
ROW_ID        VARCHAR2(30 CHAR)
)
/

 

DECLARE
            TYPE typ_rowid IS record (
            TABLE_NAME VARCHAR2(100),
            COL_VALUE NUMBER(38),
            ROW_ID VARCHAR2(100)
            );
          

            TYPE t_typ_rowid IS TABLE OF TYP_rowid;
         
            tb_col_rowid           t_typ_rowid := t_typ_rowid();
 
            TYPE r_cursor IS REF CURSOR;
            c_cur_var r_cursor;
BEGIN
     OPEN c_cur_var FOR SELECT /*+ PARALLEL(ORDER_HISTORY, 2) */ 'ORDER_HISTORY', ORDER_ID, ROWID FROM ORDER_HISTORY WHERE posting_dt <= TO_DATE('01/01/2008', 'MM/DD/YYYY');

     LOOP

         FETCH c_cur_var BULK COLLECT INTO tb_col_rowid LIMIT 5000;
                       EXIT WHEN tb_col_rowid.COUNT = 0;
                        FORALL i IN tb_col_rowid.FIRST .. tb_col_rowid.LAST
             INSERT INTO tb_load_stats VALUES (tb_col_rowid(i).table_name, tb_col_rowid(i).col_value, tb_col_rowid(i).row_id);
    
                         tb_col_rowid.DELETE;
     END LOOP;
     CLOSE c_cur_var;
END;

SQL>  CREATE OR REPLACE TYPE typ_rowid IS record (
  2              TABLE_NAME VARCHAR2(100),
  3              COL_VALUE NUMBER(38),
  4              ROW_ID VARCHAR2(100)
  5              );
  6  /

Warning: Type created with compilation errors.

SQL>  CREATE OR REPLACE TYPE typ_rowid IS object (
  2              TABLE_NAME VARCHAR2(100),
  3              COL_VALUE NUMBER(38),
  4              ROW_ID VARCHAR2(100)
  5              );
  6  /

Type created.

SQL>
SQL>
SQL>             CREATE OR REPLACE TYPE t_typ_rowid IS TABLE OF TYP_rowid;
  2  /

Type created.

SQL>
SQL> create table tb_load_stats
  2  (
  3  TABLE_NAME    VARCHAR2(30 CHAR),
  4  COL_VALUE     NUMBER(38),
  5  ROW_ID        VARCHAR2(30 CHAR)
  6  )
  7  /

Table created.

SQL> DECLARE
  2              tb_col_rowid           t_typ_rowid := t_typ_rowid();
  3
  4              TYPE r_cursor IS REF CURSOR;
  5              c_cur_var r_cursor;
  6  BEGIN
  7       OPEN c_cur_var FOR SELECT typ_rowid('ORDER_HISTORY', object_id, ROWID) FROM t WHERE rownum <= 100;
  8
  9       LOOP
 10
 11           FETCH c_cur_var BULK COLLECT INTO tb_col_rowid LIMIT 5000;
 12                         EXIT WHEN tb_col_rowid.COUNT = 0;
 13                          FORALL i IN tb_col_rowid.FIRST .. tb_col_rowid.LAST
 14               INSERT INTO tb_load_stats VALUES (tb_col_rowid(i).table_name, tb_col_rowid(i).col_value, tb_col_rowid(i).row_id);
 15
 16                           tb_col_rowid.DELETE;
 17       END LOOP;
 18       CLOSE c_cur_var;
 19  END;
 20  /

PL/SQL procedure successfully completed.