PLSQL及过程化：类型定义、判断、循环、游标、例外处理、触发器、存储

最新推荐文章于 2023-04-07 10:24:57 发布

小心的java梦

最新推荐文章于 2023-04-07 10:24:57 发布

阅读量558

点赞数

分类专栏： Oracle 文章标签： plsql oracle 数据库触发器游标

本文链接：https://blog.csdn.net/qq_44775798/article/details/88605652

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一前言

1.SQL99是什么

1）是操作所有关系型数据库（Oracle与mysql都是关系型数据库）的规则
   2）第四代语言
   3）是一种结构化查询语言
   4）只需发出合法合理的命令，就有对应的结果显示

2.SQL特点

1）交互性强，非过程化
   2）数据库操纵能力强，只需发送命令，无须关注如何实现
   3）容易调试，错误提示，直接了当
   4）SQL强调结果

3.PLSQL是什么

PLSQL:是专用于Oracle服务器，在SQL基础上，添加一些过程化控制语句，叫PLSQL
过程化包括：类型定义，判断，循环，游标，例外（异常）处理，触发器，存储概念...
PLSQL强调过程

注：PLSQL就是可以写逻辑判断由一系列的SQL语句组成。PLSQL是弥补SQL的缺陷的，并不是替代SQL的。

4.为什么用PLSQL

因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以得用过程化程序设计语言弥补SQL不足之处
SQL和PLSQL不是替代关系，而是弥补关系

5.PLSQL程序的完整组成结构

[DECLARE]
           定义变量
       BEGIN
           SLQ语句（DML,TCL语句）
           [异常]
       END;
       /

注：在PLSQL程序中，;代表每条语句的结束，/表示整个PLSQL程序结束

6.PLSQL与SQL执行有什么不同

1）SQL是单条执行;
2）PLSQL是整体执行，不能单条执行，整个PLSQL结束用/，其中每条语句结束用;。

二 PLSQL

PLSQL就是可以写逻辑判断由一系列的SQL语句组成。PLSQL是弥补SQL的缺陷的，并不是替代SQL的。

PLSQL语法：

[DECLARE]
           定义变量
       BEGIN
           SLQ语句（DML,TCL语句）
           [异常]
       END;
       /

1、需求

需求：打印helloworld

注：sqlplus默认将输出功能关闭了，我们需要打开
       命令：set serverout on;
       BEGIN
           -- 打印helloworld
           dbms_output.put_line('helloworld');
       END;
       /

2、PLSQL类型定义

需求1：使用基本数据变量，常量和注释，求10+100的和

       DECLARE
			i1 NUMBER := 10; -- 声明变量保存10
			i2 NUMBER := 100; -- 声明变量保存100
			-- result VARCHAR2(255) := '10+100的结果：';  -- 声明字符常量
			sum_i NUMBER; -- 声明变量保存和,不要用sum当做变量
		BEGIN
			sum_i := i1+i2;-- 求和
			
			dbms_output.put_line('10+100的和为' || sum_i);-- 打印结果
		END;
		/

需求2：输出7369号员工姓名和工资，格式如下：7369号员工的姓名是XXX,薪水是XXX。

语法：使用表名.字段名%type

   declare
		pname emp.ename%type;-- 保存名字,数据类型应该与表中的字段类型一样。
		psal emp.sal%type;-- 保存薪水
	
	BEGIN
		-- 使用查询语句将emp表中的用户id为7369的用户的名字和薪水查出来并赋值给pname和psal;
		select ename,sal INTO pname,psal from emp where empno=7369;
		--输出
		dbms_output.put_line('7369号员工的姓名是' || pname || ',薪水是' || psal || '美元');
	END;
	/

需求3：输出7369号员工姓名和工资，格式如下：7369号员工的姓名是XXX,薪水是XXX，

语法：使用表名.字段名%rowtype

