在应用中使用SQL课件.ppt

《在应用中使用SQL课件.ppt》由会员分享，可在线阅读，更多相关《在应用中使用SQL课件.ppt（242页珍藏版）》请在三一办公上搜索。

1、第7章 在应用中使用SQL,7.1 嵌入式SQL7.2 存储过程7.3 ODBC简介7.4 JDBC简介7.5 触发器7.6 小结,7.1 嵌入式SQL,在高级程序设计语言中使用SQL语句操纵数据库需要解决两个问题：采用某种语法形式使得编译程序可以区分SQL语句和宿主语言的语句；提供一种机制使得SQL语言和宿主语言之间可以交换数据和执行状态。,7.1 嵌入式SQL,7.1.1嵌入式SQL的一般形式 在嵌入式SQL中，为了能够区分SQL语句与宿主语言语句，所有SQL语句都必须加前缀EXEC SQL。例如， EXEC SQL DROP TABLE SC; EXEC SQL SELECT * FRO

2、M Student; EXEC SQL GRANT UPDATE ON Student TO User1;,7.1 嵌入式SQL,7.1.1嵌入式SQL的一般形式 为了不修改宿主语言的编译器，DBMS提供一个预编译器，预编译器识别嵌入式SQL语句，将它们换成SQL函数库中的函数调用，将最初的宿主语言和嵌入式SQL的混合体转换成纯宿主语言的代码，然后由编译器进行通常的编译和连接操作，最终生成可执行代码，完成过程控制和数据库操作，具体过程如图7.1所示。,7.1 嵌入式SQL,图7.1 嵌入式SQL的处理过程,7.1 嵌入式SQL,7.1.2 嵌入式SQL语句与宿主语言的通信 将SQL嵌入到高级语

3、言中混合编程，SQL语句负责操纵数据库，高级语言语句负责控制程序流程。这时程序中含有两种不同计算模型的语句，一种是描述性的面向集合的SQL语句，一种是过程性的高级语言语句，SQL标准主要使用宿主变量在它们之间互相交换数据，进行通信。,7.1 嵌入式SQL,宿主变量使用声明节（declare section）定义宿主变量，格式如下： EXEC SQL BEGIN DECLARE SECTION 按照宿主语言的语法定义的变量 EXEC SQL END DECLARE SECTION,7.1 嵌入式SQL,7.1.2 嵌入式SQL语句与宿主语言之间的通信例如：EXEC SQL BEGIN DECLA

4、RE SECTIONcharSno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;EXEC SQL END DECLARE SECTION,7.1 嵌入式SQL,7.1.3 查询结果为单个记录的SELECT语句 在嵌入式SQL中，查询结果为单个记录的SELECT语句使用INTO子句把查询结果传送到宿主变量，供宿主语言继续处理。该语句的一般格式为：,7.1 嵌入式SQL,EXEC SQL SELECT ALL|DISTINCT , INTO , FROM , WHERE GROUP BY HAVING ORDER BY ASC

5、|DESC;,7.1 嵌入式SQL,7.1.3 查询结果为单个记录的SELECT语句 1.INTO子句、WHERE子句的条件表达式、HAVING短语的的条件表达式中均可以使用宿主变量。 2.查询返回的记录中，可能某些列为空值。如果INTO子句中宿主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应宿主变量后面的指示变量置为负值，不再向宿主变量赋值。,7.1 嵌入式SQL,7.1.3 查询结果为单个记录的SELECT语句 3.如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，DBMS将SQLSTATE的值设置为21000。,7.1 嵌入式SQL,例 1 查询某个学

6、生的信息，这个学生的学号存放在宿主变量Sno中，并且将查询得到的学生信息存放到上一小节定义的变量中。EXEC SQL SELECT Sname, Ssex, Sage, SdeptINTO :Sname, :Ssex, :Sage, :SdeptFROM StudentWHERE Sno= :Sno;,7.1 嵌入式SQL,例 2 查询某个学生选修某门课程的成绩。由于学生的成绩可能是空值，使用了指示变量gradenullflag。EXEC SQL SELECT GradeINTO :grade :gradenullfalgFROM SCWHERE Sno=:Sno AND Cno=:Cno;,

