--游标:是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;
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;
--使用游标修改数据
--修改指定部门的员工的最低工资为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;
---第四章练习
/* 使用游标获取部门表(dept)中的部门号deptno,部门名,传递部门号
到游标中,获取在此部门中工作的员工姓名,工作,参加工作时间,工资。
如 部门号10 部门名ACCOUNTING
张三 SALES 1999.1.1 3000
...
*/
--1
DECLARE
CURSOR cursor_test IS
SELECT deptno,dname FROM dept;
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
dept_record dept%ROWTYPE;
emp_record emp%ROWTYPE;
ename_record emp.ename%TYPE;
CURSOR cursor_2(v_deptno1 dept.deptno%TYPE) IS
SELECT * FROM emp WHERE deptno=v_deptno1;
BEGIN
OPEN cursor_test;
LOOP
FETCH cursor_test INTO v_deptno,v_dname;
EXIT WHEN cursor_test%NOTFOUND;
dbms_output.put_line('部门号'||v_deptno);
dbms_output.put_line('部门名称'||v_dname);
OPEN cursor_2(v_deptno);
LOOP
FETCH cursor_2 INTO emp_record;
EXIT WHEN cursor_2%NOTFOUND;
dbms_output.put_line('雇员名称:'||emp_record.ename);
dbms_output.put_line('薪水:'||emp_record.sal);
dbms_output.put_line('入职时间:'||emp_record.hiredate);
END LOOP;
CLOSE cursor_2;
END LOOP;
CLOSE cursor_test;
END;
SELECT * FROM dept
SELECT * FROM copy_emp;
DROP TABLE copy_emp;
/*
2.游标处理中使用for update和where current of
在表中修改字段starts的值,sal字段中每一个1000,一个‘*’,如sal为3500,四舍五入
,starts打印3个'*'
建表语句如下:
*/
CREATE TABLE copy_emp as select * from emp;
alter table copy_emp add starts varchar(20);
SELECT * FROM copy_emp
DECLARE
v_empno emp.empno%TYPE:=&p_empno;
CURSOR emp_cursor IS
SELECT sal FROM copy_emp WHERE empno=v_empno ;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF FLOORdTHEN
UPDATE copy_emp SET starts ='*' WHERE empno=v_empno;
END IF;
END LOOP;
COMMIT;
END;
DECLARE
v_empno emp.empno%TYPE:=&p_empno;
CURSOR emp_cursor IS
SELECT sal FROM copy_emp WHERE empno=v_empno FOR UPDATE NOWAIT;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF FLOOR(emp_record.sal/1000)=1 THEN
UPDATE copy_emp SET starts ='*' WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
COMMIT;
END;
/*
3.定义游标,查询emp表,如果工资大于2000,
并且参加工作时间在82年之前,在屏幕上显示员工的名字,
工资和参加工作时间
*/
DECLARE
CURSOR emp_cursor1 IS
SELECT ename,sal,to_number(EXTRACT(YEAR FROM hiredate)) FROM emp ;
v_ename emp.ename%TYPE ;
v_sal emp.sal%TYPE ;
v_hiredate NUMBER ;
BEGIN
OPEN emp_cursor1;
LOOP
FETCH emp_cursor1 INTO v_ename,v_sal,v_hiredate;
EXIT WHEN emp_cursor1%NOTFOUND;
IF v_sal>1000 AND v_hiredate<1982 THEN
dbms_output.put_line('雇员名称:'||v_ename);
ELSE
dbms_output.put_line('!!!!!');
END IF;
END LOOP;
CLOSE emp_cursor1;
END;
SELECT * FROM copy_emp
SELECT * FROM emp FOR UPDATE
UPDATE emp SET sal
SELECT ename,sal,to_number(EXTRACT(YEAR FROM hiredate)) FROM emp;
ROLLBACK;
oracle 笔记 游标
最新推荐文章于 2021-12-29 15:04:37 发布