plsql中如何创建函数，触发器以及包和变量的使用

在数据库的操作中，在编写sql语句的时候，我们可能会用到函数以及触发器等的操作来简化我们的工作。
在这片文章中，也和我以前的博文一样，不是纯粹的理论讲解，而是采用的是案例的方式进行笔记的记录说明。还是用到的以前博文中的员工表emp

1.认识plsql的函数

问题1 输入雇员姓名返回年薪

create or replace function sp_func(spName varchar2) return number is
 yearSal number(7,2);
 begin
   select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
   return yearSal;
 end;

总结：
语法：

create 【or replace】 function 函数名（参数1 类型1，参数2 类型2） return 返回值类型
                  is 变量、常量和游标的定义；
                 begin
                       执行部分
                    exception
                       异常处理部分（可选）
                  end;

调用函数的第一种方式

var  income number;                                          --定义全局变量var 变量名  变量类型
call sp_func('SCOTT') into:income;   --全局变量赋值：call 函数into：全部变量名
print income;                        --打印全局变量 print 全局变量名

调用函数的第二种方式

select sp_func('SCOTT') from dual;

2,包

包:
主要是一种用来组织和管理过程和函数的一种机制，主要由两部分组成，包规范和包体
包里面主要是声明一下过程和函数，过程和函数的实现放在包体里面

–创建包：包里面只是声明过程和函数

问题：创建一个包，包含一个过程，包含一个函数，
过程：输入员工姓名，新的工资，更新员工工资。
函数：输入员工的姓名，计算该员工的收入

createor replace package sp_package is
  procedure update_sal(name emp.ename%type,newSal emp.sal%type);
  function annual_income(name emp.ename%type) return number;
 end;

总结
语法：创建包

create 【or replace】 package 包名  is
       过程1声明
       过程2声明
       .......
       函数1声明
       函数2声明
       ....
  end;

过程的实现放在包体里面，包里面只是声明过程和函数
–创建包体:

create or replace package body sp_package is
                   procedure update_sal(name emp.ename%type,newSal emp.sal%type) is
                     begin
                       update emp set sal=newSal where ename=name;
                     end;
                    
                   function annual_income(name emp.ename%type) return number is
                     yearSal number(7,2);
                     begin
                       select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=name;
                       return yearSal;
                     end;
            end;

创建包体的语法

create 【or replace】 package body 包名 is
       过程1实现体
       过程2实现体
       ....
       函数1实现体
       函数2实现体
end;

​
包中的过程和函数的调用 通过 包名.过程名或者 包名.函数名 来调用的
​

exec sp_package.update_sal('SCOTT',4500);
 
var income number;
call sp_package.annual_income('SCOTT') into:income;
print income;
select sp_package.annual_income('SCOTT') from dual;

PL/SQL变量：标量类型，复合类型，参照类型lob（large object）类型

–标量类型： 变量名【constan】 数据类型【not null】【：=值】|【default 值】;

案例：
定义一个变长的字符串：v_ename varchar2(20);
定义一个小数：v_vearsal number(7,2);
定义一个小数，给初始值为3.14：v_sal number(3,2):=3.14;
定义一个常量PI给默认值为3.14：PI constant number(3,2):=3.14;
定义一个日期类型；v_hiredate date
定义一个布尔类型 v_invid boolean not null default false;

编写一个PL/sql块，输入员工编号，显示雇员的姓名和工资，以及个人所得税（税率为0.05）

 declare
         v_tax_rate constant number(3,2):=0.05;
         v_ename emp.ename%type;
         v_sal emp.sal%type;
         v_personal_tax number(7,2);
      begin
         select ename,sal into v_ename,v_sal from emp where empno=&no;
v_personal_tax=v_sal*v_tax_rate;
         dbms_output.put_line('姓名：'||v_ename||'工资是'||v_sal||'个人所得税'||v_personal_tax);
      end;

%type :输入标量类型一种，比较灵活  v_ename emp.ename%type;表示v_ename 和emp表中的ename类型一样

​
复合类型：
（1）PL/SQL的记录类型：
（2）pl/sql的表类型
（3）嵌套表类型
（4）varray类型

（1）PL/SQL记录类型：%rowtype

案例：输入一个员工编号，显示该员工的所有信息
      declare
         v_emp emp%rowtype;
       begin
         select *into v_emp from emp where empno=&no;
         dbms_output.put_line('姓名:'||v_emp.ename||'工作:'||v_emp.sal||'奖金:'||v_emp.comm)
       end;

