SQL语言-SQL高级应用(2)分析

SQL高级应用本章首先介绍Transact-SQL程序设计基础：标识符、数据类型、各类运算符、变量、批处理、流程控制语句、函数等，然后介绍存储过程、游标、触发器的使用。SQL高级应用1

T-SQL程序设计基础2存储过程3游标4触发器2存储过程存储过程是存储于数据库中的一组T-SQL语句。可将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute，即可自动完成命令。存储过程是一种数据库对象，可以包含程序流、逻辑控制以及对数据库的查询，可以接受参数、输出参数、返回单个或多个结果集以及状态集，并可重用或嵌套。

存储过程的优点代码执行效率高模块化编程数据查询效率高安全性高减少网络流量存储过程的分类系统存储过程用户自定义的存储过程。系统存储过程系统存储过程是由SQLServe系统创建的存储过程，用户可直接使用。系统存储过程存储在master数据库中，以“sp_”开头命名。系统存储过程主要用于系统管理、用户登录管理、权限设置、数据库对象管理、数据复制等操作。常用的系统存储过程sp_help：报告有关数据库对象（sysobjects表中列出的任何对象）、用户定义数据类型或系统所提供的数据类型的信息。sp_addlogin：创建新的SQLServer登录，该登录允许用户使用SQLServer身份验证连接到SQLServer实例。Sp_password：为MicrosoftSQLServer登录名添加或更改密码。sp_cursor_list：报告当前为连接打开的服务器游标的属性。Sp_adduser：向当前数据库中添加新的用户。sp_addrole：在当前数据库中创建新的数据库角色。sp_addrolemember：为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows登录或Windows组。常用的系统存储过程（续）sp_droplogin：删除SQLServer登录名。这样将阻止使用该登录名对SQLServer实例进行访问。sp_dropuser：从当前数据库中删除数据库用户。sp_droprole：从当前数据库中删除数据库角色。sp_droprolemember：从当前数据库的SQLServer角色中删除安全帐户。sp_addtype：创建别名数据类型。例25使用存储过程sp_help查看bank数据库的信息。usestudentgoexecsp_helpgo用户自定义存储过程数据库用户可根据某一特定功能的需要，在用户数据库中由用户创建的存储过程存储过程命名时不能以“sp_”开头。2.2存储过程的创建CREATEPROCEDUREprocedure_name

[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]AS

sql_statement语法说明procedure_name：新创建的存储过程名称，过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。;number：是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。存储过程最多可有2100个参数。data_type:参数的数据类型。除table之外的其他所有数据类型均可以用作存储过程的参数。但是，cursor数据类型只能用于OUTPUT参数。如果指定cursor数据类型，则还必须指定VARYING和OUTPUT关键字。对于可以是cursor数据类型的输出参数，没有最大数目的限制。语法说明（续）VARYING：指定作为输出参数支持的结果集，仅适用于游标参数。default：参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或NULL。OUTPUT：表明参数是返回参数，可将信息返回给调用过程。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：RECOMPILE表明SQLServer不会缓存该过程的被引用的对象，该过程将在运行时重新编译。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。AS：指定过程要执行的操作。sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。2.3存储过程的执行存储过程一般不会自动执行，用户可使用EXECUTE命令来直接执行存储过程。

执行存储过程必须具有执行该存储过程的权限。如果存储过程是批处理中的第一个语句，EXECUTE可以省略。

存储过程的执行[EXEC[UTE]][@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,…][WITHRECOMPILE]语法说明只能在当前数据库中创建存储过程。@return_status=：是一个可选的整形变量，用来保存存储过程的返回状态。该变量在用于EXECUTE语句之前必须在批处理、存储过程或函数中声明。procedure_name：要调用的存储过程名称。;number：可选的整数，具体含义同CREATEPROCEDURE中的;number。@procedure_name_var：是局部定义变量名，代表存储过程名称。@parameter，value：是过程参数及其值。语法说明（续）@variable：用来保存参数或返回参数的变量。OUTPUT：指定存储过程必须返回一个参数。DEFAULT：根据过程的定义，提供参数的默认值。WITHRECOMPILE：可强制重新编译存储过程代码，但消耗较多的系统资源。2.4存储过程的查看和修改存储过程的有关信息以及创建存储的文本均被存储在SQLServer数据库中的系统表sysobjects和syscomments中，通过SELECT语句可直接查看存储过程的定义。存储过程的查看selectsysobjects.id,syscomments.textfromsysobjects,syscommentswheresysobjects.id=syscomments.idandsysobjects.type='P'='procedure_name'其中，procedure_name为要查看的存储过程的名称。ALTERPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement其中的参数和保留字的含义说明与CREATEPROCEDURE语句一致。存储过程的修改存储过程的删除DROPPROCEDURE{procedure_name}[,…]语法说明：procedure_name：为要删除的存储过程或存储过程组的名称。存储过程分组后，将无法删除组内的单个存储过程。删除一个存储过程将会把同组的所有存储过程都删除。例1创建一个不带参数的存储过程P1，用于检索所有学生信息，并执行该存储过程。--创建存储过程P1usestudentgocreateprocedurep1asselect*fromstudentgo--执行存储过程p1executep1

