关键词:
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):不熟悉内嵌函数,可以认真看下:
可更新的游标:
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
![](https://i-blog.csdnimg.cn/blog_migrate/be17b873183ffa5f26bfa002be7aa6dd.png)
建立存储过程:
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,使得存储过程代码看起来更加简洁
得到: