PL/SQL编程---游标

--游标
	--作用
		当数据库在执行SQL语句时，会给SQL语句分配一个缓冲区，游标是指向该该缓冲区的
		一个地址，通过游标可以获取到SQL语句的执行结果。
	--分类
		--显式游标
			主要针对SELECT语句，定义一个游标，指向SELECT语句的查询结果集，可以
			通过游标获取到每一条查询结果
		--隐式游标
			当执行INSERT、DELETE、UPDATE语句时，数据库会自动分配一个游标，可以
			使用SQL来调用游标的属性，一般情况隐式游标只用来获取SQL语句影响的数据条数。
	--游标的属性
		1)%FOUND：当游标当前指向的数据不为空时，返回TRUE
		2)%NOTFOUND：当游标当前指向的数据为空时，返回TRUE
		3)%ROWCOUNT：可以用来表示游标中的数据条数，也可以表示游标中数据的行号
		4)%ISOPEN：当当前游标是打开状态时，返回TRUE
-------------------------------------------------------------------------------

--显式游标
	--定义语法
		CURSOR 游标变量 IS SELECT语句;
	--游标的使用步骤
		1)打开游标：OPEN 游标变量;
		2)遍历游标：FETCH 游标变量 INTO 变量;
		3)关闭游标：CLOSE 游标变量;
	--游标属性总结
		游标未打开前，除了%ISOPEN属性可用，%FOUND、%NOTFOUND、%ROWCOUNT属性都不可用
		执行一次FETCH INTO语句，%ROWCOUNT属性的值增加1
	--游标的遍历
		--LOOP循环遍历游标
			--语法
			DECLARE
				CURSOR 游标变量 IS SELECT语句; --定义游标
				V_EMP 数据类型; --声明一个变量保存游标的一条记录
			BEGIN
				OPEN 游标变量; --打开游标
				LOOP --遍历游标
					FETCH 游标变量 INTO V_EMP;
					EXIT WHEN 游标变量%NOTFOUND;
					... --循环体语句(逻辑处理)
				END LOOP;
				CLOSE 游标变量; --关闭游标
			END;
		--FOR循环遍历游标
			--语法
			DECLARE
				CURSOR 游标变量 IS SELECT语句; --定义游标
			BEGIN
				FOR 循环变量 IN 游标变量 LOOP
					... --循环体语句(逻辑处理)
				END LOOP;
			END;
		--WHILE循环遍历游标
			--语法
			DECLARE
				CURSOR 游标变量 IS SELECT语句; --定义游标
				V_EMP 数据类型; --声明一个变量保存游标的一条记录
			BEGIN
				OPEN 游标变量;
				FETCH 游标变量 INTO V_EMP;
				WHILE 游标变量%FOUND LOOP
					... --循环体语句(逻辑处理)
				END LOOP;
				CLOSE 游标变量;
			END;
		
--示例1，定义一个游标，指向所有的部门名称
DECLARE
	V_DNAME SCOTT.DEPT.DNAME%TYPE;
	CURSOR CUR_DANME IS SELECT DNAME FROM DEPT;
BEGIN
	OPEN CUR_DANME;
	LOOP
		FETCH CUR_DANME INTO V_DNAME;
		EXIT WHEN CUR_DANME%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(CUR_DANME%ROWCOUNT||','||V_DNAME);
	END LOOP;
	CLOSE CUR_DANME;
END;

--示例2，使用游标，查询所有的部门信息(LOOP循环实现)
DECLARE
	CURSOR CUR_DEPT IS SELECT * FROM DEPT;
	--V_DEPT SCOTT.DEPT%ROWTYPE;
	V_DEPT CUR_DEPT%ROWTYPE;
BEGIN
	OPEN CUR_DEPT;
	LOOP
		FETCH CUR_DEPT INTO V_DEPT;
		EXIT WHEN CUR_DEPT%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPTNO||','||V_DEPT.DNAME||','||V_DEPT.LOC);
	END LOOP;
	CLOSE CUR_DEPT;
END;
	
--示例3，使用游标，查询所有的部门信息(WHILE循环实现)	
DECLARE
	CURSOR CUR_DEPT IS SELECT * FROM DEPT;
	V_DEPT CUR_DEPT%ROWTYPE;
