PL/SQL编程急速上手

2014-12-19 18:21

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

PL/SQL编程急速上手

结构化查询语言（SQL）是第四代编程语言的典型，这种命令式的语言更像一种指令，使用它，你只需要告诉计算机“做什么”，而不用告诉计算机“怎么做”。第四代编程语言普遍具有简单、易学、能更快的投入生产等优点，但也失去了部分第三代编程语言（C，C++，Java等）的灵活性。PL/SQL 在 SQL 的基础上，保留了部分第三代编程语言特性，它主要运行在 Oracle 数据库上，它同时兼备了第四代语言简单、易用的特点，又保留了高级程序设计语言的灵活性与完整性，这使得开发人员可以只使用 PL/SQL 就能进行复杂业务逻辑的编写。

一　　PL/SQL 简介

　　1，简介

　　标准 SQL 提供了定义和操纵数据库对象的能力，但与传统高级编程语言相比，由于其具有更高的抽象性，所以注定缺乏诸多高级编程语言的特性，比如封装函数、流程控制、进行错误检测和处理等。

　　PL/SQL 是 Oracle 在标准 SQL 的基础上进行功能扩充后的一门编程语言，这使它保留了部分第三代编程语言的部分特性，比如变量声明、流程控制、错误处理等。

　　PL/SQL 的全称是 Procedural Language/SQL，即过程化结构查询语言，正如其名所示，PL/SQL 增加了过程性语言中的结构，以对标准 SQL 进行扩充。在PL/SQL 中，最基本的程序单元是语句块（block），所有的程序都应该由各种块构成，块与块之间可以相互嵌套。在块中，可以定义变量，执行条件判断，循环等。

　　2，开发工具

　　Oracle 官方提供了两款开发工具：SQL*Plus 和 Oracle SQL Developer。前者是一款命令行开发工具，后者则拥有方便的图形化操作界面（类似SQL Server 的 SSMS）。

　　除了官方提供的两款工具外，PL/SQL Develpoer 是一款由第三方公司开发的，非常流行的 Oracle 数据库集成开发环境。除此之外，市面上还有很多其他工具也具备 Oracle 数据库开发的能力，大家可以根据需要选择合适的开发工具。

二　　PL/SQL基础

　　1，SQL 与 PL/SQL

　　前面提到，PL/SQL 是对标准 SQL 的扩展，所以，在 PL/SQL 中不仅可以执行 SQL 语句，还支持很多增强的特性，比如在 SQL 语句中使用变量、使用 PL/SQL 定义的函数等。在 PL/SQL 语句块中，可以使用 SQL 语句操作数据库，它支持所有的 SQL 数据操作、游标和事务处理命令，支持所有的 SQL 函数、操作符，完全支持 SQL 数据类型。

　　需要注意的是：在 PL/SQL 语句块中，不能直接使用 DDL 语句，这是因为 PL/SQL 引擎在编译时会检测语句块中所涉及的对象，如果其不存在，通常都会引发错误，导致 DDL 语句执行失败。

　　为了解决这类绑定性错误，可以使用动态SQL，即把需要执行的 DDL 操作存储在字符串中，并通过 execute immediate 来执行这个字符串，从而达到间接执行 DDL 操作的目的。

　　2，数据定义

　　数据管理主要使用 DDL 数据定义语言：create、alter、drop。

　　创建表和约束：

 1 --在列后添加约束
 2 create table table_name
 3 (
 4     col1 type constraint,
 5     ...
 6 )
 7 --单独添加约束
 8 create table table_name
 9 (
10     col1 type,
11     ....,
12     constration cons_name cons_type
13 )
14 --在Oracle中创建表和约束与标准SQL相同

　　创建索引和视图：

 1 --创建索引（非唯一）
 2 --默认系统会在具有unique和primary key的列上创建唯一约束
 3 create index index_name on (col1...);
 4 --当提供多个列时，即创建复合索引
 5 --创建视图
 6 create or replace view view_name
 7 as
 8 select ...;
 9 --创建，如果已存在则修改视图
10 create view ...
11 as
12 ...
13 with read only;
14 --创建只读的视图（推荐）

　　修改表或视图：

1 --为表增加新的列
2 alter table table_name
3 add col_name type constration;
4 --移除表中已有的列
5 alter table table_name
6 drop column col_name;

　　删除数据库对象：

1 --删除表
2 drop table table_name;
3 --删除视图
4 drop view view_name;
5 ...

　　3，数据查询

　　A：标准查询

　　　　Oracle 中的数据查询遵循 SQL 标准，常规查询请移步我的《SQL入门，就这么简单》。

　　B：dual 表

　　　　dual 是 Oracle 系统中对所有用户可用的一个实际存在的表，它不能用来存储信息，在实际开发中只能用来执行 SELECT 语句，我们可以用它来获取系统信息，比如获取当前系统日期，或输出一些测试信息。

1 --获取系统日期
2 select sysdate from dual;
3 --转换日期格式
4 select to_char(sysdate,"yyyy-mm-dd");
5 ...

　　C：伪列

　　　　常用的伪列有两个：rownum、rowid。

　　　　在 Oracle 中没有类似 SQL Server 中 TOP 这样可以提取结果集前几条记录的关键字，但 Oracle 提供了一个更方便的方法，rownum 伪列。rownum 是一个动态的序号，从 1 开始，为所有查询到的数据编号。

1 --查询员工表中前10位员工相关信息
2 select rownum,ename,sal from emp
3 where rownum<=10;
4 -- 测试数据库 Oracle 11g

　　　　使用 rownum 伪列时需要注意：rownum 是在基础查询之后动态添加上去的序号，所以，如果你想通过一条查询语句实现提取结果集中间的部分记录是不能成功的，必须使用子查询，把 rownum 当做普通列才能实现。

