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

《在应用中使用SQL-课件.ppt》由会员分享，可在线阅读，更多相关《在应用中使用SQL-课件.ppt（242页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

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

2、QLSELECT*FROMStudent;EXECSQLGRANTUPDATEONStudentTOUser1;7.1 嵌入式嵌入式SQL7.1.1嵌入式嵌入式SQL的一般形式的一般形式为了不修改宿主语言的编译器，DBMS提供一个预编译器，预编译器识别嵌入式SQL语句，将它们换成SQL函数库中的函数调用，将最初的宿主语言和嵌入式SQL的混合体转换成纯宿主语言的代码，然后由编译器进行通常的编译和连接操作，最终生成可执行代码，完成过程控制和数据库操作，具体过程如图7.1所示。7.1 嵌入式嵌入式SQLHostlanguage+EmbeddedSQLPreprocessorHostlanguage+

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

4、DECLARESECTION按照宿主语言的语法定义的变量EXECSQLENDDECLARESECTION7.1 嵌入式嵌入式SQL7.1.2嵌入式SQL语句与宿主语言之间的通信例如：EXECSQLBEGINDECLARESECTIONcharSno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;EXECSQLENDDECLARESECTION7.1 嵌入式嵌入式SQL7.1.3 查询结果为单个记录的查询结果为单个记录的SELECT语句语句在嵌入式SQL中，查询结果为单个记录的SELECT语句使用INTO子句把查询结果传送到宿

5、主变量，供宿主语言继续处理。该语句的一般格式为：7.1 嵌入式嵌入式SQLEXECSQLSELECTALL|DISTINCT,INTO,FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC;7.1 嵌入式嵌入式SQL7.1.3 查询结果为单个记录的查询结果为单个记录的SELECT语句语句1.INTO子句、WHERE子句的条件表达式、HAVING短语的的条件表达式中均可以使用宿主变量。2.查询返回的记录中，可能某些列为空值。如果INTO子句中宿主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应宿主变量后面的指示变量置为负值，不再向宿主变量赋值。7.

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

7、的成绩可能是空值，使用了指示变量gradenullflag。EXECSQLSELECTGradeINTO:grade:gradenullfalgFROMSCWHERESno=:SnoANDCno=:Cno;7.1 嵌入式嵌入式SQL7.1.4 游标游标SQL语言与宿主语言有不同的数据处理方式。SQL语言是面向集合的，一条SQL语句将产生或处理多条记录。而宿主语言是面向记录的，一组宿主变量一次只能存放一条记录。所以仅使用宿主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此SQL引入了游标的概念，用游标来协调这两种不同的处理方式。7.1 嵌入式嵌入式SQL游标（Cursor）是系统开设的

8、一个数据缓冲区，存放SQL语句的执行结果。游标有一个名字，可以通过游标逐一获取记录，并赋予宿主语言的宿主变量，交由宿主语言进一步处理。如图7.2。7.1 嵌入式嵌入式SQL记录1记录2记录n游标图7.2游标示意图游标包括以下两个部分：1、游标结果集（cursorresultset）由定义游标的SELECT语句返回的行的集合。2、游标的位置（cursorposition）指向这个集合中某一行的指针。7.1 嵌入式嵌入式SQL一、使用游标读取数据一、使用游标读取数据使用游标处理数据的流程如图7.3。是否使用DECLARECURSOR语句声明游标使用OPEN语句打开游标使用FETCHINTO语句从游

9、标中提取数据使用CLOSE语句关闭游标最后一个元组？图7.3使用游标的一般过程7.1 嵌入式嵌入式SQL1、声明游标、声明游标格式：EXECSQLDECLAREcursor-nameINSENSITIVESCROLLCURSORFORSELECT statementFORREADONLYSELECT语句定义了游标结果集，游标名用于对游标的各种操作。使用INSENSITIVE通知DBMS在游标存续期间，不允许其它的事务修改游标结果集中的数据。7.1 嵌入式嵌入式SQL如果游标不修改数据库中的数据，使用FORREADONLY告知DBMS，DBMS将允许这样的游标和INSENSITIVE类型的游标并

10、发执行。默认情况下，游标采用顺序处理的方式，依次处理结果集中的记录。根据需要，也可以将游标定义为滚动（SCROLL）游标。对滚动游标，SQL提供了若干命令将游标移动到希望的位置。7.1 嵌入式嵌入式SQLnext把游标移动到当前游标所指下一个记录prior把游标移动到当前游标所指上一个记录first把游标移动到第一个记录last把游标移动到最后一个记录7.1 嵌入式嵌入式SQLabsoluten如果n是一个正整数，把游标移动到从前往后计数的第n个记录；如果n是一个负整数，把游标移动到从后数的第n个记录。relativen 如果n是一个正整数，把游标移动相对于当前游标所指记录之后的第n个记录；如