7、7.1 嵌入式SQL,7.1.4 游标 SQL语言与宿主语言有不同的数据处理方式。SQL语言是面向集合的，一条SQL语句将产生或处理多条记录。而宿主语言是面向记录的，一组宿主变量一次只能存放一条记录。所以仅使用宿主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此SQL引入了游标的概念，用游标来协调这两种不同的处理方式。,7.1 嵌入式SQL,游标（Cursor）是系统开设的一个数据缓冲区，存放SQL语句的执行结果。游标有一个名字，可以通过游标逐一获取记录，并赋予宿主语言的宿主变量，交由宿主语言进一步处理。如图7.2。,7.1 嵌入式SQL,图7.2游标示意图,游标包括以下两个部分：1

8、、游标结果集（cursor result set） 由定义游标的SELECT语句返回的行的集合。2、游标的位置（cursor position） 指向这个集合中某一行的指针。,7.1 嵌入式SQL,一、使用游标读取数据使用游标处理数据的流程如图7.3。,图7.3使用游标的一般过程,7.1 嵌入式SQL,1、声明游标格式：EXEC SQL DECLARE cursor-name INSENSITIVE SCROLL CURSORFOR SELECT statement FOR READ ONLY SELECT语句定义了游标结果集，游标名用于对游标的各种操作。使用INSENSITIVE通知DBMS

9、在游标存续期间，不允许其它的事务修改游标结果集中的数据。,7.1 嵌入式SQL,如果游标不修改数据库中的数据，使用FOR READ ONLY告知DBMS，DBMS将允许这样的游标和INSENSITIVE类型的游标并发执行。 默认情况下，游标采用顺序处理的方式，依次处理结果集中的记录。根据需要，也可以将游标定义为滚动（SCROLL）游标。对滚动游标，SQL提供了若干命令将游标移动到希望的位置。,7.1 嵌入式SQL,next 把游标移动到当前游标所指下一个记录prior 把游标移动到当前游标所指上一个记录first 把游标移动到第一个记录last 把游标移动到最后一个记录,7.1 嵌入式SQL,

10、absolute n 如果n是一个正整数，把游标移动到从前往后计数的第n个记录；如果n是一个负整数，把游标移动到从后数的第n个记录。relative n 如果n是一个正整数，把游标移动相对于当前游标所指记录之后的第n个记录；如果n是一个负整数，把游标移动相对于当前游标所指记录之前的第n个记录。,7.1 嵌入式SQL,例 3 声明存取计算机系全体学生的游标。EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 计算机FOR READ ONLY;,7.1 嵌入式SQL,2、打开游标格式：EXEC SQL

11、OPEN ; 打开游标后，DBMS执行与游标相关联的SELECT语句，并把查询结果存放到游标中，游标指向第1个记录。例如，EXEC SQL OPEN dept_computer;,图7.4 游标dept_computer的内容,7.1 嵌入式SQL,3、存取游标 如果游标不是滚动游标，使用FETCH语句读取当前游标所指的记录到宿主变量中，然后，游标自动移向下一个记录：EXEC SQL FETCH FROM cursor_name INTO variable-list;,7.1 嵌入式SQL,如果游标是滚动游标，先将游标移动到所指定的记录，然后将游标所指的记录存放到宿主变量中： EXEC SQL

12、 FETCH NEXT | PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE nFROM cursor_nameINTO variable-list;,7.1 嵌入式SQL,4、关闭游标 游标使用完毕后，要释放其所占的系统资源。使用下面的语句：EXEC SQL CLOSE cursor_name; 游标指向了结果集最后一个记录之后时，SQLSTATE的值被设置为02000，可以用这个条件作为循环的结束条件。,7.1 嵌入式SQL,例 4 通过游标dept_computer读取每个学生的信息，并显示。 EXEC SQL BEGIN DECLARE SECTION char

13、Sno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;,7.1 嵌入式SQL,EXEC SQL END DECLARE SECTION-声明游标EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 计算机FOR READ ONLY;-打开游标EXEC SQL OPEN dept_computer;,7.1 嵌入式SQL,-读取第1条记录EXEC SQL FETCH dept_computer INTO :Sno, :Sname,:S

14、sex,:Sage,:Sdept;while(strcmp(SQLSTATE, 02000) != 0)/输出 EXEC SQL FETCH dept_computer INTO :Sno, :Sname,:Ssex,:Sage,:Sdept;-关闭游标EXEC SQL CLOSE dept_computer;,7.1 嵌入式SQL,二、使用游标修改数据 上面介绍了通过游标把数据库的批量数据传送到宿主变量中做进一步处理。游标的另一个作用是可以通过游标修改数据库中的数据。 使用游标修改数据要注意两件事： 1.声明游标时没有加FOR READ ONLY关键字； 2.一般不要修改基于多表的游标。,7

