Oracle-存储过程、函数、触发器

)

一、存储过程

存储过程指的是专门定义的一组SQL语句的集合，它可以定义用户操作参数，并且存在于数据库中，当使用时直接调用即可。

1.1 创建/更新存储过程

-- 语法
CREATE [OR REPLACE] PROCEDURE 过程名称 IS|AS
BEGIN
	PL/SQL语句
[EXCEPTION]
	异常处理;
END;
/
-- 示例
create or replace procedure pro_insertDept is
begin
	insert into dept values(77,'Market','New York');
	dbms_output.put_line('插入数据成功!');
end;
/

1.2 执行存储过程

-- 方法一：使用关键字执行
EXECUTE/EXEC pro_insertDept

-- 方法二: 在PL/SQL块中调用执行
set serveroutput on
begin
	pro_insertDept;
end;
/

1.3 存储过程的参数

1.IN(默认，数值传递):在存储过程中所做的修改不回影响原始参数内容;

-- 创建一个存储过程，定义3个IN模式的变量，将3个变量的值插入到部门表中
CREATE OR REPLACE PROCEDURE pro_insertDept
	(
	num_deptno IN number,
	var_dname IN varchar2,
	var_loc IN varchar2
	) IS
BEGIN
	INSERT INTO dept VALUES(num_deptno,var_name,var_loc);
END;
/
-- 执行
EXEC pro_insertDept(50,'Market','New York');

2.OUT(空进带值出):不带任何数值到存储过程中，但存储过程可以通过此变量将数值返回给调用方;

-- 定义两个OUT模式字符类型的参数，一个IN模式参数，通过IN模式参数查询部门信息.
CREATE OR REPLACE PROCEDURE select_dept
	(
	num_deptno IN dept.deptno%type,
	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=num_deptno;
EXCEPTION
	WHEN no_data_found THEN
		dbms_output.put_line('没有对应的部门编号');
END;
/

-- 执行
set serveroutput on
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;
/

3.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;
/

-- 执行
set serveroutput on
DECLARE
	var_number NUMBER;
	var_temp NUMBER;
	boo_flag BOOLEAN;
BEGIN
	var_temp:=3;
	var_number:=var_temp;
	boo_flag:=false;
	IF boo_flag THEN
		dbms_output.put_line(var_temp||'的平方是:'||var_number);
	ELSE
		dbms_output.put_line(var_temp||'的平方根是:'||var_number);
	END IF;
END;
/

1.4 删除存储过程

DROP PROCEDURE 存储过程名;

二、函数

在数学中，函数一般用于计算和返回一个值。在Oracle数据库中，函数也具有相同的概念，我们可以将经常需要使用的计算或功能写成一个函数。

2.1 创建函数

函数的创建语法与存储过程类似，也是一种存储在数据库中的命名程序块，函数可以接收零或多个输入参数，并且函数必须有返回值。

-- 语法
CREATE OR REPLACE FUNCTION 函数名[(参数...) RETURN 返回值类型 IS|AS
	变量声明部分
BEGIN
	程序部分
	[RETURN 返回值;]
[EXCEPTION]
	异常处理
END [函数名];
/

-- 示例
-- 该函数用于实现计算某个部门的平均工资，传入部门编号参数
CREATE OR REPLACE FUNCTION func_avg(num_deptno NUMBER) RETURN NUMBER IS
	num_avg NUMBER;
BEGIN
	SELECT avg(sal) INTO num_avg FROM emp WHERE deptno=num_deptno;
	RETURN(round(num_avg,2));
EXCEPTION
	WHEN no_data_found THEN
		dbms_output.put_line('该部门编号不存在');
		RETURN('0');
END;
/

2.2 调用函数

set serveroutput on
DECLARE
	avg_pay NUMBER;
BEGIN
	avg_pay:=func_avg(10);
	dbms_output.put_line('平均工资是:'||avg_pay);
END;
/

2.3 删除函数

DROP FUNCTION func_avg

三、触发器

触发器可以看作是一种“特殊”的存储过程，它定义了一些与数据库相关的事件(如INSERT、UPDATE和CREATE)发生时应执行的“功能代码块”，可以实现数据库对用户所发出的操作进行跟踪，并及时做出反应。

3.1 触发器与存储过程的区别

定义触发器的语法形式与存储过程和函数类似，但不同的是，存储过程和函数需要用户显式的调用，而触发器是由“触发事件”隐式调用的。

3.2 创建触发器

-- 语法
CREATE OR REPLACE TRIGGER 触发器名称
	[before | after | instead of] 触发事件
	ON [表名 | 视图名 | 用户模式名 | 数据库名]
		[for each row] [WHEN 触发条件]
[DECLARE]
	[变量声明;]
BEGIN
	程序代码部分;
END [触发器名称];
/

3.3 触发器分类

1.语句级触发器:无论DML语句影响多少行数据，它所引起的触发器都仅执行一次。

2.行级触发器:当DML语句对每一行数据进行操作时都会引起该触发器的运行。

3.替换触发器:该触发器是定义在视图上的,用来替换所使用实际语句的触发器。

4.用户事件触发器:是指与DDL操作或用户登录、退出数据库等事件相关的触发器。

5.用户事件触发器:是指在Oracle数据库系统的事件中触发的触发器，如Oracle示例的启动或关闭。

3.4 语句级触发器

-- 创建一个对dept表操作修改的记录表
CREATE TABLE dept_log(operate_tag VARCHAR2,operate_time DATE);

-- 创建一个触发器，当对dept表执行修改操作，会记录到dept_log表中
CREATE OR REPLACE TRIGGER tri_dept IS
	before insert or update or delete
	ON dept
DECLARE
var_oper varchar2;
BEGIN
	IF inserting THEN
		var_oper:='insert'
	ELSIF updating THEN
		var_oper:='update'
	ELSIF deleting THEN
		var_oper:='delete'
	END IF;
	INSERT INTO dept_log values(var_oper,sysdate);
END tri_dept;
/

-- 三个语句测试
insert into dept values(66,'IT','America');
update dept set loc='Tianshui' where deptno=10;
delete from dept where deptno=66;

3.5 行级触发器

-- 创建一个用于存储商品种类的数据表
CREATE TABLE commdity(id int primary key,good_name varchar2);
-- 为了给commdity表的id列生成不重复有虚值，创建一个序列
CREATE SEQUENCE seq_id;
-- 创建一个触发器，用于为commdity表的id列赋值
CREATE OR REPLACE TRIGGER tri_insert_comm
	before insert
	ON commdity
	for each row
BEGIN
	SELECT seq_id.nextval into :new.id from dual;
END tri_insert_comm;
/
-- 测试
insert into commdity(good_name) values('apple');
insert into commdity values(4,'banana'); -- 不管输入的id是任何值，实际的值都是seq_id.nextval的值

3.6 替换触发器

-- sysdba为soctt授权
GRANT CREATE VIEW to scott;
-- scott创建一个视图
CREATE VIEW view_emp_dept
	AS SELECT empno,ename,dept.deptno,dname,job,hiredate
	FROM emp,dept WHEREemp.deptno=dept.deptno;