第7章 PL SQL编程.doc

第7章 PL/SQL编程 学习目标： 了解PL/SQL语言的特点和应用。 掌握PL/SQL的语句结构。 掌握游标的定义和应用方法。 了解事务在PL/SQL程序中的应用。 熟悉PL/SQL Developer工具的使用方法。SQL语言是对数据库的一种访问语言，其特点是非过程化，不能实现结构控制，提供的编程能力较弱。Oracle作为一个强大的数据库管理系统，迫切需要一种兼容SQL的编程语言，PL/SQL的出现正是为了解决这一问题。PL/SQL(Procedural Language/SQL)是一种过程化语言，它不仅允许嵌入SQL语句，而且它与C、C+、Java等语言一样关注于处理细节，允许定义常量和变量，允许使用条件语句和循环语句，允许使用异常来处理各种错误，因此可以用来实现比较复杂的业务逻辑。本章主要讲述PL/SQL的编程基础，介绍PL/SQL语言特点、语句结构、游标定义和应用方法、事务在程序设计中的应用，以及PL/SQL Developer的开发环境等。7.1 PL/SQL语言基础PL/SQL是Oracle对标准数据库语言的扩展，它不是一个独立的产品，而是一个整合到Oracle服务器和Oracle工具中的技术，从版本6开始PL/SQL就被可靠地整合到Oracle中了，近几年中更多的开发人员和DBA开始使用PL/SQL。可以把PL/SQL看作Oracle服务器内的一个引擎，SQL语句执行者处理单个的SQL语句，PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL引擎处理时，Oracle服务器中的SQL语句执行器处理PL/SQL程序块中的SQL语句。7.1.1 PL/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程序权限 PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何能够运行Oracle的操作系统都是非常便利的。 对于SQL，Oracle必须在同一时间处理每一条SQL语句，在网络环境下这就意味着每一个独立的调用都必须被Oracle服务器处理，从而占用大量的服务器时间，导致网络拥挤。而PL/SQL以整个语句块形式发给服务器，可以减少网络拥挤。7.1.2 PL/SQL的基本结构PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每个块都可以划分为三个部分：声明部分(用DECLARE开头)、执行部分(以 BEGIN开头)和异常处理部分(以EXCEPTION开头)。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，基本结构都是由这三部分组成。PL/SQL块的基本语法结构如下： DECLARE 声明部分 BEGIN 执行部分 EXCEPTION 异常处理部分 END;其中： 声明部分：此部分用来定义变量和常量的数据类型和初始值，以及程序中要使用的函数、游标、异常处理名称等。如果没有需要声明的对象，可以省略这一部分。 执行部分：此部分是PL/SQL块中指令部分，所有可执行语句都放在这一部分，包括对数据库的操作语句和各种流程控制语句，也可以嵌套其他的PL/SQL块。注意，执行部分至少包含一条可执行语句。 异常处理部分：包含在执行部分中。当程序检测到错误而产生异常时，就转到由EXCEPTION标识的部分执行异常处理程序。这部分是可选的，利用异常处理可以提高PL/SQL程序的健壮性。PL/SQL块可以是不包含声明部分或异常处理部分的特殊结构，也可以是带有命名的PL/SQL程序块，语法结构如下： /*命名的PL/SQL程序块*/DECLARE 声明部分BEGIN执行部分EXCEPTION异常处理部分END;PL/SQL程序块中还可以包含PL/SQL程序，即主块中包含子块，其语法结构如下：DECLARE 主块声明部分BEGIN主块执行部分DECLARE 子块声明部分BEGIN子块执行部分EXCEPTION子块异常处理部分END；EXCEPTION主块异常处理部分END;7.1.3 PL/SQL字符集与其他程序设计语言一样，PL/SQL也有其字符集，包括允许使用的合法字符，操作符以及一些常用的字符。1. 合法字符在使用PL/SQL进行程序设计时，可以使用的有效字符包括以下四类： 大写字母AZ和小写字母az 数字09 符号()、+、-、*、/、=、！、;、:、.、”、#、$、_、? 制表符、空格符、回车符等非显示的间空符号可以使用合法字符作为标识符，标识符由一个字母开始，后面选择性的跟随任意多的字母、数字、货币符号($)、下划线(_)、#等符号组成。不允许使用空格、斜线(/)、短横线(-)、&、%。最大长度为30个字符。表7.1列出了部分合法和非法标识符。表7.1 合法和非法的标识符合法的非法的注释XYou&me不允许有&LastNameon/off不允许有/Phone#Stu-name不允许有-t_25_m必须由字母开头Oracle$char_num必须由字母开头2. 操作符PL/SQL提供的操作符有：算术操作符、关系操作符、逻辑操作符。(1)算术操作符用来执行算术运算，表7.2列出了常用的算术操作符。表7.2算术操作符操作符操作说明+加-减/除*乘*乘方(2)关系操作符(又称比较操作符)主要用于测试两个表达式之间的满足关系，其运算结果为逻辑值TRUE或FALSE。表7.3列出了PL/SQL中常用的关系操作符。表7.3关系操作符操作符操作说明小于操作符大于操作符=大于或等于操作符=等于操作符!=不等于操作符不等于操作符:=赋值操作符IS NULL检索空数据。如果操作数为NULL返回TRUELIKE检索匹配字符样式的数据BETWEENAND检索两值之间的内容IN检索匹配列表中的值(3)逻辑操作符用于对某个条件进行测试，运算结果为TRUE或FALSE，表7.4列出了PL/SQL中常用的的逻辑操作符。表7.4逻辑操作符操作符操作说明AND两个表达式为真则结果为真OR只要有一个为真则结果为真NOT取相反的逻辑值3. 其他字符PL/SQL为支持编程，还使用了一些其他符号，表7.5列出了PL/SQL中常用符号。表7.5常用符号操作符操作说明()列表分隔;语句结束:=赋值 字符串定界符|并置-注释符/* */注释定界符7.1.4 PL/SQL基本语法4. 常量与变量PL/SQL编程中使用的常量与变量都要在PL/SQL块的声明部分进行声明。(1)声明声明常量和变量的基本语法如下：CONSTANTNOT NULL(宽度):=注意问题： 常(变)量名必须以字母开头，其后跟可选的一个或多个字母、数字或特殊字符#、$、_，中间不允许有空格，长度不能超过30个字符。 如果有CONSTANT，则表明声明的是常量。 如果有NOT NULL，则表明声明的常(变)量不能为空，即在声明时就必须赋值。如果没有赋值，则表示初始化为NULL。 赋初始值可以是表达式、文本、或者其他变量、函数。例如：声明一个长度为10B的变量count，初值为1，类型为VARCHAR。count VARCHAR2(10):= 1;声明一个NUMBER数据类型的常量c_limit，并赋予初值5000.00。c_limit CONSTANT NUMBER(8.2):=5000.00;声明一个NUMBER数据类型的常量c_area，并用表达式给它赋初值。c_area CONSTANT NUMBER(8.5):=3.14159*3*2;(2)作用域变量的作用域指变量的有效使用范围，它从变量声明开始，直到当前程序块结束，只有在其作用域范围内，程序才能使用该变量，否则将导致编译错误。在同一程序块中，不允许声明两个同名变量，但在不同程序块内可以声明两个同名变量。在程序块内声明的变量称为局部变量，在程序块外声明的变量称为全局变量。引用程序块内的局部变量不需要加限定词，而引用块外的全局变量需要加限定词(即父块的名称)。子块中可以引用父块的变量，但是父块中不能引用子块的变量。如果父块与子块具有相同的变量名，在子块中引用这个变量时若是不加限定词则引用的是子块内的局部变量。若两个块不是父子关系，则他们之间的变量不能相互引用。(3)变量的属性变量有名字和数据类型两个属性。变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。在PL/SQL中，使用“%”表示属性提示符，后面紧跟属性名。%TYPE和%ROWTYPE是PL/SQL中两个特殊的属性，常用来定义变量，使被定义变量的数据类型与一个已定义变量(或参照表中的记录)的数据类型相一致。 %TYPE使用%TYPE属性定义变量时，被定义变量的数据类型与一个已经定义了的变量的数据类型相一致，当被参照的变量数据类型改变后，新定义变量的数据类型也随之改变。当无法准确知道被参照变量的数据类型时，只能采用这种定义方法。定义格式为：%TYPE【例7.1】声明三个变量“v_班级名称”，“v_班级人数”，“v_班主任”，其数据类型分别对应“班级表”中相应字段的数据类型。根据输入的班级代码，输出对应的班级名称、人数和班主任信息。代码如下：Set serverout onDECLAREv_班级名称 班级表.班级名称%TYPE;v_班级人数 班级表.班级人数%TYPE;v_班主任 班级表.班主任%TYPE;BEGINSELECT 班级名称,班级人数,班主任 into v_班级名称,v_班级人数,v_班主任FROM 班级表WHERE 班级代码=&班级代码;DBMS_OUTPUT.PUT_LINE(班级名称：| v_班级名称);DBMS_OUTPUT.PUT_LINE(班级人数：| v_班级人数);DBMS_OUTPUT.PUT_LINE(班主任：| v_班主任);END; %ROWTYPE使用%ROWTYPE属性能够定义记录变量，使得被定义的记录成员个数、名称、数据类型与已定义的表或视图中列的个数、名称和数据类型完全相同。这种定义方式，可以定义一个表，当被参照表中列及数据类型改变时，新定义表中的列及数据类型自动改变。当一个表有较多列时，使用%ROWTYPE定义记录，比使用%TYPE要方便，并且不容易出错。定义格式为：%ROWTYPE【例7.2】声明一个变量“v_班级”，与班级表具有相同的数据类型。根据输入的班级代码，输出对应的班级名称、人数和班主任信息。代码如下：DECLAREv_班级 班级表%ROWTYPE;BEGINSELECT * into v_班级 FROM 班级表WHERE 班级代码=&班级代码;DBMS_OUTPUT.PUT_LINE(班级名称：|v_班级.班级名称);DBMS_OUTPUT.PUT_LINE(班级人数：|v_班级.班级人数);DBMS_OUTPUT.PUT_LINE(班主任：|v_班级.班主任);END;5. 数据类型PL/SQL使用Oracle数据库中提供的数据类型，这些数据类型可以分为四类，分别是标量类型，复合类型，引用类型和LOB类型，以及用于数据类型定义的运算符(%TYPE，%ROWTYPE)。标量类型没有内部组件；而复合类型包含了能够被单独操作的内部组件；引用类型类似于3G语言中的指针，能够引用一个值；LOB类型的值就是一个LOB定位器，能够指示出大对象(如图像)的存储位置。下面介绍几种PL/SQL中常用的预定义数据类型。(1)标量类型标量类型分为四类：数字、字符、布尔和日期/时间。数字类型：可以存储整数、实数和浮点数，可以表示数值的大小，参与计算。表7.6列出了常用的数字类型的数值范围、子类型和使用描述。表7.6常用的PL/SQL数据类型数据类型取值子类型描述BINARY_INTEGER-2*31至2*31NATURALNATURALNNPOSITIVEPOSITIVENSIGNTYPE 用于存储单字节整数。所需的存储空间小于NUMBER用于限制范围的子类型(SUBTYPE)：NATURAL：用于非负数POSITIVE：只用于正数NATURALN：只用于非负数和非NULL值POSITIVEN：只用于正数，不能用于NULL值SIGNTYPE：只有值:-1、0或1.NUMBER1E-130至10E125DECDECIMALDOUBLE PRECISIONFLOAT INTEGERICINTNUMERIC存储数字值，包括整数和浮点数。可以选择精度和刻度方式，语法：NUMBER (p，s)p和s时可选的，它们必须是整数，p表示精度，指定数字的总长度，取值范围为138 ；s表示刻度，指定小数点后面的位数，取值范围为-84127PLS_INTEGER-2*31至2*31REALSMALLINT与BINARY_INTEGER基本相同，运算的速度要高于NUMBER和BINARY_INTEGER。 字符型：字符类型可以存放字符和数字混合的数据，表现词和文章，操作字符串。表7.7列出了常用的字符类型的数值范围、子类型和使用描述。表7.7常用的字符类型数据类型取值子类型描述CHAR最大长度32767字节 CHARACTER存储定长字符串，如果长度没有确定，缺省是1。但CHAR类型在数据库的字段中最大存储长度为2000个字节，所以，不能往数据库CHAR类型字段中插入超过2000字节的字符。LONG最大长度2147483647字节存储可变长度字符串RAW最大长度32767字节用于存储二进制数据和字节字符串，当在两个数据库之间进行传递时，RAW数据不在字符集之间进行转换。LONGRAW最大长度2147483647字节与LONG数据类型相似，同样他也不能在字符集之间进行转换。ROWID18个字节与数据库ROWID伪列类型相同，能够存储一个行标示符，可以将行标示符看作数据库中每一行的唯一键值。VARCHAR2最大长度32767字节STRINGVARCHAR与VARCHAR数据类型相似，存储可变长度的字符串。声明方法与VARCHAR相同 布尔型：布尔类型能存储逻辑值TRUE、FALSE和NULL(NULL代表缺失、未知或不可用的值)。只有逻辑操作符才允许应用在布尔变量上。数据库SQL类型并不支持布尔类型，只有PL/SQL才支持。所以就不能往数据库中插入或从数据库中检索出布尔类型的值。 日期/时间型：用于定义日期和时间。分为DATE和TIMESTAMP两种类型。表7-8列出了常用日期/时间数据类型。表7.8常用的日期/时间类型数据类型取值描述DATE公元前4721年1月1日到公元9999年12月31日存储固定长的日期和时间值，默认的DATE格式是由初始化参数NLS_DATE_FORMATE来设置的，如：默认格式为：DD-MON-YY。可以对日期进行加减运算TIMESTAMP与DATE类型相同，精确到时间秒包含了年、月、日、时、分、秒，还包括了上午、下午。【例7.3】声明一个TIMESTAMP类型的变量，并为它赋值。代码如下：SET SERVEROUT ONDECLAREcheckout TIMESTAMP(2);BEGINcheckout:=10-6月-07 07:48:53.275;DBMS_OUTPUT.PUT_LINE(checkout);END;程序输出结果为：10-6月-07 075上午(2)LOB型LOB(Large Object)数据类型提供了BFILE、BLOB、CLOB和NCLOB具体类型，可以最大存储4G的无结构数据(例如：文本、图形、视频剪辑和音频等)块。并且，它们允许高效地随机地分段访问数据。PL/SQL通过定位器来操作LOB的。例如，当我们查询出一个BLOB值，只有定位器被返回。如果在事务中得到定位器，LOB定位器就会包含事务的ID号，这样我们就不能在另外一个事务中更新LOB内容了。同样，我们也不能在一个会话中操作另外一个会话中的定位器。7.2 PL/SQL程序结构PL/SQL提供了大量的程序控制语句来辅助用户的开发工作。这些程序结构包括选择结构、循环结构和顺序结构等。每种结构都有多种语法格式。7.2.1 选择结构选择结构又称条件结构，通过判断条件的真假来选择执行不同的语句段。选择结构允许嵌套执行。6. IFTHENEND IF 结构语法格式如下：IF 条件表达式 THEN 语句段END IF；此结构中当条件表达式为“真”时，执行语句段，否则结束条件判断。7. IFTHENELSEEND IF 结构语法格式如下：IF 条件表达式 THEN语句段1ELSE语句段2END IF;当条件表达式为“真”时，执行语句段1，当条件表达式为“假”时，执行语句段2。【例7.4】查找“ASP.NET程序设计”课程，若是“备注”信息为“C#”，将其改为“J#”，否则将“备注”信息置空。代码如下：SET SERVEROUT ONDECLAREv_课程 课程表%ROWTYPE;BEGINSELECT * INTO v_课程 FROM 课程表 WHERE 课程名=ASP.NET程序设计;IF v_课程.备注=c# THENUPDATE 课程表SET 备注=J#WHERE 课程名=v_课程.课程名;ELSEUPDATE 课程表SET 备注= WHERE 课程名=v_课程.课程名;END IF; DBMS_OUTPUT.PUT_LINE(更新完成! );END;8. IFTHENELSIFEND IF 结构语法格式如下：IF 条件表达式1 THEN语句段1ELSIF 条件表达式2 THEN语句段2ELSIF 条件表达式3 THEN语句段3ELSE 语句段nEND IF;当条件表达式1为“真”时，执行语句段1，否则判断ELSIF后面的条件表达式2，若为“真”则执行语句段2，否则继续判断ELSIF后的条件表达式，直到所有的条件表达式都为“假”，则执行ELSE后面的语句段n。通常情况下建议尽量使用ELSIF代替ELSE子句的嵌套条件语句，以提高程序的可读性。注意：保留字是ELSIF而不是ELSEIF。【例7.5】学生成绩按照分数段分为“优秀”、“良好”、“合格”、“不及格”4种等级，根据等级的不同，可以输出对应的分数段，代码如下：SET SERVEROUT ONDECLAREv_result VARCHAR2(20):=良好;BEGINIF v_result=优秀 THENDBMS_OUTPUT.PUT_LINE(90100);ELSIF v_result=良好 THENDBMS_OUTPUT.PUT_LINE(8089);ELSIF v_result=合格 THENDBMS_OUTPUT.PUT_LINE(6079);ELSIF v_result=不及格 THENDBMS_OUTPUT.PUT_LINE(=90 and v_score=80 and v_score=60 and v_score=0 and v_score 60) THENDBMS_OUTPUT.PUT_LINE(成绩：|v_score|不及格);ELSE DBMS_OUTPUT.PUT_LINE(成绩：|v_score|成绩异常);END CASE;END;【例7.7】使用CASE语句完成例7.6程序。要求使用CASE语句第二种语法格式实现，代码如下：SET SERVEROUT ONDECLAREv_result VARCHAR2(20):= 良好;BEGINCASE v_resultWHEN 优秀 THEN DBMS_OUTPUT.PUT_LINE(90100);WHEN 良好 THEN DBMS_OUTPUT.PUT_LINE(8089);WHEN 合格 THEN DBMS_OUTPUT.PUT_LINE(6079);WHEN 不及格 THEN DBMS_OUTPUT.PUT_LINE(60);ELSE DBMS_OUTPUT.PUT_LINE(不存在该等级);END CASE;END;7.2.2 循环结构循环结构可以对一段语句反复执行，直到满足某一条件，跳出循环。10. LOOPEND LOOP 结构语法格式如下：LOOP语句段END LOOP 循环标签；其中，循环标签是可选的。LOOP循环语句本身没有跳出循环的条件测试语句，一旦进入LOOP循环，就会不停的重复执行语句段，因此需要在循环体中借助EXIT语句来结束循环。使用EXIT语句结束循环通常有两种方法。 EXIT语句格式1：IF 条件表达式 THENEXIT 循环标签； EXIT语句格式2：EXIT 循环标签 WHEN 条件表达式两种格式都是当条件表达式结果为“真”时，跳出循环。“循环标签”是可选的，若带有循环标签。当出现嵌套循环时，EXIT语句可以跳出循环标签指定的循环体，不受层次的限制；若没有指定循环标签，EXIT语句就使程序跳出当前的循环体。【例7.8】利用LOOP循环和EXIT语句计算1至100的自然数之和。代码如下：SET SERVEROUT ONDECLAREv_i NUMBER:=1;v_s NUMBER:=0;BEGINLOOPEXIT WHEN v_i100;v_s:=v_s+v_i; v_i:=v_i+1;END LOOP;DBMS_OUTPUT.PUT_LINE(1100自然数之和：|v_s);END;11. WHILELOOPEND LOOP 结构语法格式如下：WHILE条件表达式LOOP语句段END LOOP 循环标签;WHILE循环首先执行条件表达式的值，如果为“真”，则执行循环体内的语句段，然后再次循环，直到条件表达式的值为“假”或者为NULL时跳出循环，执行下一条语句。【例7.9】利用WHILE循环计算1至100的自然数之和。代码如下：SET SERVEROUT ONDECLAREv_i NUMBER:=0;v_s NUMBER:=0;BEGINWHILE v_i=100 LOOPv_s:=v_s+v_i;v_i:=v_i+1;END LOOP;DBMS_OUTPUT.PUT_LINE(1100自然数之和：|v_s);END;12. FORLOOPEND LOOP结构语法格式如下：FOR 循环变量INREVERS初值表达式.终值表达式 LOOP语句段END LOOP 循环标签;执行FOR循环时，首先将初值表达式的值(当使用REVERS时是终值表达式)赋予循环变量，开始执行第一次循环测试，若循环变量值小于等于终值表达式(或大于等于初值表达式)，则执行循环体内的语句段，循环变量的值自动加(减)1，开始下一次循环测试，直到循环条件大于终值表达式(或小于初值表达式)，跳出循环。【例7.10】利用FOR循环语句计算1至100的自然数之和。代码如下：SET SERVEROUT ONDECLAREv_s NUMBER:=0;BEGINFOR v_i IN 1.100 LOOPv_s:=v_s+v_i;END LOOP;DBMS_OUTPUT.PUT_LINE(1100自然数之和：|v_s);END;7.2.3 顺序结构顺序结构是一种最简单的控制结构，程序按语句顺序依次执行。有时为了打破执行顺序，将当前程序跳转到另一个地方继续运行，需要借助GOTO语句。GOTO语句语法格式为：GOTO 标签【例7.11】利用GOTO语句计算1至100的自然数之和。代码如下：SET SERVEROUT ONDECLAREv_i NUMBER:=0;v_s NUMBER:=0;BEGINv_i:=v_i+1;IF v_i=100 THENv_s:=v_s+v_i;GOTO label_1;END IF;DBMS_OUTPUT.PUT_LINE(1100自然数之和：|v_s);END;GOTO语句是一种无条件跳转语句，能够将语句无条件地跳转到一个用标签指定的语句，其目的地必须与GOTO语句在同一程序块中或更高层次(比如父块中)中。但过多的使用GOTO语句会导致程序复杂，破坏程序的结构。因此，GOTO语句一般只用在从一个PL/SQL块中跳转到异常处理语句的前面。7.2.4 NULL结构NULL语句是一个空操作的执行语句，通过执行NULL语句，可以直接将控制传递到下一条语句，以提高PL/SQL块的可读性。【例7.12】根据输入的学生学号和课程号，从“成绩表”中查找学生的课程成绩，如果成绩低于60分，将“是否补考”字段的值修改为“是”，否则不执行任何操作。代码如下：SET SERVEROUT ONDECLAREv_学号 成绩表.学号%TYPE;v_课程号 成绩表.课程号%TYPE;v_成绩 成绩表.成绩%TYPE;BEGINv_学号:= &学号;v_课程号:= &课程号;SELECT 成绩 INTO v_成绩 FROM 成绩表WHERE 学号= v_学号 AND 课程号=v_课程号;IF v_成绩3 THENRAISE e_lesson_error;ELSEDBMS_OUTPUT.PUT_LINE(v_学号|号学生| v_学年学期|学期选课| v_count|门，选课正常。);END IF;EXCEPTIONWHEN e_lesson_error THENDBMS_OUTPUT.PUT_LINE(v_学号|号学生| v_学年学期|学期选课|v_count|门，超过3门。);END;7.4 函数函数用于计算和返回特定的数据，可以将经常需要进行的计算写成函数。函数从类型上可以分为系统内置函数和用户自定义函数。7.4.1 系统内置函数系统内置函数是Oracle系统提供的一些常用函数，大概分为三类：数学运算函数、字符串函数和统计(分组)函数。数学运算函数常用于数学运算，例如用于返回数字表达式绝对值的ABS函数，返回数字表达式平方根的SQRT函数等。字符串函数用于对字符串进行处理，例如用于返回字符串或表达式长度的LENGTH函数，将给定字符串中的字符变为小写的LOWER函数等。统计(分组)函数用来处理数值型数据，包括统计记录行数的COUNT函数，统计数值/字符最大值的MAX函数等。常用到的系统内置函数见附录。7.4.2 用户自定义函数在Oracle中，用户不仅可以使用标准的内置函数，也可以根据自己特殊的需求创建函数。用户可以将一些常用的计算写成函数，存储在数据库中，方便进行调用。13. 创建函数创建函数的语法如下：CREATE OR REPLACE FUNCTION function_name/*函数名称*/(arg1 INOUTIN OUTarg_type1, /*参数定义部分*/arg2 INOUTIN OUT arg_type2,argn INOUTIN OUT arg_typen)RETURN return_type /*定义返回值类型*/ISAS BEGIN function_body /*函数体部分*/RETURN scalar_expression /*返回语句*/END function_name;其中： OR REPLACE是可选的。如果省略，则创建时不允许数据库中有同名的函数；如果使用，则会先删除同名函数，然后创建新的函数。 function_name是用户定义的函数名。函数名必须符合标识符的定义规则，对其所有者来说，该名称在数据库中是唯一的。 arg是用户定义的参数，是可选的。 arg_type是参数的数据类型。 IN，OUT，IN OUT是参数类型。如果省略，则为IN模式。IN模式用于接收调用环境的输入数据；OUT模式用于将输出数据传递到调用环境；IN OUT模式不仅要接收输入数据，而且要输出数据到调用环境。 function_body是函数的主体，可以包含异常处理部分。 RETURN scalar_expression用来将函数的值返回给调用环境。在函数体的任何地方，用户都可以定义RETURN scalar_expression子句，scalar_expression的值应与函数的数据类型匹配。【例7.16】创建函数，按照输入的课程号计算该课程的全体学生的平均成绩，并输出此门课程的学生人数。若是此门课程没有学生，进行异常处理。代码如下：CREATE OR REPLACE FUNCTION average_score(v_课程号 IN 成绩表.课程号%TYPE,v_人数 OUT NUMBER)RETURN NUMBERISv_average NUMBER;e_count_error EXCEPTION;BEGINSELECT COUNT(*),AVG(成绩) INTO v_人数,v_average FROM 成绩表WHERE 课程号= v_课程号;IF v_人数=0 THENRAISE e_count_error;END IF;RETURN v_average;EXCEPTION WHEN e_count_error THENDBMS_OUTPUT.PUT_LINE(v_课程号|号课程没有学生！);RETURN v_average;END average_score;14. 函数调用无论是在命令行还是在程序语句中，函数都可以通过函数名直接调用，唯一不同的是，在SQL*PLUS中是运行EXCUTE命令来调用的，而在PL./SQL块中是直接调用的。SQL*PLUS中的执行格式为：EXECUTE variable_name:=function_name(arg);在PL./SQL块中格式为：variable_name:=function_name(arg);另外，还可以在SQL语句中直接调用函数，格式为：SELECT function_name(arg) FROM DUAL;【例7.17】调用例7.16创建的函数“average_score”。代码如下：SET SERVEROUT ONDECLAREv_人数 NUMBER;v_average NUMBER;v_课程号 成绩表.课程号%TYPE;BEGINv_课程号:=&课程号;v_average:=average_score(v_课程号, v_人数);IF v_average IS NULL THENNULL;ELSEDBMS_OUTPUT.PUT_LINE(v_课程号|号课程选课人数为：| v_人数); DBMS_OUTPUT.PUT_LINE(平均成绩为：| v_average);END IF;END;15. 函数释放当不在使用某个函数时，可使用DROP FUNCTION命令将其从内存中删除。基本语法如下：DROP FUNCTION schema.function_name;【例7.18】删除“average_score”函数，代码如下：DROP FUNCTION avera