PL/SQL

PL/SQL的基本概念

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedure Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL代码的过程性单元中通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL只有Oracle数据库有

第一个PL/SQL程序

BEGIN --表示开始过程
NULL; --过程代码
END; --过程结束
/ --斜杠结束

输出helloword

SET SERVEROUTPUT ON;--开启打印功能
BEGIN
DBMS_OUTPUT.put_line('Hello world');
END;
/

执行结果：

定义变量根据员工编号查询员工名称（员工编号要键盘输入）

DECLARE 
	v_empno NUMBER;--定义数字类型变量，用于接收键盘输入的员工编号
	v_ename VARCHAR2(100);--定义字符类型变量，用于接收查询返回的员工姓名
BEGIN--过程开启
	--开启键盘输入
	DBMS_OUTPUT.put_line('请输入编号：');
	v_empno := &empno;
	--开始查询,并将查询结果存放在变量v_ename中
	SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;
	DBMS_OUTPUT.put_line('编号是：'||v_empno||'，名字是：'||v_ename);
END;
/

PL/SQL中的控制语句

可以设置常量

DECLARE
	v_empno CONSTANT NUMBER := 7788;--CONSTANT 声明常量，常量必须赋值切不允许改变，使用:=赋值
BEGIN
	DBMS_OUTPUT.put_line(v_empno);
END;
/

可以使用%TYPE定义变量的类型

DECLARE 
	v_empno emp.empno%TYPE;--表示变量类型与emp数据表中empno字段类型一致
	v_ename emp.ename%TYPE;--表示变量类型与emp数据表中ename字段类型一致

使用%ROWTYPE声明变量

DECLARE 
	v_empno emp.empno%TYPE;--表示变量类型与emp数据表中empno字段类型一致
	v_result emp%ROWTYPE;--表示变量类型可以与emp数据表任意字段的类型一致
BEGIN
	DBMS_OUTPUT.put_line('请输入编号：')
	v_empno:=&empno;
	SELECT * INTO v_result FROM emp WHERE empno=v_empno;
	DBMS_OUTPUT.put_line('编号是：'||v_result.empno||',姓名是：'||v_result.ename||',职位：'||v_result.job)
END;
/

IF语句

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

IF ELSE

DECLARE 
	v_count NUMBER;
BEGIN
	SELECT COUNT(*) INTO v_count FROM emp;
	IF v_count>10 THEN 
	DBMS_OUTPUT.put_line('数据大于10');
	ELSE
	DBMS_OUTPUT.put_line('数据小于或者等于10');
	END IF;--表示if结束
END;--过程结束
/

多重IF 语句

DECLARE 
	v_count NUMBER;
BEGIN
	SELECT COUNT(*) INTO v_count FROM emp;
	IF v_count>10 THEN 
	DBMS_OUTPUT.put_line('数据大于10');
	ELSIF v_count<10 THEN
	DBMS_OUTPUT.put_line('数据小于10');
	ELSE
	DBMS_OUTPUT.put_line('数据等于10');
	END IF;--表示if结束
END;--过程结束
/

存储过程

前面的过程都只可以编写执行一次，不能将其保存起来，关闭数据库后下次就无法使用。存储过程经编译后存储在数据库中，用户可通过指定存储过程的名称并给出参数来调用
存储过程可以包含逻辑控制语句和数据操纵语句，他可以接收参数、输出参数、返回单个或多个结果集以及返回值

创建存储过程

CREATE OR REPLACE PROCEDURE hello
AS
BEGIN
 DBMS_OUTPUT.put_line('Hello world');
END;
/

调用该过程

BEGIN
	hello;
END;
/

还可以通过命令执行存储过程

EXEC hello();

以上存储过程就像是JAVA中的方法一样，一旦定义了，就可以重复调用

带参的存储过程

CREATE OR REPLACE PROCEDURE proc_count(
	a NUMBER,
	b NUMBER
)
AS
BEGIN
 DBMS_OUTPUT.put_line(a+b);
END;
/

调用：

EXEC proc_count(10,20);

注意：带参的存储过程调用时需要给定参数值，当原存储过程中定义的变量有默认值时可以不给

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

如果存储过程的参数是IN修饰的则表示该参数需要在调用的时候传入，如果是OUT修饰的则表示可以被返回，不写默认是IN

CREATE OR REPLACE PROCEDURE proc_count(
	a IN NUMBER,
	b IN NUMBER,
	c OUT NUBER
)
AS
BEGIN
	c:=a+b;
END;
/

此过程参数中 c 是由OUT修饰的，是返回值，且无打印结果的语句
调用：

DECLARE
	result NUMBER;--定义一个变量传入过程中用于接收返回值
BEGIN
	proc_count(19,39,result);
	DBMS_OUTPUT.put_line(result);
END;
/

运行结果

触发器

触发器在数据库中是独立的对象存储，它与存储过程和函数不同的是：存储过程和函数需要用户显示的调用才执行，而触发器是由一个事件来触发启动执行的。即触发器是当某个事件发生时自动的隐式运行，并且触发器不能接收参数。ORACLE事件指的是对数据库的表进行的INSERT,UPDATE,DELETE操作或对视图进行类似的操作。
触发器分为语句级触发器（又叫做表触发器）和行级触发器

语句级触发器

语句级触发器对每个DML语句执行一次，如果一条INSERT语句在TABLE中插入500行，那么这个表上的语句级触发器只执行一次，而行级触发器就会触发500次

demo：在周末不能办理员工的离职或者入职手续

CREATE OR REPLACE TRIGGER emp_trigger
BEFORE INSERT OR DELETE --在增加或删除之前触发
ON emp --表示触发器建立的表emp上
--声明变量,用于接收当前时间
DECLARE
v_date VARCHAR2(50);
BEGIN
	SELECT TO_CHAR(SYSDATE,'day') INTO v_date FROM dual;
IF v_date='星期六' OR v_date='星期日' THEN
--抛出错误
RAISE_APPLICATION_ERROR(-20001, '不能删除该职位的员工信息');
END IF;
END;

对数据增加数据：

INSERT INTO emp(empno,ename,job,sal)
	SELECT 1008,'XXX','PRESIDENT',5000 FROM dual
	union
	SELECT 1009,'XXX','PRESIDENT',5000 FROM dual
	union
	SELECT 1010,'XXX','PRESIDENT',5000 FROM dual

运行结果：

行级触发器

行级触发器每条sql语句都会触发一次
实现行级触发器使用FOR EACH ROW 关键字
:new. 和 :old.使用方法和意义，:new. 和 :old.只出现在insert update的SQL语句中，在insert时:new.表示新插入的行数据，update时:new.表示要使用的新数据，:old.表示要被替换的旧数据