• 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;


  • 相关阅读:
    适者生存还是强者生存
    写给十岁的清为
    毕业后的十年
    Python3 字符编码
    线段树模板
    F
    E
    D
    C
    B
  • 原文地址:https://www.cnblogs.com/xiaomifeng1010/p/11117347.html
Copyright © 2020-2023  润新知