oracle 存储过程示例

一、基础存储过程

 

CREATE OR REPLACE PROCEDURE test_cursor
IS
   --声明游标
   CURSOR cur IS SELECT * FROM father;
BEGIN
  --遍历游标
   FOR s IN cur LOOP
     --插入新表中
     INSERT INTO t VALUES (s.f_id,s.f_name);

     END LOOP;
     dbms_output.put_line('数据插入成功');
     COMMIT;
     
     EXCEPTION
       --没有数据 
       WHEN no_data_found THEN
         dbms_output.put_line('表中没有数据无法执行插入操作...');
         --违反唯一约束
     WHEN DUP_VAL_ON_INDEX THEN
     dbms_output.put_line('违反唯一约束'); 
      
     WHEN OTHERS THEN
       dbms_output.put_line('未知异常');  
     
     ROLLBACK;

     END;
     
     
     
    
     
     
     

 

create or replace procedure yewu_guize_zxxx(prwid varchar2,ptableId varchar2,pout out number) is
temp_ varchar2(1000);--临时变量，存放字段序列(逗号隔开)
bmc varchar2(1000);
process_data_number number(8);
vsql varchar2(1000);
vsql_datas varchar2(1000);
vsql_temp varchar2(1000);

v_qiymc varchar2(100):='id';
v_gongszhuch varchar2(100):='id';
v_zzjgDm varchar2(100):='id';

v_quchong varchar2(200):='dc023bac594a4386925f711b5c7bf5b7';
v_qukong varchar2(200):='ad11c9af26344be099a50bc7f90494d3';
v_quanbanjiao varchar2(200):='3dc716fc78b84c5ba1b6e5a5e8d013c1';
v_guanlian varchar2(200):='764cf785bcc24bffb3e4392249a02164';
begin
  /*
  create GLOBAL TEMPORARY TABLE c$_temp_yewgl(
       rwid varchar2(50),
       qiymc varchar2(500),
       gongszhuch varchar2(50),
       zzjgDm varchar2(50)
     )ON COMMIT PRESERVE ROWS ;
  */
   --表名称
   select name into bmc from c_logic_table where id=ptableId;
   --数据处理总量
   vsql:='select count(1) from zjk_'||bmc||' where rwid='''||prwid||''' ';
   execute immediate vsql into process_data_number;
   --循环遍历每个字段
   for zds in(select name,name_desc from c_logic_column where logic_table_id=ptableId)
     loop
         --初始化变量
         if(zds.name_desc = '企业名称')then
            v_qiymc:=zds.name;
         end if;
         if(zds.name_desc = '工商注册号')then
             v_gongszhuch:=zds.name;
         end if;
         if(zds.name_desc = '组织机构代码')then
              v_zzjgDm:=zds.name;
         end if;
         --从中间库里面执行，全半角的操作
         vsql:='update zjk_'||bmc||' set '||zds.name||'=to_single_byte('||zds.name||') where to_single_byte('||zds.name||')!='''||zds.name||'''';
         execute immediate vsql;
         pout:=SQL%ROWCOUNT;
            --更新规则信息表
         insert into rule_exe_xx values (seq_rule_exe_xx_id.nextval,v_quanbanjiao,process_data_number,pout,1,prwid);
         --从中间库里面执行，去空操作
         vsql:='update zjk_'||bmc||' set '||zds.name||'=trim('||zds.name||') where '||zds.name||' like '''||'% %'||'''';
         execute immediate vsql;
         pout:=SQL%ROWCOUNT;
            --更新规则信息表
         insert into rule_exe_xx values (seq_rule_exe_xx_id.nextval,v_qukong,process_data_number,pout,1,prwid);  
         --为临时变量赋值
         temp_:=temp_||zds.name||'||';
         commit;
     end loop;
   
   temp_:= temp_||'.';
   temp_:=replace(temp_,'||.','');
    --从中间库里面执行，关联操作
    vsql_temp:='select t2.id,t1.* from gongsj t1,zjk_'||bmc||' t2 where (t1.gongszhuch=t2.'||v_gongszhuch||' or t1.qiymc=t2.'||v_qiymc||' or t1.zzjgDm=t2.'||v_zzjgDm||')and rwid='''||prwid||'''';
   delete c$_temp_yewgl;
   vsql_datas:='insert into c$_temp_yewgl '||vsql_temp;
   execute immediate vsql_datas;
   commit;
   for datas in (select * from c$_temp_yewgl)
     loop
       vsql:='update zjk_'||bmc||' t set comm_qiymc='''||datas.qiymc||''',comm_gongszhuch='''||datas.gongszhuch||''',comm_zzjgDm='''||datas.zzjgDm||''',comm_hyDm='''||datas.hydm||''',comm_quydm='''||datas.quydm||''' WHERE ID='''||datas.rwid||'''';  
       execute immediate vsql;
       pout:=SQL%ROWCOUNT;
      --更新规则信息表
       insert into rule_exe_xx values (seq_rule_exe_xx_id.nextval,v_guanlian,process_data_number,pout,1,prwid);
       commit;
     end loop;
    --从中间库里面执行，去重操作
   vsql:='delete from zjk_'||bmc||' t where ('||temp_||') in
   (select '||temp_||' from zjk_'||bmc||' where rwid='''||prwid||''' group by '||temp_||' having count('||temp_||')>1)and
   rowid not in (select max(rowid) from zjk_'||bmc||' where rwid='''||prwid||''' group by '||temp_||' having count('||temp_||')>1)
   and rwid='''||prwid||'''';
   execute immediate vsql;
   pout:=SQL%ROWCOUNT;
   --更新规则信息表
   insert into rule_exe_xx values (seq_rule_exe_xx_id.nextval,v_quchong,process_data_number,pout,1,prwid);
   commit;
end yewu_guize_zxxx;