Oracle数组、Record、Cursor

oakvevaalk

create or replace procedure pro_completedata
is
--需要补全数据的表
type tablename_array is varray(16) of varchar2(32);
tableNames tablename_array := tablename_array('T_HTLQUR_HAOGANG', 'T_HTLQUR_HBQ', 'T_HTLQUR_HDQ', 'T_HTLQUR_HGH',
'T_HTLQUR_HKM', 'T_HTLQUR_LEIMAN', 'T_HTLQUR_LIEYANG', 'T_HTLQUR_NKG', 'T_HTLQUR_OTHER', 'T_HTLQUR_PEK', 'T_HTLQUR_SHA'
, 'T_HTLQUR_SZX', 'T_HTLQUR_YUEXIANG', 'T_HTLQUR_AIYOU', 'T_HTLQUR_CAN', 'T_HTLQUR_CTU');
--tableNames tablename_array := tablename_array('T_HTLQUR_HKM');
tableName varchar2(32);
cursorSQL varchar2(2000);
type PPIDCURSOR IS REF CURSOR;
ppid_cursor PPIDCURSOR;
type PPIDRECORD IS RECORD(
ppid number
);
ppid_record PPIDRECORD;
begin
FOR i in 1 .. tableNames.count loop
tableName := tableNames(i);
cursorSQL := 'select DISTINCT t.priceplanid from '||tableName||'  t';
open ppid_cursor for  cursorSQL;
loop
fetch ppid_cursor into ppid_record;
exit when ppid_cursor%notfound;
--需要补全的PPID记录到数据库表中
pro_recorddatawithoutprice(ppid_record.ppid,tableName);
end loop;
end loop;
end pro_completedata;