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 ′ ;−−更新省份编号、城市编号、区域编号信息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;

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

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