1 select row_num,empno,ename,sal from (
2     select rownum as row_num,empno,ename,sal from emp
3 )a
4 where row_num >5 and row_num <=10;
5 -- 别名是为了防止服务器把外层的rownum再次当做伪列

　　　　同理，提取使用 order by 排序后的记录，也需要使用子查询。

　　　　和 rownum 不同，rowid 伪列是和表中的数据一样实际存在的列，它是一种数据类型，是基于 64 位编码的 18 个字符，用来唯一的表示一条记录物理位置的一个id。我们可以通过 rowidtochar 函数把它转换成字符串进行显示，还可以通过它来删除表中重复的记录。

1 --查看rowid
2 select rowidtochar(rowid) ename,sal from emp;
3 --基于rowid删除表中形同的记录
4 delete from emp
5 where rowid not in (
6     select min(rowid) from emp group by empno
7 );

　　4，数据操纵

　　数据操纵主要包含以下操作：insert、update、delete、merge。

　　A：insert 插入

 1 --方式一
 2 insert into table_name(column list)--如果不提供字段列表，下面的值列表需要提供每个字段的值，即使可以为空或有默认值
 3 values 
 4 (value list),
 5 (value list),
 6 ....
 7 --方式二
 8 insert into table_name
 9 select ...
10 --从其他查询获取数据，并插入表，数据必须符合表的约束

　　B：update 更新

1 --方式一
2 update table_name
3 set col=newValue
4 where ...--如果不提供过滤条件，则更新表中所有的列
5 --方式二
6 update table_name
7 set (column list)=
8 (select ...)
9 --通过子查询更新表，如果只更新一列，则可以省略column list 的括号，需要注意子查询的字段顺序需要和更新的字段顺序一致

　　C：delete

1 --方式一
2 delete from table_name
3 where ...--如果不提供过滤条件，则会删除所有记录

　　5，序列

　　Oracle 中没有 SQL Server 中 identity() 标识函数，也没有 MySQL 中 auto_increnent 这样的选项来实现自增的列。但 Oracle 提供了更有用的“序列”。类似一个封装好的函数，每次执行会返回一个按指定步长增长或减小的数字。常用来为表设置自增的主键。

1 create sequence seq_name
2 increment by n --自增的步长，（省略该选项则）默认为1，负数表示递减
3 start with n --序列的初始值，默认为1
4 max value n | nomaxvalue --指定最大值或没有最大值(无限增长)
5 min value n  | nominvalue --指定最小值或没有最小值(无限减小)
6 cycle | nocycle --规定设置的序列到达最大或最小时是否从开头循环
7 cache n | nocache --规定是否在内存中缓存序列值，以改善性能

　　通常情况下，我们只需要指定初始值，最大值和循环三项，即可创建一个序列。

1 create sequence my_seq
2 start with 1
3 nomaxvalue
4 nocycle;

　　序列也是 Oracle 数据库对象之一，序列有两个常用的属性：nextval、currval。

1 select my_seq.nextval from dual;--获取下一个序列值
2 select my_seq.currval from dual;--查看当前序列值
3 --在插入数据是使用序列
4 insert into table_name
5 values
6 (my_seq.nextval,...)
7 --使用循环批量插入时非常方便

　　我们可以为每个表创建单独的序列，从而为每个表提供没有间隙（无删除数据或回滚等操作干扰）的自增字段作为主键。

　　修改和删除序列：

1 alter sequence seq_name
2 ...
3 --为了保证主键的变化有相同的规律可循，一般不建议修改已创建的序列
4 drop sequence seq_name

三　　Oracle 内置函数

　　1，字符串函数

 1 --把二进制转换成字符
 2 select CHR(0101) from dual;
 3 --连接字符串
 4 select concat(111,"aaa") from dual;
 5 select 111 || "aaa" from dual;
 6 --首字母大写
 7 select INITCAP("char") from dual;
 8 --全大/小写转换
 9 select lower("ABC"),upper("abc") from dual;
10 --左/右填充
11 select lpad("aa",5,"*"),rpad("aa",5,"*") from dual;
12 --删除字符串左/右指定字符(第二个参数中包含的字符都会被删除)
13 select ltrim("aaa123aaa","1a"),rtrim("aa123aa","a") from dual;
14 --删除左右空格
15 select trim("  aaa  ") from dual;
16 --从左边开始删除指定字符（单个）,可选参数还包括：trailing（从右边开始）,both(两边一起)
17 select trim(leading "a" from "aa123aa") from dual;
18 --从指定位置开始截取指定长度的字符串
19 select substr("abcdefg",2,3) from dual;
20 --字符替换(第二个参数中包含的字符都会被替换)
21 select translate("11aa22aa11", "a2", "bb") from dual;
22 --替换 NULL 值
23 select nvl(NULL,"aha") from dual;

　　2，数学函数

 1 --绝对值
 2 select abs(-123) from dual;
 3 --向上取整
 4 select ceil(1.2),ceil(-1.2) from dual;
 5 --向下取整
 6 select floor(1.8),floor(-1.8) from dual;
 7 --返回自然常数 e 的 n 次方
 8 select exp(5) from dual;
 9 --返回以第一个参数为底的第二个参数的对数