DECLARE
		-- 定义变量
		emp_record EMP%rowtype;
	BEGIN
		SELECT * INTO emp_record FROM EMP WHERE EMPNO=7369;
		dbms_output.put_line('7369号员工的名字是' || emp_record.ENAME || '，薪资是' || emp_record.SAL);
	END;
	/

备注：%type和%rowtype区别
       1）当定义变量时，该变量的类型与表中的某字段类型相同时，可以使用%type
       2）当定义变量时，该变量与整个表结构完全相同时，可以使用%rowtype，此时通过变量名.字段名，可以取值
       我们一般使用%type

3、PLSQL的判断语句

1）普通判断
           IF 条件表达式 THEN
               要做的事
           ELSE
               要做的事
           END IF;

2）多重判断

           IF 条件判断 THEN
               要做的事
           ELSIF 条件判断 THEN
               要做的事
           ......
           ELSE
               要做的事
           END IF;

例：

需求1：使用if-else-end if显示今天星期几，是"工作日"还是"休息日"，周一到周五工作日，周六日是休息日（普通判断）

  declare
		pday varchar2(9);    --声明变量保存星期几
	BEGIN
		-- 将星期几查出来，  syqdate:获取当前时间
		SELECT TO_CHAR(SYSDATE,'day') INTO pday FROM DUAL;
		-- 判断是否是休息日
		IF pday IN('星期六','星期日')
		THEN
			dbms_output.put_line(pday || '休息日');
		ELSE 
			dbms_output.put_line(pday || '工作日');
		END IF;
	END;
	/

需求2：从键盘接收值，使用if-elsif-else-end if显示"age<16"，"age<30"，"age<60"，"age<80"（多重判断）

    declare
		age number(3) := &年龄;     -- 年龄，使用的是键盘录入的方式（即可以手动输入），但是在navicat中键盘录入不能使用，只在sqlplus中支持
	BEGIN
		IF age < 18 THEN
			dbms_output.put_line('未成年人');
		ELSIF age < 65 THEN
			dbms_output.put_line('青年人');
		ELSIF age < 90 THEN
			dbms_output.put_line('老年人');
		ELSE 
			dbms_output.put_line('长寿老年人');
		END IF;
	END;
	/

4、循环语句

1）loop
2）for
3）while

案例1：使用loop计算前100的和

语法：
LOOP

statement1;

......

EXIT [WHEN condition];

END LOOP;

declare
	sum_i number(4) := 0;      -- 和的值
	i number(3) := 1;          -- 当前值

BEGIN
	LOOP
		--累加
		sum_i := sum_i+i;      -- 相当于sum_i += i;  但不支持这种写法
		-- 循环变量递增
		i := i+1; 
		-- 退出条件
		EXIT WHEN i=101;
	END LOOP;              --结束循环
	-- 输出sum_i的值
	dbms_output.put_line('1-100的和是：' || sum_i);
END;
/

案例2：使用while循环向xxx_emp表中添加999条数据

复制一张表的写法：

--复制emp表
CREATE TABLE xxx_emp
AS
SELECT empno,ename from emp;


--清空xxx_emp中的数据
delete from xxx_emp;

语法：
WHILE condition LOOP

statement1;

statement2;

.....

END LOOP;

-- 循环添加数据
declare
	i number(4) :=1;--记录循环次数
BEGIN
	WHILE i<1000 LOOP
		-- 添加数据
		INSERT INTO xxx_emp (empno,ename) values (i,'张三');
		--循环变量递增
		i := i+1;
	END LOOP;
END;
/

案例3：使用for循环向xxx_emp表中添加100条数据
语法：循环变量自动+1
FOR counter in [REVERSE] lower_bound. .upper_bound LOOP

statement1;

statement2;

.......

END LOOP;

DECLARE
	i number(11) := 2000;   -- 声明变量保存初始值

BEGIN
	FOR i IN 2000..2099 LOOP
		INSERT INTO xxx_emp (empno,ename) values (i,'张三');
	END LOOP;
