PL/SQL块结构与用途

PL/SQL块结构与用途

作用：如果不使用PL/SQL语言，oracle一次只能处理一条SQL语句。每条SQL语句都导致客户（client）向服务器(server)调用，从而在性能上产生很大的开销，尤其是在网络操作中。如果使用PL/SQL,一个块中的语句作为一个组，导致客户向服务器的一次调用，减少网络转输

PL/SQL块结构与用途
一个基本的PL/SQL块由三部分组成：定义部分、可执行部分以及例外处理部分：
定义部分：
定义将在可执行部分中调用的所有变量、常量、游标和用户自定义的例外处理。这部分可以没有。
可执行部分：
包括对数据库中进行操作的SQL语句，以及对块中进行组织、控制的PL/SQL语句。这部分必须存在。
例外处理部分：
对可执行部分中的语句，在执行过程中出错或出现非正常现象时所做的相应处理。这部分可以没有。

由基本的PL/SQL块组成PL/SQL程序，可组成不同的程序形式，它们的用途和适用性各不相同。程序形式大致有以下几种：
1、无名块：也就是没有命名的PL/SQL块，它可以是嵌入某一个应用之中的一个PL/SQL块。
2、存储过程/函数：也就是命名了的PL/SQL块，它可以接收参数，并且可以重复地被调用。
3、包
命名了的PL/SQL模块，由一组相关的过程、函数和标识符组成。
4、数据库触发器：是一个与具体数据库表相关联的存储PL/SQL程序。每当一个SQL操作影响到该数据库表时，系统就自动执行相应的数据库触发器。每个表最多可以有12个触发器。

PL/SQL块的定义部分
变量声明语法：
标识符 [constant] 数据类型 [not null]
[:=默认值或PL/SQL表达式]
注意的几点（书p73):
加上关键字CONSTANT，则表示所定义的标识符为一个常量，必须为它赋初值。
如果定义的标识符不能为空，则必须加关键字NOT NULL，并赋初值。
“：=”为赋值操作符。
PL/SQL提供了SQL没有的附加数据类型。除一般的ORACLE SQL数据类型外，PL/SQL还可以使用这些数据类型对变理进行说明
1、BOOLEAN：可用预定义的常量TRUE、FALSE或NULL对一个布尔变量赋值。
2、binary_integer（二进制整数):数值范围在 -2,147,483,647到2,147,483,647之间。
3、NATURAL（自然数）:数值范围在0到2,147,483,647之间。
4、POSITIVE(正整数):数值范围在1到2,147,483,647之间。
5、%TYPE：可说明一个变量的数据类型与某一指定列的数据类型相同。
6、%ROWTYPE: 用这种数据类型可以说明一个复合变量，与某一特定有中的一行相同。
变量声明分标量型变量声明和组合变量（复合变量）声明。标量型变量是指其内部没有成员的变量。
例：
age number(5) not null:=25;
pi constant number(9):=3.1415926;
name char(10) not null:=‘fan’;
today date not null:=sysdate;
sex boolean:=true;
例：声明一个变量Student_name,其类型基于另一个变量teacher_name。
Teacher_name char(10);
Student_name teacher_name%type;
例: 声明一个变量No,使其与表emp中EMPNO的类型一致。
no emp.empno%type;
组合型变量内部包含若干个成员，每个成员由标量型变量或组合型变量组成
定义组合型变量的语法如下：
type <类型名> is record
(<域名1> {<标量型数据类型> | <record类型>}[not null],
<域名2> {<标量型数据类型> | <record类型>}[not null],
…….);
<标识符> <类型名>;
例：定义一个变量，存放一个学生的有关信息。
declare
type student is record /*定义组合型变量类型*/
(id number(4) not null:=0,
name char(10) not null:=' ',
sex boolean not null:=true,
birthday date,
physics number(3),chemistry number(3));
stu student; /*定义组合型变量*/
begin
stu.id:=1;
stu.name:='sheng';
stu.sex:=true;
end;
例：声明一个变量，其结构与表emp的数据结构相一致。
declare
emp_value emp%rowtype;
Begin
select * into empvalue from emp
where empno=7369;
dbms_output.put_line(‘姓名：’||‘ ’||emp_value.ename);
End;
注：在运行些PL/SQL块前，应先运行
Set serveroutput on /*使dbms_output.put_line可以显示在屏幕上*/

可执行部分
可执行部分可以包含变量赋值语句、数据查询、数据操纵和事务控制语句。如select、insert、update、delete、commit、rollback等语句。而不能使用CREATE，ALTER，DROP，GRANT，REVOR等数据定义或数据控制命令。
给标量型变量赋值
teacher_name:=‘liu’;
给record类型变量赋值语法:
<record名.域名>:=<pl/sql表达式>;
例:
stud.name:=‘fan’ student.sex:=true;
%rowtype型变量的赋值与record相同。
例：计算表emp中所有雇员的平均工资。
declare
avg_sal number(7,2);
begin
select avg(sal) into avg_sal from emp;
dbms_output.put_line(‘平均工资为:’||avg_sal);
End;
在运用select语句查询时注意查询的结果只能有一条，如果返回的查询结果多于一条或没有找到任何数据，则会产生异常。

