使用存储过程

--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='徐越出版社';<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();