146
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();
最后更新：2017-04-03 07:57:19