END;
/

案例4：使用for循环显示20-30

  declare
		i number(2) := 20;	
	begin
		for i in 20..30 loop
			dbms_output.put_line(i);
		end loop;
	end;
	/

案例5：提交事务（提交事务后才能把数据保存到数据库）

-- 循环添加数据2000000条
DECLARE
	i number(7) :=1;
BEGIN
	FOR i IN 1..2000000 LOOP
		--添加数据
		INSERT INTO xxx_emp(empno,ename) VALUES (i,'张三');
	END LOOP;
	--提交事务
	COMMIT;
END;
/


COMMIT;     --提交事务，写完sql语句后，可以提交一下

、PLSQL例外 (Exception)

1. 什么是例外

可以理解为我们Java中的异常

2. 语法

BEGIN
   EXECPTION
       WHEN 例外名 then
       处理;
   END;
   /

3. 案例

案例1：使用oracle系统内置例外，演示除0例外【例外名：zero_divide】

--使用oracle系统内置例外，演示除以0例外【zero_divide】
DECLARE 
	result number(5); --保存商
BEGIN 
	result := 10/0;
	dbms_output.put_line(result);
	EXCEPTION
		WHEN zero_divide THEN
		dbms_output.put_line('除0了');
END;
/

案例2：使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【例外名：no_data_found】

--使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【例外名：no_data_found】
DECLARE
	pename varchar2(20);  --存员工姓名
BEGIN 
	SELECT ENAME INTO PENAME FROM EMP WHERE DEPTNO=100;
	dbms_output.put_line(pename); --打印员工姓名
	EXCEPTION
	--出现异常(即没有这个用户)
		WHEN no_data_found THEN
		dbms_output.put_line('该用户不存在');
END;
/

、触发器 (Trigger)

触发器类似于过滤器。用户在做修改，添加，删除的时候会触发对应的触发器，触发器是我们保证数据安全的最后一道屏障了。可以验证操作是否合法（买火车票的时候，只能在早上七点以后到晚上九点其他的时间用户虽然可以查询信息，但是是不能做添加修改删除操作的）
触发器在项目中是非常重要的，它是保证数据安全的最后一道屏障

1.什么是触发器

不同的DML操作（update/delete/insert）操作，触发器能够进行一定的拦截，符合条件的操作才可以操作基表，否则不可操作基表，类似于javaweb中的filter过滤器，框架中的interceptor拦截器

2.为什么要用触发器？

如果没有触发器，那么DML（DML为数据操纵语言）的所有操作，均可无限制的操作基表，可能有些操作不符合业务需求。

3.语法

CREATE [OR REPLACE] TRIGGER 触发器名
   {BEFORE|AFTER}-- 操作前|后触发
   {INSERT|DELETE|---语句级
   UPDATE OF 列名}----行级
   ON 表名
   [FOR EACH ROW]
   PLSQL块 [declare ... begin ... end;/]

4. 案例

-创建语句级触发器：insertEmpTrigger:
--当对表【emp】进行增加【insert】操作前【before】，显示"hello world"
--当对表【emp】进行增加【insert】操作后【after】，显示"添加成功"

--创建语句级触发器insertEmpTrigger:
--当对表【emp】进行增加【insert】操作前【before】，显示"hello world"
--当对表【emp】进行增加【insert】操作后【after】，显示"添加成功"

--创建触发器
	CREATE OR REPLACE TRIGGER insertEMPTrigger
	BEFORE   --之前
	INSERT   --增加操作
	ON EMP   --操作的是emp表
	BEGIN
			--输出语句
			dbms_output.put_line('hello world');
	END;
	/

--创建触发器
CREATE OR REPLACE TRIGGER insertAFTEREMPTrigger
	AFTER   --之后
	INSERT   --增加操作
	ON EMP   --操作的是emp表
	BEGIN
			--输出语句
			dbms_output.put_line('添加成功');
	END;
	/

