oracle10g经典实战第5章_PLSQL介绍

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,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),多层集合。,对,LOB,数据类型更好地集成。,对批操作的增强。,(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,类：,所有的大写和小写英文字母；,数字,0,9,；,符号,() +, *,/ = ! ,；：,., % ,#, , ? ,。,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, ZXF,FROM XS,WHERE 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),变量必须以字母,(A,Z),开头,(2),其后跟可选的一个或多个字母、数字,(0,9),或特殊字符,$,、,#,或,_,(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_name,AUTHID CURRENT_USER | DEFINER AS OBJECT,( attribute1,datatype,attribute2,datatype,attributen,datatype,method1,method2,methodn,5.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,的学生人数。,DECLARE,v_num,number(3);,BEGIN,SELECT COUNT(*) INTO,v_num,FROM,xs,WHERE,zxf,40;,IF,v_num,0 THEN,dbms_output.put_line,(,总学分,40,的人数为：,|,TO_CHAR(v_num,);,END IF;,END;,IF-THEN,语句可以嵌套使用,5.4 PL/SQL,基本程序结构和语句,【,例,5.6】,判断计算机系总学分,40,的人数是否超过,80,人。,DECLARE,v_num,number(3);,BEGIN,SELECT COUNT(*) INTO,v_num,FROM,xs,WHERE,zxf,40 AND,zym,=,计算机,;,IF,v_num,0 THEN,IF,v_num,80 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);,BEGIN,SELECT,AVG(cj,) INTO,v_avg,FROM,xs,xs_kc,kc,WHERE,xs.xh,=,xs_kc.xh,AND,xs_kc.kch,=,kc.kch,AND,kc.kcm,=,数据库原理,;,IF,v_avg,75 THEN,dbms_output.put_line,(,平均成绩大于,75);,ELSE,dbms_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,ELSE,Run_expression3,END IF,IF-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,循环,除退出条件检测有所区别外，此结构与前一个循环结构类似。,语法格式：,LOOP,Run_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,LOOP,n:=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,是否符合退出条件*,/,LOOP,Run_expression,/*,执行循环体*,/,END LOOP;,此结构的执行如流程图,5.9,所示。,流程图,5.9 WHILE-LOOP-END,执行流程,5.4.2,循环结构,【,例,5.11】,用,WHILE-LOOP-END,循环结构求,10,的阶层。,DECLARE,n NUMBER:=1;,count1 NUMBER:=2;,BEGIN,WHILE count1,语句,使用,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 THEN,GOTO,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,异常。使用系统预定义的异常处理，使用该处理后，,程序运行时系统就不会提示出现错误。,DECLARE,v_number,number(2):=10;,v_zero,number(2):=0;,v_result,number(5);,BEGIN,v_result,:=,v_number/v_zero,; /*,用,v_number,除以,v_zero,，即,10/0,，从而产生除零错误*,/,EXCEPTION,WHEN ZERO_DIVIDE THEN,DBMS_OUTPUT.PUT_LINE(DIVIDE ZERO);,END;,系统的运行结果为：,DIVIDE ZERO,PL/SQL,过程已成功完成。,5.4.4,异常,1.,预定义异常,除了除零错误外，,PL/SQL,还有很多系统预定义异常，表,5.9,列出了常见的,PL/SQL,异常，通过检测这些异常，用户可以查找到,PL/SQL,程序产生的错误。,表,5.9 PL/SQL,中最常见的异常,5.4.4,异常,【,例,5.16】,转换错误处理。,DECLARE,v_number,number(5);,v_result,char(5):=2w;,BEGIN,v_number,:=,to_number(v_result,);,EXCEPTION,WHEN VALUE_ERROR THEN,DBMS_OUTPUT.PUT_LINE(CONVERT TYPE ERROR);,END;,5.4.4,异常,【,例,5.17】,联合的错误处理。,DECLARE,v_result,xs.xm%TYPE,;,BEGIN,SELECT,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,语,句。,语法格式：,EXCEPTION,WHEN,exception_name,THEN,sequence_of_statements1;,WHEN THEN,sequence_of_statements2;,WHEN OTHERS THEN,sequence_of_statements3;,END;,5.4.4,异常,【,例,5.18】,自定义异常处理。,DECLARE,e_overnumber,EXCEPTION; /*,定义异常处理变量*,/,v_xs_number,number(9);,v_max_xs_number,number(9):=500;,BEGIN,SELECT COUNT(*) INTO,v_xs_number,FROM,xs,;,IF,v_max_xs_number,v_xs_number,THEN,RAISE,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,异常。,DECLARE,v_result,number;,BEGIN,SELECT,xm,INTO,v_result,FROM,xs,WHERE,xh,=010010;,DBMS_OUTPUT.PUT_LINE(The,student name,isv_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 TOO_MANY_ROWS error);,WHEN OHTERS THEN,DBMS_OUTPUT.PUT_LINE(Unkown,error);,END;,5.4.5,空操作和空值,有时特别是在使用,IF,逻辑时，用户结束测试一个条件。当测试条件为,TRUE,，什,么工作都不做；而当测试值为,FALSE,时，则执行某些操作。这在,PL/SQL,中以下,述方法处理：,IF n0 THEN,NULL;,ELSE,DBMS_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/AS,BEGIN,function_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,AS,return_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 NUMBER,AS,avger,NUMBER;,BEGIN,SELECT AVG(CJ) INTO,avger,FROM XS_KC,WHERE KCH=,cnum,GROUP BY KCH;,RETURN(avger,);,END average;,5.6.1SQL,语句方式创建,【,例,5.27】,创建一个统计数据库中不同性别人数的函数。,CREATE OR REPLACE FUNCTION,count_num,/*,定义函数,count_num,*/,(,in_sex,IN char ) /*,输入参数*,/,RETURN number,AS,out_num,number; /*,指定返回值*,/,BEGIN,IF,in_sex,=,男,THEN,SELECT COUNT(XB) INTO,out_num,FROM XS,WHERE XB=,男,;,ELSE,SELECT,COUNT(XB) INTO,out_num,FROM XS,WHERE 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_name,Schema,是函数的拥有者，,function_name,是函数名。,例如，把函数,count_num,删除，只需执行如下语句：,DROP FUNCTION,count_num,;,5.6.2OEM,方式创建,(1),在如图,5.11,所示的,Oracle,企业管理中，选择单击“函数”，进入“函数搜索”界,面，如图,5.12,所示。,图,5.11,利用,OEM,创建函数,图,5.12,函数搜索界面,5.6.2OEM,方式创建,(2),在图,5.12,所示界面，单击“创建”按钮，进入“函数创建”界面，如图,5.13,所示。,在“名称”文本框中指定函数名称,count_num,；“方案”选择,ADMIN,；在“源”代码区输,入编辑函数代码，如图,5.15,所示。代码输入编辑完成后单击“确定”按钮，完成函,数创建后返回,5.11,所示界面。,图,5.13,创建函数界面,5.7,游标,PL/SQL,用游标（,cursor,）来管理,SQL,的,SELECT,语句。游标是为处理这些语句而,分配的一大块内存。一个对表进行操作的,PL/SQL,语句通常都可产生或处理一组,记录，但是许多应用程序，尤其是,PL/SQL,嵌入到的主语言（如,C,、,Delphi,、,PowerBuilder,或其它开发工具）通常不能把整个结果集作为一个单元来处理，这,些应用程序就需要一种机制来保证每次处理结果集中的一行或几行，游标就提供,了这种机制。,PL/SQL,通过游标提供了对一个结果集进行逐行处理的能力，游标可看作一种特,殊的指针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定,位置的数据进行处理。使用游标可以在查询数据的同时对数据进行处理。,5.7.1,显式游标,显式游标首先要声明,(Declare),，在使用前要打开,(Open),，使用完毕后要关闭,(Close),。,(1),声明游标,显式游标是作为声明段的一部分进行定义的，定义的方法如下：,DECLARE CURSOR,cursor_name,IS,select_statement,其中，,cursor_name,是游标名，它是与某个查询结果集联系的符号名，要遵循,Oracle,变量定义的规则。,Select_statement,是,SELECT,语句，由该查询产生与所声明的游标,相关联的结果集。,例如，下面是一个游标定义实例：,DECLARE CURSOR XS_CUR1,IS,SELECT XH,XM,CJ,FROM XS,WHERE ZYM=,计算机,;,5.7.1,显式游标,(2),打开游标,声明游标后，要使用游标从中提取数据，就必须先打开游标。在,PL/SQL,中，使用,OPEN,语句打开游标，其格式为：,OPEN,cursor_name,其中，,cursor_name,是要打开游标名。打开