第八章PLSQL应用.docx
《第八章PLSQL应用.docx》由会员分享，可在线阅读，更多相关《第八章PLSQL应用.docx（25页珍藏版）》请在冰豆网上搜索。
第八章PLSQL应用
第8章PL/SQL应用
8.1存储过程
8.1.1存储过程概念
存储过程PROCEDURE是Oracle的对象，其内容是一个可执行的PL/SQL程序块，其内容保存在系统表空间的数据字典中。
是一个命名的程序块，可以接受多个参数，无返回值（函数有返回值），但可以通过参数返回简单值。
存储过程存储在在服务器端，减少数据的传送和网络流量，存储过程一般在客户端程序中调用或在服务器中作为定时作业运行。
8.1.2存储过程操作
1.存储过程建立：
CREATE[ORREPLACE]PROCEDUREprocedurename
[（parameter1[IN|OUT|INOUT]datatype[{:
=|DEFAULT}expression]
[,parameter2[IN|OUT|INOUT]datatype[{:
=|DEFAULT}expression],...]）]
[AUTHID{CURRENT_USER|DESIGNER}]
{IS|AS}
pragramblock;
（1）说明：
①ORREPLACE如果存在同名的存储过程，则替换。
②procedurename：
存储过程名。
③parameter：
参数，可以没有参数。
④IN|OUT|INOUT：
参数的输入输出类型：
IN参数将作为一个输入变量,在过程体中不允许被赋值（默认）；
OUT参数将作为过程的返回值，在过程体中不允许使用它的值，只能对它赋值；
INOUT参数既可以作为输入参数，也可以作为输出参数。
⑤datatype：
参数的数据类型，类型不能做宽度限制。
⑥{:
=|DEFAULT}expression：
参数的缺省值。
⑦AUTHID：
存储过程中访问对象的权限方式：
CURRENT_USER：
以调用存储过程的用户权限访问存储过程中访问的对象。
DESIGNER：
以存储过程拥有者的权限访问存储过程中访问的对象。
⑧pragramblock：
PL/SQL程序块，如果有声明部分，不写DECLARE关键字。
（2）存储过程举例：
CREATEORREPLACEPROCEDUREcacluavg
（pnovarchar,pscoreavgnumber）--不用DECLARE关键字
IS
v_mathsscore.maths%TYPE;--不用DECLARE关键字
v_chinesescore.chinese%TYPE;
v_englishscore.english%TYPE;
BEGIN
SELECTmaths,chinese,englishINTOv_maths,v_chinese,V_english
FROMscoreWHEREno=pno;
pscoreavg:
=（v_maths+v_chinese+V_english）/3;
END;
说明：
参数的数据类型不能有宽度限制；说明部分不用DECLARE关键字。
2.存储过程调用
（1）在SQL*plus中的调用：
SQL>EXECUTE过程名（参数）；
（2）在PL/SQL程序块中的调用：
过程名（参数）；
PL/SQL中调用存储过程举例：
DECLARE
v_scoreavgnumber;
BEGIN
cacluavg（‘101’,vscoreavg）;--顺序调用方式
cacluavg（psoreavg=>vscoreavg,pno=’101’）;--名字调用方式
END;
3．应用举例：
创建：
Createorreplaceprocedureupr_in（add_suminnumber）
Is
sum_totalnumber（6）default10;
Begin
Sum_total:
=sum_total+add_sum;
Dbms_output.Put_line（'过程执行的结果是：
'||to_char（sum_total））;
End;
调用：
Executeupr_in（10）;
输出emp表的记录的条数
Createorreplaceprocedureupr_out（add_sumoutnumber）
Is
Begin
Selectcount（*）intoadd_sumfromemp;
End;
Declare
n_countnumber（6）;
Begin
upr_out（n_count）;
dbms_output.put_line（'过程执行的结果是：
'||n_count）;
End;
Createorreplaceprocedureupr_in_out（c_charsinoutvarchar2）
Is
Begin
C_chars:
=c_chars||'one';
End;
Declare
c_namevarchar2（20）;
Begin
c_name:
='HelloEvery';
upr_in_out（c_name）;
dbms_output.put_line（c_name）;
End;
删除emp表中指定编号的员工信息
Createorreplaceproceduredelemp（v_empnoinemp.Empno%type）as
No_resultexception;
Begin
Deletefromempwhereempno=v_empno;
Ifsql%notfoundthen
Raiseno_result;
Endif;
Dbms_output.Put_line（'编码为'||v_empno||'的员工已被除名!
'）;
Exception
Whenno_resultthen
dbms_output.Put_line（'你需要的数据不存在!
'）;
Whenothersthen
Dbms_output.Put_line（'发生其它错误!
'）;
Enddelemp;
Executedelemp（'0005'）;
计算emp指定部门的工资总和，并统计其中的职工数量
Createorreplaceprocedureproc_demo（
Dept_nonumberdefault10,
Sal_sumoutnumber,
Emp_countoutnumber）
Is
Begin
Selectsum（sal）,count（*）intosal_sum,emp_count
Fromempwheredeptno=dept_no;
Exception
Whenno_data_foundthen
dbms_output.Put_line（'你需要的数据不存在!
'）;
Whenothersthen
dbms_output.Put_line（'发生其它错误!
'）;
Endproc_demo;
Declare
V_numnumber;
V_sumnumber（8,2）;
Begin
Proc_demo（30,v_sum,v_num）;
Dbms_output.Put_line（'30号部门工资总和：
'||v_sum||'，人数：
'||v_num）;
End;
用户连接登记记录
Createtablelogtable（useridvarchar2（10）,logdatedate）;
Createorreplaceprocedurelogexecutionis
Begin
Insertintologtable（userid,logdate）values（user,sysdate）;
End;
EXECUTElogexecution;
Select*fromlogtable;
8.2存储函数
存储函数FUNCTION，与存储过程类似，函数有返回值，并且程序块中有RETURN语句。
8.2.1定义语法：
CREATE[ORREPLACE]FUNCTIONfunction
[（parameter1[IN|OUT|INOUT]datatype[:
=[DEFAULT]expression]
[,parameter2[IN|OUT|INOUT]datatype[:
=[DEFAULT]expression]…]）]
RETURNdatatype
[AUTHID{CURRENT_USER|DESIGNER}]
{IS|AS}
pragramblock;
存储函数举例：
CREATEORREPLACEFUNCTIONtotalsal
（v_empnoemp.empno%TYPE）
RETURNNUMBER
IS
totalsal1NUMBER;
BEGIN
SELECTsal+commINTOtotalsal1FROMempWHEREempno=v_empno;
RETURNtotalsal1;
END;
8.2.2函数的调用：
函数名（实参）。
函数调用作为操作值使用，如出现在赋值语句的右值等等。
8.2.3应用
计算emp表中某部门的工资总和：
CREATEORREPLACEFUNCTIONget_salary（
Dept_noNUMBER,
Emp_countOUTNUMBER）
RETURNNUMBERIS
V_sumNUMBER;
BEGIN
SELECTSUM（sal）,count（*）INTOV_sum,emp_count
FROMempWHEREdeptno=dept_no;
RETURNv_sum;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（'你需要的数据不存在!
'）;
WHENOTHERSTHEN
DBMS_OUTPUT.PUT_LINE（'发生其它错误!
'）;
ENDget_salary;
DECLARE
V_numNUMBER;
V_sumNUMBER;
BEGIN
V_sum:
=get_salary（30,v_num）;
DBMS_OUTPUT.PUT_LINE（'30号部门工资总和：
'||v_sum||'，人数：
'||v_num）;
END;
DECLARE
V_numNUMBER;
V_sumNUMBER;
BEGIN
V_sum:
=get_salary（emp_count=>v_num,dept_no=>30）;
DBMS_OUTPUT.PUT_LINE（'30号部门工资总和：
'||v_sum||'，人数：
'||v_num）;
END;
CREATEORREPLACEFUNCTIONdemo_fun（
NameVARCHAR2,
AgeINTEGER,
SexVARCHAR2）
RETURNVARCHAR2
AS
V_varVARCHAR2（32）;
BEGIN
V_var:
=name||'：
'||TO_CHAR（age）||'岁，'||sex;
RETURNv_var;
END;
DECLARE
VarVARCHAR（32）;
BEGIN
Var:
=demo_fun（'user1',30,sex=>'男'）;
DBMS_OUTPUT.PUT_LINE（var）;
Var:
=demo_fun（'user2',age=>40,sex=>'男'）;
DBMS_OUTPUT.PUT_LINE（var）;
Var:
=demo_fun（'user3',sex=>'女',age=>20）;
DBMS_OUTPUT.PUT_LINE（var）;
END;
Oracle中存储过程和函数的区别
存储过程和函数：
存储过程
函数
用于在数据库中完成特定的操作或任务（插入或删除）
用于特定的数据（选择）
程序头部声明有procedure
程序头部声明有function
程序头部声明时不需描述返回类型
程序头部声明时需要描述返回类型，且PL/SQL块至少需要一个return语句
可以使用in、out和inout三种模式的参数
可以使用in、out和inout三种模式的参数
可作为一个独立的SQL语句来执行
不能独立执行，必须作为表达式的一部分调用
可以通过out\inout返回零个或多个值
通过return返回一个值，且该值要与声明部分一致，也可以是通过out类型的参数带出的变量
SQL语句（DML或SELECT）中不可调用存储过程
SQL语句（DML或SELECT）中可以调用函数
8．3触发器
8.3.1触发器的概念
触发器：
是特定事件出现的时候，自动执行的代码块。
类似于存储过程，触发器与存储过程的区别在于存储过程是由用户或应用程序显式调用的，而触发器是不能被直接调用的。
触发器的主要作用有：
1.自动完成数据的插入，特别是序列之类的值的插入；
2.完成复杂的数据完整性约束；
3.对表操作进行审行
8.3.2触发器的创建
1.语法：
CREATE[ORREPLACE]TRIGGERtrigger_name
{BEFORE|AFTER|INSTEADOF}
{NSERT|DELETE|UPDATE[OFcolumn[,column…]]}ONtablename
[FOREACHROW]
[WHENtrigger_condition]
BEGIN
PL/SQL语句；
END；
2.说明
（1）触发时间：
指明触发器何时执行，该值可取：
BEFORE：
表示在数据库动作之前触发器执行；
AFTER：
表示在数据库动作之后触发器执行。
（2）触发事件：
指明哪些数据库动作会触发此触发器：
INSERT：
数据库插入会触发此触发器；
UPDATE：
数据库修改会触发此触发器；
DELETE：
数据库删除会触发此触发器。
（3）tablenme：
数据库触发器所有的表。
（4）FOREACHROW：
对表的每一行触发器执行一次，否则，则只对整个表执行一次。
3．触发器的执行顺序
（1）执行BEFORE语句级触发器
（2）对于受语句影响的每一行
①执行BEFORE行级触发器；
②执行INSERT|DELETE|UPDATE
③执行AFTER行级触发器
（3）执行AFTER语句级触发器
4．触发器的PL/SQL程序块的限制：
（1）触发器程序块及其调用的过程或函数都不能有事务处理命令；
（2）触发器中不能包含触发条件的对应操作，即不能嵌套触发；
（3）不能声明大数据类型BLOB、CLOB等。
5．应用实例
例：
建立一个触发器，当职工表emp表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
Createtableemp_hisasselect*fromempwhere1=2;
Createorreplacetriggerdel_emp
before
deleteonemp
foreachrow
Begin
--将修改前数据插入到日志记录表del_emp,以供监督使用。
Insertintoemp_his（deptno,empno,ename,job,sal,comm,hiredate）
values（:
old.Deptno,:
old.Empno,:
old.Ename,:
old.Job,:
old.Sal,:
old.Comm,:
old.Hiredate）;
End;
Deleteempwhereempno=7788;
Droptableemp_his;
Droptriggerdel_emp;
在触发器可以使用的两个特殊表：
:
Old表：
系统临时表，结构跟定义触发器的表的结构一样，存储刚删除的数据，只能在触发器中使用。
:
New表：
系统临时表，结构跟定义触发器的表的结构一样，存储刚添加的数据，只能在触发器中使用。
在UPDATE触发器中，:
New表中存放更新后的数据，:
Old表中存放更新前的数据。
例：
创建一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。
CREATEORREPLACETRIGGERdel_emp_deptno
Before
DeleteONdept
FOREACHROW
BEGIN
DELETEFROMempWHEREdeptno:
=old.deptno;
END;
8.3.3触发器的修改和删除
像存储过程一样，触发器不能被显式修改，必须有一个新的定义来替换。
删除触发器：
语法：
DROPTRIGGERtrigger
练习：
某学校管理系统中有教师档案表、教师借书表、教师工资表，要求在教师档案表上写一触发器，使得当有教师辞职时，能自动删除其在其他表中相关记录。
8．4包
8.4.1包的概念
包：
用于组织相关的过程和函数，当包中的任何函数或存储过程被调用时，包就加载到内存，包中的任何函数或存储过程的子程序的访问速度将大大加快。
包头（规范）：
定义公用常量、变量、存储过程说明、函数说明
包的组成
包体：
用于实现包规范中定义的存储过程和函数以及私有存储过程和函数。
8.4.2包的创建
1．包头的创建
格式：
CREATEPACKAGEpackage_name
IS
变量、常量、数据类型的定义；
游标定义；
函数声明；
存储过程声明；
ENDpackage_name;
2．包体的创建
格式：
CREATEPACKAGEBODY package_name
AS
游标、函数、存储过程的具体定义；
ENDbody_name;
8.4.3实例
例：
创建一个mypack包，包中包含一个公共函数getArea，用来求圆的面积；一个公共存储过程getLength，当半径r大于0时，求周长，否则产生异常；一个私有函数validate，用来判断r是否大于0；一个公共常量的定义PI，用来表法∏的值。
--声明一个包头
createorreplacepackagemypack
is
--声明常量
c_piconstantnumber:
=3.14;
--声明函数
functiongetArea（rinnumber）returnnumber;
--声明过程
proceduregetLength（rinnumber,lengthoutnumber）;
endmypack;
--声明一个包体，实现包头
createorreplacepackagebodymypack
is
--实现了包头中声明的函数，公共函数
functiongetArea（rinnumber）
returnnumber
is
v_areanumber;
begin
v_area:
=r*r*c_pi;
returnv_area;
endgetArea;
--实现了包头中没有声明的函数，私有函数
functionvalidate（numinnumber）
returnboolean
isbegin
ifnum<=0then
return（false）;
else
return（true）;
endif;
endvalidate;
--实现了包头中声明的过程，公共过程
proceduregetLength（
rinnumber,
lengthoutnumber
）
is
--自定义异常
e_invalidexception;
begin
ifvalidate（r）then
length:
=2*r*c_pi;
else
--触发自定义异常
raisee_invalid;
endif;
exception
--捕获和处理自定义异常
whene_invalidthen
dbms_output.put_line（'e_invalidinputnumber'）;
whenothersthen
dbms_output.put_line（sqlcode||','||sqlerrm）;
endgetLength;
endmypack;
--用匿名块调用包中的公共程序块
declare
v_areanumber;
v_lengthnumber;
begin
v_area:
=mypack.getArea（5）;
dbms_output.put_line（v_area）;
mypack.getLength（5,v_length）;
dbms_output.put_line（v_length）;
end;
--在SQL执行环境中，利用虚表条调用
selectmypack.getArea（5）fromdual;
--在sqlplus中调用带返回值的过程
varv_lengthnumber;
execmypack.getLength（5,:
v_length）;
print:
v_length;
例：
创建一个包demo_pack，包中含有三个公共函：
adddept用来向dept（deptno,dname,location）插入一条记录，若插入成功，则返回1，否则返回0；removedept用来从dept表中删除指定部门号的信息，若成功，则返回1，否则返回0;过程querydept用来输入指定部门号的信息和记录数。
--声明一个包头，含变量，函数和过程
CREATEORREPLACEPACKAGEdemo_pack
IS
dept_recorddept%ROWTYPE;
emp_countNUMBER;
FUNCTIONadddept（
v_deptnoINdept.deptno%TYPE,
v_dnameINdept.dname%TYPE,
v_locationINdept.loc%TYPE
）RETURNNUMBER;
FUNCTIONremovedept（v_deptnoINdept.deptno%TYPE）
RETURNNUMBER;
PROCEDUREquerydept（v_deptnoINdept.
升级会员 