Oracle数据库讲义(第三章).ppt

《Oracle数据库讲义(第三章).ppt》由会员分享，可在线阅读，更多相关《Oracle数据库讲义(第三章).ppt（35页珍藏版）》请在三一办公上搜索。

1、讲 义,Oracle数据库,2005.8,李明俊,第三章 PL/SQL程序设计,主要内容,3.1 PL/SQL3.2 PL/SQL块结构 3.3 PL/SQL基本语法3.4 PL/SQL处理流程3.5 异常处理3.6 游标3.7 存储过程和函数3.8 触发器,简介 SQL(SQL*PLUS)是工业标准语言，是一种通用性广、效率高、功能强的交互式处理语言，它是非结构化的，只能单语句运行，不能进行编程。PL/SQL语言在SQL的基础上增加了变量处理、语句的运行控制、意外处理等功能构成了结构化的程序设计语言，所有SQL的数据类型、函数及功能语句在这里完全兼容，使用于所有Oracle环境中。2.PL/

2、SQL优点有利于客户/服务器环境应用的运行 使用PL/SQL进行编程，将大量数据处理的应用放在服务器端来执 行，省去了数据在网上的传输时间。适合于客户环境 由于PL/SQL分为数据库PL/SQL部分和工具PL/SQL。在客户端中把PL/SQL可以嵌套到相应的工具中，客户端可以执行本地的PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。,3.1 PL/SQL,3.PL/SQL中的Select语句 在PL/SQL中Select语句与SQL*Pluas不同，查询出来的结果要付给指定的变量当中，因此，在语句中加入INTO和变量。Select语句格式如下：SELECT，IN

3、TO,FROM，WHERE HAVING GROUP BY，;从语句格式中也能看到，Select语句只允许返回一行记录，否则会出现TOO_MANY_ROWS错误。【例】读取表T_SPML中商品编码为9650005的商品名称及价格。Select spmc,xsjg into v_spmc,v_xsjg from T_SPML Where spbm=9650005;,3.2 PL/SQL块结构,PL/SQL程序由三个块组成，即 声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：Declare/*声明部分:在此声明PL/SQL用到的变量,类型及光标*/Begin/*执行部分:过程及SQL语

4、句,即程序的主要部分*/Exception/*执行异常部分:错误处理*/End;,其中：执行部分是必须的。Begin End必须嵌套使用。DECLARE、BEGIN、EXCEPTION后没有分号(；)，而END后则必须要带有分号。PL/SQL标识符的命名规则：标识符的最大长度是30个字符，包括字母、数字、$、_、#；不可包含保留字；要以字母来打头；不能和同一块中的表中的字段名一样。【例】只包含执行部分的PL/SQL块 begin dbms_output.put_line(Welcome!);end;其中：dbms_output.put_line屏幕输出语句。,【例】包含定义部分和执行部分的PL

5、/SQL块 DECLARE v_spmc Varchar2(20);BEGIN select spmc INTO v_spmc FROM t_spml WHERE spbm=输入 no 的值:9650005商品名称：喜来达空气芳香剂注意：该例中当执行该PL/SQL时，会根据输入的商品编码显示商品的名称。,3.3 PL/SQL基本语法,3.3.1 常量与变量定义常量的语法格式：常量名 constant 类型标识符 not null:=值;【例】pi constant number(9,7):=3.1415926;定义变量的语法格式变量名 类型标识符 not null:=值;【例】v_age nu

6、mber(3):=26;常量和变量名都必须以字母开头，不能有空格，不能超过30个字符长度，同时不能和保留字同名，常（变）量名称不区分大小写，在字母后面可以带数字或特殊字符。括号内的not null为可选参数，若选用，表明该常（变）量不能为空值。,3.3.2 复合数据类型变量使用%type定义变量 为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。定义字段型变量%type的格式 变量名 表名.列名%type;【例】v_spbm T_spml.spbm%type;使用%

7、rowtype定义变量 在PL/SQL中，支持将多个基本数据类型捆绑在一起的记录数据类型。引用记录型变量的方法是“记录变量名.基本类型变量名”。,定义记录型变量%rowtype的格式变量名 表名%type;【例】执行下列PL/SQL程序，程序定义了名为v_spml的复合类型变量，与T_SPML表结构相同。DECLARE v_spml t_spml%rowtype;BEGIN select*into v_spml from t_spml where spbm=,使用 Table 定义变量(数组)Table也是一种用于处理PL/SQL的数据类型。该数据类型与高级语言数组非常类似，其元素下标必须是整

