Sql Server存储过程语法和应用详细解说_B/S开发框架

Sql Server存储过程PROCEDURE是为何物,怎么编写,怎么应用,怎么优化,我们在项目究竟该怎么去应用它??

概念

语法

应用

优化

概念

       SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成,以实现某个任务,这种方法就是存储过程。
       存储过程是SQL语句和可选控制流语句的预编译集合,存储在数据库中,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程功能。
       在SQL Server中存储过程分为两类:即系统提供的存储过程和用户自定义的存储过程。

       可以出于任何使用SQL语句的目的来使用存储过程,它具有以下优点:
       可以在单个存储过程中执行一系列SQL语句。
       可以从自己的存储过程内引用其他存储过程,这可以简化一系列复杂语句。
       存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句快,而且减少网络通信的负担。
       安全性更高。

语法

存储过程创建
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
用CREATE PROCEDURE创建存储过程的参数说明如下: 
<Procedure_Name, sysname, ProcedureName> :要创建的存储过程的名称。 
<@Param1, sysname, @p1>:存储过程中的参数名称
<Datatype_For_Param1, , int>:参数的数据类型。 
<Default_Value_For_Param1, , 0>:参数的默认值。 
OUTPUT:表明该参数是一个输出参数。 

修改存储过程

修改只需要将创建的Create关键词改为Alter即可,Alter PROCEDURE <Procedure_Name, sysname, ProcedureName> 


删除存储过程
删除存储过程可以使用DROP命令,DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除,其语法形式如下:
drop procedure {procedure} [,…n]

重命名存储过程

修改存储过程的名称可以使用系统存储过程sp_rename,其语法形式如下:

 sp_rename  原存储过程名称,新存储过程名称


执行存储过程
直接执行存储过程可以使用EXECUTE命令来执行,其语法形式如下:
[[EXECUTE]]
   {       [@return_status=]
          {procedure_name[;number]|@procedure_name_var} [@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}      
      [,...n] 
[ WITH RECOMPILE ]
procedure_name:执行的存储过程名称
procedure_:输入参数名称
value:入参值

注:上述命令用企业管理器都是可以图形化操作的,相关操作步骤大家去问下度娘即可,资料有很多。

图形工具

应用

我们已一个比较简单的例子给大家加深上述知识点,B/S开发框架支持存储过程的业务应用的。
开发框架后台.Net EF代码有封装好的存储过程调用函数供大家使用,代码如下:
public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, Func<TEntity, bool> autoDetectPredicate, params object[] parameters) where TEntity : class, new()
{
    //add parameters to command
    if (parameters != null && parameters.Length > 0)
    {
	for (int i = 0; i <= parameters.Length - 1; i++)
	{
	    var p = parameters[i] as DbParameter;
	    if (p == null)
		throw new Exception("Not support parameter type");

	    commandText += i == 0 ? " " : ", ";

	    commandText += "@" + p.ParameterName;
	    if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
	    {
		//output parameter
		commandText += " output";
	    }
	}
    }

    var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList();

    //performance hack applied as described here - http://www.nopcommerce.com/boards/t/25483/fix-very-important-speed-improvement.aspx
    bool acd = this.Configuration.AutoDetectChangesEnabled;
    try
    {
	this.Configuration.AutoDetectChangesEnabled = false;

	for (int i = 0; i < result.Count; i++)
	    result[i] = AttachEntityToContext(result[i], autoDetectPredicate);
    }
    finally
    {
	this.Configuration.AutoDetectChangesEnabled = acd;
    }

    return result;
}
应用场景:web开发框架开发某网站时,网站展示了公司产品系列和产品,为每个产品页面做了关键词,现在我们根据关键词来查询产品信息,存储过程代码如下:
USE [coolensData]
GO
/****** Object:  StoredProcedure [dbo].[ProductLoadAllPaged]    Script Date: 10/08/2017 16:56:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[ProductLoadAllPaged]
(
	@Keywords			nvarchar(4000) = null,
	@UseFullTextSearch  bit = 0,
	@FullTextMode		int = 0, --0 - using 
	@TotalRecords		int = null OUTPUT
)
AS
BEGIN
	
	DECLARE
		@SearchKeywords bit,
		@sql nvarchar(max),
		@sql_orderby nvarchar(max)

	SET NOCOUNT ON
	
	--filter by keywords
	SET @Keywords = isnull(@Keywords, '')
	SET @Keywords = rtrim(ltrim(@Keywords))
	IF ISNULL(@Keywords, '') != ''
	BEGIN
		SET @SearchKeywords = 1
		
		IF @UseFullTextSearch = 1
		BEGIN
			--remove wrong chars (' ")
			SET @Keywords = REPLACE(@Keywords, '''', '')
			SET @Keywords = REPLACE(@Keywords, '"', '')
			
			--full-text search
			IF @FullTextMode = 0 
			BEGIN
				--0 - using CONTAINS with <prefix_term>
				SET @Keywords = ' "' + @Keywords + '*" '
			END
			ELSE
			BEGIN
				--5 - using CONTAINS and OR with <prefix_term>
				--10 - using CONTAINS and AND with <prefix_term>

				--clean multiple spaces
				WHILE CHARINDEX('  ', @Keywords) > 0 
					SET @Keywords = REPLACE(@Keywords, '  ', ' ')
            END
        End
        
        SET @sql = '
		INSERT INTO #KeywordProducts ([ProductId])
		SELECT p.Id
		FROM Product p with (NOLOCK)
		WHERE ';
		IF @UseFullTextSearch = 1
			SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) ';
		ELSE
			SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 ';
			
         EXEC sp_executesql @sql, N'@Keywords nvarchar(4000)', @Keywords;
         
    END
    
    CREATE TABLE #PageIndex 
	(
		[IndexId] int IDENTITY (1, 1) NOT NULL,
		[ProductId] int NOT NULL
	)
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
    DECLARE @RowsToReturn int
    
	--return products
	SELECT TOP (@RowsToReturn)
		p.*
	FROM
		#PageIndex [pi]
		INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
	WHERE
		[pi].IndexId > @PageLowerBound AND 
		[pi].IndexId < @PageUpperBound
	ORDER BY
		[pi].IndexId
    
    drop table #PageIndex;
END
我们这样写存储过程

1、创建存储过程

2、声明全局变量

变量

3、为变量赋值(包含内部变量和参数都可这样赋值)

赋值

4、还可以写业务逻辑,什么判断、循环、顺序语句统统都用上

逻辑语句

5、写sql字符串,轻轻松松组织sql语句

sql字符串

6、创建临时表,想想为什么要这样,还有其他方法吗

临时表

7、不容易吧,终于到最后了,存储过程的职责就是这样的哦,处理复杂业务的

结果

优化

       1、逐步调试
       2、预编译
       3、临时表的建立
       4、查询优化

网站&系统开发技术学习交流群:463167176

本站文章除注明转载外,均为本站原创或翻译,欢迎任何形式的转载,但请务必注明出处,尊重他人劳动,共创和谐网络环境。
转载请注明:文章转载自:华晨软件-云微开发平台 » Sql Server存储过程语法和应用详细解说_B/S开发框架
本文标题:Sql Server存储过程语法和应用详细解说_B/S开发框架
本文地址:http://www.hocode.com/OrgTec/DB/0007.html

相关文章: MSSql高级--阻塞和死锁

电话
电话 18718672256

扫一扫
二维码