Oracle PLSQL游标、游标变量的使用 - 数据库编程

TOP

Oracle PLSQL游标、游标变量的使用(一)

2019-09-17 15:42:21 【大中小】浏览:34次

参考文章：https://www.cnblogs.com/huyong/archive/2011/05/04/2036377.html

在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现

使用有四个步骤：定义、打开、提取、关闭

例子：

09:52:04 SCOTT@std1> DECLARE
09:52:07   2     CURSOR c_cursor 
09:52:07   3     IS SELECT ename, sal 
09:52:07   4     FROM emp 
09:52:07   5     WHERE rownum<11;   
09:52:07   6     v_ename  emp.ename%TYPE;
09:52:07   7     v_sal    emp.ename%TYPE;   
09:52:07   8  BEGIN
09:52:07   9    OPEN c_cursor;
09:52:07  10    FETCH c_cursor INTO v_ename, v_sal;
09:52:07  11    WHILE c_cursor%FOUND LOOP
09:52:07  12       DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
09:52:07  13       FETCH c_cursor INTO v_ename, v_sal;
09:52:07  14    END LOOP;
09:52:07  15    CLOSE c_cursor;
09:52:08  16  END;
09:52:09  17  /
SMITH---800
ALLEN---1600
WARD---1250
JONES---2975
MARTIN---1250
BLAKE---2850
CLARK---2450
SCOTT---3000
TURNER---1500
ADAMS---1100
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00

10:01:43 SCOTT@std1> DECLARE
11:31:04   2    deptrec dept%Rowtype;
11:31:04   3    dept_name dept.dname%TYPE;
11:31:04   4    dept_loc dept.loc%TYPE;
11:31:04   5    CURSOR c1 IS SELECT dname,loc FROM dept WHERE deptno<=30;
11:31:04   6    CURSOR c2(dept_no NUMBER DEFAULT 10) IS SELECT dname,loc FROM dept WHERE deptno <= dept_no;
11:31:04   7    CURSOR c3(dept_no NUMBER DEFAULT 10) IS SELECT * FROM dept WHERE deptno <= dept_no;
11:31:04   8    
11:31:04   9  BEGIN
11:31:04  10   OPEN c1;
11:31:04  11   LOOP 
11:31:04  12     FETCH c1 INTO dept_name,dept_loc;
11:31:04  13     EXIT WHEN c1%NOTFOUND;
11:31:04  14     dbms_output.put_line(dept_name||'---'||dept_loc);
11:31:04  15   END LOOP;
11:31:04  16   CLOSE c1;
11:31:04  17   
11:31:04  18   OPEN c2;
11:31:04  19   LOOP
11:31:04  20     FETCH c2 INTO dept_name,dept_loc;
11:31:04  21     EXIT WHEN c2%NOTFOUND;
11:31:04  22     dbms_output.put_line(dept_name||'---'||dept_loc);
11:31:04  23   END LOOP;
11:31:04  24   CLOSE c2;
11:31:04  25   
11:31:04  26   OPEN c3(dept_no => 20);
11:31:04  27   LOOP
11:31:04  28     FETCH c3 INTO deptrec;
11:31:04  29     EXIT WHEN c3%NOTFOUND;
11:31:04  30     dbms_output.put_line(deptrec.deptno||'---'||deptrec.dname||'---'||deptrec.loc);
11:31:04  31   END LOOP;
11:31:04  32   CLOSE c3;
11:31:04  33  END;
11:31:06  34  /
ACCOUNTING---NEW YORK
RESEARCH---DALLAS
SALES---CHICAGO
ACCOUNTING---NEW YORK
10---ACCOUNTING---NEW YORK
20---RESEARCH---DALLAS

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
11:31:07 SCOTT@std1>

游标属性： Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE； Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反； Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE； Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数例子

15:04:04 SCOTT@std1> set serverout on
15:04:27 SCOTT@std1> DECLARE
15:04:40   2    v_empno emp.empno%TYPE;
15:04:40   3    v_sal emp.sal%TYPE;
15:04:40   4    CURSOR c_cursor IS SELECT empno,sal FROM emp;  
15:04:40   5  BEGIN
15:04:40   6    OPEN c_cursor;
15:04:40   7    LOOP
15:04:40   8      FETCH c_cursor INTO v_empno,v_sal;
15:04:40   9      EXIT WHEN c_cursor%NOTFOUND;
15:04:40  10      IF v_sal<1200 THEN 
15:04:40  11        UPDATE emp SET sal=sal+50 WHERE empno=v_empno;
15:04:41  12        dbms_output.put_line('编码为'||v_empno||'工资已更新！');
15:04:41  13      END IF;
15:04:41  14    END LOOP;
15:04:41  15    CLOSE c_cursor;
15:04:41  16  END;
15:04:42  17  /
编码为7369工资已更新！
编码为7876工资已更新！
编码为7900工资已更新！

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
15:04:43 SCOTT@std1>

15:04:43 SCOTT@std1> DECLARE
15:06:12   2    v_name emp.ename%TYPE;
15:06:12   3    v

首页上一页 1 2 3 4 5 下一页尾页 1/5/5
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】

上一篇：System memory 259522560 must be..	下一篇：数据库练习（学生表）