游标语法

drop table student;
create table student
(
	stuNo int primary key,
	Name varchar2(10),
	address varchar2(30),
	birthday date
);

insert into student values(1,'梅超风','山东',to_date('1860-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));
insert into student values(2,'陆成风','山西',to_date('1860-2-12','YYYY-MM-DD'));
insert into student values(3,'冯默风','安徽','10-2月-1886');
insert into student values(4,'曲灵风','湖南常德',to_date('1870-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));


Rem ===================================================================
Rem 隐式游标 
Rem ===================================================================

SET SERVEROUTPUT ON;
BEGIN
	UPDATE student SET address='河南洛阳'
	WHERE stuNo = 1;
	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('表已更新');
	END IF;
END;
/

BEGIN
	UPDATE student SET address=replace(address,'南','北') 
	WHERE address LIKE '%南%';
	IF SQL%NOTFOUND THEN
		DBMS_OUTPUT.PUT_LINE('未找到数据........');
	ELSE
		DBMS_OUTPUT.PUT_LINE('影响了'||SQL%ROWCOUNT||'行');
	END IF;
END;
/

Rem ===================================================================
Rem 显式游标 
Rem ===================================================================
--使用游标显示name列的值
DECLARE
	l_name VARCHAR2(20);
	CURSOR stu_name_cur IS SELECT name from student;
BEGIN
	OPEN stu_name_cur;		--打开游标
	LOOP
		FETCH stu_name_cur INTO l_name;
		DBMS_OUTPUT.PUT_LINE(stu_name_cur%ROWCOUNT);
		EXIT WHEN stu_name_cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('姓名：'||l_name);
	END LOOP;
	CLOSE stu_name_cur;		--关闭游标
END;
/


--带参数的游标
DECLARE
	l_name VARCHAR2(20);
	l_stuNO NUMBER(2);
	CURSOR stu_name_cur(sNo NUMBER) 
		IS SELECT name from student
			WHERE stuNo>sNo;
BEGIN
	l_stuNO:=&stuNO;
	OPEN stu_name_cur(l_stuNO);		--打开游标
	LOOP
		FETCH stu_name_cur INTO l_name;

		EXIT WHEN stu_name_cur%NOTFOUND;

		DBMS_OUTPUT.PUT_LINE('姓名：'||l_name);
	END LOOP;
	CLOSE stu_name_cur;		--关闭游标
END;
/

Rem ===================================================================
Rem 使用显示游标修改数据 
Rem ===================================================================

DECLARE
	l_birthday DATE;
	l_stuNO NUMBER(2);
	CURSOR stu_name_cur(sNo NUMBER) 
		IS SELECT birthday from student
			WHERE stuNo>sNo FOR UPDATE OF birthday;
BEGIN
	l_stuNO:=&stuNO;
	OPEN stu_name_cur(l_stuNO);		--打开游标
	LOOP
		FETCH stu_name_cur INTO l_birthday;
		EXIT WHEN stu_name_cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('出生年月：'||to_char(l_birthday,'yyyy"年"mm"月"dd"日"'));
		UPDATE student SET birthday=ADD_MONTHS(l_birthday,2)
			WHERE CURRENT OF stu_name_cur;   --更新当前行
	END LOOP;
	CLOSE stu_name_cur;		--关闭游标
	COMMIT;
END;
/

Rem ===================================================================
Rem 使用显示游标删除记录
Rem ===================================================================

DECLARE 
l_stu_rec student%ROWTYPE;
CURSOR del_cur IS select * from student FOR UPDATE;
BEGIN
	OPEN del_cur;
	LOOP
		FETCH del_cur into l_stu_rec;
		EXIT WHEN del_cur%NOTFOUND;
		IF l_stu_rec.stuno=2 THEN
			DELETE FROM student WHERE CURRENT OF del_cur; --删除当前行
		END IF;
	END LOOP;
END;
/

Rem ===================================================================
Rem 循环游标 
Rem ===================================================================

DECLARE
	CURSOR stu_cur IS
		SELECT stuNo,name,address FROM student;
BEGIN
	FOR stu IN stu_cur		--不要加分号
	LOOP
		DBMS_OUTPUT.PUT_LINE(stu.stuNO||'   '||stu.name||'   '||stu.address);
	END LOOP;
END;
/

Rem ===================================================================
Rem 弱类型REF游标 
Rem ===================================================================

DECLARE
	sNo student.stuNo%type;
	sName student.name%type;
	sAddress student.address%type;

	TYPE stu_cur_ref IS REF CURSOR;		--声明REF游标类型
	stu_cur stu_cur_ref;			--声明“stu_cur_ref”游标类型变量
BEGIN
	OPEN stu_cur FOR
		SELECT stuNo,name,address FROM student;
	LOOP
		FETCH stu_cur INTO sNo,sName,sAddress;
		EXIT WHEN stu_cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(sNo||'   '||sName||'   '||sAddress);
	END LOOP;
	CLOSE stu_cur;
END;
/

DECLARE
  l_stuno student.stuno%TYPE;
  l_name student.name%TYPE;
  TYPE stu_cur_ref IS REF CURSOR;
  curStu stu_cur_ref;
BEGIN
	OPEN curStu FOR
	  SELECT stuno,name FROM student;
	LOOP
	  FETCH curStu INTO l_stuno,l_name;
	  EXIT WHEN curStu%NOTFOUND;
	  DBMS_OUTPUT.PUT_LINE(l_stuno||'  '||l_name);
	END LOOP;
	CLOSE curStu;
END;
/

Rem ===================================================================
Rem 强类型REF游标 
Rem ===================================================================

DECLARE
	TYPE l_stu_type IS RECORD(		--自定义记录类型
		sNo student.stuNo%type,
		sName student.name%type,
		sAddress student.address%type
	);
	l_stu l_stu_type;			--声明自定义类型变量
	TYPE stu_cur_ref IS REF CURSOR		--声明REF游标类型
		RETURN l_stu_type;		--返回类型为自定义类型
	stu_cur stu_cur_ref;			--声明“stu_cur_ref”游标类型变量
BEGIN
	OPEN stu_cur FOR
		SELECT stuNo,name,address FROM student;
	LOOP
		FETCH stu_cur INTO l_stu;
		EXIT WHEN stu_cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(l_stu.sNo||'   '||
		l_stu.sName||'   '||l_stu.sAddress);
	END LOOP;
	CLOSE stu_cur;
END;
/

DECLARE 
  TYPE l_stu_type IS RECORD(
  	sno student.stuno%TYPE,
  	sname student.name%TYPE
  );
  l_stu l_stu_type;
  TYPE stu_cur_ref IS REF CURSOR RETURN l_stu_type;
  stu_cur stu_cur_ref;
BEGIN
	OPEN stu_cur FOR
	   SELECT stuno,name FROM student;  
	LOOP
	  FETCH stu_cur INTO l_stu;
	  EXIT WHEN stu_cur%NOTFOUND;
	  DBMS_OUTPUT.PUT_LINE(l_stu.sno||'  '||l_stu.sname);
	END LOOP;
	CLOSE stu_cur;
END;
/

Rem ===================================================================
Rem 使用游标变量执行动态 SQL 
Rem ===================================================================

DECLARE
	l_stu student%ROWTYPE;			
	TYPE stu_cur_ref IS REF CURSOR;		--声明REF游标类型
	stu_cur stu_cur_ref;			--声明“stu_cur_ref”游标类型变量
	l_stuNo NUMBER(2);
BEGIN
	l_stuNo := &sNo;
	OPEN stu_cur FOR
		'SELECT * FROM student WHERE stuNo>:1'
			USING l_stuNo;   --绑定参数
	LOOP
		FETCH stu_cur INTO l_stu;
		EXIT WHEN stu_cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(l_stu.stuNo||'   '||l_stu.name||'   '||l_stu.address||
			'   '||l_stu.birthday);
	END LOOP;
	CLOSE stu_cur;
END;
/