关键词：
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，使得存储过程代码看起来更加简洁
得到：
