一、前言
PL/SQL语句在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL语言具有了过程处理的能力。
基本语法结构:
declare
--Local variable here
begin
--Test statement here
exception
-- exception handlers
end;
和学其他语言一样,在最开始的时候我们试一试打印hello world!
begin
set serveroutput on; -- 设置控制台输出内容可见
DBMS_output.put_line('hello world!');
end;
二、基本语法 —— 变量
2.1、普通变量
声明变量的方式为: 变量名 变量类型,例如:
s_id number;
s_name varchar2(4);
为变量赋值则有两种方法:
1、在DECLARE声明部分中通过:=符号直接赋值,例如:
s_name varchar2(4) := '黑猫几绛'
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;
2.2、引用型变量
引用型变量的类型和长度取决于表中字段的类型和长度,通过表名.列名%TYPE可以指定变量的类型和长度,例如:
-- 直接通过表.列的方式拿到目标变量类型
s_name_var student_table.student_name%TYPE;
-- 等价写法
s_name_var varchar2(4);
这样定义变量类型,可以让我们不必再次查表看某个变量的类型,需要哪张表的类型直接引用即可。
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;
三、流程控制
3.1、条件分支
需要注意的大概有两点:
1、else if 在这里写法为 elsif
2、在判断的最后要写上end if 表示判断结束
begin
if 条件1
THEN 执行1;
elsif 条件2
THEN 执行2;
else
执行3;
end if;
end;
3.2、循环分支
语法:
begin
loop
exit when 退出循环的条件;
循环内部语句;
end loop;
end;
举个例子,打印数字 1-10:
declare
i number := 1
begin
loop
exit when i > 10;
DBMS_output.put_line(i);
i := i + 1; --无法使用自增
end loop;
end;
四、游标
游标用于临时存储一个查询返回的多行数据,像是jdbc技术中的Resultset集合,存储所有查询到的数据集合。面对这样的集合,我们可以通过遍历游标,逐行访问该结果集的数据。
游标的使用方式:
声明->打开->读取->关闭
语法:
游标的声明:
cursor 游标名[(参数列表)] is 查询语句;
游标的打开:
open 游标名;
读取:
fetch 游标名 into 变量列表;
关闭:
close 游标名;
属性:
%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;
现在我们获取到了数据集合,若想拿到每一条数据,我们可以使用循环的方法来实现:
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;
游标也传递参数,还是以上面的例子来说,只不过这里查询的是部门编号为2019的员工信息:
五、自定义函数
其实自定义函数和存储过程大致上差不多,最大的区别在于自定义函数可以设置返回值。
语法:
create or replace function function_name (参数列表)
return 参数类型
is / as
定义变量; -- 和存储过程一样,不需要使用declare
begin
执行过程;
return 定义变量;
end;
举个例子:模拟实现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;
六、存储过程
之前我们编写的PLSQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用,这就好比是之前的代码全都编写在了main方法中,在每次的代码调用中只能执行一次。
平时我们写代码的时候,会将重复度高的逻辑封装为一个方法来解决复用的问题,这样的封装思想放在PLSQL中被称为存储过程:
语法:
-- [] 表示参数列表是可选项
create or replace procedure 存储过程名[(参数列表)] is/as
-- 这里可以直接声明变量,无需在begin前加上declare声明
begin
end 存储过程名;
根据参数类型,可以将存储过程分为:
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;
然后在别的文件中就可以执行任意次该函数:
begin
exec my_hello;
end;
6.2、有输入参数的存储
举个例子:以存储过程的方式,打印并查询employee_table表中1902号员工的名称和薪水。
首先想,我们需要根据员工号进行信息查询,既然这是一个封装好的方法,那我们可以将员工号通过参数的方式传入到存储过程中:
-- 参数名 参数类型
print_info(id employee_table.employee_id);
但是我们前面说,在存储过程中有输入参数和输出参数这两种参数,为了更好的区别他们,我们可以在参数名和参数类型中间添加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;
然后在别的文件中就可以执行任意次该函数:
begin
exec print_info(1902);
end;
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;
然后在别的文件中就可以执行任意次该函数:
declare
my_salary employee_table.employee_salary%TYPE
begin
exec ret_salary(1902,my_salary);
DBMS_output.put_line('薪水为:' || my_salary);
end;
七、触发器
数据库触发器是一个与表相关联的、存储的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;
在触发器中触发语句还有一个特殊的属性伪记录变量:
:old表示触发语句前,某个变量的值
:new表示触发语句后的新值
举个前置触发器的例子:当用户每次输入一个数字之后,自动算出两次数字之间的差值
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;
再举个后置触发器的例子:当用户修改信息表的姓名数据后,自动记录修改前后的姓名信息
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;