存储过程，函数，触发器

===========存储过程==========

一、存储过程基本概念
存储过程是一种命名的PL/SQL语句块，它既可以没有参数，也可以有若干个输入输出参数。
存储过程保存在数据库中，它不可以被SQL语句直接执行或调用、只能通过EXECUTE命令执行，或在PL/SQL内部被调用。
由于存储过程是已经编译好的代码，所以其被调用或引用时，执行效率非常高。
二、创建存储过程的语法
create [or replace] procedure pro_name[(parameter1[,parameter2])] is|as
begin
 plsql_sentences;
[exception]
 处理异常语句
end [pro_name];l

pro_name：存储过程的名称，如果数据库已经存在此名称，则可以指定 "or replace" 这样新的存储过程会覆盖原来的存储过程
parameter1：存储过程的参数，输入参数在其后指定"in"关键字 输出参数在其后指定"out"关键字

案例1：
--创建一个存储过程，该存储过程向dept表中插入一条记录
create procedure pro_insertDept is
begin
  insert into dept values(77,'市场拓展部','JILIN');
  commit;
  dbms_output.put_line('插入新记录成功！');
end pro_insertDept;

案例2：
--在当前模式下，如果数据库中存在同名的存储过程，则要求心创建的存储过程覆盖掉已经存在的存储过程
--如果不存在同名的存储过程，则直接创建即可。
create or replace procedure pro_insertDept is
begin
 insert into dept values(99,'市场拓展部','BEIJING');
 commit;
 dbms_output.put_line('插入新记录成功！');
end pro_insertDetp;
***调用存储过程
1)在sql*plus环境中执行存储过程
execute命令执行存储过程(可简写"exec")
execute pro_insertDept;
2)在plsql块中调用存储过程
begin
  pro_insertDept;
end;
三、存储过程的参数
Oracle为了增强存储过程的灵活性，可以实现向存储过程传入参数，参数是一种向程序单元输入和输出数据的机制。
存储过程可以接受多个参数，参数模式包括IN、OUT、IN OUT3种
1、IN模式参数(输入类型的参数--默认参数模式)
--存储过程参数中(IN模式案例)
create or replace procedure insert_dept(
var_deptno in number,
var_dname in varchar2,
var_loc in varchar2
)is
begin
 insert into dept values(var_deptno,var_dname,var_loc);
 commit;
end inset_dept;


--在PL/SQL中调用存储过程insert_dept，然后使用指定名称的方式向其传入参数值
begin
 --指定名称传递(传递参数值与参数定义顺序无关，但与参数个数有关)
  insert_dept(var_dname=>'采购部',var_loc=>'成都',var_deptno=>15);  
end
--在PL/SQL中调用存储过程insert_dept，然后使用“按位置传递”的方式向其传入参数值
begin
 insert_dept(28,'工程部','洛阳');
end;
2、OUT模式参数(这是一种输出类型的参数，表示这个参数已经在存储过程中赋值，这个参数可以传递到当前存储过程以外的环境中)
    案例：
--OUT模式(案例)创建一个存储工程，要求定义两个out模式的字符类型的参数，然后在dept表中检索到一行部门信息存储到这两个参数中
create or replace procedure select_dept(
  var_deptno in number,
  var_dname out dept.dname%type,
  var_loc out dept.loc%type
) is
begin
 select dname,loc into var_dname,var_loc
 from dept
 where deptno = var_deptno;
 exception
when no_data_found then
 dbms_output.put_line('该部门编号不存在');
end select_dept;


--调用select_dept存储过程
declare
var_dname dept.dname%type;
var_loc dept.loc%type;
begin
 select_dept(40,var_dname,var_loc);--传入部门编号,然后输出部门名称和位置信息
 dbms_output.put_line(var_dname||'位于:'||var_loc);
end;
    使用EXEC命令执行OUT模式的存储过程：使用EXEC命令需要在SQL*Plus环境中使用variable关键字
variable var_dname varchar2(50);
variable var_loc varchar2(50);
exec select_dept(15,:var_dname,:var_loc);

print命令查看变量的值
print var_dname;
使用select语句检索绑定的变量值
select :var_dname,:var_loc from dual;
3、IN OUT模式参数
  IN OUT模式参数可以兼顾其他两种参数的特点，可以从外界向该类型的参数传入值
  在执行完毕储存过程之后，可以将该参数的返回值传给外界。
  -- IN OUT模式(案例)
-- 创建一个存储过程，在其中定义一个"in out"参数，该存储过程用来计算这个参数的平方或平方根
create or replace procedure pro_square(
  num in out number,
  flag in boolean
)is
i int:=2;
begin
if flag then
 num:=power(num,i);--计算平方
else
 num:=sqrt(num);--计算平方根
end if;
end;
--调用存储过程pro_sequare，计算某个数的平方或平方根
declare
var_number number;--存储要进行运算的值和运算后的结果
var_temp number;--存储要进行运算的值
bool_flag boolean;--平方或平方根的逻辑标记
begin
  var_temp:=3; --变量赋值
  var_number:=var_temp;
  bool_flag:=true;--false表示计算平方根，true表示计算平方
  pro_square(var_number,bool_flag);--调用存储过程
  if bool_flag then
dbms_output.put_line(var_temp||'的平方是:'||var_number);
  else
dbms_output.put_line(var_temp||'的平方根是:'||var_number);
  end if;
end;
    4、IN参数默认值(Oracle支持在声明IN参数的同时给其初始化默认值)
