创建procedure样例

create or replace procedure procedure_procedure
AS
i_count integer;
begin
select count(*) into i_count from user_objects t where t.OBJECT_TYPE ='PROCEDURE' and t.OBJECT_NAME = upper('procedure_name');
if i_count>0 then
EXECUTE IMMEDIATE 'drop procedure procedure_name';
end if;
end procedure_procedure;
/
call procedure_procedure();
drop procedure procedure_procedure;

CREATE OR REPLACE PROCEDURE procedure_name(TOPOADDS IN T_LIST,
TOPOUPDATES IN T_LIST,
TOPODELETES IN T_LIST,
STBDELETES IN T_LIST,
STBREGIONUPDATE in T_LIST,
TOPORESULT OUT INTEGER) IS
V_TOTALCOUNT NUMBER(10);
TOPOINFO R_INFO;
STBINFO R_INFO;
V_SQL VARCHAR2(512);
V_TEMP_SQL VARCHAR2(512);
BEGIN
TOPORESULT := -1;
V_TOTALCOUNT := TOPODELETES.COUNT;
V_SQL := 'update tablename set expiretime=:1 WHERE nodename=:2 and ' ||
'expiretime=to_timestamp(''2099-01-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')';
FOR I IN 1 .. V_TOTALCOUNT LOOP
TOPOINFO := TOPODELETES(I);
EXECUTE IMMEDIATE V_SQL
USING TOPOINFO.EXPIRETIME, TOPOINFO.NODENAME;
END LOOP;

V_TEMP_SQL := 'insert into table_temp(FATHERNODENAME,EXPIRETIME)values(:1,:2)';
V_TOTALCOUNT := STBDELETES.COUNT;
FOR I IN 1 .. V_TOTALCOUNT LOOP
STBINFO := STBDELETES(I);
EXECUTE IMMEDIATE V_TEMP_SQL
USING STBINFO.NODENAME, STBINFO.EXPIRETIME;
END LOOP;

TOPORESULT := -2;
V_SQL := 'merge into tablename aa using table_temp temp on (aa.FATHERNODENAME = temp.FATHERNODENAME )' ||
' when matched then update set aa.expiretime=temp.expiretime ' ||
' where aa.expiretime = to_timestamp(''2099-01-01 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'')';

EXECUTE IMMEDIATE V_SQL;

TOPORESULT := -3;
V_TOTALCOUNT := TOPOUPDATES.COUNT;
V_SQL := 'UPDATE table_name SET fathernodename=:1,nodetype=:2,nodeif=:3,fathernodeif=:4,' ||
'bakupnodename=:5,backupfathernodename=:6,backupnodeif=:7,backupfathernodeif=:8' ||
' WHERE nodename=:9 ' ||
' and expiretime=to_timestamp(''2099-01-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')';
FOR I IN 1 .. V_TOTALCOUNT LOOP
TOPOINFO := TOPOUPDATES(I);
EXECUTE IMMEDIATE V_SQL
USING TOPOINFO.FATHERNODENAME, TOPOINFO.NODETYPE, TOPOINFO.NODEIF, TOPOINFO.FATHERNODEIF, TOPOINFO.BAKUPNODENAME, TOPOINFO.BACKUPFATHERNODENAME, TOPOINFO.BACKUPNODEIF, TOPOINFO.BACKUPFATHERNODEIF, TOPOINFO.NODENAME;
END LOOP;

TOPORESULT := -4;
V_TOTALCOUNT := TOPOADDS.COUNT;
V_SQL := 'INSERT INTO tablename(nodename,fathernodename,nodetype,nodeif,fathernodeif,' ||
'bakupnodename,backupfathernodename,backupnodeif,backupfathernodeif,effectivetime,expiretime)' ||
'VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)';
FOR I IN 1 .. V_TOTALCOUNT LOOP
TOPOINFO := TOPOADDS(I);
EXECUTE IMMEDIATE V_SQL
USING TOPOINFO.NODENAME, TOPOINFO.FATHERNODENAME, TOPOINFO.NODETYPE, TOPOINFO.NODEIF, TOPOINFO.FATHERNODEIF, TOPOINFO.BAKUPNODENAME, TOPOINFO.BACKUPFATHERNODENAME, TOPOINFO.BACKUPNODEIF, TOPOINFO.BACKUPFATHERNODEIF, TOPOINFO.EFFECTIVETIME, TOPOINFO.EXPIRETIME;
END LOOP;

TOPORESULT := -5;
V_TOTALCOUNT := STBREGIONUPDATE.COUNT;
V_SQL := 'update tablename t set t.regionname = :1 where t.fathernodename = :2 and expiretime=to_timestamp(''2099-01-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')';
FOR I IN 1 .. V_TOTALCOUNT LOOP
TOPOINFO := STBREGIONUPDATE(I);
EXECUTE IMMEDIATE V_SQL
USING TOPOINFO.REGIONNAME, TOPOINFO.NODENAME;
END LOOP;
COMMIT;
TOPORESULT := 0;
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
CONST.LOG_ERROR_TO_TABLE(SQLCODE, SQLERRM, V_SQL);--调用包const中的procedure
TOPORESULT := -6;
END;
END procedure_name;
/