PLSQL基础知识

# PL/SQL基础
块是PL/SQL的基本程序单元，编写PL/SQL就是编写PL/SQL块。
## PL/SQL块结构
PL/SQL块由三部分组成：定义部分，执行部分，例外处理部分。其中定义部分用于定义常量、变量、游标、例外、复杂数据类型等；执行部分用于实现应用模块功能，该部分包含了要执行的PL/SQL语句和SQL语句；例外部分用于处理执行部分可能出现的运行错误。PL/SQL块的基本结构如下所示
```
declare
/*定义部分——定义常量、变量、游标、复杂数据类型等*/
begin
/*执行部分，用于执行PL/SQL语句和SQL语句*/
exception
/*例外处理部分——处理运行错误*/
end;/* 块结束标记 */
```
声明部分从declare开始，该部分可选；执行部分从begin开始，该部分是必须的；例外部分从exception开始，该部分是可选的，而end是块的结束标记，需要注意的是：
- begin、declare、exception后没有分号
- end后边一定要有分号

```
set serveroutput on
begin
dbms_output.put_line('hello , PL/SQL');
end ;
/
```
dbms_output包是Oracle中提供的系统包，可以直接使用。注意：当使用dbms_output输出数据或消息的时候，要将SQL*Plus的环境变量serveroutput设置为开启模式，也就是on。

具有例外的块
```
declare
v_ename varchar2(90);
begin
select ename into v_ename from emp where empno = &no ;
dbms_output.put_line('雇员名：'||v_ename) ;
exception
when no_data_found then
dbms_output.put_line('没有找到数据，请输入正确的雇员号');
end;
```
## PL/SQL块分类
- 匿名块
匿名块是指没有名字的PL/SQL块，可以内嵌到应用程序中，可以在在交互式环境中直接使用。
```
declare
v_avgsal number(6,2) ;
begin
select avg(sal) into v_avgsal from emp where deptno=&no;
dbms_output.put_line( '平均工资为：' || v_avgsal ) ;
end ;
```
因为该PL/SQL块直接以declare开始，没有给出任何名称，所以认为是匿名块

- 命名块
命名块是指具有特定名称标识的PL/SQL块，只不过命名块比匿名块多了<<>>，加以标记。在使用嵌套的时候，可以加上命名块加以区分。
```
<>
declare
v_deptno number(2);
v_dname varchar2(10) ;
begin
<>
begin
select deptno into v_deptno from emp where lower(ename) = lower('&ename') ;
end; -- inner 结束
select dname into v_dname from dept where deptno = v_deptno ;
dbms_output.put_line('部门名'|| v_dname ) ;
end ; -- outer 结束
```
- 当然还有子程序
```
子程序中包含：过程、函数、包
```
- 触发器

## 定义并使用变量
编写PL/SQL时 ，如果要临时存储数值，就要定义变量和常量；如果在

应用环境和子程序之间传递数据，必须要为子程序指定参数。在定义变量或常量的时候，需要指定对应的数据类型。在编写PL/SQL的时候，可以使用标量(Scalar)类型、复合(Composite)类型、参照(Reference)类型和LOB(Large Object)类型。
```
复合类型：record、table、varray
参照类型：cursor、object_type
LOB类型：BFILE、BLOB、CLOB、NCLOB
标量类型：注意有boolean、其他一些数据类型(不列出来了)
```
### 定义标量变量
语法：
```
identified ：[CONSTRANT] datatype [NOT NULL ][:= | DEFAULT expr]
identified ：用于指定变量的命称
CONSTRANT ：用于指定常量。当定义常量的时候，必须指定它的初始值，并且其数值不能更改
datatype ：用于指定变量或常量的数据类型
NOT NULL ： 用于强制初始化变量不能位null。当指定not null 选项时，必须要为变量提供数值。
:= ：用于为变量或常量指定初始值。
DEAFAULT：用于为变量或常量指定初始值。
expr ： 用于指定初始值的PL/SQL表达式，可以是文本、其他变量、函数等。
```
例如：
```
v_ename varchar2(90) ;
v_sal number(6 , 2 ) ;
v_kaka CONSTRANT number(6,3) := 5.5 ;
v_valid Boolean not null default false ;
```
如何使用呢？
```
declare
v_name varchar2(5) ;
begin
select ename into v_name from emp where empno = &empno ;
dbms_output.put_line( v_name );
exception
when no_data_found then
dbms_output.put_line( '没有找到数据' );
end;
```
如果要是执行上述代码，会有一个错误提示：
```
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
```
是因为我们在声明变量的时候，数据类型的长度过于窄小导致的。我们可以采用%TYPE的形式来声明变量（并修改上述例子）。
```
declare
v_name varchar2(15) ;
v_salary emp.sal%TYPE ;
begin
select ename , sal into v_name , v_salary from emp where empno = &empno ;
dbms_output.put_line( v_name || ' , ' || v_salary );
exception
when no_data_found then
dbms_output.put_line( '没有找到数据' );
end;
```
## 编写控制语句
控制语句主要有分支、循环、顺序控制三种。

