存储过程(SQLServer).ppt

存储过程,杨之江 ,内容,存储过程概念 创建和执行存储过程 从存储过程中返回数据 查看、修改、删除存储过程 存储过程的加密 存储过程的调试 重新编译存储过程 C#中调用存储过程 Q&A,T-SQL 来进行编程的两种方法,在利用数据库管理系统创建应用程序时，SQL语言是应用程序和数据库管理系统之间的主要编程接口。 使用SQL语言编写代码时，可以用两种方法存储和执行代码。 一种是在客户端存储代码，并创建向数据库管理系统发送SQL语句并处理返回结果的应用程序； 第二种是将这些SQL语句存储在数据库管理系统中，这些存储在数据库管理系统中的SQL语句就是存储过程，然后再创建执行存储过程并处理返回结果的应用程序。,1 存储过程概念,存储过程是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它 系统存储过程 系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。 用户自定义存储过程 用户自定义存储过程是由用户创建并能完成某一特定功能如查询用户所需数据信息的存储过程,存储过程可以,接受输入参数并以输出参数的形式将多个值返回给调用过程。 包含执行数据库操作（包括调用其它存储过程）的编程语句。 向调用过程返回状态值，以表明执行成功或失败（以及失败原因）,使用存储过程的好处,允许模块化程序设计 标准的编写规范 多次调用而不必重新编写该存储过程的SQL语句 对存储过程进行修改但对应用程序源代码毫无影响 提高了程序的可移植性 改善性能，执行速度快 存储过程是预编译的，而批处理的SQL 语句在每次运行时都要进行编译和优化因此速度相对要慢一些 减少网络流量 客户计算机上调用该存储过程时网络中传送的只是该调用语句，否则将是多条SQL 语句从而大大增加了网络流量，降低网络负载 可作为安全机制使用 系统管理员通过对执行某一存储过程的权限进行限制从而能够实现对相应的数据访问权限的限制避免非授权用户对数据的访问保证数据的安全,2 创建和执行存储过程,创建存储过程 CREATE PROC | PROCEDURE schema_name. procedure_name parameter type_schema_name. data_type VARYING = default OUTPUT ,.n WITH ,.n FOR REPLICATION AS ; .n ; := ENCRYPTION RECOMPILE ,2 创建和执行存储过程,执行存储过程 EXEC UTE 存储过程名 实参 , n ,注意事项,不能将CREATE PROCEDURE语句与其它SQL语句组合到一个批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守数据库对象命名规则。 只能在当前数据库中创建存储过程。,例1带有复杂 SELECT 语句的存储过程：对pubs数据库，查询居住在“Oakland”城市（authors表中的city列）的作者的姓名（au_lname列和au_fname列的值的组合）、所写的图书的书名(titles表中的title列)、图书出版日期（titles表中的pubdate列）以及出版商名（publishers表中的pub_name列）。,CREATE PROCEDURE p_city1 AS SELECT au_lname + + au_fname AS 作者名,title as 书名, pubdate as 出版日期,pub_name as 出版商 FROM authors join titleauthor ON authors.au_id = titleauthor.au_id JOIN titles ON titles.title_id = titleauthor.title_id JOIN publishers ON publishers.pub_id = titles.pub_id WHERE authors.city = Oakland 调用：EXEC p_city1,例2带有输入参数的存储过程：查询居住在指定城市的作者的姓名、所写的图书的书名、图书出版日期和出版商名,CREATE PROCEDURE p_city2 city as varchar(20) AS SELECT au_lname + + au_fname AS 作者名,title as 书名, pubdate as 出版日期,pub_name as 出版商 FROM authors join titleauthor ON authors.au_id = titleauthor.au_id JOIN titles ON titles.title_id = titleauthor.title_id JOIN publishers ON publishers.pub_id = titles.pub_id WHERE authors.city = city 调用：EXEC p_city2 Salt Lake City,例3带有多个输入参数并有默认值的存储过程：查询某个指定州（authors表中的state列）、电话号码（authors表中的phone列）前3位为指定数字的作者的姓名、州和电话号码，其中州的默认值为“CA”。,CREATE PROCEDURE p_state zh char(10)=CA,dh char(3) AS SELECT au_lname, au_fname, state, phone FROM authors WHERE state = zh and left(phone,3) = dh,参数的传递方式,按参数位置传递值 EXEC p_state ca, 408 按参数名传递值 EXEC p_state dh=408, zh=ca,例4带有多个输入参数并均指定默认值的存储过程。对pubs数据库的titles表，查询指定类型（type）以及价格（price）大于指定价格的图书的书号（title_id）、书名(title)、价格(price)和出版日期(pubdate)，其中类型的默认值为“business”，价格的默认值为15,CREATE PROCEDURE p_title type varchar(20)=business,price int = 15 AS SELECT title_id,title,type,price,pubdate FROM titles WHERE type = type and price = price,执行示例,执行1：不提供任何参数值。 EXEC p_title 执行2：提供全部参数值。 EXEC p_title psychology,10 执行3：只提供第二个参数的值。 EXEC p_title price = 10,例5带输出参数的存储过程。计算两个数的乘积，并将计算结果作为输出参数返回给调用者。,CREATE PROCEDURE p_multi var1 int, var2 int, var3 int output As Set var3 = var1 * var2 执行此存储过程： Declare res int EXEC p_multi 5,7,res output Print res,例6带输入参数和一个输出参数的存储过程。统计指定类型的图书的平均价格，并将统计的结果作为输出参数返回。,CREATE PROC p_AvgPrice type varchar(20), avg_price int output AS SELECT avg_price = AVG(price) FROM titles WHERE type = type 执行此存储过程： DECLARE ap int EXEC p_AvgPrice business, ap output PRINT ap,例7带有多个输入参数和多个输出参数的存储过程。统计指定类型和指定年份出版的图书的个数和平均价格，并将统计的结果作为输出参数返回。,CREATE PROC p_AvgCount type varchar(20), year int, total int output, avg_price int output AS SELECT avg_price = AVG(price), total = count(title_id) FROM titles WHERE type = type and year(pubdate) = year,执行例7存储过程,DECLARE c int, g int EXEC p_AvgCount trad_cook,1991, c output, g output print c print g 执行结果为：3 16,例8建立修改数据的存储过程。对pubs数据库的titles表，将指定类型的图书的价格进行适当增加，增加的规则：如果价格高于20，则加价10%；如果价格在1020之间，则加价20%，否则加价30%。,CREATE PROCEDURE p_update type char(20) AS UPDATE titles SET price = price * case when price 20 then 1.1 when price between 10 and 20 then 1.2 else 1.3 end WHERE type = type,例9建立删除数据的存储过程。对pubs数据库的titles表，删除指定类型的图书中价格最低的图书记录。,CREATE PROCEDURE p6 type char(20) AS DELETE FROM titles WHERE price = (SELECT min(price) FROM titles WHERE type = type) and type = type,3 从存储过程中返回数据,存储过程以下列四种形式返回数据： 输出参数，既可以返回数据，也可以返回游标变量。 返回代码，始终是整型值。 SELECT 语句的结果集。 可从存储过程外引用的全局游标。,使用返回代码返回数据,使用RETURN 语句指定存储过程的返回代码， 执行存储过程时必须将返回代码保存到变量中，以便在调用程序中使用返回代码值。 例如： DECLARE result int EXECUTE result = my_proc,例1对pubs数据库的titles表，建立查询指定类型的图书的书名和价格的存储过程，如果用户没有指定类型，则在屏幕上显示“请指定一个图书类型”，并从存储过程中退出。如果用户指定了图书类型，则执行相应的查询。,CREATE PROCEDURE p_FindTitle type varchar(20) = NULL AS IF type IS NULL BEGIN PRINT 必须指定一个图书类型 RETURN END ELSE BEGIN SELECT title,price FROM titles WHERE type = type END,例2. 有返回状态代码的存储过程。对pubs数据库的authors表，检查用户指定的作者（作者编号）所在的州。如果所在的州是加利福尼亚州 (CA)，将返回状态代码“1”。否则，对于任何其它情况均返回状态代码“2”,CREATE PROCEDURE p_CheckState au_id varchar(12) AS IF (SELECT state FROM authors WHERE au_id = au_id) = CA RETURN 1 ELSE RETURN 2,例3根据各种错误设置不同的返回代码值。对Pubs数据库的titles表，查询指定图书编号（title_id）的图书的销售量，根据每种可能的错误赋予的返回代码的值如表所示。,例3代码,CREATE PROCEDURE p_GetSales title_id varchar(80) = NULL, ytd_sales int OUTPUT AS - 验证title参数的有效性 IF title_id IS NULL BEGIN PRINT 错误: 必须指定一个书名！ RETURN(1) END ELSE BEGIN - 确保书号是有效的 IF (SELECT COUNT(*) FROM titles WHERE title_id = title_id) = 0 RETURN(2) END,例3代码（续）,- 得到指定图书的销售量并将此值赋给输出参数 SELECT ytd_sales = ytd_sales FROM titles WHERE title_id = title_id - 检查SQL Server错误 IF ERROR 0 BEGIN RETURN(3) END ELSE BEGIN - 查看ytd_sales 值是否为NULL IF ytd_sales IS NULL RETURN(4) ELSE RETURN(0) - 成功! END,执行例3代码,DECLARE ytd_sales_for_title int, ret_code INT EXECUTE ret_code = p_GetSales BU1032, ytd_sales = ytd_sales_for_title OUTPUT - 检查返回代码 IF ret_code = 0 BEGIN PRINT 存储过程成功执行！ - 显示存储过程的返回值. PRINT “BU1032”的销售量是: + CONVERT(varchar(6),ytd_sales_for_title) END ELSE IF ret_code = 1 PRINT 错误: 未指定title_id。 ELSE IF ret_code = 2 PRINT 错误: 指定的title_id无效。 ELSE IF ret_code = 3 PRINT 错误: 得到销售量时出错！ ELSE IF ret_code = 4 PRINT 错误: 销售量为空！,4 查看、修改、删除存储过程,4.1查看存储过程 4.2修改存储过程 4.3重命名存储过程 4.4删除存储过程,4.1查看存储过程,（1）使用SQL Server管理平台查看用户创建的存储过程。 在SQL Server管理平台中，展开指定的服务器和数据库，选择并依次展开“程序存储过程”，然后右击要查看的存储过程名称，如图所示，从弹出的快捷菜单中，选择“创建存储过程脚本为CREATE到新查询编辑器窗口”，则可以看到存储过程的源代码,4.1查看存储过程,（2)使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及其语法形式如下： sp_help，用于显示存储过程的参数及其数据类型，其语法为： sp_help objname= name，参数name为要查看的存储过程的名称。 sp_helptext，用于显示存储过程的源代码，其语法为： sp_helptext objname= name，参数name为要查看的存储过程的名称。 sp_depends，用于显示和存储过程相关的数据库对象，其语法为： sp_depends objname=object，参数object为要查看依赖关系的存储过程的名称。 sp_stored_procedures，用于返回当前数据库中的存储过程列表，其语法为： sp_stored_proceduressp_name=name ,sp_owner=owner ,sp_qualifier = qualifier 其中，sp_name = name 用于指定返回目录信息的过程名；sp_owner = owner 用于指定过程所有者的名称；qualifier = qualifier 用于指定过程限定符的名称,4.2修改存储过程,语法格式： ALTER PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,4.3重命名存储过程,修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下： sp_rename 原存储过程名称，新存储过程名称 另外，通过SQL Server管理平台也可以修改存储过程的名称。在SQL Server管理平台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称,4.4删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下： drop procedure procedure,n 利用SQL Server管理平台删除存储过程 在SQL Server管理平台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，则会弹出除去对象对话框，在该对话框中，单击“确定”按钮，即可完成删除操作。单击“显示相关性”按钮，则可以在删除前查看与该存储过程有依赖关系的其他数据库对象名称,使用T-SQL语句删除存储过程,if exists select name from sysobjects where name=author_infor and type=p drop procedure author_infor,5 存储过程的加密,WITH ENCRYPTION 示例： CREATE PROCEDURE p_CheckState au_id varchar(12) WITH ENCRYPTION AS IF (SELECT state FROM authors WHERE au_id = au_id) = CA RETURN 1 ELSE RETURN 2,6 存储过程的调试,SQL Server 2000可以让我们在查询分析器中直接调试存储过程 SQL Server 2005，这个功能被转移到了SQL Servr Management Studio和Visual Studio IDE内。 使用这个技术，我们可以在Visual Studio中一步一步地调试存储过程。 而且你也可以在存储过程中设置断点，这样当你调试你的程序时，这些断点就会生效 所有的数据库调试都发生在Visual Studio IDE的内部。 数据库中的像存储过程、触发器和用户自定义函数（UDFs）这样的对象都是可以被调试的,Visual Studio提供的调试方法,数据库中直接调试 在Visual Studio的服务器资源管理器中，右键单击某个数据库对象，然后选择“单步执行”。 例如，当你右键单击一个存储过程的时候，弹出菜单中就会包含一个“单步执行存储过程”的选项。 应用程序调试 在数据库对象内设置断点，然后通过应用程序进行调试。 当相关的ASP.NET程序处于调试状态，并且调用了数据库对象的时候，那么Visual Studio就会暂停在你所设置的断点之处，从而允许我们一步一步地对T-SQL语句进行调试。 在SQL Server项目中调试 在Visual Studio中可以创建一个SQL Server项目。 该项目中可以包含T-SQL和数据库对象，并且这些数据库对象都可以通过SQL Server项目本身来调试。,调试远程数据库,调试远程数据库最关键的要点在于，要确保启动Visual Studio的Windows用户与连接远程数据库的用户是同一个用户。 此外，该用户必须隶属于sysadmin角色。 例如，你可以在Windows的域中创建一个名为“TestDebug”的用户。 然后使其可以登录到远程SQL Server数据库，并将该用户添加到sysadmin角色内。 另外，你还需要使用“TestDebug”用户来登录你的电脑，或者确保使用runas.exe命令启动的Visual Studio运行在“TestDebug”用户下。 接下来所介绍的SQL Server调试是基于本地数据库的。 当然，无论是本地数据库还是远程数据库都是可以调试的。 本地数据库指的是数据库部署在本机上；远程数据库指的是数据库部署在本机之外的机器上。 调试本地数据库不需要做额外的设置， 但是调试远程数据库却要复杂得多,7重新编译存储过程,在执行诸如添加索引或更改索引列中的数据等操作而更改了数据库时，应重新编译访问数据库表的原始查询计划以对其重新优化 SQL Server 2005中，强制重新编译存储过程的方式有以下3种。 （1）sp_recompile系统存储过程强制在下次执行存储过程时对其重新编译。 （2）创建存储过程时在其定义中指定WITH RECOMPILE选项，指明SQL Server将不为该存储过程缓存计划，在每次执行该存储过程时对其重新编译。 （3）可以通过指定WITH RECOMPILE选项，强制在执行存储过程时对其重新编译。,8 C#中调用存储过程,1 建立存储过程 CREATE PROCEDURE testc ProductName varchar(50)=, output varchar(200) output AS -select结果集 if(ProductName= Or ProductName=null) select top 10 * from Products else select * from Products where ProductName like %+ProductName+% -对输出参数进行赋值 set output=记录总数：+convert(varchar(10),ROWCOUNT) -使用return，给存储过程一个返回值。 return 200; GO,8 C#中调用存储过程,存储过程的使用标准版 private void sql_proc() SqlConnection conn = new SqlConnection(“server=.;uid=sa;pwd=sa;database=Northwind“); string sql = “protestc“; SqlCommand comm = new SqlCommand(sql, conn); /把Command执行类型改为存储过程方式，默认为Text。 comm.CommandType = CommandType.StoredProcedure; /传递一个输入参数，需赋值 SqlParameter sp = comm.Parameters.Add(“ProductName“, SqlDbType.VarChar); sp.Value = “Chef“; /定义一个输出参数，不需赋值。Direction用来描述参数的类型 /Direction默认为输入参数，还有输出参数和返回值型。 sp = comm.Parameters.Add(“output“, SqlDbType.VarChar, 50); sp.Direction = ParameterDirection.Output; /定义过程的返回值参数，过程执行完之后，将把过程的返回值赋值给名为myreturn的Paremeters赋值。 sp = comm.Parameters.Add(“myreturn“, SqlDbType.Int); sp.Direction = ParameterDirection.ReturnValue;,/使用SqlDataAdapter将自动完成数据库的打开和关闭过程，并执行相应t-sql语句或存储过程 /如果存储过程只是执行相关操作，如级联删除或更新，使用SqlCommand的execute方法即可。 SqlDataAdapter da = new SqlDataAdapter(comm