Oracle学习笔记5 PL/SQL编程

一、PL/SQL简介

1、PL/SQL块结构

[DECLARE]

--声明部分，可选

BEGIN

--执行部分，必须

[EXCEPTION]

--一场处理部分，可选

END

(1)声明部分由关键字DECLARE开始，到BEGIN结束。在这部分可以声明PL/SQL程序块中所用到的变量、常量和游标。需要注意的是：在某个PL/SQL块中声明的内容只能在当前块中使用，而在其他PL/SQL块中是无法引用的。

(2)执行部分以BEGIN开始，它的结束方式通常有两种。如果PL/SQL块中的代码在运行时出现异常，则执行完异常处理部分的代码就结束；如果没有使用异常处理或PL/SQL块未出现异常，则以关键字END结束。

(3)异常处理部分以关键字EXCEPTION开始，在该关键字所包含的代码执行完毕后，整个PL/SQL块就结束了。对于可能出现的多种异常情况，用户可以使用WHEN  THEN语句来实现多分支判断，然后再每个分支下通过编写代码来处理相应的异常；

(4)对于PL/SQL块中的语句，需要指出的是：每一条语句必须以分号结束，每条SQL语句可以写成多行的形式，同样必须使用分号来结束。另外，一行中也可以有多条SQL语句，但是他们之间必须以分号隔开。

2、代码注释和标识符

(1)单行注释

单行注释由两个连接字符--开始，后面紧跟这注释内容；

(2)多行注释

多行注释由/*开头，以*/结尾，这种多行注释的方法再大多数的编程语言中是相同的。

 

二、数据类型、变量和常量

1、基本数据类型

(1)  数值类型

数值类型主要包括：NUMBER、PLS_INTEGER和BINARY_INTEGER三种类型；

(2)字符类型

字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等；这些类型的变量用来存储字符串或者字符数据。

(3)日期类型

日期类型只有一种类型，用来存储日期和时间信息，DATE类型的存储空间是7个字节，分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。

(4)布尔类型

布尔类型也只有一种——即BOOLEAN，主要用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE、FALSH或NULL中的一种。

2、特殊数据类型

(1) %TYPE类型

使用%TYPE关键字可以声明一个与指定列明相同的数据类型，它通常紧跟再指定列明的后面

示例1：声明一个与表EMP中job列相同的数据类型。

SQL> declare 

           var_job emp.job%type;

示例2：使用%TYPE类型的变量输出EMP表中编号为7369的员工名称和职务信息。

set serveroutput on                                                            --在服务端输出
declare
var_ename emp.ename%type;                                          --声明与ename列类型相同的变量
var_job emp.job%type;                                                      --声明与job列类型相同的变量
begin
select ename,job
into var_ename,var_job
from emp
where empno=7369;                                                         --检索数据，并保存在变量中
dbms_output.put_line(var_ename||'的职务是'||var_job);   --输出变量的值
end;
/

(2)RECORD类型

RECORD类型也称作记录类型，使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用TYPE语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型，其语法格式如下：

type record_type is record 

（

     var_member1 data_type [not null] [:=default_value,

     ...

     var_membern data_type [not null] [:=default_value]）

record_type ：表示要定义的记录类型名称；

var_member1：表示该记录类型的成员变量名称；

data_type：表示成员变量的数据类型。

示例1：声明一个记录类型emp_type，然后使用该类型的变量存储emp表中的一条记录信息，并输出这条记录信息；

set serveroutput on                                            --输出到服务端
declare
type emp_type is record                                    --声明record类型emp_type
(
var_ename varchar2(20),                                   --定义字段
var_job varchar2(20),
var_sal number
);
empinfo emp_type;                                             --定义变量
begin
select ename,job,sal
into empinfo
from emp
where empno=7369;                                           --检索数据

dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||'、工资是'||empinfo.var_sal);                                          --输出雇员信息
end;
/

(3)%ROWTYPE类型

%ROWTYPE类型的变量结合了%TYPE类型和记录类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索出的一行数据，他的语法形式如下：

rowVar_name table_name%ROWTYPE;

rowVar_name：表示可以存储一行数据的变量名；

table_name：指定的表名；

示例1：声明一个%ROWTYPE类型的变量rowVar_emp，然后使用该变量存储emp表中的一行数据；