BEGIN
	OPEN CUR_DEPT;
	FETCH CUR_DEPT INTO V_DEPT;
	WHILE CUR_DEPT%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPTNO||','||V_DEPT.DNAME||','||V_DEPT.LOC);
		FETCH CUR_DEPT INTO V_DEPT;
	END LOOP;
	CLOSE CUR_DEPT;
END;
	
--示例4，使用游标，查询所有的部门信息(FOR循环实现)		
DECLARE
	CURSOR CUR_DEPT IS SELECT * FROM DEPT;
BEGIN
	FOR V_DEPT IN CUR_DEPT LOOP
		DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPTNO||','||V_DEPT.DNAME||','||V_DEPT.LOC);
	END LOOP;
END;
-------------------------------------------------------------------------------

--带参数的游标
	--语法
		CURSOR 游标变量(参数 数据类型 [DEFAULT 默认值],...) IS SELECT语句;
	--传参方式
		1)传值
		2)传变量
		3)参数名=>值
		
		
--示例1，根据部门编号查询该部门下的员工信息
DECLARE
	CURSOR CUR_EMP(DNO NUMBER DEFAULT 30) IS SELECT * FROM EMP WHERE DEPTNO=DNO;
	V_EMP CUR_EMP%ROWTYPE;
BEGIN
	OPEN CUR_EMP(20);
	LOOP
		FETCH CUR_EMP INTO V_EMP;
		EXIT WHEN CUR_EMP%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(V_EMP.DEPTNO||','||V_EMP.ENAME||','||V_EMP.JOB);
	END LOOP;
	CLOSE CUR_EMP;

	FOR V_EMP IN CUR_EMP LOOP
		DBMS_OUTPUT.PUT_LINE(V_EMP.DEPTNO||','||V_EMP.ENAME||','||V_EMP.JOB);
	END LOOP;
END;

--示例2，查询20号部门，工资大于2000的员工信息
DECLARE
	CURSOR CUR_EMP(I_DNO NUMBER DEFAULT 20,I_SAL NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=I_DNO AND SAL>I_SAL;
BEGIN
	FOR V_EMP IN CUR_EMP(I_SAL=>2000) LOOP
		DBMS_OUTPUT.PUT_LINE(V_EMP.DEPTNO||','||V_EMP.ENAME||','||V_EMP.SAL);
	END LOOP;
END;
-------------------------------------------------------------------------------

--游标类型和游标变量
	--系统自带游标类型
		SYS_REFCURSOR 系统定义好的游标类型，相当于使用TYPE定义的游标类型
	--游标类型的定义
		TYPE 类型名 IS REF CURSOR [RETURN];
	--定义游标变量
		变量 类型名;
	--游标变量的使用
		1)打开游标：OPEN 游标变量 FOR SELECT语句;
		2)遍历游标：FETCH 游标变量 INTO 变量;
		3)关闭游标：CLOSE 游标变量;
	--游标变量使用场景
		游标变量可以作为参数来传递数据
	--注意
		游标变量只能使用LOOP循环或WHILE循环进行遍历，不能使用FOR循环
		
--示例1，定义一个游标类型,查询所有员工的姓名
DECLARE
	TYPE TP_CUR IS REF CURSOR;
	V_TP_CUR TP_CUR;
	V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
	OPEN V_TP_CUR FOR SELECT ENAME FROM EMP;
	LOOP
		FETCH V_TP_CUR INTO V_ENAME;
		EXIT WHEN V_TP_CUR%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(V_TP_CUR%ROWCOUNT||','||V_ENAME);
	END LOOP;
	CLOSE V_TP_CUR;
END;
		
--示例1，利用系统定义的游标类型(SYS_REFCURSOR),查询所有员工的姓名
DECLARE
	CUR_EMP SYS_REFCURSOR;
	V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
	OPEN CUR_EMP FOR SELECT ENAME FROM EMP;
	LOOP
		FETCH CUR_EMP INTO V_ENAME;
		EXIT WHEN CUR_EMP%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT||','||V_ENAME);
	END LOOP;
	CLOSE CUR_EMP;
END;		
-------------------------------------------------------------------------------

--游标练习题
--练习题1，用游标显示所有部门编号和名称，以及其所拥有的员工人数。
DECLARE
	CURSOR CUR_EMP IS SELECT D.DEPTNO,D.DNAME,COUNT(E.EMPNO) CNT FROM DEPT D 
	LEFT JOIN EMP E ON D.DEPTNO=E.DEPTNO GROUP BY D.DEPTNO,D.DNAME;
	V_EMP CUR_EMP%ROWTYPE;
