ORACLE_TYPE_RECORD_OBJECT使用说明

record 与 object 区别

–记录
–Cannot be stored in the database.（无法存储在数据库中。）
–Cannot be recursively referenced.（不能递归引用。）
–Cannot have logic defined as part of their definition.（不能将逻辑定义为其定义的一部分。）

–对象
–Can be stored as a database table column or as an entire row.（可以存储为数据库表列或整行。）
–Can be recursively referenced using the SELF parameter.（可以使用self参数递归引用。）
–Can have logic defined as part of their definition using member methods.（可以使用成员方法将逻辑定义为其定义的一部分。）

declare
  TYPE CJJ_USER IS RECORD(
       AAC002 ZF03.AAC002%TYPE,
       AAC003 ZF03.AAC003%TYPE
  );
  TYPE CJJ_USER_TAB IS TABLE OF CJJ_USER;
  TYPE sy_cur IS REF CURSOR;
  v_user CJJ_USER;
  v_arr CJJ_USER_TAB;
  TmpCur sy_cur;
begin
  open TmpCur for
  SELECT AAC002,AAC003 FROM ZF03 WHERE AAC003 LIKE '陈%' and rownum<10;
  loop
    fetch TmpCur into v_user;
    dbms_output.put_line(v_user.AAC002);
    exit when TmpCur%notfound;
  end loop;
  close TmpCur;
end ;

DECLARE
  TYPE DXS_RD IS RECORD(
       NAME DZSDB.AAC003%TYPE,
       SFZ DZSDB.AAC002%TYPE
  );
  TYPE DXS_RD_ARR IS TABLE OF DXS_RD;
  DXS_TAB DXS_RD_ARR;
BEGIN
  DELETE FROM DZSDB WHERE AAC002 IN('330522199507010228','411423199604185021')
  RETURNING AAC003,AAC002    -->使用returning 返回这几个列   
  BULK  COLLECT INTO DXS_TAB;-->将前面返回的列的数据批量插入到集合变量
  --SELECT * FROM TABLE(DXS_TAB);  
  FOR I IN DXS_TAB.FIRST..DXS_TAB.LAST
  LOOP
    DBMS_OUTPUT.put_line(I);
    DBMS_OUTPUT.PUT_LINE(DXS_TAB(I).NAME);
  END LOOP;   
END ;

DECLARE
  TYPE DXS_OJ_ARR IS TABLE OF DXS_OJ;
  DXS_TAB DXS_OJ_ARR;
BEGIN
  DELETE FROM DZSDB WHERE AAC002 IN('330522199507010228','411423199604185021')
  RETURNING DXS_OJ(AAC003,AAC002)    -->使用returning 返回这几个列   
  BULK  COLLECT INTO DXS_TAB;-->将前面返回的列的数据批量插入到集合变量
  FOR I IN DXS_TAB.FIRST..DXS_TAB.LAST
  LOOP
    DBMS_OUTPUT.put_line(I);
    DBMS_OUTPUT.PUT_LINE(DXS_TAB(I).NAME);
  END LOOP;
END ;