set serveroutput on                                                 --输出到服务端
declare
rowVar_emp emp%rowtype;                                   --定义能够存储emp表中一行数据的变量rowVar_emp
begin
select *
into rowVar_emp
from emp
where empno=7369;--检索数据

dbms_output.put_line('雇员'||rowVar_emp.ename||'的编号是'||rowVar_emp.empno||',职务是'||rowVar_emp.job);   /*输出雇员信息*/

end;
/

3、定义变量和常量

(1) 定义变量

<变量名> <数据类型> [(长度) :=<初始值>] ；

(2) 定义常量

<常量名> constant <数据类型> :=<常量名> ;

 

三、流程控制语句

1、选择语句

(1) IF...THEN语句

IF...THEN语句是选择语句中最简单的一种形式，它只做一种情况和条件的判断，其语法如下

IF<condition_expression> THEN

plsql_sentence

END IF;

condition_expression：表示一个表达式，当其值为TRUE时，程序会执行IF下面的PL/SQL语句（即plsql_sentence语句）；如果其值为FLASE，则程序会跳过IF下面的语句而直接执行END IF 后面的语句。

plsql_sentence：当condition_expression表达式的值为TRUE时，要执行的PL/SQL语句。

示例1：定义两个字符串变量，然后赋值，接着使用IF...THEN语句比较两个字符串变量的长度，并输出比较结果。

set serveroutput on            --输出到服务端
declare
var_name1 varchar2(50);  --定义变量1
var_name2 varchar2(50);  --定义变量2
begin
var_name1:='East';             --为变量1赋值
var_name2:='xiaoke';          --为变量2赋值
if length(var_name1) < length(var_name2) then  --比较两个字符串的长度大小
dbms_output.put_line('字符串“'||var_name1||'”的长度比字符串“'||var_name2||'”的长度小');
end if;
end;
/

(2) IF...THEN...ELSE

IF...THEN...ELSE语句是最常用到的一种选择语句，它可以实现判断两种情况，只要IF后面的变大时为FALSE，程序就会执行ELSE语句下面的PL/SQL语句。其语法格式如下：

IF<condition_expression> THEN

plsql_sentence1;

ELSE

plsql_sentence2

END IF;

condition_expression：表示一个表达式，当其值为TRUE时，程序会执行IF下面的PL/SQL语句（即plsql_sentence1语句）；否则，程序将执行ELSE下面的OL/SQL语句，即plsql_sentence2语句。

plsql_sentence1：IF语句的表达式值为TRUE时，要执行的PL/SQL语句；

plsql_sentence2：IF语句的表达式值为FLASE时，要执行的PL/SQL语句。

(3) IF...THEN...ELSIF语句

IF...THEN...ELSIF语句实现了多分支判断选择，它使程序的判断选择条件更加丰富，更加多样化。该语句中的哪个判断分支的表达式为TRUE，那么程序就会执行其下面对应的PL/SQL语句，其语法格式如下：

IF<condition_expression1> THEN

plsql_sentence_1;

ELSIF <condition_expression2> THEN

plsql_sencence_2;

...

ELSE

plsql_sentence_n;

END IF;

condition_expression_1：第一个表达式，若其值为FLASE，则程序继续判断condition_expression_2;

condition_expression_2：第二个表达式，若其值为FLASE，则程序继续判断下面的ELSIF语句后面的表达式，若再没有ELSIF语句，则程序将执行ELSE语句下面的PL/SQL语句；

plsql_sentence_1：第一个条件表达式的值为TRUE时，要执行的PL/SQL语句；

plsql_sentence_2：第二个条件表达式的值为TRUE时，要执行的PL/SQL语句；

plsql_sentence_n：当其上面所有的条件表达式的值都为FLASE时，要执行的PL/SQL语句。

示例1：指定一个月数值，然后使用IF...THEN...ELSIF语句判断它所属的季节，并输出季节信息。

set serveroutput on                                 ----输出到服务端
declare
month int:=10;                                         --定义整形变量并赋值
begin
if month >= 0 and month <= 3 then          --判断春季
dbms_output.put_line('这是春季');
elsif month >= 4 and month <= 6 then     --判断夏季
dbms_output.put_line('这是夏季');
elsif month >= 7 and month <= 9 then     --判断秋季
dbms_output.put_line('这是秋季');
elsif month >= 10 and month <= 12 then  --判断冬季
dbms_output.put_line('这是冬季');
else
dbms_output.put_line('对不起，月份不合法！');
end if;
end;
/

