创建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 a