Oracle 中的 PL/SQL

一、前言

PL/SQL语句在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有了过程处理的能力。

基本语法结构：

declare
	--Local variable here
begin
	--Test statement here
exception
	-- exception handlers
end;
1
2
3
4
5
6
7

和学其他语言一样，在最开始的时候我们试一试打印hello world!

begin
	set serveroutput on;	-- 设置控制台输出内容可见
	DBMS_output.put_line('hello world!');
end;
1
2
3
4

二、基本语法 —— 变量

2.1、普通变量

声明变量的方式为： 变量名 变量类型，例如：

s_id number; 
s_name varchar2(4);
1
2

为变量赋值则有两种方法：

1、在DECLARE声明部分中通过:=符号直接赋值，例如：

s_name varchar2(4) := '黑猫几绛'
1

2、在方法体内通过select 值 into 变量 from 表名称语句赋值，例如：

declare
	s_name varchar2(4);
	s_id number
begin
	s_id := 1902;
	select '黑猫几绛' into s_name from studnt_table;
	DBMS_output.put_line('姓名为:' || s_name || ',id为:' || s_id);	--这里的||类似于字符串拼接时的 + 号
end;
1
2
3
4
5
6
7
8

2.2、引用型变量

引用型变量的类型和长度取决于表中字段的类型和长度，通过表名.列名%TYPE可以指定变量的类型和长度，例如：

-- 直接通过表.列的方式拿到目标变量类型
s_name_var student_table.student_name%TYPE;

-- 等价写法
s_name_var varchar2(4);
1
2
3
4
5

这样定义变量类型，可以让我们不必再次查表看某个变量的类型，需要哪张表的类型直接引用即可。

2.3、记录型变量

接受表中的一整行记录，相当于Java中的一个对象。其语法为：变量名称 表名%ROWTYPE;

还是上面的那个练习：查询employee_table表中1902号员工的个人信息，打印他的id和name。

declare
	e employee_table%rowtype;
begin
	select * into e from employee_table where employee_id = 1902;
	DBMS_output.put_line('姓名为:' || e.employee_name || ',id为:' || e.employee_id);	
end;
1
2
3
4
5
6

三、流程控制

3.1、条件分支

需要注意的大概有两点：

1、else if 在这里写法为 elsif
2、在判断的最后要写上end if 表示判断结束

begin
	if 条件1 
		THEN 执行1;
	elsif 条件2 
		THEN 执行2;
	else 
		执行3;
	end if;
end;
1
2
3
4
5
6
7
8
9

3.2、循环分支

语法：

begin
	loop
		exit when 退出循环的条件;
		循环内部语句;
	end loop;
end;
1
2
3
4
5
6

举个例子，打印数字 1-10：

declare
	i number := 1
begin
	loop
		exit when i > 10;
		DBMS_output.put_line(i);	
		i := i + 1;	--无法使用自增
	end loop;
end;
1
2
3
4
5
6
7
8
9

四、游标

游标用于临时存储一个查询返回的多行数据，像是jdbc技术中的Resultset集合，存储所有查询到的数据集合。面对这样的集合，我们可以通过遍历游标，逐行访问该结果集的数据。

游标的使用方式：

声明->打开->读取->关闭

语法：

游标的声明：

cursor 游标名[(参数列表)] is 查询语句;
1

游标的打开：

open 游标名;
1

读取：

fetch 游标名 into 变量列表;
1

关闭：

close 游标名;
1

属性：

%rowcount，获得fetch语句返回的记录条数

%found，表示是否返回了数据

%notfound，表示没有返回数据

%isopen，判断游标是否已经打开

举个例子：通过游标查询employee表中所有员工的姓名和工资，并将其依次打印出来。

首先，我们要创建游标，接收所有查询数据的集合：

declare
	cursor c_emp is select ename, esal from emp;
	-- 声明变量参数负责接收游标中的数据
	e_name emp.ename%TYPE;
	e_sal emp.esal%TYPE;
begin
end;
1
2
3
4
5
6
7

现在我们获取到了数据集合，若想拿到每一条数据，我们可以使用循环的方法来实现：

declare
	cursor c_emp is select ename, esal from emp;
	-- 声明变量参数负责接收游标中的数据
	e_name emp.ename%TYPE;
	e_sal emp.esal%TYPE;
begin
	-- 首先得开启游标
	open c_emp;
        -- 然后开启循环
        loop
        	--%notfound默认是false，所以我们需要先fetch取数据然后再进行判断
        	fetch c_emp into e_name,e_sal;
        	-- 设置循环退出条件
        	exit when c_emp%notfound;
        	-- 如果满足循环条件，即集合中有数据时再打印数据
        	DBMS_output.put_line(e_name || ' ' || e_sal);
        end loop;
	close c_emp;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

游标也传递参数，还是以上面的例子来说，只不过这里查询的是部门编号为2019的员工信息：

五、自定义函数

其实自定义函数和存储过程大致上差不多，最大的区别在于自定义函数可以设置返回值。

