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()
begin
 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);
end@

--执行存储过程
call proc_publisher()

--删除存储过程
drop procedure proc_publisher;

3 输入参数

connect to StudentTest;

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))
begin
 insert into tbl_classinfo values(v_classno,v_classname);
end@

create procedure pro_modifyByclassno(in v_classno character(2))
begin
 for classfor as select classname from tbl_classinfo where classno=v_classno
 do
  insert into tbl_result values(classfor.classname);
 end for;
end@

--执行存储过程
call pro_classinfo('10','体育')
call pro_modifyByclassno('10');

--删除存储过程
drop procedure pro_classinfo
drop procedure pro_modifyByclassno

4 输出参数

select * from tbl_result;

create table tbl_result
(
 result varchar(100)
)

create procedure proc_out(out v_classname varchar(10),in v_classno character(2))
begin
 for classfor as select classname from tbl_classinfo where classno=v_classno
 do
  set v_classname = classfor.classname;
 end for;
end@

用另一个存储过程使用输出参数
create procedure pro_test()
begin
 declare v_classname varchar(10);
 call proc_out(v_classname,'10');
 insert into tbl_result values(v_classname);
end@

call pro_test();