文档章节

SQLServer学习笔记系列11

CoderPeng
 CoderPeng
发布于 2015/11/17 11:31
字数 2142
阅读 1
收藏 0

一.写在前面的话

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

执行结果:

 

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

© 著作权归作者所有

CoderPeng
粉丝 0
博文 140
码字总数 78649
作品 0
青浦
程序员
私信 提问
在Linux上使用环境变量配置SQL Server设置

你可以使用不同的环境变量在Linux上配置SQL Server 2017。这些变量用于两个场景: 使用mssql-conf setup命令配置初始化设置。 配置一个新的Docker容器中的SQL Server。 环境变量 示例:初始设...

UltraSQL
2018/07/30
0
0
MSSQL(SQL Server) on Linux 简明部署与使用

标签 PostgreSQL , ms sql , SQL Server 背景 本文介绍MS SQL on Linux的简单部署,使用。 https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-red-hat?view=sql-serve......

德哥
04/14
0
0
SQL Serever学习11——数据库的安全管理

公司管理软件设计完成,但是日常工作繁忙,向领导提出增加几个管理员,帮助管理和维护系统,领导同意了,但是要求一定要管理好这几个管理员用户,保证数据库的安全。 修改身份验证模式 数据库...

乌云上
2018/08/26
0
0
php连接sqlserver2005

为了php连接sql2005 ,我在网络上找了一大堆资料在我的csdn博客中.晚上3:05分时候终于搞定了 php连接sql2005的问题,现在整合,同时把FAQ整合上. 我前面写的教程: 连接前配置系统: 1.检查文件 ...

charley158
2014/01/02
104
1
【用jersey构建REST服务】系列文章

1.用Jersey构建RESTful服务1--HelloWorldhttp://www.waylau.com/jersey-restful-helloworld/2.用Jersey构建RESTful服务2--JAVA对象转成XML输出http://www.waylau.com/jersey-restful-java-x......

waylau
2014/08/23
1K
1

没有更多内容

加载失败,请刷新页面

加载更多

代理模式之JDK动态代理 — “JDK Dynamic Proxy“

动态代理的原理是什么? 所谓的动态代理,他是一个代理机制,代理机制可以看作是对调用目标的一个包装,这样我们对目标代码的调用不是直接发生的,而是通过代理完成,通过代理可以有效的让调...

code-ortaerc
今天
5
0
学习记录(day05-标签操作、属性绑定、语句控制、数据绑定、事件绑定、案例用户登录)

[TOC] 1.1.1标签操作v-text&v-html v-text:会把data中绑定的数据值原样输出。 v-html:会把data中值输出,且会自动解析html代码 <!--可以将指定的内容显示到标签体中--><标签 v-text=""></......

庭前云落
今天
8
0
VMware vSphere的两种RDM磁盘

在VMware vSphere vCenter中创建虚拟机时,可以添加一种叫RDM的磁盘。 RDM - Raw Device Mapping,原始设备映射,那么,RDM磁盘是不是就可以称作为“原始设备映射磁盘”呢?这也是一种可以热...

大别阿郎
今天
12
0
【AngularJS学习笔记】02 小杂烩及学习总结

本文转载于:专业的前端网站☞【AngularJS学习笔记】02 小杂烩及学习总结 表格示例 <div ng-app="myApp" ng-controller="customersCtrl"> <table> <tr ng-repeat="x in names | orderBy ......

前端老手
昨天
16
0
Linux 内核的五大创新

在科技行业,创新这个词几乎和革命一样到处泛滥,所以很难将那些夸张的东西与真正令人振奋的东西区分开来。Linux内核被称为创新,但它又被称为现代计算中最大的奇迹,一个微观世界中的庞然大...

阮鹏
昨天
20
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部