07-存储过程(SQL_Server)

存储过程杨之江[e-mail]内容存储过程概念创建和执行存储过程从存储过程中返回数据查看、修改、删除存储过程存储过程的加密存储过程的调试重新编译存储过程C#中调用存储过程Q&AT-SQL来进行编程的两种方法在利用数据库管理系统创建应用程序时，SQL语言是应用程序和数据库管理系统之间的主要编程接口。使用SQL语言编写代码时，可以用两种方法存储和执行代码。一种是在客户端存储代码，并创建向数据库管理系统发送SQL语句并处理返回结果的应用程序；第二种是将这些SQL语句存储在数据库管理系统中，这些存储在数据库管理系统中的SQL语句就是存储过程，然后再创建执行存储过程并处理返回结果的应用程序。1存储过程概念存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它系统存储过程系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。用户自定义存储过程用户自定义存储过程是由用户创建并能完成某一特定功能如查询用户所需数据信息的存储过程存储过程可以接受输入参数并以输出参数的形式将多个值返回给调用过程。包含执行数据库操作（包括调用其它存储过程）的编程语句。向调用过程返回状态值，以表明执行成功或失败（以及失败原因）使用存储过程的好处允许模块化程序设计标准的编写规范多次调用而不必重新编写该存储过程的SQL语句对存储过程进行修改但对应用程序源代码毫无影响提高了程序的可移植性改善性能，执行速度快存储过程是预编译的，而批处理的SQL语句在每次运行时都要进行编译和优化因此速度相对要慢一些减少网络流量客户计算机上调用该存储过程时网络中传送的只是该调用语句，否则将是多条SQL语句从而大大增加了网络流量，降低网络负载可作为安全机制使用系统管理员通过对执行某一存储过程的权限进行限制从而能够实现对相应的数据访问权限的限制避免非授权用户对数据的访问保证数据的安全2创建和执行存储过程创建存储过程CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][OUTPUT]][,...n][WITHprocedure_option[,...n]][FORREPLICATION]AS{sql_statement[;][...n]}[;]procedure_option::=[ENCRYPTION][RECOMPILE]2创建和执行存储过程执行存储过程[EXEC[UTE]]存储过程名[实参[,…n]]注意事项不能将CREATEPROCEDURE语句与其它SQL语句组合到一个批处理中。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守数据库对象命名规则。只能在当前数据库中创建存储过程。例1．带有复杂SELECT语句的存储过程：对pubs数据库，查询居住在“Oakland”城市（authors表中的city列）的作者的姓名（au_lname列和au_fname列的值的组合）、所写的图书的书名(titles表中的title列)、图书出版日期（titles表中的pubdate列）以及出版商名（publishers表中的pub_name列）。CREATEPROCEDUREp_city1ASSELECTau_lname+''+au_fnameAS作者名,titleas书名,pubdateas出版日期,pub_nameas出版商FROMauthorsjointitleauthorONauthors.au_id=titleauthor.au_idJOINtitlesONtitles.title_id=titleauthor.title_idJOINpublishersONpublishers.pub_id=titles.pub_idWHEREauthors.city='Oakland'调用：EXECp_city1例2．带有输入参数的存储过程：查询居住在指定城市的作者的姓名、所写的图书的书名、图书出版日期和出版商名CREATEPROCEDUREp_city2@cityasvarchar(20)ASSELECTau_lname+''+au_fnameAS作者名,titleas书名,pubdateas出版日期,pub_nameas出版商FROMauthorsjointitleauthorONauthors.au_id=titleauthor.au_idJOINtitlesONtitles.title_id=titleauthor.title_idJOINpublishersONpublishers.pub_id=titles.pub_idWHEREauthors.city=@city调用：EXECp_city2'SaltLakeCity'例3．带有多个输入参数并有默认值的存储过程：查询某个指定州（authors表中的state列）、电话号码（authors表中的phone列）前3位为指定数字的作者的姓名、州和电话号码，其中州的默认值为“CA”。CREATEPROCEDUREp_state@zhchar(10)='CA',@dhchar(3)ASSELECTau_lname,au_fname,state,phoneFROMauthorsWHEREstate=@zhandleft(phone,3)=@dh参数的传递方式按参数位置传递值EXECp_state'ca','408'按参数名传递值EXECp_state@dh='408',@zh='ca'例4．带有多个输入参数并均指定默认值的存储过程。对pubs数据库的titles表，查询指定类型（type）以及价格（price）大于指定价格的图书的书号（title_id）、书名(title)、价格(price)和出版日期(pubdate)，其中类型的默认值为“business”，价格的默认值为15CREATEPROCEDUREp_title@typevarchar(20)='business',@priceint=15ASSELECTtitle_id,title,type,price,pubdateFROMtitlesWHEREtype=@typeandprice=@price执行示例执行1：不提供任何参数值。EXECp_title执行2：提供全部参数值。EXECp_title'psychology',10执行3：只提供第二个参数的值。EXECp_title@price=10例5．带输出参数的存储过程。计算两个数的乘积，并将计算结果作为输出参数返回给调用者。CREATEPROCEDUREp_multi@var1int,@var2int,@var3intoutputAsSet@var3=@var1*@var2执行此存储过程：Declare@resintEXECp_multi5,7,@resoutputPrint@res例6．带输入参数和一个输出参数的存储过程。统计指定类型的图书的平均价格，并将统计的结果作为输出参数返回。CREATEPROCp_AvgPrice@typevarchar(20),@avg_priceintoutputASSELECT@avg_price=AVG(price)FROMtitlesWHEREtype=@type执行此存储过程：DECLARE@apintEXECp_AvgPrice'business',@apoutputPRINT@ap例7．带有多个输入参数和多个输出参数的存储过程。统计指定类型和指定年份出版的图书的个数和平均价格，并将统计的结果作为输出参数返回。CREATEPROCp_AvgCount@typevarchar(20),@yearint,@totalintoutput,@avg_priceintoutputASSELECT@avg_price=AVG(price),@total=count(title_id)FROMtitlesWHEREtype=@typeandyear(pubdate)=@year执行例7存储过程DECLARE@cint,@gintEXECp_AvgCount'trad_cook',1991,@coutput,@goutputprint@cprint@g执行结果为：316例8．建立修改数据的存储过程。对pubs数据库的titles表，将指定类型的图书的价格进行适当增加，增加的规则：如果价格高于20，则加价10%；如果价格在10～20之间，则加价20%，否则加价30%。CREATEPROCEDUREp_update@typechar(20)ASUPDATEtitlesSETprice=price*casewhenprice20then1.1whenpricebetween10and20then1.2else1.3endWHEREtype=@type例9．建立删除数据的存储过程。对pubs数据库的titles表，删除指定类型的图书中价格最低的图书记录。CREATEPROCEDUREp6@typechar(20)ASDELETEFROMtitlesWHEREprice=(SELECTmin(price)FROMtitlesWHEREtype=@type)andtype=@type3从存储过程中返回数据存储过程以下列四种形式返回数据：输出参数，既可以返回数据，也可以返回游标变量。返回代码，始终是整型值。SELECT语句的结果集。可从存储过程外引用的全局游标。使用返回代码返回数据使用RETURN语句指定存储过程的返回代码，执行存储过程时必须将返回代码保存到变量中，以便在调用程序中使用返回代码值。例如：DECLARE@resultintEXECUTE@result=my_proc例1．对pubs数据库的titles表，建立查询指定类型的图书的书名和价格的存储过程，如果用户没有指定类型，则在屏幕上显示“请指定一个图书类型”，并从存储过程中退出。如果用户指定了图书类型，则执行相应的查询。CREATEPROCEDUREp_FindTitle@typevarchar(20)=NULLASIF@typeISNULLBEGINPRINT'必须指定一个图书类型'RETURNENDELSEBEGINSELECTtitle,priceFROMtitlesWHEREtype=@typeEND例2.有返回状态代码的存储过程。对pubs数据库的authors表，检查用户指定的作者（作者编号）所在的州。如果所在的州是加利福尼亚州(CA)，将返回状态代码“1”。否则，对于任何其它情况均返回状态代码“2”CREATEPROCEDUREp_CheckState@au_idvarchar(12)ASIF(SELECTstateFROMauthorsWHEREau_id=@au_id)='CA'RETURN1ELSERETURN2例3．根据各种错误设置不同的返回代码值。对Pubs数据库的titles表，查询指定图书编号（title_id）的图书的销售量，根据每种可能的错误赋予的返回代码的值如表所示。值含义0成功执行。1未指定所需的参数值。2指定的参数值无效。3获得销售值时出现错误。4发现销售值为NULL的书名。例3代码CREATEPROCEDUREp_GetSales@title_idvarchar(80)=NULL,@ytd_salesintOUTPUTAS--验证@title参数的有效性IF@title_idISNULLBEGINPRINT'错误:必须指定一个书名！'RETURN(1)ENDELSEBEGIN--确保书号是有效的IF(SELECTCOUNT(*)FROMtitlesWHEREtitle