存储过程是由一个或多个T-SQL语句组成的语句组,它可以接受输入参数和输出参数且能向调用程序返回多个值;可以调用其他过程;可以向调用程序返回结果状态值以标记执行成功或失败;

1.SQL server存储过程

  1. 变量的声明

声明变量时必须在变量前加@符号: declare @i int(声明变量数据类型为int类型的i)
declare @s varchar(10),@a int;(声明多个变量)
print @s 打印变量值
每条语句以”;”结尾

  1. 变量的赋值
    • 变量赋值时变量前必须加set:set @i = 5;
    • 使用select语句赋值:
  2. 循环语句

begin…end 类似于编程语言的{}

if语句

1
2
3
4
5
6
7
8
9
10
11
12
if ..
begin
...
end
else if ..
begin
...
end
else
begin
...
end

eg:

1
2
3
4
5
6
7
8
DECLARE @d INT
set @d = 1
IF @d = 1 BEGIN
PRINT '正确'
END
ELSE BEGIN
PRINT '错误'
END

多条件查询语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 多条件选择语句
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

while循环语句:

1
2
3
4
5
declare @i int 
set @i = 1
while @i<1000 begin
set @i = @i+1
end
  1. 定义游标
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--创建一个游标

--my_cursor为游标的名称,随便起
declare my_cursor cursor for
--这是游标my_cursor的值,这里随便发挥看业务场景
select id,name from my_user

--打开游标
open my_cursor
--声明变量
declare @id int --声明变量 ‘declare’为声明变量 ‘@name’为变量名称 后面为变量类型
declare @name varchar(50) --这里是两个变量用来接收游标的值
--循环游标
fetch next from my_cursor into @id,@name --获取my_cursor的下一条数据,其中为两个字段分别赋值给@id,@name
while @@FETCH_STATUS=0 --假如检索到了数据继续执行
begin
print(@name) --print()打印变量 随便发挥
select * from my_user where id=@id --这里是具体业务了,随便发挥。而我这是又执行了一次查询
fetch next from my_cursor into @id,@name --获取下一条数据并赋值给变量
end
--关闭并释放游标
close my_cursor
deallocate my_cursor
  1. 临时表(参考sql系列更新六)

  2. SQL server的存储过程创建语法:

GO在存储过程或语句中起结束,终止上面语句的作用

  • 创建语法
1
2
3
4
5
6
7
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
  • 创建不带参数的存储过程
1
2
3
4
5
6
7
8
9
10
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;

--调用、执行存储过程
exec proc_get_student;
  • 带参数的存储过程
1
2
3
4
5
6
7
8
9
10
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create procedure proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;
  • 带通配符参数的存储过程
1
2
3
4
5
6
7
8
9
10
11
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
  • 带输出参数的存储过程★★
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go

--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
-- exec代表执行的含义
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
  • 带游标参数存储过程★★
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
if (object_id('proc_cursor', 'P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
-- 这里是具体业务了,随便发挥
select id, name, age from student;
open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标

参考文章


 评论

联系我 | Contact with me

Copyright © 2019-2020 谁知你知我,我知你知深。此恨经年深,比情度日久

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议