### 条件分支语句

PL/SQL主要提供了三种条件分支语句：
```
if-then
if-then-else
if-then-elsif
```
主要语法如下
```
IF condition then
statements ;
[elsif condition then
statements ; ]
[else
statements ; ]
end if ;
```
当使用条件分支语句的时候，不仅可以使用if语句进行简单条件判断，而且还可以使用if语句进行二重分支和多重分支判断。注意，elsif 和 endif 可能与其他语言的的语

法有所不同，elsif是一个单词，end if 是两个单词。
- 简单条件判断
用于执行单一条件判断，如果满足特定条件，则会执行相应操作；如果不满足条件，则退出条件分支语句。简单条件判断是使用if-then语句来完成的。示例如下：
```
declare
v_sal number(6,2);
begin
select sal into v_sal from emp where lower(ename) = lower('&&name') ;
if v_sal < 2000 then
update emp set sal = v_sal + 200
where lower(ename) = lower('&name');
end if ;
end;
```
- 二重条件分支
二重条件分支是根据条件来选择两种可能性，当使用二重条件分支的时候，如果满足条件，则执行一组操作；如果不满足条件，则执行另外一组操作。二重条件分支是利用if...then...else 来完成的。示例如下
```
declare
v_comm number(6,2) ;
begin
select comm into v_comm from emp
where empno = &&no ;
if v_comm != 0 then
update emp set comm = v_comm + 100
where empno = &no ;
else
update emp set comm = 200
where empno= &no ;
end if;
end;
```
- 多重条件分支
用于执行最复杂的条件分支操作，当使用多重条件分支时，如果满足第一个条件，则执行第一种操作，如果不满足第一个条件，则检查是否满足第二个条件，如果满足第二个条件，则执行第二种操作；如果不满足第二个条件，则检查是否满足第三个条件，以此类推。多重条件分时是使用if...then..elsif语句来完成的。示例如下：
```
declare
v_job myemp.job%TYPE ;
v_salary myemp.sal%TYPE ;
begin
select job , sal into v_job , v_salary from myemp where empno = &&no;
if v_job = 'PRESIDENT' then
update myemp set sal = v_salary + 1000 where empno = &no ;
elsif v_job = 'MANAGER' then
update myemp set sal = v_salary + 500 where empno = &no ;
else
update myemp set sal = v_salary + 200 where empno = &no ;
end if ;
end ;
```
### CASE语句
当处理多重条件分支是，使用case语句更加简捷，而且执行效率也更好。使用CASE语句处理多重条件分支有两种方法，第一种方法是使用单一选择符进行等值比较；第二种是使用多种条件进行非等值比较。
- 在CASE语句种使用单一选择符进行等值比较

