PL/SQL编程基础简介及实践

共计 11395 个字符，预计需要花费 29 分钟才能阅读完成。

PL/SQL 编程基础简介及实践

1、开始(p1~p2)
2、背景介绍(p3)
3、特性优点(p4~p5)
4、使用说明(p6)
5、语法结构(p7)
6、命名参考(p8~p9)
7、复合类型(p10~p16)
8、运算符(p17)
9、流程控制语句(p18~p28)
10、异常和错误处理(p29~32)
11、函数与存储过程(p33~p41)
12、结束(p42)

1、背景介绍
1、PL/SQL 是一种程序语言，叫做过程化 SQL 语言（Procedural Language/SQL）。PL/SQL 是 Oracle 数据库对 SQL 语句的扩展。在普通 SQL 语句的使用上增加了编程语言的特点，所以 PL/SQL 就是把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算程序语言。
2、PL/SQL 类别：数据库引擎、工具引擎（嵌入到其他语言如：C、JAVA）。
3、PL/SQL 包括：编程结构、语法和逻辑机制，工具引擎还增加了支持（如 ORACLE Forms）的句法。

3、特性优点
1、提高运行效率
可以将大量数据的处理放在服务端，减少数据在网络上的传输时间。
2、客户端
可以在客服端执行本地的 PL/SQL，或通过向服务器发送 SQL 命令或激活服务器端来运行 PL/SQL 程序。
3、支持过程化
可嵌入 SQL 语句，及使用各种类型的条件分支语句和循环语句。
4、支持模块化
可通过使用程序包、过程、函数、触发器等将各类处理逻辑分开，方便管理。
5、支持处理异常
可通过使用自定义异常或使用内置的异常，来处理代码中可能发生的异常，提高代码的健壮性。
6、提供大量内置程序包
可使用 Oralce 内置程序包，从而更加方便的处理数据及业务流程。
7、其他：更好的性能、可移植性和兼容性、可维护性、易用性等。

4、使用说明
1、允许的语句：
可使用 INSERT、UPDATE、DELETE、SELECT INTO、COMMIT、ROLLBACK、SAVEPOINT 语句，在 PL/SQL 中需要以动态的方式使用 DDL(CREATE、ALTER、DROP、TRUNCATE) 语句。
2、运行的方式：
PL/SQL 可在 SQL*PLUS、高级语言、ORACLE 开发工具中使用(如：SQL Developer 或 Procedure Builder 等。
3、运行的过程：
PL/SQL 程序的运行是通过 Oracle 中的一个引擎来进行的。这个引擎可能在 Oracle 服务器端，也可能在 Oracle 客户端。引擎执行 PL/SQL 中的过程性语句，然后将 SQL 语句发送给数据库服务器来执行，再将结果返回给执行端。

5、语法结构
1、PL/SQL 组成：由 3 部分组成：声明部分、执行部分、异常处理部分。
2、结构说明：
– 声明部分：声明用到的常量、类型、游标、局部的存储过程、函数
declare
…
– 执行部分：具体的 SQL 语句，包含处理的流程。
begin
…
– 异常部分：针对异常处理的 SQL 语句。
exception
…
end;

6、命名参考
1) 标识符：
不区分大小写、不能包含减号 (-)、首字符必须为字母，不能是 SQL 保留字、不能超过 30 个字符。
2) 命名参考：
程序变量：v_name v_orderId 程序常量：c_name c_cityId 游标变量：cursor_name cursor_storeId 异常标示符：e_name e_agentId 记录类型：name_record test_city_record 绑定变量：g_name g_userId 错误：e_error
3)数据类型：(5 大类)
1 字符类型(CHAR、NCHAR、VARCHAR、VARCHAR2、NVARCHAR2)、2 数字类型(NUMBER、INTEGER、BINARY_FLOAT、BINARY_DOUBLE)、3 时间类型(DATE、TIMESTAMP、INTERVAL YEAR、INTERVAL DAY)、4 大对象类型(BLOB、CLOB、BFILE、NCLOB)、5 其他类型(LONG、RAW LONG RAW、ROWID、UROWID)。