(4) CASE语句

CASE语句的执行方式与IF...THEN...ELSIF语句十分相似。在CASE关键字的后面有一个选择器，它通常是一个变量，程序就从这个选择器开始执行，接下来是WHEN子句，并且在WHEN关键字的后面是一个表达式，程序将根据选择器的值去匹配每个WHEN子句中的表达式的值，从而实现执行不同的PLSQL语句的功能，其语法格式如下：

CASE <selector>

     WHEN <expression_1> THEN plsql_sentence_1;

     WHEN <expression_1> THEN plsql_sentence_2;

...

     WHEN <expression_n> THEN plsql_sentence_n;

END CASE;

selector ：一个变量，用来存储要检测的值，通常称为选择器。该选择器的值需要与WHEN子句中的表达式的值进行匹配。

expression_1：第一个WHEN子句中的表达式，它通常是一个常量，当选择器的值等于该表达式的值时，程序将执行plsql_sentence_1语句；

expression_2：第二个WHEN子句中的表达式，它通常也是一个常量，当选择器的值等于该表达式的值时，程序将执行plsql_sentence_1语句；

expression_n：第n个WHEN子句中的表达式，它通常也是一个常量，当选择器的值等于该表达式的值时，程序将执行plsql_sentence_n语句；

plsql_sentence：一个PL/SQL语句，当没有与选择器匹配的WHEN常量时，程序执行该PL/SQL语句，其所在的EKSE语句是一个可选项；

示例1：指定一个季度数值，然后使用CASE语句判断它所包含的月份信息并输出。

set serveroutput on                                   --输出到服务端
declare
season int:=3;                                            --定义整形变量并赋值
aboutInfo varchar2(50);
begin
case season 
when 1 then
aboutInfo := season||'季度包括1，2，3月份';
when 2 then
aboutInfo := season||'季度包括4，5，6月份';
when 3 then
aboutInfo := season||'季度包括7，8，9月份';
when 4 then
aboutInfo := season||'季度包括10，11，12月份';
else
aboutInfo := season||'季节不合法';
end case;
dbms_output.put_line(aboutinfo);             ---输出结果
end;
/

2、循环语句

(1) LOOP语句

LOOP语句会先执行一次循环体，然后判断EXIT WHEN关键字后面的条件表达式的值是TRUE还是FLASE，如果是TRUE，则程序会推出循环体，否则程序将再次执行循环体，这样就使得程序至少能够执行一次循环体，其语法格式如下：

LOOP

    plsql_sentence;

    EXIT WHEN end_condition_exp

END LOOP;

plsql_sentence：循环体中的PL/SQL语句，可能是一条语句，也可能是多条，这是循环体的核心部分，这些PL/SQL语句至少会被执行一遍。

end_condition_exp：循环结束条件表达式，当该表达式的值为TRUE时，程序会退出循环体，否则程序将再次执行循环体。

(2) WHILE语句

WHILE语句根据它的条件表达式的值执行零次或多次循环体，在每次执行循环体之前，首先要判断条件表达式的值是否为TRUE，若为TRUE，则程序执行循环体；否则推出WHILE循环，然后继续执行WHILE语句后面的其他代码，其语法格式如下：

WHILE condition_expression LOOP

    plsql_sentence;

END LOOP;

condition_expression：表示一个条件表达式，当其值为TRUE时，程序执行循环体，否则程序退出循环体，程序每次执行循环体之前，都要首先判断该表达式的值是否为TRUE。

plsql_sentence：循环体内的PLSQL语句。

示例1：使用WHILE语句球的前100个自然数的和，并输出结果。

set serveroutput on
declare
sum_i int:= 0;                                   --定义整数变量，存储整数和
i int:= 0;                                            --定义整数变量，存储自然数
begin
while i<=99 loop
i:=i+1;--得出自然数
sum_i:= sum_i+i;--计算前n个自然数的和
end loop;
dbms_output.put_line('前100个自然数的和是：'||sum_i);--计算前100个自然数的和
end;
/

(3) FOR语句

FOR语句时一个可预置循环次数的循环控制语句，它有一个循环计数器，通常是一个整型变量，通过这个循环计数器来控制循环执行的次数。该计数器可以从小到大进行记录，也可以相反，从大到小进行记录。另外，该计数器值的合法性由上限值和下限值控制，托计数器值在上限值和下限值的范围内，则程序执行循环，否则，种植循环，其语法格式如下：

