sql server 存储过程用到的知识

1.传参方式：

1. 无参

功能：查看作业类型表的所有信息

create procedure waw_showTaskType

as

begin

select * from TaskType

end

 

2. 入参

功能：根据TaskID查看该类型的信息

create procedure waw_showTaskTypeById

(

@taskId int

)

as

begin

select * from TaskType where TaskID=@taskId

end

 

3. 出参

功能:根据taskId查看该作业类型的名称

create procedure waw_showTaskNameById

(

@taskId int,

@taskName varchar(30) output

)

as

begin

select @taskName=taskname from TaskType where TaskID=@taskId

End

调用存储过程:

declare @taskId int,

        @taskName varchar(30)

set @taskId=1

exec waw_showTaskNameById @taskId,@taskName output

print @taskName

注意:output不能省略,否则不能传出

传参时也可以不按照参数顺序,而使用”参数名=参数值”的写法:

exec waw_showTaskNameById @taskId=@taskId,@taskName=@taskName output

注意:一旦有一个参数采用”参数名=参数值”的写法,则所有的参数都要这样写.

2.语法结构:

Create  proc|procedure 存储过程名

(

参数1,

参数2,

.......

)

as

begin

变量定义部分

业务逻辑部分

异常处理部分

end

3. 返回值方式

存储过程有三种返回方式:

1. 用return返回数字型数据

2. 用返回参数(output)返回结果,可以返回各种数据类型(通过游标来循环查询结果每一行)

3. 直接在存储过程中用select返回结果集,可以是任意的select语句,这意味着是任意的返回结果集.

3.1用return返回数字型数据

功能:根据作业类型id判断creater是否为’’(空字符串)

create proc waw_isNullOfCreaterOfTaskType

(

@taskId int

)

as

begin

if (select creater from taskType where taskid=@taskId) =''

return 0

else return 1

end

3.2 用返回参数返回结果

功能:根据taskId查看该作业类型的名称

create procedure waw_showTaskNameById

(

@taskId int,

@taskName varchar(30) output

)

as

begin

select @taskName=taskname from TaskType where TaskID=@taskId

End

3.3 直接用select返回结果集

功能：查看作业类型表的所有信息

create procedure waw_showTaskType

as

begin

select * from TaskType

End

4. 调用存储过程

在sql server查询窗口中调用存储过程的语法为:

Exec 存储过程名 参数1,参数2......

在程序中调用查看相关api

5.存储过程中用到的其他功能

5.1 变量定义

格式:Declare @变量名 变量类型 如:

Declare @userId int

 

5.2变量赋值:

5.2.1简单赋值

Declare @a int

Set @a=5

print @a

5.2.2 用select赋值

declare @user1 nvarchar(50)

select @user1='张三'

print @user1

5.2.3 用update赋值

declare @user3 nvarchar(50)

update ST_User set @user3 = Name where ID=1

print @user3

 

5.3.打印语句

5.3.1 print

declare @a varchar(20)

set @a='fdsfdsf'

Print @a

5.3.2 select

declare @a varchar(20)

set @a='fdsfdsf'

select @a

5.4 循环语句

While 条件

begin

循环体

end

实例如下:

--while循环计算1到100的和

declare @a int

declare @sum int

set @a=1

set @sum=0

while @a<=100

begin

    set @sum+=@a

    set @a+=1

end

print @sum

5.5 条件语句

5.5.1 if else条件分支

if(1+1=2)

begin

    print '对'

end

else

begin

    print '错'

end

 

5.5.2 when then 条件分支

declare @today int

declare @week nvarchar(3)

set @today=3

set @week=case

when @today=1 then '星期一'

when @today=2 then '星期二'

when @today=3 then '星期三'

when @today=4 then '星期四'

when @today=5 then '星期五'

when @today=6 then '星期六'

when @today=7 then '星期日'

else '值错误'

end

print @week

5.6游标

5.6.1语法

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 

     [ FORWARD_ONLY | SCROLL ] 

     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 

     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 

     [ TYPE_WARNING ] 

     FOR select_statement 

     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