例27创建带有一个输入参数的存储过程P2，用于检索没有参加考试某门课程考试的同学。--创建存储过程P2CREATEPROCEDUREP2@kcmsvarchar(20)ASBEGIN

select*fromstudentwheresnoNOTIN(selectsnofromscorewherecnoIN(selectcnofromcoursewherecname=@kcms))END--执行存储过程p2executep2‘数据结构'例28创建带有一个输入参数和一个输出参数的存储过程P3，输入学生的学号，返回姓名。--创建存储过程P3CREATEPROCEDUREP3@xhchar(10),@xmchar(10)OUTPUTASBEGINselect@xm=snamefromstudentwheresno=@xhEND--执行存储过程p3declare@xmvarchar(10)executep1‘101@xmOUTPUTprint@xm例29创建带有多个输入参数和多个输出参数的存储过程P4，输入学生的学号和课程号，返回姓名和成绩。--创建存储过程P4CREATEPROCEDUREP4@xhchar(10),@kchchar(10),@xmchar(10)OUTPUT,@cjintOUTPUTASBEGINselect@xm=snamefromstudentwheresno=@xhselect@cj=gradefromscorewheresno=@xhandcno=@kchEND--执行存储过程p4declare@xmvarchar(10),@cjintexecutep1’101’,’201’,@xmOUTPUT,@cjOUTPUTprint@xmPrint@cj例29删除例26、27、28创建的存储过程p1、p2、p3。usestudentdropprocedurep1,p2,p3,p4练习(1)创建存储过程，给定老师姓名，删除学习该老师的选课记录。存储过程的参数为老师姓名，返回删除记录的个数。(2)创建存储过程，给定老师姓名，把score表中该老师授课成绩低于60分的改为0分。返回修改记录的个数。(3)创建存储过程，给定学号、课程号和成绩，如果在score表中有该选课记录，更新该选课的成绩，如果没有，则在score表中插入一条新记录。数据更新成功，返回1，数据更新失败，返回-1。3游标SQL语言与主语言在数据处理方式上不同，SQL语言是面向集合的，一条SQL语句可以产生或处理多条记录；而主语言是面向单一记录的，一次只能处理一条记录。可借助于游标来进行面向单条记录的数据处理，来协调这两种不同的数据处理方式。3.1游标概述游标是一种数据结构，包括：游标结果集：由定义该游标的SELECT语句返回的行的集合。游标位置：指向这个集合中某一行的指针。游标可以逐行处理数据，具有以下优点通过游标允许程序对由SELECT产生的结果集的每一行执行相同或不同的操作。允许定位在结果集的特定行。允许结果集中的当前行被修改。允许由其他用户修改的数据在结果集中是可见的。提供脚本、存储过程和触发器中使用的访问结果集中的数据的T-SQL语句。SQLSERVER支持三种类型的游标Transact_SQL游标：由DECLARECURSOR语法定义，主要用在Transact_SQL脚本、存储过程和触发器中。Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。API游标：支持在OLEDB，ODBC以及DB_library中使用游标函数，主要用在服务器上。客户游标：主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。客户游标常常仅被用作服务器游标的辅助。使用游标的步骤定义游标打开游标从游标中获取记录关闭游标释放游标3.2声明游标DECLARE<cursor_name>CURSORFOR<select_statement>[FOR{READONLY|UPDATE[OFcolumn_name[,……n]]}]语法说明cursor_name:是所定义的游标名称。select_statement：是定义游标结果集的标准SELECT语句，既可以是简单查询，也可以是复杂的连接查询或嵌套查询。FORREADONLY：指出游标的结果集是只读的，不能修改。FORUPDATE[OFcolumn_name[,……n]]:指出游标的结果集是可以被修改的。如果指定OFcolumn_name[,……n]参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。定义游标仅仅是一条说明性语句，这时DBMS并不执行SELECT语句。

