本篇文章起,将会陆续介绍我在实际开发中经常使用到的Oracle的PL/SQL、游标、视图、存储过程以及触发器。其中会涉及一些概念性内容，要多多理解，切勿死记硬背。好了让咱们从PL/SQL开始吧，它是游标等的基础，篇幅较长。
一、认识PL/SQL
结构化查询语言（Structured Query Language，SQL）是用来访问和操作关系型数据库的一种标准通用语言，它属于第四代语言（4GL），简单易学，使用它可以很方便地调用相应语句来取得结果。该语言的特点就是非过程化。也就是说，使用SQL的时候不用指明执行的具体方法和途径，即不用关注任何的实现细节。但这种语言也存在一个问题，那就是在某些情况下满足不了复杂业务流程的需求，这就是第四代语言的不足之处。Oracle中的PL/SQL语言正是为了解决这一问题而诞生的，PL/SQL属于第三代的语言（3GL），也就是过程化的语言，同Java、C#一样可以关注细节，使用它可以实现复杂的业务逻辑，是数据库开发人员的利器。
二、PL/SQL的优势
（1）可以提高程序的运行性能
在执行标准的SQL时，只能一条一条地向Oracle服务器发送请求，客户端会数次地连接数据库服务器，而连接数据库的操作本身就是一个很耗费资源的过程，如果此时换用PL/SQL语句，可能只要请求一次数据库连接即可完成。区别如下图：
（2）可以使程序模块化
在程序块中可以实现一个或几个功能。
（3）可以采用逻辑控制语句来控制程序结构
PL/SQL可以利用条件或循环语句来控制程序的流程，这么做就大大地增加了PL/SQL的实用性，我们可以利用逻辑控制语句完成普通SQL语句完成不了的复杂业务
（4）能够利用处理运行时的错误信息
利用PL/SQL还可以处理一些程序上的异常，而不至于因终止SQL操作而造成调用SQL的展示页面上出现生硬的错误提示。
（5）良好的可移植性
PL/SQL可以成功地在不同的服务器中运行，例如，从Windows的数据库服务器下移植到Linux的数据库服务器下。也可把PL/SQL从一个Oracle版本移植到其他版本的Oracle中。
三、PL/SQL结构
PL/SQL程序的基本单位是块（block），而PL/SQL块很明确地分为三部分，其中包括声明部分、执行部分和异常处理部分
[DECLARE] --声明开始关键字
/*这里是声明部分，包括PL/SQL中的变量、常量以及类型等*/
BEGIN --执行部分开始的标志
/*这里是执行部分，是整个PL/SQL块的主体部分，该部分在PL/SQL块中
必须存在，可以是SQL语句或者程序流程控制语句等。*/
[EXCEPTION] --异常开始部分的关键字
/*这里是异常处理部分，当出现异常时程序流程可以进入此处。*/
END; --执行结束标志。其中的执行部分是必需的，而其余的两个部分则是可选的。
【示例一】包含声明，执行体和异常部分的结构----------该示例将从产品类型表CATEGORYINFO中查询产品类型“雨具”对应的产品的类型编码，并把该编码存储到变量中，最后输出到屏幕。
01 DECLARE
02 v_categoryid VARCHAR2(12);
03 BEGIN
04 SELECT CATEGORYID
05 INTO V_CATEGORYID
06 FROM CATEGORYINFO
07 WHERE CATEGORYINFO.CATEGORYNAME = '雨具';
08 DBMS_OUTPUT.PUT_LINE('雨具对应的编码是： ' || v_categoryid);
09
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 DBMS_OUTPUT.PUT_LINE('没有对应的编码！');
13 WHEN TOO_MANY_ROWS THEN
14 DBMS_OUTPUT.PUT_LINE('对应数据过多，请确认！');
15
16 END;
17 /【代码解析】
- 第1～2行是声明部分。
- 第3～16行是执行体部分，不过异常部分包含在执行体内。
- 第10～14行属于异常处理部分。
- 第4～7行代码表示把“雨具”对应的产品编码查询出来后放入变量v_categoryid中。
- 第11和13行表示出现NO_DATA_FouNL和TOO_MANY_ROWS两种异常时会输出相应的提示语句。
CATEGORYINFO表如下：
PRODUCTINFO表如下：
四、PL/SQL的基本规则
好的程序会让人眼前一亮，而阅读质量差的程序会增加工作时间，降低工作效率。所以要开始养成好习惯，从学习并遵循规则开始。
1）PL/SQL中允许出现的字符集：
■ 字母，包括大写和小写。
■ 数字，即0～9。
■ 空格、回车以及制表符。
■ 符号包括+、-、 *、 /、 <、 >、 =、 !、 ～、 ^、 ;、 :、 .、 ’、 @、 %、 ,、 "、 #、 $、 &、 _、 |、 (、 )、 [、 ]、 {、 }、 ?。
2）下面列出一些PL/SQL必须遵守的要求：
- 标识符是不区分大小写的。例如，TEST同Test、test是一样的。所有的名称在存储时都被修改成大写，这点小伙伴们需要注意。
- 标识符中只允许出现字母、数字、下划线，并且以字母开头。
- 标识符最多30个字符。
- 不能使用保留字。如与保留字同名必须使用双引号括起来。
- 使用分号结束语句。即使多条语句在同一行，只要它们都正常结束，那么就没有问题。在语句块的结束标志END后面同样需要分号。
- 语句中关键字、标识符、字段的名称以及表的名称等都需要用空格分隔分开。
- 字符类型和日期类型需要使用单引号括起。
五、PL/SQL变量、常量的类型及使用语法
所谓变量就是它所表示的值是可以变化的，而常量就是当初始化后，其值不可以再改变。PL/SQL是一种强类型的语言，所以当使用变量或常量的时候必须首先进行声明，否则系统就会提示错误。下面是声明变量和常量的语法介绍
1.声明变量的语法结构
01 variable_name datatype
02 [
03 [ NOT NULL]
04 {:= | DEFAULT} expression
05 ] ;【语法说明】
■ variable_name：表示变量的名称。变量名称可以根据小伙伴们的实际需要自行定义。
■ datatype：变量的数据类型。
■ 第2～5行表示可选部分。如果没有这部分，那么只需要变量的名称以及对应的数据类型即可，声明的时候变量可以不赋值。
■ NOT NULL项：表示非空约束。[]代表可选
■ {:= | DEFAULT}：当使用NOT NULL属性时，大括号里的内容为必需的，表示二选一；“:=”表示赋值；DEFAULT表示默认值。
■ expression：表示变量存储的值，该项可以是表达式。
2.常量声明的具体语法结构
01 constant_name CONSTANT datatype
02 [NOT NULL]
03 { := | DEFAULT } expression;【语法说明】
- constant_name：表示声明的常量名称。
- CONSTANT：如果表示常量，该项必须有，否则为变量声明。
- datatype：常量的数据类型。
- NOT NULL：表示常量值非空。
- { := | DEFAULT }：表示常量必须显式地为其赋值，具体方式同变量一样。
- expression：值或表达式。
- 变量和常量的语法结构相似，其中expression表示的含义完全一样，都可以是如下类型的表达式：
- 字符型表达式。
- 值型表达式。
- 日期型表达式。
- 布尔型表达式。
- 直接的一个值。
变量和常量的数据类型可以概括性地分为如下三种类型：
■ 标量类型变量：单一类型，不存在组合。
■ 复合类型变量：由几种单一类型组合而成的一个结构体。
■ 引用类型变量：使用一个其他数据项的引用。
在日常开发中这三种类型最常用的就是前两种。
3.标量类型的变量
标量类型是最简单的变量类型，也是普通开发者最常用的一种变量类型，它本身是单一的值，不包含任何的类型组合。下面对这几种类型做详细的介绍。
1）数值类型，主要用来存放数字型的数据。最常用的就是NUMBER、PLS_INTEGER、BINARY_INTENER类型，还有一个类型是SIMPLE_INTEGER。
2）字符类型，可以用来存储单个的字符或字符串数据的类型。主要有CHAR、VARCHAR2(VARCHAR)、NCHAR、NVARCHAR2和LONG类型。
3）布尔类型，它不能用做定义表中的数据类型。但PL/SQL中该类型可以用来存储逻辑上的值。它有3个值可选：TRUE、FALSE、NULL。
4）日期类型，主要有DATE和TIMESTAMP。
5）使用%TYPE方式定义变量类型。这种定义变量类型的方式和前面所介绍的直接定义变量类型的方式有所不同，它利用已经存在的数据类型来定义新数据的数据类型。例如，当定义多个变量或常量时，只要前面使用过的数据类型，后面的变量就可以利用%TYPE进行引用。最常见的就是把表中字段类型作为变量或常量的数据类型。使用此种方式有如下几点好处：
■ 利用%TYPE定义的变量或常量的数据类型都一致，当需要变动时，只要改变被引用的变量或常量的数据类型即可，其他引用处的数据类型自然就变了，避免了逐条修改的麻烦。
■ 使用PL/SQL语句块通常都是操作数据库表的数据，操作过程中避免不了要进行数据的传递，这时变量利用%TYPE就可以完全兼容所提取的数据，而不至于出现数据溢出或不符的情况。
■ 当利用%TYPE定义数据类型时，可以保证变量的数据类型和表中的字段类型是同步的，当表的字段类型发生变化时，PL/SQL块变量的数据类型不需要修改。
【示例二】变量定义示例--------演示了如何定义标量类型的变量。
01 DECLARE
02 v_productid productinfo.productid%TYPE; --产品ID
03 v_productname VARCHAR2(20); --产品名称
04 v_productprice NUMBER(8,2); --产品价格
05 v_quantity NUMBER(10); --数量
06 v_desperation CONSTANT v_productname%TYPE:='测试'; --测试
07
08 v_spitgr SIMPLE_INTEGER := 99.9;
09 v_long LONG :='LONG类型测试' ;
10 v_date DATE :=SYSDATE;
11 BEGIN
12 SELECT productid, productname, productprice, quantity
13 INTO v_productid, v_productname, v_productprice, v_quantity
14 FROM productinfo
15 WHERE productid = '0240040001';
16
17 DBMS_OUTPUT.PUT_LINE('v_productid = ' || v_productid);
18 DBMS_OUTPUT.PUT_LINE('v_productname = ' || v_productname
19 || ' 长度='||LENGTH(v_productname));
20 DBMS_OUTPUT.PUT_LINE('v_productprice = ' || v_productprice);
21 DBMS_OUTPUT.PUT_LINE('v_quantity = ' || v_quantity);
22 DBMS_OUTPUT.PUT_LINE('v_desperation = ' || v_desperation);
23 DBMS_OUTPUT.PUT_LINE('v_spitgr = ' || v_spitgr);
24 DBMS_OUTPUT.PUT_LINE('v_long = ' || v_long);
25 DBMS_OUTPUT.PUT_LINE('v_date = ' || v_date);
26 END;【代码解析】
■ 该语句块中的声明部分采用了右对齐的方式。
■ 第2行利用%TYPE的方式声明变量，表示变量v_productid的类型同表productinfo中的productid字段的数据类型一致。
■ 第3行表示产品名称的数据类型是长度为20的VARCHAR2型。前面介绍过，该类型能够可变地存储数据，可以利用LENGT()函数查看变量的真实长度。
■ 第4～5行是NUMBER类型的不同使用方式。
■ 第6行利用%TYPE引用了v_productname的数据类型，并且声明了一个常量。
■ 第8行初始值是99.9，但由于SIMPLE_INTEGER数据类型只能表示整数，所以变量v_spitgr的值应为100。
■ 第11～26行属于执行体部分。
■ 第12～15行表示从产品表中查询数据并存储到相应变量中。
■ 第17～25行表示输出结果到屏幕上，用于验证变量中存储的数据。
4.复合类型的变量
所谓复合类型的变量，就是变量中包含多个元素，可以存储多个值。这种变量类型的使用方式同标量类型稍有差异，复合类型需要先定义，然后才能声明该类型的变量。复合类型中最常用的是三种类型，即记录类型、索引表类型和VARRAY数组。由于篇幅，这里只介绍记录类型。
该类型可以包含一个或多个成员，而每个成员的类型可以不同，成员可以是标量类型，也可以是引用其他变量的类型（使用%TYPE）。该类型的优点是比较适合处理查询语句中有多个列的情况。最常用的就是在调用某张表中的一行记录时，可以利用该类型变量存储这行记录。如果想要调用其中的数据，就用“变量名称.成员名称”的格式进行调用。“记录类型”有两种声明方式。
4.1第一种声明方式
01 TYPE type_name IS RECORD
02 (
03 field_name datatype
04 [
05 [ NOT NULL ]
06 { := | DEFAULT } expression
07 ]
08 [, field_name datatype [ [ NOT NULL ] { := | DEFAULT } expression ]...
09 ) ;【语法说明】
■ 第1行的type_name表示定义的记录类型的名称。其余都是关键字。
■ 第3行的field_name表示行记录的成员名称，datatype表示行记录成员的数据类型。
■ 第5行的[ NOT NULL ]表示可选部分，可以约束记录的成员非空。
■ 第6行的{ := | DEFAULT }表示为记录成员赋值。expression为赋值表达式。
■ 第8行同第3～7行表示的含义相同，这里这么写是为了让小伙伴们明白，记录类型里可以有多个成员。
【示例三】定义“记录类型”的变量--------演示如何定义PL/SQL记录类型的变量
01 DECLARE
02 TYPE product_rec IS RECORD
03 (
04 v_productid productinfo.productid%TYPE, --产品ID
05 v_productname VARCHAR2(20), --产品名称
06 v_productprice NUMBER(8,2) --价格
07 );
08
09 v_product product_rec; --记录类型变量
10 BEGIN
11 SELECT productid, productname, productprice
12 INTO v_product
13 FROM productinfo
14 WHERE productid = '0240040001';
15
16 DBMS_OUTPUT.PUT_LINE('productid = ' || v_product.v_productid);
17 DBMS_OUTPUT.PUT_LINE('productname = ' || v_product.v_productname);
18 DBMS_OUTPUT.PUT_LINE('productid = ' || v_product.v_productprice);
19 END;
20 /【代码解析】
■ 第1～9行是PL/SQL块的声明部分，第10～19行是PL/SQL块的执行体部分。
■ 第2～7行属于声明行记录类型部分。第2行的product_rec是行记录的名称。
■ 第4～6行表示在行记录中包含了3个成员。这3个成员的声明方式同标量类型声明方式一致。从中可以看出记录类型数据的成员也可以利用%TYPE来声明变量类型。
■ 第9行表示声明变量v_product，它的数据类型是product_rec类型。
■ 第11～14行利用SELECT…INTO语句为变量赋值，这里INTO后面直接是v_product记录类型，赋值会依据声明记录类型时里面成员的顺序依次赋值。这种赋值方式比较方便。
■ 第16～18行表示输出结果。
4.2利用%ROWTYPE声明记录类型数据
这种声明方式可以直接引用表中的行作为变量类型。它同%TYPE很类似，使用它可以避免因表中字段的数据类型的改变而导致PL/SQL块出错的问题。
【示例四】%ROWTYPE--------演示如何利用%ROWTYPE声明记录类型的变量类型。
01 DECLARE
02 v_product productinfo%ROWTYPE;
03 BEGIN
04 SELECT * INTO v_product
05 FROM productinfo
06 WHERE productid = '0240040001';
07 DBMS_OUTPUT.PUT_LINE('productid = ' || v_product.productid);
08 DBMS_OUTPUT.PUT_LINE('productname = ' || v_product.productname);
09 DBMS_OUTPUT.PUT_LINE('productid = ' || v_product.productprice);
10 END;
11 /【代码解析】
■ 第2行声明了名称为v_product的变量，其数据类型是表productinfo的行记录类型。这里利用了%ROWTYPE方式声明变量数据类型。
■ 第4～6行表示把查询出来的数据存储到变量v_product中。其中从第4行语句可以看出，这里查询了一行记录，它可以直接把这行记录放进v_product变量中。
■ 第7～9行表输出结果，此提取结果的方式就是前面介绍过的“变量名称.成员名称”方法。
六、表达式
数据库中经常使用表达式来计算结果，尤其是在变量和常量的使用过程中。在前面我们已经接触过表达式的使用，它和普通编程语言的表达式很类似，根据数据操作及数据类型的不同可以将表达式分为如下几类：
1.数值表达式
■ 加号（+）；
■ 减号（-）；
■ 乘号（*）；
■ 除号（/）；
■ 乘方（**）。
2.关系表达式
■ 等于号（=）；
■ 小于号（<）；
■ 大于号（>）；
■ 小于等于号（<=）；
■ 大于等于（>=）；
■ 不等于号（!= ）和（<>）。
3.逻辑表达式
■ 逻辑非（NOT）；
■ 逻辑或（OR）；
■ 逻辑与（AND）。
注意 关系表达中的等于号和赋值符号“:=”是不同的。
七、结构控制
1.IF条件控制语句
流程图如下
在IF语句中有三种使用方式：IF…、IF…ELSE…、IF…ELSIF…。这三种方式可以根据实际的业务灵活选择，跟编程语言类似，直接看示例
【示例五】IF控制语句--------示例首先判断产品表中产品的价格范围并做出提示，然后在价格范围的基础上进行产品数量的判断，给出产品数量是否满足需求的提示
01 DECLARE
02 v_product productinfo%ROWTYPE;
03
04 BEGIN
05 SELECT * INTO v_product
06 FROM productinfo
07 WHERE productid = '0240040001';
08
09 IF v_product.productprice >3000 THEN
10 DBMS_OUTPUT.PUT_LINE('该产品属于高价格产品');
11 IF v_product.quantity > 50 THEN
12 DBMS_OUTPUT.PUT_LINE('该产品数量高于50，不缺货。');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE('该产品数量低于50，需要补货。');
15 END IF;
16 ELSIF v_product.productprice <3000 AND v_product.productprice>1000 THEN
17 DBMS_OUTPUT.PUT_LINE('该产品属于中间价格产品');
18 IF v_product.quantity > 80 THEN
19 DBMS_OUTPUT.PUT_LINE('该产品数量高于80，不缺货。');
20 ELSE
21 DBMS_OUTPUT.PUT_LINE('该产品数量低于80，需要补货。');
22 END IF;
23 ELSE
24 DBMS_OUTPUT.PUT_LINE('该产品属于低价格产品');
25 IF v_product.quantity > 200 THEN
26 DBMS_OUTPUT.PUT_LINE('该产品数量高于200，不缺货。');
27 ELSE
28 DBMS_OUTPUT.PUT_LINE('该产品数量低于200，需要补货。');
29 END IF;
30 END IF;
31
32 DBMS_OUTPUT.PUT_LINE('IF...ELSE...已结束！');
33 END;
34 /【代码解析】
■ 本示例的代码看起来比较多，但结构相当简单，核心部分为两个嵌套的IF条件语句。
■ 该示例外层使用了IF…ELSIF…的结构，内层使用了IF…ELSE的结构。
■ 第2行表示声明变量，类型为记录类型。
■ 第5～7行表示为该变量赋值
■ 第9～30行表示一个IF语句的结构，这是最外层的IF条件判断，它实现了判断产品价格的功能。
■ 第11～15行表示第二层的IF条件语句，它判断了产品的数量，并给出提示。
本示例包含IF示例较多并且IF嵌套语句的使用可以帮我们完成更为复杂的逻辑，但不建议过多地使用该类型语句。其原因主要有两点：
1）过多的嵌套使用会影响其执行效率。
2）过多的嵌套使用会影响阅读效果，如果允许，应尽量使用单一的IF条件语句。
2.CASE条件控制语句
CASE语句同IF语句类似，也是根据条件选择对应的语句进行执行。流程图如下：
CASE语句可以分为以下两种类型：
1）一种是简单的CASE语句，它给出一个表达式，并把表达式结果同提供的几个可预见的结果做比较，如果比较成功，则执行对应的语句序列。
2）另一种是搜索式的CASE语句，它会提供多个布尔表达式，然后选择第一个为TRUE的表达式，执行其对应的脚本。
【示例六】简单CASE语句示例----------演示简单CASE语句的使用方式。要求利用产品ID得到对应的产品类型编码，然后利用CASE语句找到产品类型编码对应的产品类型，并输出到屏幕
01 DECLARE
02 v_categoryid VARCHAR2(12);
03
04 BEGIN
05 SELECT category INTO v_categoryid
06 FROM productinfo
07 WHERE productid = '0240040001';
08
09 CASE v_categoryid
10 WHEN '010001000'||'1' THEN
11 DBMS_OUTPUT.put_line(v_categoryid || '对应雨具');
12 WHEN '0100030001' THEN --与第14行值相同。
13 DBMS_OUTPUT.put_line(v_categoryid || '对应电视');
14 WHEN '0100030001' THEN
15 DBMS_OUTPUT.put_line(v_categoryid || '对应路由器');
16 WHEN '0100030002' THEN
17 DBMS_OUTPUT.put_line(v_categoryid || '对应洗衣机');
18 ELSE
19 DBMS_OUTPUT.put_line('没有对应的产品类型！');
20 END CASE;
21
22 DBMS_OUTPUT.put_line('CASE结构已经完成。');
23 END;
24 /【代码解析】
■ 第2行声明了变量v_categoryid。
■ 第5～7行表示为变量赋值。
■ 第9～20行是一个CASE结构。其中，v_categoryid依次与第10、12、14、16行WHEN子句中的值进行对比，如果相等，则执行其对应的输出语句，而CASE语句也将执行完毕。
■ 当没有与v_categoryid值相同的项时，会执行第18行ELSE部分。如果没有ELSE子句而且也没有与v_categoryid值相同的项时，系统会给出CASE_NOT_FOUND异常。
【示例七】搜索式CASE语句使用方式--------演示搜索式CASE语句的使用方法。要求根据产品ID得到对应的价格，利用CASE语句判断价格所在的范围并给出输出提示
01 DECLARE
02 v_productprice NUMBER(8,2);
03
04 BEGIN
05 SELECT productprice
06 INTO v_productprice
07 FROM productinfo
08 WHERE productid = '0240040001';
09
10 CASE
11 WHEN v_productprice <= 1000 THEN
12 DBMS_OUTPUT.put_line('低价产品，价格是 '|| v_productprice);
13 WHEN v_productprice > 1000 AND v_productprice <= 3000 THEN
14 DBMS_OUTPUT.put_line('中价产品，价格是 '|| v_productprice);
15 WHEN v_productprice > 3000 THEN
16 DBMS_OUTPUT.put_line('高价产品，价格是 '|| v_productprice);
17 ELSE
18 DBMS_OUTPUT.put_line('错误价格！价格是' || v_productprice);
19 END CASE;
20
21 DBMS_OUTPUT.put_line('CASE结构已经完成。');
22 END;
23 /【代码解析】
■ 第2行声明了变量v_productprice，用于存放价格。
■ 第5～8行表示为变量v_productprice赋值
■ 第10～19行是一个CASE结构。程序将依次检测第11、13、15行的布尔表达式是否为TRUE。一旦为TRUE，它所在的WHEN子句将被执行。
3. LOOP循环控制语句
LOOP语句也叫循环语句，它能让我们重复地执行指定的语句块。LOOP语句有以下四种形式：
■ LOOP
■ WHILE LOOP
■ FOR LOOP
■ CURSOR FOR LOOP
1)LOOP
该形式的语句属于LOOP循环控制语句当中最基本的结构，它会重复不断地执行LOOP和END LOOP之间的语句序列。由于基本的LOOP语句本身没有包含中断循环的条件，所以通常情况下都是和其他的条件控制语句一起使用，即利用EXIT，GOTO等中断LOOP循环。当然，异常也能使LOOP语句中断。其流程见下图：
【示例八】LOOP+EXIT…WHEN--------演示LOOP的使用方式，要求每次循环都要为变量增加1，并把变量输出到屏幕中。同时，当变量值大于5的时候，终止循环
01 DECLARE
02 v_num NUMBER(8) := 1;
03 BEGIN
04 <<basic_loop>>
05 LOOP
06 DBMS_OUTPUT.put_line('当前v_num变量的值是： ' || v_num);
07 v_num := v_num + 1;
08 EXIT basic_loop WHEN v_num > 5;
09 END LOOP ;
10
11 DBMS_OUTPUT.put_line('退出！当前v_num的值是 ' || v_num);
12 DBMS_OUTPUT.put_line('LOOP循环已经结束！ ');
13 END ;
14 /【代码解析】
■ 第1行为变量声明语句，其默认值为1。
■ 第4行是LOOP循环的标签，可以选择性地填写。
■ 第5～12行属于一个基本的LOOP结构。
■ 第7行表示每循环一次都为变量增加1。
■ 第8行利用EXIT…WHEN语句判断变量v_num是否大于5，如果大于5，则退出basic_loop循环。这里，完成判断并退出，只需要一行脚本。
2）WHILE…LOOP
WHILE…LOOP结构的语句本身可以终止LOOP循环，当 WHILE后面的布尔表达式为TRUE时，LOOP和END LOOP之间的语句集将执行一次，而后会重新判断WHILE后面表达式是否为TRUE。其流程见下图：
【示例九】WHILE...LOOP----------要求输出20以内能被3整除的数
01 DECLARE
02 v_num NUMBER(8) := 1;
03 BEGIN
04 DBMS_OUTPUT.put('当前v_num变量的值是： ' );
05 << while_loop>>
06 WHILE v_num < 20
07 LOOP
08 IF MOD(v_num,3) = 0 THEN
09 DBMS_OUTPUT.put(v_num ||' ' );
10 END IF;
11
12 v_num := v_num + 1;
13 END LOOP ;
14
15 DBMS_OUTPUT.put_line('退出！当前v_num的值是 ' || v_num);
16 DBMS_OUTPUT.put_line('LOOP循环已经结束！ ');
17 END ;
18 /【代码解析】
■ 第2行表示声明变量v_num，其默认值为1。
■ 第5～13行是WHILE…LOOP语句。
■ 第5行是WHILE…LOOP语句的标签。
■ 第6行表示当变量v_num<20时，执行下面的LOOP循环。
■ 第8～10行利用SQL函数判断v_num是否能被3整除。如果能整除，则把该值输出。
■ 第12行表示每次循环都为变量v_num增加1。这样就可以判断1～20之间所有的数是否能被3整除。而且当变量增加到20时，循环会终止退出。
3）FOR…LOOP
FOR…LOOP语句可以循环遍历指定范围内的整数。具体范围被FOR和LOOP关键字封闭。当第一次进入循环时，其循环范围就会被确定，并且以后不会再次计算。每循环一次，其循环指数将会增加1。
【示例十】FOR…LOOP--------下面算出1～20之间所有整数的和
01 DECLARE
02 v_num NUMBER(8) := 0;
03 BEGIN
04 DBMS_OUTPUT.put('1～20之间整数和 ： ' );
05 <<for_loop>>
06 FOR inx IN 1..20 LOOP
07 v_num := v_num + inx;
08 END LOOP ;
09
10 DBMS_OUTPUT.put_line(v_num);
11 DBMS_OUTPUT.put_line('LOOP循环已经结束！ ');
12 END ;
13 /【代码解析】
■ 第2行表示声明变量v_num。
■ 第5～8行是一个FOR…LOOP循环结构。
■ 第6行表示循环范围，本例为从1到20。其中，1是循环范围的下标界，20是循环范围的上标界，以“..”连接，inx为循环计数器名。当inx等于20时，程序会自动退出循环。
■ 第7行表示求出1到20之间整数的和。
FOR…LOOP循环当中的循环范围可以动态地获取，例如，示例中的下标界完全可以用数值型的变量替代。
4）CURSOR FOR LOOP
标通常都是迭代结果集，在PL/SQL中这个过程中可以使用更简单的方式实现，CURSOR FOR LOOP不需要特别的声明变量，就可以提取行对象类型的数据，也可以按照类似示例4中提取数据的方式得到列数据。
【示例十一】CURSOR FOR LOOP
01 DECLARE
02 CURSOR cfl IS SELECT productname, productprice FROM PRODUCTINFO
03 WHERE productprice > 1200;
04 BEGIN
05 FOR curcfl IN cfl
06 LOOP
07 DBMS_OUTPUT.PUT_LINE('名称： ' || curcfl.productname
08 || ' 产品价格： ' || curcfl.productprice);
09 END LOOP;
10 END;【代码解析】
■ 第1～3行表示声明游标并关联查询。
■ 第5行把游标返回的数据放到curcfl中，该数据的类型是%ROWTYPE类型。
■ 第6～9行用于迭代输出数据。游标部分，请小伙伴们参考 “游标”篇播客。
八、PL/SQL中使用DML和DDL语言
由于PL/SQL对标准SQL是兼容的，因此PL/SQL当中允许使用SQL命令，但有些命令的使用方式上有所改变。DML语句在PL/SQL中的使用方式和单独执行DML操作没有区别，但SELECT和DDL的使用方式却都有所改变。
1.DML语句使用
【示例十二】INSERT语句--------要求判断产品类型编码是否存在，如果不存在，则增加该编码类型的记录。
01 DECLARE
02 v_catgid VARCHAR2(10) := 0;
03 v_bol BOOLEAN := TRUE;
04 BEGIN
05 SELECT CATEGORYID INTO v_catgid
06 FROM CATEGORYINFO
07 WHERE CATEGORYNAME = '电脑';
08 DBMS_OUTPUT.PUT_LINE('电脑对应的编码存在：' || v_catgid);
09
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 IF v_bol THEN
13 DBMS_OUTPUT.PUT_LINE('没有对应的编码，为其增加该产品类型');
14 INSERT INTO CATEGORYINFO VALUES ('0100000001','电脑');
15 COMMIT;
16 END IF;
17 WHEN TOO_MANY_ROWS THEN
18 DBMS_OUTPUT.PUT_LINE('对应数据过多，请确认！');
19 END ;
20 /【代码解析】
■第5～7行查询产品类型名称为“电脑”的产品类型编码。如果对应的编码存在，则将对应的编码输出到屏幕上。如果不存在，则在SELECT…INTO类型语句中会引发NO_DATA_FOUND异常，程序流程会发生跳转。此时会执行增加产品类型“电脑”对应的产品类型编码的操作。
■ 从第10行开始属于PL/SQL块的异常部分。
■ 第11行的NO_DATA_FOUND是Oracle的预定义异常，这在后面的相关章节中会做详细介绍。
■ 第14行表示，当CATEGORYINFO表中没有对应记录时，就增加该记录。该语句只能在触发NO_DATA_FOUND异常时被执行。
■ 第15行表示提交事务，否则由于数据库的一致性，其他的用户将看不到该记录。关于事务，后面章节会有详细介绍。
■ 第17行的TOO_MANY_ROWS是SELECT…INTO语句可能引发的另一个异常，即当返回多条记录时，会触发该异常。
注意 SELECT…INTO语句是SELECT语句在PL/SQL中的使用方法。
2.DDL语句使用
PL/SQL中也允许使用DDL操作语句，但其使用方法和DML有所不同。DDL语句要想在PL/SQL块中使用，需要通过一条命令来执行，该命令是EXECUTE IMMEDIATE，即利用它可以执行动态的SQL语句，即利用它不仅仅可以执行DDL语句，也可以执行DML语句。该命令替代了DBMS_SQL包，其性能也有所提高，但是依然不建议过程使用该命令。
【示例十三】DDL操作示例----------要求在PL/SQL块中动态创建表TAB_TEST。表结构见下图：
01 DECLARE
02 pc_createStr VARCHAR2(200);
03
04 BEGIN
05
06 pc_createStr := 'CREATE TABLE TAB_TEST
07 (
08 OPERID VARCHAR2(10) PRIMARY KEY,
09 OPERNAME VARCHAR2(30),
10 OPERDATE DATE
11 ) ';
12
13 EXECUTE IMMEDIATE pc_createStr;
14 END;
15 /【代码解析】
■ 第2行表示声明变量pc_createStr，该变量的长度为200，用于存储DDL语句。
■ 第6～11行表示为变量pc_createStr赋值，其中值为创建表TAB_TEST的脚本。
■ 第13行表示利用EXECUTE IMMEDIATE命令动态创建表TAB_TEST。
九、PL/SQL中的异常
1.PL/SQL运行过程中有可能会出现错误，这些错误有的来自程序本身，也有的来自开发人员自定义的数据，而所有的这些错误我们称之为异常（编译时的错误不能称为异常）。
为了使程序具有更好的可阅读性和健壮性，PL/SQL采用了捕获并统一处理异常的方式。当异常发生时，程序会无条件跳转到异常块处，将控制权限交给异常处理程序，异常处理程序将进行异常匹配，如果当前的块内没有对应的异常名称，则会将异常传至当前程序的上一层程序中查找，如果一直向上查找却依然没有找到对应的处理方式，则该异常会被传至当前的主机调用中，并且运行的程序也会中断。
【示例十四】异常演示-----------演示除数为0的异常
01 DECLARE
02 v_rslt NUMBER(10) := 0;
03 BEGIN
04 v_rslt := 100/0;
05 DBMS_OUTPUT.PUT_LINE('结果是： ' || v_rslt);
06 END;
07 /【代码解析】
■ 第4行表示求除法运算的结果，不过这里在除数位置出现了0，由于除数为0在四则混合运算中是不被允许的，因此该行语句在编译程序时会正常通过，但在程序执行时会出现错误提示，也就是有异常抛出。
2.处理异常的语法
01 EXCEPTION
02 WHEN exception1 [OR exception2...] THEN --异常列表
03 statement [ statement ]... --语句序列
04 [WHEN exception3 [OR exception4...] THEN --异常列表
05 statement [ statement ]...]
06 [WHEN OTHERS THEN
07 statement [ statement ]...]【代码解析】
■ 第1行的EXCEPTION表示声明异常块部分，它是异常处理部分开始的标志。
■ 第2行的WHEN后面接的是异常名称列表，THEN后面接的是语句序列，也就是说当发生的异常和异常列表里的异常相匹配时，就可以执行指定的语句序列，以完成出现异常后的操作。
■ 允许多个WHEN关键字。
■ 第6行的WHEN OTHERS THEN语句通常是异常处理的最后部分，它表示如果抛出的异常在前面没有被捕获，那么将在这个地方被捕获。该语句可以省略，但省略时没有被捕获的异常将会传递到主机环境。
Oracle中的异常可以分为3类：
■ 预定义异常
■ 非预定义异常
■ 自定义异常
其中预定义异常和非预定义异常都与Oracle中的错误有关，当出现错误时会自动触发，而自定义异常与Oracle的错误没有关系，它是人为地为某种特殊情况定义的异常，同时不会自动触发，需要显式的操作来触发。此篇自定义异常暂不讨论。
1）预定义异常
在Oracle中为每个错误设置了一个错误号，而捕获异常则需要异常名称。Oracle提供了一些已经定义好名称的常用异常，这些就是预定义异常。例如，前面在使用SELECT…INTO语句时，如果返回的数据超过一条记录就会触发TOO_MANY_ROWS异常。下图列出了一些常用的预定义异常。
【示例十五】预定义异常------------要求当除数为0时，捕捉异常
DECLARE
v_rslt NUMBER(10) := 0;
BEGIN
v_rslt := 100/0;
DBMS_OUTPUT.PUT_LINE('结果是： ' || v_rslt);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('除数是零！默认用1替代除数，结果是：' || 100/1);
END;执行过程中出现除数为0的情况时，程序会马上进入异常捕获部分，当发生的异常和异常列表中的异常名称匹配成功时，执行WHEN…THEN下的语句序列，这样就可以避免因程序中断而产生的问题。
注意 异常匹配的顺序是从上到下。
2）非预定义异常
Oracle中的异常更多的都是非预定义异常。也就是说，它们只有错误编号和相关的错误描述。而没有名称的异常是不能被捕捉的。为了解决该问题，Oracle允许开发人员为这样的异常添加一个名称，使得它们能够被异常处理模块捕捉到。
为一个非预定义异常定义名称需要如下两步：
1）声明一个异常的名称。
2）把这个名称和异常的编号相互关联。
Oracle处理预定义异常和非预定义异常的方法并没有区别。
【示例十六】关联非预定义异常-----------由于产品表PRODUCTINFO中的产品类型引用了产品类型表CATEGORYINFO中的编码，所以当修改产品类型编码时有可能造成PRODUCTINFO表中产生垃圾数据。为了避免这种情况，表PRODUCTINFO中可以使用外键约束。这时如果直接修改PRODUCTINFO表中的产品类型编码,就有可能导致ORA-02291错误。
01 DECLARE
02 v_ctgy VARCHAR2(10);
03
04 my_2291_exp EXCEPTION;
05 PRAGMA EXCEPTION_INIT(my_2291_exp, -2291);
06
07 BEGIN
08 v_ctgy := '1111111111';
09 UPDATE PRODUCTINFO SET PRODUCTINFO.CATEGORY = v_ctgy;
10
11 EXCEPTION
12 WHEN my_2291_exp THEN
13 DBMS_OUTPUT.PUT_LINE('违反完整约束条件,未找到父项关键字!' );
14 DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
15 DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
16 ROLLBACK;
17
18 END;
19 / 【代码解析】
■ 第4行表示声明异常的名称：my_2291_exp。
■ 第5行利用PRAGMA EXCEPTION_INIT函数把异常名称和数据库错误号关联起来。
■ 第9行表示修改表PRODUCTINFO中产品类型编码的数据。
■ 从第11行开始为异常处理块，第12行为异常名称列表，这里使用了my_2291_exp。也就是说，当出现-2291这个异常时，会被第12行代码捕捉。
■ 第15行中的SQLERRM表示错误信息。
■ 第16行中的SQLCODE为错误代码。
十、PL/SQL函数
在Oracle中函数由PL/SQL定义，用于操作各种数据项目完成计算并返回计算的结果。利用函数可以把复杂的计算过程封装起来，避免所有的开发人员都要面对复杂的算法。
1.函数的组成
函数要有以下几个部分组成：
■ 输入部分。函数中允许有输入的参数，在调用函数时需要给这些参数赋值。
■ 逻辑计算部分。在函数内部将完成对各种数据项目的计算，它可以进行很简单的算术运算，也可以调用多个SQL内置函数或自定义函数进行运算。
■ 输出部分。函数要求都有返回值。
2.函数的语法
01 CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
02 [
03 ( parameter_declaration [, parameter_declaration] )
04 ]
05 RETURN datatype
06 { IS | AS }
07 [ declare_section ]
08 BEGIN
09 statement [ statement | pragma ]...
10 [ EXCEPTION exception_handler [ exception_handler ]... ]
11 END [ name ] ; 【语法说明】
■ [ OR REPLACE ]：表示覆盖同名函数。
■ FUNCTION：关键字，表示创建的是函数。
■ schema：模式名称。
■ function_name：函数名称。
■ parameter_declaration：函数的参数。参数有IN、OUT、IN OUT三种类型
■ RETURN datatype：表示函数的返回值类型。
■ { IS | AS }：二选一。该项之后是PL/SQL块。
■ declare_section：语句块的变量声明部分。
■ 第9行表示语句或子程序。
■ 第10行处表示异常处理部分。
函数的作用是计算数据并返回结果，所以在PL/SQL块中至少有一个RETURN语句。函数不能用来操作数据库，这点和SQL内置函数一样。在当前模式下创建函数需要拥有CREATE PROCEDURE系统权限。
【示例十七】创建函数 -------------创建根据当前的产品数量对产品价格进行打折计算的函数。如果产品数量低于50就打七五折，如果产品数量等于或高于50的则打九折
01 CREATE FUNCTION pric
02 (v_pric IN NUMBER,v_qnty IN NUMBER)
03 RETURN NUMBER
04 IS
05 BEGIN
06 IF v_qnty < 50 THEN
07 RETURN(v_pric * 0.75);
08 ELSE
09 RETURN(v_pric * 0.9);
10 END IF;
11 END ;
12 /【代码解析】
■ 第1行表示创建名称为pric的函数。
■ 第2行表示声明函数的变量v_pric和v_qnty，分别表示价格和数量。这两个参数为输入类型的参数。
■ 第3行表示返回值的类型为数字型。
■ 第6～9行为判断产品数量并根据产品数量对价格进行打折计算的函数执行体部分。
【调用函数】
在SQL*Plus中执行函数脚本，如果执行成功，那么该函数就可以正常调用了，执行以下查询脚本：
SELECT productid,productname,productprice,pric(productprice,quantity) FROM productinfo;【示例十八】IN OUT类型参数----------演示使用IN OUT类型参数的函数。函数有两个参数，分别是产品类型编码和价格，要求求出该产品类型下比指定价格高的产品的平均价格，并返回该范围内最少的产品数量。
01 CREATE FUNCTION AVG_PRIC(V_CTGRY IN VARCHAR2, --产品类型和指定价格
02 V_PRIC IN OUT VARCHAR2) RETURN NUMBER IS
03 V_QNTY NUMBER; --利用min函数得到的产品数量
04
05 BEGIN
06 IF V_PRIC IS NULL THEN
07 V_PRIC := 0;
08 END IF;
09
10 SELECT AVG(PRODUCTPRICE), MIN(QUANTITY)
11 INTO V_PRIC, V_QNTY
12 FROM PRODUCTINFO
13 WHERE CATEGORY = V_CTGRY
14 AND PRODUCTPRICE > V_PRIC;
15
16 RETURN V_QNTY;
17
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 DBMS_OUTPUT.PUT_LINE('没有对应的数据！');
21 WHEN TOO_MANY_ROWS THEN
22 DBMS_OUTPUT.PUT_LINE('对应数据过多，请确认！');
23 END;
24 / 【代码解析】
■ 第1～2行表示创建平均函数AVG_PRIC，该函数包含两个参数：V_CTGRY表示产品类型编码，是输入参数；V_PRIC表示指定的价格，是输入输出参数。函数本身的返回类型为数值型。
■ 第3行表示PL/SQL块的内部变量，表示产品数量。
■ 第6～8行判断如果参数V_PRIC为空，就默认其值为0。
■ 第10～14行表示筛选出平均价格和最少的产品数量，并赋值到参数内。
■ 第16行表示函数返回指定范围内的最少的产品数量。
■ 第18～22行是异常捕捉块。
【调用函数】
函数执行成功后，在PL/SQL块内调用，只有这样才能获取OUT类型参数的值，否则只能得到利用RETURN语句返回的值。调用脚本如下：
DECLARE
V_CTGRY VARCHAR2(10) := '0100030002'; --指定的产品类型编码
V_PRIC VARCHAR2(20) := 1500; --指定的价格
V_QNTY VARCHAR2(20); --数量
BEGIN
V_QNTY := AVG_PRIC(V_CTGRY, V_PRIC); --调用函数，函数内部会对V_PRIC重新赋值
DBMS_OUTPUT.PUT_LINE('平均价格：' || V_PRIC);
DBMS_OUTPUT.PUT_LINE('最低的产品数量是： ' || V_QNTY);
END;到此，PL/SQL基础告一段落，欢迎纠正，欢迎补充。
本博客主要引自：《零基础学Oracle》 — 赵雪 胡可 王建强
2614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