11、果n是一个负整数，把游标移动相对于当前游标所指记录之前的第n个记录。7.1 嵌入式嵌入式SQL例例 3 声明存取计算机系全体学生的游标。EXECSQLDECLAREdept_computerCURSORFORSELECT*FROMStudentWHERESdept=计算机FORREADONLY;7.1 嵌入式嵌入式SQL2、打开游标、打开游标格式：EXECSQLOPEN;打开游标后，DBMS执行与游标相关联的SELECT语句，并把查询结果存放到游标中，游标指向第1个记录。例如，EXECSQLOPENdept_computer;图7.4游标dept_computer的内容2000012,王林,男

12、,19,计算机游标2000014,葛波,女,18,计算机7.1 嵌入式嵌入式SQL3、存取游标、存取游标如果游标不是滚动游标，使用FETCH语句读取当前游标所指的记录到宿主变量中，然后，游标自动移向下一个记录：EXECSQLFETCHFROMcursor_nameINTOvariable-list;7.1 嵌入式嵌入式SQL如果游标是滚动游标，先将游标移动到所指定的记录，然后将游标所指的记录存放到宿主变量中：EXECSQLFETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEnFROMcursor_nameINTOvariable-list;7.1 嵌入式嵌

13、入式SQL4、关闭游标、关闭游标游标使用完毕后，要释放其所占的系统资源。使用下面的语句：EXECSQLCLOSEcursor_name;游标指向了结果集最后一个记录之后时，SQLSTATE的值被设置为02000，可以用这个条件作为循环的结束条件。7.1 嵌入式嵌入式SQL例例 4 通过游标dept_computer读取每个学生的信息，并显示。EXECSQLBEGINDECLARESECTIONcharSno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;7.1 嵌入式嵌入式SQLEXECSQLENDDECLARESECTIO

14、N-声明游标EXECSQLDECLAREdept_computerCURSORFORSELECT*FROMStudentWHERESdept=计算机FORREADONLY;-打开游标EXECSQLOPENdept_computer;7.1 嵌入式嵌入式SQL-读取第1条记录EXECSQLFETCHdept_computerINTO:Sno,:Sname,:Ssex,:Sage,:Sdept;while(strcmp(SQLSTATE,02000)!=0)/输出EXECSQLFETCHdept_computerINTO:Sno,:Sname,:Ssex,:Sage,:Sdept;-关闭游标EXE

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

16、嵌入式SQL2、DELETED语句DELETEFROMtable-nameWHERECURRENTOFcursor_name功能：删除数据库中与当前游标所指向的记录相对应的元组。7.1 嵌入式嵌入式SQL例5通过游标dept_computer给每个学生年龄增加1岁EXECSQLBEGINDECLARESECTIONcharSQLSTATE6;EXECSQLENDDECLARESECTION-声明游标EXECSQLDECLAREdept_computerCURSORFORSELECT*FROMStudentWHERESdept=计算机FORUPDATEOFSage-只允许修改列Sage的值;7.

17、1 嵌入式嵌入式SQL-打开游标EXECSQLOPENdept_computer;-读取第1条记录EXECSQLFETCHdept_computer;while(strcmp(SQLSTATE,02000)!=0)EXECSQLUPDATEStudentSETSage=Sage+1WHERECURRENTOFdept_computer;FETCHdept_computer;-读下一条记录;-关闭游标EXECSQLCLOSEdept_computer;7.1 嵌入式嵌入式SQL7.1.5 动态动态SQL简介简介动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式：1.语句可变

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

19、repared_stmt_nameUSING:host-variabel,.;prepared_stmt_name是由某个PREPARE准备的SQL语句，USING后面的宿主变量用于替换SQL语句的？参数，有几个?参数就必须有几个宿主变量，数据类型必须兼容，并且按照位置对应的原则进行替换。7.1 嵌入式嵌入式SQL二、二、EXECUTE例例 6生成一个向SC表插入任意元组的SQL语句EXECSQLBEGINDECLARESECTION;charprep=INSERTINTOscVALUES(?,?,?);charsno8;charcno5;shortgrade;EXECSQLENDDECLAR