事务控制命令
一、事务的概念
事务是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。事务通常以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK操作结束，COMMIT即提交，提交事务中所有的操作、事务正常结束。ROLLBACK即撤消已作的所有操作，滚回到事务开始时的状态。
二、事务提交命令
作用：提交自上次提交以后对数据库中数据所作的改动。事务提交以后，这些操作就不能再撤消。
事务提交有3种方式：
1、显式提交:使用commit命令
2、隐式提交:有些命令，如alter、audit、comment、connect、create、disconnect、
drop、exit、grant、noaudit、revoke、rename命令等都隐含有commit操作,而无须指明该操作。
3、自动提交
用户可以使用set命令来设置自动提交环境。经过设置后，sql/plus会自动提出交用户的更新工作。一旦设置了自动提交，用户每次执行insert、update或delete命令，系统就会立即自动进行提交。
命令为:
set auto on
三、事务回退
作用：尚未提交的UPDATE，INSERT，DELETE操作，可以用事务回退命令回退，回退之后数据库将回到上次COMMIT后的状态。
命令： rollback
四、保存点
作用：可以把一个事务划分成若干部分，每一部分之间用一个保存点分隔。
格式：
savepoint 保存点名;
rollback to 保存点名;

PL/SQL流程控制
主要有三种：
条件控制
循环控制
跳转控制
有两种形式:
1、 IF_THEN_ELSE语句
语法格式:
if 条件 then
语句1；
语句2;
…… else
语句n;
语句n+1;
…… end if;
2、IF_THEN_ELSE_IF语句
语法格式:
IF 条件1 THEN
语句1;
语句2;
……Elsif 条件2 THEN
语句3;
语句4;
[ELSIF 条件 n THEN
……]
[ELSE
语句n+1
……]
END IF
1、根据下式计算y值

x+1 x>3
y= X×2 X>=0
X÷3 X<0
PL/SQL实现语句1:
declare
x number(4);
y number(10,2);
begin
x:=&x; /* 实现从键盘输入一个值给X */
if x>=3 then
y:=x+1;
elsif x>=0 then
y:=x*2;
else
y:=x/3;
end if;
dbms_output.put_line(y); /* 输出Y值 */
end;
PL/SQL实现语句2:declare
x number(4);
y number(10,2);
begin
x:=&x;
if x>=3 then
y:=x+1;
else if x>=0 then
y:=x*2;
else
y:=x/3;
end if;
end if;
dbms_output.put_line(y);
end;
注意:条件语句中可以没有else 语句如:
declare
x number(4);
y number(10,2);
begin
x:=&x;
if x>=3 then
y:=x+1;
end if;
dbms_output.put_line(y);
end;
循环控制语句
有四种类型：
FOR循环
直到型循环
当型循环
简单循环
FOR循环
语法格式：
For 计数器 in [reverse] 下界..上界
Loop
语句1;
语句2;
……END LOOP;
注意:
 计数器是有于控制循环资数的变量，它不需显式地在变量定义部分进行定义。
系统默认时，计数器从下界往上界递增记数，如果在关键字IN后加上REVERSE则表示计数器从上界到下界递增记数。
计数器变量只能在循环体内部使用，不能在循环体外使用。
例：计算1+2+3+……+100的值
declare
S number(5):=0;
Begin
For I in 1..100
Loop
s:=s+I;
End loop;
Dbms_output.put_line(s);
End;
/
例4.10
从键盘接收一个整数，计算它的阶乘并在屏幕上打印输出。
declare
num number(3);
Fac number;
begin
Num:=&num
fac:=1;
if num>0 then
for i in 1..num loop
fac:=fac*i;
end loop;
end if;
end;
直到型循环
特点：先执行循环体，后判断条件。
语法格式：
Loop
<语句1>
<……>
Exit [when 条件]
<语句 n>
<……>
End loop;
例4.11用直到型循环控制语句求从1-100所有整数的和。
解1: declare
I number(3):=100;
suma number;
begin
suma:=0;
loop
suma:=suma+I;
I:=I-1;
exit when I=0;
end loop;
end;
解2：
declare
I number(3):=1;
suma number;
begin
suma:=0;
loop
suma:=suma+I;
I:=I+1;
exit when I>100;
end loop;
dbms_output.put_line(suma);
end;
当型循环
特点：先判断条件，后执行循环体。
语法格式:
While <条件> loop
<语句1>
<……>
End loop;
例4.12 采用当型循环控制语句求1-100所有整数的和
解：先申明一个全局变量:
variable suma number
再写PL/SQL块:
declare
I number(3):=100;
begin
:suma:=0; /*变量suma前带有“:”表示为全局变量*/
While I>0 loop
:suma:=:suma+I;
I:=I-1;
End loop;
End;
跳转语句
语法格式：
<<标号>>
……Goto 标号
有几项原则:
可以实现同一块中语句之间的跳转
可以从子块跳至父块,但不能从父块跳至子块
不能从IF语句体外跳入IF语句内。
不能从循环体外跳入循环体内。
不能从子程序外部跳入到子程序内
循环语句的嵌套
即一个循环语句中还可以包括其它的循环语句。
例:求100-200间的全部素数。
declare
fag boolean:=true;
begin
for i in 100..200 loop
for j in 2..i-1 loop
if mod(i,j)=0 then
fag:=false;
end if;
end loop;
if fag then
dbms_output.put_line(i);
end if;
fag:=true;
end loop;
end;
PL/SQL允许在一个块中包含子块，下段程序中列出了一个匿名的块，它包含另一个子块，该子块有自己的说明部分。例如：
declare
max_i constant int:=100;
i int:=1;
rec_number int ;
begin
for i in 1..max_i loop
if mod(i,5)=0 then
rec_number:=5;
elsif mod(i,7)=0 then
rec_number:=7;
else
rec_number:=i;
end if;
insert into text_table
(record_number,current_date)
values
(rec_number,sysdate);
declare
max_j constant int :=10;
j int :=1;
begin
for j in 1..max_j loop
rec_number:=rec_number*j;
insert into test_table
(rec_number,current_date)
values
(record_number,sysdate);
end loop;
end;
end loop;
end;