FOR variable_counter_name in [REVERSE] lower_limit...upper_limit LOOP

plsql_sentence;

END LOOP;

variable_counter_name：表示一个变量，通常为整数类型，用来作为计数器。默认情况下计数器的值会循环递增，当在循环中使用REVERSE关键字时，计数器的值会随循环递减。

lower_limit：计数器的下限值，当计数器的值小于下限值时，程序终止FOR循环；

upper_limit：计数器的上限值，当计数器的值大于上限值时，程序终止FOE循环；

plsql_sentence：表示PL/SQL语句，作为FOE语句的循环体。

示例1：使用FOR语句求得前100个自然数中偶数之和，并输出结果；

set serveroutput on
declare
sum_i int:= 0;                               --定义整数变量，存储整数和
begin
for i in reverse 1..100 loop           --遍历前100个自然数
if mod(i,2)=0 then                        --判断是否为偶数
sum_i:=sum_i+i;                          --计算偶数和
end if;
end loop;
dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i);
end;
/

四、 PL/SQL游标

1、显式游标

显示游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回的查询结果），使用它处理数据的步骤包括声明游标、打开游标、读取游标和关闭游标4个步骤。

(1) 声明游标

声明游标主要包括游标名称和为游标提供结果集的SELECT语句。因此声明游标时，必须指定游标名称和游标所使用的人SELECT语句，声明游标格式如下：

CURSOR cur_name[input_parameter1 [,input_parameter2]...]

[RETURN ret_type]

IS select_sentence;

cur_name：表示所声明的游标名称；

ret_type：表示执行游标操作后的返回值类型，这是一个可选项；

select_srntence：游标所使用的SELECT语句，它为游标的反复读取提供了结果集；

input_parameter1：作为游标的输入参数，可以有多个，这是一个可选项。它指定用户在打开游标后向游标中传递的值，该参数的定义和初始化格式如下：

para_name [IN] DATATYPE [{:= | DEFAULT}para_value]

其中，para_name表示参数名称，其后面的关键字IN表示输入方向，可以省略；DATATYPE表示参数的数据类型，但数据类型不可以指定长度；para_value表示该参数的初始值或默认值，它也可以时一个表达式；para_name参数的初始值既可以以常规的方式赋值（:=）,也可以使用关键字DEFAUKT初始化默认值。

示例：声明一个游标，用来读取emp表中职务为销售员的雇员信息：

declare
/*声明游标，检索雇员信息*/
cursor cur_emp (var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;

(2)打开游标

在声明游标完毕之后，必须打开才能使用，打开游标的语法格式如下：

OPEN cur_name[(para_value1[,para_value2]...)]

cur_name：要打开的游标名称；

oara_value1：指定输入参数的值，根据声明游标时的实际情况，可以时多个或一个，这是一个可选项。如果在声明游标时定义了输入参数，并初始化其值，而在此处省略“输入参数”的值，则表示游标将使用“输入参数”的初始值；若在此处指定“输入参数”的值，则表示游标将使用这个指定的参数值。

打开游标就是执行定义的SELECT语句。执行完毕，查询结果装入内存，游标停在查询结果的首部，注意并不是下一行。当打开一个游标时，会完成以下几件事。

检查连编变量的取值；

根据联编变量的取值，确定活动集；

活动集的指针指向第一行。

紧接上一个例子的代码，打开游标的代码如下：

open cur_emp('MANAGER');

上边这表语句表示打开游标’cur_emp‘,然后给游标的’输入参数‘赋值为“MANAGER”。当然这里可以省略“（'MANAGER'）”，这样表示“输入参数”的值仍然使用其初始值（即SALESMAN）.

(3)读取游标

当打开一个游标之后，就可以读取游标中的数据了，读取游标就是逐行将结果集中的数据保存到变量中。读取游标使用FETCH...INTO语句，其语法格式：

FETCH cur_name IN {variable};

cur_name：要读取的游标的名称;

variable：一个变量列表或“记录”变量（RECORD类型），Oracle使用记录变量来存储游标中的数据，要比使用变量列表方便得多。

示例：声明一个检索emp表中雇员信息的游标，然后打开游标，并指定检索职务时MANAGER的雇员信息，接着使用FETCH...INTO语句和WHILE循环语句读取游标中的所有雇员信息，最后输出读取的雇员信息。

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 --声明一个记录类型（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 emp_row;--先让指针指向结果集中的第一行
while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||',工资是'||emp_row.var_sal);
fetch cur_emp into emp_row;--让指针指向结果集中的下一行
end loop;
close cur_emp;
end;
/

