Sql Server使用存储过程

 1 存储过程基本语句①

connect to studenttest;

select * from tbl_classinfo

--创建存储过程

create procedure proc_demo()

begin

 declare v_i int default 10;

 declare v_classname varchar(20);

 set v_classname='德语';

 insert into tbl_classinfo values('007',v_classname);

end@

--执行存储过程

call proc_demo

--删除存储过程

drop procedure proc_demo

2 存储过程基本语句②

connect to bookshop

select * from tbl_publisher

create procedure proc_publisher()

 declare v_pubid num(10);

 declare v_name varchar(23);

 declare v_contact varchar(15);

 declare v_phone varchar(12);

 set v_pubid=100;

 set v_name='xy出版社';

 set v_contact='111';

 set v_phone='111';

 insert into tbl_publisher values(v_pubid,v_name,v_contact,v_phone);

call proc_publisher()

drop procedure proc_publisher;

3 输入参数

select * from tbl_classinfo;

select * from tbl_result;

create table tbl_result

(

 result varchar(100)

)

create procedure pro_classinfo(in v_classno character(2), in v_classname varchar(10))

 insert into tbl_classinfo values(v_classno,v_classname);

create procedure pro_modifybyclassno(in v_classno character(2))

 for classfor as select classname from tbl_classinfo where classno=v_classno

 do

  insert into tbl_result values(classfor.classname);

 end for;

call pro_classinfo('10','体育')

call pro_modifybyclassno('10');

drop procedure pro_classinfo

drop procedure pro_modifybyclassno

4 输出参数

create procedure proc_out(out v_classname varchar(10),in v_classno character(2))

  set v_classname = classfor.classname;

用另一个存储过程使用输出参数

create procedure pro_test()

 declare v_classname varchar(10);

 call proc_out(v_classname,'10');

 insert into tbl_result values(v_classname);

call pro_test();