如果条件选择符完全相同，并且条件表达式为相等条件选择，那么可以使用单一条件选择符进行等值比较。语法如下
```
CASE selector
WHEN expression1 THEN sequence_of_statements1 ;
WHEN expression2 THEN sequence_of_statements2 ;
....
WHEN expressionN THEN sequence_of_statementsN ;
[ELSE sequence_of_statementsN+1;]
END CASE;

selector 用于指定条件选择符
expression用于指定条件值的表达式
sequence_of_statements用于指定要执行的条件操作
如果设置的所有条件都不满足，则会执行ELSE后的语句。
注意：
为了避免CASE_NOT_FOUND例外，在编写CASE语句时，应该带有ELSE子句
```
示例如下：
```
declare
v_job myemp.job%type;
begin
select job into v_job from myemp where empno = &empno ;
case v_job
when 'PRESIDENT' then
dbms_output.put_line( '大王' );
when 'MANAGER' then
dbms_output.put_line( '总钻风' );
else
dbms_output.put_line( '小钻风' );
end case ;
end ;
```
- 在CASE语句种使用多种条件比较
当使用单一条件选择符进行等值比较时，可以使用CASE selector语法来实现。如果包含有多种条件进行不等比较，那么必须在when子句中指定比较条件。语法如下：
```
case
when search_condition1 then sequence_of_statements1;
when search_condition2 then sequence_of_statements2;
...
when search_conditionN then sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;

search_condition 用于指定不同比较条件
sequence_of_statements 用于指定当满足特定条件时要执行的操作
如果设置的所有条件都不满足，则会执行ELSE后的语句。
注意：
为了避免CASE_NOT_FOUND例外，在编写CASE语句时，应该带有ELSE子句
```
示例如下：
```
declare
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE;
begin
select ename , sal into v_ename , v_sal
from emp where empno=&no;
case
when v_sal< 1000 then
update emp set comm=100 where ename=v_ename ;
when v_sal < 2000 then
update emp set comm = 80 where ename=v_ename ;
when v_sal < 6000 then
update emp set comm = 50 where ename=v_ename ;
end case ;
end ;
```
```
查询员工的薪水的级别 ( 每个级别的区间在 salgrade 表中有定义 )
declare
v_salary myemp.sal%TYPE ;
begin
select sal into v_salary from myemp where empno = &empno ;
case
when v_salary between 700 and 1200 then
dbms_output.put_line( 'first level' );
when v_salary between 1201 and 1400 then
dbms_output.put_line( 'second level' );
when v_salary between 1401 and 2000 then
dbms_output.put_line( 'third level' );
when v

_salary between 2001 and 3000 then
dbms_output.put_line( 'forth level' );
when v_salary between 3001 and 9999 then
dbms_output.put_line( 'fifth level' );
else
dbms_output.put_line( '零时工' );
end case ;
end;
```
## 循环语句
为了在编写的PL/SQL块中重复执行一条或者一组语句，可以使用循环控制结构。编写循环控制结构时，用户可以使用基本循环、while循环和for循环等三种类型的循环语句。
### 基本循环
在PL/SQL中最简单格式的循环语句时基本循环语句，这种循环语句以loop开始，以end loop结束，其语法如下：
```
loop
statement1 ;
...
exit [when condition];
end loop ;
```
当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当condition为true时，会退出循环，病执行END LOOP后的相应操作。注意：当编写基本循环时，一定要包含EXIT语句，否则PL/SQL块会陷入死循环；另当使用基本循环式，大家还应该定义循环控制变量，并且在循环体内修改循环控制变量的值。

示例如下：

```
create table temp(cola int); // 创建一个表

declare
i int := 1 ;
begin
loop
insert into temp values(i ) ;
exit when i = 10 ;
i:=i+1 ;
end loop;
end;
```
```
declare
v_count INT := 1 ;
begin
LOOP
dbms_output.put_line( v_count );
EXIT WHEN v_count = 10 ;
v_count := v_count + 1 ;
END LOOP ;
end ;
```
### while循环
对于while循环来说，只有条件为true时，才会执行循环体内的语句。while循环以while...loop开始，以end loop结束，其语法如下：
```
while condition loop
statement1;
statement2 ;
...
end loop;
```
当condition为true时，PL/SQL执行器会执行循环体内的语句；而当condition为false或null时，会退出循环，并执行end loop后的语句。注意：当使用while循环时，应该定义循环控制变量，并在循环体内改变循环控制变量的值。

示例如下：
```
declare
i int :=1 ;
begin
while i<=10 loop
insert into temp values(i);
i:= i + 1 ;
end loop;
end ;
```
```
declare
v_count int := 1 ;
begin
WHILE v_count <= 10 LOOP
dbms_output.put_line( v_count );
v_count := v_count + 1 ;
END LOOP ;
end ;
```
### for循环
当使用for循环时，oracle会

隐含定义循环控制变量。for循环的语法如下
```
for counter in [reverse] lower_bound...upper_bound loop
statement1;
statement2;
...
end loop;
```
counter是循环控制变量，并且该变量由Oracle隐含定义，不需要显示定义；
lower_bound 和 upper_bound 分别对应与循环控制变量的下界值和上界值。默认情况下，当使用for循环式，每次循环时循环控制变量会自动增一；如果指定reverse选项，那么每次循环式循环控制变量都会减一。

示例如下：
```
begin
for i in reverse 1..10 loop
insert into temp values( i );
end loop ;
end ;
```
```
begin
FOR counter IN 1..10 LOOP
dbms_output.put_line( counter );
END LOOP ;
end ;
```
```
begin
FOR counter IN REVERSE 1..10 LOOP
dbms_output.put_line( counter );
END LOOP ;
end ;
```
### 嵌套循环和标号
嵌套循环是指在一个循环语句之中嵌入另一个循环语句，而标号(Label)则用于标记嵌套块或嵌套循环。通过嵌套循环中使用标号，可以区分内层循环和外层循环，并且可以在内层循环中直接退出外层循环。