5.6.2 实例

declare @ID int

declare @Oid int

declare @Login varchar(50)

 

--定义一个游标

declare user_cur cursor for select ID,Oid,[Login] from ST_User

--打开游标

open user_cur

while @@fetch_status=0

begin

--读取游标

    fetch next from user_cur into @ID,@Oid,@Login

    print @ID

    --print @Login

end

close user_cur--关闭游标

--摧毁游标

deallocate user_cur

 

游标通常会与全局变量@@fetch_status与while循环来共同使用,以达到遍历游标所在数据集的目的

5.6.3 对于游标的一些建议

1. 如果能不用游标,尽量不要使用游标

2. 用完之后一定要关闭和释放

3. 尽量不要在大量数据上定义游标

4. 尽量不要使用使用insensitive,static和keyset这些参数定义游标

5. 如果可以,尽量使用fast_forward关键字定义游标

6. 如果只对数据进行读取,当读取只用到fetch next选项,则最好使用forward_only参数.

5. 7事务

5.7.1使用格式:

Begin try

Begin tran

业务处理

Commit tran

End try

Begin catch

Rollback tran

End catch

实例如下:

create procedure pro_insert_score

(@option varchar(50),

@name varchar(50)='',

@months int=0,

@score int=0

)

as

begin

declare @errorNum int,@errorSeverity int,@errorState int,@errorLine int,@errorPro varchar(200),@errorMessage nvarchar(4000);--定义变量

if @option='InsertScore'

begin

--使用事务

begin try

begin tran

insert into score(name,score,months)

values(@name,@score,@months)

 

--插入重复值报错事务回滚

insert into score(name,score,months)

values(@name,@score,@months)

 

--提交事务

commit tran

end try

begin catch

select @errorMessage=ERROR_MESSAGE(),

       @errorSeverity=ERROR_SEVERITY(),

       @errorState=ERROR_STATE();

raiserror(@errorMessage,@errorSeverity,@errorState)--xian显示错误信息

rollback;--回滚事务

throw

--执行失败

return 1

end catch

--执行成功

return 0

end

end

5.7.2 带有保存点的事务

创建保存点

save tran t1

回滚到保存点

rollback tran t1

5.7.3 嵌套事务

--事务1

begin tran

--事务2

begin tran

rollback

rollback

事务2的rollback操作会直接回滚到事务1的begin tran,会导致事务1的rollback没有对应的begin tran。处理方法可以再调用事务2之前定义一个事务保存点或者在事务rollback前加上if @@trancount>0判断条件是否存在事务需要回滚。如：

 

--事务1

begin tran

--创建保存点

save tran t1

--事务2

begin tran

rollback tran t1--回滚到指定保存点

rollback

5.8常用全局变量

5.8.1 全局变量说明

全局变量是sql server系统内部使用的变量,它的作用范围并不仅仅限于某一程序,而是任何程序可以随时调用.全局变量通常存储一些sql server的配置设定值和统计数据.用户可以在系统中使用全局变量来测试系统的设定值或者是t-sql命令执行后的状态值.

5.8.2 全局变量注意点

使用全局变量时应注意一下几点:

1. 全局变量不是由用户定义的,它们是服务器级定义的,用户只能使用.

2. 引用全局变量时,必须以标记符”@@”开头,局部变量以”@开头”

3. 局部变量的名称不能与全局变量的名称相同,否则会在应用程序中出现不可预测的结果.

5.8.3 常用全局变量

--1.@@error 最后一个T-SQL错误的错误号(目的是或得违反约束的错误号)

insert into Subject values('测试工程师')--违反了约束

select @@ERROR 

--2.@@identity 最后一次插入的标示值

insert into Subject (SubjectName, ClassHour, GradeId) --SubjectId为主键,自增values ('好吧','120',1)

select @@identity  --如果上面是第一次添加,则返回1

--注意:如果你用了delete语句删除了上面添加的语句后

--再select @@identity ,则返回的还是1

