在应用中使用SQL-PPT课件.ppt

1、第7章 在应用中使用SQL7.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 TABL

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

3、SQLPreprocessorHost language+Function callsCompilerObject-codeProgramSQL library图7.1 嵌入式SQL的处理过程7.1 嵌入式嵌入式SQL 7.1.2 嵌入式嵌入式SQL语句与宿主语言的通信语句与宿主语言的通信 将SQL嵌入到高级语言中混合编程，SQL语句负责操纵数据库，高级语言语句负责控制程序流程。这时程序中含有两种不同计算模型的语句，一种是描述性的面向集合的SQL语句，一种是过程性的高级语言语句，SQL标准主要使用宿主变量在它们之间互相交换数据，进行通信。7.1 嵌入式嵌入式SQL 宿主变量使用声明节（decl

4、are section）定义宿主变量，格式如下： EXEC SQL BEGIN DECLARE SECTION 按照宿主语言的语法定义的变量 EXEC SQL END DECLARE SECTION7.1 嵌入式嵌入式SQL 7.1.2 嵌入式SQL语句与宿主语言之间的通信例如：EXEC SQL BEGIN DECLARE SECTIONcharSno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;EXEC SQL END DECLARE SECTION7.1 嵌入式嵌入式SQL7.1.3 查询结果为单个记录的查询结果为单个

5、记录的SELECT语句语句 在嵌入式SQL中，查询结果为单个记录的SELECT语句使用INTO子句把查询结果传送到宿主变量，供宿主语言继续处理。该语句的一般格式为： 7.1 嵌入式嵌入式SQLEXEC SQL SELECT ALL|DISTINCT , INTO , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;7.1 嵌入式嵌入式SQL7.1.3 查询结果为单个记录的查询结果为单个记录的SELECT语句语句 1.INTO子句、WHERE子句的条件表达式、HAVING短语的的条件表达式中均可以使用宿主变量。 2.查询返回的记录中，可能某些列为空值

6、。如果INTO子句中宿主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应宿主变量后面的指示变量置为负值，不再向宿主变量赋值。 7.1 嵌入式嵌入式SQL7.1.3 查询结果为单个记录的查询结果为单个记录的SELECT语句语句 3.如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，DBMS将SQLSTATE的值设置为21000。7.1 嵌入式嵌入式SQL例例 1 查询某个学生的信息，这个学生的学号存放在宿主变量Sno中，并且将查询得到的学生信息存放到上一小节定义的变量中。EXEC SQL SELECT Sname, Ssex, Sage, SdeptINTO :

7、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.1 嵌入式嵌入式SQL7.1.4 游标游标 SQL语言与宿主语言有不同的数据处理方式。SQL语言是面向集合的，一条SQL语句将产生或处理多条记录。而宿主语言是面向记录的，一组宿主变量一次

8、只能存放一条记录。所以仅使用宿主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此SQL引入了游标的概念，用游标来协调这两种不同的处理方式。7.1 嵌入式嵌入式SQL 游标（Cursor）是系统开设的一个数据缓冲区，存放SQL语句的执行结果。游标有一个名字，可以通过游标逐一获取记录，并赋予宿主语言的宿主变量，交由宿主语言进一步处理。如图7.2。7.1 嵌入式嵌入式SQL记录1记录2记录n游标图7.2游标示意图游标包括以下两个部分：1、游标结果集（cursor result set） 由定义游标的SELECT语句返回的行的集合。2、游标的位置（cursor position） 指向这个集

9、合中某一行的指针。7.1 嵌入式嵌入式SQL一、使用游标读取数据一、使用游标读取数据使用游标处理数据的流程如图7.3。是否使用DECLARE CURSOR语句声明游标使用OPEN语句打开游标使用FETCH INTO语句从游标中提取数据使用CLOSE语句关闭游标最后一个元组？图7.3使用游标的一般过程7.1 嵌入式嵌入式SQL1、声明游标、声明游标格式：EXEC SQL DECLARE cursor-name INSENSITIVE SCROLL CURSORFOR SELECT statement FOR READ ONLY SELECT语句定义了游标结果集，游标名用于对游标的各种操作。使用I

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