10 select log(3,10) from dual;
11 --求模,如果第二个参数为0，则返回第一个参数
12 select mod(10,3) from dual;
13 --返回第一个参数的第二个参数次方
14 select power(2，3) from dual;
15 --保留指定小数位，最后一位小数四舍五入得来
16 select round(1.2345,3) from dual;
17 --保留指定小数位，其余直接截断
18 select trunc(1.2345,3) from dual;
19 
20 --格式化数字(格式位数应该与数字位数相同)
21 
22 --用0格式化时，如果数字位数不够，结果会用0补齐位数
23 select to_char(123456789000,"000,000,000,000,000") from dual;
24 --用9格式化时，如果数字位数不够，结果会用空格补齐位数
25 select to_char(123456789000,"999,999,999,999,999") from dual;
26 --使用fm格式化小数
27 select to_char(123456.258,"fm999,999,999.99") from dual;
28 --使用 $（美元） 或 L（当地） 添加货币符号
29 select to_char(123.456,"L999.999") from dual;
30

　　3，时间和日期函数

 1 --返回操作系统日期
 2 select sysdate from dual;
 3 --返回日期部分
 4 select current_date from dual;
 5 --返回日期+时间
 6 select current_timestamp from dual;
 7 --返回操作系统日期—+时间（包含时区信息）
 8 select systimestamp from dual;
 9 --按格式化日期为字符串
10 select to_char(sysdate,"YYYY-MM-DD HH:MM:SS") from dual;
11 --把字符串表示的日期转换成日期类型的值返回（前后格式需保持一致）
12 select to_date("2020-05-28 17:02:00","YYYY-MM-DD HH24:MI:SS") from dual;
13 --把字符串表示的日期转换成日期 + 时间类型的值返回（前后格式需保持一致）
14 select to_timestamp("2020-05-28 17:02:00","YYYY-MM-DD HH24:MI:SS") from dual;
15 --返回指定日期后几个月的日期
16 select add_months(sysdate,1) from dual;
17 --返回两个日期间间隔月数(注意正负)
18 select months_between(sysdate,to_date("2020-07-01","YYYY-MM-DD")) from dual;
19 --把日期按指定精度截断,可选参数有yyyy(精确到年，返回当年的第一天的日期),mm(精确到月，返回当月第一天的日期),rr(精确到日，返回当天的日期)
20 select trunc(sysdate,"mm") from dual;
21 
22 
23 TO_CHAR(sysdate, "DD-MON-YYYY HH24:MI:SS")
24 TO_CHAR(sysdate, "DD-MON-YYYY HH12:MI:SS PM")
25 TO_CHAR(systimestamp, "DD-MON-YYYY HH24:MI:SS.FF")
26 TO_CHAR(sysdate, "DY, DD-MON-YYYY")
27 TO_CHAR(sysdate,"Month DDth, YYYY") 
28 TO_CHAR(systimestamp, "DD-MON-YYYY HH24:MI:SS TZH:TZM")
29 TO_CHAR(sysdate, "MM/DD/YYYY HH24:MI:SS")
30 TO_CHAR(sysdate, "MM/DD/YY HH24:MI:SS")
31 TO_CHAR(sysdate, "MM/DD/RRRR HH12:MI:SS PM")
32 TO_CHAR(sysdate, "MM/DD/RR HH12:MI:SS PM")

　　4，聚合函数

 1 --计算行数（不计算空值）
 2 select count(*) from emp;--根据所有列计算
 3 select count(comm) from emp;--根据某一列计算（注意该列是否有空值）
 4 select count(distinct deptno) from emp;--计算deptno中不同值的个数
 5 --计算列的最大/小值
 6 select max(sal),min(sal) from emp;
 7 --返回中间值
 8 select median(sal) from emp;
 9 --返回标准差
10 select stddev(sal) from emp;
11 --求和
12 select sum(sal) from emp;
13 --计算方差
14 select variance(sal) from emp;
15 --伪列 rownum,每条数据的序号
16 select rownum,empno,ename,sal from emp;

四　　变量和类型

　　1，PL/SQL 基础

　　如果想通过 PL/SQL 程序输出内容，需要先执行以下命令，以打开输出功能，否则即使 PL/SQL 程序正常执行，也不会有任何信息输出。

1 set serveroutput on--可以不需要语句结束标记";",这是开发工具的命令
2 dbms_output.enable;--这是 Pl/SQL 提供的

　　PL/SQL 程序由不同的 block（程序块）组成，块是 PL/SQL 程序的基本组成单位，块又可以分为匿名块和命名块。

　　一个完整的 PL/SQL 程序一般包含 3 部分：declare（声明），execution code（执行代码，即业务逻辑代码），exception（异常处理），声明和异常处理不是必须的。

1 declare
2 --... 包括变量、游标等
3 begin
4 --... 业务代码
5 exception
6 --... 异常处理
7 end;

　　让我们来看一个最简单的 PL/SQL 程序：

1 --注意，PL/SQL业务代码必须运行在 begin...end 中
2 begin
3 dbms_output.put_line("hello world");
4 end;
5 --没有声明和异常部分

　　块与块之间可以相互嵌套，PL/SQL 中程序块可以限制变量的作用域（变量的作用域问题稍后的章节将会详细讲解），另外，使用<>为块命名可以让整个程序可读性更好：

1 <>--oracle 11g 不允许给最外层块命名
2 begin
3 dbms_output.put_line("outer block");
4   <<inner>>
5   begin
6   dbms_output.put_line("inner block");
7   end;
8 end;

　　2，变量

　　PL/SQL 中的变量在 declare 区域声明，不需要额外的标识符，只需要提供变量名和值类型即可。

1 declare
2   v_name emp.ename%type;--通过动态获取表中列的数据类型，来确定变量的数据类型
3   v_job  varchar(50);--直接指定具体的数据类型
4 begin
5   v_name:="&name";--通过:=为变量赋值
6 end;