--3.@@language 当前使用的语言select @@language 

--4.@@rowcount 受上一个SQL语句影响的行数

select * from Subject --如果查到了N条数据

select @@rowcount --则返回的值N

--5.@@SERVERNAME本地服务器的名称

select @@SERVERNAME  --返回的值 123-PC

--6.@@SERVICENAME 该计算机上的SQL服务的名称

select @@SERVICENAME --返回的值 MSSQLSERVER

--7.@@TIMETICKS 当前计算机上每刻度的微秒数 每一刻度的时间量依赖于计算机。操作系统的一刻度是 31.25 毫秒,或是三十分之一秒。

select @@TIMETICKS --返回的值 31250

--8.@@TRANCOUNT

--BEGIN TRANSACTION 语句将 @@TRANCOUNT 增加 1。 ROLLBACK TRANSACTION 将 @@TRANCOUNT 递减到 0,但 ROLLBACK TRANSACTION savepoint_name 除外,它不影响 @@TRANCOUNT。 COMMIT TRANSACTION 或 COMMIT WORK 将 @@TRANCOUNT 递减 1。 

--在处理事务的时候,一般都用RollBack Transaction来回滚,但是如果在嵌套事务中这样使用的话,就会出现错误。

--在SqlServer里,嵌套事务的层次是由@@TranCount全局变量反映出来的。每一次Begin Transaction都会引起@@TranCount加1。而每一次Commit Transaction都会使@@TranCount减1,而RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务,而使@@TranCount置0。例如：

--Begin Transaction -- @@TranCount = 1

--BeginTransaction -- @@TranCount = 2 

--BeginTransaction -- @@TranCount = 3

--Commit Transaction -- @@TranCount = 2

--Commit Transaction -- @@TranCount = 1

--Commit Transaction -- @@TranCount = 0

--如果出现错误ROLLBACK TRANSACTION 

--则：

--Begin Transaction -- @@TranCount = 1

--BeginTransaction -- @@TranCount = 2 

--BeginTransaction -- @@TranCount = 3

--ROLLBACK TRANSACTION -- @@TranCount = 0

--Commit Transaction -- @@TranCount = 0---出现错误

--Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

--如果被嵌套的事务中发生错误,最简单的方法应该是无论如何都先将它提交,同时返回错误码（一个正常情况不可能出现的代码 如 -1）让上一层事务来处理这个错误,从而使@@TranCount 减1。 这样外层事务在回滚或者提交的时候能够保证外层事务在开始的时候和结束的时候保持一致。由于里层事务返回了错误码,因此外层事务（最外层）可以回滚事务,这样里面已经提交的事务也可以被回滚而不会出现错误。select @@TRANCOUNT

--9.@@VERSION     SQL Server的版本信息

select @@VERSION --返回的值 Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) 

--10.可以创建的,同时连接的最大数目

select @@MAX_CONNECTIONS  --返回的值 32767

 

5.9 异常处理

异常处理有两种方式,2012之前使用raiserror,2012之后使用trow.

5.9.1 raiserror

5.9.1.1 语法

RAISERROR ( { msg_id | msg_str | @local_variable }        
            { ,severity ,state }        
            [ ,argument [ ,...n ] ] 
          )       
   [ WITH option [ ,...n ] ]

5.9.1.2 实例

raiserror(N'<<%*.*s>>',--消息

          10,--严重程度

  1,--状态

  7,--第一个参数用来指定宽度

  3,--第二个参数用来指定精度

  N'abcde')--第三个参数提供字符串

5.9.2 throw

5.9.2.1 语法

;

THROW [ { error_number | @local_variable }, 

 

        { message | @local_variable }, 

 

        { state | @local_variable } ]  

[ ; ]

参数

error_number

表示异常的常量或变量。 error_number是int并且必须为大于或等于 50000 且小于或等于 2147483647,如果CATCH中使用RAISERROR来接收错误信息那么指定的error_number必须在sys.messages 中存在；如果使用CATCH来接收则不需要。

 

消息

