oracle数据局的游标,Oracle数据库---游标

--查询所有员工的员工号、姓名和职位的信息。

DECLARE

--定义游标

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

v_job emp.job%TYPE;

BEGIN

--打开游标，执行查询

OPEN emp_cursor;

--提取数据

LOOP

FETCH emp_cursor INTO v_empno,v_ename,v_job;

DBMS_OUTPUT.PUT_LINE('员工号：'||v_empno||'，姓名：'||v_ename||',职位：'||v_job);

--什么时候能够退出循环？

--%FOUND,%NOTFOUND

EXIT WHEN emp_cursor%NOTFOUND;

END LOOP;

--关闭游标

CLOSE emp_cursor;

END;

--查询所有员工的员工号、姓名和职位的信息。

DECLARE

--定义游标

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

v_job emp.job%TYPE;

BEGIN

--打开游标，执行查询

--OPEN emp_cursor;

--检测游标是否打开

IF emp_cursor%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('游标已经打开');

ELSE

DBMS_OUTPUT.PUT_LINE('游标没有打开');

END IF;

END;

--游标FOR循环

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;

BEGIN

FOR emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.empno||',姓名：'||emp_record.ename||',职位：'||emp_record.job);

END LOOP;

END;

--游标FOR循环中引用子查询

BEGIN

FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP

DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.empno||',姓名：'||emp_record.ename||',职位：'||emp_record.job);

END LOOP;

END;

--参数游标

DECLARE

CURSOR emp_cursor(dno NUMBER) IS SELECT empno,ename,job FROM emp WHERE deptno = dno;

BEGIN

FOR emp_record IN emp_cursor(&no) LOOP

DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.empno||',姓名：'||emp_record.ename||',职位：'||emp_record.job);

END LOOP;

END;

--根据用户输入的员工号，更新指定员工的工资，比如工资涨100

--隐式游标

BEGIN

UPDATE empnew SET sal = sal + 100 WHERE empno = &no;

IF SQL%FOUND THEN

DBMS_OUTPUT.put_line('成功修改员工的工资');

COMMIT;

ELSE

DBMS_OUTPUT.put_line('修改员工工资失败!');

ROLLBACK;

END IF;

END;

SELECT * FROM empnew;

--按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。

--1：用显示游标的常规方式实现业务需求

DECLARE

--定义游标

CURSOR empnew_cursor IS SELECT empno,job FROM empnew;

v_empno empnew.empno%TYPE;

v_job empnew.job%TYPE;

BEGIN

--打开游标

OPEN empnew_cursor;

--提取数据

LOOP

FETCH empnew_cursor INTO v_empno,v_job;

IF v_job='PRESIDENT' THEN

UPDATE empnew SET sal = sal + 1000 WHERE empno = v_empno;

ELSIF v_job='MANAGER' THEN

UPDATE empnew SET sal = sal + 500 WHERE empno = v_empno;

ELSE

UPDATE empnew SET sal = sal + 300 WHERE empno = v_empno;

END IF;

EXIT WHEN empnew_cursor%NOTFOUND;

END LOOP;

COMMIT;

--关闭游标

CLOSE empnew_cursor;

END;

--2：用游标FOR循环的方式实现业务需求

DECLARE

--定义游标

CURSOR empnew_cursor IS SELECT empno,job FROM empnew;

BEGIN

FOR empnew_record IN empnew_cursor LOOP

DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);

IF empnew_record.job = 'PRESIDENT' THEN

UPDATE empnew SET sal = sal + 1000 WHERE empno = empnew_record.empno;

ELSIF empnew_record.job = 'MANAGER' THEN

UPDATE empnew SET sal = sal + 500 WHERE empno = empnew_record.empno;

ELSE

UPDATE empnew SET sal = sal + 300 WHERE empno = empnew_record.empno;

END IF;

END LOOP;

--COMMIT;

END;

select * from empnew for update;

--3：使用游标添加或删除数据时，定义游标时利用FOR UPDATE 子句可以将游标提取出来的数据进行行级锁定

DECLARE

--定义游标

CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE;

BEGIN

FOR empnew_record IN empnew_cursor LOOP

DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);

IF empnew_record.job = 'PRESIDENT' THEN

UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;

ELSIF empnew_record.job = 'MANAGER' THEN

UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;

ELSE

UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;

END IF;

END LOOP;

COMMIT;

END;

SELECT * FROM empnew;

--FOR UPDATE NOWAIT 不等待锁，如发现所操作的数据行已经锁定，将不会等待，立即返回

DECLARE

--定义游标

CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE NOWAIT;

BEGIN

FOR empnew_record IN empnew_cursor LOOP

DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);

IF empnew_record.job = 'PRESIDENT' THEN

UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;

ELSIF empnew_record.job = 'MANAGER' THEN

UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;

ELSE

UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;

END IF;

END LOOP;

COMMIT;

END;

SELECT * FROM empnew;

--使用OF子句在特定表上加行共享锁

DECLARE

CURSOR empnew_cursor IS

SELECT d.dname dname,e.ename ename

FROM empnew e join dept d on e.deptno = d.deptno

WHERE e.deptno = &deptno

FOR UPDATE OF e.deptno;

BEGIN

FOR empnew_record IN empnew_cursor LOOP

DBMS_OUTPUT.PUT_LINE('部门名称：'||empnew_record.dname||'员工名：'||empnew_record.ename);

DELETE FROM empnew WHERE CURRENT OF empnew_cursor;

END LOOP;

COMMIT;

END;

SELECT * FROM empnew where deptno = 20;

标签：job,数据库,游标,empno,cursor,record,empnew,emp,Oracle

来源： https://www.cnblogs.com/xiaomifeng1010/p/11117347.html