drop procedure up_pub
go
create PROCEDURE up_pub
as
begin
declare @regino char(25)
declare @endTime datetime
declare cur cursor for
select regino,endTime
from a
open cur
fetch cur into @regino,@endTime
while @@sqlstatus=0
begin
update b set savetime=@endTime where regino=@regino
fetch cur into @regino,@endTime
end
go
create PROCEDURE up_pub
as
begin
declare @regino char(25)
declare @endTime datetime
declare cur cursor for
select regino,endTime
from a
open cur
fetch cur into @regino,@endTime
while @@sqlstatus=0
begin
update b set savetime=@endTime where regino=@regino
fetch cur into @regino,@endTime
end
end
变量名需要前面加上@,比如@ABC
每行行末不需要分号(;)
基本结构
CREATE PROCEDURE 存储过程名字
@参数1 VARCHAR(100), //输入参数
@参数2 INT, //输入参数
@参数3 INT OUTPUT //输出参数
as
declare
@变量1 INT;
@变量2 DATE;
BEGIN
xxxx
END
IF 判断
IF @V_TEST=1
BEGIN
do something
END
while 循环
WHILE @V_TEST=1
BEGIN
XXXX
END
使用cursor
...
as
declare cur_name CURSOR for SELECT * FROM xxx(表名,在触发器中可以是inserted,deleted)
open cur_name
fetch cur_name into xxx,xxx...
begin
set @V_SUM =@xx+@yy
end