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