当前位置：首页 > 数据库 > oracle > 详细页面

PLSQL一些常用知识点梳理总结

时间：2023-10-31来源：系统城装机大师作者：佚名

1、背景

此处简单的记录一下在 oracle中如何使用plsql语法，记录一些简单的例子，防止以后忘记。

2、变量的声明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36 declare
-- 声明变量
v_name varchar2(20);
-- 此变量由 select into 赋值
v_man_sex number;
-- v_sex 变量的类型和 student表中的 sex 字段的类型一致
v_sex student.sex%TYPE;
-- v_row 中保存的是 student表中的一整行字段, 也可以是游标中的一整行
v_row student%rowtype;
-- 声明变量并赋值
v_addr varchar2(100) := '湖北省';
-- 声明日期变量
v_date date := sysdate;
-- 定义一个记录类型
type STUDENT_INFO is record
(
student_id student.student_id%TYPE,
student_name student.student_name%TYPE
);
-- 定义基于记录的嵌套表
type nested_student_info is table of STUDENT_INFO;
-- 声明变量
student_list nested_student_info;
begin
-- 直接赋值
v_name := '直接赋值';
v_date := to_date('2023-12-12', 'yyyy-mm-dd');
-- 单个字段语句赋值
select count(*) into v_man_sex from student where sex = 1;
-- 多个字段赋值
select student_name,sex into v_name,v_sex from student where student_id = 'S003';
-- 获取一行数据 ( 此处需要查询出所有的字段，否则可能报错 )
select student_id,student_name,sex,CREATE_TIME into v_row from student where student_id = 'S002';
-- 打印输出
DBMS_OUTPUT.PUT_LINE('日期:' || v_date || '姓名:' || v_name || ',' || v_row.STUDENT_NAME || ' 男生人数:' || v_man_sex || ' 地址:' || v_addr );
end;

3、if 判断

统计总共有多少个学生，并进行if判断。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 declare
-- 声明一个变量，记录有多少个学生
v_student_count number;
begin
-- 给 v_student_count 变量赋值
select count(*) into v_student_count from student;
-- 执行if判断
if v_student_count > 3 then
DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']>3');
elsif v_student_count >=2 then
DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]');
else
DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']<2');
end if;
end;

4、case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 -- case
declare
-- 声明一个变量，记录有多少个学生
v_student_count number;
begin
-- 给 v_student_count 变量赋值
select count(*) into v_student_count from student;
-- 执行if判断
case when v_student_count > 3 then
DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']>3');
when v_student_count >=2 then
DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]');
else
DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']<2');
end case;
end;

5、循环

输出1到100

1、loop 循环

1
2
3
4
5
6
7
8
9
10
11
12 declare
-- 定义一个变量并赋值
v_count number := 1;
begin
loop
-- 提出条件
exit when v_count > 100;
DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count);
-- v_count 加1
v_count := v_count + 1;
end loop;
end;

2、while 循环

1
2
3
4
5
6
7
8
9
10
11 -- while 循环
declare
-- 定义一个变量并赋值
v_count number := 1;
begin
while v_count <= 100 loop
DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count);
-- v_count 加1
v_count := v_count + 1;
end loop;
end;

3、for循环

1
2
3
4
5
6
7
8
9 -- for 循环
declare
-- 定义一个变量
v_count number;
begin
for v_count in 1..100 loop
DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count);
end loop;
end;

6、游标

1、无参数的游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 -- 游标
declare
-- 声明一个游标
cursor cur_student is select student_id,student_name,sex from student;
-- 声明变量
row_cur_student cur_student%rowtype;
begin
-- 打开游标
open cur_student;
-- 遍历数据
loop
-- 获取一行数据
fetch cur_student into row_cur_student;
-- 退出
exit when cur_student%NOTFOUND;
-- 执行业务逻辑（此句如果移动到exit when上方，则可能会多打印一句）
DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME);
end loop;
-- 关闭游标
close cur_student;
end;

2、带参数的游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 declare
-- 声明一个游标, 需要传递v_student_id参数
cursor cur_student(v_student_id student.student_id%TYPE) is
select student_id,student_name,sex from student where student_id = v_student_id;
-- 声明变量
row_cur_student cur_student%rowtype;
-- 此变量通过查询获取值，然后带到游标中
v_query_student_id student.student_id%TYPE;
begin
-- 打开游标
--参数传递方式一： open cur_student('S001');
-- 参数传递方式二：
select 'S001' into v_query_student_id from dual;
open cur_student(v_query_student_id);
-- 遍历数据
loop
-- 获取一行数据
fetch cur_student into row_cur_student;
-- 退出
exit when cur_student%NOTFOUND;
-- 执行业务逻辑（此句如果移动到exit when上方，则可能会多打印一句）
DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME);
end loop;
-- 关闭游标
close cur_student;
end;

