Oracle游标cursor相关

--游标 在pl/sql中执行select intsert update delete 语句的时候
--Oracle会在 内存中为其分配上文区。游标是指向该区的指针。


+-------<-------+
| 循环 | N
| | Y
声明游标 ---> 打开游标 ---> 提取游标 ----> 空 ----> 关闭游标

CURSOR cursor_name (参数)
[RETURN 返回类型]
SELECT 语句；
打开
open cursor_name
提取
PETCH 游标名字 INTO {...}
关闭
CLOSE cursor_name;

--------------------------------------------------------------------------------------------------
--c1不带参数的 c2带参数的 c3带参数有返回值的 c4含自定义类型
--------------------------------------------------------------------------------------------------
例子：

DECLARE 
	TYPE DeptRecord IS RECORD(
		deptno dept.deptno%TYPE,
		dname dept.dname%TYPE,
		loc dept.loc%TYPE);
	v_deptrecord DeptRecord;
	v_dept_name dept.dname%TYPE;
	v_dept_loc dept.loc%TYPE;
	
	--声明游标
	CURSOR c1
		IS
			SELECT dname, loc FROM dept WHERE deptno <= 10;
	CURSOR c2(v_dept_no NUMBER)
		IS
			SELECT dname, loc FROM dept WHERE deptno <= v_dept_no;
	CURSOR c3(v_dept_no NUMBER)
		RETURN DeptRecord
		IS
			SELECT deptno, dname, loc FROM dept WHERE deptno <= v_dept_no;
	CURSOR c4(v_dept_no NUMBER)
		IS
			SELECT deptno, dname, loc FROM dept WHERE deptno <= v_dept_no;
	v_dept_rec c4%ROWTYPE;
	
BEGIN
	--打开一个游标
	OPEN c1;
	LOOP
		FETCH c1 INTO v_dept_name, v_dept_loc;
		IF c1%FOUND THEN
			DBMS_OUTPUT.PUT_LINE('C1:'||v_dept_name||' '||v_dept_loc);
		ELSE
			DBMS_OUTPUT.PUT_LINE('C1结果集处理完毕了');
			EXIT;
		END IF;
	END LOOP;
	
	--打开第二个游标
	OPEN c2(20);
	LOOP
		FETCH c2 INTO v_dept_name, v_dept_loc;
		EXIT WHEN c2%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('C2:'||v_dept_name||' '||v_dept_loc);
	END LOOP;
	
	--打开第三个游标
	OPEN c3(v_dept_no => 30);
	LOOP
		FETCH c3 INTO v_deptrecord;
		EXIT WHEN c3%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('CE:'||v_deptrecord.dname||' '||v_deptrecord.loc);
	END LOOP;
	
	--打开第四个游标
	OPEN c4(20);
	LOOP
		FETCH c4 INTO v_dept_rec;
		EXIT WHEN c4%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('CE:'||v_dept_rec.deptno||' '||v_dept_rec.dname);
	END LOOP;
	CLOSE c1;
	CLOSE c2;
	CLOSE c3;
	CLOSE c4;
END;