70      阿裏雲  技術社區[雲棲]

Sql Server使用存儲過程

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 20:19:33

  上一篇： Linq刪除中報錯——無法刪除尚未附加的實體

  下一篇： Java關鍵字throw和throws