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