--IN参数的默认值
--创建一个存储过程，定义3个IN参数，并将其中的两个参数设置初始化默认值，然后将3个IN参数的值插入到dept表中
create or replace procedure insert_dept(
var_deptno in number,
var_dname in varchar2 default '综合部',
var_loc in varchar2 default '北京') is
begin
 insert into dept values(var_deptno,var_dname,var_loc);--插入一条记录
end;
--当传值的顺序不固定(建议使用指定名称传递的方式)


declare 
 row_dept dept%rowtype;
begin
 insert_dept(57,var_loc=>'太原');
 commit;
 select * into row_dept rom dept where deptno=57;
 dbms_output.put_line('部门名称是:'||row_dept.dname||'位置是:'||row_dept.loc);
end;

======函数=====

1、函数的语法：
create [or replace] function fun_name(parameter1,[,parameter2]) return data_type is
  [inner_variable]
begin
 plsql语句
[exception]
 异常处理语句
end [fun_name];
end;


parameter1：函数的参数
data_type：函数的返回值类型
inner_variable：函数内部变量
案例1：
--定义一个函数，用于计算emp表中某个部门的平均工资
create or replace function get_avg_pay(var_deptno number) return number is
num_avg_pay number; --保存平均工资的内部变量
begin
  select avg(sal) into num_avg_pay from emp where deptno = var_deptno;
  return (round(num_avg_pay,2));--返回平均工资
exception
  when no_data_found then
    dbms_output.put_line('该部门编号不存在');
    return(0);
end;


--调用函数get_avg_pay，计算部门编号为10的雇员平均工资并输出
declare
    avg_pay number;
begin
      avg_pay:=get_avg_pay(10);
      dbms_output.put_line('平均工资是:'||avg_pay);
end;
2、删除函数
drop function fun_name;


========触发器=====
一、基本概念：
   触发器可以看做一种特殊的存储过程，它定义了一些与数据库相关事件如(INSERT、UPDATE、CREATE等事件)
发生时应执行的“功能代码块”。


二、触发器的语法：
create [or replace] trigger tri_name
  [before|after|instead of] tri_event
  on table_name|viewname|user_name|db_name
  [for each row][when tri_condition]
begin
  plsql语句
end tri_name;


关键字相关解释如下：
trigger：表示创建触发器的关键字
before|after|instedad of：表示触发器时机的关键字(instedad of表示触发器为替代触发器)
on：表示操作的数据表、视图、用户模式和数据库等，对它们执行某种数据操作将引起触发器的运行
for each row：执行触发器行级触发器
tri_name：触发器名称
tri_event：触发事件
table_name|view_name|user_name|db_name：分别表示操作的数据表、视图、用户模式和数据库，对它们的某些操作将引起触发器的运行。
tri_condition：表示触发条件表达式
plsql_sentences：PL/SQL语句，它是实现触发器功能实现的主体


Oracle所支持的触发器分为以下5种类型：
1)行级触发器：当DML语句对每一行数据进行操作时都会引起该触发器的运行
2)语句级触发器：无论DML语句影响多少行数据，其所引起的触发器仅执行一次
3)替换触发器：该触发器是定义在视图上的，而不是定义在表上，它是用来替换所使用实际语句的触发器
4)用户事件触发器：是指与DDL操作或用户登录，退出数据库等事件相关的触发器，如，用户登录到数据库或使用ALTER语句修改表结构等。
5)系统事件触发器：是指Oracle数据库系统的事件中进行触发的触发器，如Oracle实例的启动与关闭。


三、语句级触发器
含义：针对一条DML语句而引起的触发器执行，在语句级触发器中，不使用 for each row子句，
      也就是说无论数据操作影响多少行，触发器都只会执行一次。
案例：
--语句级触发器（案例）
create table dept_log(
operate_tag varchar2(10),--定义字段，存储操作种类信息
operate_time date--定义字段，存储操作日期
);


--创建一个触发器tri_dept，该触发器在insert、update和delete事件下可以被触发
--并且操作的数据对象是dept表，要求在触发器执行时输出对dept表所做的具体操作
create or replace trigger tri_dept
 before insert or update or delete
 on dept
declare
 var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
begin
 if inserting then
var_tag:='插入';
 elsif updating then
var_tag:='修改';
 elsif deleting then
var_tag:='删除';
 end if;
 insert into dept_log
 values(var_tag,sysdate);--向日志表中插入对dept表的操作信息  
end tri_dept;


--在数据表dept中实现插入、修改、删除3种操作，以便引起触发器tri_dept的执行
insert into dept values(66,'业务咨询部','长春');
update dept set loc='沈阳' where deptno = 66;
delete from dept where deptno = 66;
四、行级触发器：
含义：行级触发器针对DML操作所影响的每一行数据都执行一次触发器，创建这种触发器时必须在语法中使用
for each row。使用行级触发器典型应用就是给数据表生成主键值。
--行级触发器案例
--创建一个用于存储商品种类的数据表，其中包括商品序号列和商品名称
create table goods
(
   id int primary key,
   good_name varchar2(50)
);


--使用create sequence语句创建一个序列命名为seq_id
create sequence seq_id;
--创建一个行级触发器，该触发器在数据表goods插入数据时被触发，并且在该触发器的主体中实现设置goods表的id列的值
create or replace trigger tri_insert_good
before insert 
on goods
for each row --创建行级触发器    
begin 
 select seq_id.nextval
 into:new.id -- :new.id关键字，列标识符，这个列标识符用来指向新行的id列
 from dual; --从序列中生成一个新的数值，赋值给当前插入行的id列
end;


--向good表中插入两条记录，其中一条记录不指定id列的值，由序列seq_id产生另一条记录指定id的值
insert into goods(good_name)values('苹果');
insert into goods(id,good_name) values(9,'葡萄');