存儲過程

簡單來說，存儲過程（Stored Procedure）是為以后的使用而保存的一條或多條MySQL語句的集合，可以將其視為批處理，雖然其作用不僅限于批處理

使用存儲過程的優(yōu)點(diǎn)

• 把處理過程封裝，簡化復(fù)雜的操作
• 封裝了過程，統(tǒng)一了輸入輸出，能減少使用錯誤
• 減少對基礎(chǔ)數(shù)據(jù)的訪問，從而減少數(shù)據(jù)訛誤
• 可以編寫更靈活的代碼

delimiter //
一般情況下MYSQL以；結(jié)尾表示確認(rèn)輸入并執(zhí)行語句，但在存儲過程中；不是表示結(jié)束，因此可以用該命令將；號改為//表示確認(rèn)輸入并執(zhí)行。

1、創(chuàng)建存儲過程

delimiter //
create procedure sp_name()
begin
    ......
end//
delimiter ;

2、調(diào)用存儲過程

call sp_name();

3、刪除存儲過程

drop procedure sp_name;
drop procedure if exists sp_name;

4、查看存儲過程

show create procedure sp_name;
show procedure status;

5、IN、OUT、INOUT

• IN 輸入?yún)?shù)
  表示該參數(shù)的值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認(rèn)值
  僅需要將數(shù)據(jù)傳入存儲過程，并不需要返回計算后的該值。
  只能當(dāng)做傳入?yún)?shù)
• OUT 輸出參數(shù)
  該值可在存儲過程內(nèi)部被改變，并可返回
  不接受外部傳入的數(shù)據(jù)，僅返回計算之后的值。
  只能當(dāng)做轉(zhuǎn)出參數(shù)
• INOUT 輸入輸出參數(shù)
  調(diào)用時指定，并且可被改變和返回
  需要數(shù)據(jù)傳入存儲過程經(jīng)過調(diào)用計算后，再傳出返回值
  可當(dāng)做傳入轉(zhuǎn)出參數(shù)

# 根據(jù)員工名稱查出員工所在部門名稱
delimiter //
create procedure pr_dname(in empName varchar(20))
begin
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where ename=empName;
end
//
delimiter ;

# 根據(jù)員工編號，輸入的年數(shù)，查出其總共應(yīng)發(fā)工資
delimiter //
create procedure pr_pay(in empno int(4), in y int(2))
begin
select e.ename,e.sal*12*y as pay from emp e where e.empno=empno;
end
//
delimiter ;

# 輸入部門編號，輸出部門平均工資
delimiter //
create procedure pr_avgsal(in deptno int(2),out avgsal double(7,2))
begin
select avg(e.sal) into avgsal from emp e group by e.deptno having e.deptno=deptno;
end
//
delimiter ;
call pr_avgsal(10,@avgsal);
select @avgsal;

# 輸入月薪，計算年薪
delimiter //
create procedure pr_test1(inout m double(7,2))
begin
select m*12 into m;
end
//
delimiter ;
set @m=1000;
call pr_test1(@m);
select @m;

6、分支語句

delimiter //
create procedure pr_test2(in i int)
begin
select if(i>0,'windows','linux'); #if簡單二分支
end
//
delimiter ;

delimiter //
create procedure pr_test3(in i int)
begin
declare j varchar(20);
# 多分支if語句
if i>0 then set j='windows';
elseif i<0 then set j='linux';
else set j='ios';
end if;
select j;
end
//
delimiter ;

delimiter //
create procedure pr_test4(in i int)
begin
declare j varchar(20);
# case等值判斷
case i
when 1 then set j='windows';
when 0 then set j='ios';
else set j='linux';
end case;
select j;
end
//
delimiter ;

delimiter //
create procedure pr_test5(in i int)
begin
declare j varchar(20);
# case條件區(qū)間判斷
case
when i>0 then set j='windows';
when i<0 then set j='ios';
else set j='linux';
end case;
select j;
end
//
delimiter ;

7、循環(huán)語句

mysql有3種循環(huán)語句

• while
  label:while 條件 do
statements;
end while lable;
• loop
  label:loop
statements;
end loop label;
• repeat
  label:repeat
statements
until 結(jié)束條件;
end repeat label;

# 給dept表插入指定數(shù)目的數(shù)據(jù)
# while循環(huán)
delimiter //
create procedure pr_test12(in n int)
begin
declare i int default 1;
a:while i<=n do
insert into dept (deptno,dname,loc) values(50+i,concat('Sales',i),concat('London',i));
set i=i+1;
end while a;
end
//
delimiter ;

# 給dept表插入指定數(shù)目的數(shù)據(jù)
# loop循環(huán)，并使用leave跳出循環(huán)
delimiter //
create procedure pr_test13(in n int)
begin
declare i int default 1;
a:loop
insert into dept (deptno,dname,loc) values(50+i,concat('Sales',i),concat('London',i));
set i=i+1;
if i>n then leave a;
end if;
end loop a;
end
//
delimiter ;

# 給dept表插入指定數(shù)目的數(shù)據(jù),但不插入5的倍數(shù)的數(shù)據(jù)
# repeat循環(huán),iterate跳出此次執(zhí)行，繼續(xù)執(zhí)行下一次
delimiter //
create procedure pr_test14(in n int)
begin
declare i int default 0;
a:repeat
set i=i+1;
if (i mod 5 = 0) then iterate a;
end if;
insert into dept (deptno,dname,loc) values(50+i,concat('Sales',i),concat('London',i));
until i>=n
end repeat a;
end
//
delimiter ;