Oracle数据库教程 第章

第7章PL/SQL编程PL/SQL编程7.1PL/SQL语言基础7.2PL/SQL程序结构7.3异常处理7.1PL/SQL语言基础7.1.1PL/SQL语言优点PL/SQL是一种高效的事务处理语言，它具备以下优点：PL/SQL是一种高性能的基于事务处理的语言，能运行在任何Oracle环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有Oracle对象类型。PL/SQL块可以被命名并存储在Oracle服务器中，同时也能被其他PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。可以使用Oracle数据工具管理PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序权限。7.1PL/SQL语言基础PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何能够运行Oracle的操作系统都是非常便利的。对于SQL，Oracle必须在同一时间处理每一条SQL语句，在网络环境下这就意味着每一个独立的调用都必须被Oracle服务器处理，从而占用大量的服务器时间，导致网络拥挤。而PL/SQL以整个语句块形式发给服务器，可以减少网络拥挤。7.1PL/SQL语言基础7.1.2PL/SQL的基本结构一个PL/SQL程序包含一个或多个逻辑块，每个块都可以划分为三个部分：声明部分（用DECLARE开头）、执行部分（以BEGIN开头）和异常处理部分（以EXCEPTION开头）

1、PL/SQL基本结构如下：

DECLARE

声明部分

BEGIN

执行部分

EXCEPTION

异常处理部分

END；7.1PL/SQL语言基础其中：声明部分：定义变量和常量的数据类型和初始值，以及程序中要使用的函数、游标、异常处理名称等。如果没有需要声明的内容，可以省略这一部分。PL/SQL中要使用的所有定义都必须在这一部分进行集中定义。执行部分：此部分是PL/SQL块中的指令部分，所有的可执行语句都放在这一部分，包括对数据库的操作语句和各种流程控制语句，也可以嵌套其他的PL/SQL块。执行部分至少包含一条可执行语句。异常处理部分：包含在执行部分中，以关键字EXCEPTION开始，到END结束。当程序检测到错误而产生异常时，就转到由EXCEPTION标识的部分执行异常处理程序。这部分是可选的，利用异常处理可以提高PL/SQL程序的健壮性。7.1PL/SQL语言基础2、

PL/SQL的特殊结构

<<BLOCK1>>/*以名字标识PL/SQL块*/DECLARE

声明部分BEGIN

执行部分EXCEPTION

异常处理部分END<<BLOCK1>>；DECLARE

主块声明部分BEGIN

主块执行部分

DECLARE

子块声明部分

BEGIN

子块执行部分

EXCEPTION

子块异常处理部分

END；EXCEPTION

主块异常处理部分END；带命名块的PL/SQL块嵌套结构的PL/SQL块7.1PL/SQL语言基础7.1.3PL/SQL的字符集1、合法字符，包括：大写字母A-Z和小写字母a-z数字0-9符号()、+、-、*、/、<、>、=、！、~、^、;、:、.、’、@、”、#、$、_、{}、[]、?制表符、空格符、回车符等非显示的间空符号2、操作符，包括：算术操作符关系操作符逻辑操作符3、其他字符7.1PL/SQL语言基础7.1.4PL/SQL基本语法1、常量与变量的声明，语法格式如下：

<常（变）量名>[CONSTANT]<数据类型>[NOTNULL][(宽度):=<初始值>]

【例7.1】几个声明的例子①声明一个长度为10B的变量count，初值为1，类型为VARCHAR２。

cvarchar2(10):='1';②声明一个NUMBER数据类型的常量c_limit，并赋予初值5000.00。

c_limitconstantnumber(8.2):=5000.00;③声明一个NUMBER数据类型的常量c_area，并用表达式给它赋初值。

