搜索泥称 发表于 2016-11-2 08:17:19

SQL Server 2000 存储过程练习

     
  
  存储过程的创建和执行:
  CREATE PROCEDURE
(@id_1 ,
@name_2 (10),
@password_3 (50),
@sex_4 (6),
@birth_5 ,
@salary_7 )
  AS INSERT INTO ..
( ,
,
,
,
,
)

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
(@id_1 ,
@name_2 (10),
@password_3 (50),
@sex_4 (6),
@birth_5 ,
@salary_7 )
  AS INSERT INTO ..
( ,
,
,
,
,
)

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
页: [1]
查看完整版本: SQL Server 2000 存储过程练习