过程函数程序包触发器.docx
《过程函数程序包触发器.docx》由会员分享，可在线阅读，更多相关《过程函数程序包触发器.docx（36页珍藏版）》请在冰豆网上搜索。
过程函数程序包触发器
过程，函数，程序包
之前学习的PL/SQL块是匿名的，不能将其存储到数据库中。
我们可以命名我们的PL/SQL块，并为他们确定参数，存储在数据库中。
这样可以从任何数据库客户端或者工具引用和运行他们，比如SQL*PLUS,Pro*C,JDBC。
这些命名的PL/SQL块成为存储过程和函数，他们的集合成为程序包。
优点：
1．可重用性：
一旦命名并保存在数据库中后，任何应用都可以
2．抽象和数据隐藏
3．安全性
过程
存储过程就是命了名的PL/SQL块，可以被赋予参数，存储在数据库中，然后由另一个应用或者PL/SQL例程调用。
比如
CREATEPROCEDUREmy_procas
BEGIN
NULL;
END;
/
语法：
CREATE[ORREPLACE]PROCEDUREprocedure_name（参数）
IS|AS
[PRAGMAAUTONOMOUS_TRANACTION;]--声明自主事务处理。
[本地变量声明]
BEGIN
执行语句部分
[EXCEPTION]
错误处理部分
END[name];
/
CREATEORREPLACEPROCEDUREmy_procas--ORREPLACE
BEGIN
Dbms_output.put_line（‘Hello,world’）;
END;
/
例子
createorreplaceprocedurepro_hrs206d0_ins_hrs206t0
（v_begin_datevarchar2,
v_end_datevarchar2,
v_user_idPUB002t0.user_id%type,
v_keyin_namehrs206t0.keyin_name%type）is
cursorpalmnis
selectsubstr（mn01,1,10）emplno,
substr（to_char（to_date（mn02,'yyyy/mm/dd'）,'yyyymmdd'）,1,8）yyyymmdd,
substr（mn03,1,2）vacation_id,
substr（mn04,1,2）||substr（mn04,-2）begin_time,
substr（mn05,1,2）||substr（mn05,-2）end_time,
mn06time_num,
substr（mn11,1,30）remark,
substr（v_keyin_name,1,10）keyin_name
frompalmn@mis
wheremn01in
（selectemplno
fromhrs101t0
wheredeptnoin
（selectdeptno
fromPUB002t2
whereuser_id=v_user_id））
andmn02betweenv_begin_dateandv_end_date
orderbymn01,mn02;
dup_val_indexexception;
pragmaexception_init（dup_val_index,-1）;
v_deptnovarchar2（8）;
begin
forvacationinpalmnloop
begin
selectdeptno
intov_deptno
fromhrs101t0
whereemplno=vacation.emplno;
exception
whenno_data_foundthen
v_deptno:
='99999999';
end;
begin
insertintohrs206t0（deptno,
emplno,
begin_yyyymmdd,
end_yyyymmdd,
begin_time,
end_time,
time_num,
vacation_id,
remark,
keyin_name）
values（v_deptno,
vacation.emplno,
vacation.yyyymmdd,
vacation.yyyymmdd,
vacation.begin_time,
vacation.end_time,
vacation.time_num,
vacation.vacation_id,
vacation.remark,
vacation.keyin_name）;
insertintohrs206t1（deptno,
emplno,
yyyymmdd,
begin_time,
end_time,
time_num,
vacation_id）
values（v_deptno,
vacation.emplno,
vacation.yyyymmdd,
vacation.begin_time,
vacation.end_time,
vacation.time_num,
vacation.vacation_id）;
commit;
exception
whenothersthen
null;
end;
endloop;
end;
/
执行存储过程（也可以调用）
setserveroutputon
begin
my_proc
end;
/
直接执行：
executemy_proc
execmy_proc
权限：
表和视图具有SELECT,INSERT,UPDATE,DELETE这样的特权，而过程具有EXECUTE特权。
只有将EXECUTE特权赋予用户，用户才可以运行它。
而将它赋予PUBLIC用户，则所有用户都可以运行。
[试验]
创建3个用户
connoracle/oracle
createuseroracleidentifiedbyoracle;
此时不能连结数据库，不能创建过程
grantconnect,resourcetooracle;
createuserseanidentifiedbysean;
grantconnect,resourcetosean;
createusermarkidentifiedbymark;
grantconnect,resourcetomark;
使用mark建立一个过程
connmark/mark
createproceduremarks_procas
begin
null;
end;
/
尝试使用oracle用户执行这个过程：
connoracle/oracle
execmark.marks.proc
授权：
connmark/mark
grantexecuteonmarks_proctooracle
connoracle/oracle
execmark.marks_proc
尝试使用sean用户执行这个过程：
connsean/sean
execmark.marks.proc
将execute授予public用户，使得所有用户都可以执行这个过程
connmark/mark
grantexecuteonmarks_proctopublic;
connsean/sean
execmark.marks.proc
参数：
过程可以进行参数化处理，可以为任何合法的PL/SQL类型，有三种模式：
IN,OUT,INOUT
IN参数通过调用者传入，只能由过程读取，不能改变。
是默认的模式，可以具有默认值。
OUT参数有过程写入。
用于过程需要向调用者返回多条信息的时候。
不能是具有默认值的变量，也不能是常量，必须向OUT参数传递返回值。
INOUT具有两者的特性，可以读取和写入。
IN参数：
Createtablet（nnumber）;
Createorreplace
procedureinsert_into_t（pinnumber）is
begin
insertintotvalues（p）;
endinsert_into_t;
/
这个时候并没有执行该过程，尝试执行
select*fromt;
execinsert_into_t（p=>100）;
select*fromt;
例子2
droptablet;
Createtablet
（nnumber,
pvarchar2（20））;
Createorreplace
procedureinsert_into_t（
p1innumber,
p2innumber）is
begin
insertintotvalues（p1,’p1’）;
insertintotvalues（p2,’p2’）;
endinsert_into_t;
/
这个时候并没有执行该过程，尝试执行
select*fromt;
execinsert_into_t（p1=>100,p2=>200）;
select*fromt;
参数传递方法：
1．使用名称表示
execinsert_into_t（p2=>101,p1=>201）;
2．使用位置表示
execinsert_into_t（102,202）;
3．使用混合表示
适用于有默认值的情况，注意：
OUT和INOUT参数不能有默认值
CREATEORREPLACEproceduredefault_values（
P1varchar2,
P2varchar2default‘Chris’,
P3varchar2default‘Sean’）as
Begin
Dbms_output.put_line（p1）;
Dbms_output.put_line（p2）;
Dbms_output.put_line（p3）;
Enddefault_values;
/
只想传入1，3参数：
setserveroutputon
execdefault_values（‘Tom’,p3=>’Joel’）;
OUT参数：
从过程向调用者返回值：
例子：
使用scott.emp表，编写搜索过程，输入empno,返回ename,sal
分析：
descscott.emp
参数：
一个in,两个out
参数类型：
innumber,outemp.ename%type,outemp.sal%type
conscott/tiger
createorreplace
procedureemp_lookup（
p_empnoinnumber,
o_enameoutemp.ename%type,
o_saloutemp.sal%type）as
begin
selectename,sal
intoo_ename,o_sal
fromemp
whereempno=p_empno;
exception
whenNO_DATA_FOUNDthen
o_ename:
=‘null’;
o_sal:
=-1;
end;
/
执行该过程：
1．使用匿名PL/SQL
分析：
目的是输出两个out参数的结果，所以匿名块中也要定义2个与out参数相同的局域变量。
setserveroutputon
declare
l_enameemp.ename%type;
l_salemp.sal%type;
begin
emp_lookup（7782,l_ename,l_sal）;
dbms_output.put_line（‘Ename=‘||l_ename）;
dbms_output.put_line（‘Sal=‘||l_sal）;
end;
/
2．在sqlplus中执行
分析：
需要使用sqlplus的VARIABLE命令绑定参数值，即为OUT参数提供接受返回值的变量。
Variablenamevarchar2（10）;
Variablesalnumber;
Execemp_lookup（‘7782’,:
name,:
sal）;
Printname;
Printsal;
Select:
name,:
salfromdual;
INOUT参数：
可以用来传入参数，并从存储过程返回值。
例子：
输入两个数，交换值
思路：
通过一个临时变量作为交换过程中的过渡
createorreplace
procedureswap（
p1inoutnumber,
p2inoutnumber）as
l_tempnumber;--局域变量的声明，相当于匿名块中declare之后的声明；过程：
as~begin
begin
l_temp:
=p1;
p1:
=p2;
p2:
=l_temp;
endswap;
/
setserveroutputon
declare
l1number:
=100;
l2number:
=200;
begin
swap（l1,l2）;
dbms_output.put_line（‘l1=‘||l1）;
dbms_output.put_line（‘l2=‘||l2）;
end;
/
关于自主事务处理:
P197
我们曾经讨论过COMMIT和ROLLBACK的概念。
建立过程P2：
CREATEORREPLACEPROCEDUREP2AS
Avarchar2（50）;
Begin
Selectvenadd1intoafromvendor_masterwherevencode=’V002’;
Dbms_output.put_line（a）;
ROLLBACK;
END;
/
建立过程P1，调用P2：
CREATEORREPLACEPROCEDUREP1AS
Bvarchar2（50）;
Begin
Updatevendor_mastersetvenadd1=’10WallsStreet‘wherevencode=’V002’;
P2（）;
Selectvenadd1intobfromvendor_masterwherevencode=’V002’;
Dbms_output.put_line（b）;
ROLLBACK;
END;
/
execp1
说明事务处理可以跨越过程继续执行。
为了防止一个过程影响其他过程，可以将其标记为自主的。
这样p2中的rollback将不会影响p1
CREATEORREPLACEPROCEDUREP2AS
Avarchar2（50）;
PRAGMAAUTONOMOUS_TRANSACTION;
Begin
Selectvenadd1intoafromvendor_masterwherevencode=’V002’;
Dbms_output.put_line（a）;
ROLLBACK;
END;
/
EXECP1;
函数：
与过程相似，遵循了相同的规则。
参数传递：
只能带有in参数，不能使用out,inout参数
函数的主要特性是必须返回一个值。
语法：
CREATE[ORREPLACE]FUNCTIONfunction_name（参数）
RETURNdatatype
IS|AS
[PRAGMAAUTONOMOUS_TRANACTION;]--声明自主事务处理。
[本地变量声明]
BEGIN
执行语句部分
[EXCEPTION]
错误处理部分
END[name];
/
返回值：
定义函数的时候必须使用RETURNdatatype子句，表示函数要返回的数据类型。
在函数体中的任何地方，都可以使用RETURN<表达式>返回值，类型要和定义中的相同。
CREATEORREPLACE
FUNCTIONmy_func
RETURNvarchar2
As
Begin
Return‘HELLO,WORLD’;
END;
/
调用函数：
用户必须要获取返回值，所以调用时，要声明一个局域变量：
setserveroutputon
declare
l_strvarchar2（100）:
=null;
begin
l_str:
=my_func;
dbms_output.put_line（l_str）;
end;
/
也可以将函数用作其他过程以及函数的IN参数。
Createorreplace
Procedureshow_it（pvarchar2）as
Begin
Dbms_output.put_line（p）;
End;
/
execshow_it（my_func）;
练习：
编写一个函数ITE，实现：
booleanexpression?
true_value:
false_value
输入一个表达式expression,IF正确，THEN输出true_value；ELSE，输出false_value
分析：
3个in参数，一个boolean,2个varchar2
createorreplace
functionite（
p_expressionBoolean,
p_truevarchar2,
p_falsevarchar2）
returnvarchar2
as
begin
ifp_expressionthen
returnp_true;
endif;
returnp_false;
end;
/
execdbms_output.put_line（ite（1=2,‘Equal’,’Notequal’））;
程序包：
是对PL/SQL类型，过程，函数，游标，异常，变量，常量的封装。
包括两部分：
规范和主体
规范：
是程序包的公共接口，
主体：
规范的实现，以及私有例程、数据和变量。
语法：
CREATEORREPLACEPACKAGEpackage_name
IS|AS
公用类型或变量常量的声明；
公用过程或函数的声明；
ENDpackage_name;
/
CREATEORREPLACEPACKAGEBODYpackage_name
IS|AS
私有类型或变量常量的声明；
公用过程或函数的实现；
ENDpackage_name
规范：
规范是程序包的接口，规范中定义的所有内容都可以由调用者使用（当然需要具有EXECUTE特权），比如规范中定义的过程函数可以被执行，类型可以被访问，变量可以被引用。
例子：
使用两个过程PRINT_ENAME（）和PRINT_SAL（），定义称为EMPLOYEE_PKG的程序包。
CREATEORREPLACE
PACKAGEemployee_pkgas
Procedureprint_ename（p_empnonumber）;
Procedureprint_sal（p_empnonumber）;
End;
/
并没有为过程提供代码，只是定义了名称和参数。
这个时候如果试图使用这个包，会报错
execemployee_pkg.print_ename（1234）;
主体：
程序包是过程，函数的具体实现部分，实现规范中定义的接口。
CREATEORREPLACE
PACKAGEBODYemployee_pkgas
Procedureprint_ename（p_empnonumber）is
L_enameemp.ename%type;
Begin
Selectenameintol_enamefromempwhereempno=p_empno;
Dbms_output.put_line（l_ename）;
Exception
Whenno_data_foundthen
Dbms_output.put_line（‘Invalidemployeenumber’）;
Endprint_ename;
Procedureprint_sal（p_empnonumber）is
L_salemp.sal%type;
Begin
Selectsalintol_salfromempwhereempno=p_empno;
Dbms_output.put_line（l_sal）;
Exception
WhenNO_DATA_FOUNDthen
Dbms_output.put_line（‘Invalidemployeenumber’）;
Endprint_sal;
Endemployee_pkg;
/
执行：
setserveroutputon
execemployee_pkg.print_ename（1234）;
execemployee_pkg.print_ename（7782）;
execemployee_pkg.print_sal（7782）;
过程和函数的重载：
在单独的程序包中定义的共享相同名称的两个或者多个过程和函数。
单独的过程和函数必须具有唯一的名称，但是程序包中可以有条件的共享相同的名称。
条件：
参数列表在数量、次序或者参数类型上有所区别，比如
procedurefoo（p1varchar2）;
procedurefoo（p1number）;
procedurefoo（p1varchar2,p2number）;
procedurefoo（p1varchar2,p2varchar2）;
但是：
只在参数名称、输入输出模式、或者返回类型上不同，是不够的。
比如
procedurefoo（p1varchar2）;
procedurefoo（p2varchar2）;
procedurefoo（p1invarchar2）;
procedurefoo（p1inoutvarchar2）;
例如
descdbms_output
试验：
重载SWAP（）
之前的例子：
createorreplace
procedureswap（
p1inoutnumber,
p2inoutnumber）as
---
l_tempnumber;
begin
l_temp:
=p1;
p1:
=p2;
升级会员 