　　&name，这种形式是 SQL Developer 工具提供的一种变量形式：替换变量，在执行程序时，你可以手动指定变量的值，提升程序的交互性，测试程序时非常有用。需要注意的是，它并不是 PL/SQL 提供的功能，当使用 & 标识变量时，每次执行该程序都需要提供值，如果使用 && 标识，则只需要在第一次执行时提供，后续执行都默认为第一次提供的值。

　　给变量赋值除了通过 := 的方式，还可以使用 select...into 的方式，直接从查询中获取值并赋给变量。

1 declare
2     v_job emp.job%type;
3 begin
4     select job into v_job from emp where ename=v_name;--通过select...into 为变量赋值
5     dbms_output.put_line(v_job);--输出变量值
6 end;

　　3，记录类型

　　当有多个逻辑相关的变量需要声明时，我们可以使用记录类型来封装他们，封装好这个东西就是记录类型（record）。

 1 declare
 2   type emp_record is record(--这里相当于定义了一种新的数据类型，类型名称是emp_record,和varcahr，int等类型一样
 3   v_name emp.ename%type,
 4   v_job emp.job%type,
 5   v_sal emp.sal%type
 6   );
 7   --记录类型类似其他编程语言中的类
 8   v_emp_record emp_record;--声明一个emp_record类型的变量，相当于创建一个类的实例
 9 begin
10   select ename,job,sal into v_emp_record from emp where ename="ALLEN";--注意查询的顺序必须和记录类型中定义的顺序一致
11   dbms_output.put_line(v_emp_record.v_name||" "||v_emp_record.v_job||" "||v_emp_record.v_sal);
12   --通过实例访问相关属性
13 end;

　　%rowtype：

1 declare
2   v_emp_record emp%rowtype;--声明一个包含指定表中所有列的rowtype变量，使用上和记录类型完全一致，但它本质上并不是记录类型
3 begin
4   select * into v_emp_record from emp where ename="ALLEN";--把所有的列都查询出来赋值给该变量
5   dbms_output.put_line(v_emp_record.ename||" "||v_emp_record.sal);
6   --该变量中的属性和表的列名完全一致，可以根据需要，只使用部分数据
7 end;

　　4，集合

　　集合类似其他编程语言中的数组，也可以通过下标来访问数据。

　　如果把它和记录类型、变量相比教，你会发现，标量标量是用来处理单行单列数据的，记录类型适合处理单行多列的数据，而集合则是用来处理单列多行数据的。

　　Oracle 提供了三种类型的集合：索引表（又称关联数组）、嵌套表、可变长度数组。

　　索引表可以通过数字或字符串来作为下标存储数据，下标可以不连续，索引表的容量即是数字的最大值，但它只能存储在内存中。

1 declare
2   type idx_table is table of varchar(20) index by pls_integer;--创建索引表类型
3   -- index by 后可选的参数有pls_integer、binary_integer、varcahr(size)和使用%type 指定的varchar2类型
4   v_idx_table idx_table;--声明索引表类型的变量
5 begin
6   v_idx_table(1):="hello";--插入值
7   v_idx_table(2):="world";
8   dbms_output.put_line(v_idx_table(1)||" "||v_idx_table(2));
9 end;

　　嵌套表只能使用数字作为下标，数字必须是有序的，嵌套表的容量没有限制，可以保存到数据库中。

 1 declare 
 2   type nest_table is table of varchar(20);--创建嵌套表类型
 3   v_nest_table nest_table:=nest_table("x");--声明嵌套表类型的变量并初始化
 4   --未初始化的嵌套表类型实际上是一个null，如果试图为其赋值会报错。初始化就是调用一个和创建的嵌套表类型同名的函数，
 5   --函数的参数值类型需要和嵌套表类型定义的存储值类型（of 后的类型）相同，并且参数的个数默认就是这个嵌套表类型变量的初始容量
 6 begin
 7   v_nest_table.extend(5);--扩充嵌套表类型变量的容量
 8   --如果要增加嵌套表的容量，需要调用extend方法（该方法将在稍后详细说明）
 9   v_nest_table(1):="hello";--插入值
10   v_nest_table(2):="world";
11   dbms_output.put_line(v_nest_table(1)||" "||v_nest_table(2));
12   dbms_output.put_line(nvl(v_nest_table(3),"it is null"));--没被使用的位置为null
13 end;

　　可变长度数组和嵌套表类似，都只能使用有序的数字作为下标，可变数组在定义时必须指定容量，但在运行时可以手动的扩充其容量，所以，可变数组的真实容量也可以是无限的，可变数组也可以存储到数据库中。

 1 declare
 2   type varr is varray(5) of int;--创建可变数组类型
 3   v_varr varr:=varr();--声明可变数组类型的变量并初始化
 4   --和嵌套表一样的原因，必须初始化
 5 begin
 6   for i in 1..5 loop--循环插入值
 7     v_varr.extend();
 8     v_varr(i):=i;
 9     end loop; dbms_output.put_line(v_varr(1)||","||v_varr(2)||","||v_varr(3)||","||v_varr(4)||","||v_varr(5));
10 end;

　　嵌套表和可变数组能存入数据库是指：他们可以和普通数据类型一样，用来定义表的列。

 1 --第一步，创建一个保存在数据库中的嵌套表类型
 2 create or replace type nest is table of varchar(20);
 3 --第二步，创建一个带有嵌套表类型列的数据表
 4 create table x(
 5   x_id int, 
 6   x_nest nest
 7 )nested table x_nest store as y;--使用nest table 指定这是一个包含嵌套表类型值的数据表，并通过 store as 创建一个关联表来专门存储嵌套表
 8 --插入一条数据（包含初始化的嵌套表类型值）
 9 insert into x values(1,nest("x","y","z"));
