学习笔记系列11"/>
SQLServer学习笔记系列11
一.写在前面的话
身体是革命的本钱,这句放在嘴边常说的话,还是拿出来一起共勉,提醒一起奋斗的同僚们,保证睡眠,注意身体!偶尔加个班,也许不曾感觉到身体发出的讯号,长期晚睡真心扛不住!自己也制定计划,敦促自己按照作息时间来上班学习生活!虽然自己每星期运动,还是觉得晚睡带来的身体压力,无法承受!程序猿兄弟们,我们早上起来的时候,可以看看自己的眼睛,如果充满血丝,那我们就该需要调养,好好休息了!没了身体,Coding的世界即将一去不复返!好好休息,保重身体!善待朋友,真爱家人,迎接每一天美丽的日出!共勉!
二.存储过程
什么是存储过程:存储过程可以认为是一个结果集,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表
的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用他就行了。
存储过程的好处:
1.由于数据库执行动作时,是先编译后执行的。然而存储过程是一个编译过的代码块,所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句,所以也能降低网络的通信量,提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库,从而确保数据的安全。
(1)存储过程实例:
例如:我们需要调整产品价格,加¥10,但是有时候我们需要将产品价格提高¥20了?此时为了sql的重用,那么可以用存储过程实现。
创建存储过程:
1 CREATE PROCEDURE ModifyPrice
2 (
3 @num money
4
5 )
6 AS
7 UPDATE Production.Products
8 SET unitprice=unitprice+@num;
其中@num为需要传递的参数,执行存储过程:exec ModifyPrice @num ; 存储过程创建以后,一直存储在数据库中。
(2)存储过程的参数传递
存储过程 默认的为传入参数,与方法不同,存储过程没有通常意义上的返回类型和返回数据。但是我们可以利用传出参数来实现。例如查询顾客的数量:
1 CREATE PROCEDURE GetCustomersCount
2 (
3 @count int OUTPUT
4 )
5 AS
6 DECLARE @num INT;
7 SELECT @num=COUNT(*) FROM Sales.Customers;
8
9 --传出
10
11 SET @count=@num;
12 go
13
14 --必须使用变量来保存传出的参数
15 DECLARE @myCount int;
16
17 --前面是参数中定义的传出参数
18 --后面是我们定义的用来保存输出结果的变量
19
20 EXEC GetCustomersCount @count=@myCount OUTPUT;
21
22 PRINT @myCount;
执行结果:
(3)存储过程的return参数
名字虽然叫return参数,但是不是普通的return,跟C#中的return不一样,只能return整数,表示执行的一种状态,返回0表示执行成功,即使没有写return ,默认为0。
例如我们想要对用户名字的长度做做一个校验,所以在这里根据return 返回的值,来进行判断。
1 --创建用户
2 CREATE PROCEDURE CreateUser
3 (
4 @username nvarchar(100)
5 )
6 AS
7 DECLARE @namelen INT;
8 SET @namelen=LEN(@username);
9
10 IF @namelen>5
11 RETURN 0
12 ELSE
13 RETURN 1 ;
14
15 GO
16 --定义变量保存结果
17
18 DECLARE @ReturnValue INT;
19 EXEC @ReturnValue=dbo.CreateUser @username = N'liupeng' -- nvarchar(100)
20 PRINT @ReturnValue;
执行结果:
(4)几种常见的分页存储过程:
首先创建一张表作为测试表,用来保存用户的个人信息:
1 --创建数据库testDB
2 if DB_ID('testDB') is not NULL
3 DROP DATABASE testDB;
4 CREATE DATABASE testDB ;
5 go
6 IF OBJECT_ID('testTable') IS NOT NULL
7 DROP TABLE testTable;
8 CREATE TABLE testTable
9 (
10 id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
11 userName NVARCHAR(200) NOT NULL,
12 userPWD NVARCHAR(200) NOT NULL,
13 userPhone NVARCHAR(200) NOT NULL
14 )
15
16 go
接着我们可以向数据库表中插入一些数据,为了是数据量大,呈现的结果准确,我们插入5000000条数据进行测试,由于插入的时候,等待了15分钟,时间
太长,我终止了sql继续执行,查询数据库,此时已插入322446行数据。
1 --插入数据
2 set identity_insert testTable on --设置为on时,可以向标识列中插入
3 declare @count int
4 set @count=1
5 while @count<=2000000
6 begin
7 insert into testTable(id,userName,userPWD,userPhone) values(@count,'liupeng','liupeng_IT','@liupengwuhan@gmail')
8 set @count=@count+1
9 end
10 set identity_insert testTable off
1.利用select top和select max结合使用,来查询数据分页数据。
1 create procedure proc_pagedFenye_with_selectMax --利用select top and select max(列)
2 (
3 @pageIndex int, --页索引
4 @pageSize int --页记录数
5 )
6 as
7 begin
8 set nocount on;
9 declare @timediff datetime
10 declare @sql nvarchar(500)
11 select @timediff=Getdate()
12 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'
13 execute(@sql)
14 select datediff(ms,@timediff,GetDate()) as 查询时间
15 set nocount off;
16 END
执行存储过程:
EXEC proc_pagedFenye_with_selectMax 10,10 --查询第十一页的数据,每页数据10条
查询结果如图所示:消耗的时间为3毫秒。
2.利用select top和select not in结合使用,来进行分页数据查询。
1 create procedure proc_pagedFenye_with_notin --利用select top and select not in
2 (
3 @pageIndex int, --页索引
4 @pageSize int --每页记录数
5 )
6 as
7 begin
8 set nocount on;
9 declare @timediff datetime --耗时
10 declare @sql nvarchar(500)
11 select @timediff=Getdate()
12 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
13 select datediff(ms,@timediff,GetDate()) as 查询时间
14 set nocount off;
15 END
执行存储过程:
EXEC proc_pagedFenye_with_notin 10,10 --查询第十一页的数据,每页数据10条
查询结果如图所示:消耗的时间为6毫秒。
3.利用row_number()排序方法来进行测试。
1 create procedure proc_pagedFenye_with_Rownumber --利用SQL 2005中的Row_number()
2 (
3 @pageIndex int,
4 @pageSize int
5 )
6 as
7 begin
8 set nocount on;
9 declare @timediff DATETIME;
10 select @timediff=getdate()
11 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)
12 select datediff(ms,@timediff,getdate()) as 查询时间
13 set nocount off;
14 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打开:
1 USE TSQLFundamentals2008;
2 SET NOCOUNT ON;
3 SELECT TOP 5 * FROM Sales.Customers
三.用户自定义函数(UDF)
在sql中我们经常用到Function,系统自带的函数为我们提供了很多方便,有时候我们需要根据业务需求,可能系统函数不能完全满足我们的需求,此时就需
要用户自定义Function,满足用户的要求。
例如:我们可以定义一个函数来根据传递的时间,获取当前时间的分钟数。
1 --创建函数
2 CREATE FUNCTION Getminnutes
3 (
4 @datevalue datetime --传入参数
5 )
6 --函数可以直接返回一个值
7 RETURNS int
8 AS
9 begin
10 --函数体
11 DECLARE @mi INT;
12 SET @mi=DATEPART(MINUTE,@datevalue);
13 RETURN @mi;
14 END;
15
16 SELECT dbo.Getminnutes(GETDATE())
执行结果:
希望各位大牛给出指导,不当之处虚心接受学习!谢谢!
转载于:
更多推荐
SQLServer学习笔记系列11
发布评论