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

关键词：

create procedure xxx（）as.......go

查询： exec  ...

从例子中感悟一下：

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

得到

例题要求：若句子结尾没有‘，(逗号)‘！’（感叹号）‘。’（句号）‘？’（问号），则为其统一加上‘，’（逗号）。

这里需要用到内嵌函数：right（x,y）:不熟悉内嵌函数，可以认真看下：

http://t.csdn.cn/oC845

可更新的游标： 

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

得到：

如果不加上

 where current of line_cursor

 会出现如下情况：

 WHERE CURRENT OF子句可用于基于游标的嵌入式SQL UPDATE或DELETE语句，以指定位于要更新或删除记录上的游标。

例如上面这个例子，更新最后一个FETCH命令从“line_cursor”游标获得的行。

关于WHERE和WHERE CURRENT OF

嵌入式SQL UPDATE或DELETE可以使用WHERE子句(不带游标)或WHERE CURRENT OF(带声明游标)，但不能同时使用两者。
如果指定的UPDATE或DELETE既不带WHERE也不带WHERE CURRENT OF，则会更新或删除表中的所有记录。

更新的限制
当使用WHERE CURRENT OF子句时，不能使用当前字段值更新字段以生成更新的值。
例如

SET Salary=Salary+100或SET Name=UPPER(Name)

尝试这样做会导致SQLCODE -69错误:

SET <field> = <value expression> not allowed with WHERE CURRENT OF <cursor>。

参照原文：https://blog.csdn.net/yaoxin521123/article/details/121602006

注意：这里是update，如果想要重新编写代码，可以

drop table cartoon

再将上面的创建和插入语句运行一遍

 进入正题：存储过程的建立：

例题要求： exec n：输出1到n的语句

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

输入

exec ShowCartoon 3

得到

注：如果想直接修改，他会提示你“数据库已存在名为“xxx”的对象”

drop procedure ShowCartoon

再此运行编写后的代码就行

例题2要求：输出

第一个动画片段：

xxxxxx

xxxxxx（两句话）

方法1：

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

得到：

方法2：

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

记得先drop procedure ShowCartoon，再

exec ShowCartoon2

得到

建立存储过程 2 ，在存储过程 2 中调用存 储过程 1

这个例子不好举例，我们建立这样一张表，建表过程忽略：

 建立存储过程：

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;

讲存储过程1的形式扩展到存储过程2，使得存储过程代码看起来更加简洁

得到：