7、执行ddl dml

需要放到 execute immediate中执行，否则会报错。

1
2
3
4
5
6
7
8
9
10
11 declare
v_table_name varchar2(20) := 'student_bak';
-- 拼接一个动态SQL
v_sql varchar2(100);
begin
execute immediate 'create table student_bak as select * from student';
execute immediate 'alter table student_bak add new_cloumn varchar2(20)';
-- 带变量的执行
v_sql := 'drop table ' || v_table_name;
execute immediate v_sql;
end;

8、存储过程

1、无参数的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 -- 无参数的存储过程
create or replace procedure sp_print_all_student
is
-- 声明一个游标
cursor c_all_student is select student_id,student_name from student;
-- 声明一个变量
row_student c_all_student%rowtype;
begin
-- 循环游标
for row_student in c_all_student loop
DBMS_OUTPUT.PUT_LINE(row_student.STUDENT_ID || ' ' || row_student.STUDENT_NAME);
end loop;
end;
-- 调用
begin
SP_PRINT_ALL_STUDENT();
end;

2、有输入输出参数的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 -- 有参数的存储过程
create or replace procedure sp_find_student(/** 输入参数 */ i_student_id in student.student_id%TYPE,
/** 输出参数 */ o_student_name out student.student_name%TYPE)
IS
-- 定义变量并赋值
v_student_id varchar2(64) := i_student_id;
begin
DBMS_OUTPUT.PUT_LINE('v_student_id:' || v_student_id);
-- 将查询到的 student_name 赋值到 o_student_name
select student_name into o_student_name from student where student_id = i_student_id;
end;
declare
-- 定义一个变量用于接收存储过程的返回值
output_student_name student.student_name%TYPE;
begin
sp_find_student('S001', output_student_name);
-- 输出存储过程的返回值
DBMS_OUTPUT.PUT_LINE(output_student_name);
end;

3、merge into 的使用

存在更新，不存在插入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 create or replace procedure sp_merge_into(i_student_id in varchar2)
IS
begin
-- 如果 using 中查询出来的数据，通过 on 条件匹配的话，则更新 student_bak表，否则插入student_bak表
merge into STUDENT_BAK t
using (select * from student where student_id = i_student_id) s
on ( t.student_id = s.student_id )
when matched then update set
-- t.STUDENT_ID = s.STUDENT_ID, on中的条件不可更新
t.STUDENT_NAME = s.STUDENT_NAME,
t.SEX = s.SEX,
t.CREATE_TIME = s.CREATE_TIME
when not matched then insert(student_id, student_name, create_time) values (
s.STUDENT_ID,
s.STUDENT_NAME,
s.CREATE_TIME
);
commit ;
end;

4、测试异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 create or replace procedure sp_error
IS
v_num number;
begin
DBMS_OUTPUT.PUT_LINE('测试异常');
-- 产生异常
v_num := 1 / 0;
exception -- 存储过程异常
when too_many_rows then
dbms_output.put_line('返回值多于1行');
when others then
-- 异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定
rollback;
dbms_output.put_line('错误码：' ||sqlcode);
dbms_output.put_line('异常信息：' || substr(sqlerrm, 1, 512));
end;
begin
sp_error();
end;

5、bulk into & record

1、select into 中使用 bulk into & record

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 create or replace procedure sp_bulk_collect_01
IS
-- 定义一个记录类型
type STUDENT_INFO is record
(
student_id student.student_id%TYPE,
student_name student.student_name%TYPE
);
-- 定义基于记录的嵌套表
type nested_student_info is table of STUDENT_INFO;
-- 声明变量
student_list nested_student_info;
begin
-- 使用 bulk collect into 将所获取的结果集一次性绑定到记录变量 student_list 中
select student_id,student_name bulk collect into student_list from student;
-- 遍历
for i in student_list.first .. student_list.last loop
DBMS_OUTPUT.PUT_LINE('studentId:' || student_list(i).student_id || ' studentName:' || student_list(i).student_name);
end loop;
end;
begin
sp_bulk_collect_01;
end;

