数据库sql语句-----游标和存储过程

create table cartoon(
linenum int,
name varchar(50) not null,
line varchar(100) not null
)
insert into cartoon values(1,'灰太狼','我一定会回来的！')
insert into cartoon values(2,'老爹','妖魔鬼怪快离开，妖魔鬼怪快离开')
insert into cartoon values(3,'胡图图','看我的动耳神功')
insert into cartoon values(4,'小智','就决定是你了，妙蛙种子！')
insert into cartoon values(5,'不二周助','正因为我看不见，所以才能赢')
insert into cartoon values(6,'大头儿子','大头大头下雨不愁，人家有伞，我有大头。')

declare @name varchar(10),@num int,@linetext varchar(50)
declare word_cursor cursor    -----定义游标
for
  select name,line from cartoon -----name和line两个变量参与游标移动过程
set @num=1
open word_cursor 
fetch next from word_cursor into @name,@linetext
while @@FETCH_STATUS = 0
begin
  print cast(@num as varchar(10)) +':'+@name+'说：'+@linetext
  fetch next from word_cursor into @name,@linetext    -----移动游标
  set @num=@num+1   ------记录游标移动的行数
end
close word_cursor
deallocate word_cursor

declare @num int,@linetext varchar(50)
declare line_cursor cursor
for
  select line from cartoon for update;---------for update
set @num=1
open line_cursor
fetch next from line_cursor into @linetext
while @@FETCH_STATUS=0
begin
  if(right(@linetext,1) not in('，','。','！','？'))
    update cartoon set line =@linetext+'。' where current of line_cursor
  fetch next from line_cursor into @linetext
  set @num=@num+1
end
close line_cursor
deallocate line_cursor

create procedure ShowCartoon(
 @v_linenum varchar(100) output    -------v_linenum表示需要输出的语句个数
) 
as
begin
declare @linetext varchar(100);
declare line_cursor cursor for
  select line from cartoon
  where linenum<=@v_linenum
    order by linenum;
  open line_cursor
  fetch next from line_cursor into @linetext
  while @@FETCH_STATUS=0
  begin
  print @linetext;
  fetch next from line_cursor into @linetext;
    end
  close line_cursor
  deallocate line_cursor;
end
go

create procedure ShowCartoon2
as
  declare @linetext varchar(100);
  declare @num int;
  declare @num2 int;
  declare @name varchar(30);
  set @num2=1
  declare cursor_linetext cursor 
    for
  select name,line from cartoon
    order by linenum;
  open cursor_linetext
  fetch next from cursor_linetext into @name,@linetext;
  while @@FETCH_STATUS=0
  begin
  print '第'+cast(@num2 as varchar)+'个动画片段：'
  set @num=1
  while(@num<=2)
  begin
  print @name+'说：'+@linetext
  fetch next from cursor_linetext into @name,@linetext;
  set @num=@num+1
  end
  set @num2=@num2+1
  end
  close cursor_linetext;
  deallocate cursor_linetext;
go

exec ShowCartoon2

create procedure ShowCartoon2
as
    declare @linenum int;
  declare @linetext varchar(100);
  declare @name varchar(30);
  declare cursor_linetext cursor 
    for
  select linenum,name,line from cartoon
    order by linenum;
  open cursor_linetext
  fetch next from cursor_linetext into @linenum,@name,@linetext;
  while @@FETCH_STATUS=0
  begin
        if @linenum%2=1
        begin
      print '第'+cast((@linenum+1)/2 as varchar(5))+'个动画片段：'
            print @name+'说：'+@linetext
  end
        else
        begin
           print @name +'答：'+@linetext
           print '' 
        end
        fetch next from cursor_linetext into @linenum,@name,@linetext;
  end
  close cursor_linetext;
  deallocate cursor_linetext;
go

exec ShowCartoon2

CREATE PROCEDURE SanGuoHuman(
 @v_name VARCHAR(20) OUTPUT
)
AS
BEGIN
 DECLARE @name VARCHAR(20);
 DECLARE @mypower int;
 DECLARE @weapon VARCHAR(20);
 DECLARE @horse VARCHAR(20);
 DECLARE @birthyear int;
 DECLARE sanguo_cursor CURSOR FOR
 SELECT name, mypower, weapon, horse, birthyear from SanGuo
 WHERE name = @v_name;
 OPEN sanguo_cursor;
 FETCH NEXT FROM sanguo_cursor INTO @name, @mypower, @weapon, @horse, @birthyear;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 PRINT '姓名：'+@name;
 PRINT '武力：' + CAST( @mypower AS VARCHAR );
 PRINT '兵器：' + @weapon;
 PRINT '坐骑：' + @horse;
 PRINT '出生年份：公元' + CAST( @birthyear AS VARCHAR );
 PRINT ' ';
 FETCH NEXT FROM sanguo_cursor INTO @name, @mypower, @weapon, @horse, @birthyear;
 END
 CLOSE sanguo_cursor;
 DEALLOCATE sanguo_cursor;
END
GO

EXEC sanguohuman '刘备';

CREATE PROCEDURE SanGuoBrother
AS
 DECLARE @name VARCHAR(20);
 DECLARE cursor_human CURSOR FOR
 SELECT name FROM SanGuo ORDER BY birthyear ASC;
BEGIN
 OPEN cursor_human;
 FETCH NEXT FROM cursor_human INTO @name;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 exec SanGuoHuman @name;     ------调用存储过程1
 FETCH NEXT FROM cursor_human INTO @name; 
 END
 CLOSE cursor_human;
 DEALLOCATE cursor_human;
END
GO
EXEC SanGuoBrother;