(4)  关闭游标

当所有的活动集都被检索以后，游标就应该被关闭。PL/SQL程序将告知对于游标的处理已经结束，与游标相关联的资源可以被释放了。这些资源包括用来存储活动集的存储空间，以及用来存储活动集的临时空间。关闭游标的语法格式如下：

CLOSE cur_name；

参数cur_name表示要关闭的游标名称。一旦关闭了游标，也就关闭了SELECT操作，释放了占用的内存区。如果再从游标提取数据就是非法的，这样做会产生下面的Oracle错误。

ORA-1001:lnvalid CUSOR   ---非法游标

或

ORA-1002:FETCH out of sequence --超出界限

类似的，关闭一个已经被关闭的游标也是非法的，这也会触发ORA-1001错误。

例如：上个例子中，在读取完结果集之后，使用如下的CLOSE语句关闭游标

SQL> close cur_emp;---关闭游标

2、隐式游标

在执行一个SQL语句时，Oracle会自动创建一个隐式游标。这个游标时内存中处理该语句的工作区域。隐式游标主要时处理数据操作语句（如UPDATE、DELETE语句）的执行结果，当然特殊情况下，也可以处理SELECT语句的查询结果。由于饮食游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称——SQL。

3、游标的属性

无论时显式游标还是隐式游标，都具有%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN4个属性，通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息。它们描述与游标操作相关的DML语句的执行情况。游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句内。下面对4个游标属性进行讲解：

(1)是否找到游标%FOUND

该属性表示当前游标是否指向有效一行，若是则值为TRUE，否则值为FALSE。检查此属性可以判断是否结束游标使用；

(2)是否没找到游标%NOTDOUND

该属性与%FOUND属性相类似，但其值恰好相反。

(3)游标行数%ROWCOUNT

该属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号。这个属性在循环判断中也很有用，使得不必抽取所有记录行就可以终端游标操作；

(4)游标是否打开%ISOPEN

该属性表示游标是否处于打开状态。在实际应用中，使用一个游标前第一步往往是检查它得%ISOPEN属性，看其是否已打开，若没有，要打开游标在向下操作。这是防止运行过程中出错得必备一步。

4、游标变量

(1)声明游标变量

游标变量是一种引用类型。当程序运行时，他们可以指向不同得寻存储单元。如果要使用引用类型，首先要声明变量，然后相应得存储单元必须被分配。PL/SQL中得引用类型通过下述的而语法进行声明。

REF type

其中type是已经被定义的类型。REF关键字指明新的类型必须是一个指向经过定义的类型的指针。因此，游标可以使用的类型就是REF CURSOR.

定义一个有游标变量类型的完整语句如下：

TYPE<类型名> IS REF CURSOR

RETURN<返回类型>

其中，<类型名>是新的引用类型的名字，而<返回类型>是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。

(2)打开游标变量

如果要将一个游标变量与特定的SELECT语句相关联，需要使用OPEN FOR语句，其语法如下：

OPEN<游标变量>FOR<SELECT>语句；

如果游标变量是受限的，则SELECT语句的返回类型必须与游标所限的记录类型匹配，如果不匹配，Oralce会返回错误ORA-6504。
(3)关闭游标变量

游标变量的关闭与静态游标的关闭类似，都是使用CLOSE语句。

5、通过FOR语句循环游标

在使用隐式游标或显式游标处理具有多行的结果集时，用户可以配合使用FOR语句来完成。在使用FOR语句遍历游标中的数据时，可以把它的计时器看作是一个自动的RECORD类型的变量。

(1)在FOR 语句中遍历隐式游标中的数据时，通常在关键字IN的后面提供由SELECT语句检索的结果集，在检索结果集的过程中，Oracle系统会自动提供一个隐式的游标SQL;

(2)在FOR语句中遍历显式游标中的数据时，通常在关键字IN的后面提供游标的名称，其语法格式如下：

FOR var_auto_record IN cur_name LOOP

plsqlsentence;

END LOOP;

var_auto_record：自动的RECORD类型的变量，可以是任意合法的变量名称；

cur_name：指定的游标名称；

plsqlsentence：PL/SQL语句；