Oracle数据库开发必备利器之PL/SQL
第一章 概述
1.PL/SQL简介
Oracle PL/SQL语言（Procedural Language/SQL）是结合了结构化查询与Oracle自身过程控制为一体的强大语言，支持多种数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包。
PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到PL/SQL语句块的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的sql语句执行器执行。
PL/SQL 块发送给服务器后，先被编译然后执行，对于有名称的 PL/SQL 块（如子程序）
可以单独编译，永久的存储在数据库中，随时准备执行。PL/SQL 的优点还有：
1.支持SQL PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事物控制命令、SQL函数、运算符和伪列。支持所有的SQL数据类型和null值。
2.支持面向对象编程 在PL/SQL中可以创建类型，可以对类型进行及成果，可以在子程序中重载方法等。
3.更好的性能 SQL是非过程语言，只能一条一条的执行，而PL/SQL把一个PL/SQL块统一进行编译后执行，同时还可以把编译好的PL/SQL块存储起来，减少了应用程序额服务器之间的通信时间，PL/SQL是快速而高效的。
4.可移植性 使用PL/SQL编写的应用程序，可以移植到任何的操作平台上的Oracle服务器上，同时还可以编写可移植程序库，在不同环境中重用。
5.安全性 可以通过存储过程对客户机服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle数据库的访问，数据库还可以授权和撤销其他用户的访问能力。
2.PL/SQL块
PL/SQL是一种快结构的语言，一个PL/SQL程序包含了一个或者多个逻辑快，逻辑快中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，还提供了专门的异常处理部分进行异常处理。每个逻辑块分为是三个部分。
语法结构：PL/SQL块
[DECLARE
---declaration statements]//说明部分，变量和常量的定义，如果不声明可省略
[BEGIN
---executable statements]//以END结尾，程序体，END后面也用分号
[EXCEPTION
---exception statements]//异常处理部分，可选，出现异常时跳转到异常部分执行
PL/SQL是一种编程语言，也有自身的语言特点：
1.对大小写不敏感，为了良好的程序风格，开发团队都会选择一个合适的编码标准。
2.PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句结束。一行中可以有多条sql语句，他们之间以分号分割，但是不推荐一行中写多条语句。
PL/SQL中的特殊符号说明：
第二章 PL/SQL基础语法
1.PL/SQL数据类型
PL/SQL的数据类型包括标量数据类型，引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型等。
1.标量数据类型
标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型、字符型、日期型和布尔型。
2.属性数据类型
当一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。Oracle中存在两种属性类型：%TYPE和%ROWTYPE。
%ROWTYPE：引用数据库表中的一行作为数据类型，即RECODE类型（记录类型），是PL/SQL附加的数据类型。表示一条记录，就相当于C#中的一个对象。可以使用“.”来访问记录中的属性。
代码演示：
SQL> DECLARE
myemp EMP%ROWTYPE; //声明一个myemp对象，该对象表示EMP表中的一行
BEGIN
SELECT * INTO myemp FROM emp WHERE empno=7934; //从EMP表中查询出一条记录放入myemp对象中
dbms_output.put_line(myemp.ename); 访问该对象的属性可以使用“.”
END;
/
MILLER%TYPE：引用某个变量或者数据库的列的类型作为某变量的数据类型。
2.PL/SQL条件控制和循环控制
PL/SQL程序可通过条件或循环结构来控制命令执行的流程。PL/SQL提供了丰富的流程控制语句。有三种控制结构：顺序结构、条件结构、循环结构。
1.条件控制（if语句）
1.if-then
语法结构：if 条件 then
--条件结构体
end if；
2.if-then-else
语法结构：if 条件 then
--条件成立结构体
else
--条件不成立结构体
end if；
3.if-then-elsif
语法结构：if 条件1 then
--条件1成立结构体
elsif 条件2 then
--条件2成立结构体
else
--以上条件都不成立结构体
end if；
2.case
case是一种选择结构的控制语句，可以根据条件从朵儿执行分支中选择相应的执行动作。也可以作为表达式使用，返回一个值，类似于switch语句。
语法结构：
case[selector]
when 表达式1 then 语句序列1；
when 表达式2 then 语句序列2；
......
[else 语句序列 n]
end case;
3.循环结构
Oracle提供的循环类型有：
1.无条件循环loop-end loop 语句
2.while循环语句
3.for循环语句
1.loop循环（无限循环）
语法格式：loop
-循环体
end loop；
2.while循环
语法格式：while 条件 loop
--循环体
end loop；
3.for循环
for循环需要预先确定循环次数，可通过给循环变量指定下限和上限来确定循环运行的次数，然后循环变量在每次循环中递增（或者递减）。
语法结构：
for 循环变量 in[reverse] 循环下限...循环上限 loop loop
--循环体
end loop；
注：reverse：指明循环从上限向下限一次循环。
4.顺序结构
在程序顺序结构中有两个特殊的语句，goto和null
1.goto语句
goto语句将无条件的跳转到标签指定的语句中去执行。标签是用双尖括号括起来的标示块，在PL/SQL块中必须具有唯一的名称，标签后必须紧跟可执行语句或者PL/SQL块。goto不能跳转到if语句、case语句、loop语句或者字块中。
2.null语句
null语句什么都不做，只是将控制权转到下一行语句中。null语句是可执行语句。null语句在if或者其他语句语法要求至少执行一条可执行语句，但又不需要具体操作的地方。
3.光标（cursor，也称游标）
Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。
游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。可以命名一个光标，它可以在程序中获取和处理SQL语句，一次返回的行引用。有两种类型的游标：隐式游标、显式游标。
1.隐式游标
Oracle在一个SQL语句的执行，当没有显式游标的语句隐式游标时自动创建。程序员无法控制隐式游标其中的信息。
每当发出一个DML语句（INSERT，UPDATE和DELETE），隐式游标与此语句关联。对于INSERT操作时，光标保持一个需要插入的数据。对于UPDATE和DELETE操作，光标标识会受到影响的行。
在PL/SQL，可以参考最近的隐式游标的SQL游标，它有类似％FOUND，％ISOPEN，％NOTFOUND，和％ROWCOUNT属性。在SQL游标有额外的属性，％BULK_ROWCOUNT和％BULK_EXCEPTIONS，设计用于所有语句中使用。下表提供了最常用的属性的描述：
2.显示游标
显式游标是程序员定义游标获得更多的控制权的上下文的区域。显式游标应在PL/SQL块的声明部分中定义。这是创建一个SELECT语句返回多行。
创建显式游标语法是：cursor cursor_name is select_statement;
3.使用显示游标的四个工作步骤：
1.声明游标用于初始化在存储器
2.打开游标分配内存
3.获取游标检索数据
4.关闭游标释放分配的内存
4.声明游标
声明游标定义的名称和相关的select语句。例如：
CURSOR c_customers IS
SELECT id,name,address FROM customers;
5.打开游标
打开游标分配内存，使得它准备的SQL语句转换成它返回的行。例如：
OPEN c_customers;
6.获取游标
获取游标涉及一次访问一次。例如：将获取行从上面打开的游标：
FETCH c_customers INTO c_id,c_name,c_addr;
7.关闭游标
关闭游标来释放分配的内存。例如：关闭前面打开的游标
CLOSE c_customers;
4.PL/SQL的异常处理
在程序运行时出现的错误称为异常。发生异常后，语句将停止执行。PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检查。PL/SQL中任何异常出现时，每一个异常都对应一个异常码和异常信息。
1.预定义异常
为了oracle开发和维护的方便，在Oracle异常中，为常见的异常码定义了对应的异常名称，称为预定义异常。常见的预定义异常有：
语法格式：异常处理
begin
--可执行部分
exception --异常处理开始
when 异常名1 then
--对应异常处理
when 异常名2 then
--对应异常处理
......
when others then
--其他异常处理
end；除了自定义异常外，用户还可以在开发中自定义异常，自定义异常尅让用户采用与PL/SQL引擎处理错误相同的方式进行处理，用户自定义异常有两个关键点：
异常定义：采用关键字exception声明异常。例如：myexception exception；
异常引发：在程序可执行区域，使用raise关键字进行引发。例如：raise myexception
代码演示：自定义异常
DECLARE
2 sal emp.sal%TYPE;
3 myexp EXCEPTION;
4 BEGIN
5 SELECT sal INTO sal FROM emp WHERE ename='JAMES';
6 IF sal<5000 THEN
7 RAISE myexp;
8 END IF;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line('NO RECORDSET FIND!');
12 WHEN MYEXP THEN
13 dbms_output.put_line('SAL IS TO LESS!');
14 END；
5.总结
1.PL/SQL 是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL 引擎把接收到 PL/SQL 语句块中的内容进行分析，把其中的过程控制语句由PL/SQL 引擎自身去执行，把 PL/SQL 语句块中的 SQL 语句交给服务器的 SQL 语句执行器执行。
2.PL/SQL 的数据类型包括标量数据类型，引用数据类型和存储文本、图像、视频、声音等非结构化得大数据类型（LOB 数据类型）等。
3.Oracle 中存在两种属性类型：%TYPE 和%ROWTYPE。
4.PL/SQL 程序可通过控制结构来控制命令执行的流程。PL/SQL 中提供三种程序结构：顺序结构、条件结构和循环结构。
5.在 PL/SQL 程序开发中，可以使用 DML 语句和事务控制语句，还可以动态执行 SQL语句，动态执行 SQL 语句的命令是：EXECUTE IMMEDIATE。
6.在程序运行时出现的错误，称为异常。发生异常后，语句将停止执行，PL/SQL 引擎立即将控制权转到 PL/SQL 块的异常处理部分。PL/SQL 中任何异常出现时，每一个异常都对应一个异常码和异常信息。
上一篇： 设计模式之美—简单工厂模式