在应用中使用SQL-课件

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 TABLE SC; EXEC SQL SELECT * FROM S

2、tudent; 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 SQLPreprocessorHost language+Function callsC

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

4、语法定义的变量 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 查询结果为单个记录的SELECT语句 在嵌入式SQL中，查询结果为单个记录的SELECT语句使用INTO子句把查询结果传送到宿主变量，供宿主语言继续处理。该语句的一般格式为

5、： 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.查询返回的记录中，可能某些列为空值。如果INTO子句中宿主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应宿主变量后面的指示变量置为负值，不再向宿主变量赋值。 7.1 嵌入式SQL7.1.3 查询结果为单个记

6、录的SELECT语句 3.如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，DBMS将SQLSTATE的值设置为21000。7.1 嵌入式SQL例 1 查询某个学生的信息，这个学生的学号存放在宿主变量Sno中，并且将查询得到的学生信息存放到上一小节定义的变量中。EXEC SQL SELECT Sname, Ssex, Sage, SdeptINTO :Sname, :Ssex, :Sage, :SdeptFROM StudentWHERE Sno= :Sno;7.1 嵌入式SQL例 2 查询某个学生选修某门课程的成绩。由于学生的成绩可能是空值，使用了指示变量gradenullflag

7、。EXEC SQL SELECT GradeINTO :grade :gradenullfalgFROM SCWHERE Sno=:Sno AND Cno=:Cno;7.1 嵌入式SQL7.1.4 游标 SQL语言与宿主语言有不同的数据处理方式。SQL语言是面向集合的，一条SQL语句将产生或处理多条记录。而宿主语言是面向记录的，一组宿主变量一次只能存放一条记录。所以仅使用宿主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此SQL引入了游标的概念，用游标来协调这两种不同的处理方式。7.1 嵌入式SQL 游标（Cursor）是系统开设的一个数据缓冲区，存放SQL语句的执行结果。游标有一个

8、名字，可以通过游标逐一获取记录，并赋予宿主语言的宿主变量，交由宿主语言进一步处理。如图7.2。7.1 嵌入式SQL记录1记录2记录n游标图7.2游标示意图游标包括以下两个部分：1、游标结果集（cursor result set） 由定义游标的SELECT语句返回的行的集合。2、游标的位置（cursor position） 指向这个集合中某一行的指针。7.1 嵌入式SQL一、使用游标读取数据使用游标处理数据的流程如图7.3。是否使用DECLARE CURSOR语句声明游标使用OPEN语句打开游标使用FETCH INTO语句从游标中提取数据使用CLOSE语句关闭游标最后一个元组？图7.3使用游标的

9、一般过程7.1 嵌入式SQL1、声明游标格式：EXEC SQL DECLARE cursor-name INSENSITIVE SCROLL CURSORFOR SELECT statement FOR READ ONLY SELECT语句定义了游标结果集，游标名用于对游标的各种操作。使用INSENSITIVE通知DBMS在游标存续期间，不允许其它的事务修改游标结果集中的数据。7.1 嵌入式SQL 如果游标不修改数据库中的数据，使用FOR READ ONLY告知DBMS，DBMS将允许这样的游标和INSENSITIVE类型的游标并发执行。 默认情况下，游标采用顺序处理的方式，依次处理结果集中的

10、记录。根据需要，也可以将游标定义为滚动（SCROLL）游标。对滚动游标，SQL提供了若干命令将游标移动到希望的位置。7.1 嵌入式SQLnext 把游标移动到当前游标所指下一个记录prior 把游标移动到当前游标所指上一个记录first 把游标移动到第一个记录last 把游标移动到最后一个记录7.1 嵌入式SQLabsolute n 如果n是一个正整数，把游标移动到从前往后计数的第n个记录；如果n是一个负整数，把游标移动到从后数的第n个记录。relative n 如果n是一个正整数，把游标移动相对于当前游标所指记录之后的第n个记录；如果n是一个负整数，把游标移动相对于当前游标所指记录之前的第n