3.3打开游标OPEN<cursor_name>语法说明：打开游标实际上是执行相应的SELECT语句，并把指定表中的所有满足查询条件的记录放到内存缓冲区中，可从全局变量@@CURSOR_ROWS中读取游标结果集合中的行数。执行了“打开游标”后，游标处于活动状态，指针指向查询结果集的第一条记录。3.4提取游标中的记录FETCH[NEXT|PRIOR|FIRST|LAST]Cursor_name[INTO@variable_name[,…n]]语法说明：NEXT：向下移动。如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。PRIOR：向前移动。如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。FIRST：提取游标中的第一行并将其作为当前行。语法说明（续）LAST：提取游标中的最后一行并将其作为当前行。INTO@variable_name[,…n]：将提取到的数据放到局部变量中。列表中的局部变量的个数及数据类型应与定义游标时的SELECT语句目标列一致。FETCH语句每次只能提取一行数据。因此FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的记录进行处理。FETCH语句的执行状态保存在全局变量@@FETCH_STATUS中，有三种取值：0表示成功；－1表示失败或此行不再结果集中；－2表示被提取的行不存在。3.5关闭游标CLOSEcursor_name语法说明：在游标使用结束后，应关闭游标，以释放游标占用的缓冲区及其他资源。3.6释放游标由于关闭游标时并没有删除游标，因此游标仍然占用系统资源。可使用DEALLOCATE语句来释放游标所占用的系统资源，其基本格式如下：DEALLOCATEcursor_name

语法说明：cursor_name：为要释放的游标名称。例30定义一个查询的游标，将score中不及格学生的姓名、课程名和成绩查询出来。--声明变量declare@xmvarchar(20),@kcmcvarchar(20),@cjint--创建游标DECLARERetrive_gradeCURSORFORselectsname,cname,gradefromstudent,course,scorewherestudent.sno=o=oandgrade<60--打开游标OPEN

Retrive_grade--提取第一行数据FETCHNEXTFROMRetrive_gradeINTO@xm,@kcmc,@cj--打印表标题PRINT

'姓名课程名称成绩'PRINT'--------------------------------------------------'while@@FETCH_STATUS=0BEGIN

--打印当前行

PRINT@xm+@kcmc+CONVERT(varchar(5),@cj)--提取下一行数据

FETCHNEXTFROMRetrive_gradeINTO@xm,@kcmc,@cjEND--关闭游标CLOSE

Retrive_grade--释放游标DEALLOCATE

Retrive_grade练习定义一个游标，查询打印score中不及格学生的姓名、课程名和成绩和绩点。说明：绩点的计算调用函数：js_jd(姓名,课程名称)

打印格式：姓名课程名称成绩绩点---------------------------------通过游标修改数据，UPDATE语句的语法形式UPDATEtable_nameWHERECURRENTOF

cursor_name其中：table_name：为需要修改的数据库表名。cursor_name：为游标名。当游标基于多个表时，UPDATE语句只能修改一个基本表中的数据，其他表中的数据不受影响。例31定义一个游标，把绩点低于2.0的课程的成绩加10分，并保存到score表中。declare@xmvarchar(20),@kcmcvarchar(20),@cjint,@jddecimal(5,1)--定义游标DECLARE

Update_gradeCURSORFORselectsname,cname,gradefromstudent,course,scorewherestudent.sno=o=o

FORUPDATEOFgrade--打开游标OPENUpdate_grade--提取第一行数据FETCH

NEXTFROMUpdate_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)–计算绩点-提取数据while@@FETCH_STATUS=0BEGIN--处理绩点低于2.0IF@jd<2.0UPDATEscoreSETgrade=grade+10

WHERECURRENTOFUpdate_gradeFETCHNEXTFROMUpdate_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)END--关闭游标CLOSEUpdate_grade--释放游标DEALLOCATEUpdate_grade通过游标删除数据，DELETE语句的语法形式DELETEFROMtable_nameWHERECURRENTOF

cursor_name其中：table_name：为需要修改的数据库表名。cursor_name：为游标名。当游标基于多个数据表时，DELETE语句一次只能删除一个基本表中的数据，其他基本表中的数据不受影响。例32定义游标，将score表中绩点为0的信息删除。declare@xmvarchar(20),@kcmcvarchar(20),@cjint,@jddecimal(5,1)--定义游标DECLARE