记录类型（自定义的）

declare
     type emp_record_type is record(name emp.ename%type,salary emp.sal%type,dept emp.deptno%type);
     sp_record emp_record_type;
     begin
       select ename,sal,deptno into sp_record from emp where empno=&no;
       dbms_output.put_line('姓名：'||sp_record.name||'工资'||sp_record.salary||'部门'||sp_record.dept);
     end;

​
记录类型的语法：
​ type 类型名 is record(变量1 数据类型1 ,变量2 数据类型2,….)
​ 变量名 类型名;
​

PL/SQL的表类型

--表类型（类似于高级语言中的数组）
--定义表类型时，不定义表的大小
--定义表类型时，需要指定表中的每个数据的类型
--表类型的下标可以为正，可以为负。

declare
      type emp_table_type is table of emp.ename%type index by binary_integer;
      sp_table emp_table_type;
      begin
        select ename into sp_table(-1)from emp where empno=&no;

参照类型：游标

案例：输入一个部门名称，显示该部门所有员工的姓名和工资

declare
type sp_emp_cursor is ref cursor;
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open test_cursor for select ename,sal from emp where deptno=&no;
  loop
    fetch test_cursor into v_ename,v_sal;
    exit when test_cursor%notfound;
    dbms_output.put_line('姓名：'||v_ename||'；工资：'||v_sal);
  end loop;
  close test_cursor;
end;

--总结：
    --（1）定义：type 类型名 is ref cursor: 表明sp_cursor  是一个游标类型
    --（2）test_cursor sp_emp_cursor;定义一个游标变量test_cursor，是sp_emp_cursor类型
    --（3）open test_cursor  for  查询语句；
    --（4）fetch 游标变量名into 变量1，变量2；
    --（5）exit when  test_cursor%notfound;   定义循环退出的条件
    --（6）close test_cursor ：游标使用完要关闭游标，关闭游标

写法2（建议使用这一种写法）

declare
cursor v_cursor is select ename,sal from emp where deptno=&no;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open v_cursor;
  loop
    fetch v_cursor into v_ename,v_sal;
    exit when v_cursor%notfound;
    dbms_output.put_line('姓名：'||v_ename||'；工资：'||v_sal);
  end loop;
  close v_cursor;
end;

/*
cursor 游标变量名is select 语句
open 游标变量
fetch 游标变量into 变量1，变量2，….
close 游标变量

*/

–写法3：用for循环使用游标

declare
cursor v_cursor is select ename,sal from emp where deptno=&no;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  for v_cursors in v_cursor loop
    v_ename:=v_cursors.ename;
    v_sal:=v_cursors.sal;
    dbms_output.put_line('姓名：'||v_ename||'；工资：'||v_sal);
  end loop;
end;

–for循环会自动打开游标和关闭游标

触发器：

当用户登录、退出或者是对对象进行操作的时候，或者是进行ddl操作的时候，引起某个存储过程的执行，我们把这样一个隐含调用的存储过程称之为触发器
1）我们在开发中，可能会提出这样的问题：
–当用户登录的时候，我们希望能够自动记录用户的名字和登录的时间
–当用户在周四或非工作日对表进行修改的时候，不允许这样做。
–当用户去删除一条记录的时候，希望能够将删除记录自动的备份到另外一张表中

解决方法：触发器
触发器分类：
dml触发器（insert，update，delete）,
ddl触发器，系统事件触发器（登录、退出等操作）

下面我们通过例子，来具体的讲解
案例1：创建一张my_emp表，给该表添加记录，提示“添加一条数据”

--语句级的触发器：针对update,insert,select只触发一次操作
create table my_emp(id number(3),name varchar2(10));
 
create or replace trigger tri1
after insert on scott.my_emp
begin
  dbms_output.put_line('添加一条数据');
end;

–执行3次

insert into my_emp values(101,'ellen')

–案例2：在my_emp表中，修改id为101的员工信息的时候，修改一次提示一次
–for each row表明这是一个行级触发器：修改一行，触发一次输出操作

create or replace trigger tri2
after update on scott.my_emp
for each row
begin
  dbms_output.put_line('修改一条记录');
end;

–创建触发器的语法结构
/*
create [or replace] trigger trigger_name
{befor|after]} {insert|update|delete}
on table_name
[for each row]
[when condition]
begin
–触发的操作
end;
*/

