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.
表示要被替换的旧数据