【转载】达梦7存储过程中游标的使用(for循环 IF等)(达梦8适用)

da11

达梦7存储过程中游标的使用(for循环 IF等)(达梦8适用)

转载地址：https://blog.csdn.net/qq78442761/article/details/105773810

表如下：



如下存储过程使用游标遍历所有数据：





CREATE OR REPLACE PROCEDURE "CFFTEST"."SELECT_STUDENT"("id" IN INT)

AS
        
        myId int;
        myName varchar(50);
        
        cursor myCursor is select id, name from cfftest.student;
BEGIN

        open myCursor;
        
        loop
        
                fetch myCursor into myId, myName;
                exit when myCursor%notfound;
                print 'id: ' || myId || '  name' || myName;
               
        end loop;
        
        close myCursor;
END;


运行截图如下：



如果需要添加一个返回的结果集添加如下即可：
Select ‘ok’;


CREATE OR REPLACE PROCEDURE "CFFTEST"."SELECT_STUDENT"("id" IN INT)

AS
        
        myId int;
        myName varchar(50);
        
        cursor myCursor is select id, name from cfftest.student;
BEGIN

        open myCursor;
        
        loop
        
                fetch myCursor into myId, myName;
                exit when myCursor%notfound;
                print 'id: ' || myId || '  name' || myName;
               
        end loop;
        
        close myCursor;
        select 'ok';
END;


运行截图如下：




使用for循环进行遍历，运行截图如下



源码如下：

CREATE OR REPLACE PROCEDURE "CFFTEST"."SELECT_STUDENT"("id" IN INT)

AS

BEGIN

        for recordOne in (select id, name from cfftest.student) loop
                print recordOne.id || '  ' || recordOne.name;
        end loop;
        select 'ok';
END;


一般获取单条数据可以这样做：
运行截图如下：




源码如下：

CREATE OR REPLACE PROCEDURE "CFFTEST"."SELECT_STUDENT"("id" IN INT)

AS
        myname varchar(50);
BEGIN

        select name into myname from cfftest.student where id="id";
        print 'name is ' || myname;
END;


这里还可以增加if等功能





源码如下：

CREATE OR REPLACE PROCEDURE "CFFTEST"."SELECT_STUDENT"("id" IN INT)

AS
        myCount int;
BEGIN

        select count(*) into myCount from cfftest.student where id="id";
        if myCount > 0 then
        
                select '存在数据';
        else
        
                select '不存中数据';
        end if;
END;



=================================

实际场景存储过程使用游标：

-- 使用游标循环遍历分析表数据的存储过程
CREATE OR REPLACE PROCEDURE ZZFX_AUTO_GDFJ_HGYCSJHZ_INSERTLOOP
AS

GDFJCUR varchar(300);

--游标获取基本模型名称表
cursor myCursor is select GDFJ from ZZFX_AUTO_GDFJLIST;
BEGIN

open myCursor;

loop

  fetch myCursor into GDFJCUR;
  exit when myCursor%notfound;

  --插入数据汇总表
  --注意：limit 1如果不加上，可能会导致执行存储过程报“单行子查询返回多行”错误
  insert into ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ(GDFJ) values (GDFJCUR);
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set YCZS = (select count(*) from ZZFX_AUTO_HB_ALL where ZRDW = GDFJCUR) where GDFJ = GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set SQXF = (
   select count(*) from ZZFX_AUTO_HB_ALL where CHECK_STATUS='2' and STATUS='2' AND DISPOSAL_METHOD != '0' and ZRDW=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set KJSF = (
   select count(*) from ZZFX_AUTO_HB_ALL where CHECK_STATUS='1' and STATUS='2' and ZRDW=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set YWCZS = (select SQXF + KJSF from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) where GDFJ=GDFJCUR;

  
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set WZG = (
   select count(*) from ZZFX_AUTO_HB_ALL where CHECK_STATUS = '2' AND DISPOSAL_METHOD = '0' and ZRDW=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set SHZ_SQX = (
   select count(*) from ZZFX_AUTO_HB_ALL where CHECK_STATUS = '2' AND STATUS = '1' and ZRDW=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set QXZS = (select SQXF + WZG + SHZ_SQX from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set WPF = (
   select count(*) from ZZFX_AUTO_HB_ALL where DISTRIBUTE_METHOD = '0' and ZRDW=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set WHS = (
   select count(*) from ZZFX_AUTO_HB_ALL where CHECK_STATUS = '0' and ZRDW=GDFJCUR) where GDFJ=GDFJCUR;
  update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set YSHZS = (select YCZS - WPF - WHS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) where GDFJ=GDFJCUR;

  --判断除数是否不为0才更新以下比率
  if((select YCZS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) != 0 ) then
   update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set YCSJSHL = (
    select YWCZS / YCZS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) where GDFJ=GDFJCUR;
   commit;
  end if;

  if((select YSHZS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) != 0 ) then
   update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set QXHSL = (select QXZS / YSHZS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) where GDFJ=GDFJCUR;
   commit;
  end if;

  if((select QXZS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) != 0 ) then
   update ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ set ZGWCL = (select SQXF / QXZS from ZZFX_AUTO_JGB_HGYCSJHZ_GDFJ where GDFJ=GDFJCUR) where GDFJ=GDFJCUR;
   commit;
  end if;
end loop;
close myCursor;
END;
/