11、个记录。7.1 嵌入式SQL例 3 声明存取计算机系全体学生的游标。EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 计算机FOR READ ONLY;7.1 嵌入式SQL2、打开游标格式：EXEC SQL OPEN ; 打开游标后，DBMS执行与游标相关联的SELECT语句，并把查询结果存放到游标中，游标指向第1个记录。例如，EXEC SQL OPEN dept_computer;图7.4 游标dept_computer的内容2000012,王林,男,19,计算机游标2000014,葛波,女,1

12、8,计算机7.1 嵌入式SQL3、存取游标 如果游标不是滚动游标，使用FETCH语句读取当前游标所指的记录到宿主变量中，然后，游标自动移向下一个记录：EXEC SQL FETCH FROM cursor_name INTO variable-list; 7.1 嵌入式SQL 如果游标是滚动游标，先将游标移动到所指定的记录，然后将游标所指的记录存放到宿主变量中： EXEC SQL FETCH NEXT | PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE nFROM cursor_nameINTO variable-list;7.1 嵌入式SQL4、关闭游标 游标使用完毕

13、后，要释放其所占的系统资源。使用下面的语句：EXEC SQL CLOSE cursor_name; 游标指向了结果集最后一个记录之后时，SQLSTATE的值被设置为02000，可以用这个条件作为循环的结束条件。7.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

14、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,:Ssex,:Sage,:Sdept;while(strcmp(SQLSTATE, 02000) != 0)/输出 EXEC SQL FETCH dept_computer INTO :Sno, :Sname,:Ssex,:Sage,:Sd

15、ept;-关闭游标EXEC SQL CLOSE dept_computer;7.1 嵌入式SQL二、使用游标修改数据 上面介绍了通过游标把数据库的批量数据传送到宿主变量中做进一步处理。游标的另一个作用是可以通过游标修改数据库中的数据。 使用游标修改数据要注意两件事： 1.声明游标时没有加FOR READ ONLY关键字； 2.一般不要修改基于多表的游标。7.1 嵌入式SQL1、UPDATE语句格式：UPDATE table-name SET column- name = expressionWHERE CURRENT OF cursor_name功能：修改数据库中与当前游标所指向的记录相对应的

16、元组的列值。7.1 嵌入式SQL2、DELETED语句DELETE FROM table-nameWHERE CURRENT OF cursor_name功能：删除数据库中与当前游标所指向的记录相对应的元组。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= 计算机

17、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 SET Sage = Sage + 1WHERE CURRENT OF dept_computer;FETCH dept_computer;-读下一条记录;-关闭游标EXEC SQL CLOSE dept_computer;7.1 嵌入式SQL7.1.5

18、动态SQL简介动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式： 1.语句可变：允许用户在程序运行时输入完整的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 嵌

19、入式SQL二、EXECUTE EXECUTE执行由PREPARE准备的SQL语句：EXECUTE prepared_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 s

20、c 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, 00000) = = 0) scanf(%s , sno); scanf(%s , cno); scabf(%d , grade); EXEC SQL EXECUTE prep_stat USING :sno, :cno, :grade;7.1 嵌入式SQL三、EXECUTE IMM

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

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

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

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

25、N 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的分支语句的功能同C语言这样的高级程序设计语言，但在语法上和逻辑条件

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

27、st(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 SC WHERE Cno = vcno;OPEN cur_sccur_loop: LOOPFETCH cur_sc INTO

28、 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),OUTvsnamechar(8),OUTvsdeptchar(20)DECLAREmax_gradeint;DECLAREvgradein

29、t;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 vsname, vsdeptFROM Student WHERE Sno = vsno;LEAVE cur_loop;END IF;END FOR

30、;END;7.2 存储过程五、异常处理 DBMS在执行SQL语句时，如果遇到错误则将变量SQLSTATE的值设置为不等于00000 的长度为5的字符串。 SQL/PSM提供了异常处理句柄(exception handler)功能，异常处理句柄被封装在一个由BEGIN和END界定的语句块中，当语句块中的某个语句发生了异常处理句柄所捕获的错误，则执行异常处理句柄预先定义的代码。7.2 存储过程异常处理句柄的格式为：DECLARE HANDLER FOR condition list是用逗号分隔开的一组条件，条件可以用DECLARE语句定义，或直接用SQLSTATE所规定的代码。7.2 存储过程 n