8、型数，可以是负数，无需指定下标的大小范围，下标可以不连续。定义数组的格式：type 类型名 is Table of 元素类型 Index By Binary_integer;当在PL/SQL块中使用数组变量时，按如下格式指定变量。数组变量名 类型名;下标可以使用整型变量。下面举例说明使用Table类型的方法：,【例】Declare type Table_type is Table Of t_spml.spmc%Type Index By Binary_Integer;v_spmc_type Table_type;begin select spmc into v_spmc_type(1)from

9、 t_spml where spbm=输入 no 的值:9650005商品名称:喜来达空气芳香剂,3.4 PL/SQL 处理流程,在PL/SQL程序中，要使程序能按照逻辑进行处理，除了有些语句是SQL语句外，还必须有能进行逻辑控制的语句。PL/SQL 也不例外，它不仅可以嵌入SQL语句，而且还支持条件分支语句（IF，CASE）、循环语句（LOOP）。3.4.1 条件分支语句 格式：IF THEN PL/SQL和SQL语句;ELSE PL/SQL和SQL语句;ELSIF THEN PL/SQL和SQL语句;END IF;,【例】判断两个整数变量的大小，输出不同的结果。declareV_num1

10、integer:=80;V_num2 integer:=90;beginif v_num1=v_num2 then if v_num1=v_num2 then dbms_output.put_line(v_num1等于v_num2);else dbms_output.put_line(v_num1小于v_num2);end if;else dbms_output.put_line(v_num1大于v_num2);end if;end;,3.4.2 CASE语句在CASE语句中使用单一选择条件进行等值比较。格式：CASE WHEN THEN 语句1；WHEN THEN 语句2；WHEN THEN

11、 语句n；ELSE 语句n+1;END CASE;【例】Declare v_dd Char(2);Begin Select to_char(last_day(Sysdate),dd)Into v_dd From dual;Case v_dd When 31 Then dbms_output.put_line(大月);When 30 Then dbms_output.put_line(小月);Else dbms_output.put_line(二月);End Case;End;,在CASE语句中使用多种条件比较 格式：CASE WHEN THEN 语句1；WHEN THEN 语句2；WHEN T

12、HEN 语句n；ELSE 语句n+1;END CASE;【例】Declarev_dd Char(2);BeginSelect to_char(last_day(Sysdate),dd)Into v_dd From dual;Case When v_dd=31 Then dbms_output.put_line(大月);When v_dd=30 Then dbms_output.put_line(小月);Else dbms_output.put_line(二月);End Case;End;,3.4.3 循环语句 类型一 LOOP循环 格式：LOOP 执行语句；:Exit When；执行语句；:E

13、nd LOOP;执行语句；:当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当条件为TURE时，会退出循环，并执行END LOOP后的相应操作。,【例】先创建表T_stu,并插入5条数据(2004610120046105)。create table T_stu(xh int);执行插入程序。declarei int:=20048101;beginloop insert into T_stu values(i);exit when i=20048105;i:=i+1;end loop;end;,类型二 WHILE 循环格式：While=常数 LOOP 执行语句；：End LOOP;只有

14、条件为真时，才会执行循环体内的语句。【例】declare i int:=20048101;begin while i=20048105 LOOP insert into T_stu values(i);i:=i+1;end loop;end;,类型三 FOR循环 格式：For REVERSE In 起点.终点 LOOP 执行语句；：End LOOP;当使用FOR循环时，每次循环时循环控制变量会自动增一；如果指定REVERSE选项，那么每次循环时循环控制变量会自动减一。【例】declare i int:=20048101;Begin For i in 20048101.20048105 LOOP

15、 insert into T_stu values(i);end loop;end;,3.5 异常处理一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中 恢复。Oracle 提供异常情况(EXCEPTION)来实现错误处理。虽然在PL/SQL编程中，异常处理不是必须的，但建议要养成在PL/SQL编 程中指定相应的异常。异常处理是用来处理正常执行过程中未预料的事件。PL/SQL程序块一旦 产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。异常处理格式EXCEPTION when exception1 then statement1;when exception2 then

16、statement2;when others then statement;END;其中：异常处理可以按任意次序排列,但 Others 必须放在最后。,【例】根据输入的商品编码查找商品名称、产地、销售价格，如果查不到提示出错。Declare v_spmc t_spml.spmc%type;v_spcd t_spml.spcd%type;v_xsjg t_spml.xsjg%type;begin select spmc,spcd,xsjg into v_spmc,v_spcd,v_xsjg from t_spml where spbm=,【例】对输入的日期进行校验。declare v_date