11、到最后一个记录7.1 嵌入式嵌入式SQLabsolute 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 嵌入式嵌

12、入式SQL2、打开游标、打开游标格式：EXEC SQL OPEN ; 打开游标后，DBMS执行与游标相关联的SELECT语句，并把查询结果存放到游标中，游标指向第1个记录。例如，EXEC SQL OPEN dept_computer;图7.4 游标dept_computer的内容2000012,王林,男,19,计算机游标2000014,葛波,女,18,计算机7.1 嵌入式嵌入式SQL3、存取游标、存取游标 如果游标不是滚动游标，使用FETCH语句读取当前游标所指的记录到宿主变量中，然后，游标自动移向下一个记录：EXEC SQL FETCH FROM cursor_name INTO varia

13、ble-list; 7.1 嵌入式嵌入式SQL 如果游标是滚动游标，先将游标移动到所指定的记录，然后将游标所指的记录存放到宿主变量中： EXEC SQL FETCH NEXT | PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE nFROM cursor_nameINTO variable-list;7.1 嵌入式嵌入式SQL4、关闭游标、关闭游标 游标使用完毕后，要释放其所占的系统资源。使用下面的语句：EXEC SQL CLOSE cursor_name; 游标指向了结果集最后一个记录之后时，SQLSTATE的值被设置为02000，可以用这个条件作为循环的结束条件。7

14、.1 嵌入式嵌入式SQL例例 4 通过游标dept_computer读取每个学生的信息，并显示。 EXEC SQL BEGIN DECLARE SECTION charSno8;charSname9;charSsex3;shortSage ;charSdept21;charSQLSTATE6;7.1 嵌入式嵌入式SQLEXEC SQL END DECLARE SECTION-声明游标EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 计算机FOR READ ONLY;-打开游标EXEC SQL OP

15、EN dept_computer;7.1 嵌入式嵌入式SQL-读取第1条记录EXEC SQL FETCH dept_computer INTO :Sno, :Sname,:Ssex,: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二、使用游标修改数据二、使用游标修改数据 上面介绍了通过游标把数据库的批量数据传送到宿主

16、变量中做进一步处理。游标的另一个作用是可以通过游标修改数据库中的数据。 使用游标修改数据要注意两件事： 1.声明游标时没有加FOR READ ONLY关键字； 2.一般不要修改基于多表的游标。7.1 嵌入式嵌入式SQL1、UPDATE语句格式：UPDATE table-name SET column- name = expressionWHERE CURRENT OF cursor_name功能：修改数据库中与当前游标所指向的记录相对应的元组的列值。7.1 嵌入式嵌入式SQL2、DELETED语句DELETE FROM table-nameWHERE CURRENT OF cursor_nam

17、e功能：删除数据库中与当前游标所指向的记录相对应的元组。7.1 嵌入式嵌入式SQL例 5 通过游标dept_computer给每个学生年龄增加1岁EXEC SQL BEGIN DECLARE SECTIONcharSQLSTATE6;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_com

18、puter;-读取第1条记录EXEC SQL FETCH dept_computer;while(strcmp(SQLSTATE, 02000) != 0)EXEC SQL UPDATE Student SET Sage = Sage + 1WHERE CURRENT OF dept_computer;FETCH dept_computer;-读下一条记录;-关闭游标EXEC SQL CLOSE dept_computer;7.1 嵌入式嵌入式SQL7.1.5 动态动态SQL简介简介动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式： 1.语句可变：允许用户在程序运行时输

19、入完整的SQL语句。 2.条件可变。 3.数据库对象、查询条件均可变。7.1 嵌入式嵌入式SQL一、一、PREPARE PREPARE把存放在宿主变量中的一个字符串“准备”为一个SQL语句，所谓“准备”就是通过和DBMS的通信，对SQL语句进行分析和生成执行计划。具体格式为：PREPARE stmt_name FROM :host-variable 其中，stmt_name为待准备的SQL语句命名，供其它动态SQL命令引用，stmt_name在一个程序模块必需是唯一的。7.1 嵌入式嵌入式SQL二、二、EXECUTE EXECUTE执行由PREPARE准备的SQL语句：EXECUTE prep