31、ext step约定执行完statement后需要做的工作，有三种情形： 1.CONTINUE 继续执行引起错误的语句的下一条语句 2.EXIT 跳出异常处理句柄所在的语句块，继续执行END后面的语句 3.UNDO 首先撤销引起错误的语句对数据库所作的所有修改，以后的操作同EXIT。7.2 存储过程例 9 查询学生王林所在的系，如果有多个学生叫王林或者没有叫王林的学生，则返回一串星号。CREATE PROCEDURE course_list(OUT vsdept ar(20)DECLARE Not_Found CONDITION FOR SQLSTATE 02000;DECLARE Too_M

32、any 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的编程语言，具有支持内存变量、条件结构和循环结构、过程和函数以及执行SQL语句的能力。本节简单介绍PL/SQL的有关概念。一、PL/SQL的块结构 PL/SQL程序的基本结构是块。所有的PL/SQL程序都是由块组成的，这

33、些块之间还可以互相嵌套，每个块完成一个逻辑操作。基本块由定义部分、执行部分和异常处理部分组成。除执行部分是必须的以外，其它的部分都是可选的。7.2 存储过程例 10 向关系Student中插入10个学生,学生编号从1001到1010。DECLAREsnoNUMBER;/*定义部分*/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 TH

34、EN/*处理异常情况others*/dbms_output.put_line(Error);END;7.2 存储过程二、变量和常量的定义1、PL/SQL中定义变量的语法形式是：变量名 数据类型 NOT NULL := 初值表达式变量名由字母、数字、下划线（_）、美圆符号($)和英镑符号(#)组成，以字母开头，长度不超过30个字符。保留字不能做变量名。除此之外，PL/SQL还提供了构造数据类型记录和表以及类型转换函数。7.2 存储过程2、常量的定义类似于变量的定义：常量名称 数据类型 CONSTANT := 常量表达式常量必须要给一个值，并且其值在存在期间或常量的作用域内不能改变。如果试图修改它

35、，PL/SQL将返回一个异常。3、赋值语句变量名 := 表达式7.2 存储过程三、控制结构1、条件控制语句形式一：IF条件 THEN语句序列1;ELSIF条件2 THEN语句序列2；. ELSE最后的语句序列；END IF;7.2 存储过程2、循环控制语句 PL/SQL 提供了三种不同的循环结构。每种循环结构都能够重复地执行一组PL/SQL语句，并可根据条件终止循环的执行。形式一：LOOP语句序列;END LOOP;跳出循环语句：形式一：EXIT形式二：EXIT WHEN条件7.2 存储过程2、循环控制语句 例 11 如果选修课程1156的学生人数不超过30人时则可以加入选课的学生。DECLA

36、REp_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 := C;ELSIF (score = 60) THENgrade := D;ELSEgrade := E;END IF;END;7.2 存储过程形式二：WHILE 条件 LOOP语句序列;END LOOP;形式三：FOR 循环变量 IN REVERSE 下限.上限 LOOP语句序列；END L

37、OOP;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包提供的输出函数*/END;7.2 存储过程用WHILE实现DECLAREp_sumNUMBER :=0;p_incNUMBER;BEGINp_inc := 1;WHILE (p_i

38、nc = 0 THEN INSERT INTO SC 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,

39、 Data Structure, 1210,4); 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块主要有两种类型，即命名块和匿名块。前面介绍的都是匿名块，匿名块每次使用时都要进行编译，它不能被存储到数据库种，也不能在

40、其它的PL/SQL块中调用。过程和函数是命名块，被编译后保存在数据库中，可以被反复调用，它们的运行速度较快。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;/*进某单位工作

41、的日期*/bonusREAL;BEGIN/*奖金为工资的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;奖金增加50

42、0元*/END IF;INSERT 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_dateDAT

43、E;bonusREAL;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、存储过程和函数的区别 过程和函数都是经过编译以后保存在数

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

45、19 计算empno从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_emp

46、no) INTO 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 Stu

47、dent可以正确执行。而语句SELECT sno INTO p_sno FROM Student WHERE Sname = 王林就可能出现错误，触发TOO_MANY_ROWS异常，因为可能有多个人叫王林。要处理这种情况就必须使用游标，游标的概念已经在3.11节中介绍过了。7.2 存储过程7.2.3 Transact-SQL Transact-SQL是Microsoft的一种编程语言，具有所有语言所具有的要素，如：数据类型、常量和变量、函数、表达式和流程控制语句。不仅提供对SQL标准的支持，而且包含了Microsoft对SQL的一系列扩展。7.2 存储过程一、常量 常量是在程序运行中值不发生变

48、化的量，常量的格式取决于它所表示的数据类型。根据常见的数据类型，分为字符串常量、整型常量、实型常量、货币常量和日期时间常量。下面举例说明各类常量的使用方法。7.2 存储过程1、字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。ASCII字符串常量是用单引号括起来，由ASCII字符构成的字符串；Unicode字符串常量前面有一个N，代表国际语言。每个ASCII字符由一个字节存储，而每个Unicode字符需要两个字节的存储空间。7.2 存储过程ASCII字符串常量Have a nice day! 马翔 2563 如果单引号是字符串的一部分，则需要用两个单引号表示。Jones

49、 homeUnicode字符串常量NHave a nice day! N马翔7.2 存储过程2、整型常量 整型常量由数字0-9以及+/-号组成的有意义的串。如：123、-9811、+893、实型常量 实型常量有定点和浮点两种表示形式。定点实型常量 由数字0-9、小数点和正负号组成有意义的串。如：1.0、3467.584。浮点实型常量 由数字0-9、小数点、正负号和字母E组成有意义的串。如：0.68E+3、1E10、-23E2。7.2 存储过程4、货币常量 货币常量是以$符号为前缀的整型或实型常量，用来表示货币值，精度为4位小数。如$100、-$52、$16.37。5、日期时间常量 日期常量是用

50、单引号括起来的有效的日期和时间组成的字符串。常见的日期格式September 1,2007、 2007年9月1号、 2007-09-01、 09/01/2007、 20070901常见的时间格式 14:12:00、 02:12PM7.2 存储过程二、变量 变量就是在程序运行中值会发生变化的量。变量可用于存放输入的值以及保存计算结果等。SQL Server有两类变量： 1.全局变量。 2.局部变量。7.2 存储过程1、全局变量 全局变量是SQL Server管理的变量，用户不能建立全局变量，可以查看全局变量的值，但不能改变全局变量的值。 全局变量分为两类，一类反映SQL Server系统的全局变

51、量，另一类反映与一个连接有关的全局变量。 全局变量的特征是变量名前2个符号必须是。SQL Server提供了大约30多个全局变量。7.2 存储过程2、局部变量 局部变量是在一定范围内有意义的变量，由用户建立和使用。局部变量的作用范围一般为批处理、存储过程、触发器。 局部变量的命名要求第一个字符必须是，由字母、数字、下划线等字符组成，不能与SQL Server的保留字、全局变量、存储过程、表等其它数据库对象重名。 局部变量要用DECLARE语句声明。 可以用SET或SELECT语句给局部变量赋值。 局部变量可以出现在SQL语句中。7.2 存储过程三、运算符 运算符完成运算功能。SQL Serve

52、r提供了丰富的运算符，绝大部分的含义同一般的编程语言，这里只是将它们罗列出来，以便于对照学习。1、算术运算符 算术运算符用于对两个数字型的量进行数学运算。算术运算符有：+（加）、-（减）、*（乘）、/（除）、%（取余）。其中，+和-还可以作为一元运算符，也可以对datatime和smalldatatime类型的数据进行运算。7.2 存储过程三、运算符2、比较运算符 比较运算符用于比较两个表达式的值之间的关系，结果为布尔值，分为TRUE（真）、FALSE（假）、UNKNOWN（未知）。 比较运算符有：=（等于）、（小于）、（大于）、=（大于等于）、（不等于，另外一个写法是！=）、！（不大于）、！

53、、=、=、 、!=、!、!位运算符：、&、|逻辑运算符：NOT、AND、OR7.2 存储过程四、函数 在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQL Server的标准命令。SQL Server支持两种函数类型：内置函数和用户定义函数。其内置函数十分丰富，具体的请参考其它书籍。7.2 存储过程五、流程控制语句语句功能BEGINEND定义语句块。IFELSE条件选择语句，条件成立执行IF后语句（第一个分支），否则执行ELSE后语句。CASE语句分支处理语句，表达式可根据条件返回不同值。WHILE循环语句，重复执行命令行或程序块。WAITFOR设置语句执行的延迟时间。B

54、REAK循环跳出语句。CONTINUE重新启动循环语句。跳过CONTINUE后语句，回到WHILE循环的第一行命令。GOTO无条件转移语句。RETURN无条件返回语句。表7.2 流程控制语句及功能7.2 存储过程1、语句块 BEGINEND语句能够将多个Transact-SQL语句组合成一个语句块，将其视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGINEND语句。格式：BEGIN 语句1 语句2 语句n END7.2 存储过程2、IF ELSE语句 IFELSE语句是条件判断语句，用来判断当某一条件成立时执行某段程序，条件不成立时

55、执行另一段程序。允许嵌套。格式：IF ELSE 条件表达式 7.2 存储过程例 21 从SC数据表中求出学号为2007012同学的平均成绩，如果此平均成绩大于或等于60分，则输出pass信息。if (SELECT AVG(Grade) FROM SC WHERE Sno=2007012)=60)beginprint passend7.2 存储过程例 22 判断数值大小，打印运算结果。declare q int,r int,s intselect q = 4,r = 5,s = 6 -为三个变量赋值if q rprint qrelse if r sprint rs elseprint sr7.2

