程序块经典教程学习

1、D第5章 PL/SQL程序块来自中国范文网学习要点键盘输入和屏幕输出条件控制循环控制PL/SQL编程举例PL/SQL的异常1 本章主要内容PL/SQL块的组成PL/SQL 块的类型PL/SQL 的数据类型常量、变量和表达式PL/SQL 块的执行过程与运行环境单行函数键盘输入和屏幕输出条件控制循环控制PL/SQL编程举例PL/SQL的异常B25.1 PL/SQL块的组成PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL 程序包含了一个或多个逻辑块，每个块都可以划分为三个部分：定义部分、执行部分、异常处理部分。 3B PL/SQL 块的格式如下：DECLAREBE

2、GINEXCEPTIONENDPL/SQL块的书写必须遵循如下规定：（1）PL/SQL块的每一条语句都必须以分号“；”结束；（2）SQL语句可以是多行的，但分号表示该语句的结束。（3）一行中可以有多条SQL语句，它们之间以分号分隔。（4）每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。（5）语句的注释由“-”标示。 45.2 PL/SQL块的类型PL/SQL程序块的种类分为：命名块；匿名块；子程序；触发器。其中子程序包括：函数，过程和包。子程序和触发器将在后来的章节讲到。匿名块可以用在服务器端也可以用在客户端。匿名块是只使用一次的PL/SQL程序块，匿名块没有名称，也不被存

3、储在数据库中。【例】匿名块例子。SET SERVEROUTPUTDECLARE A1 number(10);BEGIN A1 := 23; dbms_output.putline (A1);END;5【例】建立一个命名块AAA。CREATE OR REPLACE PROCEDURE AAA(nn out EMP.ename%TYPE) AS BEGIN SELECT ename INTO nn FROM EMP WHERE empno=7369; dbms_output.putline(nn); END;65.3 PL/SQL的数据类型1基本数据类型 类型标识符说明例子Number(n)n位数

4、字Sal Number(5);Int（或integer）整型数Sal int;Char(n)定长字符型，n位字符。最大255个字符Name Char(8);Varchar2(n)变长字符型，n位字符。最大2000个字符Name Varchar2(8);Long变长字符型，最长2GBComm. Long;Date日期型Birthday Date;72使用%type定义变量%type类型定义的变量是“不知道”的类型变量。这个变量的类型与表中字段的类型是一致。为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 提供了%type定义方法。语法格式如下： %TYPE;其中，如果是

5、如下形式： %TYPE;表明和的类型是相同的。如果是如下形式： %TYPE;表明和的类型是相同的。例如：V_NAME EMP.ENAME%TYPE;但是当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。C83使用%rowtype定义变量前面讲过，使用%type可以使变量获得字段的数据类型，但是使用%rowtype可以使变量获得整个记录的数据类型。二者在定义上也不同，%rowtype的语法格式是： %rowtype;%rowtype的属性如下：（1）与%TYPE作用类似，用于定义不确定的类型。（2）变量类型将定义为由数据库的表的字段集合构成的RECORD类型。（3）%rowt

6、ype的前缀是数据库的表名，或者另一个已经定义好的RECORD变量。（4）RECORD中的域，与表的字段的名称和数据类型完全相同。95.4 常量、变量和表达式5.4.1 常量和变量变量和常量都是标识符，定义格式基本相同。定义变量的语法格式如下：变量名称 数据类型;定义常量的语法格式如下：常量名 CONSTANT 数据标识符 NOT NULL:=值;D105.4.2 表达式变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。（1）数值表达式。PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减