insert into emp(empno,ename) values(0,'天天');
insert into emp (empno,ename,sal) values (3333,'aaa',7000);

案例2; 创建删除数据触发器

--当对表【emp】进行删除【delete】操作前【before】，显示"准备删除”

--当对表【emp】进行删除【delete】操作前【before】，显示"准备删除"
--当对表【emp】进行删除【delete】操作后【after】，显示"删除成功------"

--删除数据触发器
CREATE OR REPLACE TRIGGER deleteEmpTrigger
BEFORE --之前
DELETE --删除
ON EMP --操作emp表
BEGIN
	--输出语句
	dbms_output.put_line('准备删除');
END;
/
--删除数据触发器
CREATE OR REPLACE TRIGGER deleteAFTERTrigger
AFTER   --之后
DELETE  --删除
ON EMP	--操作emp表
BEGIN
	--输出语句
   dbms_output.put_line('删除成功-----');
END;
/

DELETE FROM EMP WHERE ENAME='天天';
DELETE FROM EMP WHERE Empno=7;

案例3：删除触发器insertEmpTrigger

语法：drop trigger 触发器名

drop trigger insertEmpTrigger;

5.总结

1.使用insert语句插入N条记录，问触发器工作几次？
答：N次

DECLARE
	i number := 1;
BEGIN
		LOOP
		  --添加
				INSERT INTO EMP (EMPNO,ENAME) VALUES(i,'张三')
				i :=i+1;
				EXIT WHEN i=100;
				
		END LOOP;

END;
/

2.使用delete语句删除N条记录，引起触发器工作

触发器的工作次数：取决于delete语句执行几次。

DECLARE
	i number := 1;
	BEGIN
		LOOP
			delete from emp;
			i := i+1;
		EXIT WHEN i=100;
		END LOOP;
	END;
/

3. 删除触发器，表还在吗？表还在

4.将表丢到回收站，触发器还在吗？触发器还在

5.当闪回表后，触发器会在吗？在，可以用

6.彻底删除表，触发器会在吗？触发器不在了

7.总结：
触发器可以在对表做 增删改 时自动触发
做修改时需要些 for each row。

6. 练习

案例：--星期一到星期五，且9-20点能向数据库emp表插入数据，否则使用函数抛出异常,提示用户不是工作时间请休息
--语法：raise_application_error('-20000','例外原因')

--星期一到星期五，且9-20点能向数据库emp表插入数据，否则使用函数抛出异常,提示用户不是工作时间请休息
--语法：raise_application_error('-20000','例外原因')

CREATE OR REPLACE TRIGGER securityTrigger
before
INSERT
ON EMP
DECLARE
	pday varchar2(10); --保存周几
	phour number(2);   --保存时间
BEGIN	
	--获取星期
	SELECT TO_CHAR(sysdate,'day') into pday from dual;
	--获取时间
	SELECT TO_CHAR(sysdate,'hh24') into phour from dual;
	--业务逻辑
	IF pday in('星期六','星期日') OR phour not BETWEEN 9 and 20 THEN
		--抛出例外
		raise_application_error('-20000','非工作时间，不能向数据库添加数据');
		END IF;
END;
/

insert into EMP(EMPNO,ENAME) VALUES(11,'aa');

案例2：--创建行级触发器checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，
--语法：for each row/:new.sal/:old.sal

--创建行级触发器checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，
--语法：for each row/:new.sal/:old.sal
CREATE OR REPLACE TRIGGER checkSalaryTrigger
after
UPDATE of sal
ON EMP
for each row --修改时需要该条数据
BEGIN
	--如果涨后工资>涨前工资  抛出例外
	if :new.sal <= :old.sal then
		--抛出例外
		raise_application_error('-20200','工资不能越涨越低');
	end if;	
END;
/

DROP TRIGGER checkSalaryTrigger;