BEGIN
	OPEN CUR_EMP;
	LOOP
		FETCH CUR_EMP INTO V_EMP;
		EXIT WHEN CUR_EMP%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(V_EMP.DEPTNO||','||V_EMP.DNAME||','||V_EMP.CNT);
	END LOOP;	
	CLOSE CUR_EMP;	
END;
		
--练习题2，用游标属性%ROWCOUNT实现输出前十个员工的信息。
DECLARE
	CURSOR CUR_EMP IS SELECT * FROM EMP;
	V_EMP CUR_EMP%ROWTYPE;
BEGIN
	OPEN CUR_EMP;
	FETCH CUR_EMP INTO V_EMP;
	WHILE CUR_EMP%FOUND AND CUR_EMP%ROWCOUNT<=10 LOOP
		--EXIT WHEN CUR_EMP%ROWCOUNT=11;
		DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT||','||V_EMP.DEPTNO||','||V_EMP.ENAME);
		FETCH CUR_EMP INTO V_EMP;		
	END LOOP;
	CLOSE CUR_EMP;
END;
		
--练习题3，接受一个部门编号，使用FOR循环从EMP表中显示该部门所有员工姓名、工作和薪资
DECLARE
	CURSOR CUR_EMP(I_DNO NUMBER) IS SELECT DEPTNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO=I_DNO;
BEGIN
	FOR V_EMP IN CUR_EMP(20) LOOP
		DBMS_OUTPUT.PUT_LINE(V_EMP.DEPTNO||','||V_EMP.ENAME||','||V_EMP.JOB||','||V_EMP.SAL);
	END LOOP;
END;
	
--练习题4,按照salgrade表中的标准,给员工加薪,1：5%,2：4%,3：3%,4：2%,5：1%,并打印输出每个人加薪前后的工资。
--方法一
DECLARE
	CURSOR CUR_EMP IS SELECT EMPNO,ENAME,SAL,GRADE FROM EMP E LEFT JOIN SALGRADE SG 
		ON E.SAL BETWEEN LOSAL AND HISAL;
	V_SAL SCOTT.EMP.SAL%TYPE;
BEGIN
	FOR V_EMP IN CUR_EMP LOOP
		--DBMS_OUTPUT.PUT_LINE('加薪前薪资='||V_EMP.SAL);
		CASE V_EMP.GRADE
			WHEN 1 THEN V_SAL:=V_EMP.SAL*1.05;
			WHEN 2 THEN V_SAL:=V_EMP.SAL*1.04;
			WHEN 3 THEN V_SAL:=V_EMP.SAL*1.03;
			WHEN 4 THEN V_SAL:=V_EMP.SAL*1.02;
			WHEN 5 THEN V_SAL:=V_EMP.SAL*1.01;
		END CASE;
		UPDATE EMP SET SAL=V_SAL WHERE EMPNO=V_EMP.EMPNO;
		DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO||','||V_EMP.ENAME||','||
			'加薪前薪资='||V_EMP.SAL||','||'加薪后薪资='||V_SAL);
	END LOOP;
END;

--方法二
DECLARE
	CURSOR CUR_EMP IS SELECT * FROM EMP;
	V_SAL SCOTT.EMP.SAL%TYPE;
BEGIN
	FOR V_EMP IN CUR_EMP LOOP
		SELECT CASE GRADE 
			WHEN 1 THEN V_EMP.SAL*1.05
			WHEN 2 THEN V_EMP.SAL*1.04
			WHEN 3 THEN V_EMP.SAL*1.03
			WHEN 4 THEN V_EMP.SAL*1.02
			WHEN 5 THEN V_EMP.SAL*1.01
		END INTO V_SAL FROM SALGRADE
		WHERE V_EMP.SAL BETWEEN LOSAL AND HISAL;
		
		UPDATE EMP SET SAL=V_SAL WHERE EMPNO=V_EMP.EMPNO;
	
		DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO||','||V_EMP.ENAME||','||
			'加薪前薪资='||V_EMP.SAL||','||'加薪后薪资='||V_SAL);
	END LOOP;
END;