Sql Server使用存储过程
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();
页:
[1]