第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<var_min THEN

6swap(var_max,var_min);

7END IF;

8dbms_output.put_line('var_max='||var_max);

9dbms_output.put_line('var_min='||var_min);

10END;

11/

var_max=18

var_min=10



PL/SQL 过程已成功完成
。
3.4.4修改/删除存储过程

在Oracle中，如果要修改存储过程，应使用CREATE OR REPLACE PROCEDURE语句，也就是覆盖原有的存储过程。

【例326】修改例325创建的存储过程swap。

创建存储过程的代码如下：

SQL> CREATE OR REPLACE PROCEDURE swap AS

2BEGIN

3dbms_output.put_line('这是修改后的存储过程');

4END;

5/



过程已创建。

查看修改后的存储过程，代码如下：

SQL> 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;