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

表如下：

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

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;