sql存储过程语法详解

大家好，又见面了，我是你们的朋友全栈君。

一、定义变量

使用关键字declare申明变量：

declare @变量名 变量类型

/*简单赋值*/

 declare @a int
 set     @a=5
 print   @a

/*select赋值*/

declare @b nvarchar(10)
select @b= stu_name from dbo.student where stu_id=6
print @b

/*update赋值*/

declare @c  nvarchar(10)
update dbo.student set @c=stu_name where stu_id=5
print @c

运行结果：

ps：使用set赋值时，等式右边不可以用函数表达式。

二、临时表、表变量

临时表：
临时表的创建是在Tempdb中，在一个数据库连接结束后或者由SQL命令DROP掉，才会消失，否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志。在Tempdb中体现，在内存中分配，它们也支持物理的磁盘，但用户在指定的磁盘里看不到文件。

/*创建本地临时表#*/
create table #tablename()

/*创建全局临时表##*/
create table ##tablename()

/*创建一个临时表#student*/
create table #student( id int, username nvarchar(20), sex nvarchar(2), age int ) /*向临时表中插入一条数据*/ insert into #student values(1,'Susan','女',18) /*从student表查询数据，填充至新生成的临时表*/ 1.select * into #student2 from student where stu_age>30 2.insert into #student select * from student where stu_age>30 

由于手误，执行了两次，表中出现了重复项，且每一列值都相同
怎么删除完全重复的项的呢？

delete T from (select ROW_NUMBER() over(partition by username order by id) as rownumber, * from #student) T WHERE T.rownumber>1

语法：ROW_NUMBER( ) OVER ( PARTITION BY 列1 ORDER BY 列2 )
根据COLUMN分组，在分组内部根据 COLUMN排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
函数“Row_Number”必须有 OVER 子句。OVER 子句必须有包含 ORDER BY

/*查询并联合两临时表*/
select * from #student where id >2 union select * from #student2 /*删除两临时表*/ drop table #student drop table #student2

/*添加一列为int型自增长列*/
alter table #student add innerid int not null identity(1,1)

ps：identity（1,1）自增列，从1开始递增，每次加1

/*增加一列，默认填充全球唯一标识*/
alter table #student add myid uniqueidentifier not null default(newid())

表变量：
表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量。

declare @local_variable table()

declare @t table(
  id int not null,
  msg nvarchar(50) not null
)

insert into @t values(1,'1')
insert into @t values(2,'2')
select * from @t

临时表与表变量的区别：

ps：
非聚集索引：数据行的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
聚集索引：与非聚集索引相反。
更多关于聚集索引与非聚集索引：
1.https://www.cnblogs.com/s-b-b/p/8334593.html
2.https://www.cnblogs.com/Jessy/p/3543063.html
更多关于表变量和临时表：
1.https://www.jb51.net/article/23952.htm
2.https://www.cnblogs.com/xinaixia/p/5821548.html

三、循环

while循环计算1到100的和：

declare @d int
declare @sum int
set @d=1
set @sum=0
while @d<100
begin
  set @d+=1
  set @sum+=@d
end
print @sum

四、条件语句

if-else

if（1+1=2）
begin
  printf（"对"）
end
else
begin
  print（"错"）
end

when -then

declare @today int
declare @week nvarchar（3）
set @week=case
set@today=1
   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

例子：导入前一天的数据

DECLARE @y varchar(4) 
DECLARE @m varchar(2) 
DECLARE @d varchar(2)

select  @y=DateName(year,GetDate())
select  @d=DateName(day,dateadd(day,-1,GetDate())) 

if (DateName(day,GetDate())='1')/*如果当天是每月1号*/
select  @m=DateName(month,dateadd(MONTH,-1,GetDate()))/*月份-1*/
else
select  @m=DateName(month,GetDate())

EXEC    [dbo].[InsertQYGPSData2]

        @year = @y,

        @month = @m,

        @day = @d

五、游标

1.定位到结果集中的某一行；
2.对当前位置的数据进行读写；
3.可以对结果集中的数据单独操作，而不是整行执行相同操作。
4.是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

分类：
1.静态游标：不反应结果集中的所有更改
2.动态游标：反应结果集中所有更改
3.只进游标：不支持滚动，只支持从头到尾顺序提取数据
4.键集驱动游标:该游标中的各个成员顺序是固定的的，被标识的列做删改时，用户滚动游标是可见的，未标识则删改不可见。

游标的生命周期：
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]]]

