SQLSERVER存储过程基本语法使用

1一、定义变量
2--简单赋值 
3declare @a int
4set @a=5 
5print @a 
6  
7--使用select语句赋值 
8declare @user1 nvarchar(50) 
9select @user1='张三'
10print @user1 
11declare @user2 nvarchar(50) 
12select @user2 = Name from ST_User where ID=1 
13print @user2 
14  
15--使用update语句赋值 
16declare @user3 nvarchar(50) 
17update ST_User set @user3 = Name where ID=1 
18print @user3
19 
20
21二、表、临时表、表变量
22
23--创建临时表1 
24create table #DU_User1 
25( 
26     [ID] [int]  NOT NULL, 
27     [Oid] [int] NOT NULL, 
28     [Login] [nvarchar](50) NOT NULL, 
29     [Rtx] [nvarchar](4) NOT NULL, 
30     [Name] [nvarchar](5) NOT NULL, 
31     [Password] [nvarchar](max) NULL, 
32     [State] [nvarchar](8) NOT NULL
33); 
34--向临时表1插入一条记录 
35insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State) values (100,2,'LS','0000','临时','321','特殊'); 
36  
37--从ST_User查询数据，填充至新生成的临时表 
38select * into #DU_User2 from ST_User where ID<8 
39  
40--查询并联合两临时表 
41select * from #DU_User2 where ID<3 union select * from #DU_User1 
42  
43--删除两临时表 
44drop table #DU_User1 
45drop table #DU_User2
46 
47--创建临时表 
48CREATE TABLE #t 
49( 
50    [ID] [int] NOT NULL, 
51    [Oid] [int] NOT NULL, 
52    [Login] [nvarchar](50) NOT NULL, 
53    [Rtx] [nvarchar](4) NOT NULL, 
54    [Name] [nvarchar](5) NOT NULL, 
55    [Password] [nvarchar](max) NULL, 
56    [State] [nvarchar](8) NOT NULL, 
57) 
58  
59--将查询结果集（多条数据）插入临时表 
60insert into #t select * from ST_User 
61--不能这样插入 
62--select * into #t from dbo.ST_User 
63  
64--添加一列，为int型自增长子段 
65alter table #t add [myid] int NOT NULL IDENTITY(1,1) 
66--添加一列，默认填充全球唯一标识 
67alter table #t add [myid1] uniqueidentifier NOT NULL default(newid()) 
68  
69select * from #t 
70drop table #t
71--给查询结果集增加自增长列 
72  
73--无主键时： 
74select IDENTITY(int,1,1)as ID, Name,[Login],[Password] into #t from ST_User 
75select * from #t 
76  
77--有主键时： 
78select (select SUM(1) from ST_User where ID<= a.ID) as myID,* from ST_User a order by myID
79--定义表变量 
80declare @t table
81( 
82    id int not null, 
83    msg nvarchar(50) null
84) 
85insert into @t values(1,'1') 
86insert into @t values(2,'2') 
87select * from @t
88 三、循环
89
90--while循环计算1到100的和 
91declare @a int
92declare @sum int
93set @a=1 
94set @sum=0 
95while @a<=100 
96begin
97    set @sum+=@a 
98    set @a+=1 
99end
100print @sum
101四、条件语句
102
103--if,else条件分支 
104if(1+1=2) 
105begin
106    print '对'
107end
108else
109begin
110    print '错'
111end
112  
113--when then条件分支 
114declare @today int
115declare @week nvarchar(3) 
116set @today=3 
117set @week=case
118    when @today=1 then '星期一'
119    when @today=2 then '星期二'
120    when @today=3 then '星期三'
121    when @today=4 then '星期四'
122    when @today=5 then '星期五'
123    when @today=6 then '星期六'
124    when @today=7 then '星期日'
125    else '值错误'
126end
127print @week
128 
129
130五、游标
131
132declare @ID int
133declare @Oid int
134declare @Login varchar(50) 
135  
136--定义一个游标 
137declare user_cur cursor for select ID,Oid,[Login] from ST_User 
138--打开游标 
139open user_cur 
140while @@fetch_status=0 
141begin
142--读取游标 
143    fetch next from user_cur into @ID,@Oid,@Login 
144    print @ID 
145    --print @Login 
146end
147close user_cur 
148--摧毁游标 
149deallocate user_cur
150六、触发器
151
152　　触发器中的临时表：
153
154　　Inserted 
155　　存放进行insert和update 操作后的数据 
156　　Deleted 
157　　存放进行delete 和update操作前的数据
158
159--创建触发器 
160Create trigger User_OnUpdate  
161    On ST_User  
162    for Update 
163As 
164    declare @msg nvarchar(50) 
165    --@msg记录修改情况 
166    select @msg = N'姓名从“' + Deleted.Name + N'”修改为“' + Inserted.Name + '”' from Inserted,Deleted 
167    --插入日志表 
168    insert into [LOG](MSG)values(@msg) 
169      
170--删除触发器 
171drop trigger User_OnUpdate
172七、存储过程
173
174--创建带output参数的存储过程 
175CREATE PROCEDURE PR_Sum 
176    @a int, 
177    @b int, 
178    @sum int output
179AS
180BEGIN
181    set @sum=@a+@b 
182END
183  
184--创建Return返回值存储过程 
185CREATE PROCEDURE PR_Sum2 
186    @a int, 
187    @b int
188AS
189BEGIN
190    Return @a+@b 
191END
192      
193--执行存储过程获取output型返回值 
194declare @mysum int
195execute PR_Sum 1,2,@mysum output
196print @mysum 
197  
198--执行存储过程获取Return型返回值 
199declare @mysum2 int
200execute @mysum2= PR_Sum2 1,2 
201print @mysum2
202 
203  
204
205八、自定义函数
206
207　　函数的分类：
208
209　　　　1）标量值函数
210
211　　　　2）表值函数
212
213　　　　　　　　a:内联表值函数
214
215　　　　　　　　b:多语句表值函数
216
217　　　　3）系统函数
218
219　　
220
221--新建标量值函数 
222create function FUNC_Sum1 
223( 
224    @a int, 
225    @b int
226) 
227returns int
228as
229begin
230    return @a+@b 
231end
232  
233--新建内联表值函数 
234create function FUNC_UserTab_1 
235( 
236    @myId int
237) 
238returns table
239as
240return (select * from ST_User where ID<@myId) 
241  
242--新建多语句表值函数 
243create function FUNC_UserTab_2 
244( 
245    @myId int
246) 
247returns @t table
248( 
249    [ID] [int] NOT NULL, 
250    [Oid] [int] NOT NULL, 
251    [Login] [nvarchar](50) NOT NULL, 
252    [Rtx] [nvarchar](4) NOT NULL, 
253    [Name] [nvarchar](5) NOT NULL, 
254    [Password] [nvarchar](max) NULL, 
255    [State] [nvarchar](8) NOT NULL
256) 
257as
258begin
259    insert into @t select * from ST_User where ID<@myId 
260    return
261end
262  
263--调用表值函数 
264select * from dbo.FUNC_UserTab_1(15) 
265--调用标量值函数 
266declare @s int
267set @s=dbo.FUNC_Sum1(100,50) 
268print @s 
269  
270--删除标量值函数 
271drop function FUNC_Sum1
272谈谈自定义函数与存储过程的区别：
273
274一、自定义函数：
275
276　　1. 可以返回表变量
277
278　　2. 限制颇多，包括
279
280　　　　不能使用output参数；
281
282　　　　不能用临时表；
283
284　　　　函数内部的操作不能影响到外部环境；
285
286　　　　不能通过select返回结果集；
287
288　　　　不能update，delete，数据库表；
289
290　　3. 必须return 一个标量值或表变量
291
292　　自定义函数一般用在复用度高，功能简单单一，争对性强的地方。
293
294二、存储过程
295
296　　1. 不能返回表变量
297
298　　2. 限制少，可以执行对数据库表的操作，可以返回数据集
299
300　　3. 可以return一个标量值，也可以省略return
301
302　　　存储过程一般用在实现复杂的功能，数据操纵方面。