游标

--隐式游标
DECLARE
v_name emp.ename%TYPE;
BEGIN
SELECT ename INTO v_name
FROM emp
WHERE empno=45;
IF SQL%notfound THEN
dbms_output.put_line('notfound该员工不存在');
ELSE
dbms_output.put_line('该员工存在');
END IF;
EXCEPTION
WHEN no_data_found then
dbms_output.put_line('no_data_found该员工不存在');
when OTHERS then
dbms_output.put_line('其他错误');
END;
----------------------------------------------------------------------------------------------------
--显示游标
DECLARE
cursor emp_cursor IS
SELECT ename,sal
FROM emp
WHERE deptno=10;
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPen emp_cursor;
loop
FETCH emp_cursor INTO v_name,v_sal;
exit WHEN emp_cursor%notfound;
dbms_output.put_line(v_name||':'||v_sal);
END loop;
close emp_cursor;
END;

----------------------------------------------------------------------------------------------------
--带参数显示游标
DECLARE
cursor emp_cursor(no NUMBER) IS
SELECT ename,sal
FROM emp
WHERE deptno=no;
emp_record emp_cursor%ROWTYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPen emp_cursor(10);
END IF;
loop
FETCH emp_cursor INTO emp_record;
exit WHEN emp_cursor%notfound;
dbms_output.put_line(emp_record.ename||':'||emp_record.sal);
END loop;
close emp_cursor;
END;

----------------------------------------------------------------------------------------------------
--循环游标
DECLARE
cursor emp_cursor(no NUMBER) IS
SELECT ename,sal
FROM emp
WHERE deptno=no;
BEGIN
FOR emp_record IN emp_cursor(20)
loop
dbms_output.put_line(emp_record.ename||':'||emp_record.sal);
END loop;
END;

--------------------------------------------------------------------------------------------------------------------------------
--使用显示游标更新行

SELECT * FROM emp WHERE deptno=30

DECLARE
cursor emp_cursor IS
SELECT ename,sal,e.deptno,dname
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno
FOR UPDATE OF e.job;
emp_record emp_cursor%ROWTYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPen emp_cursor;
END IF;
LOOP
FETCH emp_cursor INTO emp_record;
exit WHEN emp_cursor%notfound;
IF emp_record.deptno=30 THEN
UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
dbms_output.put_line(emp_record.sal);
END loop;
close emp_cursor;
END;

SELECT * from emp

ROLLBACK;
----------------------------------------------------------
--使用游标类型变量
DECLARE
--TYPE my_ctype IS ref cursor;
--cv my_ctype;
cv sys_refursor;
v_lastname emp.ename%TYPE;
query_2 VARCHAR2(200):='select * from dept';
v_emp emp%rowtype;
v_dept dept%rowtype;
BEGIN
open cv FOR
SELECT ename FROM emp WHERE job='manager' ORDER BY ename;
loop
FETCH cv INTO v_lastname;
exit WHEN cv%notfound;
dbms_output.put_line(v_lastname);
END loop;
dbms_output.put_line('------------------------------------------');
open cv FOR query_2;
loop
FETCH cv INTO v_dept;
exit WHEN cv%notfound;
dbms_output.put_line(v_dept.dname);
END loop;
close cv;
END;

select * from emp