10 --第三步，在PL/SQL中读取嵌套表类型的值（多行操作使用游标）。数据表并没有直接存储嵌套表，所以不能直接使用select查询，而应该在PL/SQL程序块中查询
11 declare
12   v_nest nest;
13 begin
14   select x_nest into v_nest from x;
15   for i in 1..3 loop
16     dbms_output.put_line(v_nest(i));
17   end loop;
18 end;

　　把可变长度数组存放到数据库就不需要使用 nested table 和 store as 指定相关信息，而且可以直接使用 select 查询存储了可变长度数组的数据表。所以，通常的建议是，当只是临时使用集合，那么索引表是最好的选择，如果需要把集合存入数据库，可变数组更操作起来更简单。

　　5，集合常用方法

　　集合的方法通过“.”点的形式调用：集合.方法。

1 集合.exists(n)--判断是否存在某个集合的值
2 集合.count--统计集合中值的个数
3 集合.limit--查询集合容量（长度）
4 集合.first/集合.last--集合中第一个/最后一个值的索引
5 集合.prior(n)/集合.nest(n)--指定索引位置前一个/下一个值的索引（一般用在索引表中，因为其下标可能不连续）
6 集合.extend/集合.extend(n)--为集合增加1个/n个容量（一般用在嵌套表和可变数组中）
7 集合.trim/集合.trim(n)--从集合末尾删除1个/n个元素（一般用在嵌套表和可变数组中）
8 集合.delete/集合.delete(n)--从集合中删除所有元素/第n个元素（一般用在索引表和嵌套表中）

　　6，变量的作用域

 1 declare
 2  v1 int default 1;--外层块变量v1
 3 begin
 4  dbms_output.put_line(v1);
 5  --dbms_output.put_line(v2);error 必须声明v2
 6  declare
 7   v2 int default 2;---内层块变量
 8   v1 int default 3;
 9  begin
10   dbms_output.put_line(v1);--返回3
11  end;
12 end;

　　变量只在声明的块中起作用，内层块可以访问外层块的变量，但外层块无法访问内层块的变量，如果内外块声明的相同的变量，那么 PL/SQL 采用就近原则。

五　　流程控制

　　1，case

　　case 语句有两种语法，简单 case 语法只做等值匹配，搜索 case 语法可以做区间匹配。

　　先来看简单 case 语法：

 1 declare
 2   v_sal int;
 3 begin
 4   select sal into v_sal from emp where empno=&empno;
 5   case v_sal
 6     when 800 then dbms_output.put_line("太少了吧");
 7     when 1600 then dbms_output.put_line("这还差不多");
 8     when 3000 then dbms_output.put_line("这样更好");
 9     when 5000 then dbms_output.put_line("这样最好");
10     else dbms_output.put_line("随缘吧");
11     end case;
12 end;

　　搜索 case 语法：

 1 begin
 2   select sal into v_sal from emp where empno=&empno;
 3   case 
 4     when v_sal<=1000 then dbms_output.put_line("太少了吧");
 5     when v_sal<=1600 then dbms_output.put_line("这还差不多");
 6     when v_sal<=3000 then dbms_output.put_line("这样更好");
 7     when v_sal<=5000 then dbms_output.put_line("这样最好");
 8     else dbms_output.put_line("随缘吧");
 9     end case;
10 end;

　　请仔细观察两种语法的区别。

　　2，if...elsif...else

 1 declare
 2   v_sal int;
 3 begin
 4   select sal into v_sal from emp where empno=&empno;
 5   if v_sal>=5000
 6     then dbms_output.put_line("还有头发吗");
 7   elsif v_sal>=3000
 8     then dbms_output.put_line("还有一半吗");
 9   else
10     dbms_output.put_line("好好珍惜头发啊，少年");
11   end if;
12 end;

　　请注意，PL/SQL 中的多分支结构 elsif 关键字与其他语言相比，少了一个字母 e，且 els 和 if 之间没有空格。

　　3，循环

　　PL/SQL 提供了 3 种循环：loop、while、for（集合部分已经见过了）。

　　在正式介绍循环之前，首先要介绍 PL/SQL 中的循环控制语句：exit，无条件结束整个循环（类似其他语言中的 break）。continue，结束本次循环，继续下一次循环。接下里让我们通过例子来详细说明每个循环的使用方法。

　　loop 循环：

 1 declare
 2   i int default 1;--定义，初始化循环控制变量
 3 begin
 4   loop
 5     if i=5 then
 6       i:=i+1;
 7       continue;--当n等于5时，直接结束本次循环，不输出
 8     end if;
 9     dbms_output.put_line(i);
10     i:=i+1;--修改循环控制变量 
11     exit when i>10;--根据循环控制比变量，判断是否退出循环
12   end loop;--结束循环
13 end;

　　while 循环：

1 declare
2   i int default 1;--定义，初始化循环控制变量
3 begin
4   while i<=10 loop--根据循环控制变量，判断是否进入循环体
5     dbms_output.put_line(i);--循环体
6     i:=i+1;--修改循环控制变量
7   end loop;--结束循环
8 end;

　　for 循环：

1 begin
2   --在for循环中，初始化循环控制变量，只需指明变量名即可，类型系统默认为数字，min..max指明控制变量的变化范围，从min开始，到max结束
3   for i in reverse 1..10 loop--i可以被循环体引用，但不能被赋值
4   dbms_output.put_line(i);--循环体
5   --注意，因为初始化循环变量时已经指定了变化范围，这相当于限定了循环条件，当变量从min变化到max时将自动结束循环
6   end loop;  --结束循环
7   --最后说明，reverse是可选的参数，表示循环变量从max开始，到min结束
8 end;

