Oracle数据库开发实用教程第5章

第5章PL/SQL程序块学习要点单行函数键盘输入和屏幕输出条件控制循环控制PL/SQL编程举例PL/SQL的异常本章主要内容PL/SQL块的组成PL/SQL块的类型PL/SQL的数据类型常量、变量和表达式PL/SQL块的执行过程与运行环境单行函数键盘输入和屏幕输出条件控制循环控制PL/SQL编程举例PL/SQL的异常5.1PL/SQL块的组成PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每个块都可以划分为三个部分：定义部分、执行部分、异常处理部分。PL/SQL块的格式如下：[DECLARE]声明语句BEGIN执行语句[EXCEPTION]异常处理语句ENDPL/SQL块的书写必须遵循如下规定：（1）PL/SQL块的每一条语句都必须以分号“；”结束；（2）SQL语句可以是多行的，但分号表示该语句的结束。（3）一行中可以有多条SQL语句，它们之间以分号分隔。（4）每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。（5）语句的注释由“--”标示。5.2PL/SQL块的类型PL/SQL程序块的种类分为：①命名块；②匿名块；③子程序；④触发器。其中子程序包括：函数，过程和包。子程序和触发器将在后来的章节讲到。匿名块可以用在服务器端也可以用在客户端。匿名块是只使用一次的PL/SQL程序块，匿名块没有名称，也不被存储在数据库中。【例】匿名块例子。SETSERVEROUTPUTDECLAREA1number(10);BEGINA1:=23;dbms_output.putline(A1);END;【例】建立一个命名块AAA。CREATEORREPLACEPROCEDUREAAA(nnoutEMP.ename%TYPE)ASBEGINSELECTenameINTOnnFROMEMPWHEREempno=7369;dbms_output.putline(nn);END;5.3PL/SQL的数据类型1．基本数据类型类型标识符说明例子Number(n)n位数字SalNumber(5);Int（或integer）整型数Salint;Char(n)定长字符型，n位字符。最大255个字符NameChar(8);Varchar2(n)变长字符型，n位字符。最大2000个字符NameVarchar2(8);Long变长字符型，最长2GBComm.Long;Date日期型BirthdayDate;2．使用%type定义变量%type类型定义的变量是“不知道”的类型变量。这个变量的类型与表中字段的类型是一致。为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle提供了%type定义方法。语法格式如下：变量名称变量名|字段名%TYPE;其中，如果是如下形式：变量名称变量名%TYPE;表明变量名称和变量名的类型是相同的。如果是如下形式：变量名称字段名%TYPE;表明变量名称和字段名的类型是相同的。例如：V_NAMEEMP.ENAME%TYPE;但是当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。3．使用%rowtype定义变量前面讲过，使用%type可以使变量获得字段的数据类型，但是使用%rowtype可以使变量获得整个记录的数据类型。二者在定义上也不同，%rowtype的语法格式是：变量名称数据表的名称%rowtype;%rowtype的属性如下：（1）与%TYPE作用类似，用于定义不确定的类型。（2）变量类型将定义为由数据库的表的字段集合构成的RECORD类型。（3）%rowtype的前缀是数据库的表名，或者另一个已经定义好的RECORD变量。（4）RECORD中的域，与表的字段的名称和数据类型完全相同。5.4常量、变量和表达式5.4.1常量和变量变量和常量都是标识符，定义格式基本相同。定义变量的语法格式如下：变量名称数据类型;定义常量的语法格式如下：常量名CONSTANT数据标识符[NOTNULL]:=值;5.4.2表达式变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。（1）数值表达式。PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和**（乘方）等。SETserveroutputONDECLAREresultnumber;BEGINresult:=100+23*12-5**2;dbms_output.put_line('运算结果是:'||to_char(result));END;（2）字符表达式。字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“||”。（3）关系表达式。关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符如表5-2所示。序号关系运算符说明1小于2大于3=等于（不是赋值运算符：=）4like类似于5in在……之中6=小于等于7=大于等于8!=不等于9between在……之间表5-2关系运算符5.5PL/SQL块的执行过程与运行环境PL/SQL块的编辑环境有如下几种：（1）Oracle工具。Oracle9i中的SQL*plusWorksheet，Oracle10g中的iSQL*Plus。或者http://:本机计算机名称:5560/iSQL*Plus输入用户名称和密码即可进入。（2）嵌入在应用程序中。（3）使用记事本写脚本，以SQL为文件后缀名进行保存。输入如下的程序：DECLAREv_comm_percentCONSTANTnumber:=10;BEGINupdateEMPSETcomm=sal*v_comm_percentWHEREdeptno=10;END;执行PL/SQL块。PL/SQL块的执行方法有两种：（1）SQL命令行。SQL*Plus中匿名的PL/SQL块的执行是在PL/SQL块后输入符号“/”来执行的。PL/SQL块如下：执行方法：SQL/PL/SQLproceduresuccessfullycompleted.或者SQLRUN如果是脚本可以：SQL@脚本文件名（2）在工具SQLPlusWorksheet（Oracle9i）中直接执行，或在工具iSQL*Plus（Oracle10g）中执行。5.6单行函数Oracle的函数主要分为两大类：单行函数和聚合函数。聚合函数包括如下几种函数：AVG()、MIN()、MAX()、SUM()、COUNT()。单行函数对查询的表或视图的每一行返回一个结果行，分为：数值函数、字符函数、日期函数、转换函数等。5.6.1NVL函数大家知道，NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为NULL值，这个规则也适合很多函数，但是在数据库查询中如果查询的结果为NULL，就无法参与运算了。如：300+NULL，这是非法的。怎么办呢？函数NVL专门处理空值NULL。格式为：NVL(x1,x2)其中x1和x2都是表达式，当x1为null时返回X2，否则返回x1。5.6.2字符串函数单行字符串函数用于操作字符串数据，它们大多数有一个或多个参数，其中绝大多数返回字符串。1．ASCII(c)返回字符c对应的ASCII（十进制数）。【例】求字符“A”，“a”,“0”和空格的ASCII码。SQLSELECTASCII('A')A,ASCII('a')a,ASCII('0')zero,ASCII('')spaceFROMdual;AAZEROSPACE-------------------------------------659748322．CHR(i)给出整数i，返回对应的字符。【例】求ASCII码对应的字符。SQLSELECTCHR(54740)zhao,chr(65)chr65FROMdual;ZHAOCHR65----------------------赵A3．CONCAT(c1,c2)连接两个字符串c1和c2。【例】字符串'0311-',和'85900982'连接结果。SQLSELECTCONCAT('0311-','85900982')||'转110'刘易电话FROMdual;刘易电话----------------------------0311-85900982转1104．INITCAP(c)返回字符串c并将字符串c中每个单词的第一个字母大写，其他字母小写，返回的第一个字母变为大写5．NSTR(C1,C2,i,j)在一个字符串中搜索指定的字符，返回发现指定的字符的位置。C1是被搜索的字符串，C2是搜索的字符串，i是搜索的开始位置（默认值为1），j是出现的次数（默认值为1）。6．LENGTH(c)返回字符串c的长度。7．LOWER(c)返回字符串c的所有字符的小写。8．UPPER(c)返回字符串c的所有字符的大写。5.6.3数字函数1．ABS(n)返回n的绝对值。2．EXP(n)返回e的n次幂，e=2.71828183。3．MOD(n1,n2)返回n1除以n2的余数。例如MOD(56,6)。4．ROUND(n1,n2)返回舍入小数点右边n2位的n1的值，n2的缺省值为0，返回四舍五入的整数值，如果n2为负数就舍入到小数点左边相应的位上。注意，n2必须是整数。5．SIGN(n)如果n为负数，返回-1；如果n为正数，返回1；如果n为0，则返回0。6．SQRT(n)返回n的平方根，n为弧度。7．TRUNC(n1,n2)返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数；如果n2为负值，就截尾在小数点左边相应的位上。例如：TRUNC(12345,-2)=12300TRUNC(12345.54,-1)=12340TRUNC(12346.5483,2)=12346.541．单行日期函数单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。2．ADD_MONTHS(,i)返回日期d加上i个月后的结果。i可以是任意整数。如果i含有小数，那么数据库系统将把小数部分截去。3．LAST_DAY(d)函数返回包含日期d的月份的最后一天。4．MONTHS_BETWEEN(d1,d2)返回d1和d2之间月的数目，如果d1和d2的日期都相同，或者都是该月的最后一天，那么将返回一个整数，否则返回的结果将包含一个分数。5．NEXT_DAY(d,dow)返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。5.6.4日期函数5.6.5转换函数1．to_char(number,'format-model')按照‘format-model’的格式把数值number转换为相应的字符串。2．TO_DATE(c[,'fomat-model'])c表示字符串，format-model表示一种特殊格式的字符串。返回format-model格式的字符串c对应的日期。函数将字符串c转换成date数据类型。3．TO_NUMBER(c[,'fomat-model'])c表示字符串，format-model表示一个特殊格式的字符串，函数返回format-model格式的数字。函数将字符串c转换为相应的数字。5.7键盘输入和屏幕输出键盘输入是通过在变量名