T-SQL存储过程

存储过程(stored procedure)有时也称sproc，它是真正的脚本，更准确地说，它是批处理(batch)，但都不是很确切，它存储与数据库而不是单独的文件中。

存储过程中有输入参数，输出参数以及返回值等。

TestTalb

CREATE TABLE [dbo].[TrackLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FullName] [varchar](200) NOT NULL,
    [MethodName] [varchar](50) NOT NULL,
    [ExecuteTime] [time](3) NULL,
    [RowDate] [datetime] NULL,
    [Status] [varchar](50) NULL,
    [Message] [text] NULL,
    [ClientIP] [varchar](50) NULL,
    [CityCode] [varchar](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Test数据语句（Sql数据导出成脚本文件方法参考：http://jingyan.baidu.com/article/2a138328b594ea074a134f0c.html）

INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (1, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x03A0000000000000 AS Time), CAST(0x0000A65E00F637A7 AS DateTime), N'OK', N'', N'::1', N'0755')
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (2, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x0300000000000000 AS Time), CAST(0x0000A65E00F68B6C AS DateTime), N'OK', N'', N'::1', N'0755')
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (3, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x0300000000000000 AS Time), CAST(0x0000A65E00F68DBF AS DateTime), N'OK', N'', N'::1', N'0755')
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (4, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x0300000000000000 AS Time), CAST(0x0000A65E00F68F63 AS DateTime), N'OK', N'', N'::1', N'0755')
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (5, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x0300000000000000 AS Time), CAST(0x0000A65E00F6903F AS DateTime), N'OK', N'', N'::1', N'0755')
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (6, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x03C4000000000000 AS Time), CAST(0x0000A65E00F9927A AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (7, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x0300000000000000 AS Time), CAST(0x0000A65E00F9AA2F AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (8, N'CentaNet.Mobile.Model.Request.GetBizGlobalRequest', N'GetBizGlobalRequest', CAST(0x03AB310000000000 AS Time), CAST(0x0000A65E00F9DCB8 AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (9, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x03DC000000000000 AS Time), CAST(0x0000A65E01219AAC AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (10, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x0309030000000000 AS Time), CAST(0x0000A65E0123B6E8 AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (11, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x03A6000000000000 AS Time), CAST(0x0000A65E01242AEC AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (12, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x03F6010000000000 AS Time), CAST(0x0000A65E01256628 AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (13, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x0305020000000000 AS Time), CAST(0x0000A65E0125C3E8 AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (14, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x039D000000000000 AS Time), CAST(0x0000A65E0125E5E4 AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (15, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x039D000000000000 AS Time), CAST(0x0000A65E01261398 AS DateTime), N'OK', N'', N'::1', NULL)
INSERT [dbo].[TrackLog] ([Id], [FullName], [MethodName], [ExecuteTime], [RowDate], [Status], [Message], [ClientIP], [CityCode]) VALUES (16, N'CentaNet.Mobile.Model.Request.GetRegionEstatesRequest', N'GetRegionEstatesRequest', CAST(0x033C0E0000000000 AS Time), CAST(0x0000A65E015A71E3 AS DateTime), N'OK', N'', N'::1', NULL)

创建存储过程

　CREATE PROCEDURE|PROC <sproc name>
　　[<parameter name> [schema.] <data type> [VARYING] [=<default value>] [OUT[PUT]] [READONLY]
　　[,<parameter name> [schema.] <data type> [VARYING] [=<default value>] [OUT[PUT]] [READONLY]
　　[,...
　　...    
　　]]
　　[WITH 
　　RECOMPILE | ENCRYPTION | [EXECUTE AS { CALLER | SELF | OWNER | <'user name'>}]
　　AS
　　<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

简单的存储过程创建语句

 CREATE PROCEDURE TEST_proc
 AS
SELECT * FROM [Test].[dbo].[TrackLog]

存储过程的执行

EXEC TEST_proc

存储过程的修改

ALTER PROCEDURE|PROC <sproc name>
　　[<parameter name> [schema.] <data type> [VARYING] [=<default value>] [OUT[PUT]] [READONLY]
　　[,<parameter name> [schema.] <data type> [VARYING] [=<default value>] [OUT[PUT]] [READONLY]
　　[,...
　　...    
　　]]
　　[WITH 
　　RECOMPILE | ENCRYPTION | [EXECUTE AS { CALLER | SELF | OWNER | <'user name'>}]
　　AS
　　<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

存储过程的删除

DROP proc|procedure TEST_proc

存储过程参数化

1、声明参数

参数一般包含名称数据类型默认值方向 @paremeter_name as datatype [default|null] [output|out]

-----------创建带带参数的存储过程
CREATE PROCEDURE TEST_proc
@sp_name nvarchar(50)
 AS 
SELECT * FROM [Test].[dbo].[TrackLog] where FullName like @sp_name+'%'

-----------执行带参数的存储过程
EXEC TEST_proc 'CentaNet.Mobile';

2、提供默认值

CREATE PROCEDURE TEST_proc
@sp_name nvarchar(50)='CentaNet.Mobile.Model.Request.GetBizGlobalRequest'
 AS 
SELECT * FROM [Test].[dbo].[TrackLog] where FullName like @sp_name+'%'

EXEC TEST_proc ;

3、输出参数

------创建带输入输出参数的存储过程----------------
ALTER PROCEDURE test_procOut(@sp_id  nvarchar(10),@fullname nvarchar(100) output)
as 
select @fullname=fullname   from dbo.TrackLog t where id =@sp_id


-------带输出参数的存储过程的执行------------------
declare @fullname nvarchar(100)  ----声明输出参数的
exec test_procOut 1,@fullname output    ------调用存储过程
print @fullname  ---打印输出参数的值

返回值

RETURN [<integer value to return>]

返回值必须是整数

关于RETURN语句，最重要的是知道它是无条件地从存储过程中退出的。无论运行到存储过程的哪个位置，在调用RETURN语句之后将不会执行任何一行代码。

　首先运行CREATE PROC过程。这回解析查询以确保会实际运行这些代码。它与直接运行脚本的区别在于CREATE PROC命令可以利用所谓的延迟名称解析。延迟名称解析可以忽略一些对象还不存在的事实。

　　在创建了存储过程后，它将等待第一次执行。在那时，存储过程被优化，而查询计划被编译并且缓存到系统上。后续几次运行该存储过程时，除非通过使用WITH RECOMPILE选项指定，否则都会使用缓存的查询计划而不是创建一个新的查询计划。这意味着每次使用该存储过程时，存储过程都会跳过很多优化和编译工作。节省的确切时间取决于批处理的复杂性，批处理中表的大小，以及每个表上索引的数量。通常，节省的时间不是很多。但对于大多数场景来说可能是1秒或更少-但通过百分比可以计算出此区别(1秒比2秒快了100%)。当需要进行多次调用时或针对循环的情况，这一区别会变得更明显。

存储过程的不利方面

　　对于存储过程的不利之处要认识到的最重要的一点事，除非手动地干预(使用WITH RECOMPILE选项)，否则只会在第一次运行存储过程的时候，或者当查询所涉及的表更新了统计信息时，才对存储过程进行优化。
　　这种"一次优化，多次使用"的策略节省了存储过程的时间，但是该策略也是一把双刃剑。如果查询是动态的(即是在使用EXEC命令时建立的)，那么只会在第一次运行时对存储过程进行优化，但是会发现以后再也不这样了。简而言之，可能会使用错误的计划

WITH RECOMPILE选项

　　可以利用存储过程提供的安全性代码和代码封装方面的好处，但还是忽略了预编译代码方面的影响。可以回避未使用正确的查询计划的问题，因为可以确保为特定一次运行创建新的计划。方法就是使用WITH RECOMPILE选项。
　　使用该选项的方式有两种：

　　1、可以在运行时包含WITH RECOMPILE。

　　EXEC spMySproc '1/1/2004'
　　WITH RECOMPILE

　　这告诉SQL Server抛弃已有的执行计划并且创建一个新的计划-但只是这一次。也就是说，只是这次使用WITH RECOMPILE选项来执行存储过程。

　　也可以通过在存储过程中包含WITH RECOMPILE选项来使之变得更持久。如果使用这种方式，则在CREATE PROC或ALTER PROC语句中的AS语句前添加WITH RECOMPILE选项即可。

　　如果通过该选项创建存储过程，那么无论在运行时选择了其他什么选项，每次运行存储过程都会重新编译它。

系统存储过程

的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

常用系统存储过程有：

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;


    系统存储过程示例：

--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

posted @ 2016-09-01 16:33 SoftSteven 阅读(2661) 评论(0) 编辑收藏举报

刷新页面返回顶部

Soft Steven

T-SQL存储过程

TestTalb

创建存储过程

存储过程参数化

返回值

系统存储过程

公告