　　4，杂项

　　这里要介绍两个东西，null 语句（不是null 值）和 goto 语句。null 语句表示什么也不做，goto 可以无条件跳转到程序指定位置。

1 begin
2   if ... then
3     ...
4   else
5     null;--什么也不做，但使整个语句块更丰满，可读性更高
6   end if;
7 end;

 1 declare
 2   i int:=0;
 3 begin
 4   <>--定义一个标签
 5   i:=i+1;
 6   dbms_output.put_line(i);
 7   if i<10 then
 8     goto outer;--通过goto实现类似循环的结构
 9   else
10     null;--通过使用null让语句块更易读
11   end if;
12 end;

　　使用 goto 语句会破坏程序常规的执行流程，它是有别于顺序、分支、循坏的另一种执行流程，如无特别需求，建议不要使用。

六　　异常处理

　　1，异常简介

　　无论何时何地何人，在编程的领域，总是无法避开异常。为了保证程序的健壮性，多数语言都提供了异常处理机制，PL/SQL 也不例外。

　　在 PL/SQL 中，异常大致可分为两大类：

　　　　编译时错误：程序在编写过程中的错误，例如语法错误，访问不存在的对象等，这类错误在编译时 PL/SQL 引擎就会发现，并通知用户。

　　　　执行时错误：这类错误会顺利通过程序的编译环节，只能等到执行时才能被发现，比如除数是 0 。这类错误也是最要命的。

　　2，异常处理语法

　　我们知道，PL/SQL 程序分为三个部分：声明区，执行区，异常处理区。基本的异常处理也包含此三个步骤：

　　　　A：在定义区，定义异常。

　　　　B：在执行区，触发异常。

　　　　C：只要执行区触发了异常，那么执行区后续的业务代码都会立即停止执行，执行流程跳转至异常处理区。

 1 declare
 2     异常变量名 exception;
 3 begin
 4     ...
 5     raise 异常变量名;
 6     ...
 7     exception
 8       when 异常变量名
 9         then ...
10 end;

　　如果有多个异常，可以定义多个变量，并在合适的时候触发他们，并在异常处理区通过多个 when...then 来捕获他们，并执行特定操作。

　　3，预定义异常

　　大多数编译时的异常，Oracle 都在内部隐式的定义好了，并且不需要在执行区手动的触发，这类异常的处理最为简单：

declare
    v_tmp varchar(10);
begin
    v_tmp:="超过10字节的长度了";
    exception
        when value_error
        then
            dbms_output.put_line("出现value_error错误！" || "错误编号："|| sqlcode || "错误名称" || sqlerrm);
end;

　　PL/SQL 中出现的错误，都一个错误号，一个错误编码（sqlcode），一个错误名称（sqlerrm）。在错误处理区通过在 when 后面指定错误名称，既可捕获到指定错误了。常见的预定义错误如下：

错误号	异常编码	异常名称	描述
ora-01012	-1017	not_logged_on	在没有连接数据库时访问数据
ora-01403	100	no_date_found	select...into没有返回值
ora-01422	-1422	too_many_rows	select...into结果集超过一行
ora-01476	-1476	zero_divide	除数为0
ora-01722	-1722	invalid_number	字符串和数字相加时，字符串转换失败
ora-06502	-6502	value-error	赋值时，变量长度不足
ora-06530	-6530	access_into_null	向null值对象赋值
ora-06592	-06592	case_not_found	case语句中没有任何匹配的值并且没有else选项

　　更多预定义异常请查询 Oracle 11g 《Oracle 在线文档》。

　　4，自定义错误

 1 declare
 2   e_nocomm exception;--定义一个异常名称
 3   v_comm number(10,2);
 4 begin
 5   select comm into v_comm from emp where empno=&empno;
 6   if v_comm is null
 7     then raise e_nocomm;--触发自定义异常
 8   end if;
 9   exception
10     when e_nocomm--捕获自定义异常
11       then dbms_output.put_lne("该员工没有提成");
12     when others--捕获未定义的错误
13       then dbms_output.put_line("未知错误 !");
14 end;

　　同一个块中不能同时声明一个异常多次，但不同的块中可以定义相同的异常，在各自的块中使用不会相互影响。

七　　编程对象

　　1，事务

　　在 SQL Server 中，每一条 DML 语句都是一个隐式的事务，除非显示的开始一个事务，否则，这些语句执行完就立即向数据库提交了这些更改。而在 Oracle 中，每一条 SQL 语句开始都会自动开启一个事务，除非显示的使用 commit 提交，或退出某个开发工具而断开连接，才会提交到数据库，否则这些操作都只会保存在内存中。

 1 --在Oracle SQL Developer中
 2 begin
 3   insert into dept values(88,"开发部","cd");
 4   savepoint a;--设置保存点a
 5   insert into dept values(88,"设计部","cd");
 6   exception
 7     when dup_val_on_index then
 8     dbms_output.put_line("插入出错");
 9     rollback to a;--回滚到a
10 end;
11 --这里我们人为的制造了一个违反唯一约束的插入操作，在错误区捕获该错误，然后回滚到保存点a
12 select * from dept;--只能查询到开发部被插入

1 
2 SQL>select * from dept;
3

1 -- 在 Oracle SQL Developer中
2 commit;
3 --现在插入已经被提交到数据库，在SQL*Plus 中也可以查询到了

　　在多个事务并发执行时，大概率会发生：一个事务读取到另一个事务还未提交的数据（脏读）；一个事务中不同时间点执行的同一个查询，由于其他事务对涉及的数据进行了修改或删除（不可重复读）或插入（幻读），而导致出现不一样的结果。

