oracle10g实战PLSQL介绍.pptx

第第5章章 PL/SQL介绍介绍 SQL(Structure Query Language)的含义是结构化查询语句，最早由Boyce和Chambedin在1974年提出，称为SEQUEL语言。1976年，IBM公司的San Jose研究所在研制关系数据库管理系统System R时修改为SEQUEL2，即目前的SQL语言。1976年，SQL开始在商品化关系数据库管理系统中应用。1982年美国国家标准化组织ANSI确认SQL为数据库系统的工业标准。目前，许多关系型数据库供应商都在自己的数据库中支持SQL语言，如：Access、Sybase、SQL Server、Infomix、DB2等。5.1.1PL/SQL语言语言PL/SQL语言由以下几部分组成：(1)数据定义语言(DDL)用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。基本的DDL命令及功能如表5.1所示。表表5.1基本的基本的DDL命令命令5.1.1PL/SQL语言语言(2)数据操纵语言用于操纵数据库中各种对象、检索和修改数据。需要注意的是，使用DML语句对某一种数据对象进行操作时，要求必须拥有该对象的相应操作权限或拥有相应的系统权限。DML包括的主要语句及功能如表5.2所示。(3)数据控制语言用于安全管理、确定哪些用户可以查看或修改数据库中的数据，DCL包括的主要语句及功能如表5.3所示。表表5.2 DML命令命令表表5.3 DCL命令命令5.1.1PL/SQL语言语言用PL/SQL编制的程序代码如下所示 DECLARE xm varchar2(8):=张琼;zym varchar2(10):=计算机;zxf number(2):=45;/*定义变量类型*/BEGIN UPDATE XS SET zxf=zxf WHERE xm=xm;/*更新学生表*/IF SQL%NOTFOUND THEN /*检查记录是否存在，如果不存在就插入记录*/INSERT INTO XS(XH,XM,ZYM,ZXF)VALUES(xs_sequence.NEXTVAL,xm,zym,zxf);END IF;END;使用PL/SQL的主要有以下好处：(1)有利于客户/服务器环境应用的运行。(2)适合于客户环境。5.1.2PL/SQL的特点的特点Oracle对PL/SQL进行了扩展，在许多方面增强了PL/SQL的功能，主要体现在以下方面：(1)SQL和PL/SQL编译器集成PL/SQL现在支持SQL所有范围的语法，如INSERT、UPDATE、DELETE等。(2)支持CASE语句和表达式。(3)继承和动态方法释放。(4)类型进化。(5)新的日期/时间类型。(6)PL/SQL代码的本地编译。(7)改善了全球和国家语言支持。(8)表函数和游标表达式。(9)多层集合。(10)对LOB数据类型更好地集成。(11)对批操作的增强。(12)MERGE语句。5.1.2PL/SQL的特点的特点使用PL/SQL，用户可以使用SQL语句操作Oracle数据和用于处理数据的流控语句，而且可以声明变量和常量、定义过程和函数、跟踪运行错误。PL/SQL将SQL的数据操纵功能同过程语言的数据处理功能结合在一起。此外，PL/SQL还具有以下特性：1.数据抽象数据抽象数据抽象可以从数据结构中提取必要的属性，忽略不必要的细节。一旦设计了数据结构，就可以忽略它的细节，从而设计操纵数据结构的算法。2.信息隐藏信息隐藏使用信息隐藏，用户只能看到算法和数据结构设计的给定层次上的信息。信息隐藏使高层设计决策与底层设计细节相分离。5.2 PL/SQL字符集字符集和所有其他程序设计语言一样，PL/SQL也有一个字符集。用户能从键盘上输入的字符都是PL/SQL的字符。此外，在某些场合，还有使用某些字符的规则。5.2.1 合法字符合法字符在使用PL/SQL进行程序设计时，可以使用的有效字符包括以下3类：所有的大写和小写英文字母；数字09；符号()+*/=!；：.%,#?。PL/SQL标识符的最大长度为30个字符，不区分大小写。但是适当地使用大小写可以提高程序的可读性。5.2.2运算符运算符Oracle提供了三类运算符：算术运算符、关系运算符和逻辑运算符。1.算术运算符算术运算符算术运算符执行算术运算。算术运算符有：+（加）、-（减）、*（乘）、/（除）*(指数)和（连接）。其中(加)和(减)运算符也可用于对DATE(日期)数据类型的值进行运算。【例例5.1】求学生的年龄。SELECT(SYSDATECSSJ)AS 年龄FROM XS;其中：SYSDATE是当前系统时间，CSSJ是出生时间。5.2.2运算符运算符2.关系运算符关系运算符关系运算符(又称比较运算符)有下面几种：(1)(等于)、或!=(不等于)、(大于)、=(大于等于)、=和=40 AND ZXF45 AND ZYM=计算机;(3)查询计算机系和通信工程系学生的基本情况。SELECT XH,XM,ZYM,ZXFFROM XSWHERE ZYM=计算机 OR ZYM=通信工程;5.2.3其他符号其他符号PL/SQL为支持编程，还使用其他一些符号。表5.4列出了部分符号，它们是最常用的，也是使用PL/SQL的所有用户都必须了解的。表表5.4 部分其他常用符号部分其他常用符号 5.3 PL/SQL变量、常量和数据类型变量、常量和数据类型5.3.1变量变量变量就是指可以由程序读取或赋值的存储单元。变量用于临时存放数据，变量中的数据随着程序的运行而变化。1.变量的声明变量的声明数据在数据库与PL/SQL程序之间是通过变量进行传递的。变量通常是在PL/SQL块的声明部分定义的。每个变量都有一个特定的类型，变量的类型定义了变量可以存放的信息类别。PL/SQL变量可以与数据库列具有同样的类型。变量名必须是一个合法的标识符，变量命名规则如下：(1)变量必须以字母(AZ)开头(2)其后跟可选的一个或多个字母、数字(09)或特殊字符$、#或_(3)变量长度不超过30个字符(4)变量名中不能有空格5.3.1变量变量1.变量的声明变量的声明表5.5给出了几个变量名实例并评价了其合法性。在使用变量前，首先要声明变量。变量定义的基本格式为：(宽度):=；例如：定义一个长度为10B的变量count，其初始值为1，是varchar2类型。count varchar2(10):=1;表表5.5是否合法的变量名是否合法的变量名5.3.1变量变量2.变量的属性变量的属性变量有名字及其数据类型两个属性。变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。%用来表示属性提示符。(1)%TYPE%TYPE属性提供了变量和数据库列的数据类型。在声明一个包含数据库值的变量时非常有用。例如，在表XS中包含XH列，为了声明一个变量my_xh与XH列具有相同的数据类型，可使用点和%TYPE属性，格式如下：my_xh XS.XH%TYPE;使用%TYPE声明具有以下两个优点：不必知道XH列的确切的数据类型；如果改变了XH列的数据库定义，my_xh的数据类型在运行时会自动进行修改。5.3.1变量变量2.变量的属性变量的属性(2)%ROWTYPE可以使用%ROWTYPE属性声明描述表的行数据的记录，对于用户定义的记录，必须声明自己的域。记录包含唯一的命名域，具有不同的数据类型。DECLARE TYPE TimeRec IS RECORD(HH number(2),MM number(2);TYPE MeetingTyp IS RECORD (Meeting_Date date,Meeting_Time TimeRec,Meeting_Addr varchar2(20),Meeting_Purpose varchar2(50)3.变量的作用域变量的作用域变量的作用域是指可以访问该变量的程序部分。对于PL/SQL变量来说，其作用域就是从变量的声明到语句块的结束。当变量超出了作用域时，PL/SQL解析程序就会自动释放该变量的存储空间。5.3.3常用数据类型常用数据类型1.VARCHAR类型类型语法格式：语法格式：var_field VARCHAR(n);其中长度值n是本变量的最大长度且必须是正整数，例如：var_field VARCHAR(11);在定义变量时，可以同时对其进行初始化，例如：var_field VARCHAR(11):=Hello world;2.NUMBER类型NUMBER数据类型可用来表示所有的数值类型。语法格式：num_field NUMBER(precision,scale);其中precision表示总的位数；scale表示小数的位数，scale缺省表示小数位为0。如果实际数据超出设定精度则出现错误。例如：num_field NUMBER(10,2);num_field是一个整数部分最多使8位，小数部分最多是2的变量。5.3.3常用数据类型常用数据类型3.DATE用来存放日期时间类型数据，用7个字节分别描述年、月、日、时、分、秒。语法格式：语法格式：date_field DATE;日期缺省格式为DD-MON-YY，分别对应日、月、年，例如17-JUN-2002。注意，月份的表达要用英文单词的缩写格式。日期的格式可以设置为中文格式，例如17-六月-2002。4.BOOLEAN逻辑型(布尔型)变量的值只有true(真)或false(假)。逻辑型变量一般用于判断状态，然后根据其值是“真”或是“假”来决定程序执行分支。关系表达式的值就是一个逻辑值。5.3.4对象类型对象类型在多表操作的情况下，当多个表中的列要存储相同类型的数据时，要确保这些列具有完全相同的数据类型、长度和为空性（数据类型是否允许空值）。用户自定义数据类型student_num后，可以重新设计学生成绩管理数据库表XS、XS_KC结构中的学号字段。如下表5.65.8所示。表表5.6自定义类型自定义类型student_num表表5.7表表XS中学号字段的重新设计中学号字段的重新设计表表5.8 表表XS_KC中学号字段的重新设计中学号字段的重新设计5.3.4对象类型对象类型创建用户自定义数据类型的方法如下：1.利用利用OEM定义定义(1)启动OEM并登录到XSCJ数据库，如图5.1所示 图图5.1 Oracle企业管理器企业管理器 5.3.4对象类型对象类型1.利用利用OEM定义定义(2)在如图5.1所示的界面中选择单击“对象类型”，进入“对象搜索”界面，如图5.2所示。图图5.2 对象搜索界面对象搜索界面5.3.4对象类型对象类型1.利用利用OEM定义定义(3)单击“创建”按钮，进入“创建对象类型”界面，如图5.3所示。在此指定自定义对象的名称以及所属用户方案。图图5.3 创建对象类型窗口创建对象类型窗口 5.3.4对象类型对象类型1.利用利用OEM定义定义(4)单击属性栏的“添加”按钮，打开“添加预定义类型属性”界面，如图5.4所示。在该界面中的“名称”文本框为自定义数据类型指定名称。然后在“数据类型”选项中设置：类型：数据类型。长度：允许的最大长度，只适用于CHAR,VARCHAR,NUM,FLOAT和RAW。精度：如果是NUMBER类型则可以指定精度。(5)单击“确定”按钮，切换到创建对象类型界面，在该窗口的“属性”栏会出现上一步定义的属性信息。(6)单击“创建”按钮，系统创建对象。创建成功后，返回到如图5.2所示的对象类型界面。可以使用方法（Method）绑定数据和代码。方法为过程或函数，它是对象类型定义的一部分，并且可在对象类型的数据属性上进行操作。这是实现封装的必由之路，是面向对象的数据库的基础。5.3.4对象类型对象类型1.利用利用OEM定义定义图图5.4 添加预定义类型属性添加预定义类型属性 5.3.4对象类型对象类型2.利用命令定义利用命令定义语法格式：语法格式：CREATE OR REPLEACE TYPE schema.type_nameAUTHID CURRENT_USER|DEFINER AS OBJECT(attribute1 datatype,attribute2 datatype,attributen datatype method1 method2 methodn5.3.4对象类型对象类型2.利用命令定义利用命令定义其中：其中：schema：用户自定义类型所属方案。type_name：用户自定义类型名称。AUTHID：指示将来执行该方法时，必须使用在创建时定义的CURRENT_USER或DEFINER的权限集合。CURRENT_USER是调用该方法的用户，DEFINER是该对象类型的所有者。Attribute1：对象类型的属性。属性的声明有一些限制，包括：属性的声明必须在方法声明之前。数据类型可以任何数据库类型，但是不能包括ROWID、UROWID、LONG、LONG RAW、NCHAR、NCLOB、NVARCHAR2类型，以及PL/SQL的专用类型或在PL/SQL包中定义的类型。不能使用那些智能在PL/SQL中使用而不能在数据库中使用的数据类型。这些类型包括：BINARY_INTEGER、BOOLEAN、PLS_INTEGER、RECORD和REF CURSOR。不能使用NOT NULL约束，但是可以通过在对象的实例上定义一个数据库触发器达到类似的效果。属性列表中至少有一个属性。不能使用默认值。method：定义方法。方法就是过程或函数，它们是在属性声明之后进行声明的。属性描绘对象的特征，而方法是作用在这些特征上的动作。5.3.4对象类型对象类型【例例5.4】定义一个简单的对象类型。CREATE OR REPLEACE TYPE TEST_OBJ AS OBJECT (item_id CHAR(6),price NUMBER(10,2);5.3.5数据类型转换数据类型转换PL/SQL可以进行数据类型之间的转化。常见的数据类型之间的转化函数如下：(1)TO_CHAR：将NUMBER和DATE类型转换成VARCHAR2类型。(2)TO_DATE：将CHAR转换成DATE类型。(3)TO_NUMBER：将CHAR转换成NUMBER类型。此外，PL/SQL还会自动地转换各种类型，如下例所示：DECLARE xh varchar2(6);BEGIN SELECT MAX(XH)INTO xh FORM XS;END;5.3.5数据类型转换数据类型转换PL/SQL可以在某些类型之间自动转换，但是使用转换函数对于程序的可读性等都是一个较好的习惯。对于上面的例子，可以使用TO_CHAR转换函数将NUMBER类型进行转换，具体代码如下：DECLARE xh varchar2(6);BEGIN SELECT TO_CHAR(MAX(XH)INTO xh FORM XS;END;5.4 PL/SQL基本程序结构和语句基本程序结构和语句PL/SQL的基本逻辑结构包括顺序结构、条件结构和循环结构。除了顺序执行的语句外，PL/SQL主要通过条件语句和循环语句来控制程序执行的逻辑顺序，这就是所谓的控制结构。控制结构是所有程序设计语言的核心。检测不同条件并加以处理是程序控制的主要部分。5.4.1 条件结构条件结构1.IF逻辑结构逻辑结构IF逻辑结构有3种表达式。(1)IF-THEN语法格式：语法格式：IF Boolean_expression THEN /*条件表达式*/Run_expression /*条件表达式为真时执行*/END IF这个结构用于测试一个简单条件。如果Boolean_expression条件表达式为true，则执行Run_expression操作。IF-THEN语句可以用流程图5.5表示。5.4.1条件结构条件结构1.IF逻辑结构逻辑结构 图图5.5 IF-THEN的执行流程的执行流程 5.4 PL/SQL基本程序结构和语句基本程序结构和语句【例例5.5】查询总学分40的学生人数。DECLAREv_num number(3);BEGIN SELECT COUNT(*)INTO v_numFROM xsWHERE zxf40;IF v_num0 THEN dbms_output.put_line(总学分40的人数为：|TO_CHAR(v_num);END IF;END;IF-THEN语句可以嵌套使用 5.4 PL/SQL基本程序结构和语句基本程序结构和语句【例例5.6】判断计算机系总学分40的人数是否超过80人。DECLAREv_num number(3);BEGIN SELECT COUNT(*)INTO v_num FROM xs WHERE zxf40 AND zym=计算机;IF v_num0 THEN IF v_num80 THEN dbms_output.put_line(计算机系总学分40的人数超过80人);END IF;END IF;END;5.4 PL/SQL基本程序结构和语句基本程序结构和语句1.IF逻辑结构逻辑结构(2)IF-THEN-ELSE语法格式：语法格式：IF Boolean_expression THEN /*条件表达式*/Run_expression /*条件表达式为真时执行*/ELSE Run_expression /*条件表达式为假时执行*/END IF这种结构与IF语句非常相似，唯一不同的是在条件为FALSE时，执行跟在ELSE后的一条或多条语句。IF-THEN-ELSE语句可以用流程图5.6所示。5.4 PL/SQL基本程序结构和语句基本程序结构和语句1.IF逻辑结构逻辑结构 图图5.6 IF-THEN-ELSE语句的执行流程语句的执行流程 5.4 PL/SQL基本程序结构和语句基本程序结构和语句【例例5.7】如果“数据库原理”课程的平均成绩高于75，则显示“平均成绩大于75”，否则显示“平均成绩小于75”。DECLARE v_avg number(4,2);BEGINSELECT AVG(cj)INTO v_avgFROM xs,xs_kc,kcWHERE xs.xh=xs_kc.xh AND xs_kc.kch=kc.kch AND kc.kcm=数据库原理;IF v_avg75 THENdbms_output.put_line(平均成绩大于75);ELSEdbms_output.put_line(平均成绩小于75);END IF;END;IF-THEN-ELSE语句也可以嵌套。5.4 PL/SQL基本程序结构和语句基本程序结构和语句1.IF逻辑结构逻辑结构(3)IF-THEN-ELSIF-THEN-ELSE语法格式：语法格式：IF Boolean_expression1 THEN Run_expression1 ELSIF Boolean_expression2 THEN Run_expression2 ELSERun_expression3 END IFIF-THEN-ELSIF-THEN-ELSE语句可以用流程图5.7表示。5.4 PL/SQL基本程序结构和语句基本程序结构和语句1.IF逻辑结构逻辑结构 图图5.7 IF-THEN-ELSIF-THEN-ELSE执行流程执行流程5.4 PL/SQL基本程序结构和语句基本程序结构和语句【例例5.8】求ax2+bx+c=0的根。DECLARE a number;b number;c number;x1 number;x2 number;d number;BEGIN a:=1;b:=4;c:=3;d:=b*b-4*a*c;IF a=0 THEN x1:=-c/b;dbms_output.put_line(只有一个平方根|to_char(x1);ELSIF d10 THEN EXIT;END IF;END LOOP;dbms_output.put_line(to_char(n);END;5.4.2循环结构循环结构2.LOOP-EXIT-WHEN-END循环循环除退出条件检测有所区别外，此结构与前一个循环结构类似。语法格式：语法格式：LOOPRun_expression /*执行循环体*/EXIT WHEN Boolean_expression /*测试是否符合退出条件*/END LOOP;其流程图可用LOOP-EXIT-END流程图来表示，如流程图5.8所示。5.4.2循环结构循环结构【例例5.10】求10的阶乘。DECLARE n NUMBER:=1;count1 NUMBER:=2;BEGIN LOOPn:=n*count1;count1:=count1+1;EXIT WHEN count1=11;END LOOP;dbms_output.put_line(to_char(n);END;5.4.2循环结构循环结构3.WHILE-LOOP-END循环循环语法格式：语法格式：WHILE Boolean_expression /*测试Boolean_expression是否符合退出条件*/LOOPRun_expression /*执行循环体*/END LOOP;此结构的执行如流程图5.9所示。流程图流程图5.9 WHILE-LOOP-END执行流程执行流程5.4.2循环结构循环结构【例例5.11】用WHILE-LOOP-END循环结构求10的阶层。DECLAREn NUMBER:=1;count1 NUMBER:=2;BEGINWHILE count1=10 LOOPn:=n*count1;count1:=count1+1;END LOOP;dbms_output.put_line(to_char(n);END;5.4.2循环结构循环结构4.FOR-IN-LOOP-END循环循环语法格式：语法格式：FOR count IN count_1.count_n /*定义跟踪循环的变量*/LOOPRun_expression /*执行循环体*/END LOOP;FOR-IN-LOOP-END的执行如流程图5.10所示。流程图流程图5.10 循环结构的执行流程循环结构的执行流程5.4.2循环结构循环结构【例例5.12】用FOR-IN-LOOP-END循环结构求10的阶层。DECLAREn NUMBER:=1;count1 NUMBER;BEGINFOR count1 IN 2.10LOOPn:=n*count1;END LOOP;dbms_output.put_line(to_char(n);END;程序中变量count是控制循环次数的计数器。计数器初始值是2，终值是10，步长为1。每循环一次，count会自动累加1，直到count大于终值10(count=11时)跳出循环。5.4.3选择和跳转语句选择和跳转语句1.CASE语句语句CASE语句是在Oracle9i才引入的，它可以使用简单的结构，对数值列表做出选择。更为重要的是，它还可以用来设置变量的值。语法格式：CASE input_nameWHEN expression1 THEN result_expression1WHEN expression2 THEN result_expression2WHEN expressionN THEN result_expressionELSE result_expressionNEND;5.4.3选择和跳转语句选择和跳转语句【例例5.13】CASE语句应用。DECLAREV_kch char(3);v_Result varchar2(16);BEGINSELECT kchINTO v_kchFROM kc WHERE kkxq=1;CASE v_kch /*判断v_kch的值，并给出结果*/WHEN 101 THEN v_Result:=计算机基础;WHEN 102 THEN v_Result:=程序设计语言;WHEN 206 THEN v_Result:=离散数学;WHEN 208 THEN v_Result:=数据结构;ELSEv_Result:=Nothing;END CASE;dbms_output.put_line(v_result);END;5.4.3选择和跳转语句选择和跳转语句2.GOTO语句语句PL/SQL提供GOTO语句，实现将执行流程转移到标号指定的位置。格式如下：格式如下：GOTO labelLabel是指向的语句标号，标号必须符合标识符规则。标号的定义形式：语句使用GOTO语句，可以控制执行顺序。5.4.3选择和跳转语句选择和跳转语句【例例5.14】设有一表temp(xh char(6),xb char(2),xm)，初始化表temp。DECLARE v_counter BINARY_INTEGER:=1;v_xh number(6);BEGIN v_xh:=100001;LOOP INSERT INTO temp(xh,xb)VALUES(to_char(v_xh),男);v_counter:=v_counter+1;v_xh:=v_xh+1;IF v_counter=10 THENGOTO loop_end;END IF;END LOOP;dbms_output.put_line(Init Ok);END;5.4.4异常异常1.预定义异常预定义异常预定义异常是由运行系统产生的。例如出现被0除，PL/SQL就会产生一个预定义的ZERO_DIVIDE异常。【例例5.15】ZERO_DIVIDE异常。使用系统预定义的异常处理，使用该处理后，程序运行时系统就不会提示出现错误。DECLAREv_number number(2):=10;v_zero number(2):=0;v_result number(5);BEGINv_result:=v_number/v_zero;/*用v_number除以v_zero，即10/0，从而产生除零错误*/EXCEPTIONWHEN ZERO_DIVIDE THENDBMS_OUTPUT.PUT_LINE(DIVIDE ZERO);END;系统的运行结果为：DIVIDE ZEROPL/SQL过程已成功完成。5.4.4异常异常1.预定义异常预定义异常除了除零错误外，PL/SQL还有很多系统预定义异常，表5.9列出了常见的PL/SQL异常，通过检测这些异常，用户可以查找到PL/SQL程序产生的错误。表表5.9 PL/SQL中最常见的异常中最常见的异常5.4.4异常异常【例例5.16】转换错误处理。DECLAREv_number number(5);v_result char(5):=2w;BEGINv_number:=to_number(v_result);EXCEPTIONWHEN VALUE_ERROR THENDBMS_OUTPUT.PUT_LINE(CONVERT TYPE ERROR);END;5.4.4异常异常【例例5.17】联合的错误处理。DECLAREv_result xs.xm%TYPE;BEGINSELECT xm INTO v_result FROM xs WHERE xm=王林;DBMS_OUTPUT.PUT_LINE(The student name is v_result);EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(There has TOO_MANY_ROWS error);WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There has NO_DATA_FOUND error);END;5.4.4异常异常2.用户定义异常用户定义异常用户可以通过自定义异常来处理错误的发生，调用异常处理需要使用RAISE语句。语法格式：语法格式：EXCEPTIONWHEN exception_name THENsequence_of_statements1;WHEN THENsequence_of_statements2;WHEN OTHERS THENsequence_of_statements3;END;5.4.4异常异常【例例5.18】自定义异常处理。DECLAREe_overnumber EXCEPTION;/*定义异常处理变量*/v_xs_number number(9);v_max_xs_number number(9):=500;BEGINSELECT COUNT(*)INTO v_xs_numberFROM xs;IF v_max_xs_numberv_xs_number THENRAISE e_overnumber;END IF;EXCEPTION WHEN e_overnumber THEN DBMS_OUTPUT.PUT_LINE(Current Xs Number is:v_xs_number max allowed is:v_max_xs_number);END;5.4.4异常异常【例例5.19】使用OTHERS异常。DECLAREv_result number;BEGINSELECT xm INTO v_resultFROM xs WHERE xh=010010;DBMS_OUTPUT.PUT_LINE(The student name isv_result);EXCEPTIONWHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE(There has TOO_MANY_ROWS error);WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(There has TOO_MANY_ROWS error);WHEN OHTERS THENDBMS_OUTPUT.PUT_LINE(Unkown error);END;5.4.5空操作和空值空操作和空值有时特别是在使用IF逻辑时，用户结束测试一个条件。当测试条件为TRUE，什么工作都不做；而当测试值为FALSE时，则执行某些操作。这在PL/SQL中以下述方法处理：IF n0 THENNULL;ELSEDBMS_OUTPUT.PUT_LINE(正常);关键字NULL表示不执行操作。5.5系统内置函数系统内置函数Oracle9i提供了许多功能强大的函数，在编程中经常用到的有以下几类。1.常用数学运算函数，如表常用数学运算函数，如表5.10所示。所示。表表5.10 常用的数学函数常用的数学函数5.5系统内置函数系统内置函数2.常用字符串函数，如表常用字符串函数，如表5.11 所示。所示。表表5.11 常用字符串函数常用字符串函数5.5系统内置函数系统内置函数3.统计函数统计函数Oracle提供丰富的统计函数用来处理数值型数据，表5.12列出常用的统计函数。表表5.12 常用统计函数常用统计函数5.5系统内置函数系统内置函数4.常用日期函数常用日期函数Oracle提供丰富的日期函数用来处理日期型数据，表5.13 给出了常用日期型函数。表表5.13 常用日期函数常用日期函数5.6用户定义函数用户定义函数5.6.1SQL语句方式创建语句方式创建1.语法格式语法格式CREATE OR REPLACE FUNCTION function_name /*函数名称*/(parameter_name1,mode1 datatype1,/*参数定义部分*/parameter_name2,mode2 datatype2,parameter_name3,mode3 datatype3,)RETURN return_datatype /*定义返回值类型*/IS/ASBEGINfunction_body /*函数体部分*/RETURN scalar_expression /*返回语句*/END function_name;5.6.1SQL语句方式创建语句方式创建1.语法格式语法格式其中：其中：function_name：用户定义的函数名。函数名必须符合标识符的定义规则，对其所有者来说，该名在数据库中是唯一的。parameter：用户定义的参数。用户可以定义一个或多个参数。mode：参数类型。datatyep：用户定义参数的数据类型。Return_datatype：函数返回值的数据类型。函数返回scalar_expression表达式的值。Function_body函数体由PL/SQL语句构成。5.6.1SQL语句方式创建语句方式创建2.示例示例给出一个函数，说明其3种参数的合法性。CREATE OR REPLACE FUNCTION explain_parameter(in_pmt IN char,out_pmt OUT char,in_out_pmt IN OUT char )RETURN char ASreturn_char char;BEGIN RETURN(return_char);END explain_parameter;5.6.1SQL语句方式创建语句方式创建2.示例示例函数语句序列及其可能出现的情况如下：in_pmt:=hello;该语句是错误的，因为IN类型的参数只能作为形参来传递值，不能在函数体中赋值。return_char:=in_pmt;该语句语法正确。因为IN类型参数本身就是用来传递值，而return_char是作为返回值变量。通过IN类型参数in_char赋值return_char。out_pmt:=hello;该语句正确。因为out_pmt作为OUT类型参数，在函数体内被赋值是允许的。return_char:=out_pmt;该语句不正确。因为OUT类型参数不能传递值。in_out_pmt:=world;该语句正确。因为IN OUT参数可以在函数体中被赋值。return_char:=in_out_pmt;该语句正确。因为IN OUT类型参数既能传递值，也可以赋值。5.6.1SQL语句方式创建语句方式创建【例例5.26】计算全体学生的平均成绩。CREATE OR REPLACE FUNCTION average (cnum IN char)RETURN NUMBERASavger NUMBER;BEGINSELECT AVG(CJ)INTO avgerFROM XS_KCWHERE KCH=cnumGROUP BY KCH;RETURN(avger);END average;5.6.1SQL语句方式创建语句方式创建【例例5.27】创建一个统计数据库中不同性别人数的函数。CREATE OR REPLACE FUNCTION count_num /*定义函数count_num*/(in_sex IN char )/*输入参数*/RETURN numberAS out_num number;/*指定返回值*/BEGINIF in_sex=男 THENSELECT COUNT(XB)INTO out_numFROM XSWHERE XB=男;ELSE SELECT COUNT(XB)INTO out_numFROM XSWHERE XB=女;END IF;RETURN(out_num);/*返回变量的值*/END count_num;/*函数结束*/5.6.1SQL语句方式创建语句方式创建3.函数的调用函数的调用无论在命令行还是在程序语句中，函数都可以通过函数名称直接在表达式中调用。语法格式：语法格式：variable_name:=function_name【例例5.28】用函数count_num统计表XS中有多少男同学。DECLARE man_num NUMBER;BEGIN man_num:=count_num(男);dbms_output.put_line(to_char(man_num);END;5.6.1SQL语句方式创建语句方式创建4.函数的释放函数的释放当函数不再使用时，要用drop命令将其从内存中删除。语法格式：语法格式：DROP FUNCTION schema.function_nameSchema是函数的拥有者，function_name是函数名。例如，把函数count_num删除，只需执行如