PL/SQL过程、函数、包和触发器知识点串讲

一、过程

（一）语法

create or replace procedure name [parameter,…]
IS｜AS  --两者都可，选其一
	变量，常量声明;
BEGIN
		Sql语句和PL/SQL语句
[EXCEPTION]
	异常处理语句
END;

参数的定义：

提醒：声明参数时不能定义参数的数据类型的长度

（二）无参数的存储过程的调用

常用两种方法：

1. 匿名块调用

Begin
		过程名();
End;

2. exec命令

exec 过程名(); --该命令适合在命令窗口完成

当然也可以使用call命令，只不过我们不经常使用。

（三）带参数的存储过程的调用

（1）带in参数的调用

按位置传递： 和其他程序语言一样，要数目、类型、顺序三个一致
按名称传递： 形式：形参名=>实参值。参数之间用逗号分隔
默认值的使用： 调用时不指定值，则为默认值。

案例：根据员工编号涨工资

--创建：
create or replace PROCEDURE raisesal(eno in emp.empno% TYPE)
as
	v_sal emp.sal% TYPE;
BEGIN
  　--涨前工资
    select sal into v_sal from emp where empno=eno;
    --给员工涨工资
    update emp set sal=sal+100 where empno=eno;
  　--输出结果
    dbms_output.put_line('涨前：'|| v_sal||'，涨后：'||(v_sal+100));
end;


--调用：
--匿名块调用：
Begin
	raisesal(7369)
End;

--命令调用：
exec raisesal(7369);

注意：在使用匿名块调用的时候，在定义的时候有多少个参数，就应该在匿名块调用的时候设置多少个变量。在匿名块中把in参数和out参数都设置为变量，然后输出out参数变量.

所以上面使用匿名块调用的的代码可以改写如下：

declare
	v_eno emp.empno%type:=&eno;
begin
	raisesal(eno=>v_eno);
end;

（2）带out参数的调用

如果参数是out类型，通常表示该过程应该有一个返回值给调用者。这样的过程一般在过程体内是不需要写输出语句的。

对out参数的理解：相当于函数中的返回值。当过程执行完，这个参数应该有值

调用方式有两种：匿名块和绑定变量

案例： 查询指定员工的姓名和薪水，并利用out模式将参数值传给调用者。

--创建
create or replace PROCEDURE show(eno emp.empno% TYPE,v_name out emp.ename% TYPE,v_sal out emp.sal% TYPE)
IS
BEGIN
    select ename,sal into v_name, v_sal from emp where empno=eno;
	exception
    when no_data_found THEN
    dbms_output.put_line('没有这个员工');
END;

--提醒：参数定义时，in可以省略，out不能省略


--调用方式一：匿名块
--匿名块：在匿名块中把in参数和out参数都设置为变量，然后输出out参数变量
set serveroutput on
DECLARE
    eno emp.empno% TYPE:=&no;
    V_NAME emp.ename% TYPE;
    v_sal emp.sal% TYPE; 
BEGIN
    --show(eno,v_name,v_sal);
    --注意⚠️：下面这种调用的方式，当eno是上面声明的读入的变量的时候相当于将=>后面的值
    --传给=>前面的值，而当v_name和v_sal都是要输出的输出变量的时候，都相当于将=>前面的值赋值给=>后面的值。
    show(eno=>eno,v_name=>v_name,v_sal=>v_sal);
    dbms_output.put_line(v_name||'   '||v_sal);
end;

--调用方式二：匿名块绑定变量：
var v_name varchar2(10); --varchar2类型必须加长度
var v_sal number;--定义声明变量时，number类型不能加长度
exec show(7369,:v_name,:v_sal); --变量绑定，调用存储过程
print v_name v_sal;
select :v_name,:v_sal from dual;

提醒：

绑定变量的方式适合在命令窗口运行

绑定变量名前要加冒号

Print命令是输出，各变量名之间用空格分隔，变量名前不能加冒号

Select命令：各变量名之间用逗号分隔，变量名前必须加冒号

二、函数

函数必须返回一个值，通常只用in参数

（一）语法

Create or replace function function_name[(argment in|out|in out type)]
Return return_type
Is|as
	[declare_section]
Begin
	Function_body
	Return 变量
	exception
	…
End;

提醒：
函数头和函数体必须都有return语句。前者用来表示返回类型，后者用来表示返回的值。

函数中不能包含对数据库执行操作的语句，如DML（ insert、update、delete等）语句

