SQLServer学习笔记系列11

一.写在前面的话

身体是革命的本钱，这句放在嘴边常说的话，还是拿出来一起共勉，提醒一起奋斗的同僚们，保证睡眠，注意身体!偶尔加个班，也许不曾感觉到身体发出的讯号，长期晚睡真心扛不住！自己也制定计划，敦促自己按照作息时间来上班学习生活！虽然自己每星期运动，还是觉得晚睡带来的身体压力，无法承受！程序猿兄弟们，我们早上起来的时候，可以看看自己的眼睛，如果充满血丝，那我们就该需要调养，好好休息了！没了身体，Coding的世界即将一去不复返！好好休息，保重身体！善待朋友，真爱家人，迎接每一天美丽的日出！共勉！

         

二.存储过程

什么是存储过程：存储过程可以认为是一个结果集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表

的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。

2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。

3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

（1）存储过程实例：

例如：我们需要调整产品价格，加￥10，但是有时候我们需要将产品价格提高￥20了？此时为了sql的重用，那么可以用存储过程实现。

创建存储过程：

 

CREATE PROCEDURE ModifyPrice
(
  @num money
  
)
AS
UPDATE Production.Products
SET unitprice=unitprice+@num;

 

其中@num为需要传递的参数，执行存储过程：exec  ModifyPrice @num;  存储过程创建以后，一直存储在数据库中。

(2)存储过程的参数传递

    存储过程 默认的为传入参数，与方法不同，存储过程没有通常意义上的返回类型和返回数据。但是我们可以利用传出参数来实现。例如查询顾客的数量：

CREATE PROCEDURE GetCustomersCount
(
 @count int OUTPUT 
)
AS
DECLARE  @num INT;
SELECT        @num=COUNT(*) FROM Sales.Customers;

--传出

SET @count=@num;
go

--必须使用变量来保存传出的参数
DECLARE @myCount int;

--前面是参数中定义的传出参数
--后面是我们定义的用来保存输出结果的变量

EXEC GetCustomersCount @count=@myCount OUTPUT;

PRINT @myCount;       

执行结果：

（3）存储过程的return参数

名字虽然叫return参数，但是不是普通的return，跟C#中的return不一样，只能return整数，表示执行的一种状态，返回0表示执行成功，即使没有写return ，默认为0。

例如我们想要对用户名字的长度做做一个校验，所以在这里根据return 返回的值，来进行判断。

--创建用户
CREATE PROCEDURE CreateUser
(
   @username nvarchar(100)
)           
AS
DECLARE @namelen INT;
SET @namelen=LEN(@username);

IF    @namelen>5
RETURN 0
ELSE
RETURN 1 ;

GO
--定义变量保存结果

DECLARE @ReturnValue INT;
EXEC  @ReturnValue=dbo.CreateUser @username = N'liupeng'  -- nvarchar(100)
PRINT @ReturnValue;

执行结果：

（4）几种常见的分页存储过程：

首先创建一张表作为测试表，用来保存用户的个人信息：

--创建数据库testDB
if DB_ID('testDB') is not NULL
DROP DATABASE testDB;
CREATE DATABASE testDB ;
go
IF OBJECT_ID('testTable') IS NOT NULL
DROP TABLE testTable;
CREATE TABLE testTable
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
userName NVARCHAR(200) NOT NULL,
userPWD  NVARCHAR(200) NOT NULL,
userPhone NVARCHAR(200) NOT NULL
)

go 

接着我们可以向数据库表中插入一些数据，为了是数据量大，呈现的结果准确，我们插入5000000条数据进行测试，由于插入的时候，等待了15分钟，时间

太长，我终止了sql继续执行，查询数据库，此时已插入322446行数据。

 --插入数据 
 set identity_insert testTable on  --设置为on时，可以向标识列中插入
 declare @count int 
 set @count=1 
 while @count<=2000000 
 begin  
    insert into testTable(id,userName,userPWD,userPhone) values(@count,'liupeng','liupeng_IT','@[e-mail]') 
     set @count=@count+1 
end 
set identity_insert testTable off