语法：

create or replace function function_name (参数列表)
return 参数类型
is / as
	定义变量;	-- 和存储过程一样，不需要使用declare
begin
	执行过程;
	return 定义变量;
end;
1
2
3
4
5
6
7
8

举个例子：模拟实现abs()函数：

create or replace my_abs(my_num number) return number
is
vnum number := my_number;
begin
	if vnum >=0
		then vnum := vnum;
	else
		vnum := -vnum;
	end if;
end;
1
2
3
4
5
6
7
8
9
10

六、存储过程

之前我们编写的PLSQL程序可以进行表的操作，判断，循环逻辑处理的工作，但无法重复调用，这就好比是之前的代码全都编写在了main方法中，在每次的代码调用中只能执行一次。

平时我们写代码的时候，会将重复度高的逻辑封装为一个方法来解决复用的问题，这样的封装思想放在PLSQL中被称为存储过程：

语法：

-- [] 表示参数列表是可选项
create or replace procedure 存储过程名[(参数列表)] is/as	
-- 这里可以直接声明变量，无需在begin前加上declare声明
begin

end 存储过程名;
1
2
3
4
5
6

根据参数类型，可以将存储过程分为：

1、无参存储
2、有输入参数的存储
3、有输入（相当于形参）、输出（相当于返回值）参数的存储

6.1、无参存储

举个例子：封装一个可以输出hello world的存储过程。

create or replace procedure my_hello is/as	
word varchar(50) := 'hello world';
begin

DBMS_output.put_line(word);

end my_hello;
1
2
3
4
5
6
7

然后在别的文件中就可以执行任意次该函数：

begin
	exec my_hello;
end;
1
2
3

6.2、有输入参数的存储

举个例子：以存储过程的方式，打印并查询employee_table表中1902号员工的名称和薪水。

首先想，我们需要根据员工号进行信息查询，既然这是一个封装好的方法，那我们可以将员工号通过参数的方式传入到存储过程中：

-- 参数名 参数类型
print_info(id employee_table.employee_id);
1
2

但是我们前面说，在存储过程中有输入参数和输出参数这两种参数，为了更好的区别他们，我们可以在参数名和参数类型中间添加in、out标识符来区分：

create or replace procedure print_info(id in employee_table.employee_id%TYPE) is
	e_name employee_table.employee_name%TYPE;
	e_salary employee_table.employee_salary%TYPE;
begin
	select employee_name, employee_salary into e_name, e_salary from employee_table where employee_id = id;
	DBMS_output.put_line('姓名为:' || e_name || ',薪水为:' || e_salary);	
end print_info;

1
2
3
4
5
6
7
8

然后在别的文件中就可以执行任意次该函数：

begin
	exec print_info(1902);
end;
1
2
3

6.3、有输入、输出参数的存储

举个例子：以存储过程的方式，查询employee_table表中1902号员工的信息，并将他的薪水作为返回值输出，给调用的程序使用。

和上一节的差别不大，通过out标识符表示返回即可。

create or replace procedure ret_salary(
    id in employee_table.employee_id%TYPE,
    salary out employee_table.employee_salary%TYPE
) is
begin
	select employee_salary into salary from employee_table where employee_id = id;
end ret_salary;

1
2
3
4
5
6
7
8

然后在别的文件中就可以执行任意次该函数：

declare
	my_salary employee_table.employee_salary%TYPE
begin
	exec ret_salary(1902,my_salary);
	DBMS_output.put_line('薪水为:' || my_salary);	
end;
1
2
3
4
5
6

七、触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

根据触发器的触发时机可以分为前置触发器(before)和后置触发器(after)

语法：

create or replace trigger my_trigger 
before | after --表示是在修改表操作前还是后
[insert] [or update [of 列名(可以有很多个)]] [or delete]
ON 表名称
-- 表明是对表的每一行触发器执行一次，如果没有这一项则是对整个表执行一次
-- 比如delete from student_table，如果加了这句话，就是在删除表的时候对每一行进行触发器操作，否则是对整张表执行触发器
for each row	
when inserting | updating | deleting 
-- 下面和写plsql相同
declare
begin

end;
1
2
3
4
5
6
7
8
9
10
11
12
13

在触发器中触发语句还有一个特殊的属性伪记录变量：

:old表示触发语句前，某个变量的值

:new表示触发语句后的新值
1
2
3

举个前置触发器的例子：当用户每次输入一个数字之后，自动算出两次数字之间的差值

create or replace trigger num_trigger
before
update of num
on num_table
for each row
declare
begin
	:new.change_num := :new.num - :old.num;
end;
1
2
3
4
5
6
7
8
9

再举个后置触发器的例子：当用户修改信息表的姓名数据后，自动记录修改前后的姓名信息

create or replace trigger name_trigger
after
update of name
on name_table
for each row
declare
begin
	insert into name_table values(:new.id,:new.name,:old.name);
end;
1
2
3
4
5
6
7
8
9