20、ESECTION;7.1 嵌入式嵌入式SQL二、二、EXECUTEEXECSQLPREPAREprep_statFROM:prep;while(strcmp(SQLSTATE,00000)=0)scanf(%s,sno);scanf(%s,cno);scabf(%d,grade);EXECSQLEXECUTEprep_statUSING:sno,:cno,:grade;7.1 嵌入式嵌入式SQL三、三、EXECUTE IMMEDIATE EXECUTEIMMEDIATE语句结合了PREPARE和EXECUTE的功能，准备一个SQL语句并且立即执行它。因为准备一个SQL语句需要和DBMS通信，开

21、销比较大，所以，采用PREPARE和EXECUTE方式适合于准备的语句要多次执行的方式，而EXECUTEIMMEDIATE适用于只执行一次的情形。语句格式为：EXECUTEIMMEDIATE:host-variable7.2 存储过程存储过程存储过程是SQL语句和可选控制流语句的预编译集合。它以一个名称存储并作为一个单元处理。存储过程存储在数据库内，由应用程序调用执行。存储过程包含流程控制以及对数据库的查询，可接受参数、输出参数、返回单个或多个结果集以及执行状态，而且允许定义变量、条件执行、循环等编程功能。7.2 存储过程存储过程存储过程有以下特点：1.确保数据访问和操作的一致性，提高应用程序

22、的可维护性；2.提高系统的执行效率；3.提供一种安全机制；4.减少了网络的流量负载；5.若要改变业务规则或策略，只需改变存储过程和参数，不必修改应用程序。7.2 存储过程存储过程7.2.1SQL/PSM编写存储过程以及后面介绍的触发器需要一种通用的程序设计语言。SQL/PSM（persistentstoredmodules）标准制定了一个程序设计语言，这个语言可以被DBMS解释执行。SQL/PSM提供了通用程序设计语言的定义变量、流程控制和存储过程定义以及调用语句。在PSM标准出现之前，一些DBMS厂商就提供了自己的编程语言。7.2 存储过程存储过程一、存储过程和函数的定义和调用一、存储过程和

23、函数的定义和调用SQL/PSM中的存储过程和函数与PASCAL语言中的过程和函数十分相似，但是语法比较复杂，下面给出一个简化版本。CREATEPROCEDURE(IN|OUT,.)routine-body;CREATEFUCTION(variable-namedatatype,.)RETURNdatatyperoutine-body;7.2 存储过程存储过程在定义存储过程和函数时，首先要赋予一个在数据库范围内唯一的名字，然后给出参数。对于存储过程，要进一步指出每个参数是输入参数还是输出参数。对于函数，在函数体内要使用RETURN语句返回一个值。过程体和函数体使用SQL/PSM提供的过程控制语句

