ERP系统应用咨询（DOC14页）

第7章PL/SQL编程2PL/SQL编程7.1PL/SQL语言基础7.2PL/SQL程序结构7.3异常处理37.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程序权限。47.1PL/SQL语言基础PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何能够运行Oracle的操作系统都是非常便利的。对于SQL，Oracle必须在同一时间处理每一条SQL语句，在网络环境下这就意味着每一个独立的调用都必须被Oracle服务器处理，从而占用大量的服务器时间，导致网络拥挤。而PL/SQL以整个语句块形式发给服务器，可以减少网络拥挤。57.1PL/SQL语言基础7.1.2PL/SQL的基本结构一个PL/SQL程序包含一个或多个逻辑块，每个块都可以划分为三个部分：声明部分（用DECLARE开头）、执行部分（以BEGIN开头）和异常处理部分（以EXCEPTION开头）1、PL/SQL基本结构如下：DECLARE声明部分BEGIN执行部分EXCEPTION异常处理部分END；67.1PL/SQL语言基础其中：声明部分：定义变量和常量的数据类型和初始值，以及程序中要使用的函数、游标、异常处理名称等。如果没有需要声明的内容，可以省略这一部分。PL/SQL中要使用的所有定义都必须在这一部分进行集中定义。执行部分：此部分是PL/SQL块中的指令部分，所有的可执行语句都放在这一部分，包括对数据库的操作语句和各种流程控制语句，也可以嵌套其他的PL/SQL块。执行部分至少包含一条可执行语句。异常处理部分：包含在执行部分中，以关键字EXCEPTION开始，到END结束。当程序检测到错误而产生异常时，就转到由EXCEPTION标识的部分执行异常处理程序。这部分是可选的，利用异常处理可以提高PL/SQL程序的健壮性。77.1PL/SQL语言基础2、PL/SQL的特殊结构BLOCK1/*以名字标识PL/SQL块*/DECLARE声明部分BEGIN执行部分EXCEPTION异常处理部分ENDBLOCK1；DECLARE主块声明部分BEGIN主块执行部分DECLARE子块声明部分BEGIN子块执行部分EXCEPTION子块异常处理部分END；EXCEPTION主块异常处理部分END；带命名块的PL/SQL块嵌套结构的PL/SQL块87.1PL/SQL语言基础7.1.3PL/SQL的字符集1、合法字符，包括：大写字母A-Z和小写字母a-z数字0-9符号()、+、-、*、/、、、=、！、~、^、;、:、.、’、@、”、#、$、_、{}、[]、?制表符、空格符、回车符等非显示的间空符号2、操作符，包括：算术操作符关系操作符逻辑操作符3、其他字符97.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；107.1PL/SQL语言基础2、作用域变量的作用域指变量的有效使用范围，它从变量声明开始，直到当前程序块结束，只有在其作用域范围内，程序才能使用该变量，否则将导致编译错误。在同一程序块中，不允许声明两个同名变量，但在不同程序块内可以声明两个同名变量。在程序块内声明的变量称为局部变量，在程序块外声明的变量称为全局变量。引用程序块内的局部变量不需要加限定词，而引用块外的全局变量需要加限定词（即父块的名称）。子块中可以引用父块的变量，但是父块中不能引用子块的变量。如果父块与子块具有相同的变量名，在子块中引用这个变量时若是不加限定词则引用的是子块内的局部变量。若两个块不是父子关系，则他们之间的变量不能相互引用。117.1PL/SQL语言基础（3）变量的属性变量有名字和数据类型两个属性。变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。在PL/SQL中，使用“%”表示属性提示符，后面紧跟属性名。%TYPE和%ROWTYPE是PL/SQL中两个特殊的属性，常用来定义变量，使被定义变量的数据类型与一个已定义变量（或参照表中的记录）的数据类型相一致。%TYPE使用%TYPE属性定义变量时，被定义变量的数据类型与一个已经定义了的变量的数据类型相一致，当被参照的变量数据类型改变后，新定义变量的数据类型也随之改变。当无法准确知道被参照变量的数据类型时，只能采用这种定义方法。定义格式为：新变量名被参照表.被参照列%TYPE127.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;语句：Setserverouton137.1PL/SQL语言基础%ROWTYPE使用%ROWTYPE属性能够定义记录变量，使得被定义的记录成员个数、名称、数据类型与已定义的表或视图中列的个数、名称和数据类型完全相同。这种定义方式，可以定义一个表，当被参照表中列及数据类型改变时，新定义表中的列及数据类型自动改变。当一个表有较多列时，使用%ROWTYPE定义记录，比使用%TYPE要方便，并且不容易出错。定义格式为：新表名被参照表%ROWTYPE147.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;157.1PL/SQL语言基础3、数据类型标量类型LOB型数据类型定义的运算符167.1PL/SQL语言基础【例7.3】声明一个TIMESTAMP类型的变量，并为它赋值。代码如下：SETSERVEROUTONDECLAREcheckoutTIMESTAMP(2);BEGINcheckout:='10-6月-0707.48.53.275';DBMS_OUTPUT.PUT_LINE(checkout);END;177.2PL/SQL程序结构7.2.1选择结构选择结构又称条件控制，通过先测试一个条件，根据测试结果选择运行不同的语句段。选择结构允许嵌套执行。选择结构常用到的4种语法格式：IF…THEN…ENDIF结构IF…THEN…ELSE…ENDIF结构IF…THEN…ELSIF…ENDIF结构CASE结构187.2PL/SQL程序结构【例7.4】查找“ASP.NET程序设计”课程，若是“备注”信息为“C#”，将其改为“J#”，否则将“备注”信息置空。197.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;试一试：数据被修改了吗？为什么？作业一、用你熟悉的方法完成该问题，比较两种方法。207.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;217.2PL/SQL程序结构【例7.6】从“成绩表”中根据学生的学号和课程号查找学生某门课程的成绩，并输出成绩的等级。采用CASE语法格式：227.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_score90)THENDBMS_OUTPUT.PUT_LINE('成绩：'||v_score||'良好');WHEN(v_score=60andv_score80)THENDBMS_OUTPUT.PUT_LINE('成绩：'||v_score||'合格');WHEN(v_score=0andv_score60)THENDBMS_OUTPUT.PUT_LINE('成绩：'||v_score||'不及格');ELSEDBMS_OUTPUT.PUT_LINE('成绩：'||v_score||'成绩异常');ENDCASE;END;237.2PL/SQL程序结构【例7.7】使用CASE语句完成例7.5程序。要求使用CASE语句第二种语法格式实现，代码如下：DECLAREv_resultVARCHAR2(20):='良好';BEGINCASEv_resultWHEN'优秀'THENDBMS_OUTPUT.PUT_LINE('90~100');W