描述异常的字符串或变量。 消息是nvarchar(2048)。

 

状态

在 0 到 255 之间的常量或变量,指示与消息关联的状态。 状态是tinyint。

 

注意：

THROW代码前必须要用分号,因为THROW会中断代码的执行,所以如果将THROW放在CATCH中时必须放在ROLLBACK TRAN之后,否则不会回滚事务导致对象一直处于提交状态被锁。

 

5.10 常用函数

5.10.1字符转换函数

1. ASCII()

返回字符表达式最左端字符的ASCII码值,在ASCII()函数中,纯数字的字符串可以不用括起来,但含其他字符的字符串必须用’’括起来,否则会出错.

2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR（） 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR (<float_expression>[,length[, <decimal>]])
length 指定返回的字符串的长度,decimal 指定返回的小数位数。如果没有指定长度,缺省的length 值为10,decimal 缺省值为0。
当length 或者decimal 为负值时,返回NULL；
当length 小于小数点左边（包括符号位）的位数时,返回length 个*；
先服从length ,再取decimal ；
当返回的字符串位数小于length ,左边补足空格。

3、所支持的最大长度为 128。任何不在 1 到 128 范围内的长度都会生成 NULL 结果。 

5.10.2 去空格函数
1、LTRIM() 把字符串头部的空格去掉。

2、RTRIM() 把字符串尾部的空格去掉。

5.10.3 取子串函数
1、left() 
LEFT (<character_expression>, <integer_expression>)
返回character_expression 左起 integer_expression 个字符。

2、RIGHT() 
RIGHT (<character_expression>, <integer_expression>)
返回character_expression 右起 integer_expression 个字符。

3、SUBSTRING()
SUBSTRING (<expression>, <starting_ position>, length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。

5.10.4 字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>, <expression>)
其中substring _expression 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。如果没有发现子串,则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>, <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。
与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。

5.10.5 字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[, quote_ character]) 其中quote_ character 标明括字符串所用的字符,缺省值为“[]”,用该符号括起来。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。
REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值,则返回NULL 。

3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。

4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE(<string_expression1>,<string_expression2>,<string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。

4、SPACE()
返回一个有指定长度的空白字符串。
SPACE (<integer_expression>) 如果integer_expression 值为负值,则返回NULL 。

5、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF (<character_expression1>, <start_ position>, <length>,<character_expression2>)
如果起始位置为负或长度值为负,或者起始位置大于character_expression1 的长度,则返回NULL 值。

5.10.6 数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])
data_type：目标系统所提供的数据类型,包括bigint和sql_variant,不能使用用户定义的数据类型。

使用CAST函数进行数据类型转换时,在下列情况下能够被接受：
（1）两个表达式的数据类型完全相同。
（2）两个表达式可隐性转换。
（3）必须显式转换数据类型。
如果试图进行不可能的转换（例如,将含有字母的 char 表达式转换为 int 类型）,SQServer 将显示一条错误信息。
如果转换时没有指定数据类型的长度,则SQServer自动提供长度为30。

CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作
2、CONVERT()

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。
CONVERT (<data_ type>[ length ], <expression> [, style])

1）data_type为SQL Server系统定义的数据类型,用户自定义的数据类型不能在此使用。
2）length用于指定数据的长度,缺省值为30。
3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6）把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
7）BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8）试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号,不同的样式编号有不同的输出格式。

5.10.7 日期函数
1、day(date_expression) 
返回date_expression中的日期值

2、month(date_expression)
返回date_expression中的月份值

3、year(date_expression)
返回date_expression中的年份值

4、DATEADD()
DATEADD (<datepart>, <number>, <date>)
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。

Datepart指的是时分秒日月年等

number 使用来增加 datepart 的值。正数表示增加,负数表示减少,如果是小数则忽略小数部分,且不做四舍五入。

5、DATEDIFF()
DATEDIFF (<datepart>, <date1>, <date2>)
返回两个指定日期在datepart 方面的不同之处,即date2 超过date1的差距值,其结果值是一个带有正负号的整数值。

