oracle---游标使用

最新推荐文章于 2022-07-02 17:16:56 发布
ShaTaLiu_YLJ
最新推荐文章于 2022-07-02 17:16:56 发布
阅读量223
点赞数
分类专栏：数据库文章标签： plsql
本文链接：https://blog.csdn.net/ShaTaLiu_YLJ/article/details/114531387
版权
数据库专栏收录该内容
6 篇文章 0 订阅
订阅专栏

--案例一通过游标处理多个返回集
DECLARE
  --定义游标
  CURSOR cursor_polno is
  select contno,polno,appflag,riskcode from lcpol where contno='886814401718';
  t_contno lcpol.contno%type;
  t_polno lcpol.polno%type;
  t_appflag lcpol.appflag%type;
  t_riskcode lcpol.riskcode%type;
begin 
  --打开游标
  open cursor_polno;
  --提取数据
  loop 
    fetch cursor_polno into t_contno,t_polno,t_appflag,t_riskcode;
    exit when cursor_polno%NOTFOUND;
    update lcpol a set a.appflag='1',a.operator='VSB2101737',modifydate=trunc(sysdate)
    where a.polno=t_polno and contno=t_contno and riskcode=t_riskcode;    
  end loop;
  close cursor_polno;
  commit;
end;  




--案例二：通过游标处理不同的结果集
alter session set nls_date_format ='YYYY-MM-DD';
DECLARE
  CURSOR cursor_a1 is
  select contno,edorno,edorvalidate from lpedoritem where edoracceptno = '6120210219017342';
  t_contno lcpol.contno%type;
  t_edorno lppol.edorno%type;
  t_edorvalidate lpedoritem.edorvalidate%type;
begin
  open cursor_a1;
  loop
    fetch cursor_a1 into t_contno,t_edorno,t_edorvalidate;
    exit when cursor_a1%NOTFOUND;
    if trim(t_contno)='130330003783095' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-04-01',signdate = date'2020-04-01',firstpaydate =date'2020-04-01',paytodate = date'2020-04-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-04-01',signdate = date'2020-04-01',firstpaydate =date'2020-04-01',paytodate = date'2020-04-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
    elsIF trim(t_contno)='130330003783089' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-04-04',signdate = date'2020-04-04',firstpaydate =date'2020-04-04',paytodate = date'2020-04-04',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-04-04',signdate = date'2020-04-04',firstpaydate =date'2020-04-04',paytodate = date'2020-04-04',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
    elsIF trim(t_contno)='130330003783090' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-12-07',signdate = date'2020-12-07',firstpaydate =date'2020-12-07',paytodate = date'2020-12-07',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-12-07',signdate = date'2020-12-07',firstpaydate =date'2020-12-07',paytodate = date'2020-12-07',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
    ELSE 
      UPDATE lcpol  a  SET cvalidate = date'2020-01-01',signdate = date'2020-01-01',firstpaydate =date'2020-01-01',paytodate = date'2020-01-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-01-01',signdate = date'2020-01-01',firstpaydate =date'2020-01-01',paytodate = date'2020-01-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =t_contno;
     end if;
  end loop;
  close cursor_a1;
  commit;
end;  
/



--案例三：通过for循环使用游标
alter session set nls_date_format ='YYYY-MM-DD';
DECLARE
  CURSOR cursor_a2 is
  select contno,edorno,edorvalidate from lpedoritem where edoracceptno = '6120210219017342';
begin
	for s_record in cursor_a2 loop
	if trim(s_record.contno)='130330003783095' THEN   
	  UPDATE lcpol  a  SET cvalidate = date'2020-04-01',signdate = date'2020-04-01',firstpaydate =date'2020-04-01',paytodate = date'2020-04-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-04-01',signdate = date'2020-04-01',firstpaydate =date'2020-04-01',paytodate = date'2020-04-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
    elsIF trim(s_record.contno)='130330003783089' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-04-04',signdate = date'2020-04-04',firstpaydate =date'2020-04-04',paytodate = date'2020-04-04',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-04-04',signdate = date'2020-04-04',firstpaydate =date'2020-04-04',paytodate = date'2020-04-04',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
    elsIF trim(s_record.contno)='130330003783090' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-12-07',signdate = date'2020-12-07',firstpaydate =date'2020-12-07',paytodate = date'2020-12-07',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-12-07',signdate = date'2020-12-07',firstpaydate =date'2020-12-07',paytodate = date'2020-12-07',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
    ELSE 
      UPDATE lcpol  a  SET cvalidate = date'2020-01-01',signdate = date'2020-01-01',firstpaydate =date'2020-01-01',paytodate = date'2020-01-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-01-01',signdate = date'2020-01-01',firstpaydate =date'2020-01-01',paytodate = date'2020-01-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
     end if;
  end loop
  commit;
end;
/


--案例四：通过for循环使用游标（使用子查询代替定义游标）
alter session set nls_date_format ='YYYY-MM-DD';
/* DECLARE
  CURSOR cursor_a2 is
  select contno,edorno,edorvalidate from lpedoritem where edoracceptno = '6120210219017342'; */
begin
	for s_record in (select contno,edorno,edorvalidate from lpedoritem where edoracceptno = '6120210219017342') loop
	if trim(s_record.contno)='130330003783095' THEN   
	  UPDATE lcpol  a  SET cvalidate = date'2020-04-01',signdate = date'2020-04-01',firstpaydate =date'2020-04-01',paytodate = date'2020-04-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-04-01',signdate = date'2020-04-01',firstpaydate =date'2020-04-01',paytodate = date'2020-04-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
    elsIF trim(s_record.contno)='130330003783089' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-04-04',signdate = date'2020-04-04',firstpaydate =date'2020-04-04',paytodate = date'2020-04-04',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-04-04',signdate = date'2020-04-04',firstpaydate =date'2020-04-04',paytodate = date'2020-04-04',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
    elsIF trim(s_record.contno)='130330003783090' THEN 
      UPDATE lcpol  a  SET cvalidate = date'2020-12-07',signdate = date'2020-12-07',firstpaydate =date'2020-12-07',paytodate = date'2020-12-07',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-12-07',signdate = date'2020-12-07',firstpaydate =date'2020-12-07',paytodate = date'2020-12-07',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
    ELSE 
      UPDATE lcpol  a  SET cvalidate = date'2020-01-01',signdate = date'2020-01-01',firstpaydate =date'2020-01-01',paytodate = date'2020-01-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno;
      UPDATE LPPOL  a  SET cvalidate = date'2020-01-01',signdate = date'2020-01-01',firstpaydate =date'2020-01-01',paytodate = date'2020-01-01',
      operator='VSB2103517',MODIFYDATE=to_date(SYSDATE, 'YYYY-MM-DD'), modifytime   = to_char(SYSDATE, 'HH24:MI:SS')
      WHERE contno =s_record.contno and edorno=s_record.edorno;
     end if;
  end loop
  commit;
end;
/
ShaTaLiu_YLJ
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
oracle---游标使用

--案例一通过游标处理多个返回集DECLARE --定义游标 CURSOR cursor_polno is select contno,polno,appflag,riskcode from lcpol where contno='886814401718'; t_contno lcpol.contno%type; t_polno lcpol.polno%type; t_appflag lcpol.appflag%type; t_riskcode lcpol.riskcode.
复制链接

扫一扫
专栏目录