Oracle PLSQL
PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.
本系列笔记可能会遇到的专业词汇有：
- procedure, 存储过程
- function, 函数
- trigger, 触发器
- package/package body, 包和包体
PLSQL基础
- PLSQL的英文全称是
procedural language struct query language，翻译成中文的意思是
过程化语言与结构化查询语的结合体。
使用PLSQL编写的应用程序只能运行在oracle数据库上，它是oracle特有的编程语言。 - PLSQL与SQL相比具有的优势
SQL作为第四代语言，它的特点是只管做什么，不管怎么做。
但是缺点也很明显：它缺少过程与控制语句，SQL没有算法描述能力正是因为这个缺点，才有PLSQL的诞生。
PLSQL有4个明显的优势：结构化模块化编程，良好的可移植性，良好的可维护性，提升系统性能。 - PLSQL编码可以分成三个部分:
申明部分（可选） declare
执行部分（必须） begin
异常处理（可选） exception
如果有异常处理的话，执行部分是以
exception结尾，如果不包含
exception部分，就要以
end;来结尾 - PLSQL字符集
PLSQL的字符集可以包含：字母，数字，空白，tab space 回车,
以及一些其它符号（如： +、-、*、/、=、;、:）注：PLSQL对大小写不敏感
- PLSQL中声明变量名的规则
- 标识符必须以字母开头，后可以跟任意字母，数字，货币符号，下划线，或#，不能有空格
- 最大长度不能超过30个字符
- 变量名不能使用保留字，如select、from
- 每一行只能声明一个变量
- 最好不要与数据库中的表或者列同名
PLSQL变量声明语法：
变量名 [constant] 变量类型 [not null] [:=value];
constant关键字表示声明变量为常量，初始化后之后不能再修改值，
类似于java中的final
not null关键字表示该变量是否可以非空PLSQL中的null表示未定义,
声明变量时不给他赋值它的默认值就为null
PLSQL中要给一个声明的变量赋值需要使用’:=值’的形式
例如：name varchar2(32) not null;
就是一种错误的写法，声明为not null就必需要给他初始值- 第一个PLSQL示例
- 第一步，打开cmd命令行，键入sqlplus启动oracle自带命令行工具
- 第二步，使用特定的账号/密码登录服务器，使用edit命令：
edit first.sql 第三步，编写如下代码：
1
2
3
4-- dbms_output.put_line作用就相当于system.out.println()向终端打印
begin
dbms_output.put_line('hello world!');
end;第四步，保存退出
- 第五步，使用@first.sql命令执行编写好的脚本，把first.sql读出来加入到缓冲区中，
终端会出现4，此时用/+enter会提示PL/SQL 过程已成功完成。注意：此时看不到屏幕输出，因为sqlplus默认关闭PLSQL向屏幕输出内容
需要使用set serveroutput on;命令允许脚本向屏幕打印内容，再次执行first.sql脚本
因为此时first.sql已经加载进了缓冲区，只需要/就可以执行 - 第二个PLSQL示例，带变量的简单PLSQL
1 | declare |
PLSQL中连接两个字符串时用||来连接，使用变量时直接使用变量名，
如果编译时有错，按提示进行修改。
一个带exception处理的简单示例
1
2
3
4
5
6
7
8
9declare
v_num number := 0;
begin
v_num := 2 / v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error!');
end;
PLSQL中变量类型的分类
数据类型：
1
2
3record 类似于java中的类
table 类似于map，它是record类型的扩展，又称为索引表类型
varray 具有相同数据类型的一组成员的集合参考类型：
1
2ref corsor 类似于指针
lob(large object) 大对象标量类型：
1
2
3
4
5
6
7
8binary_integer 整型数，它以2的补码二进制形式表述，主要用来计数而不是用来表示字段类型
decimal 浮点型，oracle中就相当于number类型
float 浮点型，只是小数点之后使用的是二进制精度
number(n,m) n代表整个浮点数整个有效位数，m小数点后面的位数
varchar 字符型，oracle中和varchar2没有区别
varchar2 字符型
boolean 布尔型，值为true和false
date 日期型%type符号
作用：使一个变量具有数据库当中的某个表的某个字段具有相同的数据类型，如1
2
3
4
5-- 让lastname的类型与student表中的lastname字段具有相同的类型
declare
firstname varchar2(20);
lastname student.lastname%type;
middlename lastname%type;SQL中的注释符号
- 单行注释 –
- 多行注释 / /
第三个PLSQL示例，定义类型时引用表中的字段类型
1
2
3
4
5
6
7
8
9
10
11
12
13-- 第一步，创建一个用户表
create table tbl_user(
firstname varchar2(20),
lastname varchar(20)
);
-- 第二步，编写PLSQL代码使两个变量的类型与表格中对应的列类型相同
declare
firstname tbl_user.firstname%type :='Chris';
lastname tbl_user.lastname%type :='Chen';
begin
dbms_output.put_line('hello ' || firstname || ' ' || lastname );
end;
此外还可以在执行部分中把查询结果赋给申明的变量：
select firstname, lastname into firstname, lastname from tbl_user;
但是不能使用类似如下语句对声明的变量进行初始化：
firstname tbl_user.firstname%type := select firstname from tbl_user;
示例如下：
1
2
3
4
5
6
7declare
firstname tbl_user.firstname%type;
lastname tbl_user.lastname%type;
begin
select firstname, lastname into firstname, lastname from tbl_user;
dbms_output.put_line('hello ' || firstname || ' ' || lastname);
end;注意：该例运行时要求表中只能包含一条数据
record类型
- 定义记录数据类型。使用它时，需要在声明部分先定义记录的
组成 和记录的
变量 ，然后在执行部分引用该记录变量本身或其中的成员， 声明记录组成的语法：
type 记录类型名称 is record(
变量1 数据类型 [not null][:=默认值],
变量2 数据类型 [not null][:=默认值],
……
变量n 数据类型 [not null][:=默认值]
);示例
1
2
3
4
5
6-- 这是声明了一个自定义类型emp该类型使用record类型
type emp is record(
id varchar2(32),
firstname tbl_emp.firstname%TYPE,
lastname varchar2(20) :='xiaoxiao'
);第四个PLSQL示例，使用record类型的示例
1 | -- 第一步，建表并初始化数据： |
<p style="color:red;">注意：该例运行时要求只能查询一条数据放入变量中</p>;
此外，为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，
避免分别使用%TYPE来定义表示表中各个列，Oracle提供%rowtype定义方式
1 | -- 编写存储过程(形式二)：让表自动映射成record类型，此种写法比上面写法更好 |
我们也可以把查询结果中的特定列值赋给record类型变量中的特定成员
1
2
3
4
5
6
7
8
9
10
11
declare
type emp_rec is record(
id tbl_emp.id%type,
name varchar2(50)
);
v_emp emp_rec;
begin
select id, name || ' ' || lastname into v_emp.id, v_emp.name from tbl_emp where id = 1;
dbms_output.put_line('id ----------> ' || v_emp.id);
dbms_output.put_line('name --------> ' || v_emp.name);
end;
table类型
- 记录表（或索引表）数据类型，类似于java中的map用法。
只不过它的key大都是数值，也可以把它看成Java中的二维数组，
这种类型使用键值对（key->value）的形式来存，table类型中元素的类型可以是复合类型 定义记录表的语法：
type 记录表类型名称 is table of value的类型/元素的类型 [not null]
index by [binary_integer|pls_integer|varray2];关键字index by表示创建一个主键索引，以便引用记录表变量中的特定行
binary_integer的说明
如语句：type numbers is table of number index by binary_integer;
使用了index by binary_integer后，numbers类型的下标就是自增长，
numbers类型在插入元素时，不需要初始化，不需要每次extend增加一个空间。
如果没有这句话index by binary_integer，那就得要显示进行初始化，
并且每插入一个元素到numbers类型的索引表中时，都必须先extend。示例
1
2
3
4
5
6
7
8
9
10declare
type type_table_fname is table of tbl_user.firstname%type index by binary_integer;
v_empnos type_table_fname;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
-- 下标可以是小于0的
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;1
2
3
4
5
6
7
8
9declare
type tabletype is table of tbl_emp%rowtype index by binary_integer;
v_emp tabletype;
begin
select * into v_emp(1) from tbl_emp where id = 1;
dbms_output.put_line('id ----------> ' || v_emp(1).id);
dbms_output.put_line('name --------> ' || v_emp(1).name);
dbms_output.put_line('lastname ----> ' || v_emp(1).lastname);
end;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
37
38
39
40
41
42
43declare
-- 非标号索引表类型，需要动态初始化，动态extend
type t_list_1 is table of varchar2(1024);
-- 标号索引表类型，不需要动态申请
type t_list_2 is table of varchar2(1024) index by binary_integer;
-- 非标号索引表变量，声明时赋初值
v_list_a t_list_1 := t_list_1('A001', 'A002');
v_list_b t_list_1;
-- 标号索引表变量
v_list_c t_list_2;
/*
标号数组声明不能赋初值
v_list_c t_list_2 := t_list_2('C001','C002');
*/
begin
dbms_output.put_line(v_list_a(1));
/*
报错，需要初始化
v_list_b(1) : = 'B001';
*/
-- 初始化
v_list_b := t_list_1();
v_list_b.extend;
v_list_b(1) := 'B001';
dbms_output.put_line(v_list_b(1));
-- 添加数据前需要手动extend否则会报错
v_list_b.extend;
v_list_b(2) := 'B002';
dbms_output.put_line(v_list_b(2));
-- 标号数组不需要初始化，下标从1开始
v_list_c(1) := 'C001';
dbms_output.put_line(v_list_c(1));
-- 并且可以指定下标赋值
v_list_c(100) := 'C100';
dbms_output.put_line(v_list_c(100));
dbms_output.put_line(v_list_c.count);
end;
注意：取元素时要要有KEY的值，利用KEY先取到某一类型的值，再利用它去访问字段
##varray类型
- 可变数组类型是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。
定义varray数据类型的语法：
type 可变数组类型名称 is varray(size) of 元素类型 [not null];
其中，size是正整数，表示可以容纳的成员的最大数量
元素类型成员默认可以取空值，根据需求使用not null加以限制注意：取值时下标从1开始
示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14declare
type t_original is varray(5) of varchar2(25);
v_varray t_original;
-- 也可以在声明时进行初始化
-- v_varray t_original := t_original('1','2','3','4','5');
begin
v_varray := t_original('1','2','3','4','5');
dbms_output.put_line('输出1：' || v_varray(1) || '、'|| v_varray(2)
|| '、'|| v_varray(3) || '、'|| v_varray(4));
dbms_output.put_line('输出2：' || v_varray(5));
v_varray(5) := '5001';
dbms_output.put_line('输出3：' || v_varray(5));
end;
##PLSQL控制语句与Java控制语句对比
Java程序用法|PLSQL中的用法
-|-
if(条件){}|if 条件 then
else if(条件){}|elsif 条件 then ... end if;
无对应|loop if 条件 then exit; end if; end loop;
while(条件){}|while 条件 loop .... end loop;
for(int i=0;i<10;i++){}|for i in 0..9 loop ...... end loop;(上限值结束条件为 <=9 )
注意：默认为自加想实现为自减可改为for i in reverse 9..0 loop …… end loop;
第五个PLSQL示例：if语句的用法
1
2
3
4
5
6
7
8
9
10
11
12declare
type t_original is varray(5) of varchar2(20);
v_varray t_original := t_original('13','2','3','4','5');
begin
if to_number(v_varray(1)) < 10 then
dbms_output.put_line('The first element of array less than 10.');
elsif to_number(v_varray(1)) < 15 then
dbms_output.put_line('The first element of array greater than 10 and less than 15.');
else
dbms_output.put_line('The first element of array don''t meet the condition.');
end if;
end;第六个PLSQL示例：实现1+2+….+99+100打印结果
1
2
3
4
5
6
7
8
9
10
11
12-- 第一种实现：while循环
declare
total number := 0;
i number := 0;
begin
while i <= 100
loop
total := total + i;
i := i + 1;
end loop;
dbms_output.put_line('total ---> ' || total);
end;1
2
3
4
5
6
7
8
9
10
11-- 第二种实现：for循环
declare
total number :=0;
i number :=0;
begin
for i in 0..100
loop
total := total + i;
end loop;
dbms_output.put_line('total ---> ' || total);
end;1
2
3
4
5
6
7
8
9
10
11
12
13-- 第三种实现: loop循环
declare
total number :=0;
i number :=0;
begin
loop
if i=101 then exit;
end if;
total := total + i;
i := i + 1;
end loop;
dbms_output.put_line('total ---> ' || total);
end;关于goto的用法使用标号：
1 | declare |
- null; 空语名，是为了语法考虑，必须要在 end; 之前加上空语句补充语法的完整性
DML和DTL可以直接在PLSQL中执行，借助于oracle中的dbms_sql中的包可以执行DDL语句
SQL语句分类：1
2
3
4
5
6DML select insert delete
DDL drop create
DTL commit rollback savepoint
会话控制 alter session
系统控制 alter system
ESQL 嵌入式sql语句
cursor的用法
游标cursor的作用是用于提取表里面的多行结果集
1
2
3
4
5
6
7
8
9声明游标
cursor 游标名 is select 查询语句; //把游标名与查询语句关联在一起
为查询打开游标
open 游标名; //如果select 语句后面有for update的话，在打开游标时会给当前cursor加上锁
将结果提取出来，存入PLSQL变量中
fetch 游标名 into 变量名;
while 游标名%found //结束条件
关闭游标
close 游标名;关于游标的几个属性：
1
2
3
4
5%found 当fetch语句还能够从游标中提取数据就返回true;反之,返回false
%notfound 与%found相反之
%isopen 判断游标是否处于打开状态，返回一个布尔类型的数据
%rowcount 返回当前游标的指针的位移量，偏向第一条记录的位移量
返回一个数字，代表已经提取多少条数据对于%found和%notfound属性，下面表格列举了提取结果和属性返回值的对应关系
执行结果|%found|%notfound
-|-
上一次fetch提取到了数据|true|false
上一次fetch没提取到了数据|false|true
没有执行fetch|false|false- 第七个PLSQL示例：利用游标去查询tbl_emp表中的所有的数据
1 | -- 第一种实方式while循环 |
1 | -- 第二种实方式loop循环 |
1 | -- 第三种实方式利用for语句使用cousor指针 |
- 定义和使用带参数的游标
1 | declare |
PLSQL中的异常处理：
- PLSQL中异常处理一般满足如下结构
1 | declare |
- 第一个异常处理的例子
1 | declare |
- 异常和游标相结合的综合例子：
1 | declare |