SQL Server 2000 存储过程练习



---

存储过程的创建和执行：

CREATE PROCEDURE [insert_members_1]
(@id_1 [tinyint],
@name_2 [char](10),
@password_3 [varchar](50),
@sex_4 [char](6),
@birth_5 [datetime],
@salary_7 [money])

AS INSERT INTO [myguest].[dbo].[members]
( [id],
[name],
[password],
[sex],
[birth],
[salary])

VALUES
( @id_1,
@name_2,
@password_3,
@sex_4,
@birth_5,
@salary_7)
GO

execute insert_members_1 1,'bitan','aaaaaaa','male','19200202',$3200.20

结果：

表members将新增一条记录。

---

存储过程的另一个例子：

/*create procedure update_members_id
(
@id int,
@name varchar(20)
)
as update members set name=@name where id=@id */
execute update_members_id 3,'susan';
select * from members;

结果：

3susan aaaaamale 1979-01-09 00:00:00.0000x00000000000001A95000.0000

---

存储过程的创建和执行：

CREATE PROCEDURE [insert_members_1]
(@id_1 [tinyint],
@name_2 [char](10),
@password_3 [varchar](50),
@sex_4 [char](6),
@birth_5 [datetime],
@salary_7 [money])

AS INSERT INTO [myguest].[dbo].[members]
( [id],
[name],
[password],
[sex],
[birth],
[salary])

VALUES
( @id_1,
@name_2,
@password_3,
@sex_4,
@birth_5,
@salary_7)
GO

execute insert_members_1 1,'bitan','aaaaaaa','male','19200202',$3200.20

结果：

表members将新增一条记录。

---

存储过程的另一个例子：

/*create procedure update_members_id
(
@id int,
@name varchar(20)
)
as update members set name=@name where id=@id */
execute update_members_id 3,'susan';
select * from members;

结果：

3susan aaaaamale 1979-01-09 00:00:00.0000x00000000000001A95000.0000

create procedure select_members_by_id @id int
as
select * from members where id=@id;

execute select_members_by_id 3

---

在存储过程中使用子查询：
create procedure multi_operation_members @id int
as
select * from members where id < (select count(*) from members)

execute multi_operation_members 30

---

在存储过程中修改参数值，使用多个查询语句：
create procedure multi_operation_members_2 @id int
as
select @id = (select count(*) from members where id > @id)
select @id = @id - 2
select * from members where id = @id

execute multi_operation_members_2 3

---

在存储过程中使用while循环，使用多个查询语句：

create procedure while_circle_members @id int
as
declare @in int //定义临时变量
select @in = 0 //给临时变量赋初始值
while @in < @id //设定循环条件
begin
select @in = @in + 1 //临时变量增一
select * from members
delete from members where id = @in
end

execute while_circle_members 10

---

在存储过程中调用另一个存储过程

create procedure procedure_1 @str varchar(20)
as
select 'execute procedure_1' as message, @str as name;
go

create procedure procedure_2 @str varchar(20)
as
select 'begin procedure_2' as message, @str as name
execute procedure_1 @str
select 'end procedure_2' as message, @str as name
go
execute procedure_2 'bitan'

结果：

message name
----------------- --------------------
begin procedure_2 bitan

（所影响的行数为 1 行）

message name
------------------- --------------------
execute procedure_1 bitan

（所影响的行数为 1 行）

message name
--------------- --------------------
end procedure_2 bitan

（所影响的行数为 1 行）

在系统表中查看已经创建的存储过程：

/*create proc my_proc_2
as
print 'This is my_proc_2'
return*/

select convert(char(10),so.name) 'name', convert(char(60),sc.text) 'text', convert(char(30),so.crdate) 'crdate' from sysobjects so, syscomments sc where so.id = sc.id and so.name = 'my_proc_2'

结果：

name text crdate
---------- ------------------------------------------------------------ ------------------------------
my_proc_2 create proc my_proc_2
as
print 'This is my_proc_2'
return 03 15 2005 9:53AM

（所影响的行数为 1 行）

使用输入参数与输出参数：

create proc my_proc_4
(@i int, @j int, @z int output)
as
select @z = @i + @j
print 'my_proc_4: ' + rtrim(convert(char,@i)) +
' + ' + rtrim(convert(char,@j)) +
' = ' + rtrim(convert(char,@z))
return

declare @sum int
execute my_proc_4 1,2,@sum output
print 'result: ' + rtrim(convert(char,@sum))
execute my_proc_4 @j=4, @i=-10, @z=@sum output
print 'result: ' + rtrim(convert(char,@sum))

结果：

my_proc_4: 1 + 2 = 3
result: 3
my_proc_4: -10 + 4 = -6
result: -6

给输入参数设置缺省值：

create proc my_proc_5
(@i int = 5, @j int = 6, @z int output)
as
select @z = @i + @j
print 'my_proc_5: ' + rtrim(convert(char,@i)) +
' + ' + rtrim(convert(char,@j)) +
' = ' + rtrim(convert(char,@z))
return

declare @sum int
exec my_proc_5 @z = @sum output
print 'result: ' + rtrim(convert(char,@sum))
exec my_proc_5 @i = -10, @z = @sum output
print 'result: ' + rtrim(convert(char,@sum))
exec my_proc_5 @i = -5, @j = -10, @z = @sum output
print 'result: ' + rtrim(convert(char,@sum))

exec my_proc_5 default, default, @z = @sum output
print 'result: ' + rtrim(convert(char,@sum))
exec my_proc_5 @j = -15, @i = default, @z = @sum output
print 'result: ' + rtrim(convert(char,@sum))

结果：

my_proc_5: 5 + 6 = 11
result: 11
my_proc_5: -10 + 6 = -4
result: -4
my_proc_5: -5 + -10 = -15
result: -15
my_proc_5: 5 + 6 = 11
result: 11
my_proc_5: 5 + -15 = -10
result: -10

利用缺省值进行参数检查：

alter proc my_proc_6
(@i int = 0)
as
declare @s char
if @i = 0
begin
print 'Occur a error: ' + convert(char, @i)
end
else
begin
select @s = convert(char, @i * 100)
print 'result: ' + @s
end
return

exec my_proc_6 default
exec my_proc_6
exec my_proc_6 5

结果：

Occur a error: 0
Occur a error: 0
result: 5

捕获返回状态值：

alter proc my_proc_7
(@s char(5) = 'bitan')
as
if @s is null //注意：当使用@s = null时，下面的 print 'param: null' 语句将不会显示结果。
begin
print 'param: null '
return -2

end
if @s != 'bitan'
begin
print 'param: ' + @s
return -1
end
print 'param: ' + @s
return //注意：没有显式给出返回值，系统默认返回0。

declare @i int
exec @i = my_proc_7
print 'state: ' + convert(char, @i)
exec @i = my_proc_7 null
print 'state: ' + convert(char, @i)
exec @i = my_proc_7 @s = null
print 'state: ' + convert(char, @i)
exec @i = my_proc_7 'aaaaaaaaaaaaaaaaa'
print 'state: ' + convert(char, @i)

结果：

param: bitan
state: 0
param: null
state: -2
param: null
state: -2
param: aaaaa
state: -1