24、和SQL语句编写。7.2 存储过程存储过程SQL/PSM定义的函数可以出现在SQL语句中可以出现常数的地方。存储过程序要使用CALL语句调用。在过程体或函数体内直接使用CALL(argument list)，在嵌入式SQL中要在CALL语句之前加上EXECSQL。例如，下面的过程完成一个学生的转系任务，学生的学号和要转到的系名称作为过程的输入参数。7.2 存储过程存储过程CREATEPROCEDUREtransform-dept(INsnochar(7),INdeptchar(20)UPDATEstudent；SETSdept=dept；WHERESno=sno;7.2 存储过程存储过程二、变

25、量的声明和赋值二、变量的声明和赋值DECLARE语句为一个变量指定变量名称和数据类型，分配存储空间。变量的作用范围是存储过程，DECLARE语句应该出现在其它可执行语句之前。DECLARE;SET语句为变量赋值，先计算出等号右边的表达式的值，然后把得到的值赋于变量，表达式的构成与其它程序设计语言相同。SET=;7.2 存储过程存储过程三、分支语句三、分支语句SQL/PSM的分支语句的功能同C语言这样的高级程序设计语言，但在语法上和逻辑条件的构成上略有不同。IFTHENELSEIFTHENELSIFELSEENDIF7.2 存储过程存储过程四、循环语句四、循环语句SQL/PSM的循环语句多用于处

26、理游标，有多种形式，最基本的语句形式为：LOOPENDLOOP;一般在LOOP前加上一个语句标号，语句标号的形式是一个名字后面紧跟一个冒号（:），一般在循环体中要对循环条件进行测试，当条件满足时使用下面的语句结束循环，开始执行ENDLOOP后面的语句。LEAVE;7.2 存储过程存储过程四、循环语句四、循环语句例例 7给出选修某门课程的成绩最好的学生的姓名和所在系，如果有多个这样的学生，则任意给出一个。CREATEPROCEDUREcourse_list(INvcnochar(4),OUT vsnamechar(8),OUT vsdept char(2)DECLAREmax_grade int

27、;DECLAREvgradeint;DECLAREvsnochar(7);7.2 存储过程存储过程四、循环语句四、循环语句DECLAREcur_scCURSORFORSELECTSno,GradeFROMSCWHERECno=vcno;BEGINSELECTmax(grade)INTOmax_gradeFROMSCWHERECno=vcno;OPENcur_sccur_loop:LOOPFETCHcur_scINTOvsno,vgrade;IFvgrade=max_gradeTHENSELECTSname,SdeptINTOvsname,vsdeptFROMStudentWHERESno=vs

28、no;LEAVEcur_loop;ENDIF;ENDLOOP;CLOSEcur_sc;END;7.2 存储过程存储过程四、循环语句例8使用FOR语句改写上例。CREATEPROCEDUREcourse_list(INvcnochar(4),OUT vsnamechar(8),OUT vsdeptchar(20)DECLAREmax_gradeint;DECLAREvgradeint;DECLAREvsnochar(7);7.2 存储过程存储过程四、循环语句BEGINSELECTmax(grade)INTOmax_gradeFROMSCWHERECno=vcno;FORcur_loopAScur

29、_scCURSORFORSELECTSno,GradeFROMSCWHERECno=vcnoDOIFvgrade=max_gradeTHENSELECTSname,SdeptINTOvsname,vsdeptFROMStudentWHERESno=vsno;LEAVEcur_loop;ENDIF;ENDFOR;END;7.2 存储过程存储过程五、异常处理五、异常处理DBMS在执行SQL语句时，如果遇到错误则将变量SQLSTATE的值设置为不等于00000的长度为5的字符串。SQL/PSM提供了异常处理句柄(exceptionhandler)功能，异常处理句柄被封装在一个由BEGIN和END界定

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

31、IT。7.2 存储过程存储过程例例 9查询学生王林所在的系，如果有多个学生叫王林或者没有叫王林的学生，则返回一串星号。CREATEPROCEDUREcourse_list(OUTvsdeptar(20)DECLARENot_FoundCONDITIONFORSQLSTATE02000;DECLAREToo_ManyCONDITIONFORSQLSTATE21000;BEGINDECLAREEXITHANDLERFORNot_Found,Too_ManySETvsdept=*;SELECTSdeptINTOvsdeptFROMStudentWHERESname=王林;END;7.2 存储过程存储

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

33、no:=1001;LOOPINSERTINTOStudent(Sno)VALUES(to_char(:sno,9999);sno:=sno+1;EXITWHENsno1010;ENDLOOP;EXCEPTION/*异常处理*/WHENothersTHEN/*处理异常情况others*/dbms_output.put_line(Error);END;7.2 存储过程存储过程二、变量和常量的定义二、变量和常量的定义1、PL/SQL中定义变量的语法形式是：变量名数据类型NOTNULL:=初值表达式变量名由字母、数字、下划线（_）、美圆符号($)和英镑符号(#)组成，以字母开头，长度不超过30个字符。

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

35、复地执行一组PL/SQL语句，并可根据条件终止循环的执行。形式一：LOOP语句序列;ENDLOOP;跳出循环语句：形式一：EXIT形式二：EXITWHEN条件7.2 存储过程存储过程2、循环控制语句例例 11如果选修课程1156的学生人数不超过30人时则可以加入选课的学生。DECLAREp_totalNUMBER;BEGINSELECTCOUNT(*)INTOp_totalFROMSCWHERECno=1156;IF(p_total=90ANDscore=80)THENgrade:=B;ELSIF(score=70)THENgrade:=C;ELSIF(score=60)THENgrade:=

36、D;ELSEgrade:=E;ENDIF;END;7.2 存储过程存储过程形式二：WHILE条件LOOP语句序列;ENDLOOP;形式三：FOR循环变量INREVERSE下限.上限LOOP语句序列；ENDLOOP;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;EXITWHEN(p_inc10);ENDLOOP;dbms_output.put_line(p_sum);/*Orac

37、le包提供的输出函数*/END;7.2 存储过程存储过程用WHILE实现DECLAREp_sum NUMBER:=0;p_incNUMBER;BEGINp_inc:=1;WHILE(p_inc=0THENINSERTINTOSCVALUES(sno,cno,grade);ELSERAISEmust_be_positive_grade;ENDIF;EXCEPTIONWHENmust_be_positive_gradeTHENdbms_output.put_line(GradeMustBePositive|grade);END;7.2 存储过程存储过程例例 16 异常处理后控制不再返回异常语句所在

38、的基本块。DECLAREratioNUMBER(3,1);t1NUMBER;t2NUMBER;BEGININSERTINTOCourse(1234,DataStructure,1210,4);SELECTCOUNT(*)INTOt1FROMStudent;SELECTCOUNT(*)INTOt2FROMSCWHERECno=1234;ratio:=t1/t2*100;INSERTINTOCourse(1235,DataMIning,1210,4);EXCEPTIONWHENZERO_DIVIDETHEN.END;7.2 存储过程存储过程五、存储过程和函数五、存储过程和函数PL/SQL块主要有两

39、种类型，即命名块和匿名块。前面介绍的都是匿名块，匿名块每次使用时都要进行编译，它不能被存储到数据库种，也不能在其它的PL/SQL块中调用。过程和函数是命名块，被编译后保存在数据库中，可以被反复调用，它们的运行速度较快。7.2 存储过程存储过程1、存储过程存储过程是利用PL/SQL编写的一组规定的操作。此过程存储在数据库中，这就是将它称为存储过程的原因。语法形式为：CREATEORREPLACEPROCEDURE过程名（参数1,参数2,.）AS|IS变量声明;BEGIN语句组；END;7.2 存储过程存储过程例例 17 计算某个员工的应发奖金CREATEORREPLACEPROCEDUREcal

40、c_bonus(emp_idININTEGER)IShire_dateDATE;/*进某单位工作的日期*/bonusREAL;BEGIN/*奖金为工资的10%*/SELECTsal*0.10,hiredateINTObonus,hire_dateFROMempWHEREempno=emp_id;IF(bonusISNULL)THENINSERTINTOemp_bonus(empno,bonus)VALUES(emp_id,NULL);7.2 存储过程存储过程例例 17 计算某个员工的应发奖金ELSEIFMONTHS_BETWEEN(SYSDATE,hire_date)60THEN/工作时间超过

41、5年bonus:=bonus+500;奖金增加500元*/ENDIF;INSERTINTOemp_bonus(empno,bonus)VALUES(emp_id,bonus);ENDIF;END;7.2 存储过程存储过程2、函数函数类似于存储过程。其语法是：CREATEORREPLACEFUNCTION函数名（参数1,参数2,.）RETURNDataTypeAS|IS变量声明;BEGIN语句组；END;7.2 存储过程存储过程例例 18 计算某个员工的应发奖金CREATEORREPLACEFUNCTIONcalc_bonus_func(emp_idINTEGER)RETURNREALIShir

42、e_dateDATE;bonusREAL;BEGINSELECTsal*0.10,hiredateINTObonus,hire_dateFROMempWHERE empno=emp_id;7.2 存储过程存储过程例例 18 计算某个员工的应发奖金IF(bonusISNULL)THENRETURN(-1);ELSEIFMONTHS_BETWEEN(SYSDATE,hire_date)60THENbonus:=bonus+500;ENDIF;RETURN(bonus);ENDIF;END;7.2 存储过程存储过程3、存储过程和函数的区别过程和函数都是经过编译以后保存在数据库中的。通过将参数的模式设

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

44、到10的员工的奖金，并把计算结果存放到关系emp_bonus中。利用存储过程来实现DECLAREp_empnoNUMBER;BEGINFORp_empnoIN1.10LOOPcalc_bonus(p_empno);/*存储过程作为一个语句*/ENDLOOP;END;7.2 存储过程存储过程例例 19 计算empno从1到10的员工的奖金，并把计算结果存放到关系emp_bonus中。利用函数来实现DECLAREp_empnoNUMBER;p_bonusREAL;BEGINFORp_empnoIN1.10LOOPSELECTcalc_bonus_func(p_empno)INTOp_bonusFR

45、OMDUAL;INSERTINTOemp_bonus(p_empno,p_bonus);ENDLOOP;END;7.2 存储过程存储过程4、删除过程和函数当一个存储过程或函数不再需要时，可以用DROP语句将它们删除掉。例例 20删除calc_bonus和calc_bonus_funcDROPPROCEDUREcalc_bonus;DROPFUNCTIONcalc_bonus_func;7.2 存储过程存储过程六、游标六、游标如果SELECT语句只返回一行，则可以将该结果存放到变量中，例如语句SELECTCOUNT(*)INTOp_totalFROMStudent可以正确执行。而语句SELECT

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

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

48、符串常量NHaveaniceday!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、日期时间常量、日期时间常

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

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