Oracle cursor

首页 > 代码库 > Oracle cursor

2024-10-31 08:41:39 205人阅读

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

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

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

-强类型：带return类型
-弱类型: 不带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并返回行数

DECLAREBEGIN   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

--多行隐式游标

DECLAREBEGIN  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 部门名称:RESEARCH2  员工姓名:ALLEN 部门名称:SALES3  员工姓名:WARD 部门名称:SALES4  员工姓名:JONES 部门名称:RESEARCH5  员工姓名:MARTIN 部门名称:SALES6  员工姓名:BLAKE 部门名称:SALES7  员工姓名:CLARK 部门名称:ACCOUNTING8  员工姓名:SCOTT 部门名称:RESEARCH9  员工姓名:KING 部门名称:ACCOUNTING10  员工姓名:TURNER 部门名称:SALES11  员工姓名:ADAMS 部门名称:RESEARCH12  员工姓名:JAMES 部门名称:SALES13  员工姓名:FORD 部门名称:RESEARCH14  员工姓名: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;

Oracle cursor

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > Oracle cursor

Oracle cursor

看完仍有疑问？有类似问题直接问程序猿