1 pl/sql基本概念 PL/SQL是一种SQL（procedure Language/SQL ）程序语言。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL 语句的使用上增加了编程语言的特点，所以PL/SQL把数据的操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断，循环等操作实现复杂的功能或者计算。PL/SQL只有Oracle数据库有。 PL/SQL程序格式：
BEGIN --表示开始 NULL;–过程代码 END;–过程结束 1 2 3 / 定义变量 DECLARE v_ num NUMBER; --声明一个全局变量 BEGIN --表示开始过程 DBMS_OUTPUT.put_line(‘变量的值是:’||v_num); --过程代码 END; --过程结束 /
注意:
1 字符串的连接使用的不是“+”而是“| |”； 2全局的变量声明放在DECLARE关键字之后 3如果声明了变量但是没有赋值则默认就是空。
为变量赋值
为变量赋值使用的不是“=”而是“:=”。
DECLARE v_ num NUMBER;–定义一个变量 BEGIN --表示开始过程 v_ num := 30;-- 为变量赋值 DBMS_OUTPUT.put_line(‘变量的值是：’|| v_ num)；-- 过程代码 END;-- 过程结束 /
给变量设置默认值
可以在DECLARE后面声明变量的时候给出默认值，如果在BEGIN之后没有为变量赋新的值则按照默认值输出。
DECLARE v_ num NUMBER:= 100; --定义一个变量 BEGIN --表示开始过程 DBMS_OUTPUT.put_line(‘变量的值是：’|| v_ num)； – 过程代码 END; – 过程结束 /
根据雇员编号查询雇员的名字(用键盘输入) DECLARE v_ empno NUMBER;–保存雇员编号 v_ ename VARCHAR2(100); --保存编号 BEGIN --表示开始过程 – 键盘输入雇员编号 DBMS_OUTPUT.put_line(‘请输入编号！’)；-- 过程代码 v _ empno := &empno ; –开始查询 SELECT enamel INTTO v_ename FROM emp WHERE empno = v_ empno; DBMS_OUTPUT.put_line(‘编号是：’|| v_ empno|| ‘,名字是：’|| v_ ename); – 过程代码 END;-- 过程结束 /
2 plsq中的程序控制 定义常量 DECLARE v_ empno CONSTANT NUMBER := 7788; – 常量必须有初始值 BEGIN DBMS_ OUTPUT.put_ line(‘编号是：’|| v _ empno); END; /
注意 常量是使用CONSTANT修饰的，不能改变，不能被重新赋值。
使用%TYPE定义变量的类型 表名. 字段名%TYPE: 让变量的类型未指定的字段类型。
DECLARE v _ empno emp.empno%TYPE; --表示变量的类型是emp数据表中empno字段的类型(NUMBER) v _ ename emp.ename%TYPE; --表示变量的类型是emp数据表中ename字段的（VARCHAR2） BEGIN DBMS _ OUTPUT.put_ line(‘请输入编号！’); v _ empno:=&empno; SELECT ename INTO v_ ename FROM emp WHERE empno = v_ empno DBMS_OUTPUT.put_ line(‘编号是：’|| v_ empno ||’,姓名是：’|| v_ename); END; /
使用%ROWTYPE声明变量 DECLARE v _ empno emp.empno%TYPE; --表示变量的类型是emp数据表中empno字段的类型(NUMBER) v _ result emp%ROWTYPE; --表示该变量可以接受查询到的所有字段数据 BEGIN DBMS _ OUTPUT.put_ line(‘请输入编号！’); v _ empno:=&empno; SELECT * INTO v_ result FROM emp WHERE empno = v_ empno DBMS_OUTPUT.put_ line(‘编号是：’|| v_ empno ||’,姓名是：’|| v_result.ename||’,职位是:’|| v_result.job||’,部门编号:’||v_ result.deptno); END; /
IF使用 DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FEOM emp; IF v_ count>10 THEN DBMS_ OUTPUT.put_line('数据量大于10 '); ENS IF ;–表示if结束 END;–过程结束 /
if else使用 DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FEOM emp WHERE job = ‘SALESMAN’; IF v_ count>10 THEN DBMS_ OUTPUT.put_line('数据量大于10 '); ELSE DBMS_OUTPUT.put_line(‘数据量小于或者等于10’)； ENS IF ;–表示if结束 END;–过程结束 /
多if 结构 DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FEOM emp WHERE job = ‘SALESMAN’; 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’)； ENS IF ;–表示if结束 END;–过程结束 /
3 存储过程 存储过程Procedure是一组为了完成特定功能的SQL语句集合，之前的pl/sql只能编写一次执行一次或者多次，但是不能将其保存起来下一次使用，关闭窗口之后写的pl/sql下一次打开窗口就失效了，如果让其还继续 生效下去则应该将这个过程存储起来，于是出现了存储过程的概念，编译后存储在 数据库中，用户通过指定存储过程的名字 并给出参数来调用。
存储过程中可以包括逻辑控制语句和数据操作语句，它可以接受参数，输出参数，返回单个 或者多个结果集以及返回值。
创建过程 CREATE OR REPLACE PROCEDURE hello_say AS BEGIN DBMS_OUTPUT.put_line(‘hello world’); end; /
调用过程使用pl/sql（调用过程名） BEGIN hello_say(); END; /
使用命令执行存储过程
存储过程就像Java中的方法一样，一旦定义了就可以重复使用。也可以给过程定义参数。
EXEC hello_say();
有参数的存储过程 CREATE OR REPLACE PROCEDURE proc_count( a NUMBER, b NUMBER ) AS BEGIN DBMS_OUTPUT.put_line(‘a+b=’||(a+b)); END; / EXEC proc_count(10,20);
给参数设定默认值 CREATE OR REPLACE PROCEDURE proc_count( a NUMBER:=100, b NUMBER:=100 ) AS BEGIN DBMS_OUTPUT.put_line(‘a+b=’||(a+b)); END; / EXEC proc_count();
存储过程的局部变量 CREATE OR REPLACE PROCEDURE proc_count( a NUMBER:=100, b NUMBER:=100 ) AS result NUMBER; --这是过程的局部变量 BEGIN result:=a+b; DBMS_OUTPUT.put_line(‘a+b=’||result); END; / EXEC proc_count(10.,10);
存储过程的输出和输入参数 如果过程的参数是IN修饰则表示该参数需要在调用的时候传递进来，如果是OUT修饰的参数则表示可以被返回，如果不写默认就是IN.
CREATE OR REPLACE PROCEDURE proc_count( a IN NUMBER:=10, --是一个输入参数，表示调用过程的时候需要输入 b IN NUMBER:=20, --是一个输入参数，表示调用过程的时候需要输入 c OUT NUMBER --是一个输出参数，该参数可以被过程返回 ) AS result NUMBER; BEGIN c:=a+b; END; / DECLARE r NUMBER; --声明全局变量 BEGIN proc_count(19,39,r); DBMS_OUTPUT.put_line(‘r的值是:’||r); END; /
作者：lyk1599021827 来源： 原文：https://blog.csdn.net/lyk1599021827/article/details/90489513 版权声明：本文为博主原创文章，转载请附上博文链接！
触发器在数据库以独立的对象存储，他与存储过程和函数不同的是：存储过程与函数需要用户显示调用才执行，而触发器是有一个事件来触发启动运行。即触发器是当某个事件发生时自动隐式运行。并且触发器不能接收参数。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似操作。
触发器分语句触发器（又叫表级触发器）和行级触发器。 语句触发器（又叫表级触发器）对每个DML语句执行一次，而行级触发器则触发多次。
DML（date manipulation language）:他们是SELECT、UPDATE、INSERT、DELETE,就像他的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。 DDL(date definition language)：DDL比DML要多，主要命令有CREATE、ALTER、DROP等，DDL主要用在定义或者改变(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表使用。
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 – 抛出错误-20999到-20000之间 RAISE_APPLICATION_ERROR(-20001,‘在周末不能办理员工离职或者入职手续’)； END IF; END; / –增加数据 INSERT INTO emp（empno,ename,job,sal） SELECT 1008,‘XXX’,‘总裁’,960,FROM dual union SELECT 1009,‘XXX’,‘总裁’,960,FROM dual union SELECT 10010,‘XXX’,‘总裁’,960,FROM dual; **DEMO:**周末以及下班时间不允许更新emp数据表 CREATE OR REPLACE TRIGGER emp_trigger BEFORE UPDATE OF sal,comm --在更新数据(修改薪资或者佣金)之前触发 ON emp –声明变量 DECLARE v_date VARCHAR2(50); v_hour VARCHAR2(50); --保存时间 BEGIN SELECT TO_CHAR(SYSDATE,‘day’),TO_CHAR(SYADATE,‘hh’) INTO v_date,v_hour FROM dual; IF v_date = ‘星期六’ OR v_ date = ‘星期日’ THEN –抛出错误 RAISE_APPLICATION_ERROR(-20001,‘周末不上班’); ELSIF v_hour>18 OR v_hour<11 THEN RAISE_APPLICATION_ERROR(-20002,‘现在是下班时间’)； END IF; END; / –增加数据 DELETE FROM emp WHERE empno=7369；