2、fetch into 中使用 bulk into & forall

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 -- bulk collect
create or replace procedure sp_bulk_collect_02
IS
-- 定义一个游标
cursor cur_student is select student_id,student_name,sex,create_time from student;
-- 定义基于游标的嵌套表
type nested_student_info is table of cur_student%rowtype;
-- 声明变量
student_list nested_student_info;
begin
-- 打开游标
open cur_student;
loop
-- 一次获取2条数据插入到 student_list 中
fetch cur_student bulk collect into student_list limit 2;
-- 退出
--exit when student_list%notfound; 不可使用这种方式
exit when student_list.count = 0;
-- 输出
for i in student_list.first .. student_list.last loop
DBMS_OUTPUT.PUT_LINE('studentId:' || student_list(i).student_id || ' studentName:' || student_list(i).student_name);
end loop;
-- 使用 forall 更新数据, 可以将多个dml语句批量发送给SQL引擎，提高执行效率。
forall i in student_list.first .. student_list.last
update student set student_name = student_list(i).STUDENT_NAME || '_update' where student_id = student_list(i).STUDENT_ID;
commit ;
end loop;
-- 关闭游标
close cur_student;
end;
begin
sp_bulk_collect_02;
end;

6、接收数组参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14 -- 创建StudentIdList数组的长度是4，每一项最多存20个字符
create or replace type StudentIdList as varray(4) of varchar2(20);
-- 创建存储过程，接收数组参数
create or replace procedure sp_param_list(studentIdList in StudentIdList)
is
begin
for i in 1..studentIdList.COUNT loop
DBMS_OUTPUT.PUT_LINE('studentId:' || studentIdList(i));
end loop;
end;
declare
begin
sp_param_list(STUDENTIDLIST('d','c','S001','S0021222222222233'));
end;

7、接收数组对象，并将数组对象转换成表使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 -- 创建数据库对象
create or replace type StudentInfo is object(
studentId varchar2(64),
studentName varchar2(64)
);
-- 创建数组对象
create or replace type StudentInfoArr as table of StudentInfo;
-- 创建存储过程
create or replace procedure sp_param_list_02(arr in StudentInfoArr)
is
-- 声明一个变量，记录传递进来的arr的数量
v_student_count number := 0;
begin
-- 传递进来的数组转换成使用
select count(*) into v_student_count from table(cast(arr AS StudentInfoArr))
where studentId like 'S%';
DBMS_OUTPUT.PUT_LINE('传递进来学生学号以S开头的学生有: ' || v_student_count || '个');
-- 输出列表参数
for i in 1..arr.COUNT loop
DBMS_OUTPUT.PUT_LINE('studentId:' || arr(i).studentId || ' studentName:' || arr(i).studentName);
end loop;
end;
declare
begin
sp_param_list_02(arr => StudentInfoArr(StudentInfo('S001','张三'),StudentInfo('S002','李四')));
end;

8、返回多个参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 create or replace procedure sp_return_value(stuInfoList out Sys_Refcursor)
IS
begin
open stuInfoList for select STUDENT_ID,STUDENT_NAME,SEX from STUDENT;
end;
declare
stu Sys_Refcursor;
v_student_id STUDENT.STUDENT_ID%TYPE;
v_student_name STUDENT.STUDENT_NAME%TYPE;
v_sex STUDENT.SEX%TYPE;
begin
SP_RETURN_VALUE( stu);
loop
fetch stu into v_student_id,v_student_name,v_sex;
exit when stu%notfound;
DBMS_OUTPUT.PUT_LINE('studentId:' || v_student_id || ' studentName: ' || v_student_name);
end loop;
end;

9、程序包 package

1、定义包头

包头可以简单的理解java中的接口。

1
2
3
4
5
6 create or replace package pkg_huan as
v_pkg_name varchar2(30) := 'pkg_huan';
function add(param1 in number, param2 in number) return number;
procedure sp_pkg_01;
procedure sp_pkg_02(param1 in varchar2);
end pkg_huan;

2、实现包体

包体可以简单的理解java中的实现接口的类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 create or replace package body pkg_huan as
-- 实现function
function add(param1 in number, param2 in number) return number IS
begin
return param1 + param2;
end;
-- 实现无参数的存储过程
procedure sp_pkg_01 as
begin
DBMS_OUTPUT.PUT_LINE('package name:' || v_pkg_name || 'procedure name: sp_pkg_01');
end;
-- 实现有参数的存储过程
procedure sp_pkg_02(param1 in varchar2) as
begin
DBMS_OUTPUT.PUT_LINE('param1:' || param1);
end;
end;

3、调用包中的方法或存储过程

1
2
3
4
5
6
7
8 begin
-- 调用方法
DBMS_OUTPUT.PUT_LINE('1+2=' || PKG_HUAN.add(1,2));
-- 调用无参数的存储过程
PKG_HUAN.sp_pkg_01();
-- 调用有参数的存储过程
PKG_HUAN.sp_pkg_02(12);
end;