存储过程的使用
- 格式：doc
- 大小：52.00 KB
- 文档页数：5
存储过程
一、创建存储过程
创建存储过程的步骤：
1、 编写Transact_SQL语句；
2、 测试Transact_SQL语句
3、 若得到所需结果。则创建存储过程
4、 执行过程
/*创建存储过程 */
CREATE PROCEDURE my_procedure1
AS
select *
from orders
where orderdate>='1/1/1998' and shipcountry='USA'
GO
/* 执行存储过程 */
use northwind
go
exec my_procedure1
/* */
创建存储过程的注意事项：
1、 不要将CREATE PROCEDURE 语句与其他SQL语句组合到单个批处理中，每一个批处理就是一个go语句段
2、 具有操作权限的用户才能创建存储过程，默认为数据库所有者
3、 存储过程是个数据库对象，命名必须符合标识符规则，在命名自定义的存储过程时避免使用sp前缀，以免与系统存储过程混淆
4、 存储过程只能在当前数据库创建，最大尺寸为128MB，可以嵌套，至多嵌套32级。
二、查看存储过程的内容
1、 执行系统存储过程sp_stored_procedures来查看数据库中所有的存储过程及其所有者的列表。
2、 到企业管理器中查看
3、 执行系统存储过程sp_helptext,sp_help,sp_depends 来查看。
/*******************************/
USE Northwind
EXEC sp_stored_procedures
Go
/*******************************/
USE Northwind
EXEC sp_helptext my_procedure1 go
/*******************************/
三、创建含有输入参数的存储过程
在存储过程中可以声明一个或多个变量作为参数
声明输入参数的格式：
@parameter_name datatype[=default]
/*******************************/
USE Northwind
Go
Create proc my_procedure2
@startdate datetime,@enddate datetime,@country varchar(20)='USA'
AS
If (@startdate IS NULL or @enddate IS NULL or @country IS NULL)
BEGIN
Raiserror('NULL values are invalid',5,5)
RETURN
END
Select * from orders
Where orderdate between @startdate and @enddate and shipcountry=@country
Go
/*******************************/
四、执行含有输入参数的存储过程
1、使用参数名传递参数值
语法格式：
EXEC procedure_name
[@parameter_name=value][,…n]
/*******************************/
USE Northwind
Go
EXEC my_procedure2
@enddate='2/1/1999',
@startdate='7/1/1998'
/*******************************/
2、按位置来传递参数值
语法格式：
EXEC procedure_name
[value1,value2,…]
/*******************************/
USE Northwind Go
EXEC my_procedure2
'2/1/1998','7/1/1997','USA'
/*******************************/
五、创建含有输出参数的存储过程
语法格式：
@parameter_name datatype[=default] OUTPUT
/*******************************/
USE Northwind
Go
create procedure my_procedure3
@startdate datetime,@enddate datetime,@country varchar(20)='USA',
@recordcount int OUTPUT
AS
IF @startdate IS NULL Or @enddate IS NULL or @country IS NULL
BEGIN
raiserror ('NULL value are invalid',5,5)
return
END
select * from orders
where orderdate between @startdate and @enddate and shipcountry=@country
select @recordcount=@@ROWCOUNT
/*******************************/
六、执行含有输出参数的存储过程
/*******************************/
USE Northwind
Go
DECLARE @recordnumber int
EXEC my_procedure3
'7/1/1997','2/1/1998','Germany',@recordnumber OUTPUT
PRINT 'The order count is :'+str(@recordnumber)
go
/*******************************/
七、修改存储过程
/*******************************/
USE Northwind
Go ALTER proc my_procedure1
WITH RECOMPILE,ENCRYPTION /* RECOMPILE 为存储过程指定重编译，ENCRYPTION 对包含alter proc 文本的syscomments表中的项进行加密 */
AS
select orderid,customerid,orderdate,shipcountry
from orders
where orderdate>='1/1/1998' and shipcountry='USA'
/*******************************/
八、删除存储过程
/*******************************/
USE Northwind
Go
DROP PROC my_procedure4
/*******************************/
九、重编译存储过程
SQLServer在创建存储过程时要进行语法检查，若语法正确，则存储过程的文本将保存在syscoments系统表中，在执行存储过程时要分析该存储过程并创建执行计划，并把执行计划置于内存中，当用户改变表内数据时，可能导致原来的执行计划效率低或不可用，因此需要重新进行编译。
1、在建立存储过程时设定重编译选项
格式：
CREARE PEROCEDURE procedure_name [ WITH RECOMPILE]
/*******************************/
USE Northwind
go
CREATE PROCEDURE my_procedure1
WITH RECOMPILE
AS
select *
from orders
where orderdate>='1/1/1998' and shipcountry='USA'
GO
/*******************************/
2、在执行存储过程时设定重编译选项
procedure_name [PARAMETER] [WITH RECOMPILE]
/*******************************/
EXEC my_procedure1 WITH RECOMPILE
/*******************************/
3、通过系统存储过程来设定重编译选项 EXEC sp_recompile
/* OBJECT 为当前数据库中的存储过程、触发、*/
/*******************************/
EXEC sp_recompile Order /*表名*/
/*******************************/
十、系统存储过程和扩展存储过程
系统存储过程在master数据库中创建的，由系统管理员拥有，以sp_开头的存储过程，目的是为用户提供方便，并使用户很容易从系统表中取出信息、管理数据库，并执行更新系统表等任务。
举例：
Sp_helpsql 显示关于SQL语句，存储过程和其他对象的信息
Sp_help
提供关于存储过程或其他数据库对象的报告
Sp_helptext
显示存储过程和其他对象的文本
Sp_depends
列举引用或依赖指定对象的所有存储过程
/*******************************/
use northwind
go
exec Sp_depends orders
/*******************************/
扩展存储过程提供一种方法，动态装入和执行动态连接库内的函数，无缝地扩展SQL Server
的功能。
举例：
Xp_loginconfig 报告服务器的登录安全性配置
Xp_cmdshell 为操作系统外壳执行指定的命令串，并以文本的形式返回输出
/*******************************/
EXEC master ..xp_cmdshell "dir *.exe" /* 列出C:\WINNT\system32 的目录下文件的内容*/
/*******************************/
use master
go
EXEC xp_loginconfig
/*******************************/