bfile(moive)：存放大的二进制数据对象，表中只存放文件的目录。大小 <=4GB
blob(photo)：存放大的二进制数据对象的位置，位置指向二进制对象的数据块。大小 <=4GB
clob(book)：存放大的字符数据对象的位置，位置指向字符的数据块。大小 <=4GB
nclob(ncahr 字符数据)：存放大的 nchar 字符数据对象的位置，位置指向 nchar 字符的数据块。大小 <=4GB
4)变量声明
v_flag boolean not null default false;
identifier [constant] datetype [not null] [:=value|default value |expression]
identifier：变量名称
datetype：变量类型
:=value 变量或常量的初始值
default value：默认值
expression 为函数其他变量、文本值等
5) 注释
– 单行注释 /* 多行注释 */

7、复合类型介绍
1、复合类型：(记录类型、数组类型、一维表类型、二维表类型)
1) 记录类型：记录类型类似于 c 语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或 record 数据类型的成员，称作 pl/sql record 的域 (field)，其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时，需要先在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。
type record_name is record(
v1 data_type1 [not null] [:= default_value],
…
vn data_typen [not null] [:= default_value] );
2) 说明：%type：表示变量的数据类型与表对应的列的类型一致
%rowtype：表示变量的数据类型与表对应的所有列的类型一致
可以不用知道列的数据类型、当列的数据类型改变后，修改 pl/sql 代码
被赋值的变量与 select 中的列名要一一对应。

declare
id varchar2(32); – 证件号码
province varchar2(10); - 省份编号
city varchar2(10); – 城市编号
district varchar2(10); – 区域编号

– 定义省份、城市、区域编号记录表对象
type base_info_type is record(
province base_info.province%type,
city base_info.city%type,
district base_info.district%type);

sp_record base_info_type;

begin
id := sys_guid();
– 查询出关联的省份编号、城市编号、区域编号信息
select province, city, district
into sp_record
from base_info bi
where bi.store_id = ‘storeId ′ ;−−更新省份编号、城市编号、区域编号信息 updatetest h ousefohsetfoh.province=sp r ecord.province,foh.city=sp r ecord.city,foh.region=sp r ecord.district,foh.address= ′ 商务路 ′ ||lpad(abs(dbms r andom.random),4,dbms r andom.string(′ x ′ ,2))wherefoh.order i d= ′ storeId′;−−更新省份编号、城市编号、区域编号信息 updatetesthousefohsetfoh.province=sprecord.province,foh.city=sprecord.city,foh.region=sprecord.district,foh.address=′商务路′||lpad(abs(dbmsrandom.random),4,dbmsrandom.string(′x′,2))wherefoh.orderid=′{orderId}’;
commit;
end;

2)数组类型：具有相同数据类型的记录的集合。
type array_name is varray(size) of elementType [not null];
array_name：数组类型名称 size：元素的大小 elementType：数据类型
– 位置从 1 开始
declare
type city_array is varray(3) of varchar2(10);
v_city_array city_array;
begin
v_city_array := city_array(‘ 北京市 ’, ‘ 上海市 ’, ‘ 深圳市 ’);
dbms_output.put_line(‘ 第 3 个城市名称 =’ || v_city_array(3));
end;

1、绑定变量：使用 variable 来定义
variable return_cityId number;

SQL> variable returnValue number;
SQL> begin
2 select 3*6 into :returnValue from dual;
3 end;
4 /
PL/SQL procedure successfully completed
returnValue
———
18
SQL> print returnValue;
returnValue
———

3)表类型：定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在 pl/sql 中模仿其他数据库中的表。
type table is table of elementType [not null]
index by [binary_integer | pls_integer |varray2]
关键字 index by 表示创建一个主键索引，以便引用记录表变量中的特定行
– 按一维数组使用记录表的示例
declare
type city_table is table of varchar2(20) index by binary_integer;
v_city_table city_table;
begin
v_city_table(1) := ‘ 北京市 ‘;
v_city_table(2) := ‘ 深圳市 ‘;
dbms_output.put_line(‘ 第 2 个城市名称 = ‘ || v_city_table(2));
end;