15、.1 嵌入式SQL,1、UPDATE语句格式：UPDATE table-name SET column- name = expressionWHERE CURRENT OF cursor_name功能：修改数据库中与当前游标所指向的记录相对应的元组的列值。,7.1 嵌入式SQL,2、DELETED语句DELETE FROM table-nameWHERE CURRENT OF cursor_name功能：删除数据库中与当前游标所指向的记录相对应的元组。,7.1 嵌入式SQL,例 5 通过游标dept_computer给每个学生年龄增加1岁EXEC SQL BEGIN DECLARE SECTI

16、ONcharSQLSTATE6;EXEC SQL END DECLARE SECTION-声明游标EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 计算机FOR UPDATE OF Sage -只允许修改列Sage的值;,7.1 嵌入式SQL,-打开游标EXEC SQL OPEN dept_computer;-读取第1条记录EXEC SQL FETCH dept_computer;while(strcmp(SQLSTATE, 02000) != 0)EXEC SQL UPDATE Student

17、SET Sage = Sage + 1WHERE CURRENT OF dept_computer;FETCH dept_computer;-读下一条记录;-关闭游标EXEC SQL CLOSE dept_computer;,7.1 嵌入式SQL,7.1.5 动态SQL简介动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式： 1.语句可变：允许用户在程序运行时输入完整的SQL语句。 2.条件可变。 3.数据库对象、查询条件均可变。,7.1 嵌入式SQL,一、PREPARE PREPARE把存放在宿主变量中的一个字符串“准备”为一个SQL语句，所谓“准备”就是通过和DBMS

18、的通信，对SQL语句进行分析和生成执行计划。具体格式为：PREPARE stmt_name FROM :host-variable 其中，stmt_name为待准备的SQL语句命名，供其它动态SQL命令引用，stmt_name在一个程序模块必需是唯一的。,7.1 嵌入式SQL,二、EXECUTE EXECUTE执行由PREPARE准备的SQL语句：EXECUTE prepared_stmt_name USING : host-variabel ,.; prepared_stmt_name是由某个PREPARE准备的SQL语句，USING后面的宿主变量用于替换SQL语句的？参数，有几个?参数就必

19、须有几个宿主变量，数据类型必须兼容，并且按照位置对应的原则进行替换。,7.1 嵌入式SQL,二、EXECUTE 例 6 生成一个向SC表插入任意元组的SQL语句EXEC SQL BEGIN DECLARE SECTION;char prep = INSERT INTO sc VALUES(?,?,?);char sno8;char cno5;short grade;EXEC SQL END DECLARE SECTION;,7.1 嵌入式SQL,二、EXECUTEEXEC SQL PREPARE prep_stat FROM :prep;while (strcmp(SQLSTATE, 0000

20、0) = = 0) scanf(%s , sno); scanf(%s , cno); scabf(%d , grade); EXEC SQL EXECUTE prep_stat USING :sno, :cno, :grade;,7.1 嵌入式SQL,三、EXECUTE IMMEDIATE EXECUTE IMMEDIATE语句结合了PREPARE和EXECUTE的功能，准备一个SQL语句并且立即执行它。因为准备一个SQL语句需要和DBMS通信，开销比较大，所以，采用PREPARE和EXECUTE方式适合于准备的语句要多次执行的方式，而 EXECUTE IMMEDIATE适用于只执行一次的情

21、形。语句格式为：EXECUTE IMMEDIATE : :host-variable,7.2 存储过程,存储过程是SQL语句和可选控制流语句的预编译集合。它以一个名称存储并作为一个单元处理。存储过程存储在数据库内，由应用程序调用执行。存储过程包含流程控制以及对数据库的查询，可接受参数、输出参数、返回单个或多个结果集以及执行状态，而且允许定义变量、条件执行、循环等编程功能。,7.2 存储过程,存储过程有以下特点： 1.确保数据访问和操作的一致性，提高应用程序的可维护性； 2.提高系统的执行效率； 3.提供一种安全机制； 4.减少了网络的流量负载； 5.若要改变业务规则或策略，只需改变存储过程和参

