简单游标使用

2019独角兽企业重金招聘Python工程师标准>>>

需求：

比较post_info和post_info_if表。如果post_info_if中某条数据在post_info中则更新，如果没有则插入，并且将异常的某条记录打上标志。该存储过程比较灵活，可以在if中可以继续添加条件及处理方式。

createorreplaceprocedure fn_merge_post is
begin
  DECLARE
        CURSOR c_dl IS    //定义一个游标
            (select id , post_name,    dep_id,  department_id
        from post_info_if
       );
        c_row c_dl%ROWTYPE;  //声明一个游标变量，及类型
        numb number;  //声明变量及类型
        v_sqlfalg varchar(400);//声明变量及类型
   BEGIN
        FOR c_row IN c_dl LOOP  //循环遍历游标，将游标中的一条数据放到c_row中
          begin
               v_sqlfalg   := 'SELECT count(*) FROM  post_info  WHERE  post_id=''' ||c_row.ID ||'''';  
               execute immediate v_sqlfalg into numb;      //变量赋值   
           if numb>0 then  //判断是更新还是插入操作
                          update post_info oo set
                           oo.post_name = c_row.post_name,
                           oo.dep_id = c_row.dep_id,
                           oo.department_id = c_row.DEPARTMENT_ID;
                           COMMIT;
           elsif numb=0 then
                          insert into post_info(POST_ID,post_name,dep_id,DEPARTMENT_ID)  values(c_row.id,c_row.post_name,c_row.dep_id,c_row.department_id);
               COMMIT;
           END IF;
           EXCEPTION   //异常处理
                WHEN OTHERS THEN
                    ROLLBACK;   //发生异常，并回滚。
                    BEGIN   
                       dbms_output.put_line('出错ID：'||c_row.id);  //打印错误数据id
                       update post_info_if set sychro_flag ='-1' where id = c_row.id;  //把错误数据打上标记位
                         COMMIT;
                    END;
          end;
        END LOOP;
    END;
end fn_merge_post;

需求更新：当该条数据的结束时间小于当前时间，该条数据要删除，否则更新或插入该条数据

create or replace procedure fn_merge_post is
begin
  DECLARE
        CURSOR c_dl IS
            (select id , post_name,    dep_id,  department_id ,end_date
        from post_info_if
       );
        c_row c_dl%ROWTYPE;
        numb number;
        v_sqlfalg varchar(400);
        currentDate date;
        v_err_msg varchar2(1000);
        i_code  number:=0;
   BEGIN
        select sysdate into currentDate from dual;
        FOR c_row IN c_dl LOOP
          begin             
               v_sqlfalg   := 'SELECT count(*) FROM  post_info  WHERE  post_id=''' ||c_row.ID ||'''';
               execute immediate v_sqlfalg into numb;                
           if numb>0 then
                          if c_row.end_date <= currentDate then
                             dbms_output.put_line('删除时间：'||c_row.end_date);
                             delete from post_info where post_id=c_row.id;
                          elsif  c_row.end_date > currentDate then
                            update post_info oo set
                             oo.post_name = c_row.post_name,
                             oo.dep_id = c_row.dep_id,
                             oo.department_id = c_row.DEPARTMENT_ID;
                             COMMIT;
                           end if;
           elsif numb=0 then
                          insert into post_info(POST_ID,post_name,dep_id,DEPARTMENT_ID)  values(c_row.id,c_row.post_name,c_row.dep_id,c_row.department_id);
               COMMIT;
           END IF;
           EXCEPTION
                WHEN OTHERS THEN
                    ROLLBACK;           
                    BEGIN
                       v_err_msg:=sqlerrm;
                       i_code:=to_char(sqlcode);
                       dbms_output.put_line('错误信息：'||v_err_msg);
                       update post_info_if set sychro_flag ='-1',erro_msg= v_err_msg where id = c_row.id;
                         COMMIT;
                    END;    
          end;         
        END LOOP;
    END;
end fn_merge_post;

 

 

 

 

 

 

 

 

 

 

 

转载于:https://my.oschina.net/u/3409039/blog/1552936