– 按二维数组使用记录表的示例
declare
type bse_city_table is table of test_city%rowtype index by binary_integer;
v_bse_city_table bse_city_table;
begin
select city_id, city_name
into v_bse_city_table(1).city_id,v_bse_city_table(1).city_name
from test_city bc
where bc.p_city_id = ‘020’
and rownum = 1;
select city_id, city_name
into v_bse_city_table(2).city_id,v_bse_city_table(2).city_name
from test_city bc
where bc.p_city_id = ‘0755’
and rownum = 1;
dbms_output.put_line(‘ 记录 1 中区域编号 =’ || v_bse_city_table(1).city_id ||
‘_记录 1 中区域名称 =’ || v_bse_city_table(1).city_name);
dbms_output.put_line(‘ 记录 1 中区域编号 =’ || v_bse_city_table(2).city_id ||
‘_记录 1 中区域名称 =’ || v_bse_city_table(2).city_name);
end;

8、运算符
1、关系运算符：
=、<> ~= != ^=、>、>=、<、<=
2、一般运算符：
+、-、*、/、:=(赋值号)、..(范围运算符)、||、=>(关系号)
3、逻辑运算符：
is null、in、and、or、not、between and
4、注意事项：
1) 变量赋值：先声明再赋值。
v_storePhone varchar2(11); – 手机号码
v_storePhone := ‘158’ || lpad(abs(dbms_random.random), 8, 0);
2)null+ 数字为 null，null|| 字符串为字符串
3)boolean 类型的值只能取 true false null3 个值

9、流程控制语句
1) 语句分类：控制语句 (IF)、循环语句(LOOP、EXIT) 顺序语句(GOTO、NULL)
2) 结构说明
a)
IF < 布尔表达式 > THEN
PL/SQL 语句和 SQL 语句
END IF;
b)
IF < 布尔表达式 > THEN
PL/SQL 语句和 SQL 语句
ELSE
其他语句
END IF;

IF < 布尔表达式 1 > THEN
PL/SQL 语句和 SQL 语句 1
ELSIF < 布尔表达式 2 > THEN
其他语句 1
ELSIF < 布尔表达式 3 > THEN
其他语句 2
ELSE
其他语句 3
END IF;

IF 语句示例
declare
v_roleId varchar2(20); – 角色编号
v_result varchar2(60);
begin
for vv in (select distinct su.role_id
from test_ur su
where su.role_id in (‘project_sz’,
‘project_bj’,
‘project_gz’,
‘project_sh’)) loop
if (vv.role_id = ‘project_sz’) then
v_result := vv.role_id || ‘_表示的是_角色 1 ’;
dbms_output.put_line(v_result);
elsif (vv.role_id = ‘project_sh’) then
v_result := vv.role_id || ‘_表示的是_角色 2 ’;
dbms_output.put_line(v_result);

elsif (vv.role_id = ‘project_gz’) then
v_result := vv.role_id || ‘_表示的是_角色 3 ’;
dbms_output.put_line(v_result);
elsif (vv.role_id = ‘project_bj’) then
v_result := vv.role_id || ‘_表示的是_角色 4 ’;
dbms_output.put_line(v_result);
else
v_result := vv.role_id || ‘_表示的是_未知角色 ’;
dbms_output.put_line(v_result);
end if;
end loop;
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) || ‘ 处理成功 ’);
end;

loop 语句示例
loop
执行的语句
exit when < 条件语句 >; – 当条件为满足时，退出循环
end loop;
–loop 示例
declare
v_count number;
v_time number;
begin
v_count := 0;
loop
v_count := v_count + 1;
dbms_output.put_line(‘ 第 ’ || v_count || ‘ 次循环 ’);
exit when(v_count > 3);
end loop;
end;

