ORACLE数据库之PL/SQL编程
1、PL/SQL简介
PL/SQL是Oracle在数据库中引入的一种过程化编程语言。PL/SQL构建于SQL之上，可以用来编写包含SQL语句的程序。可以通过IF语句或LOOP语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL语言就能够实现操控程序处理的细节过程。
1.1 PL/SQL块结构
PL/SQL程序是以块（BLOCK）为基本单位，整个PL/SQL块分三部分：
声明部分（用DECLARE开头）、执行部分（以BEGIN开头）和异常处理部分（以EXCEPTION开头）。其中执行部分必选，其他两个部分可选。
[DECLARE]
--声明部分，可选
BEGIN
--执行部分，必须
[EXCEPTION]
--异常处理部分，可选
END
每一条语句都必须以分号结束。
1.2 代码注释和标示符
1）单行注释
由两个连接符号“--”开始，后面紧跟着注释内容。
2）多行注释
由/开头，以/结尾。
3）标识符
标识符必须满足以下条件：
当定义变量、常量时，每行只能定义一个变量或者常量。
当定义变量、常量时，名称必须以英文字符（AZ、az）开始，并且最大长度为30个字符。如果以其他字符开始，那么必须使用双引号引住。
当定义变量、常量时，名称只能使用AZ、az、0~9、_、$和#。如果以其他字符开始，那么必须使用双引号引住。
当定义变量、常量时，名称不能使用Oracle关键字。如果要使用Oracle关键字，那么必须使用双引号引住。
合法的字符集主要包含以下内容：
大写和小写字母：AZ或az
数字：0~9
非显示的字符：制表符、空格和回车
数字符号：+、-、*、/、>、<、=等
间隔符：包括（）、{}、？、！、；、：、@、#、%、$、&等
4）分界符
用来将标识符相互分隔开。
+、-、、/、=、>、<、（、）、：、%、'、<>、！=、~=、^=、<=、>=、@、/、：、**、：=、=>、..、||、<<、>>、--、/、*/、
1.3 文本
文本是指实际的数值的数值。包括数字文本、字符文本、字符串文本、布尔文本、日期时间文本等。
2、数据类型、变量、常量
2.1基本数据类型
1）数值类型
数值类型主要包含NUMBER、PLS_INTEGER和BINARY_INTEGER三种基本数据类型。其中NUMBER类型可以存储整数和浮点数，而PLS_INTEGER和BINARY_INTEGER类型只能存储整数。
NUMBER类型可以通过NUMBER(P,S)的形式来格式化字符串，P代表精度（数值中所有有效数字的个数），S代表刻度范围（小数点右面小数位的个数）。
Num_Money NUMBER(9,2) --声明一个精度为9，且刻度范围为2的表示金额的变量
与NUMBER类型等价的类型别名：DEC、DECIMAL、DOUBLE、INTEGER、INT、NUMERIC、SMALLINT、PLS_INTEGER、BINARY_INTEGER等。
2）字符类型
字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等，这些类型的变量用来存储字符串或字符数据。
VARCHAR2用来存储可变长度的字符串
VARCHAR2(maxlength) --maxlength用来表示存储字符串的最大长度，最大值为32727,在定义变量时必须给出。
CHAR类型表示指定长度的字符串。如果赋给CHAR类型的值不足maxlength则会在其后面用空格补全。
CHAR(maxlength) --maxlength用来表示存储字符串的最大长度，最大值为32727,定义变量时不是必须给出，默认长度为1
LONG类型表示一个可变长度的字符串，最大长度为32767字节。
NCHAR和NVARCHAR2类型的长度根据各国字符集来确定。
3）日期类型
日期类型只有一种：DATE类型，用来存储日期和时间信息，存储空间为7个字节，分别使用一个字节存储世纪、年、月、日、时、分、秒。
4）布尔类型
布尔类型也只有一种：BOOLEAN，主要用来程序的流程控制和业务逻辑判断。其变量值可以是TRUE、FALSE、NULL中的一种。
2.2特殊数据类型
1）%TYPE类型
关键字可以声明一个与指定列相同的数据类型，他通常紧跟在指定列名的后面。
declare
var_job emp.job%type --声明一个与emp表中job列的数据类型完全相同的变量var_job
2）RECORD类型
RECORD类型可以存储由多个列值组成的一行数据。声明记录类型变量之前，需要先定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，使用type语句进行定义。
语法格式如下：
type record_type is record
(
var_member1 data_type[not null][:=default_value],
...
var_member data_type[not null][:=default_value]
)
3)%ROWTYPE类型
%ROWTYPE类型结合“%TYPE类型”和“记录类型”变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。语法形式如下：
rowVar_name table_name%rowtype
可以把“table_name%rowtype”看作是一种能够存储表中一行数据的特殊类型。
2.3定义变量和常量
1）定义变量
语法格式：
<变量名><数据类型>[(长度):=<初始值>]；
2）定义常量
语法格式：
<变量名>constant<数据类型>:=<常量值>;
3）变量初始化
PL/SQL定义了一个未初始化变量应该存放的内容，其被赋值未NULL。
2.4 PL/SQL表达式
1）字符表达式
唯一的字符运算符就是并置运算符“||”，它的作用是把几个字符连在一起。例如'Hello'||'World'||'!'的值就是'Hello World!'
2）布尔表达式
布尔表达式是一个判断结果为真还是为假的条件。它的值只有TRUE、FALSE、NULL。例如：(x>y)、NULL、(4>5)OR(-1<0)
布尔表达式由3个布尔运算符：AND、OR和NOT。它们的操作对象是布尔变量或表达式。
布尔表达式的算术运算符有：=、<、<=、!=、>、>=
此外，between操作符可以划定一个范围，在范围内为真，否则为假。如：1 between 0 and 100 表达式的值为真。
IN操作符判断一个元素是否数据某个集合。如:'Scott'IN('Mike'、'Jone'、'Mary')为假。
3、流程控制语句
3.1 选择语句
1)IF...THEN语句
if <condition_expression> then
plsql_sentence;
end if
2)IF...THEN...ELSE语句
if <condition_expression> then
plsql_sentence_1;
else
plsql_sentence_2;
end if
3)IF...THEN...ELSIF语句
if <condition_expression1> then
plsql_sentence_1;
elsif<condition_expression> then
plsql_sentence_2;
...
else
plsql_sentence_n;
end if
4)CASE语句
case<selector>
when<expression_1> then plsql_sentence_1;
when<expression_2> then plsql_sentence_2;
...
when<expression_n> then plsql_sentence_n;
[else plsql_sentence;]
end case;
3.2 循环语句
1）LOOP语句
LOOP语句先执行一次循环体，然后判断‘EXIT WHEN’关键字后面的条件表达式的值是true还是false。如果是true，退出循环体。如果是false，再次执行循环体。
loop
plsql_sentence;
exit when end_condition_exp
end loop;
2）WHILE语句
while condition_expression loop
plsql_sentence;
end loop;
3）FOR语句
for variable_counter_name in[reverse] lower_limit...upper_limit loop
plsql_sentence;
end loop;
4)GOTO语句
无条件转向语句。
GOTO label;
4、PL/SQL游标
游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。
4.1 基本原理
游标操作数据主要包含显式游标和隐式游标。
显式游标是由用户声明和操作的一种游标；隐式游标是Oracle为所有数据操纵语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。
4.2 显式游标
显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回id查询结果）。
使用它处理数据的步骤：声明游标、打开游标、读取游标和关闭游标。其中读取游标可能是一个反复操作的步骤，因为游标每次只能读取一行数据，所以对于多条记录，需要反复读取，直到游标读取不到数据为止。
1）声明游标
语法格式：
cursor cur_name[input_parameter1[,input_parameter2]...]
[return ret_type]
is select_sentence;
2)打开游标
语法格式：
open cur_name[(para_value1[,para_value1]...)]
打开游标就是执行定义的select语句。执行完毕，查询结果装入内存，游标停在查询结果的首部。
3）读取游标
读取游标就是逐行将结果集中的数据保存到变量中。语法格式如下：
fetch cur_name into {variable};
刚刚打开游标时，指针指向结果集中的第一行，当使用FRTCH...INTO语句读取数据完毕，游标指针自动指向下一行数据。此时循环结构中使用FRTCH...INTO语句，每次循环都会从结果集中读取一行数据，直至指针指向结果集中最后一条记录之后为止。
4）关闭游标
语法格式：
close cur_name;
举例：
set serveroutput on
declare
/*声明游标*/
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;
type record_emp is record --声明一个记录类型
(
/*定义当前记录的成员变量*/
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;--声明一个record_emp类型的变量
begin
open cur_emp('MANAGER'); --打开游标
fetch cur_emp into cur_row; --先让指针指向结果集的第一行，并把值保存在cur_row中
while cur_emp%found loop
dbms_output.put_line(cur_row.var_ename||'的编号是'||cur_row.var_empno||',工资是'||cur_row.var_sal)
fetch cur_emp into cur_row; --让指针指向结果集的下一行，并把值保存在cur_row中
end loop;
close cur_emp; --关闭游标
end;
4.3 隐式游标
在执行一个SQL语句时，Oracle会自动创建一个隐式游标。
隐式游标主要是处理数据操纵语句（如UPDATE、DELETE）的执行结果，当特殊情况下，也可以处理SELECT语句的查询结果。
由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称--SQL。
举例如下
set serveroutput on
begin
update emp
set sal=sal*(1+0.2)--把销售员的工资上调20%
where job='SALESMAN';
if sql%notfound then --如果update语句没有影响到任何一条数据
dbms_output.put_line('没有雇员需要上调工资')
else --如果update语句至少影响到一条数据
dbms_output.put_line（'有'||sql%rowcount||'个雇员工资上调20%'）；
end if;
end;
4.4 游标的属性
1）是否找到游标（%FOUND）
该属性表示当前游标是否指向有效一行，若是则值为TRUE，否则值为FALSE。
检查此属性可以判断是否结束游标使用。
open cur_emp; --打开游标
fetch cur_emp into var_ename,var_job; --将第一行数据放入变量中，游标后移
loop
exit when not cur_emp%found;
end loop;
在隐式游标中此属性的引用方法是SQL%FOUND。
delete from emp where empno=emp_id;--emp_id为一个有值变量
if SQL%FOUND then --如果删除成功，写入SUCCESS表中该行员工编码
insert into success values(empno);
else --如果删除不成功，写入fail表中该行员工编码
insert into fail values(empno);
2）是否没找到游标（%NOTFOUND）
该属性与%FOUND属性类型相似，但其值恰好相反。
open cur_emp; --打开游标
fetch cur_emp into var_ename,var_job; --将第一行数据放入变量中，游标后移
loop
exit when cur_emp%notfound;
end loop;
在隐式游标中此属性的引用方法是SQL%NOTFOUND。
delete from emp where empno=emp_id;--emp_id为一个有值变量
if SQL%NOTFOUND then --如果删除不成功，写入fail表中该行员工编码
insert into fail values(empno);
else --如果删除成功，写入sucess表中该行员工编码
insert into sucess values(empno);
3）游标行数（%ROWCOUNT）
该属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行数。
loop
fetch cur_emp into var_ename,var_job; --将一行数据放入变量中，游标后移
exit when cur_emp%ROWCOUNT=10;--只抽取10条记录
end loop;
4）游标是否打开（%ISOPEN）
该属性表示游标是否处于打开状态。
IF cur_emp%ISOPEN THEN
fetch cur_emp into var_ename,var_job;
ELSE
OPEN cur_emp;
END IF;
在隐式游标中此属性的引用方法是SQL%ISOPEN。在隐式游标中SQL%ISOPEN属性总为TRUE。因此在隐式游标中不用打开和关闭游标，也不用检查游标状态。
5）参数化游标
在定义游标时，可以带上参数，使得在游标使用时，根据参数不同所选中的数据行也不同，达到动态使用的目的。
set serveroutput on
declare
var_ename varchar2(50);--声明变量，用来存储雇员名称
var_job varchar2(50);--声明变量，用来存储雇员职务
/*声明游标，检索指定员工编号的雇员信息*/
cursor cur_emp --定义游标，检索指定员工编号的雇员信息
is select ename,job
from emp
where empno=7499;
begin
open cur_emp; --打开游标
fetch cur_emp into var_ename,var_job; --读取游标，并存储雇员名称和职务
if cur_emp%found then --若检索到数据，则输出雇员信息
dbms_output.put_line('编号为7499的雇员名称为：'||var_ename||',职务是：'||var_job)
else --提示无记录信息
dbms_output.put_line（'无数据记录'）；
end if;
end;
4.5 游标变量
前面所讲的游标都是与一个SQL语句相关联，并且编译该块的时候此语句已经是可知的，是静态的。
游标变量可以在运行时与不同的语句关联，是动态的。
游标变量被用于处理多行的查询结果集。
在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时才能确定所对应的查询。因此游标变量可以一次对应多个查询。
在使用游标变量之前，必须先声明游标变量。
1）声明游标变量
游标变量是一种引用类型。在程序运行时，可以指向不同的存储单元。
定义游标变量：
TYPE <类型名> is REF CURSOR
RETURN <返回类型>
<类型名>表示新的引用类型名字，<返回类型>是一个记录类型，指明最终由游标变量返回的选择列表的类型。
declare
--定义非受限游标变量
TYPE t_FlexibleRef IS REF CURSOR;
--游标变量
V_CURSORVar t_FlexibleRef；
2）打开游标变量
如果要将一个游标变量与一个特定的SELECT语句相关联，需要使用OPEN FOR语句，其语法格式是：
OPEN <游标变量> FOR <SELECT语句>
如果游标变量是受限的，则SELECT语句的返回类型必须与游标所限的记录类型匹配。
declare
--定义受限游标变量
TYPE t_StudentRef IS REF CURSOR
RETURN STUDENTS%ROWTYPE;
--游标变量
V_STUDENTVar t_StudentRef； --定义新的记录类型
BEGIN
OPEN V_STUDENTVar FOR
SELECT * FROM STUDENTS;
END;
3)关闭游标变量
CLOSE语句。
4.6 通过for语句循环游标
1）隐式游标
set serveroutput on
begin
for emp_record in(select empno,ename,sal from emp where job='SALESMAN')--遍历隐式游标中的记录
loop --遍历雇员信息
dbms_output.put('雇员编号：'||emp_record.empno); --输出雇员编号
dbms_output.put(';'||'雇员名称：'||emp_record.empno);--输出雇员名称
dbms_output.put(';'||'雇员工资：'||emp_record.empno);--输出雇员工资
end loop;
end;
2）显式游标
set serveroutput on
declare
cursor cur_emp is
select * from emp
where depno=30 --检索部门编号为30的雇员
begin
for emp_record in cur_emp --遍历显式游标中的记录
loop --遍历雇员信息
dbms_output.put('雇员编号：'||emp_record.empno); --输出雇员编号
dbms_output.put(';'||'雇员名称：'||emp_record.empno);--输出雇员名称
dbms_output.put(';'||'雇员工资：'||emp_record.empno);--输出雇员工资
end loop;
end;