22、数，不必修改应用程序。,7.2 存储过程,7.2.1 SQL/PSM 编写存储过程以及后面介绍的触发器需要一种通用的程序设计语言。SQL/PSM（persistent stored modules）标准制定了一个程序设计语言，这个语言可以被DBMS解释执行。 SQL/PSM提供了通用程序设计语言的定义变量、流程控制和存储过程定义以及调用语句。在PSM标准出现之前，一些DBMS厂商就提供了自己的编程语言。,7.2 存储过程,一、存储过程和函数的定义和调用 SQL/PSM中的存储过程和函数与PASCAL语言中的过程和函数十分相似，但是语法比较复杂，下面给出一个简化版本。CREATE PROCEDU

23、RE (IN | OUT ,.)routine-body;CREATE FUCTION ( variable-name datatype,.) RETURN datatyperoutine-body;,7.2 存储过程,在定义存储过程和函数时，首先要赋予一个在数据库范围内唯一的名字，然后给出参数。对于存储过程，要进一步指出每个参数是输入参数还是输出参数。对于函数，在函数体内要使用RETURN 语句返回一个值。过程体和函数体使用SQL/PSM提供的过程控制语句和SQL语句编写。,7.2 存储过程,SQL/PSM定义的函数可以出现在SQL语句中可以出现常数的地方。存储过程序要使用CALL语句调用。

