---

1. pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
2. 过程、函数、触发器是pl/sql编写。
3. 过程、函数、触发器是在Oracle中。
4. pl/sql是非常强大的数据库过程语言。
5. 过程、函数可以在Java程序中调用。

优点：

1. 提高应用程序的运行性能
2. 模块化的设计思想【分页的过程，订单的过程，转账的过程。。】
3. 减少网络传输量
4. 提高安全性（sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会）

缺点：

移植性不好（换数据库就用不了）。

sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的：

举一个简单的案例：

编写一个存储过程，该过程可以向某表中添加记录。

1. 创建一个简单的表

   create table mytest(name varchar2(30),passwd varchar2(30));

2. 创建过程

   replace：表示如果有sp_pro1，就替换

   查看错误信息：show error;

3. 调用该过程：

   exec 过程名（参数值1，参数值2…）；

   call 过程名（参数值1，参数值2…）；

pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。

举一个简单案例：

编写一个存储过程，该过程可以删除某表记录。

调用过程：

exec sp_pro2;

开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。

1. 单行注释 –
2. 多行注释 来划分
3. 标志符号的命名规范
   • 当定义变量时，建议用v_作为前缀，例如：v_sal 。
   • 当定义常量时，建议用c_作为前缀，例如：c_rate。
   • 当定义游标时，建议用_cursor作为后缀，例如：emp_cursor 。
   • 当定义例外时，建议用e_作为前缀，例如：e_error 。

概述：

块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。

块结构示意图：

pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。

注意：

定义部分是从declare开始的，该部分是可选的；

执行部分是从begin开始的，该部分是必须的；

例外处理部分是从exception开始的，该部分是可选的。

pl/sql块的实例1，只包括执行部分的pl/sql块：

dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。

pl/sql块的实例2，包含定义部分和执行部分的pl/sql块：

如果要把薪水也显示出来，那么执行部分就应该这么写：

select ename,sal into v_ename,v_sal from emp where empno=&aa;

& 表示要接收从控制台输入的变量。

pl/sql块的实例3，包含定义部分，执行部分和例外处理部分：

为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。

1. 比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。
2. 有时出现异常，希望用另外的逻辑处理

实例：

过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。

实例：

请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资。

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数。

实例：

输入雇员的姓名，返回该雇员的年薪。

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

1. 我们可以使用create package命令来创建包。

   实例：

   –创建一个包sp_package

   –声明该包有一个过程update_sal

   –声明该包有一个函数annual_income

   包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。

2. 建立包体可以使用create package body命令

   –给包sp_package实现包体

3. 如何调用包的过程或是函数

   当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：

1. 标量类型(scalar)
2. 复合类型(composite)
3. 参照类型(reference)
4. lob(large object)

在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常量的语法如下：

identifier [constant] datatype [not null] [:=| default expr]

identifier : 名称

constant ：指定常量。需要指定它的初始值，且其值是不能改变的

datatype ：数据类型

not null ：指定变量值不能为null

:= 给变量或是常量指定初始值

default 用于指定初始值

expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。

标量定义的案例：

1. 定义一个变长字符串

   v_ename varchar2(10);

2. 定义一个小数,范围 -9999.99~9999.99

   v_sal number(6,2);

3. 定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号

   v_sal2 number(6,2):=5.4;

4. 定义一个日期类型的数据

   v_hiredate date;

5. 定义一个布尔变量，不能为空，初始值为false

   v_valid boolean not null default false;

在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)

下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。

使用%type类型：

对于上面的pl/sql块有一个问题：

就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。

我们看看这个怎么使用：

标识符名 表名.列名%type;

比如上例的v_ename，这样定义： v_ename emp.ename%type;

用于存放多个值的变量。主要包括这几种：

1. pl/sql记录
2. pl/sql表
3. 嵌套表
4. varray

复合类型——pl/sql记录：

类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

复合类型-pl/sql表：

相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。

实例：

sp_table_type 是pl/sql表类型

emp.ename%type 指定了表的元素的类型和长度

sp_table 为pl/sql表变量

sp_table(0) 则表示下标为0的元素

如果把select ename into sp_table(-1) from emp where empno = 7788;变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：

ORA-01422:实际返回的行数超出请求的行数

解决方法是：使用参照变量

参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。

1. 游标变量

   使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。

   实例：

在任何计算机语言(c，java，pascal)都有各种控制语句（条件语句，循环结构，顺序控制结构…）在pl/sql中也存在这样的控制结构。

1. 简单的条件判断 if – then

   编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。

2. 二重条件分支 if – then – else

   编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；

3. 多重条件分支 if – then – elsif – then

   编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200。

4. 循环语句 –loop

   是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。

   案例：

5. 循环语句 –while循环

   基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while…loop开始，以end loop结束。

   案例：

6. 循环语句 –for循环

   基本for循环的基本结构如下：

7. 顺序控制语句 –goto

   goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。

   基本语法：

   其中lable是已经定义好的标号名

8. 顺序控制语句 –null

   null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。

分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。

无返回值的存储过程：

案例：

有返回值的存储过程（非列表）：

案例：

编写一个过程，可以输入雇员的编号，返回该雇员的姓名。

案例扩张：

编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

有返回值的存储过程（列表[结果集]）：

案例：

编写一个过程，输入部门号，返回该部门所有雇员信息。

对该题分析如下：

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：

1. 建立一个包，在该包中，定义类型test_cursor,是个游标。

2. 建立存储过程。

3. 用Java调用

编写分页过程：

要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。

后续还需要使用Java测试代码。

oracle将例外分为预定义例外，非预定义例外和自定义例外三种。

1. 预定义例外：用于处理常见的oracle错误

2. 非预定义例外：用于处理预定义例外不能处理的例外

3. 自定义例外：用于处理与oracle错误无关的其它情况

处理预定义例外：

预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。

1. 预定义例外 case_not_found

   在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外：

2. 预定义例外 cursor_already_open

   当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open：

3. 预定义例外 dup_val_on_index

   在唯一索引所对应的列上插入重复的值时，会隐含的触发例外：

4. 预定义例外 invalid_cursor

   当试图在不合法的游标上执行操作时，会触发该例外：

   例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外

5. 预定义例外 invalid_number

   当输入的数据有误时，会触发该例外：

   比如：数字100写成了loo就会触发该例外

6. 预定义例外 no_data_found

   下面是一个pl/sql块，当执行select into 没有返回行，就会触发该例外：

7. 预定义例外 too_many_rows

   当执行select into语句时，如果返回超过了一行，则会触发该例外。

8. 预定义例外 zero_divide

   当执行2/0语句时，则会触发该例外。

9. 预定义例外 value_error

   当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外。

10. 其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)

    1.login_denied

    当用户非法登录时，会触发该例外

    2.not_logged_on

    如果用户没有登录就执行dml操作，就会触发该例外

    3.storage_error

    如果超过了内存空间或是内存被损坏，就触发该例外

    4.timeout_on_resource

    如果oracle在等待资源时，出现了超时就触发该例外

非预定义例外：

非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。

处理自定义例外：

预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外。

案例：

请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。

---