sqlserver存储过程

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Transact-SQL,程序设计,在,Transact-SQL,语言中标准的,SQL,语句畅通无阻。,Transact-SQL,也有类似于,SQL,语言的分类不过做了许多扩充。,在第,3,章中，我们介绍了标准,SQL,语言的语法及其基本使用方法，在此只介绍,Transact-SQL,语言中的其它部分。,1,变量,2,流程控制命令,3,其它命令,4,常用函数,1,1 .,变量,Transact-SQL,中使用两种变量：局部变量和全局变量。,1,局部变量,局部变量是用户可自定义的变量，它的作用范围仅在程序内部。,在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。,局部变量必须以,开头，而且必须先用,DECLARE,命令说明后才可使用。其说明形式为：,DECLARE ,变量名 变量类型,，,变量名变量类型,2,1 .,变量,在,Transact-SQL,中不能像在一般的程序语言中一样使用“变量,=,变量值”来给变量赋值，必须使用,SELECT,或,SET,命令来设定变量的值。其语法如下：,SELECT ,局部变量,=,变量值,SET ,局部变量量,=,变量值,【,例,】,声明一个长度为,8,个字符的变量,id,，并赋值。,declare id char,（,8,）,select id =10010001,3,2,全局变量,全局变量是,SQL Server,系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。,全局变量通常存储一些,SQL Server,的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或,Transact-SQL,命令执行后的状态值。,全局变量不是由用户的程序定义的，它们是在服务器级定义的，只能使用预先说明及定义的全局变量。,引用全局变量时必须以“,”,开头,。,局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。,4,3,注释符,在,Transact-SQL,中可使用两类注释符：,1,ANSI,标准的注释符“,-”,用于单行注释。,2,与,C,语言相同的程序注释符号，即“,/*/”,，,/*,用于注释文字的开头，*,/,用于注释文字的结尾，可在程序中标识多行文字为注释。,5,2 .,流程控制命令,Transact-SQL,语言使用的流程控制命令主要有以下几种控制命令。,2.1,BEGINEND,其语法如下：,BEGIN,END,BEGINEND,用来设定一个程序块，将在,BEGINEND,内的所有程序视为一个单元执行。,BEGINEND,经常在条件语句（如,IFELSE,）,中使用。,在,BEGINEND,中可嵌套另外的,BEGINEND,来定义另一程序块。,6,2.2,IF,ELSE,其语法如下：,IF ,ELSE ,条件表达式式,其中,:,可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。,ELSE,子句是可选的，最简单的,IF,语句没有,ELSE,子句部分。,IFELSE,用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。,如果不使用程序块，,IF,或,ELSE,只能执行一条命令。,IF ELSE,可以进行嵌套，在,Transact-SQL,中最多可嵌套,32,级。,7,【,例,】,从,SC,数据表中求出学号为,S1,同学的平均成绩，如果此平均成绩大于或等于,60,分，则输出“,pass”,信息。,if (select,avg(score,) from sc where,sno,=S1 group by,sno,)=60,begin,print pass,end,8,2.3,CASE,CASE,命令有两种语句格式：,格式,1,：,CASE ,WHEN THEN ,WHEN THEN ,ELSE ,END,该语句的执行过程是：,将,CASE,后面表达式的值与各,WHEN,子句中的表达式的值进行比较，,如果二者相等，则返回,THEN,后的表达式的值，然后跳出,CASE,语句，否则返回,ELSE,子句中的表达式的值。,ELSE,子句是可选项。当,CASE,语句中不包含,ELSE,子句时，如果所有比较失败时，,CASE,语句将返回,NULL,。,9,【,例,】,从学生表,S,中，选取,SNO,，,SEX,，,如果,SEX,为“男”则输出“,M”,，,如果为“女”输出“,F”,。,SELECT SNO,SEX=,CASE sex,WHEN ,男, THEN M,WHEN ,女, THEN F,END,FROM S,10,格式,2,：,CASE,WHEN THEN ,WHEN THEN ,ELSE ,END,该语句的执行过程是：,首先测试,WHEN,后的表达式的值,如果其值为真，则返回,THEN,后面的表达式的值，否则测试下一个,WHEN,子句中的表达式的值,如果所有,WHEN,子句后的表达式的值都为假，则返回,ELSE,后的表达式的值,如果在,CASE,语句中没有,ELSE,子句，则,CASE,表达式返回,NULL,。,注：,CASE,命令可以嵌套到,SQL,命令中。,11,【,例,】,从,SC,表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于,60,分输出“不及格”、,60,分至,70,分输出“及格”、,70,分至,90,分输出“良好”、大于或等于,90,分时输出“优秀”。,SELECT SNO,CNO,SCORE=,CASE,WHEN SCORE IS NULL THEN ,未考,WHEN SCORE=60 AND SCORE=70 AND SCORE=90 THEN ,优秀,END,FROM SC,12,2.4,WHILECONTINUEBREAK,其语法如下：,WHILE ,BEGIN,BREAK,CONTINUE,命令行或程序块,END,WHILE,命令在设定的条件成立时，会重复执行命令行或程序块。,CONTINUE,命令可以让程序跳过,CONTINUE,命令之后的语句，回到,WHILE,循环的第一行，继续进行下一次循环。,BREAK,命令则让程序完全跳出循环，结束,WHILE,命令的执行。,WHILE,语句也可以嵌套。,13,如：以下程序计算,1,100,之间所有能被,3,整除的数的个数及总和。,DECLARE S SMALLINT,I SMALLINT,NUMS SMALLINT,SET S=0,SET I=1,SET NUMS=0,WHILE (I=100),BEGIN,IF (I%3=0),BEGIN,SET S=S+I,SET NUMS=NUMS+1,END,SET I=I+1,END,PRINT S,PRINT NUMS,14,2.5,WAITFOR,其语法如下：,WAITFOR DELAY | TIME ,| ERROREXIT | PROCESSEXIT | MIRROREXIT,WAITFOR,命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。,其中时间必须为,DATETIME,类型的数据，但不能包括日期。,各关键字含义如下：,1,DELAY,：,用来设定等待的时间，最多可达,24,小时,2,TIME,：,用来设定等待结束的时间点,3,ERROREXIT,：,直到处理非正常中断,4,PROCESSEXIT,：,直到处理正常或非正常中断,5,MIRROREXI,：,直到镜像设备失败,15,【,例,】,等待,1,小时,2,分零,3,秒后才执行,SELECT,语句。,waitfor,delay 01:02:03,Select * from employee,16,2.6,GOTO,语法如下：,GOTO,标识符,GOTO,命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往下执行。,作为跳转目标的标识符可为数字与字符的组合。但必须以“：”结尾。,在,GOTO,命令行，标识符后不必跟“：”,17,如：求,1,2,3,10,的总和。,DECLARE S SMALLINT,I SMALLINT,SET I=1,SET S=0,BEG:,IF (I=10),BEGIN,SET S=S+I,SET I=I+1,GOTO BEG,END,PRINT S,18,2.7,RETURN,语法如下：,RETURN,（,整数值,）,RETURN,命令用于结束当前程序的执行，返回到上一个调用它的程序或其它程序。,在括号内可指定一个返回值。,如果没有指定返回值，,SQL Server,系统会根据程序执行的结果返回一个内定值，如：,0,程序执行成功,-1,找不到对象,-2,数据类型错误,-3,死锁,-4,违反权限原则,-5,语法错误,-6,用户造成的一般错误,-7,资源错误如磁盘空间不足,-8,非致命的内部错误,-9,已达到系统的极限,-10 -11,致命的内部不一致性错误,-12,表或指针破坏,-13,数据库破坏,-14,硬件错误,如果运行过程产生了多个错误，,SQL Server,系统将返回绝对值最大的数值；,如果此时用户定义了返回值，则以返回用户定义的值。,RETURN,语句不能返回,NULL,值。,19,2,8 SELECT,SELECT,命令可用于给变量赋值其语法如下：,SELECT local_variable = expression ,，,.n,SELECT,命令可以一次给多个变量赋值。,当表达式,expression,为列名时，,SELECT,命令可利用其查询功能一次返回多个值，变量中保存的是其返回的最后一个值。,如果,SELECT,命令没有返回值，则变量值仍为其原来的值。,当表达式,expression,是一个子查询时，如果子查询没有返回值，则变量被设为,NULL,。,20,12,SET,SET,命令有两种用法：,（,1,）用于给局部变量赋值,在用,DECLARE,命令声明之后，,所有的变量都被赋予初值,NULL,。,需要用,SET,命令来给变量赋值，但与,SELECT,命令不同的是,SET,命令一次只能给一个变量赋值。,不过由于,SET,命令功能更强且更严密，因此，,SQL Server,推荐使用,SET,命令来给变量赋值。,21,（,2,）用于设定用户执行,SQL,命令时，,SQL Server,的处理选项设定。,有以下几种设定方式：,SET,：,选项,ON,SET,：,选项,OFF,SET,：,选项值,22,15,USE,语法如下：,USE ,databasename,USE,命令用于改变当前使用数据库为指定的数据库。,用户必须是目标数据库的用户成员或目标数据库建有,GUEST,用户账号时，使用,USE,命令才能成功切换到目标数据库。,23,3.,存储过程和触发器,在大型数据库系统中，存储过程和触发器具有很重要的作用。,无论是存储过程还是触发器，都是,SQL,语句和流程控制语句的集合。,就本质而言，触发器也是一种存储过程。,存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。,SQL Server 2000,不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。,24,3.1,存储过程概述,存储过程（,Stored Procedure,）,是一组为了完成特定功能的,SQL,语句集，经编译后存储在数据库中。,用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。,在,SQL Server,的系列版本中存储过程分为两类：,系统提供的存储过程,用户自定义存储过程,25,系统过程,主要存储,在,master,数据库中并以,sp_,为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理,SQL Server,提供支持。,通过系统存储过程，,SQL Server,中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。,尽管这些系统存储过程被放在,master,数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。,而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。,用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。,26,3.2,创建存储过程,在,SQL Server 2000,中创建一个存储过程有两种方法：,一种是使用,Transaction-SQL,命令,Create Procedure,，,另一种是使用图形化管理工具,Enterprise Manager,。,用,T-SQL,创建存储过程是一种较为快速的方法，,对初学者使用,Enterprise Manager,更易理解，更为简单。,当创建存储过程时，需确定存储过程的,三个组成部分,：,1,所有的,输入参数,以及传给调用者的,输出参数,2,被执行的针对数据库的操作语句包括调用其它存储过程的语句,3,返回给调用者的状态值以指明调用是成功还是失败,27,2.1,用,CREATE PROCEDURE,命令创建存储过程,在创建存储过程之前应该考虑到以下几个方面：,1,在一个批处理中,Create Procedure,语句不能与其它,SQL,语句合并在一起。,2,数据库所有者具有默认的创建存储过程的权限它可把该权限传递给其它的用户。,3,存储过程作为数据库对象其命名必须符合命名规则。,4,只能在当前数据库中创建属于当前数据库的存储过程。,28,创建存储过程的语法规则,CREATE PROCEDURE,存储过程名,参数名 类型,=default OUTPUT,n,AS,SQL,语句,n,如：在,teach,数据库中，创建一个名称为,myproc,的存储过程，该存储过程的功能是从数据表,s,中查询所有男同学的信息。,GO,CREATE PROCEDURE,myproc,AS,SELECT * FROM s WHERE sex = ,男,GO,29,定义具有,参数,的存储过程。,如：,在,teach,数据库中，创建一个名称为,InsertRecord,的存储过程，该存储过程的功能是向数据表,s,中插入一条记录，新记录的值由参数提供。,USE teach,GO,CREATE PROCEDURE,InsertRecord,(,sno,char(6),sn,char(20),age numeric(5),sex char(2),dept char(10),),AS,INSERT INTO s,VALUES(sno,sn,sex,age,dept,),GO,30,定义具有,参数默认值,的存储过程。,如：在,teach,数据库中，创建一个名称为,InsertRecordDefa,的存储过程，该存储过程的功能是向数据表,s,中插入一条记录，新记录的值由参数提供，如果未提供系别,dept,的值时，由参数的默认值代替。,USE teach,GO,CREATE PROCEDURE,InsertRecordDefa,(,sno,char(6),sn,char(20),age numeric(5),sex char(2),dept char(10)=,无,),AS,INSERT INTO s,VALUES(sno,sn, sex , age, dept),GO,31,定义能够,返回值,的存储过程。,如：在,teach,数据库中，创建一个名称为,Query_Study,的存储过程，该存储过程的功能是从数据表,s,中根据学号查询某一同学的姓名和系别。,USE teach,GO,CREATE PROCEDURE Query_Study,(,sno,char(6),sn,char(20) OUTPUT,dept char(10) OUTPUT,),AS,SELECT ,sn,=,sn, dept=dept,FROM s,WHERE,sno=sno,GO,32,2.2,使用,Enterprise Manager,创建存储过程,1,启动,Enterprise Manager,，,登录到要使用的服务器。,2,在,Enterprise Manager,的左窗格中，展开要创建存储过程的数据库文件夹，单击“存储过程”文件夹，此时在右窗格中显示该数据库的所有存储过程。,3,用右键单击“存储过程”文件夹，在弹出的快捷菜单中选择“新建存储过程”,此时打开“新建存储过程”对话框。,4,在“文本”编辑框中输入存储过程的正文内容。,5,单击“检查语法”按钮，检查语法是否正确。,6,单击“确定”，保存。,7,在右窗格中，右击该存储过程，在弹出菜单中选择“所有任务”,选择“管理权限”设置权限。,33,3.2,重新命名存储过程,通过,Enterprise Manager,也可修改存储过程的名字，其操作过程与,Windows,下修改文件名字的操作类似。,即首先选中需修改名字的存储过程,然后右击鼠标，在弹出菜单中选取“重命名”选项,最后输入新存储过程的名字。,34,3.3,删除存储过程,删除存储过程使用,drop,命令,，,drop,命令可将一个或多个存储过程或者存储过程组从当前数据库中删除。其语法规则为：,DROP PROCEDURE,procedure ,n,如：将存储过程,mynewproc,从数据库中删除。,drop procedure,mynewproc,35,3.4,执行存储过程,执行已创建的存储过程使用,EXECUTE,命令,1.,执行,teach,库中的存储过程,myproc,(,无参调用,),。,EXECUTE,myproc,2.,执行,teach,库中存储过程,InsertRecord,(,带参调用,),。,EXECUTE,InsertRecord,sno,= S1, ,sn,= ,王大利, sex = ,男, age = 18,dept= ,计算机系,36,3.,执行,teach,库中的存储过程,InsertRecordDefa,(,含默认值调用,),。,EXECUTE,InsertRecordDefa,sno,= S10,sn = ,高平, sex = ,女, age = 18,4.,执行,teach,库中的存储过程,Query_Study,(,含有输出参数,),。,DECLARE ,sn,char(20),DECLARE dept char(10),EXECUTE,Query_Study,S10,sn,OUTPUT,dept,OUTPUT,SELECT ,姓名, =,sn, ,系别,=dept,37,触发器概述,上面介绍了一般意义的存储过程，即用户自定义的存储过程和系统存储过程。,接下来将介绍一种特殊的存储过程，即触发器。,触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。,当对某一表进行诸如,UPDATE,、,NSERT,、,DELETE,这些操作时，,SQL Server,就会自动执行触发器所定义的,SQL,语句。从而确保对数据的处理必须符合由这些,SQL,语句所定义的规则。,触发器的主要作用就是其能够实现,由主键和外键所不能保证的复杂的参照完整性和数据的一致性,。,除此之外，触发器还有其它许多不同的功能。,38,创建触发器,1,用,CREATE TRIGGER,命令创建触发器,CREATE TRIGGER,触发器名,ON,表名,FOR INSERT,（,UPDATE,、,DELETE,）,AS,SQL,语句,其中：,insert,、,update,、,delete,为当前表所执行的操作（这些操作可根据需要进行设定），只有招待了指定的操作，,AS,后面的语句才会执行，也就是激活了触发器。,在触发器中也可以象存储过程中一样定义变量。,39,如：下面创建一个触发器，当向表,s,中插入一条记录时，自动显示表,s,中的记录。,CREATE TRIGGER Change_Display,ON s,FOR INSERT,UPDATE,DELETE,AS,SELECT * FROM s,该触发器建立完毕后，当执行如下操作时将会显示数据表,s,中的全部记录。,EXECUTE,InsertRecordDefa,sno,= S11, ,sn,= ,张建峰, age = 17, sex = ,男,40,2,用管理工具,Enterprise Manger,创建触发器,1,启动,Enterprise Manager,登录到要使用的服务器。,2,在,Enterprise Manager,的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。,3,在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，此时会出现 “触发器属性”对话框,。,41,2,用管理工具,Enterprise Manger,创建触发器,4,在“名称”下拉框中选择“,”,，在“文本”编辑框中输入触发器的文本命令。,5,单击“检查语法”按钮，检查语句是否正确。,6,单击“应用”按钮，在“名称”下拉列框中会有新创建的触发器名字。,7,单击“确定”按钮，关闭窗口创建成功。,42,管理触发器,1,使用,Enterprise Manager,显示触发器信息,1,启动,Enterprise Manager,登录到要使用的服务器。,2,在,Enterprise Manager,的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。,3,在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，出现 “触发器属性”对话框。,4,在“名称”下拉框中选择所要查看的触发器的名称，在“文本”编辑框中显示出该触发器的文本命令。,43,3,修改、删除触发器,通过,Enterprise Manager,和系统过程或,T_SQL,命令，可以修改触发器的名字和正文。,1,使用,sp_rename,命令修改触发器的名字,其语法格式为：,sp_rename,oldname,newname,oldname,为触发器原来的名称 ，,newname,为触发器的新名称。,2,通过,Enterprise Manager,修改触发器正文,通过,Enterprise Manager,修改触发器正文的操作步骤与查看触发器信息一样。,修改完触发器后要使用“检查语法”选项对语句进行检查。,3,通过,Alert trigger,命令修改触发器正文,44,4,删除触发器,用户在使用完触发器后可以将其删除。,只有触发器属主才有权删除触发器。,删除已创建的触发器有三种方法：,（,1,）用系统命令,DROP TRIGGER,删除指定的触发器，其语法形式如下：,DROP TRIGGER,触发器名字,（,2,）删除触发器所在的表时，,SQL Server,将自动删除与该表相关的触发器。,（,3,）按前介绍的方法进入“触发器属性”对话框，在该对话框中选择要删除的触发器，然后单击“删除”按钮即可。,45,CREATE PROCEDURE,disRecord,AS,declare ,sno,char(6),sn char(20),sex char(2),age,smallint,declare sp cursor for,select,s_no,s_name,s_sex,year(getdate,()-,year(s_birthday,) from student where,s_sex,=,女,open sp,fetch sp into ,sno,sn,sex ,age,while ,fetch_status,=0,begin,print ,sno+sn,+sex +,convert(char,age,),fetch sp into ,sno,sn,sex,end,close sp,deallocate,sp,46,课后必学内容,在,SQL Server,企业管理器中按,F1,键进入联机丛书，仔细阅读,“,存储过程,”,和,“,触发器,”,中的内容，读懂,“,设计存储过程,”,中的,5,个例子（,A,E,）,47,