目录
cursor介绍
什么是cursor
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。
游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
cursor类型
“两类三种”
--静态游标：结果集已经存在（静态定义）的游标，分为隐式和显示游标
- 隐式游标：所有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
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;参考文章
https://www.cnblogs.com/bluedy1229/p/4215787.html
https://blog.csdn.net/mydreamneverstop/article/details/78604033
1268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
