数据库存储过程.ppt

存储过程,18:57,存储过程概述,以一个名字存储在数据库中，经过预编译的T-SQL语句集合可以独立执行或通过应用程序调用一旦创建，在服务器即被编译，可在需要时使用多次支持过程嵌套调用,18:57,存储过程概述,系统存储过程，存储在master数据库中，可以作为命令执行各种操作，以sp_开头。用户自定义的存储过程：用户创建，具有一定功能。,存储过程的类型：,注：不要以sp_开头创建自定义的存储过程。,18:57,存储过程的特点：,代码重用性：,创建一个存储过程存放在数据库中后，就可以被其它程序反复使用，提高了应用程序的可移植性。,高速性：,第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。,存储过程概述,18:57,存储过程概述,减少网络通信量,安全性,有了存储过程后，在网络上只要一条语句就能执行一个存储过程。,通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,18:57,创建存储过程,1.利用企业管理器,存储过程节点，或单击“操作菜单”选择“新建存储过程”命令，系统弹出“存储过程属性”对话框。,（1）在控制台根目录中展开要建立存储过程的数据库，选择存储过程节点，则右边窗口会列出数据库中目前所有的存储过程，右键单击,18:57,创建存储过程,（2）在“文本”文本框中输入正确的SQL语句。（3）可以单击“语法检查”按钮，由系统检查语法错误。（4）单击“确定”按钮完成存储过程的创建。,一般存储过程可以使用SQL语句创建，修改则使用企业管理器比较方便。,18:57,创建存储过程,创建语法：CREATE PROCEDURE procedure_name;numberparameter data_type=default,nAS sql_statement,n,2.使用查询分析器,18:57,创建存储过程,Procedure_name:指定存储过程的名称；Number:对同名的存储过程指定一个序号；parameter date_type=default：指定存储过程的参数。Parameter为参数名称，date_type参数的数据类型，=default 用于指定参数的默认值。默认值必须是常数或空值。AS sql_statement：过程中要包含的T-SQL语句。,注：存储过程中不可以使用创建数据库对象的语句；创建存储过程的权限默认是DBO。,18:57,创建存储过程,SELECT S.SNO,SUM(SCORE)FROM S,S_C WHERE S.SNO=S_C.SNO GROUP BY S.SNO,例：统计学生所选课程所得的总成绩，要求显示学号和总成绩。,CREATE PROCEDURE SCORESUM AS,18:57,创建存储过程,执行语法：EXECUTEreturn_status=procedure_name parameter=vale|variable,EXECUTE SCORESUMEXEC SCORESUMSCORESUM,该执行方法只能是批处理中的第一句！,18:57,创建存储过程,变量从变量的作用范围来分，SQL Server提供两种形式的变量：局部变量和全局变量。,局部变量,局部变量一般在批处理中被声明、定义、赋值和引用，批处理结束后，局部变量就消失了。利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。,18:57,创建存储过程,1局部变量的定义 必须先用DECLARE命令定义后才可以使用。DECLAER 变量名 数据类型,n 2局部变量的赋值方法 SET 变量名=表达式 或者 SELECT 变量名=表达式,.n 3局部变量的显示 SELECT 变量名,18:57,创建存储过程,例1：定义age和name两个变量。declare age smallint,name char(10)例2：将name和age赋常量值。declare age smallint,name char(10)select name=张三,age=20例3：从s表中选择学号0001的姓名和年龄赋值给name和age。declare age smallint,name char(10)select name=sname,age=age from s where sno=0001,18:57,创建存储过程,例4：利用变量修改数据 declare age int set age=20 update s set age=age where sno=0001,例5：显示变量select name,age,18:57,创建存储过程,1、全局变量被服务器用来跟踪服务器范围和特定会话期的信息，它不能由用户定义，也不能显式地被赋值。2、服务器提供了一些有用的全局变量，只读的。,全局变量,error,执行正确返回“0”，否则返回其它值。,18:57,创建存储过程,带参数的存储过程：,例:创建一个存储过程SCORE，求某个学生（按学号）某门课（课程号）的分数。,CREATE PROC SCORESNO VARCHAR(10),CNO VARCHAR(4)AS SELECT SCORE FROM S_C WHERE SNO=SNO AND CNO=CNO,18:57,创建存储过程,调用时：,按预定义顺序提供参数；EXEC SCORE 0001,001,查询学号为0001，课程号为001的成绩。,按任意顺序提供参数（按名传递）；EXEC SCORE CNO=001,SNO=0001,EXEC SCORE SNO=0001,CNO=001,EXEC SCORE 0001,CNO=001,EXEC SCORE SNO=0001,001,注：一旦一个参数用了按名传递，则其后所有的参数也必须都按名传递。,18:57,创建存储过程,例:创建一个存储过程SCORE1，求某个学生（按姓名）某门课（课程名）的分数。,CREATE PROC SCORE1SNAME VARCHAR(10),CNAME VARCHAR(40)AS SELECT SNAME,CNAME,SCORE FROM S,C,S_C WHERE S.SNO=S_C.SNO AND C.CNO=S_C.CNO AND SNAME=SNAME AND CNAME=CNAME,18:57,创建存储过程,EXEC SCORE1 马诚,网络数据库,EXEC SCORE1 CNAME=网络数据库,SNAME=马诚,查询姓名为“马诚”的学生“网络数据库”的成绩。,EXEC SCORE1 马诚,CNAME=网络数据库,18:57,创建存储过程,参数有默认值的存储过程：,默认值是一个常量，存储过程的调用者没有提供参数的时候，自动使用预先约定的默认值。,例：创建一个存储过程SCORE2，求某个学生（按学号）某门课（课程号）的分数，其中默认课程为001。,18:57,创建存储过程,CREATE PROC SCORE2SNO VARCHAR(10),CNO VARCHAR(4)=001AS SELECT SCORE FROM S_C WHERE SNO=SNO AND CNO=CNO,18:57,创建存储过程,注：在调用带默认值参数的存储过程时,具有默认值的参数可以被重新指定新值，也可以被省略以使用其默认值。,EXEC SCORE2 0001,002,EXEC SCORE2 0001,EXEC SCORE2 SNO=0001,在存储过程内部，SNO和CNO分别为？,18:57,创建存储过程,CREATE PROC SCORE3SNO VARCHAR(10)=0001,CNO VARCHAR(4)AS SELECT SCORE FROM S_C WHERE SNO=SNO AND CNO=CNO GO,EXEC SCORE3 001,EXEC SCORE3 CNO=001,CNO VARCHAR(4),SNO VARCHAR(10)=0001,正确？,如何改？,18:57,创建存储过程,注：若要在一个存储过程中使用一个或者多个带默认值的参数，则应将这些参数放到参数列表的最后。,18:57,创建存储过程,带传出参数的存储过程：,通过参数将值从存储过程带到调用程序，这种参数传递方式称为引用参数传递，这种参数称为传出参数。定义和调用传出参数时，必须带上OUTPUT关键字。,18:57,创建存储过程,例：创建一个存储过程，完成除法功能，并可以将商传出。,CREATE PROC DIVIDEDIVIDEND INT,DIVISOR INT,QUOTIENT INT OUTPUTASSELECT QUOTIENT=DIVIDEND/DIVISOR,18:57,创建存储过程,EXEC DIVIDE,RESULT OUTPUT,DECLARE RESULT INT,SELECT RESULT,20,5,执行该存储过程：,EXEC DIVIDE DIVIDEND=20,DIVISOR=5,QUOTIENT=RESULT OUTPUT,考虑:OUTPUT是否可以省略?,DECLARE RESULT INTSET RESULT=3EXEC DIVIDE 20,5,RESULT SELECT RESULT,18:57,创建存储过程,CREATE PROC DIVIDE1DIVIDEND INT,QUOTIENT INT OUTPUT,DIVISOR INTASSELECT QUOTIENT=DIVIDEND/DIVISOR,EXEC DIVIDE1 20,RESULT OUTPUT,5,SELECT RESULT,EXEC DIVIDE1 DIVIDEND=20,QUOTIENT=RESULT OUTPUT,DIVISOR=5,DECLARE RESULT INT,18:57,创建存储过程,注：若要在一个存储过程中一个或者多个参数为传出参数，则应将这些参数放到参数列表的最后。,18:57,创建存储过程,CREATE PROC DIVIDE2DIVIDEND INT,DIVISOR INT,QUOTIENT INT OUTPUTASSELECT QUOTIENTSELECT QUOTIENT=DIVIDEND/DIVISOR,传出参数也可以作为普通参数使用，即先用传出参数向存储过程传入参数值，然后再用该传出参数传出一个值。,18:57,创建存储过程,174,结果：,DECLARE RESULT INTSELECT RESULT=17EXEC DIVIDE2 20,5,RESULT OUTPUTSELECT RESULT,存储过程内部执行显示存储过程外部执行显示,NULL4,18:57,创建存储过程,特别说明：,如果在CREATE PROC语句中定义了OUTPUT参数，而用户在调用时使用了一个变量向其传递数值，则存储过程会由这个变量向调用者返回数值。如果某参数被定义为OUTPUT，一旦该参数在存储过程中的值发生变化，与之相对应的调用者的变量也会发生变化，并在存储过程执行结束后，在调用者中保持改变后的值，这种调用被称作：“地址调用”。如果该参数没有被定义为OUTPUT，在存储过程中该参数的改变不会影响到存储过程执行后的变量值，称为：“传值调用”。,18:57,创建存储过程,CREATE PROC SUMSUM1SUM INT=0 OUTPUTASSET SUM=SUM+1GODECLARE XX INTSET XX=2EXEC SUMSUM1 XX OUTPUTSELECT XX,CREATE PROC SUMSUMSUM INT=0 ASSET SUM=SUM+1DECLARE XX INTSET XX=2EXEC SUMSUM XX SELECT XX,GO,18:57,查看存储过程,1、使用企业管理器查看存储过程的步骤为：1）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。2）单击相应的数据库（这里我们选择student数据库），选择“存储过程”节点，在右边的列表中显示出当前数据库中所有的存储过程。3）选择需要查看的存储过程右击，例如“ST_PROC_BJ”，在弹出的快捷菜单中选择“属性”命令，打开“存储过程属性”对话框。4）在“属性”对话框中，既可以查看过程定义信息，又可以在文本框中对存储过程的定义进行修改。修改后，可以单击“应用”或“确定”按钮，保存修改。,18:57,查看存储过程,查看存储过程的定义语法：EXEC sp_helptext procedure_name例如：EXEC sp_helptext SCORE,查看存储过程的其它（所有者、类型、创建日期和参数）等属性语法：EXEC sp_help procedure_name例如：EXEC sp_help SCORE,利用系统存储过程,18:57,重命名存储过程,语法：EXEC sp_rename 存储过程原名,存储过程新名 例：将存储过程SCORE更名为“S_SCORE”EXEC sp_rename SCORE,S_SCORE,18:57,修改存储过程,创建语法：ALTER PROCEDURE procedure_name;numberparameter data_type=default,nAS sql_statement,n,2.使用查询分析器,1.使用企业管理器,18:57,修改存储过程,CREATE PROCEDURE SS_SCOREAS SELECT SNAME,SUM(SCORE)FROM S,S_C WHERE S.SNO=S_C.SNO GROUP BY SNAME,例：修改存储过程SS_SCORE，要求改成统计学生所选课程所得的平均成绩,要求显示姓名和平均成绩。,ALTER PROCEDURE SS_SCOREAS SELECT SNAME,AVG(SCORE)FROM S,S_C WHERE S.SNO=S_C.SNO GROUP BY SNAME,18:57,删除存储过程,语法：DROP RPOCEDURE 存储过程名称,n例：删除存储过程 SCORE1和SCORE2。语句：DROP PROCEDURE SCORE1,SCORE2,2.使用查询分析器,1.使用企业管理器,注：删除存储过程的语句中不能指定序号，即该语句将同时删除同名的所有存储过程。,18:57,编程结构,一、批和脚本,1.批（batch）,两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。SQL Server服务器将批处理编译成一个可执行单元，称为执行计划。这样处理可以节省系统开销。,18:57,编程结构,使用批时有如下限制：,2.脚本 脚本是一系列顺序提交的批。,1）并不是所有语句都可以和其它语句在一个批中。下列语句不能组合在同一个批中：create procedure create rule create defaul create trigger create view2）规则和缺省不能在同一个批中既绑定到列又被使用。3）CHECK约束不能在同一个批中既定义又使用。4）在同一个批中不能删除对象又重新定义它。5）用SET语句改变的选项在批结束时生效。6）在同一个批中不能改变一个表再立即引用其新列。,18:57,编程结构,二、PRINT语句,PRINT PRINT语句的作用是在屏幕上显示用户的信息。该语句语法如下：print 字符串|局部变量名|全局变量名 其中，字符串不超过255字节。,18:57,编程结构,三、条件执行语句,IF 布尔表达式 语句序列1ELSE 语句序列2,例:将S_C表中CNO为“002”的成绩增加10%，执行正确输出“修改成功！”，否则输出“修改失败！”,UPDATE S_CSET SCORE=SCORE*1.1WHERE CNO=002IF ERROR0PRINT 修改失败!ELSE PRINT 修改成功!,18:57,编程结构,四、IF EXISTS 语句,例：查询是否有人选修001号课程，如果有，则显示：“有人选修001号课程”，否则显示“无人选修001号课程”。,PRINT 有人选修001号课程ELSE PRINT 无人选修001号课程,IF EXISTS,(SELECT*FROM S_C WHERE CNO=001),18:57,编程结构,例：查询是否有人选修“网络数据库”课程，如果有，则显示：“有人选修“网络数据库”，否则显示“无人选修“网络数据库”。,IF EXISTS(SELECT*FROM S_C,C WHERE S_C.CNO=C.CNO AND CNAME=网络数据库)PRINT 有人选修网络数据库ELSE PRINT 无人选修网络数据库,18:57,编程结构,例：查询是否有人选修“网络数据库”课程，如果有，则显示：“有X人选修“网络数据库”，否则显示“无人选修“网络数据库”。,18:57,编程结构,DECLARE NUM INTIF EXISTS(SELECT*FROM S_C,C WHERE S_C.CNO=C.CNO AND CNAME=网络数据库)BEGINSELECT NUM=COUNT(*)FROM S_C,C WHERE S_C.CNO=C.CNO AND CNAME=网络数据库PRINT 有+CAST(NUM AS VARCHAR(4)+人选修“网络数据库”ENDELSE PRINT 无人选修网络数据库,18:57,编程结构,DECLARE NUM INTSELECT NUM=COUNT(*)FROM S_C,C WHERE S_C.CNO=C.CNO AND CNAME=网络数据库IF NUM0PRINT 有+CAST(NUM AS VARCHAR(4)+人选修网络数据库ELSE PRINT 无人选修网络数据库,CONVERT(VARCHAR(4),NUM),18:57,编程结构,例：查询是否有人选修某门课程，如果有，则显示：“有X人选修YY”，否则显示“无人选修YY”。,18:57,编程结构,CREATE PROC XX_S_CCNAME VARCHAR(30)ASDECLARE NUM INTSELECT NUM=COUNT(*)FROM S_C,C WHERE S_C.CNO=C.CNO AND CNAME=CNAMEIF NUM0PRINT 有+CAST(NUM AS VARCHAR(4)+人选修+CNAMEELSE PRINT 无人选修+CNAME,执行：EXEC XX_S_C 网络数据库,18:57,编程结构,六、重复执行语句,1、循环语句WHILE 布尔条件 语句序列,2、退出语句 BREAK,3、短路语句CONTINUE,五、语句块 BEGIN END,18:57,编程结构,declare t smallint,s intselect t=1,s=0while t=100begin select s=s+t select t=t+1endselect s,例：求S=1+2+3+100,18:57,编程结构,七、无条件返回语句,RETURN,例：查询有几人选修某门课程，显示：“有X人选修YY。,18:57,编程结构,CREATE PROC XX_S_C_NUMCNAME VARCHAR(30)ASDECLARE NUM INTSELECT NUM=COUNT(*)FROM S_C,C WHERE S_C.CNO=C.CNO AND CNAME=CNAMERETURN NUM,DECLARE XXNUM INTDECLARE XXCNAME VARCHAR(30)SET XXCNAME=网络数据库EXEC XX_S_C_NUM XXCNAME,XXNUM=,PRINT 有+CONVERT(VARCHAR(4),XXNUM)+人选修+XXCNAME,DECLARE NUM INTDECLARE CNAME VARCHAR(30)SET CNAME=网络数据库EXEC NUM=XX_S_C_NUM CNAMEPRINT 有+CONVERT(VARCHAR(4),NUM)+人选修+CNAME,18:57,编程结构,八、延时语句,WAITFOR DELAY 延时WAITFOR TIME 延时到时间,例2：延时到10:00:00，执行存储过程XX_S_C。,WAITFOR TIME 10:00:00EXEC XX_S_C 网络数据库,例1：延时5秒，执行存储过程XX_S_C。,WAITFOR DELAY 00:00:05EXEC XX_S_C 网络数据库,18:57,存储过程实例,例：创建一个存储过程WHSALARY，功能为：得出某仓库职工的平均工资，并能返回操作结果。,18:57,存储过程实例,USE 仓库管理GOCREATE PROC WHSALARYWHNUM VARCHAR(4),AVG_SALARY INT OUTPUTASDECLARE ERRORSAVE INTSET ERRORSAVE=0SELECT AVG_SALARY=AVG(工资)FROM 职工 WHERE 仓库号=WHNUMIF(ERROR0)SET ERRORSAVE=ERRORRETURN ERRORSAVE,18:57,存储过程实例,DECLARE RETURNCODE INTDECLARE AVERAGE INTEXEC RETURNCODE=WHSALARY WHNUM=WH2,AVG_SALARY=AVERAGE OUTPUTSELECT RETURNCODESELECT AVERAGE,18:57,存储过程实例,USE 仓库管理GOCREATE PROC WHSALARYWHNUM VARCHAR(4),AVG_SALARY INT OUTPUTASSELECT AVG_SALARY=AVG(工资)FROM 职工 WHERE 仓库号=WHNUMRETURN ERROR,18:57,存储过程实例,例：建立一个存储过程，完成自动编号。表中的关键字为字符型，并且是自动计算出来的，如原有的记录中关键字有00001，下一条记录的关键字就应产生为00002，以此类推。,所用的表BOOK（BNO，BNAME）,18:57,存储过程实例,CREATE PROC AUTO_IDROW_ID CHAR(5)OUTPUTASDECLARE OLDNUM INT,NEWNUM INTSELECT OLDNUM=COUNT(*)FROM BOOKIF OLDNUM=0 SET NEWNUM=1ELSE BEGIN SELECT OLDNUM=MAX(BNO)FROM BOOK SELECT NEWNUM=OLDNUM+1 ENDSET ROW_ID=REPLACE(STR(NEWNUM,5),0),18:57,存储过程实例,DECLARE ROW_ID CHAR(5)EXEC AUTO_ID ROW_ID OUTPUTSELECT ROW_ID,执行:,插入一条名为“信号与系统”的书。,DECLARE ROW_ID CHAR(5)EXEC AUTO_ID ROW_ID OUTPUTINSERT BOOK VALUES(ROW_ID,信号与系统),18:57,存储过程实例,CREATE PROC AUTO_IDRECBNO VARCHAR(50)ASDECLARE OLDNUM INT,NEWNUM INTSELECT OLDNUM=COUNT(*)FROM BOOKIF OLDNUM=0 SET NEWNUM=1ELSE BEGIN SELECT OLDNUM=MAX(BNO)FROM BOOK SELECT NEWNUM=OLDNUM+1 ENDINSERT BOOK VALUES(REPLACE(STR(NEWNUM,5),0),BNO),18:57,存储过程实例,插入一条名为“信号与系统”的书。,EXEC AUTO_IDREC 信号与系统,18:57,存储过程实例,例：创建一个存储过程，功能为自动生成出库单号（格式为E080500001）。,思路：1、用getdate()获取系统时间，用year()获取年的最后两位，用month()获取月，将年和月连接起来存在变量a里。2、在出库表里查找有没有变量a这个时间的单号，如果不存在，则新单号为E+a+00001；如果存在，获取这个时间的最大单号，加1得到新单号的次序。3、以传出参数的形式输出新单号。,18:57,存储过程实例,例：完成一个数据加密的存储过程。,思路：,加密：1、确定参数：登陆名和原密码，两个参数。2、从密钥表里将密钥取出来存在变量里。3、比较原密码与密钥的长度，如果密钥长度小于原密码长度，则将密钥加长至大于等于原密码长度。4、循环（原密码长度的次数），获得每个字母加密后的密文连接起来为密文。5、插入表中（登陆名参数，密文）,18:57,存储过程实例,解密：1、确定参数：登陆名2、根据登陆名在表中取出密文存在变量中。3、从密钥表里将密钥取出来存在变量中。4、将密文除以3得出商，再与密钥比较长度，若不够长度，则同上补密钥长度。5、循环，解密。6、将解密后的密码以传出参数的形式输出。,18:57,存储过程实例,CREATE PROC AUTO_CKCKDNO CHAR(10)OUTPUTASDECLARE CKDT CHAR(4),CKDY CHAR(2),CKDM CHAR(2)SET CKDY=SUBSTRING(CAST(YEAR(GETDATE()AS CHAR(4),3,2)SET CKDM=REPLACE(STR(MONTH(GETDATE(),2),0)SET CKDT=CKDY+CKDMIF NOT EXISTS(SELECT*FROM 出库 WHERE SUBSTRING(出库单号,2,4)=CKDT)SET CKDNO=E+CKDT+00001ELSE BEGIN DECLARE XH INT SELECT XH=MAX(CAST(SUBSTRING(出库单号,6,5)AS INT)FROM 出库 WHERE SUBSTRING(出库单号,2,4)=CKDT SET XH=XH+1 SET CKDNO=E+CKDT+REPLACE(STR(XH,5),0)END,