17、char(8);Begin v_date:=,3.6游标,PL/SQL用游标来管理SQL语句返回的记录。游标是为了处理这些语句而分配的一块内存工作区，每个Select语句都对应一个游标。游标分显示游标和隐式游标。3.6.1 显式游标 使用显示游标有下面四个步骤：声明游标(定义查询范围)Select语句打开游标(指针指向第一条记录)读取数据(一次一行记录)关闭游标,格式：Declare cursor is select from where Group by order by for update of;Begin Open；Loop fetch into;exit when%notfound;

18、:Update Set Where current of;End Loop;Close;End;,定义游标 定义游标是指定要提取的记录的列和设定读取条件。打开游标 打开游标是执行游标所对应的SELECT语句，将其查询结果放入工作区，指针指向工作区的首部。提取数据使用FETCH语句提取游标指针所指定的行数据，并把指针指向下一行记录。关闭游标close 游标名称;读取显示游标记录没有返回行时，认定为出现异常，程序自动跳到异常处理部分。,【例】显示商品编码965打头的所有商品的编码及名称。（使用FETCHINTO语句）declare cursor c1 is select SPBM,SPMC fro

19、m T_SPML where SPBM like 965%;V_SPML c1%rowtype;begin if not c1%ISOPEN then open c1;end if;loop fetch c1 into v_spml;exit when c1%NOTFOUND;dbms_output.put_line(v_spml.spbm|,|v_spml.spmc);end loop;close c1;end;,3.6.2 隐式游标 所有的SQL语句在都有一个内存工作区，这个工作区就是所谓的SQL游标(SQL cursor)，即隐式游标。与显式游标不同，SQL游标不被程序打开和关闭。当一个

20、DML语句执行时，PL/SQL内部打开一个游标，语句的执行信息被保存在4个游标属性中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN,【例 3-20】把供货单位编码G90007的扣款比率更新为20，如果没找到则往供货单位目录表中插入该供货单位记录。BEGIN UPDATE T_GHDWML SET KKBL=20 WHERE DWBM=G90007;-如果更新没有匹配则插入一新行 IF SQL%NOTFOUND THEN INSERT into T_GHDWML VALUES(G90007,哈尔滨强生日用品有限公司,强生,20,，);END IF;COM

21、MIT;END;,3.7异常处理 异常是指PL/SQL语言运行时产生的Oracle错误。Oracle主要异常例子,3.7.1 捕获异常的语句格式：Exception When 异常名称 Then 处理语句;例：Begin select zgmc into v_zgmc from t_xsml where zgbm=00001;Exception When NO_DATA_FOUND Then Null;When OTHERS Then Message(Sqlerrm);End;,设商品入库明细表T_sprkmx和商品库存明细表T_spkcmxb的结构如下：商品入库明细表 T_sprkmx 商品

22、入库明细表 T_sprkmx spbm char(6)not null spbm char(6)not null ghdw char(6)not null ghdw char(6)not null rkbh number(8)not null rkbh number(8)not null rkrq char(8)not null rkrq char(8)not null jhjg number(8,2)jhjg number(8,2)rksl number(6)rksl number(6)cfdz char(1)not null yysl number(6)clbz char(1)Cksl n

23、umber(6)xssl number(6)存放地址cfdz：Y-营业，C-仓库 zcsl number(6),例 根据商品入库表的内容修改商品库存明细表。declare cursor c1 is select spbm,ghdw,rkbh,rkrq,jhjg,rksl,cfdz from t_sprkmx where rkrq=20050526 and clbz is null order by spbm for update of clbz;v c1%rowtype;begin ksbz:=0;open c1;程序循环体在后页。close c1;COMMIT;exception when

24、OTHERS then:xx:=数据出现异常 SPBM=|v.spbm|,RKBH=|v.rkbh);end;,loop fetch c1 into v;exit when c1%notfound;if v.cfdz=Y then insert into t_pskcmx(spbm,ghdw,rkbh,rkrq,jhjg,yysl)values(v.spbm,v.ghdw,v.rkbh,v.rkrq,v.jhjg,v.rksl);else insert into t_pskcmx(spbm,ghdw,rkbh,rkrq,jhjg,cksl)values(v.spbm,v.ghdw,v.rkbh,v.rkrq,v.jhjg,v.rksl);end if;update t_sprkmx set clbz=C where current of c1;end loop;,3.7.2 异常处理及SQL%异常处理可分为两类：Select语句的NO_DATA_FOUND读数据异常 程序运行的所有逻辑错误SQL%主要捕获隐式游标的执行结果信息SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT经常用在Update、Delete之后使用。,