PLSQL 学习笔记

PL/SQL学习笔记

笔记内容说明

PL/SQL；

目录

1、PL/SQL简介

1.1什么是PL/SQL

PL/SQL（Procedural Language/SQL）是Oracle在标准SQL的基础上增加了过程化处理，把DML和select语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作，实现复杂的功能或者计算的程序语言。

扩展：变量和类型、控制结构、过程与函数。

• 注意事项：

• java中是写方法，把复杂的业务逻辑写入方法中，再调用方法。

• PL/SQL是写过程、函数，把复杂的业务逻辑写入过程、函数中，再调用它们。

1.2 PL/SQL程序结构

PL/SQL块：包含三部分，声明部分：declare；执行部分：begin；异常处理：exception

eg1：语法

declare

v_AccountID number(5):=1001;

v_RealName varchar(20);

begin

select real_name info v_RealName from account where id=v_AccountID;

exception

when on_data_found then

insert info Fee_Log(desrc) values('Account 1001 dece not exit!')

commin;

end;--（不需要理解代码的具体含义！）

• 注意事项：最简写的方式为begin end;

eg2：打印Hello World

begin

dbms_output.put_line('Hello World');

end;

• 注意事项：

• dbms_output是系统提供的包package，包含多个过程、函数。其中的过程put_line实现的是输出功能，只有一个参数，只能为“字符类型”（日期和数值也可，系统自动转换！布尔类型不行！但java中可以），用于接收需输出的字符串。在sql工作表子窗口中可以调用存储过程。

• 想要在屏幕上输出需要写：set serveroutput on（在begin上面写）。

• 如何调用过程：begin 包名.过程名();所有过程都是没有返回值的，即java中的void。

1.3 PL/SQL运行过程

如下图所示：

1.4注释

增加可阅读性，使程序更容易理解。编译时将被忽略。

1）单行注释：由两个连字符“--”开始，到行尾都是注释。

2）多行注释：由“/*”开头，由“*/”结尾。

2、变量与数据类型

2.1数据类型

标量类型：数字型、字符型、日期型、布尔型；可以直接用的。

复合类型：record、associative array、nested table、varray；需要自己去定义的。

2.2标量类型

1. 数字类型：①number ②number的子类型dec(38)、float(38)、real(18)…

③binary_integer（只能在PL/SQL中用），按10进制赋值，但存的时候会换成2进制存。优势是计算快。

2）字符类型：①varchar2、varchar（长度：1.~32767）②string（只能在PL/SQL中用，长度：1.~32767）③char（长度：1~32767）④long

3）日期类型：date

4）布尔类型：boolean

①用于存储逻辑值true,false,null（java中只有true,false）。

②不能向数据库中插入boolean类型的数据。

③不能将列值保存到boolean变量中。

④只能对boolean变量执行逻辑操作。

2.3变量声明

1）语法：var_name type [constant][not null][:=value];

• 注意事项：PL/SQL规定没有初始化的变量为null。

2）直接定义类型

declare

v_n1 number :=1;--赋初值要有“冒号”，PL/SQL中赋值和等号是区分开的

v_c1 varchar2(10);

v_d1 date :=sysdate;--sysdate是函数，有返回值

begin

• 注意事项：

• PL/SQL中没返回值的叫过程；有返回值的叫函数，且必须有指向（要么打印出，要么赋值给变量）。所以，过程和函数的调用是不一样的。

• java中的方法一种没有返回值，一种是有返回值；有返回值的如果没返回，则会丢弃。但PL/SQL不行！

3）%type方式：变量具有与数据库的表中的某列或其他变量相同的类型。

eg1：三种声明方式

declare v_RealName varchar2(20);

declare v_RealName account.real_name%type;

declare v_TempVar number(7,3) not null :=12.3;

v_AnotherVar v_TempVar%type :=12.3;

eg2：定义变量，打印系统时间，并输出它们的值

set serveroutput on

declare

v_d1 date :=sysdate;

v_c1 varchar2(20);

begin

v_c1 :=to_char(v_d1,'yyyy mm dd hh24:mi:ss');

dbms_output.put_line('Current date is ' || v_c1);

--put_line只有一个参数，所以必须拼接！

end;

4）复合类型

record类型、集合类型。详细内容见后面的第五、六章！

3、流程控制语句

条件语句：if、case；循环语句：loop、while、for

3.1条件语句

1. if语句

方式一：

if then

statement;

end if;//最简写的方式

方式二：

if then

statement;

else

statement1;

end if;

方式三：

if boolean_expr then

statement;

elsif boolean_expr then

statement1;

else

statement2;

end if：

• 注意事项：else if在PL/SQL中的写法是elsif！

eg：输出布尔类型的变量的值

declare

v_b1 boolean :=false;

begin

if v_b1 then

dbms_output.put_line('true');

elsif v_b1 is null then

dbms_output.put_line('null');

else

dbms_output.put_line('false');

end if;

end;--注意：put_line中的参数只能为字符类型，但日期和数值会隐式转换，布尔类型不行！

2）case语句

case when then

when then

else

end;

3.2循环语句

1）loop循环（无条件进入）

语法：

loop

statement1;

statement2;

exit when<condition>

end loop;

• 注意事项：

• exit when<condition>子句是必须的，否则循环将无法停止。

• when后面直接写条件，没有尖括号或圆括号。

eg1：循环输出数字1～10

declare v_index binary_integer :=1;

begin

loop

dbms_output.put_line(v_index);

v_index := v_index+1;

exit when v_index>10;

end loop;

end;

eg2：循环插入10条记录（静态）

declare v_index binary_integer :=1;

begin

loop

insert into test_chang values(v_index);

v_index := v_index+1;

exit when v_index>10;

end loop;

end;

eg3：循环输出语句insert into test_chang values('i')，i为字符。

declare v_index binary_integer :=1;

begin

loop

