数据库存储过程.ppt
《数据库存储过程.ppt》由会员分享,可在线阅读,更多相关《数据库存储过程.ppt(69页珍藏版)》请在课桌文档上搜索。
1、存储过程,18:57,存储过程概述,以一个名字存储在数据库中,经过预编译的T-SQL语句集合可以独立执行或通过应用程序调用一旦创建,在服务器即被编译,可在需要时使用多次支持过程嵌套调用,18:57,存储过程概述,系统存储过程,存储在master数据库中,可以作为命令执行各种操作,以sp_开头。用户自定义的存储过程:用户创建,具有一定功能。,存储过程的类型:,注:不要以sp_开头创建自定义的存储过程。,18:57,存储过程的特点:,代码重用性:,创建一个存储过程存放在数据库中后,就可以被其它程序反复使用,提高了应用程序的可移植性。,高速性:,第一次被执行后,就驻留在内存中。以后执行就省去了重新分
2、析、优化、编译的过程。,存储过程概述,18:57,存储过程概述,减少网络通信量,安全性,有了存储过程后,在网络上只要一条语句就能执行一个存储过程。,通过隔离和加密的方法提高了数据库的安全性,通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,18:57,创建存储过程,1.利用企业管理器,存储过程节点,或单击“操作菜单”选择“新建存储过程”命令,系统弹出“存储过程属性”对话框。,(1)在控制台根目录中展开要建立存储过程的数据库,选择存储过程节点,则右边窗口会列出数据库中目前所有的存储过程,右键单击,18:57,创建存储过程,(2)在“文本”文本框中输入正确的SQL语句。(3)可以单击“
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为参数名称
4、,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_statu
5、s=procedure_name parameter=vale|variable,EXECUTE SCORESUMEXEC SCORESUMSCORESUM,该执行方法只能是批处理中的第一句!,18:57,创建存储过程,变量从变量的作用范围来分,SQL Server提供两种形式的变量:局部变量和全局变量。,局部变量,局部变量一般在批处理中被声明、定义、赋值和引用,批处理结束后,局部变量就消失了。利用局部变量还可以保存程序执行过程中的中间数据值,保存由存储过程返回的数据值等。,18:57,创建存储过程,1局部变量的定义 必须先用DECLARE命令定义后才可以使用。DECLAER 变量名 数据类型
6、,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 fro
7、m 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,求某个学生(按学号)某门课(课程号
8、)的分数。,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,注:一旦一
9、个参数用了按名传递,则其后所有的参数也必须都按名传递。,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=网络数
10、据库,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:
11、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
12、VARCHAR(4),SNO VARCHAR(10)=0001,正确?,如何改?,18:57,创建存储过程,注:若要在一个存储过程中使用一个或者多个带默认值的参数,则应将这些参数放到参数列表的最后。,18:57,创建存储过程,带传出参数的存储过程:,通过参数将值从存储过程带到调用程序,这种参数传递方式称为引用参数传递,这种参数称为传出参数。定义和调用传出参数时,必须带上OUTPUT关键字。,18:57,创建存储过程,例:创建一个存储过程,完成除法功能,并可以将商传出。,CREATE PROC DIVIDEDIVIDEND INT,DIVISOR INT,QUOTIENT INT OUTPUTA
13、SSELECT 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 DIVIDE1DIVIDE
14、ND 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,DI
15、VISOR 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语句中定义了OUTPU
16、T参数,而用户在调用时使用了一个变量向其传递数值,则存储过程会由这个变量向调用者返回数值。如果某参数被定义为OUTPUT,一旦该参数在存储过程中的值发生变化,与之相对应的调用者的变量也会发生变化,并在存储过程执行结束后,在调用者中保持改变后的值,这种调用被称作:“地址调用”。如果该参数没有被定义为OUTPUT,在存储过程中该参数的改变不会影响到存储过程执行后的变量值,称为:“传值调用”。,18:57,创建存储过程,CREATE PROC SUMSUM1SUM INT=0 OUTPUTASSET SUM=SUM+1GODECLARE XX INTSET XX=2EXEC SUMSUM1 XX O
17、UTPUTSELECT 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”,在弹出的快捷菜单中选择“属性”命令,打开“存储过程属性
18、”对话框。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 存储过程原名,存储过程新名 例:将存储过程
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 存储 过程
链接地址:https://www.desk33.com/p-250680.html