56、 存储过程3、CASE语句 CASE语句可以计算多个条件式，并返回其中一个符合条件的表达式的结果。按照使用形式的不同，可以分为简单CASE语句和搜索CASE语句。7.2 存储过程1.简单CASE语句格式： CASE WHEN THEN WHEN THEN ELSE END7.2 存储过程2.搜索CASE语句格式：CASEWHEN THEN WHEN THEN ELSE END7.2 存储过程例 23 输出选修1156号课程的成绩（分为优、良、中、及格、不及格五个等级）。select Sno, 成绩=casewhen Grade=60 and Grade=70 and Grade=80 and

57、Grade=90 then 优endfrom SCwhere Cno=11567.2 存储过程4、WHILE语句 WHILE语句通过布尔表达式设置重复执行SQL语句或语句块的循环条件。WHILE命令在设定的条件成立时会重复执行SQL语句或程序块。可以使用BREAK和CONTINUE语句在循环内部控制WHILE循环中语句的执行。格式：WHILE BEGIN BREAK CONTINUE END7.2 存储过程例 24 计算从1加到100的值。declare s int,i intset i=0set s=0while i=100beginset s=s+iset i=i+1endprint 1+

58、2+100= + cast(s as char(25)7.2 存储过程5、RETURN语句 RETURN语句用于在任何时候从过程、批处理或语句块中结束当前程序、无条件退出，而不执行位于RETURN之后的语句，返回到上一个调用它的程序，在括号内可指定一个返回值。格式：RETURN整数值7.2 存储过程6、WAITFOR语句 WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。格式：WAITFOR DELAY | TIME | ERROREXIT | PROCESSEXIT | MIRROREXIT7.2 存储过程例 25 等待1

59、 小时2 分零3 秒后才执行SELECT 语句。waitfor delay 01:02:03select * from SC等到11点12分才执行select语句waitfor time 11:12:00select * from SC7.2 存储过程7、批处理 一个批处理是一组语句，以GO作为结束标志。SQL Server将批处理作为一个工作单位，批处理中的所有语句作为一个整体进行词法分析、语法分析，进行编译和执行。在分析阶段，若某一条语句有错误，则不会执行批处理中的任何语句。在执行批处理时，某条语句在执行中出错，不会影响到其它语句的执行。SQL Server规定有些语句必须是批处理中的唯一

60、一条语句或者是第一条语句，如，一条DDL语句必须作为一个批处理。7.2 存储过程7、批处理 例 26 下面的批处理由两条语句组成select *from studentinsert student(Sno,Sname)values(2007002,王亮)go7.2 存储过程7、批处理 例 27 局部变量的作用范围是批处理为变量studentName赋值，然后在SELECT语句中引用，要求赋值和引用在一个批处理中。DECLARE studentName varchar(12) -声明变量SET studentName = 马 + 翔 -为变量赋值SELECT *FROM StudentWHERE