第3章〓数据库编程 标准SQL是非过程化的查询语言，具有操作统一、面向集合、功能丰富、使用简单等多项优点。但和程序设计语言相比，高度非过程化的优点同时也造成了它的一个弱点： 缺少流程控制能力，难以实现应用业务中的逻辑控制。SQL编程技术可以有效克服SQL实现复杂应用方面的不足，提高应用系统和RDBMS间的互操作性。 这里主要介绍Oracle 19c中与数据库编程相关的内容。 3.1PL/SQL编程的基础 PL/SQL是Oracle的专用语言，它是对标准SQL的扩展。SQL语句可以嵌套在PL/SQL代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。在Oracle数据库以及开发工具中都内置了PL/SQL处理引擎。PL/SQL被集成在Oracle数据库服务器产品中，因此，其代码可以得到非常高效的处理。 3.1.1PL/SQL程序的结构 PL/SQL程序的基本结构是块。所有的PL/SQL程序都是由块组成的。这些块之间可以互相嵌套，每个块完成一个逻辑操作。 PL/SQL程序通常包括3部分： (1) DECLARE部分。DECLARE部分包含定义变量、常量和游标等类型的代码。 (2) BEGIN…END部分。BEGIN…END部分是程序的主体，其中还可以再嵌套BEGIN…END部分。该部分包含了该程序块的所有处理操作。 (3) EXCEPTION部分。EXCEPTION部分是异常处理部分，允许在执行BEGIN…END部分发生异常时控制程序的执行。 一个程序块总是以END语句结束的，其中BEGIN…END部分是PL/SQL必需的部分，但一般的程序块都包括这3部分，其基本结构如图31所示。 图31PL/SQL程序块的基本结构 【例31】PL/SQL程序块示例。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2sum_num number(2); 3BEGIN 4SELECT COUNT(*) INTO sum_num FROM dept; 5dbms_output.put_line('记录个数：'||sum_num); 6END; 7/ 记录个数：4 PL/SQL 过程已成功完成 。 注意： SET SERVEROUTPUT ON为打开服务器的输出显示，即打开Oracle自带的输出方法dbms_output。 【例32】PL/SQL程序块的嵌套使用。 SQL> SET SERVEROUTPUT ON SQL> DECLARE --外层程序块头 2out_text1 varchar2(20):='外层程序块'; 3BEGIN 4DECLARE --内层程序块头 5out_text2 varchar2(20); 6BEGIN 7out_text2:='内层程序块'; 8dbms_output.put_line(out_text2); 9END; --内层程序块尾 10dbms_output.put_line(out_text1); 11END; --外层程序块尾 12/ 内层程序块 外层程序块 PL/SQL 过程已成功完成 。 注意： 变量可以在程序块的DECLARE部分和BEGIN…END部分为其赋值。赋值时，常用的方法是使用PL/SQL赋值操作符“∶=”。 3.1.2使用%TYPE和%ROWTYPE类型的变量 在定义变量时，除了可以使用Oracle规定的数据类型外，还可以使用%TYPE和%ROWTYPE来定义变量。 1. %TYPE变量 在例31中，为了存储从数据库中检索到的数据，首先根据检索的数据列的数据类型定义变量，然后使用SELECT语句中的INTO子句将检索到的数据保存到变量中。这里有一个前提条件，即用户必须事先知道检索的数据类型。如果用户事先并不知道检索的数据列的数据类型，这时可以考虑使用%TYPE定义变量。 【例33】使用%TYPE变量类型。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2nodept.deptno%type; 3name dept.dname%type; 4place dept.loc%type; 5BEGIN 6SELECT deptno,dname,loc 7INTO no,name,place 8FROM dept WHERE deptno=10; 9dbms_output.put_line(no||' '||name||' '||place); 10END; 11/ 10 ACCOUNTING NEW YORK PL/SQL 过程已成功完成 。 使用%TYPE定义变量的好处如下： (1) 用户不必查看数据类型就可以确保定义的变量能够存储检索的数据。 (2) 使用%TYPE类型的变量后，如果用户后期修改数据库结构(如改变某列的数据类型)，则不必考虑对所定义的变量进行更改。 2. %ROWTYPE变量 %ROWTYPE类型的变量一次可以存储从数据库检索的一行数据。该变量的结构与检索表的结构完全相同。 【例34】使用%ROWTYPE变量类型。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2row_dept dept%ROWTYPE; 3BEGIN 4SELECT * 5INTO row_dept 6FROM dept WHERE deptno=10; 7dbms_output.put_line(row_dept.deptno); 8dbms_output.put_line(row_dept.dname||' '||row_dept.loc); 9END; 10/ 10 ACCOUNTING NEW YORK PL/SQL 过程已成功完成 。 3.1.3条件判断语句 PL/SQL与其他编程语言一样，也都具有条件判断语句。条件判断语句主要的作用是根据条件的变化选择执行不同的代码。PL/SQL中常用的条件判断语句有IF语句和CASE语句。 1. IF语句 在PL/SQL中，为了控制程序的执行方向，引进了IF语句。IF语句主要有如下两种形式。 1) 形式一 IF 条件 THEN PL/SQL语句1或SQL语句1; ［ ELSE PL/SQL语句2或SQL语句2; ］ END IF; ELSE短语用方括号括起来，同其他语言一样，表示它为可选项。 【例35】IF语句示例1： 判断变量a和b 的大小。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2a number; 3b number; 4BEGIN 5a:=1; 6b:=2; 7IF a>b THEN 8dbms_output.put_line(a||'>'||b); 9ELSE 10dbms_output.put_line(a||'<'||b); 11END IF; 12END; 13/ 1<2 PL/SQL 过程已成功完成 。 2) 形式二 IF…END IF语句一次只能判断一个条件，语句IF…ELSIF…END IF则可以判定两个以上的判断条件。该语句的语法形式如下。 IF 条件1 THEN PL/SQL语句1或SQL语句1; ELSIF 条件2 THEN PL/SQL语句2或SQL语句2; … ELSE PL/SQL语句n或SQL语句n; END IF; 【例36】IF语句示例2： 根据成绩输出对应的成绩级别。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2score_var number; 3BEGIN 4score_var:=88; 5IF score_var<60 THEN 6dbms_output.put_line('差'); 7ELSIF score_var<80 THEN 8dbms_output.put_line('中'); 9ELSIF score_var<90 THEN 10dbms_output.put_line('良'); 11ELSE 12dbms_output.put_line('优'); 13END IF; 14END; 15/ 良 PL/SQL 过程已成功完成 。 2. CASE语句 CASE语句的作用与IF…ELSIF…END IF语句相同，都可以实现多项选择。但CASE语句是一种更简洁的表示法，并且相对于IF结构表示法而言消除了一些重复。CASE语句共有两种形式。 1) 形式一 第一种形式是获取一个表达式的值，系统根据其值，查找与其相匹配的WHEN常量。当找到一个匹配时，就执行与该WHEN常量相关的THEN子句； 如果没有与表达式相匹配的WHEN常量，那么就执行ELSE子句。该语句的语法形式如下： CASE 表达式 WHEN常量1THENPL/SQL语句1; WHEN常量2THENPL/SQL语句2; … WHEN常量nTHENPL/SQL语句n; ［ ELSEPL/SQL语句n+1; ］ END CASE; 【例37】CASE语句示例1： 判断emp表中“SMITH”员工的职务。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 job_var emp.job%type; 3BEGIN 4SELECT job 5INTO job_var 6FROM emp 7WHERE ename='SMITH'; 8CASE job_var 9WHEN 'SALESMAN' THEN dbms_output.put_line('SMITH'||'是销售员'); 10WHEN 'CLERK' THEN dbms_output.put_line('SMITH'||'是管理员'); 11ELSE dbms_output.put_line('SMITH'||'是经理'); 12END CASE; 13END; 14/ SMITH是管理员 PL/SQL 过程已成功完成 。 2) 形式二 第二种形式是判断每个WHEN子句后的条件。该语句的语法形式如下： CASE WHEN 条件1 THEN PL/SQL语句1; WHEN 条件2 THEN PL/SQL语句2; … WHEN 条件n THEN PL/SQL语句n; ［ELSE PL/SQL语句n+1;］ END CASE; 【例38】CASE语句示例2： 假设所给的数值是一个分数，判断该分数的等级。 等级判断标准如下： 分数<60为“差”，60<=分数<80为“中”，80<=分数<90为“良”，90<=分数<=100 为“优”。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2score_var number; 3BEGIN 4score_var:=85; 5CASE 6WHEN score_var<60 THEN dbms_output.put_line('差'); 7WHEN score_var<80 THEN dbms_output.put_line('中'); 8WHEN score_var<90 THEN dbms_output.put_line('良'); 9ELSE dbms_output.put_line('优'); 10END CASE; 11END; 12/ 良 PL/SQL 过程已成功完成 。 3.1.4循环语句 循环语句与条件语句一样都能控制程序的执行流程，它允许重复执行一条语句或一组语句。PL/SQL支持3种类型的循环： 无条件循环、WHILE循环和FOR循环。 1. 无条件循环 最基本的循环称为无条件循环。这种类型的循环如果没有指定EXIT语句将一直进行下去，成为死循环。所以，无条件循环中必须指定EXIT语句何时停止执行循环。该语句的语法形式如下： LOOP PL/SQL语句; EXIT WHEN 条件; END LOOP; 为了能让循环正常运行，必须为EXIT WHEN子句提供一个在某时刻可以判断为TRUE的条件。当判断条件为TRUE时，就停止循环的执行。 【例39】LOOP循环语句示例： 求1～5的和。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2i number:=1; 3s number:=0; 4BEGIN 5LOOP 6s:=s+i; 7i:=i+1; 8EXIT WHEN i>5; 9END LOOP; 10dbms_output.put_line('1+2+…+5='||s); 11END; 12/ 1+2+…+5=15 PL/SQL 过程已成功完成 。 2. WHILE循环 WHILE循环在每次执行时，都将判断循环条件。如果它为TRUE，那么循环将继续执行； 如果条件为FALSE，则循环将会停止执行。该语句的语法形式如下： WHILE 条件 LOOP PL/SQL语句; END LOOP; 【例310】WHILE循环语句示例： 求1～5的和。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2i number:=1; 3s number:=0; 4BEGIN 5WHILE i<=5 LOOP 6s:=s+i; 7i:=i+1; 8END LOOP; 9dbms_output.put_line('1+2+…+5='||s); 10END; 11/ 1+2+…+5=15 PL/SQL 过程已成功完成 。 3. FOR循环 在WHILE循环中，为了防止出现死循环，需要在循环内不断修改判断条件。而FOR循环则通过指定一个数字范围，确切地指出循环应执行多少次。该语句的语法形式如下： FOR 循环控制变量 IN ［REVERSE］ 下限值．．上限值 LOOP PL/SQL语句; END LOOP; 使用FOR循环时应注意以下两点。 (1) FOR循环中的下限值和上限值决定了循环的运行次数。默认情况下，循环控制变量从下限值开始。每运行一次，循环计数器的值就会自动加1； 当循环控制变量达到上限值时，FOR循环结束。 (2) 使用关键字REVERSE时，循环控制变量将自动减1，并强制循环控制变量的值从上限值到下限值。 【例311】FOR循环语句示例： 计算1～5的和。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2s number:=0; 3BEGIN 4FOR i IN 1..5 LOOP 5s:=s+i; 6END LOOP; 7dbms_output.put_line('1+2+…+5='||s); 8END; 9/ 1+2+…+5=15 PL/SQL 过程已成功完成 。 3.2游标 通过SELECT语句查询时，返回的结果是一个由多行记录组成的集合。而程序设计语言有时要处理查询结果集中的每一条记录。为此，SQL提供了游标机制。游标充当指针的作用，使程序设计语言一次只能处理查询结果中的一行。 在Oracle中，有显式和隐式两种游标。对于PL/SQL程序中发出的所有DML(数据操纵语言)和SELECT语句，Oracle都会自动声明“隐式游标”。为了处理由SELECT语句返回的一组记录，需要在PL/SQL程序中声明和处理“显式游标”。这里主要介绍显式游标的应用。 3.2.1显式游标的定义和使用 显式游标是在PL/SQL程序中使用包含SELECT语句来声明的游标。如果需要处理从数据库中检索的一组记录，则可以使用显式游标。使用显式游标处理数据需要4个PL/SQL步骤： 声明游标、打开游标、提取数据和关闭游标。 1. 声明游标 在DECLARE部分按以下格式声明游标： CURSOR游标名ISSELECT语句; 声明游标时需注意以下两点： (1) 声明游标的作用是得到一个SELECT查询结果集。该结果集中包含了应用程序中要处理的数据，从而为用户提供逐行处理的途径。 (2) SELECT语句是对表或视图的查询语句。可以带WHERE条件、ORDERBY或GROUP BY等子句，但不能使用INTO子句。 2. 打开游标 在BEGIN…END部分，按以下格式打开游标： OPEN游标名; 游标必须先声明后打开。打开游标时，SELECT语句的查询结果就被传送到了游标工作区，以便供用户读取。 3. 提取数据 在BEGIN…END部分，按以下格式将游标工作区中的数据读取到变量中(提取游标必须在打开游标之后进行)： FETCH游标名INTO变量名1［,变量名2,…］; 成功打开游标后，游标指针指向结果集的第一行之前，而FETCH语句将使游标指针指向下一行。因此，第一次执行FETCH语句时，将检索第一行中的数据并保存到变量中。随后每执行一条FETCH语句，该指针将移动到结果集的下一行。可以在循环中使用FETCH语句，这样每一次循环都会从表中读取一行数据，然后进行相同的逻辑处理。 4. 关闭游标 显式游标打开后，必须显式地关闭。按以下格式关闭游标： CLOSE游标名; 游标一旦关闭，其占用的资源就被释放，用户不能再从结果集中检索数据。如果想重新检索，必须重新打开游标才能使用。 【例312】用游标提取emp表中7788员工的姓名和职务。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2v_ename emp.ename%type; 3v_jobemp.job%type; 4CURSOR emp_cursor IS SELECT ename,job FROM emp WHERE empno=7788; 5BEGIN 6OPEN emp_cursor; 7FETCH emp_cursor INTO v_ename,v_job; 8dbms_output.put_line(v_ename||' '||v_job); 9CLOSE emp_cursor; 10END; 11/ SCOTT ANALYST PL/SQL 过程已成功完成 。 【例313】用游标显示工资最高的前3名员工的姓名和工资。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2v_ename emp.ename%type; 3v_sal emp.sal%type; 4CURSOR emp_cursor 5IS SELECT ename,sal FROM emp ORDER BY sal DESC; 6BEGIN 7OPENemp_cursor; 8FOR i IN 1..3 LOOP 9FETCHemp_cursorINTOv_ename,v_sal; 10dbms_output.put_line(v_ename||' '||v_sal); 11END LOOP; 12CLOSEemp_cursor; 13END; 14/ KING5000 FORD3000 SCOTT 3000 PL/SQL 过程已成功完成 。 3.2.2显式游标的属性 虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如表31所示。 表31显式游标的属性 属性返回值类型功能 %ROWCOUNT整型获得FETCH语句返回的数据行数 %FOUND布尔型FETCH语句是否提取一行数据，提取成功则为TRUE，否则为FALSE %NOTFOUND布尔型与%FOUND属性的返回值相反 %ISOPEN布尔型游标是否已经打开，打开为TRUE，否则为FALSE 如果要取得游标属性，在属性前加游标名即可。 【例314】使用游标显示dept表中的每行记录。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2row_dept dept%rowtype; 3CURSOR dept_cursor IS SELECT * FROM dept; 4BEGIN 5OPEN dept_cursor; 6IF dept_cursor%ISOPEN THEN 7LOOP 8FETCH dept_cursor INTO row_dept; 9EXIT WHEN dept_cursor%NOTFOUND; 10dbms_output.put_line(row_dept.deptno||' '||row_dept.dname||' '||row_dept.loc); 11END LOOP; 12ELSE 13dbms_output.put_line('用户信息：游标没有打开!'); 14END IF; 15CLOSE dept_cursor; 16END; 17/ 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON PL/SQL 过程已成功完成 。 3.2.3游标的FOR循环 在PL/SQL中还有一种更加方便地使用显式游标的方法，那就是游标的FOR循环。游标的FOR循环是显式游标的一种快捷使用方式，它使用FOR循环依次读取结果集中的行数据。当FOR循环开始时，游标将自动打开(不需要使用OPEN方法)。每循环一次，系统将自动读取游标当前行的数据(不需要使用FETCH)； 当退出FOR循环时，游标被自动关闭(不需要使用CLOSE)。 语句的定义格式如下： FOR记录变量名IN游标名LOOP PL/SQL语句; END LOOP; 【例315】使用FOR循环形式显示10部门员工的编号和姓名。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2CURSOR emp_cursor IS SELECT empno,ename FROM emp WHERE deptno=10; 3BEGIN 4FOR emp_r IN emp_cursor LOOP 5dbms_output.put_line(emp_r.empno||' '||emp_r.ename); 6END LOOP; 7END; 8/ 7782 CLARK 7839 KING 7934 MILLER PL/SQL 过程已成功完成 。 3.2.4带参数的游标 在声明游标时，可以将参数传递给游标并在查询中使用。带参数游标的声明语句格式如下： CURSOR 游标名 (参数［,参数,…］) IS SELECT语句; 其中，参数的定义格式如下： 参数名［IN］数据类型［:=值 或 DEFAULT 值］ 对于参数，需要注意以下两点。 (1) 参数只定义数据类型，没有长度。 (2) DEFAULT用于给参数设定一个默认值。当没有参数值给游标时，就使用默认值。 打开游标时，可以指定传递的参数值。带参数游标的打开语句格式如下： OPEN 游标名(值［,值,…］) 【例316】根据所给的参数值，显示员工编号和姓名。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2v_empno emp.empno%type; 3v_ename emp.ename%type; 4CURSOR emp_cursor(p_deptno number,p_job varchar2) 5IS SELECT empno,ename FROM emp WHERE deptno=p_deptno and job=p_job; 6BEGIN 7OPEN emp_cursor(10, 'CLERK'); 8LOOP 9FETCH emp_cursor INTO v_empno,v_ename; 10EXIT WHEN emp_cursor%NOTFOUND; 11dbms_output.put_line(v_empno||' '||v_ename); 12END LOOP; 13CLOSE emp_cursor; 14END; 15/ 7934 MILLER PL/SQL 过程已成功完成 。 3.2.5使用游标更新和删除数据 通过游标可以查询数据表中的数据，那么如何使用游标修改或删除数据表中的记录呢？使用游标修改和删除表中记录的操作是指在游标定位后，修改或删除表中指定的数据行。 为了使用游标更新和删除数据，需要在声明游标时使用FOR UPDATE选项，以便在打开游标时锁定游标结果集与表中对应数据行的所有列和部分列。使用FOR UPDATE选项声明游标的语法格式如下： CURSOR游标名 ISSELECT语句FORUPDATE［OF列1［,列2,…］］; 其中，OF选项只在要进行数据更新(UPDATE)时使用。OF后面指定要更新的具体数据列。如果不指定OF选项，则可更新游标当前行中的所有数据。 当使用FOR UPDATE声明游标后，可在DELETE和UPDATE语句中使用WHERE CURRENT OF子句，修改或删除游标结果集中当前行对应的表中的数据行。格式如下： WHERECURRENTOF游标名 【例317】使用游标更新emp表中的COMM值。 代码如下： SQL> SET SERVEROUTPUT ON SQL> DECLARE 2CURSOR c1 IS SELECT empno,sal FROM emp 3WHERE comm IS NULL FOR UPDATE OF comm; 4v_comm emp.sal%TYPE; 5BEGIN 6FOR r IN c1 LOOP 7CASE 8WHEN r.sal <500 THEN v_comm:=r.sal *0.25; 9WHEN r.sal <1000 THEN v_comm:=r.sal *0.2; 10WHEN r.sal <3000 THEN v_comm:=r.sal *0.15; 11ELSE v_comm:=r.sal *0.12; 12END CASE; 13UPDATE emp SET comm=v_comm WHERE CURRENT OF c1; 14END LOOP; 15END; 16/ PL/SQL 过程已成功完成 。 3.3异 常 处 理 异常是Oracle数据库中的PL/SQL代码执行期间出现的错误。发生异常后，语句将停止执行，跳转到PL/SQL程序块的异常处理部分。SQL Plus处理异常的方法就是在屏幕上显示异常信息。 Oracle常用的有两种类型的异常。 (1) 预定义异常。Oracle为用户提供了大量的在PL/SQL中使用的预定义异常，以检查用户代码失败的一般原因。 (2) 自定义异常。如果程序设计人员认为某种情况违反了业务逻辑，设计人员可明确定义并触发异常。 异常处理部分一般放在PL/SQL程序块的后半部分，其结构如下： EXCEPTION WHEN异常情况1THEN处理异常代码1; WHEN异常情况2THEN处理异常代码2; … WHENOTHERSTHEN处理异常代码; 3.3.1Oracle的预定义异常 对于Oracle提供的预定义异常，用户可以在自己的PL/SQL异常处理部分使用名称对其进行标识。常用的预定义异常及其对应的Oracle错误信息如表32所示。 表32Oracle的预定义异常 错 误 信 息异 常 名 称说明 ORA0001DUP_VAL_ON_INDEX试图破坏一个唯一性限制 ORA0051Timeoutonresource在等待资源时发生超时 ORA0061Transactionbackedout由于发生死锁，事务被撤销 ORA1001InvalidCURSOR试图使用一个无效的游标 ORA1012Notloggedon没有连接到Oracle ORA1017Logindenied无效的用户名/口令 ORA1403NO_DATA_FOUNDSELECT INTO没有找到数据 ORA1422TOO_MANY_ROWSSELECT INTO返回多行 ORA1476Zerodivide试图被零除 ORA1722InvalidNUMBER转换一个数字时失败 ORA6500Storageerror内存不够引发的内部错误 ORA6501Programerror内部错误 ORA6502Valueerror转换或截断错误 ORA6504Rowtypemismatch主变量和游标的类型不兼容 ORA6511CURSORALREADYOPEN试图打开一个已经打开的游标时，将产生这种异常 ORA6530AccessINTOnull试图为null对象的属性赋值 【例318】向dept表中插入与主键值相同的记录。 情况一不用预定义异常解决，代码如下： SQL> BEGIN 2INSERT INTO dept(deptno,dname) VALUES(10,'HR'); 3END; 4/ BEGIN * 第1行出现错误: ORA-00001: 违反唯一约束条件 (SCOTT.PK_DEPT) ORA-06512: 在 line 2 情况二使用预定义异常解决，代码如下： SQL> SET SERVEROUTPUT ON SQL> BEGIN 2INSERT INTO dept(deptno,dname) VALUES(10,'HR'); 3EXCEPTION 4WHEN DUP_VAL_ON_INDEX THEN 5dbms_output.put_line('捕获到了DUP_VAL_ON_INDEX异常'); 6dbms_output.put_line('该主键值已经存在'); 7END; 8/ 捕获到了DUP_VAL_ON_INDEX异常 该主键值已经存在 PL/SQL 过程已成功完成 。 3.3.2用户自定义异常的处理 在实际的程序开发中，为了实施具体的业务逻辑规则，程序开发人员往往会根据这些逻辑规则自定义一些异常。当用户违反了这些规则时，就会引发一个自定义异常，从而中断程序的正常执行，并转到自定义异常处理部分。 用户自定义异常是通过显式使用RAISE语句来触发的。当引发一个异常时，控制就转向EXCEPTION异常处理部分，执行异常处理语句。处理自定义异常的步骤如下。 (1) 在PL/SQL程序块的定义部分定义异常情况，语句如下： 异常情况EXCEPTION; (2) 使用RAISE引出自定义异常，语句如下： RAISE异常情况; (3) 在PL/SQL程序块的异常处理部分对异常情况做出相应的处理。 【例319】检查dept表中的记录是否被更新。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2ex_update EXCEPTION; 3BEGIN 4UPDATE dept SET dname='HR' WHERE deptno=50; 5IF sql%notfound THEN 6RAISE ex_update; 7END IF; 8EXCEPTION 9WHEN ex_update THEN 10dbms_output.put_line('捕获到自定义异常ex_update'); 11dbms_output.put_line('未更新任意行'); 12END; 13/ 捕获到自定义异常ex_update 未更新任意行 PL/SQL 过程已成功完成 。 3.4存 储 过 程 前面所创建的PL/SQL程序块都是匿名的。这些匿名的程序块没有被存储，每次执行后都不可以被重新使用。因此，每次运行匿名程序块时，都需要先编译然后再执行。很多时候都需要保存PL/SQL程序块，便于以后可以重新使用。 存储过程是一种命名的PL/SQL程序块，它可以被赋予参数并存储在数据库中，以便被用户调用。由于存储过程是已经编译好的代码，所以在调用的时候不必再次进行编译，从而提高了程序的运行效率。 3.4.1创建存储过程 创建存储过程的语法结构如下： CREATE ［OR REPLACE］ PROCEDURE过程名 AS 声明部分; BEGIN 功能语句; EXCEPTION 异常处理; END; 说明： 选择OR REPLACE选项时，如果创建的存储过程已经存在，将重新建立与原来同名的存储过程。 【例320】存储过程示例。 SQL> CREATE OR REPLACE PROCEDURE emp_P AS 2emp_row emp%ROWTYPE; 3BEGIN 4SELECT * INTO emp_row FROM emp WHERE job='CLERK'; 5dbms_output.put_line(emp_row.empno||' '||emp_row.ename); 6EXCEPTION 7WHEN TOO_MANY_ROWS THEN 8dbms_output.put_line('捕获到了TOO_MANY_ROWS异常'); 9dbms_output.put_line('SELECT语句检索到了多行数据'); 10END; 11/ 过程已创建。 3.4.2调用存储过程 存储过程创建后，就可以任意调用该过程了。 可以使用EXECUTE语句直接调用存储过程。EXECUTE语句的语法形式如下： EXEC［UTE］过程名; 【例321】调用执行例320所创建的存储过程。 SQL> SET SERVEROUTPUT ON SQL> EXEC emp_P; 捕获到了TOO_MANY_ROWS异常 SELECT语句检索到了多行数据 PL/SQL 过程已成功完成。 3.4.3存储过程的参数 在创建存储过程时，需要考虑存储过程的灵活应用，以便重新使用它们。通过使用“参数”可以使程序单元变得灵活。参数是一种向程序单元输入/输出数据的机制。存储过程可以接收和返回0到多个参数。Oracle有3种参数模式： IN、OUT和IN OUT。 带参数存储过程的创建语法格式如下： CREATE ［OR REPLACE］ PROCEDURE 过程名( 参数1 ［ IN | OUT | IN OUT］ 数据类型, 参数2 ［ IN | OUT | IN OUT］ 数据类型, … ) AS 声明部分; BEGIN 功能语句; EXCEPTION 异常处理; END; 1. IN参数 IN参数为输入参数。该参数值由调用者传入，并且只能被存储过程读取。 【例322】创建一个向dept表中插入新记录的存储过程dept_p。 SQL> CREATE OR REPLACE PROCEDURE dept_p( 2p_deptno IN number, 3p_dname IN varchar2, 4p_loc IN varchar2 5) AS 6BEGIN 7INSERT INTO dept VALUES(p_deptno,p_dname,p_loc); 8EXCEPTION 9WHEN DUP_VAL_ON_INDEX THEN 10dbms_output.put_line('重复的部门编号'); 11END; 12/ 过程已创建。 打开系统默认设置的输出功能，代码如下： SQL> SET SERVEROUTPUT ON SQL> EXEC dept_p(50,'HR','CHINA'); PL/SQL 过程已成功完成。 查看修改后的表，代码如下： SQL> SELECT * FROM dept WHERE deptno=50; 语句执行结果为： DEPTNODNAMELOC -------- -------- -------- 50 HR CHINA 2. OUT参数 OUT参数为输出参数，该类型的参数值由存储过程写入。OUT类型的参数适用于存储过程向调用者返回一个或多个数据的情况。 【例323】创建存储过程dept_p，该过程根据提供的部门编号返回部门的名称和地址。 SQL> CREATE OR REPLACE PROCEDURE dept_p( 2i_no IN dept.deptno%TYPE, 3o_name OUT dept.dname%TYPE, 4o_loc OUT dept.loc%TYPE 5) AS 6BEGIN 7SELECT dname,loc INTO o_name,o_loc FROM dept WHERE deptno=i_no; 8EXCEPTION 9WHEN NO_DATA_FOUND THEN 10o_name:='NULL'; 11o_loc:='NULL'; 12END; 13/ 过程已创建。 因为该存储过程要通过OUT参数返回值，这意味着在调用它时必须提供能够接收返回值的变量。因此，在调用前需要使用VARIABLE命令定义变量接收返回值，并且在调用存储过程时需要在变量前加冒号。 【例324】调用例323创建的存储过程，输出指定部门编号的部门名称和地址。 使用VARIABLE命令定义变量接收返回值，代码如下： SQL> VARIABLE v_dname varchar2(20); SQL> VARIABLE v_loc varchar2(10); SQL> EXEC dept_p(10,:v_dname,:v_loc); PL/SQL 过程已成功完成 。 输出部门名称和地址，代码如下： SQL> PRINT v_dname; V_DNAME -------------- ACCOUNTING SQL> PRINT v_loc; V_LOC -------------- NEW YORK 3. IN OUT参数 IN参数可以接收一个值，但是不能在过程中修改这个值。而对于OUT参数而言，它在调用过程时为空，在过程的执行中将为这个参数指定一个值，并在执行结束后返回。而IN OUT类型的参数同时具有IN参数和OUT参数的特性，在过程中可以读取和写入该类型参数。 【例325】使用IN OUT参数实现两个数的交换。 创建存储过程的代码如下： SQL> CREATE OR REPLACE PROCEDURE swap( 2p_num1 IN OUT number, 3p_num2 IN OUT number 4) AS 5var_temp number; 6BEGIN 7var_temp:=p_num1; 8p_num1:=p_num2; 9p_num2:=var_temp; 10END; 11/ 过程已创建。 实现两个数的交换，代码如下： SQL> SET SERVEROUTPUT ON SQL> DECLARE 2var_max number:=10; 3var_min number:=18; 4BEGIN 5IF var_max EXEC swap; 这是修改后的存储过程 PL/SQL 过程已成功完成 。 删除存储过程可以使用DROP语句，其语法格式如下： DROP PROCEDURE存储过程名; 【例327】删除例325创建的存储过程swap。 代码如下： SQL> DROP PROCEDURE swap; 过程已删除 。 3.4.5查看存储过程的错误 编写的存储过程难免会出现各种错误而导致编译失败。为了缩小排查错误的范围，Oracle提供了查看存储过程错误的语句，其语法格式如下： SHOW ERRORS PROCEDURE存储过程名; 【例328】创建一个有错误的存储过程，然后查看错误信息。 代码如下： SQL> CREATE OR REPLACE PROCEDURE test_proc 2AS 3BEGIN 4dbmm_output.put_line('这是有错误的存储过程，将dbms写成了dbmm'); 5END; 6/ 警告： 创建的过程带有编译错误。 查看错误的具体细节，代码如下： SQL> SHOW ERRORS PROCEDURE test_proc; PROCEDURE TEST_PROC 出现错误: LINE/COLERROR ------- -------- 4/2PL/SQL: Statement ignored 4/2 PLS-00201: 必须声明标识符 'DBMM_OUTPUT.PUT_LINE' 从错误提示可知，错误是由第4行引发的，正确的写法如下： dbms_output.put_line('这是有错误的存储过程，将dbms写成了dbmm'); 3.5小结 本章介绍了PL/SQL程序块定义部分、执行部分和异常处理部分的作用以及编写方法。注意： 编写PL/SQL程序块时，执行部分是必需的，而定义部分和异常处理部分是可选的。 PL/SQL程序块中的IF语句可执行简单条件判断、二重分支判断和多重分支判断。当使用IF语句时，注意END IF是两个词，而ELSIF是一个词。CASE语句可执行多重分支判断。WHILE语句和FOR语句执行循环控制操作的方法。 在使用SELECT语句查询数据库时，查询返回的数据存放在结果集中。用户在得到结果集后，需要逐行逐列地获取其中存储的数据，以便在应用程序中使用这些值。游标机制可完成此类操作。如果使用游标更新或删除数据，则在定义游标时必须指定FOR UPDATE子句，当更新或删除游标行时必须带有WHERE CURRENT OF子句。 当PL/SQL运行错误时，可以使用预定义异常和用户自定义异常的方法来处理发生的错误。 存储过程是用于执行特定操作的PL/SQL程序块，在需要时可以直接调用，提高代码的重用性和共享性。 SQL的用户可以是终端用户，也可以是应用程序。嵌入式SQL将SQL作为一种数据子语言嵌入高级语言中，利用高级语言和其他专门软件来弥补SQL语句在实现复杂应用方面的不足。动态SQL允许在执行一个应用程序时，根据不同的情况动态地定义和执行某些SQL语句。动态SQL可实现应用中的灵活性。SQL已经成为数据库的主流语言，其意义也远远超过数据库范围。 习题三 一、 选择题 1. 下列哪条语句允许检查UPDATE语句所影响的行数？() A. SQL%FOUNDB. SQL%ROWCOUNT C. SQL%COUNTD. SQL%NOTFOUND 2. 在定义游标时，使用FOR UPDATE子句的作用是()。 A. 执行游标B. 执行SQL语句的UPDATE语句 C. 对要更新表的列进行加锁D. 以上都不对 3. 对于游标FOR循环，以下哪一种说法是不正确的？() A. 循环隐含使用FETCH获取数据 B. 循环隐含使用OPEN打开记录集 C. 终止循环操作也就关闭了游标 D. 游标FOR循环不需要定义游标 4. 下列哪个关键字用来在IF语句中检查多个条件？() A. ELSEIFB. ELSEIFC. ELSIFD. ELSIFS 5. 如何终止LOOP循环，而不会出现死循环？() A. 当LOOP语句中的条件为FALSE时停止 B. 这种循环限定的循环次数会自动终止循环 C. EXIT WHEN语句中的条件为TRUE D. EXIT WHEN语句中的条件为FALSE 6. 如果PL/SQL程序块的可执行部分引发了一个错误，则程序的执行顺序将发生什么变化？() A. 程序将转到EXCEPTION部分运行 B. 程序将中止运行 C. 程序仍然正常运行 D. 以上都不对 二、 填空题 1. PL/SQL程序块主要包含3个主要部分： 声明部分、可执行部分和部分。 2. 使用显式游标主要有4个步骤： 声明游标、、检索数据、。 3. 在PL/SQL中，如果SELECT语句没有返回列，则会引发Oracle错误，并引发异常。 4. 查看下面的程序块，其中变量var_b的结果为： DECLARE var_a number:=1200; var_b number; BEGIN IF var_a>500 THEN var_b:=5; ELSIF var_a>1000 THEN var_b:=10; ELSE var_b:=8; END IF; END;