PL/SQL ,存储过程 , 触发器

PL/SQL

PL/SQL的基本概念

PL/SQL也是一种程序语言,叫做过程化SQL语言,PL/SQL是Oracle对SQL语言的扩展,在普通的SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断,循环等操作实现复杂的功能或者计算.

打印输出:hello world

SET SERVEROUTPUT ON; --开启打印功能
BEGIN --过程开始
	DBMS_OUTPUT.put_line('hello world');--过程代码
END;--过程结束
/

定义变量

注意:PL/SQL中赋值符号为":=",键盘输入的符号为"&",字符串连接用"||",如果声明了变量但没有赋值默认为空

DECLARE -- 声明
	num NUMBER; --定义变量
BEGIN 
	num:=#--等待键盘输入num的值
	DBMS_OUTPUT.put_line('num的值为:'||num);
END;
/

定义常量

注意:常量使用CONSTANT修饰,而且必须有初始值且常量不能重新赋值.

DECLARE
	num CONSTANT NUMBER :=120 ; ---常量必须赋初值,
BEGIN 
	DBMS_OUTPUT.put_line('常量num的值为:'||num);
END;
/

%TYPE与%ROWTYPE定义的变量类型

DECLARE 
	v_no emp.empno%TYPE;   --表示num的类型为emp表中的empno的字段类型
	result emp%ROWTYPE;    --表示这个变量可以接受emp表中的所有字段的数据
BEGIN
	v_no:=&empno;
	SELECT * INTO result
		FROM emp
		 WHERE empno = v_no;
	DBMS_OUTPUT.put_line('姓名:'||result.ename||' , 部门编号:'||result.deptno||' , 薪水:'||result.sal||' , 领导编号:'||result.mgr||' , 职位:'||result.job);
END;
/

IF的使用

DECLARE
	num NUMBER;
BEGIN
	SELECT COUNT(*) INTO num
		FROM emp;
 	IF num > 10 THEN
 		DBMS_OUTPUT.put_line('数据量大于10');
 	END IF;  --表示if结束
 END;
 /

IF ELSE

DECLARE
	num NUMBER;
BEGIN
	num:=#
 	IF num > 10 THEN
 		DBMS_OUTPUT.put_line('数据大于10');
 	ELSE
 		DBMS_OUTPUT.put_line('数据小于10');
 	END IF;  --表示if结束
 END;
 /

ELSIF

DECLARE
	num NUMBER;
BEGIN
	num:=#
 	IF num > 10 THEN
 		DBMS_OUTPUT.put_line('数据大于10');
 	ELSIF num = 10 THEN
 		DBMS_OUTPUT.put_line('数据等于于10');
 	ELSE
 		DBMS_OUTPUT.put_line('数据小于10');
 	END IF;  --表示if结束
 END;
 /

存储过程

存储过程Procedure是一组为了完成特定功能的SQL语句集合,之前的pl/sql只能编写一次执行一次或多次,但是不能将其保存起来下次使用.如果要让其继续生效则应该将这个过程存储起来,于是就出现了存储过程的概念,经编译后存储在数据库中,用户通过存储过程的名称并给出参数来调用.

调用过程输出 hello world

--创建存储过程
CREATE OR REPLACE PROCEDURE say_hello
AS
BEGIN
	DBMS_OUTPUT.put_line('hello world');
END;
/

--使用命令执行存储过程
EXEC say_hello()

有参数参与的存储过程

--创建存储过程
CREATE OR REPLACE PROCEDURE print_add(
	a NUMBER ,
    b NUMBER
)
AS
BEGIN
	DBMS_OUTPUT.put_line('a+b'||(a+b));
END;
/

--使用命令执行存储过程
EXEC print_add(10,20);

存储过储的输出和输入参数

如果过程的参数是IN修饰的则表示该参数需要在调用的时候传递进来,如果是OUT修饰的则表示可以被返回,如果不写默认就是IN

--创建存储过程
CREATE OR REPLACE PROCEDURE print_sum(
	a IN NUMBER , --这是一个输入参数,表示调用过程时需要传递进来
    b IN NUMBER , --这是一个输入参数,表示调用过程时需要传递进来
    c OUT NUMBER  --这是一个输出但是,表示这个参数可以被返回
)
AS
	result NUMBER; --这是过程的局部变量
BEGIN
	result := a+b;
	c := result*10;
END;
/

--执行
DECLARE 
	r NUMBER;
BEGIN
	print_sum(10,20,r);
	DBMS_OUTPUT.put_line('返回的r值为:'||r);
END;
/

触发器

触发器在数据库中是一独立的对象存储的,它与存储过程和函数不同的是:存储过程与函数需要用户调用才会执行,而触发器是由一个事件来触发运行的.即触发器是当某个事件发生时自动运行,并且,触发器不能接收参数.

Oracle的事件是指对数据库的表进行的,INSERT,UPDATE,DELETE操作或对视图进行类似的操作.

语句级触发器(表级触发器)

语句级触发器是对每个DML语句执行一次,如果一条INSERT语句在表中插入500行,那么这个表上的触发器只执行一次.

--创建触发器
CREATE OR REPLACE TRIGGER emp_trigger
	BEFORE INSERT OR DELETE --在增加或删除数据之前触发
ON emp  --表示触发器建立在emp数据表上
DECLARE --声明变量
v_date VARCHAR2(30);
BEGIN
	SELECT TO_CHAR(SYSDATE,'day')  INTO v_date FROM dual; --获得当前是星期几
	IF v_date ='星期六' OR v_date ='星期日' THEN
		--RAISE_APPLICATION_ERROR  抛出错误在 -20999 到 -20000 之间
    	RAISE_APPLICATION_ERROR(-20001,'周末不能办理入职或离职手续!!');
    END IF;
END;
/

添加数据测试

行级触发器

对于行级触发器,只要有一条数据被影响就会触发一次

--创建触发器
CREATE OR REPLACE TRIGGER emp_trigger
	BEFORE INSERT --在增加数据之前触发
ON emp  --表示触发器建立在emp数据表上
FOR EACH ROW  --表示是行级的触发器,不写就表示是表级触发器
BEGIN
	IF :new.sal>10000 THEN
		RAISE_APPLICATION_ERROR(-20001,'给的工资太高了,公司要破产的!!!!');
	ELSIF :new.sal<1000 THEN
		RAISE_APPLICATION_ERROR(-20002,'给的工资太低了!!!!!');
	END IF;
END;
/

添加人员触发触发器

利用触发器实现级联更新

--创建触发器
CREATE OR REPLACE TRIGGER dept_trigger
	BEFORE UPDATE OF deptno --指定了更新demp表中的deptno字段之前会触发触发器
ON dept  
FOR EACH ROW
BEGIN
	UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
END;
/

修改了dept表中的deptno字段,emp表中的字段也会跟着变化

注意: 在INSERT是:new表示新插入的数据,在UPDATE时,:new表示要替换的数据,:old表示原来要被更改的数据,