while 语句示例
while < 布尔表达式 > loop
执行的语句
end loop;
–while 示例
declare
v_count number;
v_time number;
begin
v_count := 0;
while (v_count < 3) loop
v_count := v_count + 1;
dbms_output.put_line(‘ 第 ’ || v_count || ‘ 次循环 ’);
end loop;
end;

for 循环语句示例
for 循环计数器 in [reverse] 下限 .. 上限 loop
执行的语句
end loop;
每循环 1 次，计数器自动加 1，加上 reverse 关键字则自动减 1，必须为从小到大的整数，可以使用 exit when 退出循环
declare
v_count number;
begin
v_count := 8;
for i in 1 .. v_count loop
dbms_output.put_line(‘ 第 ’ || i || ‘ 次循环 ’);
exit when(i > 3);
end loop;
end;

case when 循环语法
– 语法 1
case 条件表达式
when 表达式结果 1 then
语句 1
…
when 表达式结果 n then
语句 n
[else 表达式结果]
end case;

– 语法 2
case 条件表达式
when 表达式结果 1 then
语句 1
…
when 表达式结果 n then
语句 n
[else 语句]
end case;

case when 语句示例
select trunc(tur.created_date, ‘dd’),
count(case
when tur.role_id = ‘project_sh’ then
1
else
null
end) as 角色 1,
count(case
when tur.role_id = ‘project_gz’ then
1
else
null
end) as 角色 2,

count(case
when tur.role_id = ‘project_sz’ then
1
else
null
end) as 角色 3,
from test_ur tur
group by trunc(tur.created_date, ‘dd’)
order by trunc(tur.created_date, ‘dd’) desc;

5)goto 无条件跳转到指定标签所在部分
goto lable
…
<<lable>>
6)null 不执行任何操作的语句

10、异常和错误处理
1、Oracle 提供异常情况(exception) 和异常处理 (exception handler) 来实现对错误的处理。
2、异常情况 (exception) 指在正常执行过程中未预料的事件，程序块的异常处理预定义错误和自定义错误，运行 PL/SQL 块时一旦产生异常而没有指出如何处理时，就会自动终止整个 PL/SQL 块的运行。
3、异常错误分为 3 类 (预定义错误、非预定义错误、自定义错误)
预定义错误：无需在程序中定义，由 Oracle 自动引发，共 24 个，直接在异常中使用。
非预定义错误：需在程序中定义，由 Oracle 自动引发
自定义错误：需在程序中定义，且需在程序中引发。

1)预定义错误
exception
when No_data_found then
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) ||’execute failure’);
2) 非预定义错误
– 定义错误
< 异常情况 > exception;
– 与标准的 Oracle 错误关联
pragma exception_init(< 异常情况 >,< 异常代码 >);
– 处理错误
exception
when foundError then
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) ||’execute failure’);

3)自定义错误
– 定义错误
< 异常情况 > exception;
– 通过 raise 引发错误
raise 异常情况
– 处理错误
exception
when raiseError then
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) ||’execute failure’);

4)修改自定义错误消息
dbms_standard.raise_application_error(errorNumber,errorCode,errorsMsg);
errorNumber：错误编号：-20000~-20999
errorMsg：提示的错误消息(<2014KB)
errorFlag：true 将错误添加到错误列表，false 替换当前的错误列表，缺省为 false
dbms_standard.raise_application_error(-20001,’ 错误的消息 ’);
记录错误关键字：
sqlcode：错误编号如：6502
sqlerrm：错误消息如：ORA-06502: PL/SQL: 数字或值错误 : character string buffer too small (<500KB)
dbms_output.put_line(‘ 错误编号_’ || sqlcode || ‘_错误信息_’ || sqlerrm);
when others exception 必须放在异常处理部分的最后面，以作为缺省异常的处理，when … exception 没有数量限制，没被处理的异常将检测调用异常的程序，并将异常传播到外面，异常被处理并被解决或达到最外层循环后停止，在声明部分的抛出的异常将控制转到上一层部分。