1.利用select top和select max结合使用,来查询数据分页数据。

 create procedure proc_pagedFenye_with_selectMax  --利用select top and select max(列) 
 ( 
     @pageIndex int,  --页索引 
     @pageSize int    --页记录数 
 ) 
 as 
 begin 
 set nocount on; 
     declare @timediff datetime 
   declare @sql nvarchar(500) 
    select @timediff=Getdate() 
    set @sql='select top '+str(@pageSize)+' * From testTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From testTable order by ID) as TempTable)) order by ID' 
   execute(@sql) 
   select datediff(ms,@timediff,GetDate()) as 查询时间
set nocount off; 
END

执行存储过程：

EXEC proc_pagedFenye_with_selectMax 10,10    --查询第十一页的数据,每页数据10条

查询结果如图所示：消耗的时间为3毫秒。

2.利用select top和select not in结合使用，来进行分页数据查询。

create procedure proc_pagedFenye_with_notin  --利用select top and select not in 
(     
     @pageIndex int,  --页索引 
     @pageSize int    --每页记录数 
) 
 as 
 begin 
    set nocount on; 
    declare @timediff datetime --耗时 
    declare @sql nvarchar(500) 
    select @timediff=Getdate() 
    set @sql='select top '+str(@pageSize)+' * from testTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from testTable order by ID ASC)) order by ID'     execute(@sql)  --因select top后不支技直接接参数，所以写成了字符串@sql 
    select datediff(ms,@timediff,GetDate()) as 查询时间 
    set nocount off; 
END

执行存储过程：

EXEC proc_pagedFenye_with_notin 10,10        --查询第十一页的数据,每页数据10条

查询结果如图所示：消耗的时间为6毫秒。

3.利用row_number()排序方法来进行测试。

create procedure proc_pagedFenye_with_Rownumber  --利用SQL 2005中的Row_number() 
 ( 
    @pageIndex int, 
    @pageSize int 
 ) 
 as 
 begin 
 set nocount on;
    declare @timediff DATETIME;  
    select @timediff=getdate() 
    select * from (select *,Row_number() over(order by ID asc) as IDRank from testTable) as IDWithRowNumber where IDRank>@pageSize*(@pageIndex-1) and IDRank<@pageSize*(@pageIndex+1) 
    select datediff(ms,@timediff,getdate()) as 查询时间 
set nocount off; 
END

执行存储过程：

EXEC proc_pagedFenye_with_Rownumber 10,10       --查询第十一页的数据,每页数据10条

查询结果如图所示：消耗的时间为3毫秒。

根据上述测试结果：我们可以得到查询效率上：select max >row_number>not in，可能由于数据量的问题，结果并不准确，在这里只作为参考作用。希望可以一起探讨！

在这里提示一下：

在存储过程中，经常用到SET NOCOUNT ON；作用：阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息。

当SET ONCOUNT ON时候，不返回计数，当SET NOCOUNT OFF时候，返回计数；

即使当SET NOCOUNT ON 时候，也更新@@RowCount；

当SET NOCOUNT on时候，将不向客户端发送存储过程每个语句的DONE_IN_proc消息，如果存储过程中包含一些并不返回实际数据的语句，网络通信流量便会大量减少，可以显著提高应用程序性能；

SET NOCOUNT 指定的设置时在执行或运行时候生效，分析时候不生效。

例如：查询顾客表中前5位顾客：

nocount 关闭：

USE TSQLFundamentals2008;
SET NOCOUNT OFF;
SELECT TOP 5  *  FROM Sales.Customers

nocount打开：

USE TSQLFundamentals2008;
SET NOCOUNT ON;
SELECT TOP 5  *  FROM Sales.Customers

 

三.用户自定义函数（UDF）

在sql中我们经常用到Function，系统自带的函数为我们提供了很多方便，有时候我们需要根据业务需求，可能系统函数不能完全满足我们的需求，此时就需

要用户自定义Function，满足用户的要求。

例如：我们可以定义一个函数来根据传递的时间，获取当前时间的分钟数。

--创建函数
CREATE FUNCTION Getminnutes
(
 @datevalue datetime  --传入参数
)
--函数可以直接返回一个值
RETURNS int
AS
begin
 --函数体
 DECLARE @mi INT;
 SET @mi=DATEPART(MINUTE,@datevalue);
 RETURN @mi;
 END;

SELECT dbo.Getminnutes(GETDATE())

执行结果：

 

希望各位大牛给出指导，不当之处虚心接受学习！谢谢！