Sql Server使用存储过程

hti

1 存储过程基本语句①
connect to StudentTest;　　select * from tbl_classinfo
　　--创建存储过程
create procedure proc_demo()
begin
<wbr>declare v_i int default 10;<br><wbr>declare v_classname varchar(20);<br><wbr>set v_classname='德语';<br><wbr>insert into tbl_classinfo values('007',v_classname);<br>end@</wbr></wbr></wbr></wbr>
　　--执行存储过程
call proc_demo
　　--删除存储过程
drop procedure proc_demo
　　<wbr></wbr>
　　
2 存储过程基本语句②
　　connect to bookshop
　　select * from tbl_publisher
　　--创建存储过程
create procedure proc_publisher()
begin
<wbr>declare v_pubid num(10);<br><wbr>declare v_name varchar(23);<br><wbr>declare v_contact varchar(15);<br><wbr>declare v_phone varchar(12);<br><wbr>set v_pubid=100;<br><wbr>set v_name='xy出版社';<br><wbr>set v_contact='111';<br><wbr>set v_phone='111';<br><wbr>insert into tbl_publisher values(v_pubid,v_name,v_contact,v_phone);<br>end@</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
　　--执行存储过程
call proc_publisher()
　　--删除存储过程
drop procedure proc_publisher;
　　<wbr></wbr>
　　
3 输入参数
　　connect to StudentTest;
　　select * from tbl_classinfo;
　　select * from tbl_result;
　　create table tbl_result
(
<wbr>result varchar(100)<br>)</wbr>
　　--创建存储过程
create procedure pro_classinfo(in v_classno character(2), in v_classname varchar(10))
begin
<wbr>insert into tbl_classinfo values(v_classno,v_classname);<br>end@</wbr>
　　create procedure pro_modifyByclassno(in v_classno character(2))
begin
<wbr>for classfor as select classname from tbl_classinfo where classno=v_classno<br><wbr>do<br><wbr><wbr>insert into tbl_result values(classfor.classname);<br><wbr>end for;<br>end@</wbr></wbr></wbr></wbr></wbr>
　　--执行存储过程
call pro_classinfo('10','体育')
call pro_modifyByclassno('10');
　　--删除存储过程
drop procedure pro_classinfo
drop procedure pro_modifyByclassno
　　<wbr></wbr>
　　4 输出参数
　　select * from tbl_result;
　　create table tbl_result
(
<wbr>result varchar(100)<br>)</wbr>
　　create procedure proc_out(out v_classname varchar(10),in v_classno character(2))
begin
<wbr>for classfor as select classname from tbl_classinfo where classno=v_classno<br><wbr>do<br><wbr><wbr>set v_classname = classfor.classname;<br><wbr>end for;<br>end@</wbr></wbr></wbr></wbr></wbr>
　　用另一个存储过程使用输出参数
create procedure pro_test()
begin
<wbr>declare v_classname varchar(10);<br><wbr>call proc_out(v_classname,'10');<br><wbr>insert into tbl_result values(v_classname);<br>end@</wbr></wbr></wbr>
　　call pro_test();