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

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}';

--更新省份编号、城市编号、区域编号信息
update test_house foh
set foh.province = sp_record.province,
foh.city = sp_record.city,
foh.region = sp_record.district,
foh.address = '商务路' ||lpad(abs(dbms_random.random),4,dbms_random.string('x', 2))
where foh.order_id = '${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;

--调用方式2
exec[ute] 存储过程名称(参数1,..参数n);
--可以在PL/SQL块中建立本地函数和过程，但不能使用 create or replace关键字

1)函数与过程的差异
1、如果要返回多个值或不返回值，可以使用过程；如果只返回1个值，可以使用函数。
2、过程用于执行一系列的动作，而函数用于计算和返回1个值。
3、可以在SQL语句内部通过调用函数来完成复杂的计算，而过程则做不到。