7、法）、*（乘法）、/（除法）和*（乘方）等。SET serveroutput ONDECLARE result number;BEGIN result:= 100+23*12 - 5*2; dbms_output.put_line(运算结果是:|to_char(result);END;C11（2）字符表达式。字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“|”。（3）关系表达式。关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符如表5-2所示。A12D序号关系运算符说明1大于3=等于（不是赋值运算符：=）4like类似于5in在

8、之中6=大于等于8!=不等于9between在之间表5-2 关系运算符135.5 PL/SQL块的执行过程与运行环境PL/SQL块的编辑环境有如下几种：（1）Oracle工具。Oracle 9i 中的SQL*plus Worksheet，Oracle 10g中的iSQL*Plus。或者http:/:本机计算机名称:5560/ iSQL*Plus输入用户名称和密码即可进入。（2）嵌入在应用程序中。（3）使用记事本写脚本，以SQL为文件后缀名进行保存。输入如下的程序：DECLARE v_comm_percent CONSTANT number:=10; BEGIN update EMP SET c

9、omm=sal*v_comm_percent WHERE deptno=10;END;C14执行PL/SQL块。PL/SQL块的执行方法有两种：（1）SQL命令行。SQL*Plus中匿名的PL/SQL块的执行是在PL/SQL块后输入符号“/”来执行的。PL/SQL块如下：执行方法：SQL/PL/SQL procedure successfully completed.或者SQLRUN如果是脚本可以：SQL脚本文件名（2）在工具SQL Plus Worksheet（Oracle 9i）中直接执行，或在工具iSQL*Plus（Oracle 10g）中执行。D155.6 单行函数Oracle的函数主

10、要分为两大类：单行函数和聚合函数。聚合函数包括如下几种函数：AVG()、MIN()、MAX()、SUM()、COUNT()。单行函数对查询的表或视图的每一行返回一个结果行，分为：数值函数、字符函数、日期函数、转换函数等。165.6.1 NVL 函数大家知道，NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为NULL值，这个规则也适合很多函数，但是在数据库查询中如果查询的结果为NULL，就无法参与运算了。如：300+NULL，这是非法的。怎么办呢？函数NVL专门处理空值NULL。格式为：NVL(x1,x2)其中x1和x2都是表达式，当x1为null时返回X2

11、，否则返回x1。175.6.2 字符串函数单行字符串函数用于操作字符串数据，它们大多数有一个或多个参数，其中绝大多数返回字符串。1ASCII(c)返回字符c对应的ASCII（十进制数）。【例】求字符“A”，“a”,“0”和空格的ASCII码。SQLSELECT ASCII(A) A,ASCII(a) a,ASCII(0) zero,ASCII( ) space FROM dual; A A ZERO SPACE - - - - 65 97 48 32182CHR(i)给出整数i，返回对应的字符。【例】求ASCII码对应的字符。SQLSELECT CHR(54740) zhao,chr(65)

12、chr65 FROM dual; ZHAO CHR65 - 赵 A3CONCAT (c1,c2)连接两个字符串c1和c2。【例】字符串0311-,和85900982连接结果。SQLSELECT CONCAT(0311-, 85900982)| 转110 刘易电话 FROM dual; 刘易电话-2转110194INITCAP(c)返回字符串c并将字符串c中每个单词的第一个字母大写，其他字母小写，返回的第一个字母变为大写5NSTR(C1,C2,i,j)在一个字符串中搜索指定的字符，返回发现指定的字符的位置。C1是被搜索的字符串，C2是搜索的字符串，i是搜索的开始位置（默认值为1），j是出现的次数

13、（默认值为1）。6LENGTH(c)返回字符串c的长度。7LOWER(c)返回字符串c的所有字符的小写 。8UPPER(c)返回字符串c的所有字符的大写。205.6.3 数字函数1ABS(n)返回n的绝对值。2EXP(n)返回e的n次幂，e=2.71828183。3MOD(n1,n2)返回n1除以n2的余数。例如MOD(56,6)。4ROUND(n1,n2)返回舍入小数点右边n2位的n1的值，n2的缺省值为0，返回四舍五入的整数值，如果n2为负数就舍入到小数点左边相应的位上。注意，n2必须是整数。215SIGN(n)如果n为负数，返回-1；如果n为正数，返回1；如果n为0，则返回0。6SQRT

14、(n)返回n的平方根，n为弧度。7TRUNC(n1,n2)返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数；如果n2为负值，就截尾在小数点左边相应的位上。例如：TRUNC (12345,-2) = 12300TRUNC (12345.54,-1) =12340TRUNC (12346.5483,2) =12346.54221单行日期函数单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。2ADD_MONTHS(,)返回日期d加上i个月后的结果。i可以是任意整数。如果i含有小数，那么数据库系统将把小数

15、部分截去。3LAST_DAY(d)函数返回包含日期d的月份的最后一天。4MONTHS_BETWEEN(d1,d2)返回d1和d2之间月的数目，如果d1和d2的日期都相同，或者都是该月的最后一天，那么将返回一个整数，否则返回的结果将包含一个分数。5NEXT_DAY(d,dow)返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。5.6.4 日期函数235.6.5 转换函数1to_char(number,format-model)按照format-model的格式把数值number转换为相应的字符串。2TO_DATE(c ,

16、fomat-model)c表示字符串，format-model表示一种特殊格式的字符串。返回format-model格式的字符串c对应的日期。函数将字符串c转换成date数据类型。3TO_NUMBER(c ,fomat-model)c表示字符串，format-model表示一个特殊格式的字符串，函数返回format-model格式的数字。函数将字符串c转换为相应的数字。245.7 键盘输入和屏幕输出键盘输入是通过在变量名前面加一个“&”符号实现的，如：&V_INPUT。如果此变量是数值型（如：number，integer等），在提示输入时直接输入数据；如果此变量是字符型（如：varchar2(

17、)等），有两个办法输入：如果书写时是：&V_INPUT，那么输入时要加单引号“”把字符串引起来；如果书写时是：&V_INPUT，那么输入时不要加单引号，直接输入字符串。屏幕输出是通过dbms_output.put_line函数输出的。dbms_output.put_line()语句是Oracle 内置的包，它可以使用SQL*Plus 从PL/SQL中输出信息。如：dbms_output.put_line(VARP)。255.8 条件控制5.8.1 IFTHEN 语句语法：IF condition THEN END IF265.8.2 IFTHENELSE语句语法：IF condition TH

18、EN ELSE END IF如果条件condition为TRUE，则执行THEN到ELSE之间的语句，否则执行ELSE到END IF之间的语句。275.8.3 IFTHENELSIF语法：IF condition1 THEN ELSIF condition2 THEN ELSIF condition3 THEN ELSE END IF; 如果条件condition1为TRUE，则执行语句组1，然后执行语句组5；否则判断语句组2是否为TRUE，若为TRUE则执行语句组2，然后执行语句组5；对于语句组3也是相同的，如果语句组1、语句组2、语句组3都不成立，那么将执行语句组4，然后执行语句组5。28

19、5.8.4 CASE 语句CASE语句有两种格式：第1种形式：CASE语句的基本格式如下：CASE变量WHEN 值1 THEN 语句1;WHEN 值2 THEN 语句2;WHEN 值3 THEN 语句3;WHEN 值4 THEN 语句4;ELSE 语句5;END CASE;CASE语句的功能：首先设定变量的值作为条件，然后顺序检查表达式，一旦从中找到与条件匹配的表达式的值，就停止CASE语句的处理。例如，变量的值如果与值2的值相同，那么执行语句2，然后停止CASE。295.9 循环控制1LOOPEXIT WHENEND LOOP循环控制循环控制语法格式为：LOOPEXIT WHEN ;END

20、LOOP;302LOOPIFTHEN EXITEND LOOP循环控制前面的EXIT WHEN实际上就相当于如下的语句：IF 条件 THEN EXIT; END IF;313WHILELOOPEND LOOP循环控制WHILE循环控制的语法如下：WHILE 条件 LOOP 执行语句段; END LOOP;324FORLOOP循环控制FORLOOP循环的格式是：FOR counter IN start_rangeend_range LOOP;END LOOP;其中：（1）counter是循环变量。（2）start_range是循环变量的起始值，end_range是循环变量的终止值。（3）如果要退

21、出FOR循环可以使用EXIT语句。335.10 PL/SQL的异常在设计PL/SQL程序时，经常会发生这样或那样的错误，异常处理就是针对错误进行处理的程序段，Oracle的异常处理分为： PL/SQL的异常；预定义异常；自定义异常。345.10.1 PL/SQL异常异常处理块中包含了与异常相关的错误发生以及当错误发生时要进行执行和处理的代码。异常部分的语法一般如下：BEGINEXCEPTIONWHEN excep_name1 THEN WHEN excep_name2 THEN WHEN OTHERS THEN END;其中和都是异常名称。355.10.2 预定义异常1 系统预定义异常 36异

22、常名称ORACLE错误说明CURSOR_ALREADY_OPENORA-6511试图打开一个已打开的光标DUP_VAL_ON_INDEXORA-0001试图破坏一个唯一性限制INVALID_CURSORORA-1001试图使用一个无效的光标INVALID_NUMBERORA-1722试图对非数字值进行数字操作LOGIN_DENIEDORA-1017无效的用户名或者口令NO_DATA_FOUNDORA-1403查询未找到数据NOT_LOGGED_ONORA-1012还未连接就试图进行数据库操作PROGRAM_ERRORORA-6501内部错误ROWTYPE_MISMATCHORA-6504主变量和光标的类