SQLServer学习笔记系列11

2022-12-07 13:48:59

一.写在前面的话

身体是革命的本钱，这句放在嘴边常说的话，还是拿出来一起共勉，提醒一起奋斗的同僚们，保证睡眠，注意身体!偶尔加个班，也许不曾感觉到身体发出的讯号，长期晚睡真心扛不住！自己也制定计划，敦促自己按照作息时间来上班学习生活！虽然自己每星期运动，还是觉得晚睡带来的身体压力，无法承受！程序猿兄弟们，我们早上起来的时候，可以看看自己的眼睛，如果充满血丝，那我们就该需要调养，好好休息了！没了身体，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()

 )

 AS

 DECLARE @namelen INT;

 SET @namelen=LEN(@username);

 IF    @namelen>

 RETURN

 ELSE

 RETURN  ;

 GO

 --定义变量保存结果

 DECLARE @ReturnValue INT;

 EXEC  @ReturnValue=dbo.CreateUser @username = N'liupeng'  -- nvarchar()

 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(,) PRIMARY KEY NOT NULL,

 userName NVARCHAR() NOT NULL,

 userPWD  NVARCHAR() NOT NULL,

 userPhone NVARCHAR() NOT NULL

 )

 go

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

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

  --插入数据

  set identity_insert testTable on  --设置为on时，可以向标识列中插入

  declare @count int

  set @count=

  while @count<=

  begin

     insert into testTable(id,userName,userPWD,userPhone) values(@count,'liupeng','liupeng_IT','@liupengwuhan@gmail.com')

      set @count=@count+

 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()

     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条

查询结果如图所示：消耗的时间为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()

     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-) and IDRank<@pageSize*(@pageIndex+)

     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   *  FROM Sales.Customers

nocount打开：

 USE TSQLFundamentals2008;

 SET NOCOUNT ON;

 SELECT TOP   *  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())

执行结果：

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

码农公寓

一.写在前面的话

二.存储过程

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

相关文章