Oracle的存储过程。

       近日，因为工作需要，时间投入到写存储过程的中。下面贴上存储过程。进行分析，以便自己记忆，和朋友分享存储过程的写法。

 

       曾几何时，认为存储过程很难，现在看来也并非如此。

 

      此存储过程乃是我们用到递归删除。

 

 

--声明一个带参数的存储过程，参数都是varchar型，如果带有输出参数的话，参数后面跟的类型必须有个out 例： num out integer;

create or replace procedure recursion_delete(v_tablename varchar2,v_fieldvalue varchar2) is
--kobe.lee 编辑于2009-09-17
--递归删除

 

--声明存储过程中用到的参数

--v_tablename varchar2(100);  -- 表名
--v_fieldvalue varchar2(100);  --字段值
v_fieldvalue2 varchar2(100);
v_count integer;    --总数
v_count2 integer;
v_reltable varchar2(100);  --关系表
v_relfield varchar2(100);  --关系表字段
v_relfieldvalue varchar2(100);   --关系表字段值
v_sql_str varchar2(100);    --动态sql
v_sql_str2 varchar2(100);
v_sql_str3 varchar2(100);
v_primarykey_field varchar2(100);   --主键字段

 

--声明游标
cursor mycur is select str.reltable,str.relfield from systablerel str where str.maintable=v_tablename and str.associatedelete=1;

 

--此游标为动态游标 必须声明为引用游标
type cur_type is ref cursor;   
mycur1 cur_type;

begin
      --取出来所有和此表有关系的表名字。并统计数量，如果大于0 进行循环。
      select count(*) into v_count from systablerel str where str.maintable=v_tablename and str.associatedelete=1;  
      --如果大于0证明还有引用，对引用进行循环
      if (v_count>0) then

     --打开游标
      open mycur;

     --进入循环
      Loop

            --从游标中取出参数进行赋值
            fetch mycur into v_reltable,v_relfield;
            if mycur%notfound then exit;  --如果不存在  退出
            elsif mycur%found then    --如果存在继续执行
          --先通过v_reltable查字段
          select distinct str2.mainfield into v_primarykey_field from systablerel str2 where str2.maintable=v_reltable;         
              --动态sql（动态拼接sql的方法）
              v_sql_str := 'select '||v_primarykey_field||' from '||v_reltable||' where '||v_relfield||'='''||v_fieldvalue||'''';
              --先查询是否有数据，如果有数据进入循环
              v_sql_str2 := 'select count(*) from '||v_reltable||' where '||v_relfield||'='''||v_fieldvalue||'''';

              --执行动态sql，把执行出来的值给v_count2
              execute immediate v_sql_str2 into v_count2;
              if (v_count2>0) then
                        --给动态游标赋值
                        open mycur1 for v_sql_str;
                        --对动态游标进行循环判断
                        Loop
                        fetch mycur1 into v_fieldvalue2;
                            if mycur1%notfound then exit;  --如果不存在  退出
                            elsif mycur1%found then    --如果存在继续执行
                            --继续调用存储过程（存储过程调用存储过程，调用自己，如果带参数，可以用类似动态sql的方式声明后动态执行）
                            recursion_delete(v_reltable,v_fieldvalue2);
                            end if;
                        end Loop;
              end if;
      end if;     
      end Loop;
      --循环结束，证明此数据虽然有关系表，但是没有引用，删除数据。
      --查询此表的主见字段
      select distinct str3.mainfield into v_primarykey_field from systablerel str3 where str3.maintable=v_tablename;
      v_sql_str3 := 'delete from '||v_tablename||' where '||v_primarykey_field||'='''||v_fieldvalue||'''';
      execute immediate v_sql_str3;
     
      --证明此为最后一层，开始删除
      elsif (v_count=0) then
      select distinct str3.mainfield into v_primarykey_field from systablerel str3 where str3.maintable=v_tablename;
      v_sql_str3 := 'delete from '||v_tablename||' where '||v_primarykey_field||'='''||v_fieldvalue||'''';
      execute immediate v_sql_str3;
      end if;  
end; 

在sql_plus中调用带参数的存储过程的方法：

declare
v_flag integer;
begin

--存储过程名以及参数，v_flag输出到哪个变量上
deleteproc('plan','4028e48523bb4be80123bbb8ee8a000d',1,v_flag);
dbms_output.put_line(v_flag);
end;

 

在高级点的存储过程，可能会用到临时表，以及group by 进行分组......