24、在过程体或函数体内直接使用CALL (argument list)，在嵌入式SQL中要在CALL语句之前加上EXEC SQL。例如，下面的过程完成一个学生的转系任务，学生的学号和要转到的系名称作为过程的输入参数。,7.2 存储过程,CREATE PROCEDURE transform-dept(IN sno char(7), IN dept char(20) UPDATE student； SET Sdept = dept； WHERE Sno = sno;,7.2 存储过程,二、变量的声明和赋值 DECLARE语句为一个变量指定变量名称和数据类型，分配存储空间。变量的作用范围是存储过程，DE

25、CLARE语句应该出现在其它可执行语句之前。DECLARE ; SET语句为变量赋值，先计算出等号右边的表达式的值，然后把得到的值赋于变量，表达式的构成与其它程序设计语言相同。SET = ;,7.2 存储过程,三、分支语句 SQL/PSM的分支语句的功能同C语言这样的高级程序设计语言，但在语法上和逻辑条件的构成上略有不同。IF THEN ELSEIF THEN ELSIFELSE END IF,7.2 存储过程,四、循环语句 SQL/PSM的循环语句多用于处理游标，有多种形式，最基本的语句形式为：LOOPEND LOOP; 一般在LOOP前加上一个语句标号，语句标号的形式是一个名字后面紧跟一个

26、冒号（:），一般在循环体中要对循环条件进行测试，当条件满足时使用下面的语句结束循环，开始执行END LOOP后面的语句。LEAVE ;,7.2 存储过程,四、循环语句 例 7 给出选修某门课程的成绩最好的学生的姓名和所在系，如果有多个这样的学生，则任意给出一个。CREATE PROCEDURE course_list(INvcno char(4),OUT vsname char(8),OUT vsdept char(2)DECLAREmax_grade int;DECLAREvgradeint;DECLAREvsnochar(7);,7.2 存储过程,四、循环语句DECLARE cur_sc

27、CURSOR FOR SELECT Sno, Grade FROM SC WHERE Cno = vcno;BEGINSELECT max(grade) INTO max_grade FROM SC WHERE Cno = vcno;OPEN cur_sccur_loop: LOOPFETCH cur_sc INTO vsno, vgrade;IF vgrade = max_grade THENSELECT Sname, Sdept INTO vsname, vsdeptFROM Student WHERE Sno = vsno;LEAVE cur_loop;END IF;END LOOP;C

28、LOSE cur_sc;END;,7.2 存储过程,四、循环语句 例 8 使用FOR语句改写上例。CREATE PROCEDURE course_list(INvcnochar(4),OUTvsnamechar(8),OUTvsdeptchar(20)DECLAREmax_gradeint;DECLAREvgradeint;DECLAREvsnochar(7);,7.2 存储过程,四、循环语句 BEGINSELECT max(grade) INTO max_grade FROM SC WHERE Cno = vcno;FOR cur_loop AS cur_sc CURSOR FORSELEC

29、T Sno, Grade FROM SC WHERE Cno = vcnoDOIF vgrade = max_grade THENSELECT Sname, Sdept INTO vsname, vsdeptFROM Student WHERE Sno = vsno;LEAVE cur_loop;END IF;END FOR;END;,7.2 存储过程,五、异常处理 DBMS在执行SQL语句时，如果遇到错误则将变量SQLSTATE的值设置为不等于00000 的长度为5的字符串。 SQL/PSM提供了异常处理句柄(exception handler)功能，异常处理句柄被封装在一个由BEGIN和E

30、ND界定的语句块中，当语句块中的某个语句发生了异常处理句柄所捕获的错误，则执行异常处理句柄预先定义的代码。,7.2 存储过程,异常处理句柄的格式为：DECLARE HANDLER FOR condition list是用逗号分隔开的一组条件，条件可以用DECLARE语句定义，或直接用SQLSTATE所规定的代码。,7.2 存储过程,next step约定执行完statement后需要做的工作，有三种情形： 1.CONTINUE 继续执行引起错误的语句的下一条语句 2.EXIT 跳出异常处理句柄所在的语句块，继续执行END后面的语句 3.UNDO 首先撤销引起错误的语句对数据库所作的所有修改，以

31、后的操作同EXIT。,7.2 存储过程,例 9 查询学生王林所在的系，如果有多个学生叫王林或者没有叫王林的学生，则返回一串星号。CREATE PROCEDURE course_list(OUT vsdept ar(20)DECLARE Not_Found CONDITION FOR SQLSTATE 02000;DECLARE Too_Many CONDITION FOR SQLSTATE 21000;BEGINDECLARE EXIT HANDLER FOR Not_Found, Too_ManySET vsdept = *;SELECT Sdept INTO vsdept FROM Stu

32、dent WHERE Sname = 王林;END;,7.2 存储过程,7.2.2 PL/SQL PL/SQL是Oracle的编程语言，具有支持内存变量、条件结构和循环结构、过程和函数以及执行SQL语句的能力。本节简单介绍PL/SQL的有关概念。一、PL/SQL的块结构 PL/SQL程序的基本结构是块。所有的PL/SQL程序都是由块组成的，这些块之间还可以互相嵌套，每个块完成一个逻辑操作。基本块由定义部分、执行部分和异常处理部分组成。除执行部分是必须的以外，其它的部分都是可选的。,7.2 存储过程,例 10 向关系Student中插入10个学生,学生编号从1001到1010。DECLAREsn

33、oNUMBER;/*定义部分*/BEGIN/*执行部分*/sno := 1001;LOOP INSERT INTO Student(Sno) VALUES(to_char(:sno, 9999); sno := sno +1; EXIT WHEN sno 1010; END LOOP;EXCEPTION/*异常处理*/WHEN others THEN/*处理异常情况others*/dbms_output.put_line(Error);END;,7.2 存储过程,二、变量和常量的定义1、PL/SQL中定义变量的语法形式是：变量名 数据类型 NOT NULL := 初值表达式变量名由字母、数字、

34、下划线（_）、美圆符号($)和英镑符号(#)组成，以字母开头，长度不超过30个字符。保留字不能做变量名。除此之外，PL/SQL还提供了构造数据类型记录和表以及类型转换函数。,7.2 存储过程,2、常量的定义类似于变量的定义：常量名称 数据类型 CONSTANT := 常量表达式常量必须要给一个值，并且其值在存在期间或常量的作用域内不能改变。如果试图修改它，PL/SQL将返回一个异常。3、赋值语句变量名 := 表达式,7.2 存储过程,三、控制结构1、条件控制语句形式一：IF条件 THEN语句序列1;ELSIF条件2 THEN语句序列2；. ELSE最后的语句序列；END IF;,7.2 存储过

35、程,2、循环控制语句 PL/SQL 提供了三种不同的循环结构。每种循环结构都能够重复地执行一组PL/SQL语句，并可根据条件终止循环的执行。形式一：LOOP语句序列;END LOOP;跳出循环语句：形式一：EXIT形式二：EXIT WHEN条件,7.2 存储过程,2、循环控制语句 例 11 如果选修课程1156的学生人数不超过30人时则可以加入选课的学生。DECLAREp_total NUMBER;BEGINSELECT COUNT(*) INTO p_total FROM SC WHERE Cno = 1156;IF (p_total 30) THENINSERT INTO SC(20000

36、12 , 1156,NULL);END IF;END;,7.2 存储过程,2、循环控制语句 例 12 将百分制转换成等级制DECLARE score NUMBER;gradeCHAR(1);BEGIN./*从关系SC中读出一个一个学生的成绩*/IF (score =90 AND score =100) THENgrade := A;,ELSIF (score = 80) THENgrade := B;ELSIF (score = 70) THENgrade := C;ELSIF (score = 60) THENgrade := D;ELSEgrade := E;END IF;END;,7.2

37、 存储过程,形式二：WHILE 条件 LOOP语句序列;END LOOP;形式三：FOR 循环变量 IN REVERSE 下限.上限 LOOP语句序列；END LOOP;,7.2 存储过程,例 13 求1到10的和用LOOP实现DECLAREp_sumNUMBER :=0;/*p_sum的初始值为0*/p_incNUMBER;BEGINp_inc := 1;LOOPp_sum := p_sum + p_inc;p_inc := p_inc + 1;EXIT WHEN (p_inc 10);END LOOP;dbms_output.put_line(p_sum);/*Oracle包提供的输出函数

38、*/END;,7.2 存储过程,用WHILE实现DECLAREp_sumNUMBER :=0;p_incNUMBER;BEGINp_inc := 1;WHILE (p_inc = 10) LOOPp_sum := p_sum + p_inc;p_inc := p_inc + 1;END LOOP;dbms_output.put_line(p_sum);END;,7.2 存储过程,用FOR实现DECLAREp_sumNUMBER :=0;p_incNUMBER;BEGINFOR p_inc IN 1.10 LOOPp_sum := p_sum + p_inc;END LOOP;dbms_outp

39、ut.put_line(p_sum);END;,7.2 存储过程,四、异常处理 异常是一个在出现特定问题时被激活（或唤醒）了的错误状态。在出现异常时，程序在产生异常的语句处停下来，根据异常的种类去寻找该语句所在基本块块的处理该异常的语句组，执行这些语句，完成异常处理。如果语句所在的基本块中无对应的异常处理部分，而该基本块又嵌套在另一个块中，则去外层基本块中的异常处理部分查找，如果最外层的程序块也没有包含处理该异常的部分，则程序停止执行。 异常处理完毕后，控制返回引起异常的语句所在基本块的外层基本块。,7.2 存储过程,1、系统预定义的异常PL/SQL预先定义了一些常见的异常名称。,表7.1系统

40、定义的异常(a),7.2 存储过程,1、系统预定义的异常PL/SQL预先定义了一些常见的异常名称。,表7.1系统定义的异常(b),7.2 存储过程,例 14 处理零做除数DECLAREnum1NUMBER := 6;num2 NUMBER := 0;BEGINnum1 := num1 / num2;EXCEPTIONWHEN ZERO_DIVIDE THENDECLARE/*嵌套块*/err_numNUMBER := SQLCODE;err_msgVARCHAR2(512):= SQLERRM;BEGINdbms_output.put_line(ORA Error Number | err_n

41、um);dbms_output.put_line(ORA Error Messager | err_msg);END;END;,7.2 存储过程,2、程序定义的异常 程序员可以定义一个异常名，然后在满足一定的条件下用RAISE语句触发异常处理。,7.2 存储过程,例 15在向关系SC中输入学生成绩时，成绩必须为正数。DECLAREsnoCHAR(7);cnoCHAR(4);gradeSMALLINT;must_be_positive_gradeEXCEPTION;BEGIN/*输入sno,cno,grade*/,7.2 存储过程,IF grade = 0 THEN INSERT INTO SC

42、 VALUES(sno,cno,grade);ELSERAISE must_be_positive_grade;END IF;EXCEPTIONWHEN must_be_positive_grade THENdbms_output.put_line(Grade Must Be Positive | grade);END;,7.2 存储过程,例 16 异常处理后控制不再返回异常语句所在的基本块。DECLARE ratio NUMBER(3,1); t1 NUMBER; t2 NUMBER;BEGIN INSERT INTO Course(1234, Data Structure, 1210,4)

43、; SELECT COUNT(*) INTO t1 FROM Student; SELECT COUNT(*) INTO t2 FROM SC WHERE Cno = 1234;ratio := t1 / t2 *100;INSERT INTO Course(1235, Data MIning, 1210,4);EXCEPTIONWHEN ZERO_DIVIDE THEN.END;,7.2 存储过程,五、存储过程和函数 PL/SQL块主要有两种类型，即命名块和匿名块。前面介绍的都是匿名块，匿名块每次使用时都要进行编译，它不能被存储到数据库种，也不能在其它的PL/SQL块中调用。过程和函数是命名

44、块，被编译后保存在数据库中，可以被反复调用，它们的运行速度较快。,7.2 存储过程,1、存储过程存储过程是利用PL/SQL编写的一组规定的操作。此过程存储在数据库中，这就是将它称为存储过程的原因。语法形式为：CREATE OR REPLACE PROCEDURE 过程名（参数1, 参数2, .） AS | IS变量声明;BEGIN语句组；END;,7.2 存储过程,例 17 计算某个员工的应发奖金CREATE OR REPLACE PROCEDURE calc_bonus(emp_id IN INTEGER) IShire_dateDATE;/*进某单位工作的日期*/bonusREAL;BEG

45、IN/*奖金为工资的10%*/SELECT sal * 0.10, hiredate INTO bonus,hire_date FROM emp WHERE empno = emp_id;IF (bonus IS NULL) THEN INSERT INTO emp_bonus(empno,bonus) VALUES(emp_id,NULL);,7.2 存储过程,例 17 计算某个员工的应发奖金ELSE IFMONTHS_BETWEEN(SYSDATE,hire_date) 60 THEN /工作时间超过5年bonus := bonus + 500;奖金增加500元*/END IF;INSER

46、T INTO emp_bonus(empno,bonus) VALUES(emp_id,bonus); END IF;END;,7.2 存储过程,2、函数函数类似于存储过程。其语法是：CREATE OR REPLACE FUNCTION 函数名（参数1, 参数2, .） RETURN DataType AS | IS变量声明;BEGIN语句组；END;,7.2 存储过程,例 18 计算某个员工的应发奖金CREATE OR REPLACE FUNCTION calc_bonus_func(emp_id INTEGER) RETURN REAL IShire_dateDATE;bonusREAL;

47、BEGINSELECT sal * 0.10, hiredate INTO bonus,hire_date FROM emp WHERE empno = emp_id;,7.2 存储过程,例 18 计算某个员工的应发奖金IF (bonus IS NULL) THENRETURN ( -1 );ELSEIF MONTHS_BETWEEN(SYSDATE,hire_date) 60 THENbonus := bonus + 500;END IF;RETURN ( bonus ); END IF;END;,7.2 存储过程,3、存储过程和函数的区别 过程和函数都是经过编译以后保存在数据库中的。 通过

48、将参数的模式设置为OUT，则过程也可以返回值，函数也可以返回多于一个值（但不提倡）。参数的模式有IN,OUT和IN OUT，默认的参数模式是IN。IN表示参数是向过程（函数）传送值的，该参数的值在过程（函数）体中不能被改变。OUT表示该参数的值是由过程（函数）返回的。IN OUT具有IN和OUT的双重意义。,7.2 存储过程,过程和函数都可以有声明部分、执行部分和异常处理部分。 在使用过程和函数时，过程要作为一个语句，而函数必须出现在SQL语句中（所有可以出现数值的地方） 过程中可以包含INSERT、UPDATE和DELETE语句，而函数中不能出现这些语句。,7.2 存储过程,例 19 计算e

49、mpno从1到10的员工的奖金，并把计算结果存放到关系emp_bonus中。利用存储过程来实现DECLAREp_empnoNUMBER;BEGINFOR p_empno IN 1.10LOOPcalc_bonus(p_empno);/*存储过程作为一个语句*/END LOOP;END;,7.2 存储过程,例 19 计算empno从1到10的员工的奖金，并把计算结果存放到关系emp_bonus中。利用函数来实现DECLAREp_empnoNUMBER;p_bonusREAL;BEGINFOR p_empno IN 1.10LOOPSELECT calc_bonus_func(p_empno) I

50、NTO p_bonus FROM DUAL;INSERT INTO emp_bonus(p_empno,p_bonus);END LOOP;END;,7.2 存储过程,4、删除过程和函数 当一个存储过程或函数不再需要时，可以用DROP语句将它们删除掉。例 20删除calc_bonus和calc_bonus_funcDROP PROCEDURE calc_bonus;DROP FUNCTIONcalc_bonus_func;,7.2 存储过程,六、游标 如果SELECT语句只返回一行，则可以将该结果存放到变量中，例如语句SELECT COUNT(*) INTO p_total FROM Stud