• Oracle cursor


    --游标分2种类型: --静态游标:结果集已经存在(静态定义)的游标,分为隐式和显示游标

    1. 隐式游标:所有DML语句为隐式游标,通过隐式游标属性可以获取SQL语句信息
    2. 显示游标:用户显示声明的游标,即指定结果集,当查询返回结果超过一定行时,就需要一个显示游标

    --REF 游标:动态关联结果集的临时对象

    1. -强类型:带return类型
    2. -弱类型: 不带return类型

    --隐式游标 --在PL/SQL中编写的每条SQL 语句实际上都是隐匿游标。通过在DML操作后使用SQL%ROWCOUNT属性,可以 --知道语句所改变的行数(INSERT ,UPDATE,DELETE)返回理新行数,SELECT 返回查询行数.
    --显示游标 --语法:CURSOR 游标名称 ([参数列表,]) [RETURN 返回值类型] IS 子查询(SELECT _statement) --第一步:声明游标: CURSOR 游标名 IS SELECT 。。使用CURSOR定义 --第二步:打开游标     使用OPEN     OPEN 游标名 --第三步:提取游标     使用FETCH  游标 INTO 变量 --第四步:关闭游标 CLOSE 游标名 --显式游标属性:   %FOUND     找到是否找到数据,有数据TRUE,没有则FALSE   %ISOPEN    判断游标是否打开,打开则返回TRUE,没有打开则返回FALSE   %NOTFOUND  返回FETCH ...INTO...是否有数据如果没有返回TRUN,有则为FALSE   %ROWCOUNT  返回执行FETCH 语句所返回的行数,初始为0,每执行一行则%ROWCOUNT增加1 --隐式游标属性: SQL%FOUND     找到是否找到数据,有数据TRUE,没有则FALSE   SQL%ISOPEN    判断游标是否打开,打开则返回TRUE,没有打开则返回FALSE   SQL%NOTFOUND  返回FETCH ...INTO...是否有数据如果没有返回TRUN,有则为FALSE   SQL%ROWCOUNT  返回执行FETCH 语句所返回的行数,初始为0,每执行一行则%ROWCOUNT增加1

    隐式游标:

    --验证SQL%ROWCOUNT

    DECLARE
      v_count         NUMBER;
    BEGIN
      SELECT COUNT(*) INTO v_count FROM dept; --只返回一行结果
      dbms_output.put_line('SQL%ROWCOUNT= '|| SQL%ROWCOUNT);
    END;

    结果:SQL%ROWCOUNT= 1

    --验证SLQ%ROWCOUNT并返回行数

    DECLARE
    BEGIN
       INSERT INTO dept(deptno,dname,loc)VALUES(90,'qqqq','北京');
         dbms_output.put_line('SQL%ROWCOUNT= '|| SQL%ROWCOUNT);
    END;

    结果:SQL%ROWCOUNT= 1

    --单行隐式游标

    复制代码
    复制代码
    DECLARE
       v_empRow         emp%ROWTYPE;
    BEGIN
      SELECT * INTO v_empRow FROM emp WHERE empno=7369;
      IF SQL%FOUND THEN   --发现数据
        dbms_output.put_line('员工姓名: '|| v_empRow.ename||'职位: '||v_empRow.job);
        END IF;
    END;
    复制代码
    复制代码

    结果:员工姓名: SMITH职位: CLERK

    --多行隐式游标

    复制代码
    复制代码
    DECLARE
    BEGIN
      UPDATE EMP SET SAL = SAL * 1.2;
      IF SQL%FOUND THEN
        --发现数据
        DBMS_OUTPUT.PUT_LINE('更新行数' || SQL%ROWCOUNT);
      ELSE
        DBMS_OUTPUT.PUT_LINE('更新行数' || SQL%ROWCOUNT);
      END IF;
    END;
    复制代码
    复制代码

    结果:更新行数14

    --显示游标 --定义游标例1:

    复制代码
    复制代码
    DECLARE
     CURSOR emp_cur IS
     SELECT empno,ename FROM emp;                    --定义游标
     v_id     emp.empno%TYPE;              --定义变量ID
     v_name    emp.ename%TYPE;
    BEGIN
      OPEN emp_cur ;                          --打开游标
      FETCH emp_cur INTO v_id,v_name;    ---提示取游标
      LOOP
        EXIT WHEN emp_cur%notFOUND;                     --判断是否还有数据
        dbms_output.put_line('员工编号'||v_id||',员工姓名:'||v_name);
        FETCH emp_cur INTO v_id,v_name;    ---提示取游标
      END LOOP;
      CLOSE emp_cur;                  --关闭游标
    END;
    复制代码
    复制代码

    结果:

    复制代码
    复制代码
    员工编号7369,员工姓名:SMITH
    员工编号7499,员工姓名:ALLEN
    员工编号7521,员工姓名:WARD
    员工编号7566,员工姓名:JONES
    员工编号7654,员工姓名:MARTIN
    员工编号7698,员工姓名:BLAKE
    员工编号7782,员工姓名:CLARK
    员工编号7788,员工姓名:SCOTT
    员工编号7839,员工姓名:KING
    员工编号7844,员工姓名:TURNER
    员工编号7876,员工姓名:ADAMS
    员工编号7900,员工姓名:JAMES
    员工编号7902,员工姓名:FORD
    员工编号7934,员工姓名:MILLER
    复制代码
    复制代码

    --定义游标例2:

    复制代码
    复制代码
    DECLARE
      V_NAME  VARCHAR2(50); --定义变量姓名
      V_DNAME VARCHAR2(50); --定义变量部门名称
      CURSOR CUR_E IS --定义游标
        SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
    BEGIN
      OPEN CUR_E; --打开游标
      LOOP
        --使用循环来读取游标
        FETCH CUR_E
          INTO V_NAME, V_DNAME; --提取游标
        EXIT WHEN CUR_E%NOTFOUND; --判断游标是否还有内容
        DBMS_OUTPUT.PUT_LINE(CUR_E%ROWCOUNT || '  员工姓名:' || V_NAME || ' 部门名称:' ||
                             V_DNAME);                            --输出内容
      
      END LOOP;
      CLOSE CUR_E; --关闭游标
    END;
    复制代码
    复制代码

    结果:

    复制代码
    复制代码
    程序结果:
    1  员工姓名:SMITH 部门名称:RESEARCH
    2  员工姓名:ALLEN 部门名称:SALES
    3  员工姓名:WARD 部门名称:SALES
    4  员工姓名:JONES 部门名称:RESEARCH
    5  员工姓名:MARTIN 部门名称:SALES
    6  员工姓名:BLAKE 部门名称:SALES
    7  员工姓名:CLARK 部门名称:ACCOUNTING
    8  员工姓名:SCOTT 部门名称:RESEARCH
    9  员工姓名:KING 部门名称:ACCOUNTING
    10  员工姓名:TURNER 部门名称:SALES
    11  员工姓名:ADAMS 部门名称:RESEARCH
    12  员工姓名:JAMES 部门名称:SALES
    13  员工姓名:FORD 部门名称:RESEARCH
    14  员工姓名:MILLER 部门名称:ACCOUNTING
    复制代码
    复制代码

    另一种指定变量类型:

    复制代码
    复制代码
    DECLARE
      V_EMPNAME EMP.ENAME%TYPE;
      V_DNAME   DEPT.DNAME%TYPE;
      CURSOR EMP_CUR IS
        SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;
    BEGIN
      OPEN EMP_CUR;
      LOOP
        FETCH EMP_CUR
          INTO V_EMPNAME, V_DNAME;
        EXIT WHEN EMP_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('员工姓名 :' || V_EMPNAME || ',部门名称 :' || V_DNAME);
      END LOOP;
      CLOSE EMP_CUR;
    END;
    复制代码
    复制代码

    结果同上

    --定义游标例3:

    复制代码
    复制代码
    DECLARE
      CURSOR CUR_EMP IS
        SELECT * FROM EMP;
      V_EMPROW EMP%ROWTYPE;
    BEGIN
      IF CUR_EMP%ISOPEN THEN
        NULL;
      ELSE
        OPEN CUR_EMP;
      END IF;
      FETCH CUR_EMP
        INTO V_EMPROW;
      WHILE CUR_EMP%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE('员工姓名: ' || V_EMPROW.ENAME || ',职位: ' ||
                             V_EMPROW.JOB || ' ,工资' || V_EMPROW.SAL);
        FETCH CUR_EMP
          INTO V_EMPROW;
      END LOOP;
      CLOSE CUR_EMP;
    END;
    复制代码
    复制代码

    结果:

    复制代码
    复制代码
    员工姓名: SMITH,职位: CLERK ,工资800
    员工姓名: ALLEN,职位: SALESMAN ,工资1600
    员工姓名: WARD,职位: SALESMAN ,工资1250
    员工姓名: JONES,职位: MANAGER ,工资2975
    员工姓名: MARTIN,职位: SALESMAN ,工资1250
    员工姓名: BLAKE,职位: MANAGER ,工资2850
    员工姓名: CLARK,职位: MANAGER ,工资2450
    员工姓名: SCOTT,职位: ANALYST ,工资3000
    员工姓名: KING,职位: PRESIDENT ,工资5000
    员工姓名: TURNER,职位: SALESMAN ,工资1500
    员工姓名: ADAMS,职位: CLERK ,工资1100
    员工姓名: JAMES,职位: CLERK ,工资950
    员工姓名: FORD,职位: ANALYST ,工资3000
    员工姓名: MILLER,职位: CLERK ,工资1300
    复制代码
    复制代码

    --使用FOR循环

    复制代码
    复制代码
    DECLARE 
    CURSOR cur_emp IS SELECT * FROM emp;
    BEGIN
      FOR emp_row IN cur_emp LOOP
        DBMS_OUTPUT.PUT_LINE('员工姓名: ' || emp_row.ENAME || ',职位: ' ||
                             emp_row.JOB || ' ,工资' || emp_row.SAL);
         END LOOP;
    END;
    复制代码
    复制代码

    结果同上

    --使用FOR循环操作游标不仅代码简单,而且可以将游标的状态交给系统去完成,尽量使用FOR循环为主

    --定义游标例4:使用游标UPDATE数据 --公司上市,决定给员工涨工资,入职年限超过1年加100,1000元封顶 --第一种 直接将计算的结果进行判断

    复制代码
    复制代码
    DECLARE
      V_ID       EMP.EMPNO%TYPE;
      V_HIREDATE EMP.HIREDATE%TYPE;
      CURSOR EMP_CUR IS
        SELECT EMPNO, HIREDATE FROM EMP;
    BEGIN
      OPEN EMP_CUR;
      LOOP
        FETCH EMP_CUR
          INTO V_ID, V_HIREDATE;
        EXIT WHEN EMP_CUR%NOTFOUND;
        IF (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100 < 1000 THEN
          UPDATE EMP
             SET SAL = SAL +
                       (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100
           WHERE EMPNO = V_ID;
          DBMS_OUTPUT.PUT_LINE('工资增加成功');
          COMMIT;
        ELSE
          UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = V_ID;
          DBMS_OUTPUT.PUT_LINE('工资增加成功');
          COMMIT;
        END IF;
      END LOOP;
      CLOSE EMP_CUR;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('数据异常');
        ROLLBACK;
    END;
    复制代码
    复制代码

    --第二种通过一个变量判断

    复制代码
    复制代码
    DECLARE
      V_ID       EMP.EMPNO%TYPE; --定义员工编号ID
      V_HIREDATE EMP.HIREDATE%TYPE; --定义员工入职日期变量
      V_SAL      EMP.SAL%TYPE; --定义计算每个员工要涨工资的总数变量
      CURSOR CUR_EMP IS
        SELECT EMPNO, HIREDATE --定义游标查询员工ID和入职日期
          FROM EMP;
    BEGIN
      IF CUR_EMP%ISOPEN THEN
        --判断游标是否打开
        NULL; --打开了就什么也不做
      ELSE
        OPEN CUR_EMP; --没有打开就打开游标
      END IF;
      LOOP
        FETCH CUR_EMP
          INTO V_ID, V_HIREDATE;
        EXIT WHEN CUR_EMP%NOTFOUND;
        V_SAL := (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100;
        IF V_SAL < 1000 THEN
          --判断是否小于1000
          UPDATE EMP SET SAL = SAL + V_SAL WHERE EMPNO = V_ID;
          COMMIT;
        ELSE
          --大于1000
          UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = V_ID;
          COMMIT;
        END IF;
      END LOOP;
      CLOSE CUR_EMP; --关闭游标
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('数据异常');
        ROLLBACK; --出现异常 就回滚
    END;
    复制代码
    复制代码

    --定义游标例5 --在动态SELECT中使用游标

    复制代码
    复制代码
    DECLARE
      V_LOWSAL EMP.SAL%TYPE := &LOWSAL;
      V_HISAL  EMP.SAL%TYPE := &HISSAL;
      CURSOR CUR_EMP IS
        SELECT * FROM EMP WHERE SAL BETWEEN V_LOWSAL AND V_HISAL;
    BEGIN
      FOR EMP_ROW IN CUR_EMP LOOP
        DBMS_OUTPUT.PUT_LINE('员工姓名: ' || EMP_ROW.ENAME || ',职位: ' ||
                             EMP_ROW.JOB || ' ,工资' || EMP_ROW.SAL);
      END LOOP;
    END;
    复制代码
    复制代码

    --REF动态游标 TYPE 类型名 IS REF CURSOR [RETURN]数据类型 游标名 类型名       OPEN 游标名 FOR 查询语句 --强类型:带RETURN

    复制代码
    复制代码
    DECLARE
      TYPE REF_EMP IS REF CURSOR RETURN EMP%ROWTYPE; --定义一个REF动态游标,并返回类型
      CUR_EMP REF_EMP; --定义一个变量类型是上面的REF动态游标也称游标变量
      V_EMP   EMP%ROWTYPE; --定义一个变量,类型和REF游标返回类型相同,行类型
    BEGIN
      OPEN CUR_EMP FOR
        SELECT * FROM EMP; --打开游标,并关联查询语句
      LOOP
        FETCH CUR_EMP
          INTO V_EMP; --提取游标数据
        EXIT WHEN CUR_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT||'  员工编号:' || V_EMP.EMPNO || ' 员工姓名:' || V_EMP.ENAME);
      END LOOP;
      CLOSE CUR_EMP;
    END;
    复制代码
    复制代码

    --弱类型:不带RETURN

    复制代码
    复制代码
    DECLARE
      TYPE REF_EMP IS REF CURSOR; --定义一个REF动态游标,并返回类型
      CUR_EMP REF_EMP; --定义一个变量类型是上面的REF动态游标也称游标变量
      V_EMP   EMP%ROWTYPE; --定义一个变量,类型和REF游标返回类型相同,行类型
      V_DEPT  DEPT%ROWTYPE; --定义一个变量,类型和REF游标返回类型相同,行类型
    BEGIN
      --员工表
      OPEN CUR_EMP FOR
        SELECT * FROM EMP; --打开游标,并关联查询语句
      LOOP
        FETCH CUR_EMP
          INTO V_EMP; --提取游标数据
        EXIT WHEN CUR_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '  员工编号:' || V_EMP.EMPNO ||
                             ' 员工姓名:' || V_EMP.ENAME);
      END LOOP;
      CLOSE CUR_EMP;
      ------------下面是部门表  
      OPEN CUR_EMP FOR
        SELECT * FROM DEPT; --打开游标,并关联查询语句
      LOOP
        FETCH CUR_EMP
          INTO V_DEPT; --提取游标数据
        EXIT WHEN CUR_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '  部门编号:' || V_DEPT.DEPTNO ||
                             ' 部门名称:' || V_DEPT.DNAME);
      END LOOP;
      CLOSE CUR_EMP;
    END;
    复制代码
    复制代码
    在Oracle9i之后为了方便用户使用弱类型游标变量,可以使用  
    SYS_REFCURSOR 来替代  TYPE REF_EMP IS REF CURSOR
    上面的声明可以换为:
      CUR_EMP  SYS_REFCURSOR; --定义一个变量类型是上面的REF动态游标也称游标变量
      V_EMP   EMP%ROWTYPE; --定义一个变量,类型和REF游标返回类型相同,行类型
      V_DEPT  DEPT%ROWTYPE; --定义一个变量,类型和REF游标返回类型相同,行类型

    --根据用户输入,来输出内容

    复制代码
    复制代码
    DECLARE
      --TYPE REFC_T IS REF CURSOR;
      REFC    SYS_REFCURSOR;
      V_ID    NUMBER;
      V_NAME  VARCHAR2(50);
      V_INPUT VARCHAR(1) := UPPER(SUBSTR('&input', 1, 1));
    BEGIN
      IF V_INPUT = 'E' THEN
        OPEN REFC FOR
          SELECT EMPNO, ENAME FROM EMP;
        DBMS_OUTPUT.PUT_LINE('=====员工表信息======');
      ELSIF V_INPUT = 'D' THEN
        OPEN REFC FOR
          SELECT DEPTNO, DNAME FROM DEPT;
        DBMS_OUTPUT.PUT_LINE('=====部门表信息======');
      ELSE
        DBMS_OUTPUT.PUT_LINE('=====员工表信息(E)或者部门表信息(D)=======');
        RETURN;
      END IF;
      FETCH REFC
        INTO V_ID, V_NAME;
      WHILE REFC%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(REFC%ROWCOUNT || '# ' || V_ID || '  ' || V_NAME);
        FETCH REFC
          INTO V_ID, V_NAME;
      END LOOP;
      CLOSE REFC;
    END;
    复制代码
    复制代码
  • 相关阅读:
    4-8(十五)性能测试流程
    数据库面试题(一)多表查询
    4-8(十四) jmeter 性能分析从哪几个方面
    4-8(十二)Jmeter+Ant+Jenkins定时构建
    4-8(十一)Jmeter自动化集成工具Ant的安装
    4-8(十)Jmeter 分布式测试
    4-8(九)Jmeter性能测试之阶梯式场景(负载测试)、波浪式场景(压力测试)
    4-8(八)Jmeter性能测试插件jpgc的安装
    4-8(六)Jmeter 脚本录制后调优
    6分钟演示,15种排序算法(视频)
  • 原文地址:https://www.cnblogs.com/dianli_jingjing/p/8422420.html
Copyright © 2020-2023  润新知