特别注意，oracle中的DML语句就专指 insert、update、delete，不包括select

（二）调用

通常使用两种方法：

--1. 在sql语句中调用：
select get_ename(7788) from dual; --get_ename为函数名


--2. 使用输出语句调用函数，注意：输出语句必须在块的执行体中，不能单独执行。
set serveroutput on
begin
  	dbms_output.put_line('员工姓名是：'||get_eanme(7788));
end;

注意：对于函数，和储存器类似，只要声明的时候设置多少个参数就应该在调用的时候设置多少个变量(在函数中一般都只有in参数，并没有out参数)

所以调用的时候可以改写成下面的代码：

declare
	v_eno emp.empno%type := &eno;
begin
	dbms_output.put_line('员工姓名是：'||get_eanme(v_eno));
end;

（三）案例:创建一个函数get_ename，该函数按empno获取ename的值。

create or replace function get_ename(fno in emp.empno% TYPE) RETURN VARCHAR2
as
	f_name emp.ename% TYPE;
BEGIN
    select ename into f_name from emp where empno=fno;
    return f_name;
END;


--调用：
declare
	v_eno emp.empno%type := &eno;
begin
	dbms_output.put_line('员工姓名是：'||get_eanme(v_eno));
end; 

上面的函数return返回的是varchar2类型的，当然也可以用emp.ename%type进行获取类型进行返回。

（四）函数与过程比较

三、包

（一）分类：公有元素和私有元素。

公有元素不仅可以被包中元素调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程所访问。

（二）组成：包头（包规范、包定义）和包体

（三）包定义

包定义包括数据类型、变量、常量、游标、子程序和异常等元素，这些元素为包的公有元素。

语法：

create or replace package package _name 
is|as
<定义公有变量、常量、类型、游标>
<定义公有过程和函数>
end;

提醒：包定义中规范过程和函数时，只能包含过程头和函数头

（四）包体

在PL/SQL中，包定义和包体是分开编译的，并作为两个分开的对象存放在数据库字典中。

包体用于实现包定义所规范的公有过程和函数。并且可以定义包内的私有组件。
语法：

create or replace package body package_name 
is|as
<定义私有变量、常量、类型、游标、过程和函数>
<实现公有过程和函数>
end;

提醒：

包头和包体结尾都要有end

包体名称必须与包定义名称相同，在包体定义公有子程序时，它们必须与包定义中所声明子程序的格式完全一致。

（五）包的调用

当在其它应用程序或PL/SQL语句块中调用包的变量、函数或存储过程时，需在它们名字之前加包名，并用点号连接。即：包名.公有元素名

（六）案例——创建一个包：声明一个可以获取指定部门的平均工资的函数，然后再声明一个可以实现按指定比例上调指定职务的工资的存储过程。

--包定义：
create or replace package pack_emp
is
	function noavg(dno emp.deptno%type) RETURN NUMBER;
	PROCEDURE shsal(pjob emp.job%type,bili number);
end;


--实现包定义，完成包体：
create or REPLACE package body pack_emp
is

FUNCTION noavg(dno emp.deptno%type) RETURN NUMBER
is
	f_pjsal emp.sal%type;
begin
    select avg(sal) into f_pjsal from emp where deptno= dno;
    return f_pjsal;
    exception 
    WHEN no_data_found THEN
    dbms_output.put_line('没有这个部门');
    when OTHERS THEN
    dbms_output.put_line('其它错误');
end;

--实现包定义，完成包体：
PROCEDURE shsal(pjob emp.job%type,bili number)
is
begin
    update emp set sal=sal*(1+bili) where job= pjob;
    dbms_output.put_line('成功涨工资');
end;
end;



--包调用：
set serveroutput on
declare
	v_no emp.deptno% TYPE:=&no;
	v_sal emp.sal%type;
begin
    v_sal:=pack_emp.noavg(v_no);
    dbms_output.put_line(v_no||'部门的平均工资是：'||v_sal);
    pack_emp.shsal('MANAGER',0.1);
end;

在实现包定义，完成包体的过程中，函数function与存储过程procedure前面都不能加create or replace。

四、触发器

（一）概念

触发器是在特定事件出现的时候，自动执行的代码块。
触发器是自动隐式运行
触发器不能有参数

（二）触发前提：在建立触发器之前，要对以下内容进行分析