11、函数和存储过程
1) 函数：
create [or replace] function functionName
(arg1 [{in out in out}] type1 default value1,
…
argn [{in out in out}] typen default valuen)
[authid definer|current_user]– 权限控制
return resultType
{is | as}
变量的声明部分
begin
执行语句部分
return expression
exception
异常处理部分
end functionName;
in out in out– 表示参数的模式，有入参、出参，不写的话默认为入参，其中只能为入参设置默认值，当调用函数后，不指定入参的值时，就使用入参的默认值。
create or replace function funTranslateRole(v_roleId in varchar2,
v_result out varchar2)
return varchar2
is
role_id varchar2(20); – 角色编号
begin
role_id := v_roleId;
if (role_id = ‘project_sz’) then
v_result := role_id || ‘_表示的是_客服 ’;
else
v_result := role_id || ‘_表示的是_未知 ’;
end if;
return v_result;
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) || ‘ 处理成功 ’);
exception
when others then
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) || ‘ 处理失败 ’);
end funTranslateRole;

1)位置表示法
–argvalue1,argvalue2,..argvaluen
funTranslateRole(v_roleId,v_result)

2)名称表示法
– 这种方式与参数的顺序没关系，v_result、v_roleId 与函数中的参数名称需一致
declare
roleId varchar2(20); – 角色编号
vresult varchar2(60); – 角色的结果
begin
roleId := ‘project_bj’;
vresult := funTranslateRole(v_result => vresult, v_roleId => roleId);
dbms_output.put_line(vresult);
end;

3)组合 (名称表示 + 位置表示法)
如果前一个参数用名称表示法，则后面的所有参数都要用名称表示法。
– 调用方式
declare
v_roleId varchar2(20); – 角色编号
v_result varchar2(60); – 角色的结果
begin
v_roleId := ‘project_bj’;
v_result := funTranslateRole(v_roleId,v_result);
dbms_output.put_line(v_result);
end;

2)存储过程：
create [or replace] procedure procedure Name
(arg1 [{in out in out}] type1 default value1,
…
argn [{in out in out}] typen default valuen)
[authid definer|current_user]– 权限控制
{is |as}
变量的声明部分
begin
执行语句部分
exception
异常处理部分
end procedureName;
in out in out—表示参数的模式，有入参、出参，不写的话默认为入参，其中只能为入参设置默认值，当调用函数后，不指定入参的值时，就使用入参的默认值。
create or replace procedure proTranslateRole(v_roleId in varchar2,
v_result out varchar2)
is
role_id varchar2(20); – 角色编号
begin
role_id := v_roleId;
if (role_id = ‘project_sz’) then
v_result := role_id || ‘_表示的是_客服 ’;
else
v_result := role_id || ‘_表示的是_未知 ’;
end if;
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) || ‘ 处理成功 ’);
exception
when others then
dbms_output.put_line(to_char(sysdate, ‘hh24:mi:ss’) || ‘ 处理失败 ’);
end proTranslateRole;

1)位置表示法
–argvalue1,argvalue2,..argvaluen
proTranslateRole(v_roleId,v_result)
2) 名称表示法
– 这种方式与参数的顺序没关系，v_result、v_roleId 与函数中的参数名称需一致
declare
roleId varchar2(20); – 角色编号
vresult varchar2(60); – 角色的结果
begin
roleId := ‘project_bj’;
vresult := proTranslateRole (v_result => vresult, v_roleId => roleId);
dbms_output.put_line(vresult);
end;
3) 组合 (名称表示 + 位置表示法)
如果前一个参数用名称表示法，则后面的所有参数都要用名称表示法。
– 调用方式 1
declare
v_roleId varchar2(20); – 角色编号
v_result varchar2(60); – 角色的结果
begin
v_roleId := ‘project_bj’;
v_result := proTranslateRole (v_roleId,v_result);
dbms_output.put_line(v_result);
end;