　　为了解决这样的问题，Oracle 允许对事务设立隔离级别：

1 begin
2   commit;
3   set transaction read only;--只读的事务
4   --settransaction read write;--可读写的事务
5   --set transaction isolation level [serializable | read commited];
6   --serializable：整个事务只能读到当前事务开始前就以提交的数据
7   --read commited：当前事务中的查询，只能读到该查询前以提交的数据（不是整个事务，而是该查询语句。这也是 Oracle 默认的隔离级别）
8 end;

　　由于一个事务中有且只能存在一条 set transaction 语句，且必须是事务的第一条语句，所以通常先使用 commit 结束前一个事务（理论上rollback也可以），以保证该语句是事务的第一条语句。

　　2，子程序

　　Oracle 中子程序事实上就是 SQL Server 中对存储过程和用户自定义函数的总称。过程和函数本质上是一个命名块，可以被存储在数据库中，并在合适的时候调用，这样可以解决代码重用的问题，并且由于它是已编译好的代码，所以执行起来也更快。

　　过程和函数相比，过程不会返回值，常用来做数据的增删改。而函数必须有返回值，通常用来向应用程序返回值。其他方面，过程和函数几无区别。

　　存储过程：

1 --无参过程
2 create or replace procedure p2 as
3 begin
4   dbms_output.put_line("hello world");
5 end p2;
6 --or replace：如果存在则替换存储过程，建议使用
7 --p1：过程名
8 --as：不能省略，也可以用is代替
9 --end p2：创建完成时也要跟上过程名

 1 --带参数的过程
 2 create or replace procedure p2(p_deptno in int)--使用括号添加过程需要的形参
 3 as
 4   v_empcount number;--定义过程中需要使用的变量，只需指定数据类型，不能添加类型所占字节长度
 5 begin
 6   select count(ename) into v_empcount from emp where deptno=p_deptno;
 7   if v_empcount>0 then
 8     dbms_output.put_line("有人");
 9   else
10     dbms_output.put_line("没人");
11   end if;
12 end p2;--不要忘了过程名

1 --调用存储过程
2 begin
3   p2(20);--通过（）传递实参
4 end;
5 --call p2(20);

　　函数：

 1 --创建函数
 2 create or replace function f1
 3 return number--需要指定返回值类型，不需要长度
 4 as
 5 begin
 6   return 1;--需要使用return指定返回值
 7 end f1;
 8 --调用函数
 9 declare 
10   v_f1 number(10);
11 begin
12   v_f1:=f1();--调用函数，并把返回值赋值给变量
13   dbms_output.put_line(v_f1);
14 end;

　　在上面带参数存储过程中，指定形参时使用关键字 in，该关键字表示参数的模式是输入型，可选的还有 out 输出型，in out 输入输出型。

　　in 模式的参数被用作输入参数，在过程内部只能被访问，不能被赋值。

　　out 模式的参数被当做输出参数使用，在过程内部可以被赋值，不能访问。使用 out 类型参数时，必须在过程外部定义一个变量，用于接收过程在内部需要输出的值，然后在调用子程序时把该变量当做形参传入。待过程执行完毕，直接访问外部定义的这个变量即可得到过程希望输出的值了。

　　in out 模式的参数既可以被当做输入参数，也可以被当做输出参数。使用方式和 out 型参数一致，但可以给这个变量一个初始化值，一并传入过程内部。out 型参数即使传入了初始值，也会被过程忽略。

　　过程的参数模式和 MySQL 完全一致，例子可以参考我的《MySQL 编程》。函数本身就需要使用 return 返回值，所以不使用 in 或 out 指定参数模式，这样毫无意义。

　　3，触发器

　　Oracle 中的触发器本质上也是一个命名的语句块，定义的方式和 PL/SQL 语句块差不多，但它和过程或函数不同，它只能被隐式的调用。并且不能接受任何参数。

　　定义触发器的语法：

1 create or replace trigger trigger_name--触发器名称
2 [before | after | instead of]--在事件之前还是之后执行触发器中的代码
3 trigger_event--触发事件
4 [referenceing_caluse]--通过新的名称引用当前正在更新的数据
5 [when trigger_condition]--指定触发条件
6 [for each row]--指定行级触发器（每一条记录都触发一次）
7 trigger_body--触发体（程序块）

　　一个简单例子：

 1 create test(--创建测试表
 2     id int primary key,
 3     name varchar(20)
 4 )
 5 create or replace trigger t_test--创建触发器
 6 after insert or update or delete--触发操作（也可以是其中一种）
 7 on test--在表test上
 8 for each row--行级触发器
 9 begin
10     if inserting then--在插入数据时
11         dbms_output.put_line("插入了数据，name："||:new.name);
12     end if;
13     if updating then--在更新数据时
14         dbms_output.put_line("更新了数据，oldname："||:old.name||",newname:"||:new.name);
15     end if;
16     if deleting then--在删除数据时
17         dbms_output.put_line("删除了数据，name："||:old.name);
18     end if;
19 end;

　　谓词：new 表示引用新的数据（更新后或插入的数据），：old 引用旧的数据（被删除的或更新前的数据）。可以在创建触发器时通过 referencing（操作类型之后，for each row 之前）指定新的谓词。

1 ...
2 referencing old as test_old new as test_new
3 ...
4 --下面通过:test_old 引用修改前的数据，:test_new引用修改后的数据

　　测试代码：

1 insert into test values(1,"r");
2 update test set name="e" where id=1;
3 delete from test where id=1;
4 --注意观察输出结果

　　4，游标

　　Oracle 中的游标用来处理多行多列的数据集合，包含四个步骤：定义，打开，遍历，关闭。游标的语法如下：

