/* 存储过程可以看作是在数据库中的存储t-sql脚本 为什么使用存储过程 1、增加性能 本地存储发送的内容少、调用快、预编译、高速缓存 一般语句的执行：检查权限、检查语法，建立执行计划处理语句的要求 存储过程：创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译； 再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划 2、增强安全 加密、分离（权限设置，用户只需要有执行存储过程的权限，不需要有访问存储过程所使用的对象的权限） 3、在transact-sql中使用非数据库技术 dll 4、编程模式——使用外部编程语言调用 1）input 2）output 3）feedback 状态代码或描述性的文本 4）模块化、可重用、可调用其他存储过程 5）隐藏程序逻辑，便于编程 6）可以调用动态连接库（外接的程序） 基本原则：越简单越好 单一任务 */ /* 分类 1、系统存储过程 存在于master数据库，一般以sp_开头 提供对系统表格数据调用、数据库管理功能、安全管理功能的支持 --表格授权 use pubs go execute sp_table_privileges stores --显示kylinadministrator的所有进程 execute sp_who @loginame='W2K3SERVERAdministrator' --报告有关孤立的 microsoft windows nt 用户和组的信息，这些用户和组已不在 windows nt 环境中，但仍在 microsoft sql server系统表中拥有项。 execute sp_validatelogins 2、本地存储过程 用户创建的解决特定问题的 3、临时存储过程 存储于tempdb 创建、调用时的数据库 使用范围 生存周期 #local 不限数据库 创建时的连接有效 从创建时开始，当创建的连接中断时消失 ##global 不限数据库 所有连接 从创建时开始，当创建的连接中断时消失 直接创建在tempdb的存储过程 tempdb 所有连接 从创建时开始，当数据库服务器服务停止时消失 create proc #local as select '#local' go exec #local go create proc ##global as select '##global' go exec ##global go use tempdb go create procedure directtemp as select * from [pubs].[dbo].[authors] go use northwind go exec tempdb.dbo.directtemp 4、扩展存储过程 c++ xp xp_sendmail既是系统存储过程，也是扩展存储过程 使用objectproperty来判断是否是扩展存储过程 use master --扩展存储过程 select objectproperty(object_id('sp_prepare'), 'isextendedproc') --非扩展存储过程 select objectproperty(object_id('xp_logininfo'), 'isextendedproc') 5、远程存储过程 目前版本中只是为了向后兼容，已被分布式查询替代 */ /* 存储过程在数据库中如何存储 名字 sysobjects 文本 syscomments */ /* 练习1：通过查询分析器中的对象查看器查看存储过程 */ /* 练习2：查看存储过程的内容 图形 语句 */ select * from sysobjects select * from syscomments go select * from syscomments where id = object_id('custorderhist') go select name,text from sysobjects inner join syscomments on sysobjects.id = syscomments.id where sysobjects.name = 'custorderhist' go sp_helptext sp_helptext go use northwind go exec sp_help custorderhist exec sp_helptext custorderhist exec sp_depends custorderhist exec sp_stored_procedures 'custorderhist' /* 系统存储过程 以使用为主 */ /* 本地存储过程的创建、修改、删除 1、t-sql语句 create procedure alter procedure drop procedure create procedure 存储过程名字 as 存储过程文本 go alter procedure 存储过程名字 as 存储过程文本 go drop procedure 存储过程名字 2、企业管理器 右键 向导 */ /* 简单 */ -- -- -- select top 1 * from products -- -- -- select top 1 * from orders -- -- -- select top 1 * from [order details] /*1、和视图比较*/ alter proc sp_qry_salesdetails as select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格, b.unitprice*b.quantity as 金额,c.requireddate as 销售时间 from [order details] as b join products as a on b.productid=a.productid join orders as c on b.orderid=c.orderid go print '测试' execute sp_qry_salesdetails --递归算法 --视图 存储过程 函数 alter view v_qry_salesdetails as select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格, b.unitprice*b.quantity as 金额,c.requireddate as 销售时间 from [order details] as b join products as a on b.productid=a.productid join orders as c on b.orderid=c.orderid print '测试' select * from v_qry_salesdetails /* 默认情况下第一次执行时的执行计划被保存，以后执行时都是用这个执行计划，直到服务器重启或存储过程使用的表格变化时 当存储过程变化时，如：参数变化，需要重新编译、制定新的执行计划 当每次调用存储过程时强制重新编译的方法： 1、创建时指定 with recompile 2、sp_recompile */ create procedure sp1 as select * from customers exec sp1 alter procedure sp1 as select * from customers alter procedure sp1 with recompile as select * from customers sp_recompile sp1 --加密存储过程 with encryption select objectproperty(object_id('sp_qry_salesdetails'), 'isencrypted') /* 删除存储过程 drop proc */ use northwind go create proc dbo.sp_dropproc as select 'northwind.dbo.sp_dropproc' go exec northwind.dbo.sp_dropproc go use master go create proc dbo.sp_dropproc as select 'master.dbo.sp_dropproc' go exec master.dbo.sp_dropproc go use northwind go drop proc sp_dropproc go exec sp_dropproc exec master.dbo.sp_dropproc /* 提供输入参数 input */ create proc qry_salesdetails @y int,@m int --varchar(10) as select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间 from [order details] as b join products as a on b.productid=a.productid join orders as c on b.orderid=c.orderid --where convert(varchar(2),month(c.requireddate)) = @m where year(c.requireddate) = @y and month(c.requireddate) = @m go exec qry_salesdetails 1996,9 exec qry_salesdetails 9,1996 exec qry_salesdetails @m=9,@y=1996 exec qry_salesdetails @y=1996,@m=9 go /* northwind 数据库 orders order details 表格 * 根据指定用户ID显示此用户在1996-07-01到1997-07-01之间的订货记录 要求存储过程文本加密 */ use northwind go --创建存储过程 -- drop proc qry_showorders create proc qry_showorders @custid nchar(5) with encryption --加密 as if @custid is null -- begin -- print '提供了不正确的参数' -- return -- end select * from orders od inner join [order details] oddt on od.orderid = oddt.orderid where shippeddate >='1996-07-01' and shippeddate 0 begin print '有错误' set @e = @@error end return @e go declare @er int exec @er = testerror select @er /* @@rowcount */ select @@rowcount select * from customers select @@rowcount /* null 值 */ create proc testreturn @a int as if @a is null begin return(100) end else if @a=@start go exec qry_salesdetails 6,'1996-01-01','1997-01-01' alter proc qry_salesdetails @no int = -1,@start char(10),@end char(10) as declare @sql varchar(4000) set @sql = 'select a.productid as 商品编号,a.productname as 商品名称, b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额, c.requireddate as 销售时间 from [order details] as b join products as a on b.productid=a.productid join orders as c on b.orderid=c.orderid where 1=1 ' if @no is not null set @sql = @sql + ' and a.productid = '+convert(varchar(10),@no) if @start is not null and @end is not null set @sql = @sql + ' and c.requireddate >= '''+ @start+'''' + ' and c.requireddate <p>更多sql的相关技术文章，请访问<a href="http://www.php.cn/sql/" target="_self" style="color: rgb(146, 208, 80); text-decoration: underline;"><span style="color: rgb(146, 208, 80);">sql教程</span></a><span style="color: rgb(146, 208, 80);"></span>栏目进行学习！</p>
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