示例如下
```
declare
result int;
begin
<>
for i in 1..100 loop
<>
for j in 1..100 loop
result:=i*j ;
exit outer when result = 1000;
exit when result= 500 ;
end loop inner ;
dbms_output.put_line(result);
end loop outer ;
dbms_output.put_line( result ) ;
end;
```
练习：使用PL/SQL中的循环打印99乘法表。

## 顺序控制语句
PL/SQL不仅提供了条件分支语句和循环语句，而且还提供了顺序控制语句Goto和null。但是在一般情况下，不要使用。

### goto
goto语句用于跳转到特定标号处去执行语句。注意：因为使用Goto语句会增加程序的复杂性，并且使得应用程序可读性变差，所以开发应用程序时，一般都建议用户不要使用GOTO语句。语法如下
```
goto label_name
```
其中，label_name是已经定义的标号名。注意，当使用goto跳转到特定标号时 ， 标号后至少要包含一条可执行语句。

示例如下
```
declare
i int:=1 ;
begin
loop
insert into temp values(i) ;
if i=10 then
goto end_loop;
end if ;
i:=i+1;
end loop;
<>
dbms_output.put_line('循环结束');
end;
```
```
declare
v_count int := 1 ;
begin
LOOP
dbms_output.put_line( v_count );
IF v_co

unt = 10 THEN
GOTO loop_ended ;
END IF ;
v_count := v_count + 1 ;
END LOOP ;
<>
dbms_output.put_line( 'loop ended.' );
end;
```
### null
null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高PL/SQL程序的可读性。

示例如下
```
declare
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE;
begin
select ename , sal into v_ename , v_sal from emp where empno=&no;
if v_sal<3000 then
update emp set comm= sal*0.1 where ename=v_ename;
else
null;
end if ;
end;
```
# 例外
例外(Exception)是一种标识符，如果运行PL/SQL块是出现错误或警告，则会触发例外，当例外触发时，默认会终止PL/SQL块的执行，通过PL/SQL块中引入例外处理部分，可以捕捉到各种例外，并根据例外出现的情况进行相应的处理。

## 例外的分类
为了处理PL/SQL应用程序中的各种错误，Oracle中提供了预定义例外、非预定义例外、和自定义例外三种例外类型。其中预定义来外用于处理常见的Oracle错误；非预定义例外可以处理预定义例外不能处理的Oracle错误；自定义例外用于处理与Oralce错误无关的其他情况。
## 处理例外
如果不捕捉例外，Oracle会在出现错误时将错误传递到调用环境；如果捕捉到例外，Oracle会在PL/SQL块内解决运行错误。处理例外可以采用一下两种方法：
#### 传递例外
如果在运行PL/SQL应用程序中出现例外，并且在例外处理部分没有捕捉到该例外，Oracle会将该例外传递到调用块或PL/SQL运行环境。
示例如下：
```
declare
v_name emp.ename%TYPE;
begin
select ename into v_name from emp where empno=&no ;
dbms_output.put_line('雇员名' || v_name );
exception
when too_many_rows then
dbms_output.put_line('查询只能返回单行数据') ;
end;
```
### 处理非预定义例外
非预定义例外用于处理与预定义里无关的Oracle错误。使用预定义例外，只能处理21个错误，当使用PL/SQL开发应用程序时，可能会遇到其他的一些错误。所以我们可以使用非预定义例外。

使用非预定义例外包括三步：定义部分定义例外名，然后在例外和Oracle错误中建立关联，最终在例外处理部分捕捉并处理例外。
```
declare
e_integrity Exception;
pragma exception_init( e_integrity , -2291) ;
begin
update emp set deptno=&dno where empno=&eno ;
exception
when e_integrity then
dbms_output.put_line('该部门不存在');
end;
```
### 处

理自定义例外
自定义例外是指由PL/SQL开发人员所定义的例外。预定义例外和非预定义例外斗鱼Oracle错误有关，并且出现Oracle错误时会隐含触发相应的例外；而自定义例外与Oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外。

使用自定义例外的步骤如下：
```
在定义部分定义例外，然后在执行部分触发例外，最后在例外处理部分捕捉并处理例外。
```
```
declare
e_integrity exception;
pragma exception_init( e_integrity , -2291 );
e_no_employee exception;
begin
update emp set deptno=&no where empno=&eno;
if SQL%NOTFOUND then
raise e_no_employee ;
end if ;
exception
when e_integrity then
dbms_output.put_line('该部门不存在');
when e_no_employee then
dbms_output.put_line('该雇员不存在');
end;
```