1、存储过程和函数
存储过程和函数也是一种 pl/sql块，是存入数据库的 pl/sql块。但存储过程和函数不
同于已经介绍过的 pl/sql程序，我们通常把 pl/sql程序称为无名块，而存储过程和函数
是以命名的方式存储于数据库中的。和 pl/sql程序相比，存储过程有很多优点，具体归
纳如下：
a）存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优
点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调
用或修改代码。
b）存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储
过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用
函数。
c）存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，
用户编写的 pl/sql程序或其他存储过程都可以调用它(但存储过程和函数不能调用 pl/sql
程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，
可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号
返回雇员的姓名，可以设计成存储函数。
d）像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递
也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通
过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名
的调用返回函数值。
存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。
创建存储过程，需要有 create procedure或 create any procedure的系统权限。该权限可由
系统管理员授予。创建一个存储过程的基本语句如下：
create [or replace] procedure存储过程名[(参数[in|out|in out]数据类型…)]
{as|is}
[说明部分]
begin
可执行部分
[exception
错误处理部分]
end [过程名];
其中：可选关键字 or replace表示如果存储过程已经存在，则用新的存储过程覆
盖，通常用于存储过程的重建。参数部分用于定义多个参数(如果没有参数，就可以省
略)。参数有三种形式：in、out和 in out。如果没有指明参数的形式，则默认为 in，关
键字 as也可以写成 is，后跟过程的说明部分，可以在此定义过程的局部变量，编写存
储过程可以使用任何文本编辑器或直接在 sql*plus环境下进行，编写好的存储过程必须
要在 sql*plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被
存入数据库。编译成功的存储过程就可以在 oracle环境下进行调用了。一个存储过程
在不需要时可以删除。删除存储过程的创建者或者拥有 drop any procedure系统权限的
人。删除存储过程的语法如下：
语法格式：drop procedure存储过程名；
如果要重新编译一个存储过程，则只能是过程的创建者或者拥有 alter any procedure
系统权限的人。语法如下：
alter procedure存储过程名 compile；
执行(或调用)存储过程的人是过程的创建者或是拥有 execute any procedure系统权
限的人或是被拥有者授予 execute权限的人。执行的方法如下：
方法 1：
execute模式名.存储过程名[(参数…)];
方法 2：
begin
模式名.存储过程名[(参数…)];
end;
【训练 1】创建一个显示雇员总人数的存储过程。
步骤 1：登录 scott账户(或学生个人账户)。
步骤 2：在 sql*plus输入区中，输入以下存储过程：
create or replace procedure dyk_count
as
v_total1 number(10);
v_total2 number(10);
v_total3 number(10);
begin
select count(*) into v_total1 from dyk_table1;
dbms_output.put_line(‘雇员总人数为：‘||v_total1);
delete dyk_table1 where owner In (‘XDB’,‘CTSSYS’,‘OUTLN’,‘WMSYS’);
select count(*) into v_total2 from dyk_table1;
dbms_output.put_line(‘剩余总人数为：‘||v_total2);
insert into dyk_table1 nologging
select * from dba_objects;
select count(*) into v_total3 from dyk_table1;
dbms_output.put_line(‘增加后的总人数为：‘||v_total3);
end;
步骤 3：按“执行”按钮进行编译。
如果存在错误，就会显示:警告:创建的过程带有编译错误。
如果存在错误，对脚本进行修改，直到没有错误产生。
如果编译结果正确，将显示：（sql代码）
过程已创建。
步骤 4：调用存储过程，在输入区中输入以下语句并执行：
sql代码
set serverout on /从 sql提示符下输出存储过程变量值
execute dyk_count;
显示结果为：
雇员总人数为：337386
剩余总人数为：336456
增加后的总人数为：386254
说明：在该训练中，v_total变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。
注意：在 sql*plus中输入存储过程，按“执行”按钮是进行编译，不是执行存储过程。如果在存储
过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限。一个存储过程一
旦编译成功，就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行
该过程的权限。
存储过程没有参数，在调用时，直接写过程名即可。
【训练 2】在 pl/sql程序中调用存储过程。
步骤 1：登录 scott账户。
步骤 2：授权 student账户使用该存储过程，即在 sql*plus输入区中，输入以下的命令：
sql代码
grant execute on emp_count to student
sql代码
授权成功。
步骤 3：登录 student账户，在 sql*plus输入区中输入以下程序：
sql代码
set serveroutput on
begin
scott.emp_count;
end;
步骤 4：执行以上程序，结果为：
sql代码
雇员总人数为：14
pl/sql过程已成功完成。
说明：在本例中，存储过程是由 scott账户创建的，studen账户获得 scott账户的授权后，才能调
用该存储过程。
注意：在程序中调用存储过程，使用了第二种语法。
【训练 3】
编写显示雇员信息的存储过程 emp_list，并引用 emp_count存储过程。
步骤 1：在 sql*plus输入区中输入并编译以下存储过程：
sql代码
create or replace procedure emp_list
as
cursor emp_cursor is
select empno,ename from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line(emp_record.empno||emp_record.ename);
end loop;
emp_count;
end;
过程已创建。
步骤 2：调用存储过程，在输入区中输入以下语句并执行：
sql代码
execute emp_list
execute emp_list
显示结果为：
sql代码
7369smith
7499allen
7521ward
7566jones
执行结果：
雇员总人数为：14
pl/sql过程已成功完成。
说明：以上的 emp_list存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已
经成功编译的存储过程 emp_count，用来附加显示雇员总人数。通过 execute命令来执行 emp_list
存储过程。
【练习 1】编写显示部门信息的存储过程 dept_list，要求统计出部门个数。
参数传递：参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以
大大增加存储过程的灵活性和通用性，参数的类型有三种，如下所示。
sql代码
in定义一个输入参数变量，用于传递参数给存储过程
out定义一个输出参数变量，用于从存储过程获取数据
in out定义一个输入、输出参数变量，兼有以上两者的功能
in定义一个输入参数变量，用于传递参数给存储过程
out定义一个输出参数变量，用于从存储过程获取数据
in out定义一个输入、输出参数变量，兼有以上两者的功能
参数的定义形式和作用如下：
参数名 in数据类型 default值；
定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可
以是常量、有值变量或表达式等。default关键字为可选项，用来设定参数的默认值。如果在调用
存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但
不能对其进行赋值。
参数名 out数据类型；
定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。
在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，
参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。
参数名 in out数据类型 default值；
定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能
是一个变量，而不能是常量或表达式。default关键字为可选项，用来设定参数的默认值。在存储
过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中
必须给变量至少赋值一次。
如果省略 in、out或 in out，则默认模式是 in。
【训练 1】编写给雇员增加工资的存储过程 change_salary，通过 in类型的参数传递要增加工资的
雇员编号和增加的工资额。
步骤 1：登录 scott账户。
步骤 2：在 sql*plus输入区中输入以下存储过程并执行：
sql代码
create or replace procedure change_salary(p_empno in number default 7788,p_raise number default
10)
as
v_ename varchar2(10);
v_sal number(5);
begin
select ename,sal into v_ename,v_sal from emp where empno=p_empno;
update emp set sal=sal+p_raise where empno=p_empno;
dbms_output.put_line(‘雇员‘||v_ename||’的工资被改为‘||to_char(v_sal+p_raise));
commit;
exception
when others then
dbms_output.put_line(‘发生错误，修改失败！‘);
rollback;
end;
过程已创建。
步骤 3：调用存储过程，在输入区中输入以下语句并执行：
sql代码
execute change_salary(7788,80)
显示结果为：
sql代码
雇员 scott的工资被改为 3080
说明：从执行结果可以看到，雇员 scott的工资已由原来的 3000改为 3080。
参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可
以采用以下调用方法。如上例，执行语句可以改为：
execute change_salary(p_raise=>80,p_empno=>7788);
可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值，=>运算符左侧是参
数名，右侧是参数表达式，这种赋值方法的意义较清楚。
【练习 1】创建插入雇员的存储过程 insert_emp，并将雇员编号等作为参数。
在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了。
【训练 2】
调用存储过程 change_salary，不传递参数，使用默认参数值。
在 sql*plus输入区中输入以下命令并执行：
sql代码
execute change_salary
显示结果为：
sql代码
雇员 scott的工资被改为 3090
说明：在存储过程的调用中没有传递参数，而是采用了默认值 7788和 10，即默认雇员号为 7788，
增加的工资为 10。
【训练 3】
使用 out类型的参数返回存储过程的结果。
步骤 1：登录 scott账户。
步骤 2：在 sql*plus输入区中输入并编译以下存储过程：
sql代码
create or replace procedure emp_count(p_total out number)
as
begin
select count(*) into p_total from emp;
end;
执行结果为：
sql代码
过程已创建。
步骤 3：输入以下程序并执行：
sql代码
declare
v_empcount number;
begin
emp_count(v_empcount);
dbms_output.put_line(‘雇员总人数为：‘||v_empcount);
end;
显示结果为：
sql代码
雇员总人数为：14
pl/sql过程已成功完成。
说明：在存储过程中定义了 out类型的参数 p_total，在主程序调用该存储过程时，传递了参数
v_empcount。在存储过程中的 select…into…语句中对 p_total进行赋值，赋值结果由 v_empcount
变量带回给主程序并显示。
以上程序要覆盖同名的 emp_count存储过程，如果不使用 or replace选项，就会出现以下错误：
sql代码
error位于第 1行:
ora-00955:名称已由现有对象使用。
【练习 2】创建存储过程，使用 out类型参数获得雇员经理名。
【训练 4】
使用 in out类型的参数，给电话号码增加区码。
步骤 1：登录 scott账户。
步骤 2：在 sql*plus输入区中输入并编译以下存储过程：
sql代码
create or replace procedure add_region(p_hpone_num in out varchar2)
as
begin
p_hpone_num:=’0755-‘||p_hpone_num;
end;
、
执行结果为：
过程已创建。
步骤 3：输入以下程序并执行：
sql代码
set serveroutput on
declare
v_phone_num varchar2(15);
begin
v_phone_num:=’26731092′;
add_region(v_phone_num);
dbms_output.put_line(‘新的电话号码：‘||v_phone_num);
end;
显示结果为：
sql代码
新的电话号码：0755-26731092
pl/sql过程已成功完成。
说明：变量 v_hpone_num既用来向存储过程传递旧电话号码，也用来向主程序返回新号码。新的
号码在原来基础上增加了区号 0755和–。
创建和删除存储函数
创建函数，需要有 create procedure或 create any procedure的系统权限。该权限可由系统管理员授
予。创建存储函数的语法和创建存储过程的类似，即
create [or replace] function函数名[(参数[in]数据类型…)]
return数据类型
{as|is}
[说明部分]
begin
可执行部分
return (表达式)
[exception
错误处理部分]
end [函数名];
其中，参数是可选的，但只能是 in类型(in关键字可以省略)。
在定义部分的 return数据类型，用来表示函数的数据类型，也就是返回值的类型，此部分不可省
略。在可执行部分的 return(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说
明的函数返回值的数据类型一致。在函数的执行部分可以有多个 return语句，但只有一个 return
语句会被执行，一旦执行了 return语句，则函数结束并返回调用环境。
一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有 drop any procedure
系统权限的人。其语法如下：
drop function函数名；
重新编译一个存储函数时，编译的人应是函数的创建者或者拥有 alter any procedure系统权限
的人。重新编译一个存储函数的语法如下：
alter procedure函数名 compile；
函数的调用者应是函数的创建者或拥有 execute any procedure系统权限的人，或是被函数的拥
有者授予了函数执行权限的账户。函数的引用和存储过程不同，函数要出现在程序体中，可以参加
表达式的运算或单独出现在表达式中，其形式如下：
变量名:=函数名(…)
【训练 1】
创建一个通过雇员编号返回雇员名称的函数 get_emp_name。
步骤 1：登录 scott账户。
步骤 2：在 sql*plus输入区中输入以下存储函数并编译：
sql代码
create or replace function get_emp_name(p_empno number default 7788)
return varchar2
as
v_ename varchar2(10);
begin
elect ename into v_ename from emp where empno=p_empno;
return(v_ename);
exception
when no_data_found then
dbms_output.put_line(‘没有该编号雇员‘);
return (null);
when too_many_rows then
dbms_output.put_line(‘有重复雇员编号！‘);
return (null);
when others then
dbms_output.put_line(‘发生其他错误！‘);
return (null);
end;
步骤 3：调用该存储函数，输入并执行以下程序：
sql代码
begin
dbms_output.put_line(‘雇员 7369的名称是：‘|| get_emp_name(7369));
dbms_output.put_line(‘雇员 7839的名称是：‘|| get_emp_name(7839));
end;
显示结果为：
sql代码
雇员 7369的名称是：smith
雇员 7839的名称是：king
pl/sql过程已成功完成。
说明：函数的调用直接出现在程序的 dbms_output.put_line语句中，作为字符串表达式的一部分。
如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息。试修改雇员编号，重新运
行调用部分。
【练习 1】创建一个通过部门编号返回部门名称的存储函数 get_dept_name。
【练习 2】将函数的执行权限授予 student账户，然后登录 student账户调用。
存储过程和函数的查看可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询
当前用户的存储过程或函数的源代码，可以通过对
user_source数据字典视图的查询得到。user_source的结构如下：
sql代码
describe user_source
结果为：
sql代码
名称
是否为空?类型
————————————————————- ————- ———————–
name varchar2(30)
type varchar2(12)
line number
text varchar2(4000)
说明：里面按行存放着过程或函数的脚本，name是过程或函数名，type代表类型(procedure或
function)，line是行号，text为脚本。
【训练 1】
查询过程 emp_count的脚本。
在 sql*plus中输入并执行如下查询：
sql代码
select text from user_source where name=’emp_count’;
结果为：
sql代码
text
——————————————————————————–
procedure emp_count(p_total out number)
as
begin
select count(*) into p_total from emp;
end;
【训练 2】
查询过程 get_emp_name的参数。
在 sql*plus中输入并执行如下查询：
sql代码
describe get_emp_name
结果为：
sql代码
function get_emp_name returns varchar2
参数名称
类型
输入/输出默认值？
——– ——- ——————–
p_empno number(4) in default
【训练 3】
在发生编译错误时，显示错误。
sql代码
show errors
以下是一段编译错误显示：
sql代码
line/col error
———- ——————- ————–
4/2 pl/sql: sql statement ignored
4/36 pls-00201:必须说明标识符 ’empp’
说明：查询一个存储过程或函数是否是有效状态(即编译成功)，可以使用数据字典 user_objects的
status列。
【训练 4】查询 emp_list存储过程是否可用：
sql代码
select status from user_objects where object_name=’emp_list’;
结果为：
sql代码
status
————
valid
说明：valid表示该存储过程有效(即通过编译)，invalid表示存储过程无效或需要重新编译。当 oracle
调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成 valid并
执行，否则给出错误信息。当一个存储过程编译成功，状态变为 valid，会不会在某些情况下变成
invalid。结论是完全可能的。比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过
程就会变成无效 invalid。所以要注意存储过程和函数对其他对象的依赖关系。
如果要检查存储过程或函数的依赖性，可以通过查询数据字典 user_denpendencies来确定，该表结
构如下：
sql代码
describe user_dependencies;
结果：
sql代码
名称
是否为空
类型
—– ———- ————
name not null varchar2(30)
type varchar2(12)
referenced_owner varchar2(30)
referenced_name varchar2(64)
referenced_type varchar2(12)
referenced_link_name varchar2(128)
schemaid number
dependency_type varchar2(4)
说明： name为实体名， type为实体类型， referenced_owner为涉及到的实体拥有者账户，
referenced_name为涉及到的实体名，referenced_type为涉及到的实体类型。
【训练 5】
查询 emp_list存储过程的依赖性。
sql代码
select referenced_name,referenced_type from user_dependencies where name=’emp_list’;
执行结果：
sql代码
referenced_name referenced_type
—————————— —————————
standard package
sys_stub_for_purity_analysis package
dbms_output package
dbms_output synonym
dbms_output non-existent
emp table
emp_count procedure
说明：可以看出存储过程 emp_list依赖一些系统包、emp表和 emp_count存储过程。如果删除
了 emp表或 emp_count存储过程，emp_list将变成无效。还有一种情况需要我们注意：如果一个
用户 a被授予执行属于用户 b的一个存储过程的权限，在用户 b的存储过程中，访问到用户 c的表，
用户 b被授予访问用户 c的表的权限，但用户 a没有被授予访问用户 c表的权限，那么用户 a调用
用户 b的存储过程是失败的还是成功的呢？答案是成功的。如果读者有兴趣，不妨进行一下实际测
试。
2、程序包
包的概念和组成
包是用来存储相关程序结构的对象，它存储于数据字典中。包由两个分离的部分组成：包头(package)
和包体(package body)。包头是包的说明部分，是对外的操作接口，对应用是可见的；包体是包的
代码和实现部分，对应用来说是不可见的黑盒。
包中可以包含的程序结构如下所示。
sql代码
过程(procudure)
函数(function)
变量(variable)
常量(constant)
带参数的命名的程序模块
带参数、具有返回值的命名的程序模块
存储变化的量的存储单元
存储不变的量的存储单元
游标(cursor)用户定义的数据操作缓存区，在可执行部分使用
类型(type)
用户定义的新的结构类型
异常(exception)
在标准包中定义或由用户自定义，用于处理程序错误
说明部分可以出现在包的三个不同的部分：出现在包头中的称为公有元素，出现在包体中的称
为私有元素，出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同，如下所示。
sql代码
公有元素(public)
过程有效
在包头中说明，在包体中具体定义在包外可见并可以访问，对整个应用的全
私有元素(private)
在包体的说明部分说明
只能被包内部的其他部分访问
局部变量(local)在过程或函数的说明部分说明
只能在定义变量的过程或函数中使用
在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体
中的说明一致。
包有以下优点：
*包可以方便地将存储过程和函数组织到一起，每个包又是相互独立的。在不同的包中，过程、函
数都可以重名，这解决了在同一个用户环境中命名的冲突问题。
*包增强了对存储过程和函数的安全管理，对整个包的访问权只需一次授予。
*在同一个会话中，公用变量的值将被保留，直到会话结束。
*区分了公有过程和私有过程，包体的私有过程增加了过程和函数的保密性。
*包在被首次调用时，就作为一个整体被全部调入内存，减少了多次访问过程或函数的 i/o次数。
创建包和包体
包由包头和包体两部分组成，包的创建应该先创建包头部分，然后创建包体部分。创建、删除和编
译包的权限同创建、删除和编译存储过程的权限相同。
创建包头的简要语句如下：
create [or replace] package包名
{is|as}
公有变量定义
公有类型定义
公有游标定义
公有异常定义
函数说明
过程说明
end;
创建包体的简要语法如下：
create [or replace] package body包名
{is|as}
私有变量定义
私有类型定义
私有游标定义
私有异常定义
函数定义
过程定义
end;
包的其他操作命令包括：
删除包头：
drop package包头名
删除包体：
drop package body包体名
重新编译包头：
alter package包名 compile package
重新编译包体：
alter package包名 compile package body
在包头中说明的对象可以在包外调用，调用的方法和调用单独的过程或函数的方法基本相同，
惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意，不同的会话
将单独对包的公用变量进行初始化，所以不同的会话对包的调用属于不同的应用。
系统包 oracle预定义了很多标准的系统包，这些包可以在应用中直接使用，比如在训练中我们使
用的 dbms_output包，就是系统包。put_line是该包的一个函数。常用系统包下所示。
sql代码
dbms_output在 sql*plus环境下输出信息
dbms_ddl
dbms_session
编译过程函数和包
改变用户的会话，初始化包等
dbms_transaction
控制数据库事务
dbms_mail
dbms_lock
dbms_alert
dbms_pipe
dbms_job
dbms_lob
dbms_sql
连接 oracle*mail
进行复杂的锁机制管理
识别数据库事件告警
通过管道在会话间传递信息
管理 oracle的作业
操纵大对象
执行动态 sql语句
dbms_output在 sql*plus环境下输出信息
dbms_ddl
编译过程函数和包
dbms_session改变用户的会话，初始化包等
dbms_transaction控制数据库事务
dbms_mail
dbms_lock
dbms_alert
dbms_pipe
连接 oracle*mail
进行复杂的锁机制管理
识别数据库事件告警
通过管道在会话间传递信息
dbms_job
dbms_lob
dbms_sql
管理 oracle的作业
操纵大对象
执行动态 sql语句
包的应用
在 sql*plus环境下，包和包体可以分别编译，也可以一起编译。如果分别编译，则要先编译包头，
后编译包体。如果在一起编译，则包头写在前，包体在后，中间用“/”分隔。
可以将已经存在的存储过程或函数添加到包中，方法是去掉过程或函数创建语句的 create or
replace部分，将存储过程或函数复制到包体中，然后重新编译即可。
如果需要将私有过程或函数变成共有过程或函数的话，将过程或函数说明部分复制到包头说明
部分，然后重新编译就可以了。
【训练 1】创建管理雇员信息的包 employe，它具有从 emp表获得雇员信息，修改雇员名称，修
改雇员工资和写回 emp表的功能。
步骤 1：登录 scott账户，输入以下代码并编译：
sql代码
create or replace package employe —包头部分
is
procedure show_detail;
procedure get_employe(p_empno number);
procedure save_employe;
procedure change_name(p_newname varchar2);
procedure change_sal(p_newsal number);
end employe;
/
create or replace package body employe —包体部分
is
employe emp%rowtype;
————–显示雇员信息 —————
procedure show_detail
as
begin
dbms_output.put_line(‘—–雇员信息 —–’);
dbms_output.put_line(‘雇员编号：‘||employe.empno);
dbms_output.put_line(‘雇员名称：‘||employe.ename);
dbms_output.put_line(‘雇员职务：‘||employe.job);
dbms_output.put_line(‘雇员工资：‘||employe.sal);
dbms_output.put_line(‘部门编号：‘||employe.deptno);
end show_detail;
—————–从 emp表取得一个雇员 ——————–
procedure get_employe(p_empno number)
as
begin
select * into employe from emp where empno=p_empno;
dbms_output.put_line(‘获取雇员‘||employe.ename||’信息成功‘);
exception
when others then
dbms_output.put_line(‘获取雇员信息发生错误！‘);
end get_employe;
———————-保存雇员到 emp表 ————————–
procedure save_employe
as
begin
update emp set ename=employe.ename, sal=employe.sal where empno=
employe.empno;
dbms_output.put_line(‘雇员信息保存完成！‘);
end save_employe;
—————————-修改雇员名称 ——————————
procedure change_name(p_newname varchar2)
as
begin
employe.ename:=p_newname;
dbms_output.put_line(‘修改名称完成！‘);
end change_name;
—————————-修改雇员工资 ————————–
procedure change_sal(p_newsal number)
as
begin
employe.sal:=p_newsal;
dbms_output.put_line(‘修改工资完成！‘);
end change_sal;
end employe;
create or replace package employe —包头部分
is
procedure show_detail;
procedure get_employe(p_empno number);
procedure save_employe;
procedure change_name(p_newname varchar2);
procedure change_sal(p_newsal number);
end employe;
/
create or replace package body employe —包体部分
is
employe emp%rowtype;
————–显示雇员信息 —————
procedure show_detail
as
begin
dbms_output.put_line(‘—–雇员信息 —–’);
dbms_output.put_line(‘雇员编号：‘||employe.empno);
dbms_output.put_line(‘雇员名称：‘||employe.ename);
dbms_output.put_line(‘雇员职务：‘||employe.job);
dbms_output.put_line(‘雇员工资：‘||employe.sal);
dbms_output.put_line(‘部门编号：‘||employe.deptno);
end show_detail;
—————–从 emp表取得一个雇员 ——————–
procedure get_employe(p_empno number)
as
begin
select * into employe from emp where empno=p_empno;
dbms_output.put_line(‘获取雇员‘||employe.ename||’信息成功‘);
exception
when others then
dbms_output.put_line(‘获取雇员信息发生错误！‘);
end get_employe;
———————-保存雇员到 emp表 ————————–
procedure save_employe
as
begin
update emp set ename=employe.ename, sal=employe.sal where empno=
employe.empno;
dbms_output.put_line(‘雇员信息保存完成！‘);
end save_employe;
—————————-修改雇员名称 ——————————
procedure change_name(p_newname varchar2)
as
begin
employe.ename:=p_newname;
dbms_output.put_line(‘修改名称完成！‘);
end change_name;
—————————-修改雇员工资 ————————–
procedure change_sal(p_newsal number)
as
begin
employe.sal:=p_newsal;
dbms_output.put_line(‘修改工资完成！‘);
end change_sal;
end employe;
步骤 2：获取雇员 7788的信息:
sql代码
set serveroutput on
execute employe.get_employe(7788);
结果为：
sql代码
获取雇员 scott信息成功
pl/sql过程已成功完成。
步骤 3：显示雇员信息：
sql代码
execute employe.show_detail;
结果为：
sql代码
——————雇员信息 ——————
雇员编号：7788
雇员名称：scott
雇员职务：analyst
雇员工资：3000
部门编号：20
pl/sql过程已成功完成。
步骤 4：修改雇员工资：
sql代码
execute employe.change_sal(3800);
结果为：
sql代码
修改工资完成！
pl/sql过程已成功完成。
步骤 5：将修改的雇员信息存入 emp表
sql代码
execute employe.save_employe;
结果为：
sql代码
雇员信息保存完成！
pl/sql过程已成功完成。
说明：该包完成将 emp表中的某个雇员的信息取入内存记录变量，在记录变量中进行修改编辑，
在确认显示信息正确后写回 emp表的功能。记录变量 employe用来存储取得的雇员信息，定义为
私有变量，只能被包的内部模块访问。
【练习 1】为包增加修改雇员职务和部门编号的功能。
阶段训练
下面的训练通过定义和创建完整的包 emp_pk并综合运用本章的知识，完成对雇员表的插入、删除
等功能，包中的主要元素解释如下所示。
sql代码
程序结构
类型说明
v_emp_count公有变量
跟踪雇员的总人数变化，插入、删除雇员的同时修改该变量的值
init
公有过程
对包进行初始化，初始化雇员人数和工资修改的上、下限
list_emp
insert_emp
公有过程
公有过程
显示雇员列表
通过编号插入新雇员
delete_emp公有过程
通过编号删除雇员
change_emp_sal
公有过程
通过编号修改雇员工资
v_message
c_max_sal
c_min_sal
私有变量
私有变量
私有变量
存放准备输出的信息
对工资修改的上限
对工资修改的下限
show_message
私有过程
显示私有变量 v_message中的信息
exist_emp
私有函数
判断某个编号的雇员是否存在，该函数被 insert_emp、delete_emp和
change_emp_sal等过程调用
【训练 1】
完整的雇员包 emp_pk的创建和应用。
步骤 1：在 sql*plus中登录 scott账户，输入以下包头和包体部分，按“执行”按钮编译：
create or replace package emp_pk
—包头部分
is
v_emp_count number(5);
—雇员人数
procedure init(p_max number,p_min number); —初始化
procedure list_emp;
—显示雇员列表
procedure insert_emp(p_empno number,p_enamevarchar2,p_job varchar2,
p_sal number);
—插入雇员
procedure delete_emp(p_empno number); —删除雇员
procedure change_emp_sal(p_empno number,p_sal number);
—修改雇员工资
end emp_pk;
/create or replace package body emp_pk
—包体部分
is
v_message varchar2(50); —显示信息
v_max_sal number(7); —工资上限
v_min_sal number(7); —工资下限
function exist_emp(p_empno number) return boolean; —判断雇员是否存在函数
procedure show_message; —显示信息过程
——————————-初始化过程 —————————-
procedure init(p_max number,p_min number)
is
begin
select count(*) into v_emp_count from emp;
v_max_sal:=p_max;
v_min_sal:=p_min;
v_message:=’初始化过程已经完成！‘;
show_message;
end init;
—————————-显示雇员列表过程 ———————
procedure list_emp
is
begin
dbms_output.put_line(‘姓名
职务
工资‘);
for emp_rec in (select * from emp)
loop
dbms_output.put_line(rpad(emp_rec.ename,10,”)||rpad(emp_rec.job,10,’
‘)||to_char(emp_rec.sal));
end loop;
dbms_output.put_line(‘雇员总人数‘||v_emp_count);
end list_emp;
—————————–插入雇员过程 —————————–
procedureinsert_emp(p_empno
number,p_enamevarchar2,p_job varchar2,p_sal number)
is
begin
if not exist_emp(p_empno) then
insert into emp(empno,ename,job,sal)
commit;
v_emp_count:=v_emp_count+1;
v_message:=’雇员‘||p_empno||’已插入!’;
else
v_message:=’雇员‘||p_empno||’已存在,不能插入!’;
end if;
show_message;
exception
when others then
v_message:=’雇员‘||p_empno||’插入失败!’;
show_message;
end insert_emp;
—————————删除雇员过程 ——————–
procedure delete_emp(p_empno number)
is
begin
if exist_emp(p_empno) then
delete from emp where empno=p_empno;
commit;
v_emp_count:=v_emp_count-1;
v_message:=’雇员‘||p_empno||’已删除!’;
else
v_message:=’雇员‘||p_empno||’不存在，不能删除!’;
values(p_empno,p_ename,p_job,p_sal);
end if;
show_message;
exception
when others then
v_message:=’雇员‘||p_empno||’删除失败!’;
show_message;
end delete_emp;
—————————————修改雇员工资过程 ————————————
procedure change_emp_sal(p_empno number,p_sal number)
is
begin
if (p_sal>v_max_sal or p_sal<v_min_sal) then
v_message:=’工资超出修改范围!’;
elsif not exist_emp(p_empno) then
v_message:=’雇员‘||p_empno||’不存在，不能修改工资!’;
else
update emp set sal=p_sal where empno=p_empno;
commit;
v_message:=’雇员‘||p_empno||’工资已经修改!’;
end if;
show_message;
exception
when others then
v_message:=’雇员‘||p_empno||’工资修改失败!’;
show_message;
end change_emp_sal;
—————————-显示信息过程 —————————-
procedure show_message
is
begin
dbms_output.put_line(‘提示信息：‘||v_message);
end show_message;
————————判断雇员是否存在函数 ——————-
function exist_emp(p_empno number)
return boolean
is
v_num number; —局部变量
begin
select count(*) into v_num from emp where empno=p_empno;
if v_num=1 then
return true;
else
return false;
end if;
end exist_emp;
—————————–
end emp_pk;
结果为：
sql代码
程序包已创建。
程序包主体已创建。
步骤 2：初始化包：
sql代码
set serveroutput on
execute emp_pk.init(6000,600);
显示为：
sql代码
提示信息：初始化过程已经完成！
步骤 3：显示雇员列表：
sql代码
execute emp_pk.list_emp;
显示为：
sql代码
姓名
职务
工资
smith clerk 1560
allen salesman 1936
ward salesman 1830
jones manager 2975
…
雇员总人数：14
步骤 4：插入一个新记录：
sql代码
execute emp_pk.insert_emp(8001,’小王‘,’clerk’,1000);
显示结果为：
sql代码
提示信息：雇员 8001已插入!
pl/sql过程已成功完成。
步骤 5：通过全局变量 v_emp_count查看雇员人数：
sql代码
begin
dbms_output.put_line(emp_pk.v_emp_count);
end;
显示结果为：
sql代码
15
pl/sql过程已成功完成。
步骤 6：删除新插入记录：
sql代码
execute emp_pk.delete_emp(8001);
显示结果为：
sql代码
提示信息：雇员 8001已删除!
pl/sql过程已成功完成。
再次删除该雇员：
sql代码
execute emp_pk.delete_emp(8001);
结果为：
sql代码
提示信息：雇员 8001不存在，不能删除!
步骤 7：修改雇员工资：
sql代码
execute emp_pk.change_emp_sal(7788,8000);
显示结果为：
sql代码
提示信息：工资超出修改范围!
pl/sql过程已成功完成。
步骤 8：授权其他用户调用包：
如果是另外一个用户要使用该包，必须由包的所有者授权，下面授予 studen账户对该包的使用
权：
sql代码
grant execute on emp_pk to student;
每一个新的会话要为包中的公用变量开辟新的存储空间,所以需要重新执行初始化过程。两个会
话的进程互不影响。
步骤 9：其他用户调用包。
启动另外一个 sql*plus，登录 student账户，执行以下过程：
sql代码
set serveroutput on
execute scott.emp_pk. emp_pk.init(5000,700);
结果为：
sql代码
提示信息：初始化过程已经完成！
pl/sql过程已成功完成。
说明：在初始化中设置雇员的总人数和修改工资的上、下限，初始化后 v_emp_count为 14人，
插入雇员后 v_emp_count为 15人。v_emp_count为公有变量，所以可以在外部程序中使用
dbms_output.put_line输出，引用时用 emp_pk.v_emp_count的形式，说明所属的包。
而私有变量 v_max_sal和 v_min_sal不能被外部访问，只能通过内部过程来修改。
同样，exist_emp和 show_message也是私有过程，也只能在过程体内被其他模块引用。
注意：在最后一个步骤中，因为 student模式调用了 scott模式的包，所以包名前要增加
模式名 scott。不同的会话对包的调用属于不同的应用，所以需要重新进行初始化。
3、操作练习
1)如果存储过程的参数类型为 out，那么调用时传递的参数应该为：
a．常量 b．表达式
c．变量 d．都可以
2)下列有关存储过程的特点说法错误的是：
a．存储过程不能将值传回调用的主程序
b．存储过程是一个命名的模块
c．编译的存储过程存放在数据库中
d．一个存储过程可以调用另一个存储过程
3)下列有关函数的特点说法错误的是：
a．函数必须定义返回类型
b．函数参数的类型只能是 in
c．在函数体内可以多次使用 return语句
d．函数的调用应使用 execute命令
4)包中不能包含的元素为:
a．存储过程 b．存储函数
c．游标
d．表
5)下列有关包的使用说法错误的是：
a．在不同的包内模块可以重名
b．包的私有过程不能被外部程序调用
c．包体中的过程和函数必须在包头部分说明
d．必须先创建包头，然后创建包体
4、触发器
触发器:是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用
他们，其功能如下：
1）允许/限制对表的修改
2）自动生成派生列，比如自增字段
3）强制数据一致性
4）提供审计和日志记录
5）防止无效的事务处理
6）启用复杂的业务逻辑
开始
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
触发器的组成部分：的
1）触发器名称
2）触发语句
3）触发器限制
4）触发操作
触发器名称：
create trigger biufer_employees_department_id
命名习惯：
biufer（before insert update for each row）
employees表名
department_id列名
触发语句
比如：
表或视图上的 dml语句
ddl语句
数据库关闭或启动,startup shutdown等等
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
说明：
1）无论是否规定了 department_id，对 employees表进行 insert的时候
2）对 employees表的 department_id列进行 update的时候
3）触发器限制
when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列 department_id不等于 80的时候，触发器就会执行。
其中的 new_value是代表更新之后的值。
触发操作：
是触发器的主体
begin
:new_value.commission_pct :=0;
end;
主体很简单，就是将更新后的 commission_pct列置为 0
触发：
insert into employees(employee_id,
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
values( 12345,’chen’,’donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);
select commission_pct from employees where employee_id=12345;
触发器不会通知用户，便改变了用户的输入值。
触发器类型：
1）语句触发器
2）行触发器
3） instead of触发器
4）系统条件触发器
5）用户事件触发器
5、Job创建和操作
1)设置初始化参数 job_queue_processes
alter system set job_queue_processes=n;（n>0）job_queue_processes最大值为 1000
查看 job queue后台进程
select name,description from v$bgprocess;
2)dbms_job package用法介绍
包含以下子过程：
broken()过程;change()过程;interval()过程;isubmit()过程;next_date()过程;
remove()过程;run()过程;submit()过程;user_export()过程;what()过程;
(1)broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。
这个过程有三个参数：job、broken与 next_date。
procedure broken (job in binary_integer,broken in boolean,next_date in date :=sysdate)
job参数是工作号，它在问题中唯一标识工作。
broken参数指示此工作是否将标记为破——true说明此工作将标记为破，而 flase说明此工作
将标记为未破。
next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。
job如果由于某种原因未能成功之行，oracle将重试 16次后，还未能成功执行，将被标记为
broken重新启动状态为 broken的 job，有如下两种方式;
a、利用 dbms_job.run()立即执行该 job
begin
dbms_job.run(:jobno)该 jobno为 submit过程提交时返回的 job number
end;
/
b、利用 dbms_job.broken()重新将 broken标记为 false
begin
dbms_job.broken (:job,false,next_date)
end;
/
(2) change()过程用来改变指定工作的设置。
这个过程有四个参数：job、what、next_date与 interval。
procedure change (job in binary_integer,
what in varchar2,
next_date in date,
interval in varchar2)
此 job参数是一个整数值，它唯一标识此工作。
what参数是由此工作运行的一块 pl/sql代码块。
next_date参数指示何时此工作将被执行。
interval参数指示一个工作重执行的频度.
(3)interval()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数：job与
interval。
procedure interval (job in binary_integer,interval in varchar2)
job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。
(4) isubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：job、what、next_date、
interval与 no_parse。
procedure isubmit (job in binary_ineger,
what in varchar2,
next_date in date,
interval in varchar2,
no_parse in booean:=false)
这个过程与 submit()过程的唯一区别在于此 job参数作为 in型参数传递且包括一个由开发者提
供的工作号。如果提供的工作号已被使用，将产生一个错误。
(5)next_date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job与 next_date。
procedure next_date(job in binary_ineger,next_date in date)
job标识一个已存在的工作。next_date参数指示了此工作应被执行的日期与时间。
(6)remove()过程来删除一个已计划运行的工作。这个过程接收一个参数：
procedure remove(job in
binary_ineger);
job参数唯一地标识一个工作。这个参数的值是由为此工作调用 submit()过程返回的 job参数
的值。已正在运行的工作不能由调用过程序删除。
(7)run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：
procedure run(job in binary_ineger)
job参数标识将被立即执行的工作。
(8)使用 submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval与 no_parse。
procedure submit ( job out binary_ineger,
what in varchar2,
next_date in
interval in
no_parse in
date,
varchar2,
booean:=false)
job参数是由 submit()过程返回的 binary_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的 pl/sql代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——true指示此 pl/sql代码在
它第一次执行时应进行语法分析，而 false指示本 pl/sql代码应立即进行语法分析。
(9)user_export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交,此程序
有两个参数：job与 my_call。
procedure user_export(job in binary_ineger,my_call in out varchar2)
job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要的
正文。
(10)what()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job与 what
procedure what (job
in binary_ineger, what in out varchar2)
—job参数标识一个存在的工作。what参数指示将被执行的新的 pl/sql代码。
3)查看相关 job信息
(1)相关视图
dba_jobs
all_jobs
user_jobs
dba_jobs_running包含正在运行 job相关信息
(2)查看相关信息
select job, next_date, next_sec, failures, broken
from dba_jobs;
job next_date next_sec failures b
——- ——— ——– ——– –
9125 01-jun-01 00:00:00 4 n
14144 24-oct-01 16:35:35 0 n
9127 01-jun-01 00:00:00 16 y
3 rows selected.
正在运行的 job相关信息
select sid, r.job, log_user, r.this_date, r.this_sec
from dba_jobs_running r, dba_jobs j
where r.job = j.job;
sid job log_user this_date this_sec
—– ———- ————- ——— ——–
12 14144 hr 24-oct-94 17:21:24
25 8536 qs 24-oct-94 16:45:12
2 rows selected.
job queue lock相关信息
select sid, type, id1, id2
from v$lock
where type = ‘jq’;
sid ty id1 id2
——— — ——— ———
12 jq 0 14144
1 row selected.
4)实例操作
创建测试表
create table test(a date);
——create table
创建一个自定义过程
create or replace procedure myproc as
begin
insert into test values(sysdate);
end;
/
————–过程已创建。
创建 job
variable job1 number;
begin
dbms_job.submit(:job1,’myproc;’,sysdate,’sysdate+1/1440′);
运行 test过程一次
end;
/
pl/sql过程已成功完成。
运行 job
begin
dbms_job.run(:job1);
end;
/
pl/sql过程已成功完成。
sql> select to_char(a,’yyyy/mm/dd hh24:mi:ss’)时间 from test;
时间
——————-
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24
删除 job
begin
dbms_job.remove(:job1);
end;
/
6、后台脚本
编辑文件：dyk_name_mtable1.sql写入以下内容：
alter table owner.table_name1 move tablespace tablespace_name1;
alter table owner.table_name2 move tablespace tablespace_name2;
编辑文件 dyk_name_mtable1.sh
—每天 1440分钟，即一分钟
sqlplus username/passwdString @TNSstring <<!>>dyk_name_mtable2.out
@ dyk_name_mtable1.sql
!
执行命令：
Nohup dyk_name_mtable1.sh &