c_areaconstantnumber(8.5):=3.14159*3**2；7.1PL/SQL语言基础2、作用域变量的作用域指变量的有效使用范围，它从变量声明开始，直到当前程序块结束，只有在其作用域范围内，程序才能使用该变量，否则将导致编译错误。在同一程序块中，不允许声明两个同名变量，但在不同程序块内可以声明两个同名变量。在程序块内声明的变量称为局部变量，在程序块外声明的变量称为全局变量。引用程序块内的局部变量不需要加限定词，而引用块外的全局变量需要加限定词（即父块的名称）。子块中可以引用父块的变量，但是父块中不能引用子块的变量。如果父块与子块具有相同的变量名，在子块中引用这个变量时若是不加限定词则引用的是子块内的局部变量。若两个块不是父子关系，则他们之间的变量不能相互引用。7.1PL/SQL语言基础（3）变量的属性性变量有名字和和数据类型两两个属性。变变量名用于标标识该变量，，变量的数据据类型确定了了该变量存放放值的格式及及允许的运算算。在PL/SQL中，使用“%”表示属性提示示符，后面紧紧跟属性名。。%TYPE和%ROWTYPE是PL/SQL中两个特殊的的属性，常用用来定义变量量，使被定义义变量的数据据类型与一个个已定义变量量（或参照表表中的记录））的数据类型型相一致。%TYPE使用%TYPE属性定义变量量时，被定义义变量的数据据类型与一个个已经定义了了的变量的数数据类型相一一致，当被参参照的变量数数据类型改变变后，新定义义变量的数据据类型也随之之改变。当无无法准确知道道被参照变量量的数据类型型时，只能采采用这种定义义方法。定义义格式为：<新变量名><被参照表.被参照列>%TYPE7.1PL/SQL语言基础【例7.1】声明三个变量量“v_班级名称”，，“v_班级人数”，，“v_班主任”，其其数据类型分分别对应“班班级表”中相相应字段的数数据类型。根根据输入的班班级代码，输输出对应的班班级名称、人人数和班主任任信息。代码码如下：DECLAREv_班级名称班级表.班级名称%TYPE;v_班级人数班班级表.班级人数%TYPE;v_班主任班级级表.班主任%TYPE;BEGINSELECT班级名称,班级人数,班主任intov_班级名称,v_班级人数,v_班主任FROM班级表WHERE班级代码='&班级代码';DBMS_OUTPUT.PUT_LINE('班级名称：'||v_班级名称);DBMS_OUTPUT.PUT_LINE('班级人数：'||v_班级人数);DBMS_OUTPUT.PUT_LINE('班主任：'||v_班主任);END;语句：Setserverouton7.1PL/SQL语言基础%ROWTYPE使用%ROWTYPE属性能够定义义记录变量，，使得被定义义的记录成员员个数、名称称、数据类型型与已定义的的表或视图中中列的个数、、名称和数据据类型完全相相同。这种定定义方式，可可以定义一个个表，当被参参照表中列及及数据类型改改变时，新定定义表中的列列及数据类型型自动改变。。当一个表有较较多列时，使使用%ROWTYPE定义记录，比比使用%TYPE要方便，并且且不容易出错错。定义格式式为：<新表名><被参照表>%ROWTYPE7.1PL/SQL语言基础【例7.2】声明一个变量量“v_班级”，与班班级表具有相相同的数据类类型。根据输输入的班级代代码，输出对对应的班级名名称、人数和和班主任信息息。代码如下下：DECLAREv_班级班级表表%ROWTYPE;BEGINSELECT*intov_班级FROM班级表WHERE班级代码='&班级代码';DBMS_OUTPUT.PUT_LINE('班级名称：'||v_班级.班级名称);DBMS_OUTPUT.PUT_LINE('班级人数：'||v_班级.班级人数);DBMS_OUTPUT.PUT_LINE('班主任：'||v_班级.班主任);END;7.1PL/SQL语言基础3、数据类型标量类型LOB型数据类型定义义的运算符7.1PL/SQL语言基础【例7.3】】声明一个TIMESTAMP类型型的变量，并并为它赋值。。代码如下：：SETSERVEROUTONDECLAREcheckoutTIMESTAMP(2);BEGINcheckout:='10-6月月-07075';DBMS_OUTPUT.PUT_LINE(checkout);END;7.2PL/SQL程序结构7.2.1选择结构选择结构又称称条件控制，，通过先测试试一个条件，，根据测试结结果选择运行行不同的语句句段。选择结结构允许嵌套套执行。选择结构常用用到的4种语法格式：：IF…THEN…ENDIF结构IF……THEN…ELSE……ENDIF结构IF……THEN…ELSIF…ENDIF结构CASE结构7.2PL/SQL程序结结构【例7.4】查查找““ASP.NET程程序设设计””课程程，若若是““备注注”信信息为为“C#””，将将其改改为““J#”，，否则则将““备注注”信信息置置空。。7.2PL/SQL程序结结构DECLAREv_课课程课课程表表%ROWTYPE;BEGINSELECT*INTOv_课课程FROM课课程程表WHERE课课程名名='ASP.NET程程序设设计';IFv_课程程.备备注='C#'THENUPDATE课课程程表SET备备注='J#'WHERE课课程名名=v_课课程.课程程名;ELSEUPDATE课课程程表SET备备注=''WHERE课课程名名=v_课课程.课程程名;ENDIF;DBMS_OUTPUT.PUT_LINE('更更新完完成!');END;试一试试：数数据被被修改改了吗吗？为为什么么？作业一一、、用你你熟悉悉的方方法完完成成该问问题，，比比较两两种方方法。。7.2PL/SQL程序结结构【例7.5】学生成成绩按按照分分数段段分为为“优优秀””、““良好好”、、“合合格””、““不及及格””4种等级级，根根据等等级的的不同同，可可以输输出对对应的的分数数段，，代码码如下下：DECLAREv_resultVARCHAR2(20):='良好';BEGINIFv_result='优秀'THENDBMS_OUTPUT.PUT_LINE('90~100');ELSIFv_result='良好'THENDBMS_OUTPUT.PUT_LINE('80~89');ELSIFv_result='合格'THENDBMS_OUTPUT.PUT_LINE('60~79');ELSIFv_result='不及格格'THENDBMS_OUTPUT.PUT_LINE('<60');ELSEDBMS_OUTPUT.PUT_LINE('不存在在该等等级');ENDIF;END;7.2PL/SQL程序结结构【例7.6】从“成成绩表表”中中根据据学生生的学学号和和课程程号查查找学学生某某门课课程的的成绩绩，并并输出出成绩绩的等等级。。采用用CASE语法格格式：：7.2PL/SQL程序结结构DECLAREv_scoreNUMBER(3);BEGINSELECT成绩intov_scoreFROM成绩表表WHERE学号=&学号and课程号号=&课程号号;CASEWHEN(v_score>=90andv_score<=100)THENDBMS_OUTPUT.PUT_LINE('成绩：：'||v_score||'优秀');WHEN(v_score>=80andv_score<90)THENDBMS_OUTPUT.PUT_LINE('成绩：：'||v_score||'良好');WHEN(v_score>=60andv_score<80)THENDBMS_OUTPUT.PUT_LINE('成绩：：'||v_score||'合格');WHEN(v_score>=0andv_score<60)THENDBMS_OUTPUT.PUT_LINE('成绩：：'||v_score||'不及格格');ELSEDBMS_OUTPUT.PUT_LINE('成绩：：'||v_score||'成绩异异常');ENDCASE;END;7.2PL/SQL程序结结构【例7.7】使用CASE语句完完成例例7.5程序。。要求求使用用CASE语句第第二种种语法法格式式实现现，代代码如如下：：DECLAREv_resultVARCHAR2(20):='良好';BEGINCASEv_resultWHEN'优秀'THENDBMS_OUTPUT.PUT_LINE('90~100');WHEN'良好'THENDBMS_OUTPUT.PUT_LINE('80~89');WHEN'合格'THENDBMS_OUTPUT.PUT_LINE('60~79');WHEN'不及格格'THENDBMS_OUTPUT.PUT_LINE('<60');ELSEDBMS_OUTPUT.PUT_LINE('不存在在该等等级');ENDCASE;END;7.2PL/SQL程序结结构7.2.2循环结结构循环结结构可可以对对一段段语句句反复复执行行，直直到满满足某某一条条件件，跳跳出循循环。循环结结构常常用到到的3种语法法格式式：LOOP……ENDLOOP结构WHILE…LOOP……ENDLOOP结构FOR…LOOP……ENDLOOP结构7.2PL/SQL程序结结构【例7.8】利用LOOP循环和和EXIT语句计计算1至100的自然然数之之和。。代码码如下下：DECLAREv_iNUMBER:=1;v_sNUMBER:=0;BEGINLOOPEXITWHENv_i>100;v_s:=v_s+v_i;v_i:=v_i+1;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1~100自然数数之和和：'||v_s);END;7.2PL/SQL程序结结构【例7.9】利用WHILE循环计计算1至100的自然然数之之和。。代码码如下下：DECLAREv_iNUMBER:=0;v_sNUMBER:=0;BEGINWHILEv_i<=100LOOPv_s:=v_s+v_i;v_i:=v_i+1;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1~100自然数之和和：'||v_s);END;7.2PL/SQL程序结构【例7.10】利用用FOR循循环语句计计算1至100的自自然数之和和。代码如如下：DECLAREv_sNUMBER:=0;BEGINFORv_iIN1..100LOOPv_s:=v_s+v_i;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1~100自然然数之和：：'||v_s);END;思考题：如如何利用循循环语句生生成数据？？7.2PL/SQL程序结构7.2.3顺序结构顺序结构是是一种最简简单的控制制结构，程程序按语句句顺序依次次执行。有有时为了打打破执行顺顺序，将当当前程序跳跳转到另一一个地方继继续运行，，需要借助助GOTO语句。GOTO语句是一种种无条件跳跳转语句，，能够将语语句无条件件地跳转到到一个用标标签指定的的语句，其其目的地必必须与GOTO语句在同一一程序块中中或更高层层次（比如如父块中））中。但过过多的使用用GOTO语句会导致致程序复杂杂，破坏程程序的结构构。因此，，GOTO语句一般只只用在从一一个PL/SQL块中跳转到到异常处理理语句的前前面7.2PL/SQL程序结构【例7.11】】利用GOTO语句计算1至100的自然数之之和。代码码如下：DECLAREv_iNUMBER:=0;v_sNUMBER:=0;BEGIN<<label_1>>v_i:=v_i+1;IFv_i<=100THENv_s:=v_s+v_i;GOTOlabel_1;ENDIF;DBMS_OUTPUT.PUT_LINE('1~100自然数之和和：'||v_s);END;7.2PL/SQL程序结构7.2.4NULL结构NULL语句是一个个空操作的的执行语句句，通过执执行NULL语句，可以以直接将控控制传递到到下一条语语句，以提提高PL/SQL块的可读性性。【例7.12】】根据输入的的学生学号号和课程号号，从“成成绩表”中中查找学生生的课程成成绩，如果果成绩低于于60分，将“是是否补考””字段的值值修改为““是”，否否则不执行行任何操作作。7.2PL/SQL程序结构DECLAREv_学号成成绩表.学号%TYPE;v_课程号号成绩表表.课程号号%TYPE;v_成绩成成绩表.成绩%TYPE;BEGINv_学号:='&学号';v_课程号号:='&课程号号';SELECT成绩绩INTOv_成绩FROM成成绩表WHERE学号=v_学学号AND课程程号=v_课程号;IFv_成绩<60THENUPDATE成绩绩表SET是否否补考='是'WHERE学号=v_学学号AND课程程号=v_课程号号;DBMS_OUTPUT.PUT_LINE(v_学号号||'号号学生'||v_课程号||'号课课程'||'成绩为为：'||v_成绩||'需要要补考');ELSENULL;ENDIF;END;思考：如何对表中中所有数据据进行判断断并设置补补考信息？？7.3异常处理注意：commit语句7.3异常处理在语句执行行过程中，，因为各种种原因是的的语句不能能正常执行行时，就有有可能造成成更大错误误或整个系系统的崩溃溃，所以应应该采取必必要的措施施来防止这这种情况的的发生。PL/SQL提供了异常常这一处理理错误情况况的方法。。在PL/SQL代码部分执执行的过程程中无论何何时发生错错误，控制制将自动转转向执行异异常部分。。PL/SQL有三种异常常：系统预定义义异常、非非预定义异异常和用户户自定义异异常。7.3异常处理7.3.1系统预定义义异常系统预定义义异常处理理是针对PL/SQL程序编译、、执行过程程中发生的的问题进行行处理的程程序，当一一段PL/SQL程序违反Oracle规则或是超超出系统特特定限制时时就会引发发异常。例例如，如果果SELECTINTO语句没有返返回记录，，或者在已已经达到结结果集末尾尾的情况下下，Oracle将激活预定定义异常NO_DATA_FOUND。系统中预定定义的异常常如表7-9所示。7.3异常处理系统预定义义异常的抛抛出和处理理的语法格格式如下：：EXCEPTIONWHEN异常名1[OR异常名2…]THEN异常处理段段1；[WHEN异常名3[OR异常名4…]THEN异常处理段段2；[WHENOTHERSTHEN异常处理段段3；]]7.3异常处理【例7.13】根据据输入的学学生姓名，，在“学生生表”中查查找并输出出其相关信信息，如果果不存在该该学生，进进行异常处处理。代码码如下：7.3异常处理DECLAREv_学号学学生表.学号%TYPE;v_性别学学生表.性别%TYPE;v_入学时时间学生生表.入学学时间%TYPE;BEGINSELECT学号号,性别,入学时间间INTOv_学号,v_性别,v_入学学时间FROM学学生表WHERE姓名='&姓名';DBMS_OUTPUT.PUT_LINE('学号：：'||v_学号);DBMS_OUTPUT.PUT_LINE('性别：：'||v_性别);DBMS_OUTPUT.PUT_LINE('入学时时间：'||v_入入学时间);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('没有该该学生!');END;作业二、用用%ROWTYPE完成上述问问题。7.3异常处理7.3.2非预定义异异常预定义异常常只能用于于处理21种Oracle系统异常，，而PL/SQL块运行时可可能还会遭遭遇其他Oracle异常，为了了提高PL/SQL程序块的健健壮性，系系统提供了了非预定义义异常，用用于处理与与预定义异异常无关的的Oracle异常。非预定义异异常处理步步骤如下：：在声明部分分，用EXCEPTION类型定义异异常的名称称。在声明部分分，使用EXCEPTION_INIT编译指令建建立该异常常名称与某某个Oracle异常的关联联。当该Oracle异常发生时时，就会自自动进行处处理。在异常处理理部分，处处理异常。。7.3异常处理EXCEPTION_INIT的语法格式式为：PRAGMAEXCEPTION_INIT（exception_name,oracle_error_code）其中：exception_name是定义的异异常名称；；oracle_error_code是Oracle的异常代码码。7.3异常处理【例7.14】“学学生表”中中学号字段段不允许为为空，当插插入一个空空值时，系系统会产生生错误代码码为-1400（-01400）的异常，对对这个错误误进行异常常处理的代代码如下：：DECLAREe_null_errorEXC