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();