oracle存储过程的一个例子

最近刚刚写了一个删除表存储过程，记录一下，方便以后参考。

create or replace procedure proc_drop_op(opid in varchar2) as
       sqlstr long;
       tablelist dbms_sql.Varchar2_Table;
       tableliststr long;
       v_count integer default 0;
       resource_out integer;
 begin
       sqlstr := 'select count(value) from tb_op_param where paramname=''RESOURCE_OUT'' and paramtype = ''OUT'' and opid = '''|| opid ||''' and rownum=1';
       dbms_output.put_line(sqlstr);
       execute immediate sqlstr INTO v_count;
       
       if v_count > 0 then
          sqlstr := 'select value from tb_op_param where paramname=''RESOURCE_OUT'' and paramtype = ''OUT'' and opid = '''|| opid ||''' and rownum=1';
          dbms_output.put_line(sqlstr);
          execute immediate sqlstr INTO resource_out;
       end if ;
         -- get all reg table-sourcename
         sqlstr := 'select sourcename from base_source where tabflag = 1 and  parentsourceid = '||resource_out;
         dbms_output.put_line(sqlstr);
         execute immediate sqlstr bulk collect into tablelist;
         
         -- join all tables to condition
         if tablelist.count < 1000 then
           for i in 1..tablelist.count loop
             
             if i=1 then
               tableliststr := 'table_name in ('''||tablelist(i)||'''';
            elsif i=tablelist.count then
               tableliststr := tableliststr||','''||tablelist(i)||''')';
             else
               tableliststr := tableliststr||','''||tablelist(i)||'''';
             end if;
           dbms_output.put_line(tablelist(i));
           end loop;
         else 
           for i in 1..tablelist.count loop
             if i = 1 then
               tableliststr := 'table_name = '''||tablelist(i)||'''';
             else
               tableliststr := tableliststr||' or ''table_name = '||tablelist(i)||'''';
             end if;
           end loop;
          end if;
         sqlstr := 'select table_name from user_tables where '||tableliststr;
         dbms_output.put_line(sqlstr);
         execute immediate sqlstr bulk collect into tablelist;
                  
         -- drop all op result table
         for i in 1..tablelist.count loop
           sqlstr := 'drop table '||tablelist(i)||' purge';
           dbms_output.put_line(sqlstr);
           execute immediate sqlstr;
         end loop;

         --del children reg source
           sqlstr := 'delete from base_source where tabflag = 1 and parentsourceid = '||resource_out ;
           dbms_output.put_line(sqlstr);
           execute immediate sqlstr;

         --del father reg source
        sqlstr := 'delete from base_source where tabflag = 1 and sourceid = '||resource_out;
        dbms_output.put_line(sqlstr);
         execute immediate sqlstr;
        
        commit;
        exception
          --record unknown Exception
          when NO_DATA_FOUND then
            dbms_output.put_line('Occur unknown Exception!');
          when others then
            begin
              dbms_output.put_line(sqlerrm);
              rollback;
              --throws Exception
              raise;
            end;
 end proc_drop_op;