20、ared_stmt_name USING : host-variabel ,.; prepared_stmt_name是由某个PREPARE准备的SQL语句，USING后面的宿主变量用于替换SQL语句的？参数，有几个?参数就必须有几个宿主变量，数据类型必须兼容，并且按照位置对应的原则进行替换。7.1 嵌入式嵌入式SQL二、二、EXECUTE 例例 6 生成一个向SC表插入任意元组的SQL语句EXEC SQL BEGIN DECLARE SECTION;char prep = INSERT INTO sc VALUES(?,?,?);char sno8;char cno5;short grade

21、;EXEC SQL END DECLARE SECTION;7.1 嵌入式嵌入式SQL二、二、EXECUTEEXEC SQL PREPARE prep_stat FROM :prep;while (strcmp(SQLSTATE, 00000) = = 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和

22、EXECUTE的功能，准备一个SQL语句并且立即执行它。因为准备一个SQL语句需要和DBMS通信，开销比较大，所以，采用PREPARE和EXECUTE方式适合于准备的语句要多次执行的方式，而 EXECUTE IMMEDIATE适用于只执行一次的情形。语句格式为：EXECUTE IMMEDIATE : :host-variable7.2 存储过程存储过程 存储过程是SQL语句和可选控制流语句的预编译集合。它以一个名称存储并作为一个单元处理。存储过程存储在数据库内，由应用程序调用执行。存储过程包含流程控制以及对数据库的查询，可接受参数、输出参数、返回单个或多个结果集以及执行状态，而且允许定义变量、

23、条件执行、循环等编程功能。7.2 存储过程存储过程 存储过程有以下特点： 1.确保数据访问和操作的一致性，提高应用程序的可维护性； 2.提高系统的执行效率； 3.提供一种安全机制； 4.减少了网络的流量负载； 5.若要改变业务规则或策略，只需改变存储过程和参数，不必修改应用程序。7.2 存储过程存储过程7.2.1 SQL/PSM 编写存储过程以及后面介绍的触发器需要一种通用的程序设计语言。SQL/PSM（persistent stored modules）标准制定了一个程序设计语言，这个语言可以被DBMS解释执行。 SQL/PSM提供了通用程序设计语言的定义变量、流程控制和存储过程定义以及调用

24、语句。在PSM标准出现之前，一些DBMS厂商就提供了自己的编程语言。 7.2 存储过程存储过程一、存储过程和函数的定义和调用一、存储过程和函数的定义和调用 SQL/PSM中的存储过程和函数与PASCAL语言中的过程和函数十分相似，但是语法比较复杂，下面给出一个简化版本。CREATE PROCEDURE (IN | OUT ,.)routine-body;CREATE FUCTION ( variable-name datatype,.) RETURN datatyperoutine-body;7.2 存储过程存储过程 在定义存储过程和函数时，首先要赋予一个在数据库范围内唯一的名字，然后给出参数

25、。对于存储过程，要进一步指出每个参数是输入参数还是输出参数。对于函数，在函数体内要使用RETURN 语句返回一个值。过程体和函数体使用SQL/PSM提供的过程控制语句和SQL语句编写。7.2 存储过程存储过程 SQL/PSM定义的函数可以出现在SQL语句中可以出现常数的地方。存储过程序要使用CALL语句调用。在过程体或函数体内直接使用CALL (argument list)，在嵌入式SQL中要在CALL语句之前加上EXEC SQL。例如，下面的过程完成一个学生的转系任务，学生的学号和要转到的系名称作为过程的输入参数。7.2 存储过程存储过程CREATE PROCEDURE transform-

26、dept(IN sno char(7), IN dept char(20) UPDATE student； SET Sdept = dept； WHERE Sno = sno;7.2 存储过程存储过程二、变量的声明和赋值二、变量的声明和赋值 DECLARE语句为一个变量指定变量名称和数据类型，分配存储空间。变量的作用范围是存储过程，DECLARE语句应该出现在其它可执行语句之前。DECLARE ; SET语句为变量赋值，先计算出等号右边的表达式的值，然后把得到的值赋于变量，表达式的构成与其它程序设计语言相同。SET = ;7.2 存储过程存储过程三、分支语句三、分支语句 SQL/PSM的分支语

27、句的功能同C语言这样的高级程序设计语言，但在语法上和逻辑条件的构成上略有不同。IF THEN ELSEIF THEN ELSIFELSE END IF7.2 存储过程存储过程四、循环语句四、循环语句 SQL/PSM的循环语句多用于处理游标，有多种形式，最基本的语句形式为：LOOPEND LOOP; 一般在LOOP前加上一个语句标号，语句标号的形式是一个名字后面紧跟一个冒号（:），一般在循环体中要对循环条件进行测试，当条件满足时使用下面的语句结束循环，开始执行END LOOP后面的语句。LEAVE ;7.2 存储过程存储过程四、循环语句四、循环语句 例例 7 给出选修某门课程的成绩最好的学生的姓

28、名和所在系，如果有多个这样的学生，则任意给出一个。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 CURSOR FOR SELECT Sno, Grade FROM SC WHERE Cno = vcno;BEGINSELECT max(grade) INTO max_grade FROM S

29、C 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;CLOSE cur_sc;END;7.2 存储过程存储过程四、循环语句 例 8 使用FOR语句改写上例。CREATE PROCEDURE course_list(INvcnochar(4),OUT

30、vsnamechar(8),OUT vsdeptchar(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 FORSELECT Sno, Grade FROM SC WHERE Cno = vcnoDOIF vgrade = max_grade THENSELECT Sname, Sdept INTO vs

31、name, 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和END界定的语句块中，当语句块中的某个语句发生了异常处理句柄所捕获的错误，则执行异常处理句柄预先定义的代码。7.2 存储过程存储过程异常处理句柄的格式为：DECLARE

32、HANDLER FOR condition list是用逗号分隔开的一组条件，条件可以用DECLARE语句定义，或直接用SQLSTATE所规定的代码。7.2 存储过程存储过程 next step约定执行完statement后需要做的工作，有三种情形： 1.CONTINUE 继续执行引起错误的语句的下一条语句 2.EXIT 跳出异常处理句柄所在的语句块，继续执行END后面的语句 3.UNDO 首先撤销引起错误的语句对数据库所作的所有修改，以后的操作同EXIT。7.2 存储过程存储过程例例 9 查询学生王林所在的系，如果有多个学生叫王林或者没有叫王林的学生，则返回一串星号。CREATE PROCE

33、DURE 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 Student WHERE Sname = 王林;END;7.2 存储过程存储过程7.2.2 PL/SQL PL/SQL是Oracle的编程语言，具有支持内存

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

35、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 := 初值表达式变量名由字母、数字、下划线（_）、美圆符号($)和英镑符号(#)组成，以字母开头，长度不超过30个字符。保留字不能做变量名。除此

36、之外，PL/SQL还提供了构造数据类型记录和表以及类型转换函数。7.2 存储过程存储过程2、常量的定义类似于变量的定义：常量名称 数据类型 CONSTANT := 常量表达式常量必须要给一个值，并且其值在存在期间或常量的作用域内不能改变。如果试图修改它，PL/SQL将返回一个异常。3、赋值语句变量名 := 表达式7.2 存储过程存储过程三、控制结构三、控制结构1、条件控制语句形式一：IF条件 THEN语句序列1;ELSIF条件2 THEN语句序列2；. ELSE最后的语句序列；END IF;7.2 存储过程存储过程2、循环控制语句 PL/SQL 提供了三种不同的循环结构。每种循环结构都能够重复

37、地执行一组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 =90 AND score = 80) THENgrade := B;ELSIF (score = 70) THENgrade :=

38、C;ELSIF (score = 60) THENgrade := D;ELSEgrade := E;END IF;END;7.2 存储过程存储过程形式二：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;EXI

39、T WHEN (p_inc 10);END LOOP;dbms_output.put_line(p_sum);/*Oracle包提供的输出函数*/END;7.2 存储过程存储过程用WHILE实现DECLAREp_sum NUMBER :=0;p_incNUMBER;BEGINp_inc := 1;WHILE (p_inc = 0 THEN INSERT INTO SC VALUES(sno,cno,grade);ELSERAISE must_be_positive_grade;END IF;EXCEPTIONWHEN must_be_positive_grade THENdbms_output

40、.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); SELECT COUNT(*) INTO t1 FROM Student; SELECT COUNT(*) INTO t2 FROM SC WHERE Cno = 1234;ratio := t1 / t2 *100;I

41、NSERT INTO Course(1235, Data MIning, 1210,4);EXCEPTIONWHEN ZERO_DIVIDE THEN.END;7.2 存储过程存储过程五、存储过程和函数五、存储过程和函数 PL/SQL块主要有两种类型，即命名块和匿名块。前面介绍的都是匿名块，匿名块每次使用时都要进行编译，它不能被存储到数据库种，也不能在其它的PL/SQL块中调用。过程和函数是命名块，被编译后保存在数据库中，可以被反复调用，它们的运行速度较快。7.2 存储过程存储过程1、存储过程存储过程是利用PL/SQL编写的一组规定的操作。此过程存储在数据库中，这就是将它称为存储过程的原因。语

42、法形式为：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;BEGIN/*奖金为工资的10%*/SELECT sal * 0.10, hiredate INTO bonus,hire_date FROM emp WHERE empno = emp_id;IF

43、 (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;INSERT INTO emp_bonus(empno,bonus) VALUES(emp_id,bonus); END IF;END;7.2 存储过程存储过程2、函数函数类似于存储过程。其语法是

44、：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;BEGINSELECT sal * 0.10, hiredate INTO bonus,hire_date FROM emp WHERE empno = emp_id;7.2

45、存储过程存储过程例例 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、存储过程和函数的区别 过程和函数都是经过编译以后保存在数据库中的。 通过将参数的模式设置为OUT，则过程也可以返回值，函数也可以返回多于一个值（但不提倡）。参数的模式有IN,OUT和IN OUT，默认的参数模式是IN。IN表示参数是向过程

46、（函数）传送值的，该参数的值在过程（函数）体中不能被改变。OUT表示该参数的值是由过程（函数）返回的。IN OUT具有IN和OUT的双重意义。7.2 存储过程存储过程 过程和函数都可以有声明部分、执行部分和异常处理部分。 在使用过程和函数时，过程要作为一个语句，而函数必须出现在SQL语句中（所有可以出现数值的地方） 过程中可以包含INSERT、UPDATE和DELETE语句，而函数中不能出现这些语句。7.2 存储过程存储过程 例例 19 计算empno从1到10的员工的奖金，并把计算结果存放到关系emp_bonus中。利用存储过程来实现DECLAREp_empnoNUMBER;BEGINFOR

47、 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) INTO p_bonus FROM DUAL;INSERT INTO emp_bonus(p_empno,p_bonus);END LOOP;EN

48、D;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 Student可以正确执行。而语句SELECT sno INTO p_sno FROM Student WHERE Sname = 王

49、林就可能出现错误，触发TOO_MANY_ROWS异常，因为可能有多个人叫王林。要处理这种情况就必须使用游标，游标的概念已经在3.11节中介绍过了。7.2 存储过程存储过程7.2.3 Transact-SQL Transact-SQL是Microsoft的一种编程语言，具有所有语言所具有的要素，如：数据类型、常量和变量、函数、表达式和流程控制语句。不仅提供对SQL标准的支持，而且包含了Microsoft对SQL的一系列扩展。7.2 存储过程存储过程一、常量一、常量 常量是在程序运行中值不发生变化的量，常量的格式取决于它所表示的数据类型。根据常见的数据类型，分为字符串常量、整型常量、实型常量、货币

50、常量和日期时间常量。下面举例说明各类常量的使用方法。7.2 存储过程存储过程1、字符串常量、字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。ASCII字符串常量是用单引号括起来，由ASCII字符构成的字符串；Unicode字符串常量前面有一个N，代表国际语言。每个ASCII字符由一个字节存储，而每个Unicode字符需要两个字节的存储空间。7.2 存储过程存储过程ASCII字符串常量Have a nice day! 马翔 2563 如果单引号是字符串的一部分，则需要用两个单引号表示。Jones homeUnicode字符串常量NHave a nice day! N马翔