Delete_gradeCURSORFORselectsname,cname,gradefromstudent,course,scorewherestudent.sno=o=o

FORUPDATE--打开游标OPENDelete_grade--提取第一行数据FETCH

NEXTFROMDelete_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)–计算绩点-提取数据while@@FETCH_STATUS=0BEGIN--删除绩点为0的记录IF@jd=0DELETEFROMscore

WHERECURRENTOFDelete_gradeFETCHNEXTFROMDelete_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)END--关闭游标CLOSEDelete_grade--释放游标DEALLOCATEDelete_grade练习创建一个存储过程P8，查询打印给定学生姓名的选课信息,并计算其平均绩点。说明：绩点的计算调用函数：js_jd(姓名,课程名称)

打印格式：XXX同学的成绩表课程名称成绩绩点---------------------------------XXXXXX.X---------------------------------

平均绩点：X.X4触发器触发器是一种在使用UPDATE、INSERT或DELETE命令对指定表中的数据进行修改时，由数据库管理系统自动执行的内嵌程序，用来保证数据的一致性和完整性。触发器与表紧密相连，可以看作是表定义的一部分。触发器可以建立在一个用户定义的表或视图上，但不能建立在临时表或系统表上。触发器基于一个表创建，但可以操作多个表。触发器作为一个独立的单元被执行，被看作一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。触发器是一种特殊的存储过程，但它与存储过程不同，存储过程的存在独立于表，而触发器和表紧密结合；存储过程可以通过存储过程名称而被直接调用，触发器则是通过事件进行触发而自动被执行的。触发器的优点触发器是自动执行的。触发器可以通过数据库中的相关表进行层叠更改，实现多个表之间数据的一致性和完整性。触发器可以强制比用check约束定义的约束更为复杂的约束。触发器可以引用其他表中的列。一个表中的多个同类触发器（INSERT、UPDATE、DELETE）运行采取多个不同的对策，以相应同一个修改语句。触发器可以评估数据修改前后的表状态，并根据其差异采取对策。SQLServer触发器的分类AFTER触发器：又称后触发器。这种触发器在数据变动完成后才被触发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。只能在表上定义。在同一个数据表中可以创建多个after触发器。默认为after触发器。for同after。INSTEADOF触发器：前触发器。其在数据变动前被触发，并取代变动数据的操作（update、insert、delete），而去执行触发器定义的操作。可在表或视图上定义，每个update、insert、delete语句最多可定义一个insteadof触发器。触发器的组成事件：事件是指对数据库的插入、删除、修改等操作。触发器在这些事件发生时将开始工作。条件：触发器将测试条件是否成立。如果条件成立，就执行相应的动作；否则什么也不做。动作：如果条件满足，则执行这些动作。Inserted表和deleted表在结构上类似于定义触发器的表。由系统自动创建和管理。可使用这两个临时驻留内存的表测试某些数据修改的效果以及设置触发器操作的条件；但是不能直接对表中的数据进行更改。Deleted表用于存储delete和update语句所影响的行的备份。在执行delete或update语句时，行从表中删除，并转移到deleted表中。Deleted表和基表通常没有相同的行。Inserted表用于存储insert和update语句所影响的行的备份。在一个插入或更新事务处理中，新建行被同时添加到inserted表和基表中。Inserted表中的行是基表中新行的备份。在对具有触发器的表（触发器表）进行操作时，其操作过程如下：执行insert操作：插入到触发器表中的新行被插入到inserted表中。执行delete操作：从触发器表中删除的行被插入到deleted表中。执行update操作：先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到deleted表中，插入的新行被插入到inserted表中。创建触发器CREATE

TRIGGER<trigger_name>ON<table|view>[WITHENCRYPTION]

{

{

{

FOR|AFTER|INSTEADOF

}{[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement[...n]

}

}

语法说明：Trigger_name：触发器的名称。Table|view：是在其上执行触发器的表或视图。Withencryption：加密syscomments表中包含createtrigger语句文本的条目。After：指定触发器只有在触发sql语句中指定的所有操作都已成功执行后才激发。如果仅指定for，则是默认设置。不能在视图上定义after触发器。Insteadof：指定执行触发器而不是执行触发sql语句，从而替代触发语句的操作。在表或视图上，每个insert、update、delete语句最多可以定义一个insteadof触发器。