笔者根据播布客小布视频整理而成
目录
游标的高级用法—游标带有参数（动态pl/sql）... 89
有错误编号ora-的处理方式（第二种）--隐式异常处理... 103
用户定义的异常处理（第三种） --显示异常处理（raise 名字）... 109
Raise_application_error 自定义错误... 114
1、当一个异常发生时，当前的pl/sql block就结束了，怎么样让他下面的程序继续执行呢 116
3、Debug，确定多条语句的transaction是那条语句发生的错误... 120
再看一个例子1，在子块有异常处理，父块（即调用子块的块）没有异常处理：... 145
在看一个例子2没有异常处理的情况，结果是整个事务都被回滚了，即使有成功的也回滚了： 145
再看例子3，在子块没有异常处理，但是在调用他的块（父块）有异常处理：那么只有发生错误的语句没有生效，其他执行成功的就提交了。... 147
PL/SQL
起名字就是过程和函数，不起名字就就是匿名函数
函数需要返回一个值，过程不需要返回一个值。
匿名块不是存储在oracle数据块中的，执行完了就没了，用于测试、调试等，如果正式的就写成过程或者函数。
函数和过程存储在数据块中。
SQL> declare
2 v_fname varchar2(20);
3 begin
4 select first_name into v_fname from employees where employee_id=100;
5 dbms_output.put_line('the result is '||v_fname);
6 end;
7 /
the result is Steven
PL/SQL procedure successfully completed.
SQL>
变量就是一个容器，里面能存很多的值
变量的用途：
可以临时存储数据，申明好后可以反复使用
变量命名的规则：
第一个名字必须是字母
他可以包含字母和数字
可以包含特殊符号，但是$,#最好不要用，可以用_
变量长度不超过30
不能和保留关键字一样
声明和使用变量
可以再declare声明
可以做为参数传递给其他函数，如传递给dbms_output.put_line
变量的输入输出
:=是赋值
constnat是常量的意思
Pl/sql大小写不敏感
Q是quote引用的意思
实验：
SQL> declare
2 v_event varchar2(15);
3 begin
4 v_event:=q'!father's day!';
5 dbms_output.put_line('3rd Sunday in june is: '|| v_event);
6 v_event:=q'[mother's day]';
7 dbms_output.put_line('2nd sunday in may is:' ||v_event);
8 v_event:='sister''s day';
9 dbms_output.put_line('9nd in sun is:'||v_event);
10 end;
11 /
3rd Sunday in june is: father's day
2nd sunday in may is:mother's day
9nd in sun is:sister's day
PL/SQL procedure successfully completed.
变量的声明和定义
变量的类型
1、标量：刻度，单一，比如date,varchar，用途较广
2、复合型
3、指针
4、LOB 比如cob存储文档，字符串，bob里面是二进制代码，存放存储图片等类型，bob存储在数据库中。Bfile是存储在磁盘中，只在数据库里面留一个指针，bfile存储速度更快。 NCLOB存放非字母的文字，如中韩的文字
5、绑定变量，这个不是pl/sql的变量，是sqlplus的环境变量，和pl/sql的变量不一样
命名方式：
上述两个变量是一样的，not null必须赋值
%type
声明一个变量和一个表的某一列或者另外一个声明变量的类型一样，将来那个表的
的那列类型变了，我也跟着变，这样就避免把程序写死
绑定变量
实验：
SQL> var b_salary number;
SQL> print b_salary;
B_SALARY
----------
SQL> begin
2 select salary into :b_salary from employees where employee_id=178;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print b_salary; 不用加冒号了
B_SALARY
----------
7000
SQL> select first_name,last_name from employees where salary=:b_salary; 加个冒号现在的:b_salary的值就是7000
FIRST_NAME LAST_NAME
-------------------- -------------------------
Oliver Tuvault
Sarath Sewall
Kimberely Grant
3 rows selected.
上述var是sqlplus的变量，sqlplus是pl/sql的外部环境
绑定变量必须在Pl/sql
实验:
var b_emp_salary number
set autotprint on
SQL> declare
2 v_empno number(6):=&empno;
3 begin
4 select salary into :b_emp_salary
5 from employees where employee_id=v_empno;
6 end;
7 /
Enter value for empno: 206
PL/SQL procedure successfully completed.
B_EMP_SALARY
------------
8300
书写可执行语句
注释
--是单行注释 /* */是多行注释
序列：
数据类型转换：分为隐式转换和显示转换
上图中，125的值是文本类型，通过隐式转换成了数字类型
执行测试
推荐大家用显示转换
嵌套
在生产中最好最多嵌套3层，嵌套多了说明你的程序设计的有问题
变量的范围
SQL> begin
2 declare
3 v_inner_variable varchar2(20):='LOCAL VARIABLE';
4 begin
5 dbms_output.put_line(v_inner_variable);
6 dbms_output.put_line(v_outer_variable);
7 end;
8 dbms_output.put_line(v_outer_variable);
9 dbms_output.put_line(v_inner_variable);
--内部嵌套的变量在外面不能用，外部变量可以在内部和外部变量都能用。
比如国家主席和内部省长，省长出了省就没人认识了，但国家主席在哪都有人认识
10 end;
11 /
dbms_output.put_line(v_outer_variable);
*
ERROR at line 6:
ORA-06550: line 6, column 22:
PLS-00201: identifier 'V_OUTER_VARIABLE' must be declared
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 22:
PLS-00201: identifier 'V_OUTER_VARIABLE' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
ORA-06550: line 9, column 22:
PLS-00201: identifier 'V_INNER_VARIABLE' must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored
上述v_date_of_birth 内部和外部重名了，那么在内部优先使用内部变量的值。
那么怎么在内部引用外部的变量v_date_of_birth呢，请继续往下面看：
可以加标签<>，中间的out可以随便写，在内部引用重名的变量时加out.来修饰引用
SQL> begin <>
2 declare
3 v_father_name varchar2(20):='Patrick';
4 v_date_of_birth date:='20-Apr-1972';
5 begin
6 <>
7 declare
8 v_child_name varchar2(20):='Milke';
9 v_date_of_birth date:='12-Dec-2002';
10 begin
11 dbms_output.put_line('Father''s name: '|| v_father_name);
12 dbms_output.put_line('Date of birth: '||outer.v_date_of_birth);
13 dbms_output.put_line('Chile''s name: '||v_child_name);
14 dbms_output.put_line('date of birth: '||inter.v_date_of_birth);
15 end;
16 end;
17 end outer;
18 /
Father's name: Patrick
Date of birth: 20-APR-72
Chile's name: Milke
date of birth: 12-DEC-02
PL/SQL procedure successfully completed.
SQL>
在给PL/SQL变量赋值是还可以按照下面的方法赋值：
编程序多用注释，保持程序的可读性
自己定义的变量用小写的形式，PL/SQL的关键字用大写
使用vi自动换行的小技巧
:set autoindent
然后再插入数据时，按tab键就会空8个字符，在输入下一行数据就不用再按tab自动空8个字符；
如果想空4个字符的设置方法：
:set tabstop=4
在PL/SQL 函数不能用group decode，nvl等，但可以用upper,to_char等
=========================================================================
PL/SQL block，如果退出没有提交，那么这个数据块还被锁着，所以再写pl/sql程序的时候要考虑怎么回退或提交。
Sqlplus做的就很好了，如果你没提交，退出sqlplus就回滚了
1 declare
2 v_fname employees.first_name%TYPE;
3 BEGIN
4 SELECT first_name into v_fname
5 FROM employees where employee_id>200;
6 DBMS_OUTPUT.PUT_LINE('first name is: '||v_fname);
7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
employee_id>200就报错了，因为一个变量v_fname存储不了多个值
Sum得到的是一个结果，然后存入变量v_sum_sal；
一个变量不能存入多个值。
================================================
在PL/SQL中，一个数据块可以有多个transaction,同时一个transaction也可以跨越多个数据块。
可以通过v$transaction动态性能视图来看有没有transaction在进行。
SQL> create table tt (id int);
Table created.
SQL> select xid,xidusn from v$transaction;
no rows selected
SQL> insert into tt values (0);
1 row created.
SQL> select xid,xidusn from v$transaction; 有transaction这个视图就有值
XID XIDUSN
---------------- ----------
06000F0040030000 6
SQL> commit;
Commit complete.
SQL> select xid,xidusn from v$transaction; 没有transaction这个视图就是空值
no rows selected
SQL>
实验
SQL> begin
2 insert into tt values (1);
3 end;
4 /
上面没有commit，这个事务并没有消失。
PL/SQL procedure successfully completed.
SQL> select * from tt;
ID
----------
0
1
1
QL> select xid,xidusn from v$transaction;
XID XIDUSN
---------------- ----------
010017000F030000 1
SQL> commit;
Commit complete.
SQL> select xid,xidusn from v$transaction;
no rows selected
上述merge的意思是：将e表数据拷贝到c表中，如果两个表的数据相同，就把e表数据更新到c表中，e表的数据在c表中没有，就把
E表的数据更新到c表中。
如果我们在用insert和update都达不到我们的要求时，就可以考虑用merge。
Merge是insert 和update的结合体
游标：
在处理一个sql语句时，oracle会分配一个私有内存区，在用户的session中，如果是专有服务器，这个session在pga中，如果是共享服务器，就放在sga中。游标创建指针，指向内存区
游标有显示游标和隐式游标。
如果我想知道update、delete有多少行受影响，就用下面三种游标的属性来处理。
下面介绍三种游标的属性
Sql%found是布尔值，如果update或者delete有记录，Sql%found就是true，反之是false；
Sql%notfound，如果没有修改一条记录Sql%notfound就是true
Sql%rowcount表示多少条记录被修改
Create table emp as selct * from employees；
上面看我删除了多少条记录
游标要在commit之前写，如上图中的dbms_output.put_line(sql%rowcount||'rows is deleted');
在commit之后就不显示了。
上图中的returning into 和select into是一样的。
控制结构
顺序结构
分支判断
循环结构
分支判断
在数据库中，布尔值有三种情况：true,false,null。这是数据库特殊的地方
注意：上面的是elsif，不是elseif
上面elsif较多，用case解决会更好
下面介绍null
If v_myage is null 结果成立，打印出的结果就是 I am a child。
CASE
下面介绍case，case分为两种，一种是simple case,一种是serched case。
Case还分为case表达式和case语句
简单Case
Case 表达式
简单case
Case 表达式
下面的case是searched case，和simple case的区别就是case后面不跟东西，如下图。
Simple case和searched case表达的含义是一样的。
Searched case
Case 表达式
上面介绍了两种case:simplecase 和searched case
上面是case 表达式
下面是case语句，不能再赋值了
NULL
Null值具有黑洞（只要有黑洞，什么都吸进来）的特色，只要有Null参与运算的表达式，结果是null。
===========================================
从上图中，在and条件中，null and false 结果是false，这个结果出乎我们的预料。
And表示只要有一个false结果就是false。
Null值虽然具有黑洞特点，但也并不是百分之百是这样的
短路
只要确信前面条件能得出结果，那么后面的就不用看了。如下图，and前面条件是false，那么条件2就不用看了
当你希望PL/SQL啥也不做时，直接写个null;就行，如下图
循环
反反复复干一件事情，循环分以下三种：
Basic loop
For
While
如果上图没有exit，就是死循环；
另外，exit也可以加标签
执行结果如下：
While 循环和基本loop循环在循环之前可能不知道循环多少次，FOR循环知道循环多少次
For循环分为简单for循环和游标循环
上面for counter是隐式定义，不需要定义。
上面for 1..10 加上Reverse是就成了10..1
实验：
从上面看出，i出了loop就不能用了。
Reverse
下届一定比上届小，不要自作聪明，写反了，写反了，就不执行了
嵌套
For循环还可以嵌套，一般嵌套两到三层就行了，嵌套多了说明你程序设计的不好
上图中嵌套用了标签来引用
continue
Coninue 是11G才有的，10G之前都没有这个功能，10G之前想要这个功能得自己通过一些方法构造。
Contine的意思和break相反，break是跳出这次循环，continue的意思是结束这次循环，cuntinue后面的东西不执行了，进入下一次循环。
Continue 分为三种：
1、Continue;
2、Continue when;
3、Continue 标签 when ---这个有点像goto语句
Exit也分为三种：
1、exit;
2、exit when
3、exit 标签 when
当Continue i>5下面的dbms和v_total两天语句就不执行了，而是回去再从for 循环开始执行。
i<5才执行下面的两条语句。
For 循环中的变量只在自己的for循环里面生效，外部就不生效了
declare
v_total number:=0;
begin
<>
for i in 1..10
loop
v_total:=v_total+1;
dbms_output.put_line('Total is '||v_total);
for j in 1..10
loop
continue befortoploop when i+j>5;
v_total:=v_total+1;
end loop;
end loop;
end;
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> /
Total is 1
Total is 6
Total is 10
Total is 13
Total is 15
Total is 16
Total is 17
Total is 18
Total is 19
Total is 20
PL/SQL procedure successfully completed.
declare
v_total number:=0;
begin
<>
for i in 1..10
loop
v_total:=v_total+1;
dbms_output.put_line('Total is '||v_total);
for j in 1..10
loop
continue when i+j>5;
v_total:=v_total+1;
end loop;
end loop;
end;
Total is 1
Total is 6
Total is 10
Total is 13
Total is 15
Total is 16
Total is 17
Total is 18
Total is 19
Total is 20
PL/SQL procedure successfully completed.
上图中的continue是无条件退出循环。所以for j循环就循环了一次。
begin
<>
for i in 1..5
loop
dbms_output.put_line('outer index= '||i);
<>
for j in 1..5
loop
dbms_output.put_line('---->inner index= '||j);
continue outer;
end loop;
end loop;
end;
outer index= 1
---->inner index= 1
outer index= 2
---->inner index= 1
outer index= 3
---->inner index= 1
outer index= 4
---->inner index= 1
outer index= 5
---->inner index= 1
PL/SQL procedure successfully completed.
Goto语句
只能在一个PL/SQL过程或块内进行无条件的跳转。不能在两个过程之间跳转。
复合数据类型
以前讲的都是日期型，字符串、布尔型等，都是简单的数据类型。下面讲复合数据类型。
复合数据分为以下两类:
1、集合 用、分为关联数组和嵌套表、索引表（和iot表不一样），可变长数组几个
注：oracle可以把嵌套表和可变长数组以表的形式存放在数据库的表中。
2、记录 （%rowtype）
record记录：表示的是一个包，一个整体的逻辑单元。这个包里面放的东西不一样，是不同的数据类型，并且在这个包裹里面只出现一次。
上图中的pl/sql record 就有四种的数据类型，成为四种域；
collection集合：也是一个包裹，里面的数据类型一样。集合类似数组，里面的每个部件称为元素。
记录里面可以嵌套记录，集合里面也可以嵌套集合。
一行的全部或一部分就可以称为record记录。
创建记录的方法
1、自定义；2、%rowtype;3、显示游标
第一只定义记录的方法： 自定义
上面1是定义记录，2是调用记录
上面声明变量type 部分是不占内存的,调用变量部分是占用的内存的。另外，上面的%rowtype是另外一种定义复合数据类型的方法。
2、第二种定义record记录的方法
%rowtype是第二种定义记录的方法。我定义一个record，这个record里面的域我不操心，只是和某个表的某一行一样。比自定义简单，不用自己一个一个写域了。
上述我不操心person的域，employees列变了，person就变成无效状态了，就需要重新编译，保持更新。
%rowtype可以避免把程序写死。
%type和%rowtype
Oracle称这两个为锚技术。就是锁定技术，即A锁定B。
显示游标是虚拟表，是一个临时表。
Retired_emp是指退休表
上述insert into 看到很麻烦。可以改进，看下面的
上面insert into 改进：
UPDATE
可以把null赋给record。
但不能在record中用is null；
不能比较两个record，如想比较，需要将两个record的域值必须一条一条比较，用if 域值=域值 and 域值=域值 and……….
下面这个例子很好：
上图中的custom.name 的custom是另外一张表，需要自己建立这个表。
=======================================================
集合
集合包括三个：关联数组、嵌入式表和可变数组
我们只介绍关联数组，其他两个在高级课程中介绍
关联数组在早期叫索引表（和iot索引组织表不一样，iot表是存储在数据库中的一种类型），PL/SQL中的关联数组只能存储在PL/SQL中，不能存储在数据库中。
关联数组囊括了普通数据和hash表
用的最多的是一维数组，二维和三维用的少
关联数组只能存在PL/SQL中，不能存在数据库中。
关联数组包括两列Key(整形值或字符串)和values，见上面的图片
如果key是整形，那么就相当于数组，如果KEY是字符串那么关联数组就相当于perl里面的hash表。
Value可以是字符串或者record。
Key就是数组的下标，可以是整形或字符串。
如果下标是字符串，那么排列顺序是有NLS_sort和NLS_COMP环境变量决定。
如果下标是整形，输出结果是从大到小排列的，但是创建顺序可以随意。
刚创建的关联数组是空的，直到你填数据为止。
使用数组的方法就是直接使用下标引用或者一个一个遍历来使用关联数组。
关联数组是存在内存中的，不存在磁盘中，只是一个缓存。
关联数组不能使用DML语句，只能使用自己的方法。
定义关联数组
Index by定义的是集合中key值，即下标的类型。
Index by基于整形索引还是基于字符串的索引
Table of后面定义的是集合的values的类型
下标随便写，只要在规定的范围内就可以，如上图中的1和8
PLS_INERGE是整形的。
Happyfamily下标不是按照数字顺序写的，只要数组在规定的范围内就可以。
L_row：=happyfamily.first可以获得第一个下标
遍历出来
First拿到第一个下标，next下一个下标
使用下标方法
declare
type dept_table_type is table of
departments%rowtype
index by pls_integer;
dept_table dept_table_type;
begin
select * into dept_table(1)
from departments
where department_id=10;
dbms_output.put_line(dept_table(1).department_id||' ' ||dept_table(1).department_name||' ' ||dept_table(1).manager_id);
end;
/
~
再重复一遍:
Values可以是字符串或者record。 Is table of后面定义的就是values的类型
Key就是数组的下标，可以是整形或字符串。
上面的For只执行一次，然后into到内存表中，下面的for解决了客户端一个屏幕显示不下，就会分页显示，那么每页显示的内容就是从上面for循环的内存中表取的数据。
Hash表是查找数据最快的，使用字符串做索引，不使用整形
关联数组下标可以是数值，也可以是字符串。
关联数组常用来做cash，缓存到内存中，以后用户访问直接在内存中就行了，不用和数据库打交道了。
如果下标是字符串，就是hash表。直接就跳转到要找的值了。
下面是一个下标是字符串的实验
关联数组输出时是排序的，不是以你创建时的顺序决定的。
再看一个例子：
通过上述两个例子，看出index by是字符串值，不是整形值。
my_emp_table(i) 这个是一个record,后面带上要输出的字段my_emp_table(i).XXX
Subtype是子串
上面遍历4次，很慢
下面把上面的例子改进算法
上述以字符串做下标就是hash表，速度就快
他的查找不会受表数据量大小的影响，即使影响也很小。
Hash 是计算机伟大的发明
HASH
给x一个值，就会得到y；但是反过来不行。
上面两个命令做签名用的，hash得出一个数值。
Hash的查找速度是所有查找最快的。
字符下标长度在100以内，差别不大
嵌入式表
表中有表就是嵌入式表。嵌入式表下标必须是整形。这东西很复杂，初学者不用研究了。嵌入式表可以用父表和子表来替代。嵌入式表如下图。
可变数组
一般嵌套的表比较小就是可变数组，反之是嵌套表。
这个也不用研究，较复杂。
使用显示游标
对于for循环来说，可以通过数字和cursor作为for循环的上下界。
游标：每次发出语句，oracle会有内存区，存放这些语句，有指针指向这语句，这就是游标。
显示游标：用户自己去声明的游标。
Select into只能查询一条记录，想查询多条记录就得用显示游标了。
游标的步骤：
1、 声明游标，就是起名字；
2、 打开游标，把查询结果放到内存中；
3、 抓取，把当前行的数据导入到一个变量中，一条一条取记录，指针一条一条往下移。
4、 判断，行是否为空，非空就继续抓取，否则关闭游标；
5、 关闭
关闭游标就是把内存和锁等都释放了
declare
cursor c_emp_cursor is
select employee_id,last_name from employees
where department_id=30;
v_empno employees.employee_id%type;
v_name employees.last_name%type;
begin
open c_emp_cursor;
loop
fetch c_emp_cursor into v_empno,v_name;
exit when c_emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_name);
end loop;
close c_emp_cursor;
end;
游标就是一个虚拟表
游标经常和record和for一起使用
For循环式隐式的定义、打开、fetch、关闭一个游标
Loop end loop 循环控制游标控制力更高，想什么时候结束就什么时候结束（exit when）；
For循环是自动控制游标的，控制力没有loop循环控制高。
%Rowcount是游标的一个属性，前面加上游标的名字。
上述for i的i变量是隐式声明的，这个i出了for 就不能够用了
再看下面的例子，游标都不用定义了，直接用for来引用，更加方便。
下面是显示游标的属性，以前曾经学过隐式游标的属性
隐式游标：sql%found，而现实游标是：游标的名字%found
%isopen判断游标是否打开
%rowcount在显示游标打开时是一行一行记录行数的，不是打开有多少行就有多少行的。假设打开100条记录，但是rowcount=0，当fetch第一条数据时，%rowcount=1，fetch第二条时%rowcount=2…..
游标的高级用法—游标带有参数（动态pl/sql）
可以传送不同的值，这就是动态的pl/sql
For update
Oracle 有表级所和行级锁
Select 查询时是不会加锁的，其他的操作如update ,insert等会加锁
Select …..from For update 表示对查询的那些行加锁
For update of column表示只锁定update of后面的列，不锁全表
Nowait 就不会挂在哪，而是直接报错说是这些行被锁定；如果不见nowait那些行锁定就一种挂起在那不动。
Current of 表示把游标指针fetch的当前行进行修改，只修改当前行。
Select …from For update和current of经常连用，也就是我for update锁住一些行后，来修改current of后面的当前内容。
Bulk collect
以前是一条一条的取数据放到关联数组里面，如下图，这样慢
改进方法：用bulk就是批量的把数据放到关联数组里面，不是一次一次的了，这样就快。
处理异常
异常就是意外意想不到的事情。
Select into 查询的是一条数据，想查询多条数据要用游标into到一个变量。
上图中错误的原因是如果有多个人叫john，那么就会出现多条记录into到一个变量中，从而出错。
PL/SQL块，匿名函数的结构就是declare、 begin end 、exception
为了避免上述出错，加上exception,如下图，这个错误是隐式触发的，也就是oracle自己触发的
上图truncat 表后，错误就跳转到了when others then部分。
你可能从c语言、java、sqlplus、perl去调用pl/sql的。如果你在pl/sql中定义了exception，那么这些调用pl/sql的程序显示是正常的，因为错误被你内部消化了，在调用你程序看来是正常的。
异常可以被数据库软件触发（隐式的触发）和我的程序判断（显示触发）。
如果异常抛出来了就要被处理，可以通过异常处理句柄捕获。
如果异常跑出来了你不捕获，就会扩散，扩散调用你pl/sql的程序。
如果被调用的pl/sq;程序有错误，没有被处理，那么调用他的程序（如c语言，java等）也会出错。
发生错误的程序pl/sql不管内部捕获处理还是不处理，调用他的程序（如c语言，java等）都会被终止，只不过调用的程序（如c语言，java等）是正常的终止（pl/sql的错误内部处理了）或者不正常的终止（pl/sql的错误内部没有被处理）。
只要有错误，你的程序就跳转到exception，那么你的程序就不会再回到执行体了，如下图所示：
看到上述goto不回去了
====================================================
异常的触发是由oracle软件触发的，叫做隐式的触发；
用户定义的错误就是显示触发
Oracle异常处理的三种方式
上图用户定义的错误，如银行账户是0，但是你还要取钱，这对于业务逻辑来讲是错误的，但是对于oracle本身来讲是正确的。
看PL/SQL的联机文档
上述大概有20多个错误
那么怎么看更多的oracle错误呢，见下面
看到更多oracle错误编号
有错误名字处理方式（第一种）--隐式异常处理
When other一定要放在最后，不能放在最前面。
有错误编号ora-的处理方式（第二种）--隐式异常处理
那么对于oracle没有名字只有编号的怎么处理呢，见下面：
看个例子就明白了
上图中：
1、 表明e_insert_excep这是异常处理句柄的名字
2、 把e_insert_excep和-01400关联起来了。Prgma exception 通知编译器把01400和e_insert_excep名字关联起来
上述Begin 部分故意插入一条空值，故障造成错误用来测试
3、 When部分用来当发生错误时的引用
SQLERRM就是字符串，表示最近发生异常的信息
上述的-01400前面的0可以省略
Pragma exception_init 是通知编译器去执行的，如果关联很多，就一条一条的写出对应关系。
Sqlcode和sqlerrm是并行兄弟，是一起出现的，前者是整形值后者是字符串（10g最大是256个字符,11g是512个字符）。一旦oracle发生错误，会自动填充sqlcode和sqlerrm的内容
上述的errors表我们称为日志表
例子：
SQL> create table errors (e_user varchar2(20),e_date date,error_code number(30),error_message varchar2(255));
Table created.
declare
error_code number;
error_message varchar2(255);
e_insert_excep exception;
pragma exception_init(e_insert_excep,-01400);
begin
insert into departments
(department_id,department_name) values (280,null);
exception
when e_insert_excep then
rollback;
error_code:=sqlcode;
error_message:=sqlerrm;
insert into errors (e_user,e_date,error_code,error_message)
values (user,sysdate,error_code,error_message);
end;
/
~
PL/SQL procedure successfully completed.
SQL> select * from errors;
E_USER E_DATE ERROR_CODE
-------------------- --------- ----------
ERROR_MESSAGE
--------------------------------------------------------------------------------
HR 14-SEP-14 -1400
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
自治（不受别人控制）事务
用户定义的异常处理（第三种） --显示异常处理（raise 名字）
Raise 就是触发错误处理的意思
Raise是触发的意思
上图中：
1、 定义了一个异常的名字
2、 显示抛出异常，这是程序员自己定义的，raise是触发的意思。
Exception的扩散传播
如果pl/sql没有exception，就会传播到其他地方。
如果pl/sql执行引擎不能在当前块中找到exception句柄，就会将错误抛到别的地方去，怎么都怎么抛呢？
其实这个错误就会去上层pl/sql block去找有没有处理的方式，如果外部有错误数组处理，就在上层处理；如果上层找不到，就去上层的上层去找，如果上层的上层都找不到，pl/sql就会把错误抛给数组环境host environment（例如sqlplus，在sqlplus就会报错）。
下面看几个扩散情况：
第一种：内部程序自己处理
第二种：扩散到外层块
第三种：都找不到异常处理
上述c都没找到，就把执行权交给数组环境host environment，如果sqlplus，在sqlplus里面就报错了。
Raise_application_error 自定义错误
这个是pl/sql的一个过程procedure
上图中Error_number错误编号，范围是-20000..-20999，也就是自定义自己的sqlcode，有1000个编号可以供我们用。
上图message相当于sqlerrm，自定义的错误信息，大小事2kb
如果是true，就把错误信息写到堆栈里面，如果是false，就把堆栈清空。
默认是false
编程的技巧
1、当一个异常发生时，当前的pl/sql block就结束了，怎么样让他下面的程序继续执行呢
当一个异常发生时，当前的pl/sql block就结束了。
但是我们想当前异常处理完后，还想继续执行当前的pl/sql block的下一条语句，怎么办呢，用goto都不行。不过可以用savepoint返回
有办法的，可以把出错的语句放在独立的sub-block子块就能实现。
这么说比较抽象，下面看一个例子：
因为上面的commission_pct=0，所以salary/commission_pct是错误的，这样从select (salary/commission_pct)……语句直接就跳转到了exception数据异常处理部分，不会再执行下面的insert into emp_tmp values (302,sal_calc/100,.1)这条语句了。
但是我们想异常处理完了后，还继续再执行下面的insert into emp_tmp values (302,sal_calc/100,.1)这样语句，那么就要做如下处理：用begin end括起来，这样就形成了子块。
所以说你想让select (salary/commission)出错后继续能执行他下面的语句，那么你就给这个sselect (salary/commission)加个begin end，即变成子块。
2、transaction失败了在重试执行
如果transaction失败了（如插入的值重复了，就不让插入了），那我想失败了再重试一下这条语句该怎么办捏。
用这种方法解决：1、把transaction变成子块（加begin end），这样transation如果报错，还可以接着执行出错transaction当的下一条语句；2、loop循环；3、定义一个savepoint，如果出现异常，就rollback到savepoint的点。然后再重试。
结合下面的例子来理解：
Suffix是后缀的意思，是自己随便定义的变量，比如上面+1。
3、Debug，确定多条语句的transaction是那条语句发生的错误
比如我同时发出了update、delete，insert、merger，最后commit或rollback。
但是有语句出错了，我不知道是哪个语句发生的异常。这个可以使用位置变量来解决。
比如s=1执行update，s=2执行delete…..一旦出错就可以在exception看s=几，那么就知道是哪个语句导致的错误。
下面看个例子来理解：
下图中Stmt_no是位置变量
可以看到执行第一条select 语句就出现了问题。
========================
过程
概述
参数分为形式参数和实际参数；
参数传递方式包括基于过程的、基于名字的和混合式的三种方式。
工作中用的最多的是过程和函数，匿名块用的很少。
模块化编程：把一串代码起个名字，在调用代码的地方直接call这个名字就行了。
编程序就像搭建积木一样，然后构成一个复杂的程序。
C语言只有函数，没有过程。
分层次编程：PL/SQL 和SQL是联合在一起。
可以把程序分为数据访问层（直接和数据块打交道，SQL语句就是）和商业逻辑层（除了直接和数据库打交道的以外的,pl/sql就是）
匿名块：c语言、java语言等调用匿名块。结构如下：
执行引擎：
Sql用sql引擎执行，同样pl/sql用pl/sql引擎执行。
Sql和pl/sql引擎是分离的.
Sql引擎是在服务端，pl/sql引擎可以在客户端和服务端
过程和函数的区别
函数是我从你那得到一个结果:比如我问你今天天气怎么样，我只是想从你那获得一个结果；
函数必须返回一个结果。
过程是执行一个动作，比如我用手打你，你疼。
当然过程也可以返回一个值，也可以不返回值。
匿名块没有被编译，所以不能存储在数据库中；过程和函数是经编译的可以保存到数据库中。
匿名块是被调用者；函数和过程是调用者，用来调用匿名块。
匿名块是不能传递参数的，函数和过程是可以传递参数的。
过程：他主要是执行一个过程，是pl/sql的子函数。
用sqlplus，vi,记事本写过程，然后数据库进行编译存储在数据库中，然后通过开发工具（如sqlplus，pl/sql developer等）看错误，如show errors看错误，或者all_errors，dba_errors，user_errors看错误。
看到增加了一张表和一个过程。
匿名块并不存储在数据库中，过程函数是存储在数据库中的。
调用过程
把过程包装在匿名块中进行调用
End label
建议加这个label，这样目的是为了可以增加过程的可读性。当让这个label也可以不加。
变量的命名规则（建议）
变量用v_开头….，常量用c_开头……见下图
过程—传参
参数传递模式：
In 缺省的就是In，往过程里面提供变量，这个变量是只读的，供过程引用（这个变量只供别人引用），但是不能改写这个变量的值--------只读的。In传的值可以是变量，可以是常值，可以是表达式。In一开始传值是没有初始值的。
Out 这个变量一开始没有啥值，但是在函数可以改，调用时这个值就变了，提取一个值当做返回值--------只写的。Out传值时必须是变量，因为传出来的值必须放在一个变量里面。out一开始传值是没有初始值的。必须是变量
In out 可以往里传值，传完值还可以作为返回值-----可读可写的。out一开始传值是有有初始值的。必须是变量，因为出来要带值，所以必须是变量
IN的例子
默认就是 in类型的
Out 的例子
下图中的P_salary 参数像一个容器，不往里面传东西，只是用来保存结果的，如下图的select into 到两个变量，就是保存结果的。
IN OUT
查看 out变量
Host varable ：host翻译成数组，理解成包容的东西。
猪就是host，细菌就是寄生虫。Sqlplus、java、c语言就是host。
参数传递的方法
PL/SQL编译器有两个方法把实际的值传入子程序：
1、传引用，相当于传c++的指针
上图中的方块是一个内存区，假设里面有个数值2，虚线以上是过程和函数外部，虚线以下是内部，外部是实际参数，里面是形式参数，所以如果你内部改值，外部也就改了，因为内外是指针指向一个内存区（图中的方块），指向的是一个东西。
2、传值：编译器把实际参数的值拷贝到对应形式参数里面去。形式参数和实际参数的值指向不同的内存块。这样你改变一个内存块，不会影响另外一个内存块。
传值和传引用的区别
传引用快，因为他不需要再拷贝一份，但是不安全，如果你一个变量改了，别人就能看到；
传值慢，因为需要拷贝一个就慢，优点是在内存中有两个同样的拷贝。改变一个不会影响另外一个。在外边函数调用实际参数并未改变。
In是传引用
Out 和inout是传值的 为什么，不清楚
变量赋值
1、 按次序传；
2、 使用赋值变量=>，左边是形参，右边是实参。
3、 混合式，即上面的两种方法一起用，但是最好不要用这个。
形式参数：定义在过程头的参数
实际参数:真正传递进去的值
看下面的例子就理解了。
=====================================
过程是一个黑匣子，往里面传东西就用in，往外传东西就是Out。
调用
两种方法:
DEFAULT
缺省值
调用过程
可以用匿名块调用过程，或者用包调用
向前引用
代用一个函数，先定义在调用。
如果有互相调用的块，递归调用，告诉编译器这是个啥。
向前引用，先声明（declare）他是过程还是函数，然后再定义（define），见下面的例子，你就懂了。
上面的是过程proc1调用proc2，同时，过程proc2调用proc1时，这时候就使用了向前调用。
过程里面怎么处理异常
事务和异常的关系，transaction是独立的。只要在begin之间，上面所有的dml是一个transaction。用commit或者rollback结尾。
上述99是在departments不存在的，所以，上述实际才插入两条记录。
SQL> exec create_departments
Added Dept: Media
Err:adding dept: Editin
Added Dept: Advertising
PL/SQL procedure successfully completed.
错误被内部处理了，在外部看来，这个过程是正常执行的，没有报错。
可以看出employee_id在100-206之间，没有99
说明当前数据库里面没有事务。
=============================================================
如果没有异常处理（***）
上面表示，如果第二条出错了，如果他找不到处理异常的地方exception，那么他下一条就不执行了，同时在sqlplus 数组环境中报错，最终导致上面的插入成功的第一条Media也回滚了，最后结果导致所有记录都没插入。
反之，如果有exception，那么只有发生异常的语句进行回滚，其他不回滚，其他执行成功的就提交了。
再看一个例子1，在子块有异常处理，父块（即调用子块的块）没有异常处理：
上面的例子说明，第一条语句执行成功了，但是第二条语句失败了，因为第二条语句有异常处理的exception,所以只有第二条语句执行失败了，第一条语句插入成功了。
在看一个例子2没有异常处理的情况，结果是整个事务都被回滚了，即使有成功的也回滚了：
再看例子3，在子块没有异常处理，但是在调用他的块（父块）有异常处理：那么只有发生错误的语句没有生效，其他执行成功的就提交了。
Help set
Set autocommit on; 这个是自动commit；
总结：如果这个匿名块调用procedure后结果是发生错误了，那么所有的都被回滚了，包括执行成功的也被回滚了；
反之，如果这个匿名块调用的procedure没有发生错误，表面上看没发生错误，其实这里面可能有发生错误的语句，只不过是被exception捕获，使得这个错误被内部消化了，那么此时匿名块表现的结果是里面执行成功的被正常提交了，执行失败的回滚了。
怎么看procedure的源代码
######################################################
函数
函数和过程是姊妹花，大同小异。函数也有名字，其他来调用 这个函数
函数必须返回一个值，用Out来返回一个值，这是和过程的区别。
函数可以把一个值放在一个表达式里面，也可以放在其他参数里面传递。
上图中的PL/SQL块中，每执行一个路径都会有一个返回值，并不是pl/sql块只有一个返回值。
下面看一个例子：
上面有三条执行路径，所以要加三个return。
执行函数
上面函数check_sal写在了两个if语句里面，这样函数会执行两次，效率会很低。下面有优化
下面把上面执行函数优化一下，放入一个中间变量中，这样函数执行一次就行了，然后把这个结果反复调用就行了。从而效率会更高。这是优化的一种手段。
函数和过程的区别
过程是一个动作。函数是执行结果。
函数必须包含return，过程可以包含或者也可以不包含return。
过程显示一个值用dbms_output，函数用return返回一个值来看。
函数用show errors或者all/dba/user_errors_views来看错误。
用不同的方法来调用一个函数
上面的variable是sqlplus的变量，不是sql或者pl/sql的变量
上图打星号地方，表示我们可以把函数放入一个SQL语句里面去执行，这样大大增强了函数的使用范围。
上述介绍了几种调用函数的方法
因为sql处理能力是有限的，比如where子句过滤的能力是有限的。所以你可以创建一个函数，完成一个复杂的任务，然后用sql调用就行了。
下面演示了如何sql语句中调用pl/sql函数
在SQL语句中，函数可以用在select where having update set ,order by等等中使用，看下图
但是函数在sql语句里面使用还是有很多限制的，并不是随心所欲的，看下面的介绍。
在SQL语句中使用用户自定义的函数的限制
1、 所有参数必须是In类型；
2、 在函数不能返回record
3、 返回的是sql语句的类型，不能是pl/sql类型
Sql调用pl/sql这个知识比较复杂。
4、一个select 调用函数不能包含dml语句；
5、一个update or delete使用函数时，这个函数也不能包含查询同一张表或者对同一张表的修改操作；
6、sql调用一个pl/sql函数，那个pl/sql函数里面不能包含commit或者rollback，因为会影响外面的rollback或者commit；
所以，一个sql语句调用一个函数应该是只读的，你不能有修改语句；
上述语句犯的错误是update引用函数时，这个函数不能对同一张表进行修改。
改进
上面可以看出在update的时候查询会出错
上面查询另外一张不相关的表，就不报错了
还有一些限制：
一致性原则，比如我发出一条select语句，这条语句调用了函数，并且这条语句执行很长时间才返回结果，但是在执行过程中可能有别的人对数据进行了修改，这个时候有可能会被函数捕获从报错。
下面是11G的新功能，调用的时候可以不写第一个参数，不写就用defaulte
上述10g就不支持，11G才支持
附件列表
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28916011/viewspace-1409344/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28916011/viewspace-1409344/
4027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
