一：Oracle存储过程基础
1.存储过程异常介绍
预定义异常，非预定义异常，自定义异常
（1）预定义异常
oracle提供的一些已经定义好的异常。共有25种，常用的预定义异常有：
too many rows：结果集超过一行，即返回多行数据；
value error：赋值时，变量的长度不足以容纳实际数据长度；
zero_divide：除数为0；
dup_val_on_index：唯一索引对应的列上有重复值；
collection_is_null：集合元素未初始化；
invalid_number：内嵌的SQL语句不能将字符串转换为数字；
no_data_found：使用 select into未返回行，或应用索引表未初始化的元素时；
case_not_found：case中若未包含相应的when，并且没有设置else时；
access_into_null：未定义对象；
subscript_beyond_count：元素下标超过嵌套表或varray的最大值；
实例：
create or replace procedure test_select_procedure AS v_ds ly_ds%rowtype; BEGIN select * into v_ds from ly_ds; EXCEPTION -- 由EXCEPTION开始 WHEN TOO_MANY_ROWS THEN --如果符合这个异常，则进入下边的执行 DBMS_OUTPUT.PUT_LINE('返回结果超过一条'); END;
（2）非预定义异常
有些异常只有错误编号和相关的错误描述，并没有名称，为了解决这一问题，oracle允许开发人员为这样的异常添加一个名称，使他们能够被异常处理模块捕获到。
比如像下边的这个异常，ORA-12899，就是这种情况：
ORA-12899: 列 "ZNXD_GATEWAY"."LY_DS"."CREATE_TIME" 的值太大 (实际值: 149, 最大值: 50)
它没有对应的异常名称，不属于预定义异常。
解决办法就是，只需要将它的错误号码，与异常名称关联一下即可，例子写法如下：
create or replace procedure test_add_procedure (id varchar,createtime varchar,name varchar, age varchar,sex varchar) AS my_12899_exp exception; --定义一个异常，取名为my_12899_exp --编译当前异常名，并与错误号对应 pragma exception_init(my_12899_exp,-12899); BEGIN insert into ly_ds values(id,createtime,name,age,sex); exception when dup_val_on_index then --不符合当前异常则继续匹配边的 dbms_output.put_line('主键冲突'); when my_12899_exp then --符合该异常则进入执行 dbms_output.put_line('内容超出存储范围'); end;
执行如下，当把第二个字段的值设的超出存储范围后，会引发异常：
SET SERVEROUTPUT ON; BEGIN test_add_procedure('11','2018-09-081辅导费地方发地方的分担分担放到发地方地方','刘七','30','男'); END;
输出结果如下：
匿名块已完成
内容超出存储范围
（3）自定义异常
如果有些并不属于系统错误，可能是因为某段业务逻辑，只是想让它抛出异常，像这种主动抛出异常的情况，就需要自定义异常了。
异常都会有错误名称和错误号，自定义异常的时候，可以使用-20999~-20000 这个范围的数字作为错误号，不会引起冲突的。
如下，数据库的内容：
想要写一个，根据性别，匹配条数，如果大于一条，则抛出异常，这样的存储过程，如下：
create or replace procedure test_select2_procedure (sex varchar) AS countNum number(10); my_range_exp exception; --声明一个异常，取名为my_range_exp pragma exception_init(my_range_exp,-20001); --编译异常 BEGIN select count(*) into countNum from ly_ds where LY_NB=sex; if(countNum >1) then raise my_range_exp; --使用raise抛出异常 end if; dbms_output.put_line(countNum); exception when my_range_exp then dbms_output.put_line('查询条数大于1，主动抛出异常'); END;
当分别传入男 、女 两种参数时，结果如下：
执行男 ：
SET SERVEROUTPUT ON; BEGIN test_select2_procedure('男'); END;
结果：
执行女：
SET SERVEROUTPUT ON; BEGIN test_select2_procedure('女'); END;
结果：
（4）实例
如果一个系统错误没有在标准包中定义，则需要在说明部分定义，语法如下：
错误名 EXCEPTION;
定义后使用PRAGMA EXCEPTION_INIT来将一个定义的错误同一个特别的Oracle错误代码相关联，就可以同系统预定义的错误一样使用了。语法如下：
PRAGMA EXCEPTION_INIT(错误名，- 错误代码)；
1.定义新的系统错误类型
SET SERVEROUTPUT ON DECLARE V_ENAME VARCHAR2(10); NULL_INSERT_ERROR EXCEPTION; PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400); BEGIN INSERT INTO EMP(EMPNO) VALUES(NULL); EXCEPTION WHEN NULL_INSERT_ERROR THEN DBMS_OUTPUT.PUT_LINE('无法插入NULL值！'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其他系统错误！'); END;
执行结果
说明：
NULL_INSERT_ERROR是自定义异常，同系统错误1400相关联。
2.自定义异常
程序设计者可以利用引发异常的机制来进行程序设计，自己定义异常类型。可以在声明部分定义新的异常类型，定义的语法是：
错误名 EXCEPTION;
用户定义的错误不能由系统来触发，必须由程序显式地触发，触发的语法是：
RAISE 错误名；
RAISE也可以用来引发模拟系统错误，比如，RAISE ZERO_DIVIDE将引发模拟的除零错误。
使用RAISE_APPLICATION_ERROR函数也可以引发异常。该函数要传递两个参数，第一个是用户自定义的错误编号，第二个参数是用户自定义的错误信息。使用该函数引发的异常的编号应该在20 000和20 999之间选择。
自定义异常处理错误的方式同前。
示例1：插入新雇员，限定插入雇员的编号在7000～8000之间。
SET SERVEROUTPUT ON DECLARE new_no NUMBER(10); new_excp1 EXCEPTION; new_excp2 EXCEPTION; BEGIN new_no:=6789; INSERT INTO emp(empno,ename) VALUES(new_no, '小郑'); IF new_no<7000 THEN RAISE new_excp1; END IF; IF new_no>8000 THEN RAISE new_excp2; END IF; COMMIT; EXCEPTION WHEN new_excp1 THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限！'); WHEN new_excp2 THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限！'); END;
执行结果：
雇员编号小于7000的下限！
PL/SQL 过程已成功完成。
说明：
在此例中，自定义了两个异常：new_excp1和new_excp2，分别代表编号小于7000和编号大于8000的错误。在程序中通过判断编号大小，产生对应的异常，并在异常处理部分回退插入操作，然后显示相应的错误信息。
实例2：使用RAISE_APPLICATION_ERROR函数引发系统异常。
SET SERVEROUTPUT ON DECLARE New_no NUMBER(10); BEGIN New_no:=6789; INSERT INTO emp(empno,ename) VALUES(new_no, 'JAMES'); IF new_no<7000 THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, '编号小于7000的下限！'); END IF; IF new_no>8000 THEN ROLLBACK; RAISE_APPLICATION_ERROR (-20002, '编号大于8000的下限！'); END IF; END;
执行结果：
说明：在本训练中，使用RAISE_APPLICATION_ERROR引发自定义异常，并以系统错误的方式进行显示。错误编号为20001和20002。
注意：同上一个训练比较，此种方法不需要事先定义异常，可直接引发。
示例3：将雇员从一个表复制到另一个表。
步骤1：创建一个结构同EMP表一样的新表EMP1：
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;
步骤2：通过指定雇员编号，将雇员由EMP表移动到EMP1表
SET SERVEROUTPUT ON DECLARE v_empno NUMBER(5):=7788; emp_rec emp%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM emp WHERE empno=v_empno; DELETE FROM emp WHERE empno=v_empno; INSERT INTO emp1 VALUES emp_rec; IF SQL%FOUND THEN COMMIT; DBMS_OUTPUT.PUT_LINE('雇员复制成功！'); ELSE ROLLBACK; DBMS_OUTPUT.PUT_LINE('雇员复制失败！'); END IF; END;
执行结果为：
雇员复制成功！
PL/SQL 过程已成功完成。
步骤2：显示复制结果：
SELECT empno,ename,job FROM emp1;
执行结果为：
说明：
emp_rec变量是根据emp表定义的记录变量，SELECT...INTO...语句将整个记录传给该变量。INSERT语句将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。试修改雇员编号为7902，重新执行以上程序。
示例4：输出雇员工资，雇员工资用不同高度的*表示。
SET SERVEROUTPUT ON BEGIN FOR re IN (SELECT ename,sal FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*')); END LOOP; END;
执行结果：
说明：
第一个rpad函数产生对齐效果，第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简略游标循环形式。
实例5：已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。
输入并执行如下程序：
SET SERVEROUTPUT ON DECLARE v_deptno number(8); v_count number(3); v_sumsal number(6); v_dname varchar2(15); v_manager varchar2(15); CURSOR list_cursor IS SELECT deptno,count(*),sum(sal) FROM emp group by deptno; BEGIN OPEN list_cursor; DBMS_OUTPUT.PUT_LINE('----------- 部 门 统 计 表 -----------'); DBMS_OUTPUT.PUT_LINE('部门名称 总人数 总工资 部门经理'); FETCH list_cursor INTO v_deptno,v_count,v_sumsal; WHILE list_cursor%found LOOP SELECT dname INTO v_dname FROM dept WHERE deptno=v_deptno; SELECT ename INTO v_manager FROM emp WHERE deptno=v_deptno and job='MANAGER'; DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8) ||rpad(to_char(v_sumsal),9)||v_manager); FETCH list_cursor INTO v_deptno,v_count,v_sumsal; END LOOP; DBMS_OUTPUT.PUT_LINE('--------------------------------------'); CLOSE list_cursor; END;
执行结果：
说明：
游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。
示例6：为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800元，显示增加工资的人数和余额。
SET SERVEROUTPUT ON DECLARE V_NAME CHAR(10); V_EMPNO NUMBER(5); V_SAL NUMBER(8); V_SAL1 NUMBER(8); V_TOTAL NUMBER(8) := 800; --增加工资的总额 V_NUM NUMBER(5):=0; --增加工资的人数 CURSOR emp_cursor IS SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC; BEGIN OPEN emp_cursor; DBMS_OUTPUT.PUT_LINE('姓名 原工资 新工资'); DBMS_OUTPUT.PUT_LINE('---------------------------'); LOOP FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL; EXIT WHEN emp_cursor%NOTFOUND; V_SAL1:= V_SAL*0.1; IF V_TOTAL>V_SAL1 THEN V_TOTAL := V_TOTAL - V_SAL1; V_NUM:=V_NUM+1; DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')|| TO_CHAR(V_SAL+V_SAL1,'99999')); UPDATE EMP SET SAL=SAL+V_SAL1 WHERE EMPNO=V_EMPNO; ELSE DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999')); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('---------------------------'); DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：'||V_TOTAL); CLOSE emp_cursor; COMMIT; END;
执行结果：
2.存储过程结构
（1）基本结构
过程声明，执行过程部分，存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性就写上异常处理）
（2）无参存储过程
CREATE OR REPLACE PROCEDURE demo AS/IS --demo存储过程名称 变量2 DATE; 变量3 NUMBER; BEGIN --要处理的业务逻辑 EXCEPTION --存储过程异常 END;
（3）带参存储过程
a.带参的存储过程
CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE) AS/IS name student.name%TYPE; age number :=20; BEGIN --业务处理..... END;
上面脚本中：
第一行：param1是参数，类型和student表中id字段的类型一致；
第三行：声明变量name，类型和student表中name字段类型一致；
第四行：声明变量age，数字类型，初始化为20；
b.带参数的存储过程并且赋值
CREATE OR REPLACE PROCEDURE 存储过程名称( --in表示输出参数，是参数的默认模式 s_no in varchar, --out表示返回值参数，类型是Oracle任意合法类型 --OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程 --IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去 s_name out varchar, s_age number) AS total NUMBER := 0; BEGIN --查询语句，把参数s_age作为过滤条件，INTO关键字，把查到的结果赋给total变量。 SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age; --输出查询结果，在数据库中“||”用来连接字符串 dbms_output.put_line('符合该年龄的学生有'||total||'人'); --做异常处理 EXCEPTION WHEN too_many_rows THEN DBMS_OUTPUT.PUT_LINE('返回值多于1行'); END;
3.存储过程语法
（1）运算符
这里s，m，n是变量，类型是number
|
分类 |
运算符 |
含义 |
示例表达式 |
|
算术运算符 |
+ |
加 |
s := 2 + 2; |
|
- |
减 |
s := 3 – 1; |
|
|
* |
乘 |
s := 2 * 3; |
|
|
/ |
除 |
s := 6 / 2; |
|
|
mod(,) |
取模，取余 |
m : = mod(3,2) |
|
|
** |
乘方 |
10**2 =100 |
|
|
关系运算符 |
= |
等于 |
s = 2 |
|
<>或!=或~= |
不等于 |
s != 2 |
|
|
< |
小于 |
s < 3 |
|
|
> |
大于 |
s > 0 |
|
|
<= |
小于等于 |
s <= 9 |
|
|
>= |
大于等于 |
s >= 1 |
|
|
比较运算符 |
LIKE |
满足匹配为true |
‘li’ like ‘%i’返回true |
|
BETWEEN |
是否处于一个范围中 |
2 between 1 and 3 返回true |
|
|
IN |
是否处于一个集合中 |
‘x’ in (‘x’,’y’) 返回true |
|
|
IS NULL |
判断变量是否为空 |
若：n:=3,n is null,返回false |
|
|
逻辑运算符 |
AND |
逻辑与 |
s=3 and c is null |
|
OR |
逻辑或 |
s=3 or c is null |
|
|
NOT |
逻辑非 |
not c is null |
|
|
其他 |
:= |
赋值 |
s := 0; |
|
.. |
范围 |
1..9，即1至9范围 |
|
|
|| |
字符串连接 |
‘hello’||’world’ |
（2）SELECT INTO STATEMENT语句
该语句将select到的结果赋值给一个或多个变量：
CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS --DEMO_CDD1存储过程名称 s_name VARCHAR2; --学生名称 s_age NUMBER; --学生年龄 s_address VARCHAR2; --学生籍贯 BEGIN --给单个变量赋值 SELECT student_address INTO s_address FROM student where student_grade=100; --给多个变量赋值 SELECT student_name,student_age INTO s_name,s_age FROM student where student_grade=100; --输出成绩为100分的那个学生信息 dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address); END;
存储过程名称：DEMO_CDD1, student是学生表，要求查出成绩为100分的那个学生的姓名，年龄，籍贯。
（3）选择语句
a. if..end if
IF s_sex=1 THEN dbms_output.put_line('这个学生是男生'); END IF
b. if ..else..end if
IF s_sex=1 THEN dbms_output.put_line('这个学生是男生'); ELSE dbms_output.put_line('这个学生是女生'); END IF;
（4）循环语句
a. loop语句
LOOP语句会先执行一次循环体，然后再判断EXIT WHEN关键字后面的条件表达式是ture还是false，为true的时候退出循环体，否则程序将再次执行循环体。
语法：
LOOP A; EXIT WHEN B; END LOOP;
A: 代表循环体中的sql语句，可以是一句也可能是多句，这是循环体核心部分，这些语句至少被执行一遍。
B: 循环结束条件表达式，为ture时，退出循环，否则再次执行循环体。
代码示例：
declare i int:= 0; begin loop i:=i+1; dbms_output.put_line(i); EXIT WHEN i > 3; END LOOP; end;
运行结果：
b. for 语句
FOR语句是一个可提前设置循环次数的循环控制语句，它有一个循环计数器，通常是一个整型变量，通过这个计数器来控制循环次数。
语法：
for A IN (reverse) B...C LOOP D; END LOOP;
A: 表示一个变量，通常为证书类型，用来作为计数器，默认值是递增的，当循环当中使用reverse关键字时，就会循环递减。
B: 计数器下线值，当计数器的值小于下限值的时候，终止循环。
C: 计数器上线值，当计数器的值大于上限值的时候，终止循环。
D: 循环体。
代码示例：
declare i int := 0; begin for i IN reverse 1..5 LOOP dbms_output.put_line(i); END LOOP; end;
运行结果：
c. while 语句
在执行之前，首先要判断条件表达式的值是否为true，true则执行循环体，否则退出WHILE循环，继续执行循环后面的代码。
基本语法：
while a loop b; end loop;
A: 表示一个条件表达式，当值为true时，程序执行循环体，否则退出。
B: 循环体内的sql语句。
代码示例：
declare i int := 0; begin while i < 3 loop i := i + 1; dbms_output.put_line(i); end loop; end;
运行结果：
4.游标
Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。
游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。
游标用来存储多条查询数据的一种数据结构（结果集），它有一个‘指针’，从上往下移动（fetch），从而能够遍历每条记录。
（1）概念图
（2）思维导图
（3）语法
A. 基本写法
示例：获取学习信息表的记录
-- 测试基础数据 create table stu_info ( id number(3), name varchar2(30), sex varchar2(2) ); insert into stu_info(id, name, sex) values (1, '小游子', '女'); insert into stu_info(id, name, sex) values (2, '小优子', '男'); commit;
游标语法：
DECLARE -- 1 声明游标 CURSOR cur_stu_info IS SELECT * FROM stu_info; v_stu_info cur_stu_info%ROWTYPE; BEGIN -- 2 开启游标 OPEN cur_stu_info; -- 3 获取数据（一次获取一行） FETCH cur_stu_info INTO v_stu_info; dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name); -- 4 关闭游标 CLOSE cur_stu_info; END;
（4）游标4大属性
特别说明：sql%notfound
Oracle 官方文档解释：Before the first fetch%NOTFOUND returns NULL. If fetch never executes susscessfully. the loop is never exited, because then EXIT WHEN statement executes only if it’s WHEN condition is true. To be safe. you might want to use the following EXIT statement instead:
EXIT WHEN SQL%NOTFOUND OR SQL%NOTFOUND IS NULL;
简单说，退出循环的必要条件：fetch 语句执行成功，且第一次 fetch 的值返回 null；
DECLARE CURSOR cur_stu_info IS SELECT * FROM stu_info t WHERE t.id = 9; v_stu_info cur_stu_info%ROWTYPE; BEGIN OPEN cur_stu_info; LOOP FETCH cur_stu_info INTO v_stu_info; -- 可测试，这段 fetch 放在 %notfound 后面 EXIT WHEN cur_stu_info%NOTFOUND; dbms_output.put_line('该语句不会执行，因为没有 id = 9 的记录'); END LOOP; CLOSE cur_stu_info; END;
（5）隐式游标
1.对于Select…into…语句，一次只能从数据库中获取一条数据，对于这类型的的DML_Sql语句，就是隐式Cursor。
如前所述，DML操作和单行select语句会使用隐式游标，它们是：
*插入操作：INSERT;
*更新操作：UPDATE;
*删除才做：DELETE;
*单行查询：SELECT…INTO……。
2.通过隐式Cursor的属性了解操作的状态和结果，从而达到流程的控制。Cursor属性：
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数 ；
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功 ；
SQL%NOTFOUND 布尔型
与SQL%FOUND属性返回值相反 ；
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假 ；
3.隐式Cursor是系统自动打开和关闭Cursor；
DECLARE v_count NUMBER; BEGIN INSERT INTO stu_info (id, NAME, sex) VALUES (3, '瑶瑶', '女'); IF SQL%FOUND THEN dbms_output.put_line('插入成功!'); END IF; UPDATE stu_info t SET t.name = '悠悠' WHERE t.id = 3; IF SQL%FOUND THEN dbms_output.put_line('更新成功!'); END IF; DELETE FROM stu_info t WHERE t.id = 3; IF SQL%FOUND THEN dbms_output.put_line('删除成功!'); END IF; SELECT COUNT(1) INTO v_count FROM stu_info t; IF SQL%FOUND THEN dbms_output.put_line('总记录为： ' || v_count); END IF; IF SQL%ISOPEN THEN dbms_output.put_line('不可能的，永远不可能走这一步'); ELSE dbms_output.put_line('系统已自动关闭游标'); END IF; END;
测试结果：
4示例：
SET SERVEROUTPUT ON BEGIN UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！'); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！'); END IF; END
运行结果：
将雇员编号改为7788：
（6）显示游标
1.属性
对于从数据库中提取多行数据，就需要用到显示游标。
显式Cursor的属性包括：
%ROWCOUNT 整型 获得FETCH语句返回的数据行数；
%FOUND 布尔型 最近FETCH语句返回一行数据位真，否则为假；
%NOTFOUND 布尔型 与%FOUND属性返回值相反；
%ISOPEN 布尔型 游标已经打开时为真，否则为假。
2.对于显式游标的运用步骤：
（1） 定义游标——Cousor [ Cousor Name] IS;
（2） 打开游标——Open [Cousor Name];
（3） 操作数据——Fenth [Cousor Name];
（4） 关闭游标——Close [Cousor Name]，这个步骤绝对不可以遗漏；
3.显式游标的用法
（1） 不带参数：
DECLARE -- 1 声明游标 CURSOR cur_stu_info IS SELECT * FROM stu_info; v_stu_info cur_stu_info%ROWTYPE; BEGIN -- 2 开启游标 OPEN cur_stu_info; -- 3 获取数据（一次获取一行） FETCH cur_stu_info INTO v_stu_info; dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name); -- 4 关闭游标 CLOSE cur_stu_info; END;
（2）带参数
DECLARE v_stu_info stu_info%ROWTYPE; CURSOR cur_stu_info(v_id stu_info.id%TYPE) IS SELECT * FROM stu_info t WHERE t.id = v_id; -- 步骤1: 声明游标 BEGIN OPEN cur_stu_info(1); -- 步骤2: 打开游标 -- fetch 一次只能获取一条记录， -- 要想获取多条记录可用 循环语句 或者 集合，后面介绍 FETCH cur_stu_info INTO v_stu_info; -- 步骤3: 提取数据 dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name); CLOSE cur_stu_info; -- 步骤4: 关闭游标 END;
测试结果（都一样，因为都是取得id=1的记录）：
（4）示例：
Loop语句：
declare cursor user is select * from user_table; user1 user_table%rowtype; begin open user; loop fetch user into user1; exit when user%notfound; dbms_output.put_line('用户名称：' || user1.USER_NAME); dbms_output.put_line('用户年龄：' || user1.USER_AGE); end loop; close user; --关闭游标 end;
while语句：
declare cursor user is select * from user_table; user1 user_table%rowtype; begin open user; fetch user into user1; while(user%found)loop dbms_output.put_line('用户名称：' || user1.USER_NAME); fetch user into user1; end loop; end;
for语句：
declare cursor user is select * from user_table; begin for user1 in user loop dbms_output.put_line('用户名称：'||user1.USER_NAME); dbms_output.put_line('用户年龄：'||user1.USER_AGE); end loop; end;
（5）训练：
1.用游标提取emp表中7788雇员的名称和职务
SET SERVEROUTPUT ON DECLARE v_ename VARCHAR2(10); v_job VARCHAR2(10); CURSOR emp_cursor IS SELECT ename,job FROM emp WHERE empno=7788; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO v_ename,v_job; DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job); CLOSE emp_cursor; END;
执行结果：
2.用游标提取雇员的姓名、职务和工资：
SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO emp_record; DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal); CLOSE emp_cursor; END;
执行结果：
说明：
实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。
注意：
可通过以下形式获得记录变量的内容： 记录变量名.字段名
3.显示工资最高的前3位雇员
SET SERVEROUTPUT ON DECLARE V_ename VARCHAR2(10); V_sal NUMBER(5); CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC; BEGIN OPEN emp_cursor; FOR I IN 1..3 LOOP FETCH emp_cursor INTO v_ename,v_sal; DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal); END LOOP; CLOSE emp_cursor; END;
执行结果：
说明：该程序在游标定义中使用了ORDER BY子句进行排序，并使用循环语句来提取多行数据。
6.游标循环
1.使用特殊的FOR循环显示全部雇员的编号和名称：
SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp; BEGIN FOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename); END LOOP; END;
执行结果：
说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。
2.另一种循环的游标循环：
SET SERVEROUTPUT ON BEGIN FOR re IN (SELECT ename FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(re.ename) END LOOP; END;
执行结果：
说明：：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。
7.游标参数的传递
1.带参数的游标：
SET SERVEROUTPUT ON DECLARE V_empno NUMBER(5); V_ename VARCHAR2(10); CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = p_deptno AND job = p_job; BEGIN OPEN emp_cursor(10, 'CLERK'); LOOP FETCH emp_cursor INTO v_empno,v_ename; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename); END LOOP; END;
执行结果：
说明：：游标emp_cursor定义了两个参数：p_deptno代表部门编号，p_job代表职务。语句OPEN emp_cursor(10, 'CLERK')传递了两个参数值给游标，即部门为10、职务为CLERK，所以游标查询的内容是部门10的职务为CLERK的雇员。循环部分用于显示查询的内容。
2.通过变量传递参数给游标：
SET SERVEROUTPUT ON DECLARE v_empno NUMBER(5); v_ename VARCHAR2(10); v_deptno NUMBER(5); v_job VARCHAR2(10); CURSOR emp_cursor IS SELECT empno, ename FROM emp WHERE deptno = v_deptno AND job = v_job; BEGIN v_deptno:=10; v_job:='CLERK'; OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_empno,v_ename; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename); END LOOP; END;
执行结果：
说明：：
该程序与前一程序实现相同的功能。
动态SELECT语句和动态游标的用法
Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。
对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行，语法是：
execute immediate 查询语句字符串 into 变量1[,变量2...];
（7）动态游标
1.区别
与隐式Cursor,显式Cursor的区别：Ref Cursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor，是静态的，在编译期间就决定数据结果集。
2.自定义类型 rel cursor
1.弱类型，无return（常用）
DECLARE v_sql_select VARCHAR(200); v_id scott.stu_info.id%TYPE; v_name scott.stu_info.name%TYPE; TYPE cur_stu_type IS REF CURSOR; -- 无 return cur_stu_info cur_stu_type; BEGIN -- 测试 v_id := 1; v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1'; OPEN cur_stu_info FOR v_sql_select USING v_id; -- 绑定变量（大数据处理常用优化手段） FETCH cur_stu_info INTO v_id, v_name; dbms_output.put_line(v_id || ' ：' || v_name); CLOSE cur_stu_info; END;
执行结果：
2.强类型，有return
DECLARE v_sql_select VARCHAR(200); v_stu_info_rows scott.stu_info%ROWTYPE; TYPE cur_stu_type IS REF CURSOR RETURN scott.stu_info%ROWTYPE; -- 有 return cur_stu_info cur_stu_type; BEGIN OPEN cur_stu_info FOR SELECT t.id, t.name, t.sex FROM stu_info t; FETCH cur_stu_info INTO v_stu_info_rows; dbms_output.put_line(v_stu_info_rows.id || ' ：' || v_stu_info_rows.name); CLOSE cur_stu_info; END;
执行结果：
3. 系统类型 sys_relcursor
常用，省去了手动定义动态游标的步骤，以下效果等同：
DECLARE -- TYPE cur_stu_type IS REF CURSOR; -- 手动定义动态游标 -- cur_stu_info cur_stu_type; cur_stu_info SYS_REFCURSOR; -- 声明动态游标， 这一个步骤等于上面两个步骤 BEGIN END;
4.动态SELECT语句和动态游标用法
Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。
对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行，语法是：
execute immediate 查询语句字符串 into 变量1[,变量2...];
SET SERVEROUTPUT ON DECLARE str varchar2(100); v_ename varchar2(10); begin str:='select ename from scott.emp where empno=7788'; execute immediate str into v_ename; dbms_output.put_line(v_ename); END;
执行结果：
说明：
SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。
在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。
定义游标类型的语句如下：
TYPE 游标类型名 REF CURSOR;
声明游标变量的语句如下：
游标变量名 游标类型名;
在可执行部分可以如下形式打开一个动态游标：
OPEN 游标变量名 FOR 查询语句字符串;
示例：按名字中包含的字母顺序分组显式雇员的信息
declare type cur_type is ref cursor; cur cur_type; rec scott.emp%rowtype; str varchar2(50); letter char:= 'A'; begin loop str:= 'select ename from emp where ename like ''%'||letter||'%'''; open cur for str; dbms_output.put_line('包含字母'||letter||'的名字：'); loop fetch cur into rec.ename; exit when cur%notfound; dbms_output.put_line(rec.ename); end loop; exit when letter='Z'; letter:=chr(ascii(letter)+1); end loop; end;
执行结果：
说明：使用了二重循环，在外循环体中，动态生成游标的SELECT语句，然后打开。通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。
（5）异常处理，错误处理
错误处理部分位于程序的可执行部分之后，是由WHEN语句引导的多个分支构成的。错误处理的语法如下：
EXCEPTION
WHEN 错误1[OR 错误2] THEN
语句序列1；
WHEN 错误3[OR 错误4] THEN
语句序列2；
WHEN OTHERS
语句序列n；
END;
其中：
错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误，参见下一节系统预定义的错误类型。
语句序列就是不同分支的错误处理部分。
凡是出现在WHEN后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN OTHERS部分进行统一处理，OTHENS必须是EXCEPTION部分的最后一个错误处理分支。如要在该分支中进一步判断错误种类，可以通过使用预定义函数SQLCODE( )和SQLERRM( )来获得系统错误号和错误信息。
如果在程序的子块中发生了错误，但子块没有错误处理部分，则错误会传递到主程序中。
下面是由于查询编号错误而引起系统预定义异常的例子。
【训练1】
查询编号为1234的雇员名字。
SET SERVEROUTPUT ON DECLARE v_name VARCHAR2(10); BEGIN SELECT ename INTO v_name FROM emp WHERE empno = 1234; DBMS_OUTPUT.PUT_LINE('该雇员名字为：'|| v_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('编号错误，没有找到相应雇员！'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其他错误！'); END;
执行结果：
说明：在以上查询中，因为编号为1234的雇员不存在，所以将发生类型为“NO_DATA_
FOUND”的异常。“NO_DATA_FOUND”是系统预定义的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，并执行相应代码部分。在本例中，输出用户自定义的错误信息“编号错误，没有找到相应雇员!”。如果发生其他类型的错误，将执行OTHERS条件下的代码部分，显示“发生其他错误!”。
【训练2】
由程序代码显示系统错误。
SET SERVEROUTPUT ON DECLARE v_temp NUMBER(5):=1; BEGIN v_temp:=v_temp/0; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生系统错误！'); DBMS_OUTPUT.PUT_LINE('错误代码：'|| SQLCODE( )); DBMS_OUTPUT.PUT_LINE('错误信息：' ||SQLERRM( )); END;
执行结果：
说明：
程序运行中发生除零错误，由WHEN OTHERS捕捉到，执行用户自己的输出语句显示错误信息，然后正常结束。在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。
（8）三种游标循环效率比较
结论：一般来说 '批量处理' 的速度要最好,'隐式游标' 的次之,'单条处理' 的最差 说明：若有兴趣，可以在数据量多的表里面，分别尝试下列三种写法，并打印时间，用作比较 1. 批量处理 open 游标; loop fetch 游标 bulk collect into 集合变量（也就是 table 类型哦） limit 数值; -- 一般 500 左右 exit when 条件; -- cursor.count = 0 逻辑处理; end loop; close 游标; 2. 隐式游标 for x in (sql 语句) loop 逻辑处理; end loop; 3. 单条处理 open 游标; loop fetch 游标 into 变量; exit when 条件; 逻辑处理; end loop; close 游标;
示例：遍历stu_info信息
DECLARE v_sql_select VARCHAR(300); cur_stu_info SYS_REFCURSOR; TYPE record_stu_info IS RECORD( v_id scott.stu_info.id%TYPE, v_name scott.stu_info.name%TYPE); TYPE table_stu_info IS TABLE OF record_stu_info; v_stu_info_table table_stu_info; BEGIN v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1'; OPEN cur_stu_info FOR v_sql_select USING 3; -- 绑定变量，此处 3，仅用作测试 LOOP FETCH cur_stu_info BULK COLLECT INTO v_stu_info_table LIMIT 1; -- 数据量太少，仅当前测试使用哦，实际开发 建议 500 左右 -- 此时 %notfound 不合适，count 适用 -- 因为 可能找到数据了（found 非空值），但是小于 LIMIT N EXIT WHEN v_stu_info_table.count = 0; -- 退出条件（ FOR i IN v_stu_info_table.first .. v_stu_info_table.last LOOP dbms_output.put_line('序号：' || v_stu_info_table(i).v_id || ' , ' || '姓名：' || v_stu_info_table(i).v_name); END LOOP; END LOOP; CLOSE cur_stu_info; EXCEPTION WHEN OTHERS THEN IF cur_stu_info%ISOPEN THEN CLOSE cur_stu_info; END IF; dbms_output.put_line(SQLCODE || ' : ' || SQLERRM); dbms_output.put_line(dbms_utility.format_error_backtrace); END;
测试结果：（建议在 ‘测试窗口’ debug 看看执行步骤）
（9）使用游标
15-1:使用标量变量接收游标数据 DECLARE CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE deptno=&dno; v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; v_job emp.job%TYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_ename,v_job,v_sal; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line('姓名：'||v_ename||'，岗位：'||v_job||'，工资：'||v_sal); END LOOP; CLOSE emp_cursor; END; / 15-2:使用pl/sql纪录变量接收游标数据 DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT>&n; dbms_output.put_line('姓名：'||emp_record.ename|| '，工资：'||emp_record.sal); END LOOP; CLOSE emp_cursor; END; / 15-3:使用pl/sql集合变量接收游标数据 DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM emp WHERE lower(job)=lower('&job'); TYPE emp_table_type IS TABLE OF emp_cursor%ROWTYPE INDEX BY BINARY_INTEGER; emp_table emp_table_type; i INT; BEGIN OPEN emp_cursor; LOOP i:=emp_cursor%ROWCOUNT+1; FETCH emp_cursor INTO emp_table(i); EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line('姓名：'||emp_table(i).ename|| '，工资：'||emp_table(i).sal); END LOOP; CLOSE emp_cursor; END; / 15-4:在游标for循环中引用已定义游标 DECLARE CURSOR emp_cursor IS SELECT ename,hiredate FROM emp ORDER BY hiredate DESC; BEGIN FOR emp_record IN emp_cursor LOOP dbms_output.put_line('姓名：'||emp_record.ename ||',工作日期：'||emp_record.hiredate); EXIT WHEN emp_cursor%ROWCOUNT=&n; END LOOP; END; / 15-5:在游标for循环中直接引用子查询 BEGIN FOR emp_record IN (SELECT ename,hiredate,rownum FROM emp ORDER BY hiredate) LOOP dbms_output.put_line('姓名：'||emp_record.ename ||',工作日期：'||emp_record.hiredate); EXIT WHEN emp_record.rownum=&n; END LOOP; END; / 15-6:参数游标 DECLARE CURSOR emp_cursor(dno NUMBER) IS SELECT ename,job FROM emp WHERE deptno=dno; BEGIN FOR emp_record IN emp_cursor(&dno) LOOP dbms_output.put_line('姓名：'||emp_record.ename ||',岗位：'||emp_record.job); END LOOP; END; / 15-7:更新游标行 DECLARE CURSOR emp_cursor IS SELECT ename,sal,deptno FROM emp FOR UPDATE; dno INT:=&no; BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.deptno=dno THEN dbms_output.put_line('姓名:'||emp_record.ename ||',原工资:'||emp_record.sal); UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor; END IF; END LOOP; END; / 15-8:删除游标行 DECLARE CURSOR emp_cursor IS SELECT ename FROM emp FOR UPDATE; name VARCHAR2(10):=lower('&name'); BEGIN FOR emp_record IN emp_cursor LOOP IF lower(emp_record.ename)=name THEN DELETE FROM emp WHERE CURRENT OF emp_cursor; ELSE dbms_output.put_line('姓名:'||emp_record.ename); END IF; END LOOP; END; / 15-9:使用of子句在特定表上加行共享锁． DECLARE CURSOR emp_cursor IS SELECT a.dname,b.ename FROM dept a JOIN emp b ON a.deptno=b.deptno FOR UPDATE OF b.deptno; name VARCHAR2(10):=LOWER('&name'); BEGIN FOR emp_record IN emp_cursor LOOP IF LOWER(emp_record.dname)=name THEN dbms_output.put_line('姓名:'||emp_record.ename); DELETE FROM emp WHERE CURRENT OF emp_cursor; END IF; END LOOP; END; / 15-10:使用无返回类型的游标变量 DECLARE TYPE ref_cursor_type IS REF CURSOR; ref_cursor ref_cursor_type; v1 NUMBER(6); v2 VARCHAR2(10); BEGIN OPEN ref_cursor FOR SELECT &col1 col1,&col2 col2 FROM &table WHERE &cond; LOOP FETCH ref_cursor INTO v1,v2; EXIT WHEN ref_cursor%NOTFOUND; dbms_output.put_line('col1='||v1||',col2='||v2); END LOOP; CLOSE ref_cursor; END; / 15-11:使用有返回类型的游标变量 DECLARE TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE; emp_cursor emp_cursor_type; emp_record emp%ROWTYPE; BEGIN OPEN emp_cursor FOR SELECT * FROM emp WHERE deptno=&dno; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line('姓名:'||emp_record.ename|| ',工资:'||emp_record.sal); END LOOP; CLOSE emp_cursor; END; / 15-12:使用fetch...bulk collect提取所有数据 DECLARE CURSOR emp_cursor IS SELECT * FROM emp WHERE LOWER(job)=LOWER('&job'); TYPE emp_table_type IS TABLE OF emp%ROWTYPE; emp_table emp_table_type; BEGIN OPEN emp_cursor; FETCH emp_cursor BULK COLLECT INTO emp_table; CLOSE emp_cursor; FOR i IN 1.. emp_table.COUNT LOOP dbms_output.put_line('姓名:'||emp_table(i).ename ||',工资:'||emp_table(i).sal); END LOOP; END; / 15-13:使用limit子句限制提取行数 DECLARE CURSOR emp_cursor IS SELECT * FROM emp; TYPE emp_array_type IS VARRAY(5) OF emp%ROWTYPE; emp_array emp_array_type; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor BULK COLLECT INTO emp_array LIMIT &rows; FOR i IN 1..emp_array.COUNT LOOP dbms_output.put_line('姓名:'||emp_array(i).ename ||',工资:'||emp_array(i).sal); END LOOP; EXIT WHEN emp_cursor%NOTFOUND; END LOOP; CLOSE emp_cursor; END; / 15-14:使用cursor表达式 DECLARE CURSOR dept_cursor(no NUMBER) IS SELECT a.dname,CURSOR(SELECT * FROM emp WHERE deptno=a.deptno) FROM dept a WHERE a.deptno=no; TYPE ref_cursor_type IS REF CURSOR; emp_cursor ref_cursor_type; emp_record emp%ROWTYPE; v_dname dept.dname%TYPE; BEGIN OPEN dept_cursor(&dno); LOOP FETCH dept_cursor INTO v_dname,emp_cursor; EXIT WHEN dept_cursor%NOTFOUND; dbms_output.put_line('部门名:'||v_dname); LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line('----雇员名:'||emp_record.ename ||',岗位:'||emp_record.job); END LOOP; END LOOP; CLOSE dept_cursor; END;