创建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;
/