1 cursor cursor_name [形参]--形参可以用来在where子句中限定游标记录
2 [return type]--可选的指定游标返回的值类型
3 is query--通过is指定查询（在这里使用形参）
4 [for update[of column_list]]--允许在游标中修改表中的数据，并在游标打开期间锁定选中的记录

　　下面是一个通过游标遍历输出 dept 部门信息的例子：

 1 declare
 2     deptrow dept%rowtype;--定义一个存储记录的变量
 3     cursor dept_cur is--通过cursor定义游标，is指定需要遍历的结果集（一个查询语句）
 4     select * from dept;
 5 begin
 6     open dept_cur;--打开游标
 7     loop--通过循环遍历游标中的记录
 8         fetch dept_cur into deptrow;--通过fetch提取游标中记录（每次一条）赋值给变量
 9         dbms_output.put_line(deptrow.deptno||":"||deptrow.dname);
10         exit when dept_cur%notfound;--通过%notfound判断游标中是否还有记录
11     end loop;
12     close dept_cur;--关闭游标
13 end;

　　游标除了 %notfound 还有以下常用的的属性：

1 cursor%isopen;--检测游标是否已打开，打开返回ture，否则返回false
2 cursor%found;--检测是否提取到值，提取到返回true，否者返回false
3 cursor%notfound;--与%found相反
4 cursor%rowcount;--统计到目前为止已提取的记录数

　　PL/SQL 中的三种循环都可以用来循环遍历游标中的记录，while 和 loop 相似，这里不再举例，for 循环专门对遍历游标做了强化，工作中使用最多，也最方便：

1 delcare
2     cursor dept_cur is
3     select * from dept;
4 begin
5     for dept_row in dept_cur loop
6         dbms_output.put_line(deptrow.deptno||":"||deptrow.dname);
7     end loop;
8 end;

　　dept_row 不需要显式的声明为记录类型，PL/SQL 引擎自动隐式的声明为 %rowtype。for 循环开始，自动打开游标，并自动提取记录，然后赋值给dept_row，不用显式的使用 fetch 提取记录，循环完毕自动关闭游标并退出循环。

　　5，包

　　Oracle 中包（package）是一个工程化和面向对象的概念，它就像一个容器或命名空间，把逻辑相关的变量、类型、子程序或异常等组合起来一起存放，形成一个有序的组织单元或模块，当我们编写大型的复杂的应用程序时，我们就可以通过包来方便的归类和管理各个功能模块。

　　完整的包由包规范和包体组成，但 Oracle 分开编译的存储包规范和包体，这又使得我们可以脱离包体使用包规范（反向不行）。包规范中主要是一些定义信息（也可以看成是 PL/SQL 提供的 API），比如记录类型、变量、游标、异常和子程序的声明。包体则负责实现包规范中定义的子程序。

　　包规范简单应用：

 1 create or replace package pkg1--创建包规范
 2 as
 3   i int := 1;--标量变量
 4   dept_record dept%rowtype;--rowtype类型
 5   type dept_tab is table of varchar(20) index by pls_integer;--集合类型
 6 end pkg1;
 7 
 8 declare
 9   mydept pkg1.dept_tab;--创建一个包中集合类型的变量（通过"包.内容"的方式访问包中的内容）
10 begin
11   select * into pkg1.dept_record from dept where deptno=10;--给包中定义的rowtype类型变量赋值
12   dbms_output.put_line(pkg1.dept_record.dname);--访问包中的rowtype类型变量
13   dbms_output.put_line("-------------------------------------------");--分割线
14   for deptrow in (select * from dept) loop--使用游标给包中的集合赋值
15     mydept(pkg1.i) := deptrow.dname;
16     pkg1.i := pkg1.i+1;--修改包中的标量变量
17   end loop;
18   for j in 1..mydept.count loop--使用循环访问集合
19     dbms_output.put_line(mydept(j));
20   end loop;
21   pkg1.i := 1;--初始化包中的标量变量（防止下一次游标读取不到数据）
22 end;

　　在这个例子中，我们只创建了包规范，没有包体，并且在包中定义了标量变量，rowtype类型（记录类型同理），集合这些基本的数据类型，然后在 PL/SQL 程序块中使用了他们。

　　包规范中只有声明，没有具体的实现，事实上，包规范中的声明的内容是公共的，对于一个方案来说，相当于一个全局的对象，在包内任何地方都能访问他们。包规范和包体分别进行独立的编译和存储，所以没有包体，上诉例子任然能正常运行。

　　另一个例子：

 1 create or replace package pkg2--创建包规范
 2 as
 3   cursor dept_cur return dept%rowtype;--定义游标类型
 4   procedure dept_ins(p_deptno int,p_dname varchar);--定义存储过程
 5   function f2 return varchar;--定义函数
 6 end pkg2;
 7 
 8 create or replace package body pkg2--创建包体
 9 as
10   cursor dept_cur return dept%rowtype--创建游标
11   is
12     select * from dept;
13   procedure dept_ins(p_deptno in int,p_dname in varchar)--创建存储过程
14   as
15   begin
16     insert into dept(deptno,dname) values(p_deptno,p_dname);
17     dbms_output.put_line("新增了部门："|| p_deptno||","||p_dname);
18   end dept_ins;
19   function f2 return varchar--创建函数
20   is
21   begin
22     return "这是个函数";
23   end f2;
24 end pkg2;
25 
26 
27 begin
28   for deptrow in pkg2.dept_cur loop--读取游标
29     dbms_output.put_line(deptrow.dname);
30   end loop;
31   pkg2.dept_ins(99,"TI");--执行存储过程
32   dbms_output.put_line(pkg2.f2());--执行函数
33 end;