dbms_output.put_line('insert into test_chang values('''||v_index||''')');

v_index := v_index+1;

exit when v_index>10;

end loop;

end;

2）while循环

语法：

while<boolean expr>loop

statement1; statement2;

end loop;

①循环语句执行的顺序是先判断<boolean expr>的真假，如果为true则循环执行，否则退出循环。

②在while循环语句中仍然可以使用exit或exit when子句。

• 注意事项：while后面直接写条件，没有尖括号或圆括号。

eg1：循环输出数字1～10

declare v_index binary_integer :=1;

begin

while v_index <=10 loop

dbms_output.put_line(v_index);

v_index := v_index+1;

end loop;

end;

eg2：循环插入10条记录（动态）

declare v_index binary_integer :=1;

begin

while v_index <=10 loop

execute immediate 'insert into test_chang values('||v_index||')';

v_index := v_index+1;

end loop;

end;

3）for循环

语法：

for 循环计数器in 下限.. 上限loop

statement1;

statement2;

end loop;

①循环计数器是一个变量，这个变量不需要声明（和java不一样）。它的作用域仅是在循环中。

②每循环一次，循环变量自动加1；使用关键字reverse，循环变量自动减1。

③可以使用exit或者exit when子句退出循环。

• 注意事项：

• 跟在in、in reverse后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式。

• 不能用null作上限或下限下标！

eg1：循环输出数字1～10

begin

for i in 1..10 loop

dbms_output.put_line(i);

end loop;

end;

eg2：使用in reverse循环输出数字1～10

begin

for i in reverse 1..10 loop

dbms_output.put_line(i);

end loop;

end;

eg3：循环插入10条记录（静态）

begin

for i in 1..10 loop

insert into test_chang values(i);

end loop;

end;

4、PL/SQL中的SQL

4.1 PL/SQL中的SQL分类

1）静态SQL

在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象，即SQL语句是在PL/SQL编译阶段编译的。效率高。能用静态就不用动态！

2）动态SQL

在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态的创建语句、对语句进行语法分析并执行该语句。效率低。

4.2 DML（insert，update，delete）和TCL（commit，rollback）

1）它们可以直接在PL/SQL中使用标准的SQL语句。

2）语法：

begin

insert into host(id) values('10.0.0.11');

commit;

end;

4.3 DDL

1）不能原封不动的像DML和TCL那样直接写。

2）所有的DDL语句要在PL/SQL中操作，必须用如下的语法形式（转成字符串），即本地动态SQL是使用execute immediate语句来实现的。

eg1：可执行

begin

execute immediate 'create table test_chang(c1 number)';

end;--本地动态SQL执行DDL语句

eg2：不可执行

begin

execute immediate 'create table test_chang(c1 number)';

insert into test values(1);

commit;

end;--报错！是编译错误，说test_chang不存在！说明表还没有创建，只是在编译期编译。

eg3：可执行

begin

execute immediate 'create table test_chang(c1 number)';

execute immediate 'insert into test_chang values(1)';

commit;

end;--编译，执行都通过！使用动态SQL

• 注意事项：在一段PL/SQL程序中，先create再insert，必须用execute immediate（静态sql和动态sql）

eg4：用循环向test_chang表中插入10条记录。（从1到10）

begin

execute immediate 'create table test_chang(c1 number)';

for i in 1..10 loop

execute immediate 'insert into test_chang values('||i||')';

end loop;

commit;

end;

5、PL/SQL中的select

5.1 select语句的实现

根据select语句返回的记录数，将select语句的实现分为两类：

1）当且仅当只返回“一条”记录：用select…into…语句实现（将结果放入到变量中）。

2）返回“0条或多条”记录：用cursor实现。

• 注意事项：select into确认select只返回一条记录，才不报错，否则加异常处理（没有返回记录或返回记录太多）。

eg：提供客户ID，打印客户姓名和年龄

declare

v_realname account.real_name%type;

v_age number(3);

begin

select real_name,round((sysdate-birthdate)/365)//减出的是天数

into v_realname,v_age

from account

where id=1005;--换成where 1=2报错：异常，无数据；换成where 1=1报错：返回记录太多

dbms_output.put_line('姓名：' | | v_realname||' 年龄：' | |v_age);

end;

3）select...into后的查询结果集种类

①若查询结果是单行单列，into子句后用标量类型（标准类型），与select子句后的目标数据类型一致即可。

②若查询结果是单行多列，into子句后的变量个数、顺序、每个变量的数据类型，应该与select子句后的目标数据相匹配；也可以用记录类型的变量。

5.2 record类型

记录类型，处理单行多列数据。

eg1：语法

declare

type t_cost_rec id record(base_cost cost.base_cost%type,--t_cost_rec是记录类型

base_duration cost.base_duration%type,--base_cost是成员

unix_cost cost.unit_cost%type);

v_cost t_cost_rec;--v_cost是记录类型的变量

v_cost_1 t_cost_rec;

eg2：打印每个客户的名字，年龄，身份证

declare

type t_account_rec is record(real_name account.real_name%type,

age number(3), idcard_no char(18));

v_account t_account_rec;

begin

select real_name,round((sysdate-birthdate)/365),idcard_no into v_account

from account

where id=1005;

dbms_output.put_line('姓名：'||v_account.real_name);

dbms_output.put_line('年龄：'||v_account.age);

dbms_output.put_line('身份证：'||v_account.idcard_no);

end;--t_account_rec是记录类型，real_name是成员，v_account是记录类型的变量。

5.3 %rowtype

用表结构或视图结构定义变量

当使用%rowtype定义记录变量时，record成员的名称和类型与表或视图的列名称和类型完全相同。

eg：语法

declare v_cost cost%rowtype;

5.4 record变量的引用

1）记录类型变量的属性引用方法是“.”引用，即变量名.属性名。

eg1：语法

declare

v_cost.base_cost :=5.9;

v_cost.base_duration :=20;

v_cost.unit_cost :=0.4;

v_cost_1 :=v_cost;

select base_cost,base_duration,unit_cost into v_cost_1 from cost where id =2;

eg2：语法

declare

v_cost cost%rowtype;

begin

select * into v_cost from cost where id=1;

dbms_output.put_line(v_cost.name);

dbms_output.put_line(v_cost.base_duration);

dbms_output.put_line(v_cost.descr);

end;

• 注意事项：打印记录类型，一定是打印它的成员！

2）若用PL/SQL程序对表进行DML操作，则该表必须事先存在，用SQL建表。

eg：语法

create table cost_t1 as select base_cost,base_duration,unix_host from cost where id=3;

3）在insert语句和update语句中可以使用记录类型变量。

eg：语法

begin

insert into cost_t1 values v_cost;

update cost_t1 set row = v_cost_1;

commit;

end;

4. 案例

eg：提供客户ID，打印客户名称和身份证号，若该客户不存在，打印客户不存在

declare

type t_account_rec is record(real_name account.real_name%type,idcard_no char(18));

v_account t_account_rec;

v_cnt number;

begin

select count(*) into v_cnt from account where id =1005;

if v_cnt=1 then

select real_name,idcard_no into v_account from account where id=1005;

dbms_output.put_line('姓名：'||v_account.real_name);

dbms_output.put_line('身份证：'||v_account.idcard_no);

else

dbms_output.put_line('用户不存在');

end if;

end;

5.5 cursor的概念

1）根据select语句返回的记录数，若返回记录数是0条或多条用cursor实现。

2）Oralce使用专有SQL工作区（private SQL workarea）来执行SQL语句，存储处理信息。这个工作区称为“cursor”。

3）Oracle所执行的每一个SQL语句都有唯一的cursor与之相对应。

4）程序员用PL/SQL的cursor定义所需执行的select语句。

5.6 cursor的分类

隐式：select…into语句、DML语句

显式：返回多条记录的select语句用显式cursor实现。

5.7显式cursor的处理

如下图所示：

5.8显式cursor的属性

目的：获取有关显式cursor的状态信息。

• 注意事项：

• 布尔值（true、false、null），当不fetch时，open后直接%found，则返回null。

• %notfound和%found缺省为null，由fetch去改变。

• sqlplus命令：exec 过程名，即可直接调用过程。

5.9隐式cursor的属性

目的：获取有关隐式cursor的状态信息。

• 注意事项：sql是指最近的sql语句。

eg：相关操作

begin

insert into test_chang values(100);

dbms_output.put_line(sql%rowcount);

update test_chang set c1=10 where c1=100;

dbms_output.put_line(sql%rowcount);

end;

5.10 cursor的声明

1）在游标声明中使用标准的select语句。

2）如果需要按指定的次序处理行，可在查询中使用order by子句。

3）可以在查询中引用变量，但必须在cursor语句之前声明这些变量。

eg：语法

declare

cursor c_service_id(p_cost_id number)is select id from service where cost_id=p_cost_id;

begin

5.11 open cursor

1）通过open cursor来执行select语句并标识结果集。

2）select语句如果没有返回记录，不会出现异常。

3）语法：open c_service_id(5);

5.12 fetch cursor

1）检索当前行，把值赋给变量。

2）变量可以是record类型或简单变量。

3）如果是简单变量：①包含相同数量的变量。②把每一个变量和相应的列进行位置匹配。

4）通过循环检测cursor中是否包含数据行。

5）语法：

fetch cursor_name into [var1,var2,…|record_name];

fetch c_service_id into v_service_id

• 注意事项：

• fetch into 变量：变量一定是记录类型，与select后的列的个数、类型相同。

• cursor不会因为select子句的结果集为0或多值报错。

5.13结果集提取的控制

1）使用循环处理显式cursor结果集中的多行数据。

2）每次fetch一行，反复进行。

3）使用%notfound属性检测一次不成功的提取操作。

4）使用显式cursor的属性检测每一次提取是否成功，避免出现无限循环。

5.14 close cursor

1）处理完结果集中的数据后，应该关闭cursor。

2）如果需要，可以再次打开该cursor。

3）cursor一旦关闭，所有和该cursor相关的资源都会被释放，不可再从关闭的cursor中提取数据。

4）任何对关闭的cursor的操作都会引发invalid_cursor错误。

5）每个session能打开的cursor数量由open_cursor参数决定。

6）语法：close c_service_id;

eg1：打印每个客户的名字和身份证号码（使用loop循环处理cursor）。

declare

cursor c_account is select real_name from account where 1=1;

v_realname varchar2(20);

begin

open c_account;--open则结果集已经在内存中了

loop

fetch c_account into v_realname;

exit when c_account%notfound;

dbms_output.put_line(v_realname);--若输出在判断前，则最后的记录多打印一次

end loop;

close c_account;

end;

eg2：打印每个客户的名字和身份证号码，若查询没有记录返回，打印客户不存在（使用loop循环处理cursor）。where 1=1输出所有用户姓名，where 1=2输出no account

declare

cursor c_account is select real_name from account where 1=1;

v_realname varchar2(20);

begin

open c_account;

fetch c_account into v_realname;

if c_account%notfound then

dbms_output.put_line('no account');

else

loop

dbms_output.put_line(v_realname);

fetch c_account into v_realname;

exit when c_account%notfound;

end loop;

end if;

close c_account;

end;

eg3：打印每个客户的名字和身份证号码，若查询没有记录返回，打印客户不存在（使用loop循环处理cursor）。v_realname也可用rowtype定义为cursor类型，但要注意变量的引用。

declare

cursor c_account is select real_name from account where 1=1;

v_realname c_account％rowtype;

begin

open c_account;

fetch c_account into v_realname.real_name;

if c_account%notfound then

dbms_output.put_line('no account');

else

loop

dbms_output.put_line(v_realname.real_name);

fetch c_account into v_realname.real_name;

exit when c_account%notfound;

end loop;

end if;

close c_account;

end;

eg4：打印每个客户的名字和身份证号码，若查询没有记录返回，打印客户不存在（使用while循环处理cursor）。

declare

cursor c_account is select real_name,idcard_no from account where 1=1;

v_account c_account%rowtype;

begin

open c_account;

fetch c_account into v_account;

if c_account%notfound then

dbms_output.put_line('no account');

else

while c_account%found loop

dbms_output.put_line('姓名：'||v_account.real_name||' 身份证：'||v_account.idcard_no);

fetch c_account into v_account;

end loop;

end if;

close c_account;

end;

• 注意事项：用while循环和loop循环时，注意fetch和put_line()的顺序；否则while会少打记录，loop会多打记录。

eg5：打印每个客户的名字和身份证号码（使用for循环处理cursor）

方式一：for的最简单写法

begin

for i in(select real_name,idcard_no from account)

loop

dbms_output.put_line('姓名：'||i.real_name||' 身份证：'||i.idcard_no);

--dbms_output.put_line(c_account%rowcount);无法打印，因为拿不到cursor的名字

end loop;

end;

• 注意事项：for循环把cursor的操作集成了（open、fetch、close），自动打开关闭，自动把值fetch给i，所以i的类型是记录类型。

方式二：定义一个cursor

declare

cursor c_account is select real_name,idcard_no from account;

begin

for i in c_account

loop

dbms_output.put_line('姓名：'||i.real_name||' 身份证：'||i.idcard_no);

dbms_output.put_line(c_account%rowcount);

end loop;

end;

eg6：打印每个客户的名字和身份证号码，若查询没有记录返回，打印客户不存在（使用for循环处理cursor）

方式一：

declare

cursor c_account is select real_name,idcard_no from account where 1=1;

v_account c_account%rowtype;

begin

open c_account;

fetch c_account into v_account;

if c_account%notfound then

dbms_output.put_line('no account');

else

close c_account;

for i in c_account

loop

dbms_output.put_line('姓名：'||i.real_name||' 身份证：'||i.idcard_no);

end loop;

end if;

close c_account;

end;

方式二：

declare

cursor c_account is select real_name,idcard_no from account where 1=1;

v_cnt binary_integer := 0;

begin

for i in c_account loop

dbms_output.out_line(rpad(i.real_name,12,' ')||' '||i.idcard_no);

v_cnt := 1;

end loop;

if v_cnt = 0 then

dbms_output.put_line('account not exists!');

end if;

end;

6、集合

6.1什么是collection

collection是按某种顺序排列的一组元素，所有的元素有相同的数据类型，每个元素有唯一一个下标标识其在这一组元素中的位置。

分类：

1）Associative arrays（关联数组）又称index-by table，使用键值访问。

2）Nested table嵌套表（本课程不讲解）。

3）Varray数据，变长数组，定义时需要指定数组大小（本课程不讲解）。

6.2什么是关联数组

1）关联数组就是键值对的集合，其中键是唯一的，用于确定数组中对应的值。

2）键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素，而后续这样的操作就是更新元素。

3）关联数组能帮我们存放任意大小的数据集合，快速查找数组中的元素。它像一个简单的SQL表，可以按主键来检索数据。

6.3 Associative arrays的定义

1）同种类型的一维、无边界的稀疏集合，只能用于PL/SQL。

2）type_name是关联数组的类型名，element_type是关联数组中存储的元素类型。

3）index by后面的数据类型是下标的数据类型。

• 注意事项：

• 关联数组和下标不要求连续，不要求从0、从1开始。

• 集合对下标是自动排序的，按元素排序要自己写算法。

• 两个下标一样，则是覆盖操作！

eg1：语法

type type_name is table of element_type [not null]

index by [binary_integer|pls_integer|varchar2(size_limit)];

eg2：集合下标自动排序

dbms_output.put_line('index:'||v_account.first);--输出排序后的第一个下标

dbms_output.put_line('index:'||v_account.last); --输出排序后的最后一个下标

6.4声明Associative arrays类型和变量

eg：声明类型

type associative_array_type is table of number index by binary_integer;

eg：声明变量

V1 associative_array_type;

V2 v1%type;

• 注意事项：

v_account account%rowtype;

type is table of v_account index by binary_integer;

-报错v_account是变量，不是数据类型，改为account%rowtype才正确

6.5关联数组的操作

eg：语法

declare

type t_indtab is table of number

index by binary_integer;

v_indtab t_indtab;

begin

v_indtab(1) :=1;

v_indtab(5) :=5;

v_indtab(6) :=6;

v_indtab(10) :=10;

dbms_output.put_line(v_indtab(10));

dbms_output.put_line(v_indtab(7));

end;

6.6 Associative arrays的方法

1）exists方法：

①exists(1)，判断第1个元素是否存在。

②在使用count方法前，建议先用exists来判断一下，这样可以避免抛出异常。

eg：语法

if courses.exists(i) then

courses(i) := new_course;

end if;

2）count方法：

①返回联合数组的元素个数，不包括被删除的元素。

②对于空的联合数组，返回值也是0；

eg：语法

if projects.count = 25 then ……

3）first和last：返回最小和最大下标号，如果collection为空，则返回null。

4）prior(n)和next(n)：返回第n个元素的前一个和后一个，如果不存在，则返回null。

5）trim(n)：从最后一个元素删除n个元素。

6）delete方法：

①delete：删除所有元素

②delete(n)：删除第n个元素

③delete(m,n)：从第m个元素删除到第n个元素

eg：相关操作

declare

type t_account_idxtab is table of varchar2(20)--元素的数据类型

index by binary_integer;--下标的数据类型

cursor c_account is select id,real_name from account;--用id作下标

v_account t_account_idxtab;

begin

for i in c_account loop

v_account(i.id) :=i.real_name;

dbms_output.put_line('index:'||i.id);

dbms_output.put_line('element:'||v_account(i.id));

end loop;

dbms_output.put_line('element:'||v_account(1005));

dbms_output.put_line('index:'||v_account.first);

dbms_output.put_line('index:'||v_account.last);

end;

6.7关联数组的遍历

eg1：语法

declare ……

begin ……

for i in v_indtab.first .. v_indtab.last loop

dbms_output.put_line(v_indtab(i));

end loop;

end;

eg2：关联数组的遍历（用while循环）

declare

type t_account_idxtab is table of varchar2(20)--元素的数据类型

index by binary_integer;--下标的数据类型

cursor c_account is select id,real_name from account;--用id作下标

v_account t_account_idxtab;

v_index binary_integer;

begin

for i in c_account loop

v_account(i.id) :=i.real_name;

end loop;

v_index :=v_account.first;

while v_index <= v_account.last loop

dbms_output.put_line(v_account(v_index));

v_index := v_account.next(v_index);--找1005的下一个下标

end loop;

end;

eg3：关联数组的遍历（用for循环）

declare

type t_account_idxtab is table of varchar2(20)--元素的数据类型

index by binary_integer;--下标的数据类型

cursor c_account is select id,real_name from account;--用id作下标

v_account t_account_idxtab;

begin

for i in c_account loop

v_account(i.id) :=i.real_name;

end loop;

for i in v_account.first .. v_account.last loop

if v_account.exists(i) then

dbms_output.put_line(v_account(i));

end if;

end loop;

end;

• 注意事项：

• 对于不连续的下标，用for循环会空转，效率低。

• 下标是连续的用for循环好。

• 下标不连续的用while循环好（匹配集合中的next()方法）。

6.8 Associative arrays的异常

现象：

declare

type numlist is table of number index by binary_integer;

nums numlist;--自动初始化为null

begin

nums(null) :=3;--触发value_error，没下标，下标不能为空

if nums(1) =1 then --触发no_data_found，下标有，但没元素

6.9批量绑定

1）通过bulk collect减少loop处理的开销。

2）采用bulk collect可以将查询结果一次性的加载到collections中。

3）可以在select into、fetch into、returning into语句中使用bulk collect。

• 注意事项：选择操作的几种方式：select、select into、cursor fetch into、select bulk collect into、fetch bulk collect into、fetch bulk collect into limit 10

4）注意在使用bulk collect时，所有的into变量都必须是collections。

• 注意事项：

• 当返回多条记录时可用bulk collect和cursor，但cursor是一条一条处理，bulk collect是批量处理。

• 集成度越高，可控的内容越少，如下标不可控了。

• 使用bulk collect时，下标自动生成从1开始。

• 使用问题：如果内存空间不够，则将无法执行下去，所以要限制limit一次取出的数量。

eg1：fetch bulk collect into

declare

cursor is v_account 集合变量

begin

open c_account;

fetch bulk collect into v_account limit 3;

close c_account;

eg2：打印每个客户的名字、年龄、以及累计年龄

方式一：

declare

type t_account_rec is record (real_name varchar2(20),age binary_integer);

type t_account_idxtab is table of t_account_rec index by binary_integer;

v_account t_account_idxtab;

v_sum binary_integer :=0;

begin

select real_name,round((sysdate-birthdate)/365) bulk collect into v_account from account where 1=1;

dbms_output.put_line('RealName'||' '||'Age'||' '||'SumAge');

if v_account.count<>0 then

for i in v_account.first .. v_account.last loop

v_sum :=v_sum + v_account(i).age;

dbms_output.put_line(rpad(v_account(i).real_name,12,' ')||' '||rpad(v_account(i).age,4,' ')||' '||v_sum);

end loop;

else

dbms_output.put_line('no account');

end if;

end;

方式二：

declare

cursor c_account is select real_name,round((sysdate-birthdate)/365) age--想引用必须起别名

from account;

type t_account_indtab is table of c_account%rowtype index by binary_integer;

v_account t_account_indtab;

v_culage binary_integer :=0;

begin

select real_name,round((sysdate-birthdate)/365)age bulk collect into v_account

from account

where 1=1;--用bulk collect也可处理多条记录，下标自动生成从1开始

if v_account.first is not null then

for i in v_account.first .. v_account.last loop

v_culage :=v_culage + v_account(i).age;

dbms_output.put_line(rpad(v_account(i).real_name,12,' ')||' '||rpad(v_account(i).age,4,' ')||' '||v_culage);

end loop;

else

dbms_output.put_line('no account');

end if;

end;

7、异常

7.1 Oralce错误

1）PL/SQL错误：编译时错误、运行时错误。

2）运行时的错误：Oracle错误（ORA-XXXXX）、PL/SQL运行错误、用户定义的错误。

7.2 Oralce错误处理机制

1）在程序运行期间的错误对应一个异常（exception）。

2）一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。

7.3异常的类型

1）隐式触发：Oracle预定义异常、非Oracle预定义异常。

2）显式触发：用户自定义异常。

7.4 PL/SQL中的异常

如下图所示：

7.5异常捕获

1）异常的捕获通过异常处理器实现。它是程序的一个独立部分，把错误与程序的其他部分分离开来，使程序逻辑更加易于理解。

2）当异常产生时，控制权立即转移到异常处理器。一旦执行控制权被转移到异常处理器，就无法再回到本语句块的可执行部分。如没有异常部分，则被传播到外层语句块（类似于java中的异常一直向上抛）。

3）异常处理器语法：

exception

when exception_name1 then

sequence_of_statements1;

when exception_name2 then

sequence_of_statements2;

[when others then

sequence_of_statements3;]

end;

7.6异常的捕获规则

1）exception关键字，标识异常处理的开始区域。

2）一个异常处理器可以捕获多个异常，只需要在when子句中用or连接即可。

3）允许有多个异常处理器。

4）一个异常只能被一个异常处理器捕获，并进行处理。

5）others异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。

7.7 Oralce预定义错误的捕获

1）no_date_found(ORA-1403)：如没有找到数据。

2）too_many_rows(ORA-1422)：如返回的行数超出请求行数。

3）invalid_cursor(ORA-1001)：如cursor没有open就使用。

4）zero_divide(ORA-1476)：如0做了除数。

5）dup_val_on_index(ORA-0001)：如多个重复索引（违反了唯一键约束）。

6）value_error(ORA-6502)：如集合（数组）中下标为null（或着说元素为空）。

7）不需要定义，不需要触发，只要捕获到即可。

8）Oralce预定义错误的捕获

eg1：语法

begin

select …… commit;

exception

when no_date_found then

statement1;

when too_many_rows then

statement2;

when others then

statement3;

end;

eg2：相关操作

declare

v_realname varchar2(20);

begin

insert into test_chang values(1);

insert into test_chang values(1);

commit;--保证没有把锁带进异常

select real_name into v_realname from account where 1=1;

dbms_output.put_line('a');--异常时，此句不执行

exception--执行一个异常后

when no_data_found then--若不接异常，则报错ORA—1403：no date found

dbms_output.put_line('no data found');

when too_many_rows then--若不接异常，则报错ORA—1422：exact fetch returns

more than requested number of rows

dbms_output.put_line('too many rows');

when dup_val_on_index then

rollback;--要做都做，要不做都不做，回滚保证没有锁

end;

7.8非Oracle预定义异常

将一个经过命名的异常和一个Oracle错误相关联。

1）在语句块的声明部分声明一个异常名称：e_integrity exception;

2）通过pragma exceptoin_init将异常与一个Oracle错误号相关联：

pragma exception_init(e_integrity,-2291)

3）在异常处理部分捕获并处理异常：when e_integrity then ……

eg：非预定义异常

declare

e_noparent exception;

pragma exception_init(e_noparent,-2291);

e_childexist exception;

pragma exception_init(e_childexist,-2292);

begin

delete from parent_chang where c1=1;

insert into child_chang values(1,2);

exception

when e_noparent then

dbms_output.put_line('no parent');

when e_childexist then

dbms_output.put_line('e_childexist');

end;

7.9用户自定义异常

1）用户自定义异常必须在声明部分进行声明。

2）当异常发生时，系统不能自动触发，需要用户使用raise语句（类似java中的throw）。

3）在异常处理部分捕获并处理异常。

eg1：自定义大于100为异常

declare

v_n1 number :=101;

e_more100 exception;

begin

if v_n1 > 100 then

raise e_more100;

end if;

exception

when e_more100 then

dbms_output.put_line('>100');

end;

eg2：others接受所有未捕获的异常，sqlcode、sqlerrm

declare

v_n1 number :=101;

e_more100 exception;

begin

insert into test values('abc');--异常

if v_n1 > 100 then

raise e_more100;

end if;

exception

when e_more100 then

dbms_output.put_line('>100');

when others then

dbms_output.put_line('a'||sqlcode);--获取错误的编号

dbms_output.put_line('a'||sqlerrm);--获取错误的信息

end;

7.10异常处理总结

1）在声明部分为错误定义异常时，包括非Oracle预定义异常和用户定义异常

①e_exception exception;

②pragma exception_init(e_exception,-####);

2）在执行过程中当错误产生时抛出与错误对应的异常：raise excepname。

3）在异常处理部分通过异常处理器捕获异常，并进行异常处理。

7.11 sqlcode和sqlerrm

通过以下两个函数获取错误相关信息。

1）sqlcode：返回当前错误代码

①如果是用户定义错误返回值为1；

②如果是ORA-1403：no data found错误，返回值为100

③其他Oracle内部错误返回相应的错误号。

eg：语法

exception--执行一个异常后

when no_data_found then--若不接异常，则报错ORA—1403：no date found

dbms_output.put_line('no data found');

dbms_output.put_line('a'||sqlcode);--100

when too_many_rows then--若不接异常，则报错ORA—1422：exact fetch returns more than requested number of rows

dbms_output.put_line('too many rows');

dbms_output.put_line('a'||sqlcode);--1422

end;

2）sqlerrm：返回当前错误的消息文本

①如果是Oracle内部错误，返回系统内部的错误描述

②如果是用户定义的错误，则返回信息文本为“user-defined exception”。

eg：语法

declare

v_n1 number :=101;

e_more100 exception;

begin

insert into test values('abc');--异常

if v_n1 > 100 then

raise e_more100;

end if;

exception

when e_more100 then

dbms_output.put_line('>100');

when others then

d bms_output.put_line('a'||sqlcode);--a-1722

dbms_output.put_line('a'||sqlerrm);--aORA-01722: 无效数字

end;

• 注意事项：insert into log_tab values(sqlcode,sqlerrm,sysdate);sqlerrm不能直接插入表中，要赋给一个变量！v_errm :=sqlerrm;

insert into log_tab values(sqlcode,v_errm,sysdate);--才能执行

7.12异常的传播

1）可执行部分异常的传播：

如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传递到外层语句块。

如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。然后，对外层语句块执行步骤1。如果没有外层语句块，则该异常将传播到调用环境。

2）声明部分异常的传播：

声明部分的异常立刻传播到外层语句块，即使当前语句块有异常处理器。

3）异常处理部分的异常的传播：

异常处理器中产生的异常，可以有raise语句显式产生，也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。

8、子程序

8.1子程序

1）匿名子程序，即匿名块declare begin exception end;

①匿名块不存在数据库中②每次使用时都会进行编译③不能在其他块中相互调用

2）有名子程序

8.2有名子程序

1）命名的PL/SQL块，编译并存储在数据库中，可以在任何需要的地方调用。

2）子程序的组成部分：子程序头、声明部分、可执行部分、异常处理部分（可选）。

• 注意事项：有名子程序编译和运行是分开的。

8.3有名子程序的分类

procedure：过程，put_line() function：函数

package：包，dbms_output trigger：触发器

8.4有名子程序的优点

1）模块化：将程序分解为逻辑模块。 2）可重用性：可以被任意数目的程序调用。

3）可维护性：简化维护操作。 4）安全性：通过设置权限，是数据更安全。

9、过程procedure

9.1语法

create [or replace] procedure proc_name [(arg_name[{in|out|in out}]) type,… ]

｛is｜as｝

<local variable declaration>--存储过程要用到的局部变量

begin

<executable statements>

exception

<exception handlers>

end;

9.2创建存储过程

就是编译存储过程的代码，目的是为了调用它。

eg：语法

create or replace procedure p_accountno_chang--只要是create出来的都是数据库对象，类型是procedure

is

v_cnt number;--存储过程要用到的局部变量

begin

select count(id) into v_cnt from account;

dbms_output.put_line(v_cnt);

end;

• 注意事项：

• select status from user_objects where object_name='P_ACCOUNTNO_CHANG';--查看状态，名字必须大写，存的时候是按大写存的

• select type from user_source where name='P_ACCOUNTNO_CHANG';--查看是哪种有名子程序

• --create or replace view(procedure)编译不成动，但数据库对象已创建，只不过状态是invalid

9.3形参和实参

1）在创建过程语句中的参数为形参

create or replace procedure procchang(p_n1 number)--p_n1是形参

is

v_n1 number;--v_n1是过程中的局部变量

begin

v_n1:=p_n1;

end;

2）在调用过程时括号内的参数为实参

declare

v_n1 number :=1;

begin

procchang(v_n1);--v_n1是实参

procchang(2);--2是实参

end;

• 注意事项：

• 形参相当于实参的“占位符”。

• 形参的名字和列名相同，系统则默认按列名。

9.4形参的种类

1）in：参数的缺省模式！在调用过程的时候，实际参数的值被传递给该过程；在过程内部，形参是只可读的。

2）out：在调用过程时，任何的实参将被忽略；在过程内部，形参是只可写的。

3）in out：是in与out的组合。在调用过程的时候，实参的值可以被传递给该过程；在过程内部，形参可以被读出也可以被写入；过程结束时，控制会返回给控制环境，而形式参数的内容将赋给调用时的实际参数。

• 注意事项：

• in 相当于java中八大基本类型+string的类似效果。

• in out 相当于java中的引用类型的类似效果。

• out 无对应。

9.5调用存储过程

1）用匿名块调用

begin

procedure_name();

end;

2）在SQL动作表中直接调用

exec procedure_name();

9.6存储过程中的参数

eg1：语法

create or replace procedure procchang(

p_c1 in varchar2,--只有此时varchar才不用写长度，长度有传入的实参决定

p_c2 out varchar2,p_c3 in out varchar2)

is

v_c1 varchar2(10);--存储过程要用到的局部变量

begin

v_c1 :=p_c1;

--p_c1 :=p_c1||'d';--报错，不能作赋值目标。in在调用过程中，形参只可“读取”实参的值，即不能被写（不能在赋值语句左边）。

p_c2 :=p_c2||'d';--out在调用过程中，任何实参都被忽略，形参只可“写入”实参中（在过程内部可读，但无值，即p_c2 :=p_c2||'d';不报错）。

p_c3 :=p_c3||'d';--只有变量才能作赋值目标

end;

eg2：用匿名块调用过程

declare

v_c2 varchar2(20) :='abc';--对于传出参数没必要定义初值，即也可v_c2 varchar2(20);

v_c3 varchar2(20) :='abc';

begin

procchang('abc',v_c2,v_c3);--过程的调用可直接单独写出，但函数不行，不要有变量接收返回值

dbms_output.put_line(v_c2);--存储过程依靠out返回，d

dbms_output.put_line(v_c3);--abcd

end;

9.7对实际参数的要求

1）模式为in的形参对应的实际参数可以是“常量或变量”。

2）模式为in out或out的形参对应的实际参数必须是“变量”，用于存储返回的值，所以不能是常量或表达式。

9.8形式参数的限制

1）在调用过程当中，实际参数在将值传递给过程时，也传递了对变量的限制。

2）形式参数不能声明长度，但可使用%type来进行限制。

9.9带参数的过程调用

1）位置表示法：调用时添入所有参数，实参与形参按顺序一一对应。

2）名字表示法：调用时给出形参名字，并给出实参：

procname(12,p_outparm => v_var1,p_inout => 10);

前为形参，后为实参；理解为形参对应的实参

3）两种方法可以混用：混用时，第一个参数必须通过位置来指定，名字表示对于参数很多时，可提高程序的可读性。

9.10使用缺省参数

1）形参可以指明缺省值：parm_name [mode] type {:= | default} init_value

2）位置标示法时，所有的缺省值都放在最后面。

3）使用名字标示法则无所谓。

4）声明时，如果有缺省值，尽量将缺省值放在参数表的末尾。

eg：使用缺省参数default

create or replace procedure pro_chang1(p_c1 number,p_c2 number,p_c3 number,

p_c4 number default 1,p_c5 number default 1)

is

begin

dbms_output.put_line(p_c1); dbms_output.put_line(p_c2);

dbms_output.put_line(p_c3); dbms_output.put_line(p_c4);

dbms_output.put_line(p_c5);

end;

exec pro_chang1(1,2,3);--1,2,3,1,1 把缺省值放最后

exec pro_chang1(1,2,3,4,5);--1,2,3,4,5

9.11存储过程中的DDL语句

eg：语法

create or replace procedure proc1

is

begin

execute immediate ‘create table test(c1 number)’;

end;

exec proc1

ORA-01031:权限不足

• 注意事项：为何会出现创建table权限不足问题？明明创建table已经好几天了？！

问题分析：

1）DBA创建用户：

create user jsd1304;--创建的用户初始什么都做不了，只能慢慢授权各种权限

create role connect--角色connect，是缺省创建的

create role resource--另一种角色resource

2）把角色授权给用户：

grant connect,resource to jsd1304;--授权两种角色给用户1304

user role:enable、disable--用户角色分为：可用和不可用

3）举例说明：

create role role1--创建角色role1

grant create table to role1;--把创建table的权限授予角色role1

grant create index to role1;--把创建index的权限授予角色role1

grant create view to role1;--把创建view的权限授予角色role1

grant role1 to jsd1304;--把角色role1所拥有的权限授予用户jsd1304

4）用户能创建table的原因：用户被授予了一个角色，而角色是可用的

connect role enable priviledge 可用

5）而在处理有名块时，把用户的所有角色disable，priviledge不可用了，所以会出现权限不足问题

6）解决方法：grant create table to jsd1304;--直接授予的权限不会被disable

7）因此，可总结如下：

①在过程中执行DDL操作，所需的权限必须通过直接授予的方式，不能通过角色授予。

②调用过程时，所有角色都是disable的（系统做的），即角色中包含的所有权限都不能生效。

③调用其他用户的过程，必须由过程的属主授予执行权限：grant execute on procname to username;

④权限-->角色-->用户<--权限

9.12变量

1）局部变量：在匿名块或存储过程中定义的变量为局部变量，即作用域在整个匿名块或存储过程中。程序运行结束，该变量也就不存在了。

2）绑定变量（宿主变量）：在PL/SQL的SQL中可以直接使用绑定变量：bv_name，即也可不需要定义。标志是以“冒号”开头。

eg1：绑定变量的定义

--variable i number--绑定变量的定义，也可不声明；且不能定义宽度；且最后一定要有赋值

begin

for i in 1..10 loop

execute immediate 'insert into test_chang values (:i)' using i;

--系统使用using自动给绑定变量赋值，用绑定变量是占位置的，若不声明则系统认为存在！

end loop;

dbms_output.put_line(:i);

commit;

end;

eg2：绑定变量的赋值

variable i number

begin

:i :=1;

end;

print i;--1,print是环境里的命令，不是PL/SQL命令

exec :i :=10000--绑定变量的赋值

print i;

3）各类变量总结

①variable i：绑定变量，不退出环境，变量始终存在，跨session的，生命周期最长。

②declare .. begin之间和is .. begin之间定义的变量：叫局部变量，匿名块退出，变量不存在。

③for i：i变量不需要声明，for循环结束，变量不存在。

④调用一个函数、过程时，需要提供实参，绑定变量和局部变量可作实参。作用域匿名块里。

⑤定义一个函数、过程时，用形参，形参作用域在函数、过程里。

⑥在包package声明中的变量v1，在包中的过程、函数里都能直接调用，不用写包名；如果出了包：匿名块、过程、函数、其他包里可以用包名调，pkg.v1。也称为session里的全局变量。

9.13 PL/SQL中的SQL分类

1）静态SQL：在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象，即SQL语句是在PL/SQL编译阶段编译的。

• 注意事项：

• SQL书写是有规范的，目的是减少硬分析（硬分析对cpu、内存的耗费代价最高）。

• SQL中使用绑定变量（非常重要）！目的是减少硬分析。

• SQL出现在各种开发工具中，两种形式：静态SQL，动态SQL。比如：jdbc只能提供动态SQL方式，PL/SQL可以使用静态方式（运行时sql已经编译过了，oracle对其做额外的优化，如cursor cache，减少软分析的次数）

• 一个session中可以同时执行sql的数量是有限的（每打开一个cursor就会占内存，内存限制）。

• 一个session可以打开的cursor是有限的（参数限制open_cursor）。

2）动态SQL：在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

• 注意事项：在编译期execute immediate相当于SQL语句，后面的就是单纯的字符串，不看内容。只有在执行时才看、报错。

3）PL/SQL中的静态SQL：

①Oracle在解析SQL时会吧PL/SQL中定义的变量转化为绑定变量，insert into test_chang values(i);减少了硬分析次数。

②server process将执行完的SQL cache起来，不关闭！当再次执行SQL时，不需要软分析。

③静态高效的原因是系统自动使用了绑定变量，同时动态SQL也可用绑定变量方式。

④静态SQL的1次软分析是由于cursor用完没有被关闭，而是下次继续使用。

⑤过程中的参数会自动转化为绑定变量。

eg1：静态SQL

create or replace procedure proc1

is

begin

for i in 1..1000 loop

insert into test_chang values(i);

end loop;

commit;

end;--编译存储过程时编译SQL语句

begin proc1 end；--1次硬分析，1次软分析，1000次执行，静态SQL的1次软分析是由于cursor用完没有被关闭，而是下次继续使用

eg2：动态SQL（未使用绑定变量）

create or replace procedure proc1

is

begin

for i in 1..1000 loop

execute immediate ‘insert into test_chang values(’||i||‘)’;

end loop;

commit;

end;--编译存储过程时不编译SQL语句

begin proc1 end；--1000次硬分析，1000次软分析，1000次执行

eg3：动态SQL（使用绑定变量）

create or replace procedure proc1

is

begin

for i in 1..1000 loop

execute immediate ‘insert into test_chang values(:i) using i;

end loop; commit;

end;--编译存储过程时不编译SQL语句

begin proc1 end；--1次硬分析，1000次软分析，1000次执行

9.14 再一次来看SQL语句的处理过程

1）语法检查（syntax check）：检查此SQL的拼写是否符合语法。

2）语义检查（semantic check）：诸如检查SQL语句中的访问对象是否存在及该用户是否具备相应的权限。

3）对SQL语句进行解析（pares）：利用内部算法对SQL进行解析，生成解析树（pares tree）及执行计划（execute plan）。

4）执行SQL，返回结果（execute and return）。

9.15软分析和硬分析

Oracle利用内部的hash算法来取得该SQL的hash值，然后在library cache里查找是否存在该hash值。

1）假设存在，则将此SQL与cache中的进行比较；假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解释的过程。

2）如果上面两个假设中有任意一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫做硬解析。

• 注意事项：hash算法对大小写是敏感的

9.16对过程procedure的基本操作

1）创建并编译过程：create or replace procedure

2）编译过程：alter procedure procname compile;

3）调用过程：用匿名子程序调用，直接写过程名；用有名子程序调用，直接写过程名。

4）删除过程：drop procedure;

9.17案例

eg1：提供客户ID，返回客户名称、年龄，若该客户不存在则返回no account

create or replace procedure pro_chang(p_id number,name out varchar2,age out number)

is

begin

select real_name,round((sysdate-birthdate)/365)into name,age from account

where id=p_id;

exception

when no_data_found then

name:='no account';

age:=0;

end;

declare

v_realname varchar2(20);

v_age number(3);

begin

pro_chang(105,v_realname,v_age);

dbms_output.put_line(v_realname||' '||v_age);

end;

eg2：使用绑定变量

variable b_realname varchar2;

variable b_age number;

begin

pro_chang(1005,:b_realname,:b_age);

end;

print b_realname;

print b_age;

• 注意事项：也可使用exec pro_chang(1005,:b_realname,:b_age);直接调用

10、函数function

10.1语法

create [or replace] function func_name[(arg_name [{in|out|in out}]) type,…)]

return type

{is|as}

<local variable declaration>--函数中要用到的局部变量

begin

<executable statements>

return value;

exception

<exception handlers>

end;

10.2创建函数

create or replace function fun1(p_in number,p_out out number)return number

is

begin

p_out :=2;

return p_in;

end;

10.3调用函数

方式一：

declare

v_out number(3);

being

dbms_output.put_line(fun1(10,v_out)||' '||v_out);

end;

方式二：

declare

v_out number(3);

v_fun1 number;--java中函数可以单独使用，但PL/SQL中不可以，有传出要定义变量接收。

being

v_fun1 :=fun1(10,v_out);--函数不能单独放，函数的调用方式为表达式右边

dbms_output.put_line(v_fun1||' '||v_out);

end;

方式三：

variable b_out number

exec dbms_output.put_line(fun1(10,:b_out));

print b_out

10.4对函数function的基本操作

1）创建并编译函数：create or replace function

2）编译函数：alter function funcname compile;

3）调用函数：用匿名子程序调用，允许写值的位置就可以调用函数；用有名子程序调用，允许写值的位置就可以调用函数；在DML语句和select语句中调用。

4）删除函数：drop function;

10.5过程和函数的比较

• 注意事项：过程的那一套东西也都适合函数。

10.6匿名块中的过程和函数声明

declare

v_n1 number :=1;

function fun1(p_in number) return number

is

begin

return p_in;

end;

procedure proc1

is

begin

dbms_output.put_line(fun1(v_n1));

end;

begin

proc1;

end;

• 注意事项：过程和函数只能在本匿名块中调用

10.7案例

eg1：创建一个函数并调用

create or replace function fun1(p_c1 varchar2,p_c2 out varchar2)

return varchar2

is

begin

p_c2 :=p_c1||'d';

return p_c1;

end;

declare

v_c2 varchar2(20);

begin

dbms_output.put_line(fun1('abc',v_c2));--abc

dbms_output.put_line(v_c2);--abcd

end;

eg2：提供客户ID，返回客户名称、年龄，若该客户不存在则null

create or replace function getaccount(p_id number,p_age out number)return varchar2

is

T ype t_account_rec is record(real_name account.real_name%type,age number(3));

v_account t_account_rec;

begin

select real_name,round((sysdate-birthdate)/365) into v_account

from account

where id = p_id;

p_age := v_account.age ;

return v_account.real_name;

exception

when no_data_found then

v_account.real_name :='null';

return v_account.real_name;

end;

declare

v_age number(3);

begin

dbms_output.put_line(getaccount(10111,v_age)|| v_age);

end;

11、包package

11.1什么是package

1）package（包）是一个可以将相关对象存储在一起的PL/SQL结构。package包含了两个分离的组成部分：specification（package的声明，即包声明）和body（声明中的程序实现，即包体）。每个部分都单独被存储在数据字典中。包声明是一个操作接口，对应用来说是可见的。

2）包体是黑盒，对应用来说隐藏了实现细节。

11.2包的组成

将相关的若干程序单元组织到一块，用一个包来标识这个集合，包中可以包含以下的程序单元：

11.3包的优点

1）方便对存储过程和函数的组织：

①将相关的过程和函数组织在一起。②在一个用户环境中解决命名的冲突问题。

2）方便对存储过程和函数的管理：

①在不改变包的声明定义是可以改变包体的实现的。②限制过程、函数的依赖性。

③在包体未实现时，其他程序中可以调用包中的对象，对自己的程序进行编译，可以并行地对程序开发。

3）方便对存储过程和函数的安全性管理：

①整个包的访问权限只需一次性授权。②区分公用过程和私有过程。

4）改善性能：

①在包被首次调用时作为一个整体全部调入内存。②减少多次调用时磁盘I/O次数。

11.4 package声明的语法

create or replace package pkg_name

{is|as}

公共变量（variable）的定义｜公共类型（type）的定义｜

公共异常（Exception）的定义｜公共游标（cursor）的定义｜

函数说明｜过程说明

end;

11.5 package body声明的语法

create or replace package body pkg_name

{is|as}

函数实现;--调用一次执行一次

过程实现;--调用一次执行一次

begin

--初始化代码;--首次调用包中的任意对象则执行一次（只执行1次）

end;

11.6编译包和包体

alter package pkg1 compile;

alter package pkg1 compile body;

11.7案例

eg1:创建一个包并调用

create or replace package pkg_chang--创建package

is

type t_rec is record(m1 number,m2 varchar2(10));

v_rec t_rec;

procedure proc1;

function fun1(p_in number)return number;

end;

--创建package body

create or replace package body pkg_chang

is

procedure proc1--存储在数据库中的package包里

is

begin

dbms_output.put_line(v_rec.m1);

end;

function fun1(p_in number)return number

is

begin

return p_in;

end;

--begin

--v_rec.m1 :=100;

end;

--调用package

begin

pkg_chang.v_rec.m1 :=pkg_chang.fun1(10);

pkg_chang.proc1;

end;

eg2：创建一个包并调用

--创建package

create or replace package pkg_chang1

is

type t_rec is record(realname varchar2(30),age number);

v_account t_rec;

procedure p_account;

function f_account(p_id number)return t_rec;

end;

--创建package body

create or replace package body pkg_chang1

is

procedure p_account

is

begin

dbms_output.put_line('姓名：'||v_account.realname||' 年龄：'||v_account.age);

end;

function f_account(p_id number)return t_rec

is

--v_account t_rec;

begin

select real_name,round((sysdate-birthdate)/365) into v_account from account

where id=p_id;

return v_account;

end;

end;

--调用package

begin

pkg_chang1.v_account :=pkg_chang1.f_account(1005);

pkg_chang1.p_account;

end;

• 注意事项：

• to_char、nvl等常用函数都在系统提供的standard package包中，只不过可以省略包名，其他包都要写包名；包内部可不写包名，出了包要写。

• 包里的过程和函数也可以重载。

• 包声明变了，包体一定要变。

• 可以有包声明，而没有包体，但不能只有包体而没有包声明。

• 删除包，则包体也被删除了。

• 若package没有更新，只更新了package body，调用包的程序代码不需要修改，也不需要重新编译。

12、触发器trigger

12.1面临问题

1）在作DML操作时，不需要提供主键值，系统自动生成。

2）如何实现级联更新。

12.2 DML触发器的组成

12.3 DML触发器的类型

1）语句级触发器。

2）行级触发器。

3）行级触发器与语句级触发器的区别：触发的次数不同，如果DML语句只影响一行，则语句级与行级触发器效果一样；如果影响多行，则行级触发器触发的次数比语句级触发器触发的次数多。

12.4 DML触发器的触发顺序

根据触发的时间、类型不同，可以组合为四种DML触发器。

• 注意事项：如在update 表时分为5个执行区间：做update语句之前；修改每一条符合条件的记录之前；修改记录；修改每一条符合条件的记录之后；做完update语句之后；

12.5 DML行级触发器

for each row子句创建一个行级触发器，使其在受到触发事件影响的每一行上都被触发。

eg：语法

create [or replace] trigger trig_name {before|after} insert [or update…]

on tab_name

for each row [when restricting_condition]

PL/SQL block;

12.6 :OLD和:NEW

在行级触发器中，在列名前加上：old标识符表示该列变化前的值，加上：new标识符表示变化后的值（绑定变量）。

eg1：自动产生主键值

--创建表

create table test_chang(c1 number primary key,c2 number);

--产生序列号

create sequence s1_chang;

--创建触发器，以序列号作为c1列的值

create or replace trigger gen_pk_chang

before insert on test_chang

for each row

begin

select s1_chang.nextval into :new.c1

from dual;

end;

--只插入c2列的值，发现不报错了。原因：系统自动将序列号插入c1列

insert into test_chang(c2) values(1);

--把c1列的值也插入，发现数字10被序列号替换了，before insert for each row覆盖了:new的值

insert into test_chang values(10,5);

若不想被替换，则匿名块修改为：

if :new.c1 is null then

select s1_chang.nextval into :new.c1

from dual;

end if;

• 注意事项：：new和：old是记录类型。

• ：new和：old只能用于行级触发器，不能用于语句级触发器。

• ：new tabname%rowtype，绑定变量new和定义触发器的table的行记录结构相同。

• ：old tabname%rowtype，绑定变量old和定义触发器的table的行记录结构相同。

• after行级触发器不可以修改：new的值，但可以查看：new的值。

• insert into test_chang(c2) values(1); :new.c1=null :new.c2=1 --若没有触发器，则报错。原因：将null插入了主键列。

eg2：实现级联更新

--创建父子表

create table parent_chang(c1 number primary key,c2 number);

create table child_chang(c1 number primary key,c2 constraint child_c2_fk references parent_chang(c1));

--向父子表中插入数据

insert into parent_chang values(11,1);

insert into parent_chang values(12,2);

insert into child_chang values(21,11);

insert into child_chang values(22,12);

--若无触发器，更新c1列的值则报错。

update parent_chang set c1=1 where c1=11;

--若定义触发器

create or replace trigger cascade_update_chang

after update on parent_chang

for each row

begin

update child_chang set c2=:new.c1

where c2=:old.c1;

end;

--再次执行更新语句，则不报错，两表数据同时被更新

update parent_chang set c1=1 where c1=11;

• 注意事项：

• 在触发器里，缺省状态下，不能写提交和回滚的！

• 另一状态：Oracle自治事务，可写。

• 约束检查发生在触发器完成之后。

12.7触发器的重新编译

1）如果触发器内调用其他函数或过程，当他们被删除或修改后，触发器的状态被表示为无效。当DML语句激活一个无效触发器时，Oracle将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。

2）调用alter trigger语句重新编译已创建的触发器：

alter trigger [schema.] trig_name compile;

• 注意事项：查看触发器列表

desc user_triggers; select * from user_triggers;

12.8触发器的状态

1）有效状态（enable）：当触发事件发生时，处于有效状态的trigger将被触发。

2）无效状态（disable）：当触发事件发生时，处于无效状态的trigger将不会被触发。

3）trigger的两种状态可以相互转换，格式为：

alter trigger trig_name [disable|enable];

4）alter trigger语句一次只能改变一个触发器的状态，而alter table语句则一次能改变与指定表相关的所有触发器的使用状态。

alter table [schema.] tab_name {enable|disable} all triggers;

• 注意事项：drop table后：

• 表中的all constraints drop、表中的all index drop、表中的all trigger drop

• 无效的有：view、synonym、procedure、function、package

13、其他注意事项

13.1 PL/SQL的特点

1）结构化模块编程。2）良好的可移植性。3）良好的可维护性。4）提升系统性能

5）不便于向异构数据库移植应用程序。

13.2写PL/SQL的好处

client端可以不用写sql，人员要求低，安全，权限控制。

相对于C/S结构，代码存在数据库中，维护的成本低（JDBC的java程序相当于C/S结构）。

B/S结构：sql代码在as上（application server）。

能写静态sql，可以预先编译的，效率高。

JDBC只能写动态sql，程序在运行时，sql语句才编译。

13.3命名建议

b_绑定变量 v_普通变量 p_形参

13.4搞清楚如下内容

1. 过程参数in、out、in out。 2）过程，角色，权限的关系。

3）静态sql、动态sql、绑定变量。

13.5保证所有对象的状态都是valid

alter procedure procname compile;

alter package pkg1 compile;

alter package pkg1 compile body;

13.6 declare中都可声明什么

变量、类型、cursor、execption、过程、函数

13.7数据库对象

table、view、index、sequence、synonym、procedure、function、package、package body