用DATEADD和DATEDIFF得到特殊的日期实例

1)一年的第一天

print dateadd(year,datediff(year,0,getdate()),0)

注解:首先DATEDIFF(YEAR,0,GETDATE()) --计算当前年份与 1900年相差的年数,然后通过计算1900-1-1加上相差的年数的日期即为当年第一天

2) 一个季的第一天

print dateadd(quarter,datediff(quarter,0,getdate()),0)

注：首先DATEDIFF(Quarter,0,GETDATE()) --计算当前季份与 1900年相差的季份数,然后通过计算1900-1-1加上相差的季份数的日期即为当季第一天

 

3) 一个月的第一天

print dateadd(month,datediff(month,0,getdate()),0)

注：首先DATEDIFF(Quarter,0,GETDATE()) --计算当前月份与 1900年相差的月份数,然后通过计算1900-1-1加上相差的月份数的日期即为当季第一天

4) 一周的第一天

print dateadd(week,datediff(week,0,getdate()),0)

5) 当天的半夜

print dateadd(DAY,datediff(DAY,0,getdate()),0)

6) 上月的最后一天

print dateadd(ms,-3,dateadd(month,datediff(month,0,getdate()),0))

7) 本月的最后一天

print dateadd(ms,-3,dateadd(month,datediff(month,0,getdate())+1,0))

8) 本月的天数

1.print day(dateadd(ms,-3,dateadd(month,datediff(month,0,getdate())+1,0)))--得到本月的天数,即得到本月最后一天的天

2.

print 32-day(getdate())--如果本月是32天的话剩余的天数

print getdate()+(32-day(getdate()))--上面剩余的天数加上当前的天数一定到了下一个月

print 32-day(getdate()+(32-day(getdate())))--用32减去超过的部分就是本月的天数

 

6、DATENAME()
DATENAME (<datepart>, <date>)
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。

7、DATEPART()
DATEPART (<datepart>, <date>)
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART (dd, date) 等同于DAY (date)
DATEPART (mm, date) 等同于MONTH (date)
DATEPART (yy, date) 等同于YEAR (date)

8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间.

 

 

 

5.10.8 计算长度函数

1. Len

返回给定字符串表达式的字符(而不是字节)个数,其中不含尾随空格

2.  Datalength

返回任意表达式所占用的字节数

两者的区别:

Len只返回字符数,一个汉字代表一个字符 。Datalength返回的是字节数,一个汉字两个字节。Len不包含空格在内长度,而datalength包含空格。

5.11. 存储过程常用设置

5.11.1 set xact_abort on

并不是所有的错误都会被catch所捕获。对于严重级别为0-10（信息性消息）和20-25（致命性消息）是不能被catch到的，这时如果在事务中遇到了这类的报错那么通常会导致事务处于打开状态，这时就需要开启xact_abort。当开启xact_abort后只要代码中存在报错就会执行回滚操作（即便是已经提交成功的子事务也会回滚），而不管错误的级别。

5.11.2 Set nocount on 

作用:阻止在结果集中返回受t-sql语句或者usp影响的行计数信息

当set nocount on 时,不返回计数,当set nocount off时,返回计数,即使set nocount on,也更新@@rowcount

当 set nocount on时,将不给客户端发送存储过程中每个语句的DONE_IN_PROC信息,当使用sqlserver提供的实用工具执行查询时,在t-sql结束时将不会再查询结果中系那是nn rows affected

如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能

Set oncount on 是在执行或者运行时设置,而不是在分析时设置(分析时需要查看相关信息)

建议:我们应该在存储过程的头部加上set nocount on ,在退出存储过程的时候加上set nocount off,以达到优化存储过程的目的.

 

 

6.存储过程的设计思路

第一步:明确存储过程功能以及返回结果

第二步:列出需要的参数

第三步:列出所使用到的表

第四步:定义需要的变量

第五步:写日志(如果需要)

第六步:把业务封装到事务中(如果需要用到事务)

第七步:异常处理

第八部:返回结果