--游标:是Oracle系统在内存中开辟的一个工作区,可以存放select查询结果
--隐式的游标(SQL游标):不需要open、fetch、close等操作,
DECLARE
v_deptno NUMBER:=30;
BEGIN
DELETE FROM emp WHERE deptno=v_deptno;
dbms_output.put_line(SQL%ROWCOUNT||'行记录被删除。');
END;
SELECT * FROM emp;
--显式的游标:open打开游标、fetch提取游标数据、close关闭游标等操作
--循环打印部门表中的部门号和部门名称(1.使用游标变量接收数据)
DECLARE
--定义一个游标
CURSOR dept_cursor IS
SELECT deptno,dname FROM dept;
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
--游标使用之前,需要手动打开
OPEN dept_cursor;
LOOP
--提取数据
FETCH dept_cursor INTO v_deptno,v_dname;
EXIT WHEN dept_cursor%NOTFOUND;
dbms_output.put_line('部门号:'||v_deptno);
dbms_output.put_line('部门名称:'||v_dname);
dbms_output.put_line('==================');
END LOOP;
--关闭游标
CLOSE dept_cursor;
END;
--循环打印部门表中的部门号和部门名称(2.使用记录变量接收数据)
DECLARE
--定义一个游标
CURSOR dept_cursor IS
SELECT * FROM dept;
dept_record dept%ROWTYPE;
BEGIN
--游标使用之前,需要手动打开
IF NOT dept_cursor%ISOPEN THEN
OPEN dept_cursor;
END IF;
LOOP
--提取数据
FETCH dept_cursor INTO dept_record;
EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;
dbms_output.put_line('部门号:'||dept_record.deptno);
dbms_output.put_line('部门名称:'||dept_record.dname);
dbms_output.put_line('==================');
END LOOP;
--关闭游标
CLOSE dept_cursor;
END;
--3.使用集合变量接收数据
DECLARE
CURSOR dept_cursor IS
SELECT deptno,dname,loc FROM dept;
TYPE dept_table_type IS TABLE OF dept_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
idx NUMBER;--存储下标
BEGIN
OPEN dept_cursor;
LOOP
idx:=dept_cursor%ROWCOUNT+1;
FETCH dept_cursor INTO dept_table(idx);
EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;
dbms_output.put_line('部门号:'||dept_table(idx).deptno);
dbms_output.put_line('部门名称:'||dept_table(idx).dname);
dbms_output.put_line('部门地址:'||dept_table(idx).loc);
dbms_output.put_line('==================');
END LOOP;
END;
--4.带参数的游标
DECLARE
--定义一个游标
CURSOR dept_cursor(v_deptno NUMBER DEFAULT 30) IS
SELECT deptno,dname,loc FROM dept WHERE deptno<v_deptno;
dept_record dept%ROWTYPE;
BEGIN
--OPEN dept_cursor;
OPEN dept_cursor(v_deptno=>&deptno);
LOOP
--提取数据
FETCH dept_cursor INTO dept_record;
EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3;
dbms_output.put_line('部门号:'||dept_record.deptno);
dbms_output.put_line('部门名称:'||dept_record.dname);
dbms_output.put_line('部门地址:'||dept_record.loc);
dbms_output.put_line('==================');
END LOOP;
--关闭游标
CLOSE dept_cursor;
END;
--游标for循环1(自动执行游标的open、fetch、close)
DECLARE
CURSOR dept_cursor IS
SELECT deptno,dname,loc FROM dept;
BEGIN
FOR dept_record IN dept_cursor LOOP
dbms_output.put_line('部门号:'||dept_record.deptno);
dbms_output.put_line('部门名称:'||dept_record.dname);
dbms_output.put_line('部门地址:'||dept_record.loc);
dbms_output.put_line('==================');
END LOOP;
END;
--游标for循环2(简化写法)
DECLARE
BEGIN
FOR dept_record IN (SELECT deptno,dname,loc FROM dept) LOOP
dbms_output.put_line('部门号:'||dept_record.deptno);
dbms_output.put_line('部门名称:'||dept_record.dname);
dbms_output.put_line('部门地址:'||dept_record.loc);
dbms_output.put_line('==================');
END LOOP;
END;
--使用游标修改数据
--修改指定部门的员工的最低工资为1500
--NOWAIT:如果当前数据行已经被其他回话锁定,open将立即返回一个oracle错误
DECLARE
v_deptno emp.deptno%TYPE:=&p_deptno;
CURSOR emp_cursor IS
SELECT empno,sal FROM emp WHERE deptno=v_deptno FOR UPDATE NOWAIT;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF emp_record.sal<1500 THEN
dbms_output.put_line('职工号:'||emp_record.empno);
dbms_output.put_line('工资:'||emp_record.sal);
dbms_output.put_line('==================');
--修改数据(游标中当前行:WHERE CURRENT OF emp_cursor,需要在查询时提供FOR UPDATE)
--UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor;
DELETE FROM emp WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
COMMIT;
END;
SELECT * FROM emp;
--异常
DECLARE
v_empno emp.empno%TYPE:=&empno;
BEGIN
UPDATE emp SET sal=sal+1000 WHERE empno=v_empno;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('指定的员工不存在');
WHEN OTHERS THEN
dbms_output.put_line('产生其他异常');
END;
--自定义异常
DECLARE
v_empno emp.empno%TYPE:=&empno;
noresult EXCEPTION;
BEGIN
UPDATE emp SET sal=sal+1000 WHERE empno=v_empno;
IF(SQL%NOTFOUND) THEN
RAISE noresult;
ELSE
COMMIT;
END IF;
EXCEPTION
WHEN noresult THEN
dbms_output.put_line('指定的员工不存在');
WHEN OTHERS THEN
dbms_output.put_line('产生其他异常');
END;