–案例3：在删除my_emp表中的记录的时候，提示’删除一条记录’

create or replace trigger tri3
after delete on scott.my_emp
for each row
begin
  dbms_output.put_line('删除一条记录');
end;

–案例4：在删除my_emp表中的记录的时候，触发报错操作，不让删除

create or replace trigger tri4
before delete on scott.my_emp
begin
  raise_application_error(-20001,'不能删除');
end;

/*
raise_application_error是oracle给我们提供的存储过程，
可以传入两个参数，第一个参数是错误号，-20000–20999之间，
第二个参数是报错信息
*/

–案例5：为了禁止工作人员在周四删除my_emp表中的员工信息，创建一个触发器完成这个操作

create or replace trigger tri5
before delete on scott.my_emp
begin
  if to_char(sysdate,'day')='星期四' then
    raise_application_error(-20002,'对不起，周四不允许删除员工信息');
  end if;
end;

–案例6：禁止工作人员在周四修改员工信息（insert update delete）,不同操作报错信息不同

create or replace trigger tri6
before insert or update or delete on scott.my_emp
begin
  case
    when inserting then
      dbms_output.put_line('请不要添加');
      raise_application_error(-20003,'请不要添加');
    when updating then
      raise_application_error(-20004,'请不要修改');
    when deleting then
      raise_application_error(-20005,'请不要删除');
  end case;
end;

–当触发器中同时包含多个触发事件时，为了区分触发事件，可以使用三个条件：updating,deleting,inserting

–案例7：修改员工工资的时候，要求新工资必须比原来工资高，创建一个触发器来解决这个问题，输出新工资和老工资

create or replace trigger tri7
before update on scott.emp
for each row
begin
  if :new.sal<:old.sal then
    raise_application_error(-20006,'新工资不能低于原来工资');
  else
    dbms_output.put_line('原来的工资是：'||:old.sal||'新的工资：'||:new.sal);
  end if;
end;

–:new：update操作完成之后的列的值
–:old：update操作完成前的列的值

–案例8：编写一个触发器，当用户删除my_emp的记录的时候，自动将删除的id和name备份到另外一张表中

create table my_emp_bak as select * from my_emp where 1=2;
 
create or replace trigger tir8
before delete on scott.my_emp
for each row
begin
  insert into my_emp_bak values(:old.id,:old.name);
end;

–禁用触发器

alter trigger trigger_name disable;

–启用触发器

alter trigger trigger_name enable;

–删除触发器

drop trigger trigger_name;

–编写一个触发器，控制员工的新工资不能低于原来的工资，但是也不能高于原来工资的20%

create or replace trigger tri9
before update on scott.emp
for each row
begin
  if :new.sal<:old.sal or :new.sal>:old.sal*1.2 then
    raise_application_error(-20007,'工资范围不对');
  end if;
end;

–作业：创建一张学生表stu（id,name,birthdate）,编写一个触发器，要求是：如果学生年龄小于18岁，则不允许插入

–Oracle的系统触发器：主要是针对oracle的系统事件的一个触发器
–Oracle定义的常用的系统事件：
–ora_client_ip_address:返回客户端的ip
–ora_database_name:返回数据库名
–ora_login_user:返回登录用户名
–ora_sysevent:返回触发器的系统事件名
–ora_des_encrypted_password:返回加密后的密码

–案例：为了记录用户登录和退出的时间，可以建立登录和退出的触发器

create table log_table(username varchar2(10),logon_time date,logoff_time date,address varchar2(20));

–创建登录触发器

create or replace trigger logon
after logon on database
begin
  insert into log_table(username,logon_time,address)values(ora_login_user,sysdate,ora_client_ip_address);
end;

–创建退出触发器

create or replace trigger logoff
before logoff on database
begin
  insert into log_table(username,logoff_time,address)values(ora_login_user,sysdate,ora_client_ip_address);
end;

–系统触发器需要时系统管理员身份才能创建

–ddl触发器：
–编写一个触发器，可以记录用户所进行的ddl操作

create table my_ddl_event(event varchar2(20),username varchar2(10),time date);
 
create or replace trigger ddl_tri
after ddl on scott.schema
begin
  insert into my_ddl_event values(ora_sysevent,ora_login_user,sysdate);
end;

–禁用一张表上所有的触发器：

alter table 表名 disable all triggers;

–激活一张表上所有的触发器：

alter table 表名 enable all triggers;