plsql定义 临时 table的方法

1.定义

TYPE T_VAL IS RECORD(
    sRIC        instrument.ric%TYPE,
    sExpireDate VARCHAR2(200),
    sErrorMsg   VARCHAR2(4000));
  TYPE T_VAL_TAB IS TABLE OF T_VAL;

v_tRics          dbms_sql.Varchar2_Table;

2.声明

 v_sExpiredRics T_VAL_TAB

3.赋值

SELECT ii.ric,
                 ii.expir_date,
                 '' BULK COLLECT
            INTO v_sExpiredRics
            FROM exchange_list el,
                 mod_group     mg,
                 instrument    ii
           WHERE el.exl_name = v_sExlName
             AND mg.exl_id = el.exl_id
             AND mg.mod_group_id = ii.mod_group_id
                -- ignore specific date
             AND ii.expir_date !=
                 trunc(to_date('01/01/1901', 'DD/MM/YYYY'))
             AND (trunc(ii.expir_date) <= trunc(v_dWorkDay));
     

4.使用：

FOR i IN 1 .. v_sExpiredRics.COUNT LOOP
        v_tRics(i) := v_sExpiredRics(i).sRIC;
      END LOOP;