数据库存储过程.ppt
存储过程存储过程18:30存储过程概述存储过程概述l以一个名字存储在数据库中，经过预编译的以一个名字存储在数据库中，经过预编译的T-SQL语句集合语句集合l可以独立执行或通过应用程序调用可以独立执行或通过应用程序调用l一旦创建，在服务器即被编译，可在需要时使一旦创建，在服务器即被编译，可在需要时使用多次用多次l支持过程嵌套调用支持过程嵌套调用18:30存储过程概述存储过程概述系统存储过程，存储在系统存储过程，存储在master数据库中，可数据库中，可以作为命令执行各种操作，以以作为命令执行各种操作，以sp_开头。开头。用户自定义的存储过程：用户创建，具有一用户自定义的存储过程：用户创建，具有一定功能。定功能。存储过程的类型：存储过程的类型：注：不要以注：不要以sp_开头创建自定义的存储过程开头创建自定义的存储过程。18:30存储过程的特点：存储过程的特点：代码重用性：代码重用性： 创建一个存储过程存放在数据库中后，就可以被其它程序反复使用，提高了应用程序的可移植性。高速性：高速性： 第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。存储过程概述存储过程概述18:30存储过程概述存储过程概述减少网络通信量减少网络通信量安全性安全性 有了存储过程后，在网络上只要一条语句就能执行一个存储过程。 通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。18:30创建存储过程创建存储过程1.利用企业管理器利用企业管理器存储过程节点，或单击存储过程节点，或单击“操作菜单操作菜单”选择选择“新新建存储过程建存储过程”命令，系命令，系统弹出统弹出“存储过程属性存储过程属性”对话框。对话框。（1 1）在控制台根目录中展开要建立存储过程的数据库，选择存储过）在控制台根目录中展开要建立存储过程的数据库，选择存储过程节点，则右边窗口会列出数据库中目前所有的存储过程，右键单击程节点，则右边窗口会列出数据库中目前所有的存储过程，右键单击18:30创建存储过程创建存储过程（2 2）在）在“文本文本”文本框中输入正确的文本框中输入正确的SQLSQL语句。语句。（3 3）可以单击）可以单击“语法检查语法检查”按钮，由系统检查语法错按钮，由系统检查语法错误。误。（4 4）单击）单击“确定确定”按钮完成存储过程的创建。按钮完成存储过程的创建。 一般存储过程可以使用一般存储过程可以使用SQLSQL语句创建，修改则使用企语句创建，修改则使用企业管理器比较方便。业管理器比较方便。18:30创建存储过程创建存储过程创建语法：创建语法：CREATE PROCEDURE procedure_name;numberparameter data_type =default ,nAS sql_statement,n2.使用查询分析器使用查询分析器18:30创建存储过程创建存储过程 Procedure_name:指定存储过程的名称；指定存储过程的名称； Number:对同名的存储过程指定一个序号；对同名的存储过程指定一个序号； parameter date_type =default ：指定存储过程的：指定存储过程的参数。参数。 Parameter为参数名称，为参数名称， date_type参数的数据参数的数据类型，类型， =default 用于指定参数的默认值。用于指定参数的默认值。默认值必须默认值必须是常数或空值。是常数或空值。 AS sql_statement：过程中要包含的：过程中要包含的T-SQL语句语句。注：存储过程中不可以使用创建数据库对象的语句；注：存储过程中不可以使用创建数据库对象的语句； 创建存储过程的权限默认是创建存储过程的权限默认是DBO。18:30创建存储过程创建存储过程 SELECT S.SNO,SUM(SCORE) FROM S,S_C WHERE S.SNO=S_C.SNO GROUP BY S.SNO例：统计学生所选课程所得的总成绩例：统计学生所选课程所得的总成绩，要求显示要求显示学号和总成绩。学号和总成绩。CREATE PROCEDURE SCORESUM AS18:30创建存储过程创建存储过程执行语法：执行语法：EXECUTEreturn_status=procedure_name parameter=vale|variableEXECUTE SCORESUMEXEC SCORESUMSCORESUM该执行方法只能是批处理中的第一句！该执行方法只能是批处理中的第一句！18:30创建存储过程创建存储过程变量变量从变量的作用范围来分，从变量的作用范围来分，SQL ServerSQL Server提供两种形式的变量：提供两种形式的变量：局部变量和全局变量。局部变量和全局变量。局部变量局部变量 局部变量一般在批处理中被声明、定义、赋值和引用，批局部变量一般在批处理中被声明、定义、赋值和引用，批处理结束后，局部变量就消失了。处理结束后，局部变量就消失了。 利用局部变量还可以保存程序执行过程中的中间数据值，利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。保存由存储过程返回的数据值等。18:30创建存储过程创建存储过程1 1局部变量的定义局部变量的定义 必须先用必须先用DECLARE命令定义后才可以使用。命令定义后才可以使用。 DECLAER 变量名变量名 数据类型数据类型 ,n 2局部变量的赋值方法局部变量的赋值方法 SET 变量名变量名 = 表达式表达式 或者或者 SELECT 变量名变量名 = 表达式表达式 ,.n 3局部变量的显示局部变量的显示 SELECT 变量名变量名 18:30创建存储过程创建存储过程例例1 1：定义：定义ageage和和namename两个变量。两个变量。declare age smallint, name char(10)例例2：将：将name和和age赋常量值。赋常量值。 declare age smallint, name char(10) select name=张三张三,age=20=20例例3 3：从：从s s表中选择学号表中选择学号0000001 1的姓名和年龄赋值给的姓名和年龄赋值给namename和和ageage。declare age smallint, name char(10) select namename=sname,=sname, ageage=age=age from s where sno=s where sno=00010001 18:30创建存储过程创建存储过程例例4 4 ：利用变量修改数据：利用变量修改数据 declare age int set age=20 update s set age=age where sno=0001例例5 5 ：显示变量：显示变量select name, age18:30创建存储过程创建存储过程1 1、全局变量被服务器用来跟踪服务器范围和特定、全局变量被服务器用来跟踪服务器范围和特定会话期的信息，它不能由用户定义，也不能显式地会话期的信息，它不能由用户定义，也不能显式地被赋值。被赋值。2 2、服务器提供了一些有用的全局变量，只读的。、服务器提供了一些有用的全局变量，只读的。全局变量全局变量error执行正确返回执行正确返回“0”，否则返回其它值。，否则返回其它值。18:30创建存储过程创建存储过程带参数的存储过程：带参数的存储过程：例例:创建一个存储过程创建一个存储过程SCORE，求某个学生（按学求某个学生（按学号）某门课（课程号）的分数。号）某门课（课程号）的分数。CREATE PROC SCORESNO VARCHAR(10),CNO VARCHAR(4)AS SELECT SCORE FROM S_C WHERE SNO=SNO AND CNO=CNO18:30创建存储过程创建存储过程调用时：调用时： 按预定义顺序提供参数；按预定义顺序提供参数； EXEC SCORE 0001,001查询学号为查询学号为0001，课程号为，课程号为001的成绩。的成绩。 按任意顺序提供参数（按名传递）；按任意顺序提供参数（按名传递）； EXEC SCORE CNO=001,SNO=0001EXEC SCORE SNO=0001,CNO=001EXEC SCORE 0001,CNO=001EXEC SCORE SNO=0001,001注：一旦一个参数用了按名传递，则其后所有的参数也必须都按名传递。注：一旦一个参数用了按名传递，则其后所有的参数也必须都按名传递。18:30创建存储过程创建存储过程例例:创建一个存储过程创建一个存储过程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=CNAME18:30创建存储过程创建存储过程EXEC SCORE1 马诚马诚,网络数据库网络数据库EXEC SCORE1 CNAME= 网络数据库网络数据库,SNAME=马诚马诚查询姓名为查询姓名为“马诚马诚”的学生的学生“网络数据库网络数据库”的成绩。的成绩。EXEC SCORE1 马诚马诚,CNAME=网络数据库网络数据库18:30创建存储过程创建存储过程参数有默认值的存储过程：参数有默认值的存储过程： 默认值是一个常量，存储过程的调用者没有默认值是一个常量，存储过程的调用者没有提供参数的时候，自动使用预先约定的默认值。提供参数的时候，自动使用预先约定的默认值。例：创建一个存储过程例：创建一个存储过程SCORE2，求某个学生求某个学生（按学号）某门课（课程号）的分数，其中默认课（按学号）某门课（课程号）的分数，其中默认课程为程为001。18:30创建存储过程创建存储过程CREATE PROC SCORE2SNO VARCHAR(10),CNO VARCHAR(4)=001AS SELECT SCORE FROM S_C WHERE SNO=SNO AND CNO=CNO18:30创建存储过程创建存储过程注：在调用带默认值参数的存储过程时注：在调用带默认值参数的存储过程时,具有默认值的参具有默认值的参数可以被重新指定新值，也可以被省略以使用其默认值。数可以被重新指定新值，也可以被省略以使用其默认值。EXEC SCORE2 0001,002EXEC SCORE2 0001EXEC SCORE2 SNO=0001在存储过程内部，在存储过程内部，SNO和和CNO分别为？分别为？18:30创建存储过程创建存储过程CREATE PROC SCORE3SNO VARCHAR(10)=0001,CNO VARCHAR(4)AS SELECT SCORE FROM S_C WHERE SNO=SNO AND CNO=CNO GOEXEC SCORE3 001EXEC SCORE3 CNO=001CNO VARCHAR(4),SNO VARCHAR(10)=0001正确？正确？如何改？如何改？18:30创建存储过程创建存储过程注：若要在一个存储过程中使用一个或者多个带默认值的注：若要在一个存储过程中使用一个或者多个带默认值的参数，则应将这些参数放到参数列表的最后。参数，则应将这些参数放到参数列表的最后。18:30创建存储过程创建存储过程带传出参数的存储过程：带传出参数的存储过程： 通过参数将值从存储过程带到调用程序，这通过参数将值从存储过程带到调用程序，这种参数传递方式称为种参数传递方式称为引用参数传递引用参数传递，这种参数称，这种参数称为为传出参数传出参数。 定义和调用传出参数时，必须带上定义和调用传出参数时，必须带上OUTP