注释：

cursor_name：游标名称。
Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
[Local | Global]:默认为local。
Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
Static:静态游标
KeySet：键集游标
Dynamic：动态游标，不支持Absolute提取选项
Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
Read_Only:不能通过游标对数据进行删改。
Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
For Update[of column_name ,....] :定义游标中可更新的列。

2.声明一个动态游标：

declare OrderNum_cursor sursor scoll
for select OrderID from bigOrder where OrderNum='xxxx'

3.打开游标

open [Global] cursor_name | cursor_variable_name
              /*游标名 游标变量名*/

4.提取数据

Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,….]]

注释：

Frist：结果集的第一行
Prior：当前位置的上一行
Next：当前位置的下一行
Last：最后一行
Absoute n：从游标的第一行开始数，第n行。
Relative n：从当前位置数，第n行。
Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

5.利用游标更新删除数据

/*①游标修改当前数据语法*/
Update 基表名 Set 列名=值[,...] Where Current of 游标名 /*②游标删除当前数据语法*/ Delete 基表名 Where Current of 游标名

6.关闭游标

/*关闭游标语法*/
close [ Global ] cursor_name | cursor_variable_name
/*关闭游标*/
close orderNum_03_cursor

7.删除游标

/*释放游标语法*/
deallocate  [ Global ] cursor_name | cursor_variable_name
/*释放游标*/
deallocate orderNum_03_cursor

六、触发器

原理，优点，作用参考：https://www.cnblogs.com/wangprince2017/p/7827091.html

分类：
1.DML：数据操纵语言
insert，delete，update触发器
2.DDl：数据定义语言
create，drop，alter触发器

语法:

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] AS T-SQL语句 GO --with encryption 表示加密触发器定义的sql文本 --delete,insert,update指定触发器的类型

/*创建触发器 */
Create trigger User_OnUpdate On ST_User for Update As declare @msg nvarchar(50) /*@msg记录修改情况*/ select @msg = N'姓名从“' + Deleted.Name + N'”修改为“' + Inserted.Name + '”' from Inserted,Deleted /*插入日志表*/ insert into [LOG](MSG)values(@msg) /*删除触发器*/ drop trigger User_OnUpdate

七、自定义函数

1.标量函数：返回的结果是一个标量
例：

2.内联表值函数：返回表数据,内联表函数返回的表结构由函数体内的SELECT语句来决定。
例：

create function FUN_Sum1 ( @myid int ) RETURNS table AS RETURN ( select * from St_user where ID<@myid ) GO

3.多语句表值函数：已经定义好要返回的表中的字段

create function FUNC_UserTab_2 ( @myId int ) RETURNS @t table ( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL ) AS BEGIN insert into @t select * from ST_User where ID<@myId RETURN END GO

4.调用表值函数

select * from dbo.FUNC_UserTab_1(15) 

5.调用标量函数

declare @s int
set @s=dbo.FUNC_Sum1(100,50) 
print @s 

6.删除标量值函数

drop function FUNC_Sum1

尝试写了一个简单的存储过程：

USE [test]
GO
/****** Object: StoredProcedure [dbo].[InsertQYGPSData2] Script Date: 09/05/2018 15:51:27 ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertQYGPSData2] @year varchar(4), @month varchar(2), @day varchar(2) AS BEGIN declare @tablename varchar(20) set @day = right('00'+@day,2) set @tablename='QYGPSData_'+@year+'_'+ @month+'_'+@day IF NOT EXISTS(SELECT NAME FROM sys.objects WHERE NAME=@tablename and TYPE='u') begin exec('create Table ' +@tablename+ ' ( [Create_Date] [datetime] NULL, [CarNum] [varchar](50) NULL, [CarNo] [varchar](50) NULL, [FrontDoorUp] [int] NULL, [FrontDoorDown] [int] NULL, [BackDoorUp] [int] NULL, [BackDoorDown] [int] NULL, [lng] [varchar](50) NULL, [lat] [varchar](50) NULL, [State] [varchar](10) NULL, [CarPassenger] [int] NULL, [Line_Name] [varchar](100) NULL )') end END 

ps：语法：LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)

LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串，ARG可以是CHAR或BINARY STRING。