SQL工作笔记-达梦7存储过程中游标的使用(for循环 IF等)SQL,达梦IT1995的博客-

表如下：

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

 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;