1. 触发事件：DML操作、DDL操作和系统事件等
2. 触发时间：before after
3. 触发对象：表、视图、用户和数据库
4. 触发级别：
   语句级触发（表级触发）：无论操作影响了多少行，语句触发器只被调用一次
   行级触发器：操作影响到的每一行，都会执行触发器(行级触发器要加for each row)。
5. 触发限制：一个逻辑表达式，当该表达式为真时，触发器被触发执行

（三）创建

语法：

Create or replace trigger <触发器名>
	[before|after]
	<触发事件> on <表名>
	[for each row]
	[when <条件表达式>]
<PL/SQL程序体>

（四）DML 触发器

根据触发器的四个应用场景确定的案例

1. 实施复杂的安全性检查——例：禁止在非工作时间操作emp表

知识点：三个谓词：instering deleting updating

create or replace trigger emptime2
before insert or delete or update 
on emp
declare
	v_riqi varchar2(10);
	v_hh number(2);
begin
    v_riqi:=to_char(sysdate,'day');
    v_hh:=to_number(to_char(sysdate,'hh24'));
    if(v_riqi in('星期六','星期日') or (v_hh>17 or v_hh<8)) THEN
    	if inserting then
      	raise_application_error(-20013,'非工作时间禁止插入');
    	elsif deleting then
      	raise_application_error(-20014,'非工作时间禁止删除');
    	elsif updating then
      	raise_application_error(-20015,'非工作时间禁止更新');
    	else 
      	raise_application_error(-20016,'非工作时间禁止任何操作');
    	end if;
  end if;
end; 

验证：在emp表上做增删改操作，都会触发。

2. 数据完整性检查——例：修改后的工资不能比原工资低

知识点：两个伪记录变量 :old :new

这两个变量只在触发器内部有效，并且只有在DML触发表中字段时才有效。使用形式：:old.字段名和:new.字段名

:old和 :new 表示的是同一条记录。两个都是记录变量

:old 是这条记录被操作之前；:new 是这条记录被操作之后。

特别注意⚠️：只有在行级触发器中(for each row)中才有:old与:new两个伪记录变量。

create or replace trigger checksal
before update
on emp
for each row
begin
  	if :old.sal>:new.sal then
    dbms_output.put_line('涨前：'||:old.sal||'，涨后：'||:new.sal);
    RAISE_application_error(-20002,'涨后的工资不能小于涨前的工资');
    end if;
end; 

验证：修改某员工的工资，使其工资值减少。

3. 数据库的审计——例：当工资超过6000元时，查看该员工的信息

先要创建表：

Create table audit_info(info varchar(200));

再创建触发器：

create or replace trigger shji_info
after update 
on emp
for each row
begin
 	 if :new.sal>6000 then
     insert into audit_info values(:new.empno||'  '||:new.ename||'  '||:new.sal);
  	 end if;
end; 

验证：修改(update)员工的工资为大于6000

4. 数据的备份和同步——例：员工涨工资后，会同步备份到备份表中

先建立备份表：

create table emp_back as select * from emp;

再建立触发器：

create or replace trigger copysal
after update 
on emp
for each row
begin
 	 update emp_back set sal=:new.sal where empno=:new.empno;
end; 

验证：修改emp表中某员工的工资，再查看备份表中的数据

（五）替代触发器

解决多表视图的更新问题，替代视图完成对多个表中数据的修改。

替代触发器都是行级触发。因为触发操作不会执行，所以没有before和after

1.例：当向视图中插入新记录时，被认定为非法。试用触发器完成插入操作

create or replace trigger view_insert_tigger 
instead of insert 
on v_emp20  
for each row 
declare 
    v_empCount  NUMBER; 
    v_deptCount  NUMBER; 
begin 
    --判断要增加的员工是否存在
    SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno; 
    --判断要部门是否存在
    SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;
    --如果部门不存在
    IF v_deptCount=0 THEN 
    	INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc); 
    END IF;
    --如果员工不存在
    IF v_empCount=0 THEN 
    	INSERT INTO emp(empno,ename,job,sal,deptno) VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno); 
    END IF; 
end ; 

（六）DDL触发器

1、例：记录用户所有的DDL操作

先建立日志表：

create table ddl_log(lname varchar2(30),obj_type varchar2(20),eventname rchar2(20),ddl_date date); 

再创建触发器

create or replace trigger trig_ddl
after ddl 
on schema
begin
  	insert into ddl_log values(ora_dict_obj_name,ora_dict_obj_type,sysdate,ora_sysevent);
end;