关键词:
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 -----定义游标forselect name,line from cartoon -----name和line两个变量参与游标移动过程set @num=1open word_cursor fetch next from word_cursor into @name,@linetextwhile @@FETCH_STATUS = 0beginprint cast(@num as varchar(10)) +':'+@name+'说:'+@linetextfetch next from word_cursor into @name,@linetext -----移动游标set @num=@num+1 ------记录游标移动的行数endclose word_cursordeallocate word_cursor
得到
例题要求:若句子结尾没有‘,(逗号)‘!’(感叹号)‘。’(句号)‘?’(问号),则为其统一加上‘,’(逗号)。
这里需要用到内嵌函数:right(x,y):不熟悉内嵌函数,可以认真看下:
/oC845
可更新的游标:
declare @num int,@linetext varchar(50)declare line_cursor cursorforselect line from cartoon for update;---------for updateset @num=1open line_cursorfetch next from line_cursor into @linetextwhile @@FETCH_STATUS=0beginif(right(@linetext,1) not in(',','。','!','?'))update cartoon set line =@linetext+'。' where current of line_cursorfetch next from line_cursor into @linetextset @num=@num+1endclose line_cursordeallocate 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>。
参照原文:/yaoxin521123/article/details/12160
注意:这里是update,如果想要重新编写代码,可以
drop table cartoon
再将上面的创建和插入语句运行一遍
进入正题:存储过程的建立:
例题要求: exec n:输出1到n的语句
create procedure ShowCartoon(@v_linenum varchar(100) output -------v_linenum表示需要输出的语句个数) asbegindeclare @linetext varchar(100);declare line_cursor cursor forselect line from cartoonwhere linenum<=@v_linenumorder by linenum;open line_cursorfetch next from line_cursor into @linetextwhile @@FETCH_STATUS=0beginprint @linetext;fetch next from line_cursor into @linetext;endclose line_cursordeallocate line_cursor;endgo
输入
exec ShowCartoon 3
得到
注:如果想直接修改,他会提示你“数据库已存在名为“xxx”的对象”
drop procedure ShowCartoon
再此运行编写后的代码就行
例题2要求:输出
第一个动画片段:
xxxxxx
xxxxxx(两句话)
方法1:
create procedure ShowCartoon2asdeclare @linetext varchar(100);declare @num int;declare @num2 int;declare @name varchar(30);set @num2=1declare cursor_linetext cursor forselect name,line from cartoonorder by linenum;open cursor_linetextfetch next from cursor_linetext into @name,@linetext;while @@FETCH_STATUS=0beginprint '第'+cast(@num2 as varchar)+'个动画片段:'set @num=1while(@num<=2)beginprint @name+'说:'+@linetextfetch next from cursor_linetext into @name,@linetext;set @num=@num+1endset @num2=@num2+1endclose cursor_linetext;deallocate cursor_linetext;go
调用存储过程
exec ShowCartoon2
得到:
方法2:
create procedure ShowCartoon2asdeclare @linenum int;declare @linetext varchar(100);declare @name varchar(30);declare cursor_linetext cursor forselect linenum,name,line from cartoonorder by linenum;open cursor_linetextfetch next from cursor_linetext into @linenum,@name,@linetext;while @@FETCH_STATUS=0beginif @linenum%2=1beginprint '第'+cast((@linenum+1)/2 as varchar(5))+'个动画片段:'print @name+'说:'+@linetextendelsebeginprint @name +'答:'+@linetextprint '' endfetch next from cursor_linetext into @linenum,@name,@linetext;endclose cursor_linetext;deallocate cursor_linetext;go
记得先drop procedure ShowCartoon,再
exec ShowCartoon2
得到建立存储过程2,在存储过程2中调用存储过程1这个例子不好举例,我们建立这样一张表,建表过程忽略:
建立存储过程:
CREATE PROCEDURE SanGuoHuman(@v_name VARCHAR(20) OUTPUT)ASBEGINDECLARE @name VARCHAR(20);DECLARE @mypower int;DECLARE @weapon VARCHAR(20);DECLARE @horse VARCHAR(20);DECLARE @birthyear int;DECLARE sanguo_cursor CURSOR FORSELECT name, mypower, weapon, horse, birthyear from SanGuoWHERE name = @v_name;OPEN sanguo_cursor;FETCH NEXT FROM sanguo_cursor INTO @name, @mypower, @weapon, @horse, @birthyear;WHILE @@FETCH_STATUS = 0BEGINPRINT '姓名:'+@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;ENDCLOSE sanguo_cursor;DEALLOCATE sanguo_cursor;ENDGO
调用
EXEC sanguohuman '刘备';
得到:
调用这一存储过程
CREATE PROCEDURE SanGuoBrotherASDECLARE @name VARCHAR(20);DECLARE cursor_human CURSOR FORSELECT name FROM SanGuo ORDER BY birthyear ASC;BEGINOPEN cursor_human;FETCH NEXT FROM cursor_human INTO @name;WHILE @@FETCH_STATUS = 0BEGINexec SanGuoHuman @name;------调用存储过程1FETCH NEXT FROM cursor_human INTO @name; ENDCLOSE cursor_human;DEALLOCATE cursor_human;ENDGOEXEC SanGuoBrother;
讲存储过程1的形式扩展到存储过程2,使得存储过程代码看起来更加简洁
得到: