Stored routines

SQL允許儲存procedure 及 function, 此二個都是SQL的元件, 會存放在資料庫的stored procedures中.

procedure及function蠻類似的. procedure沒有傳回值, 但可以使用out 變數, 跟vb的副程式類似, 需於外面使用 call 指令來執行. 而function則有傳回值, 使用方式就像調用一般的函數.

有了function, 為什麼還要有Procedure

procedure可以在php中使用如下方式執行
$sql=”call procedure_name()”;
$result=$conn->query($sql);

但function只能在SQL中執行

Delimiter

SQL使用 “;” 作為預設的delimiter, 要更改delimiter 可以使用如下指令

delimiter $$
use world$$/*此處使用 –會有問題 */
select * from city$$–查詢
delimiter ;–改回原來的delimiter

Stored Procedures

將常用的程序儲存在Server中, 需要時, 再將之叫出來執行.
在建立procedure之前, 需變更delimiter, 讓原本的 “;” 保留出來

Procedure 會在於Server中

建立

use world;
 drop procedure test;
 delimiter $$
 create procedure test(in code varchar(100), out total int, inout c int)
 begin
     select count(*) from city
     where countrycode=code
     into total;
 end $$
 delimiter ;

執行

call test(‘AFG’, @total, @var2);
select @total, @var2;

Procedures 參數

Procedures的參數, 有分 in, out, inout

調用端

in : 傳入, 可為常數, 也可為變數, 不能接收值
out : 傳出, 一定要是變數, 可接收值
inout : 可為常數, 也可為變數

程序端

在執行完後, 使用into 變數名稱傳回

Function

自訂函數, 可以像一般函數被調用.

請注意, 函數在begin之後，變數宣告一定要寫在最前面, 等所有變數宣告完畢後, 才可以開始撰寫程式碼. 不可以寫到一半, 然後又宣告其他的變數

一般公司行號的員工編號, 最常見的格式是一組固定長度的字串, 開頭是某些英文字, 後面才是流水編號, 比如 FL0001, FL0002, 所以如果使用auto_increment就顯得力不從心了.

底下的程式碼說明如何取得最大的員工編號, 然後加1

use mahaljsp;
drop function if exists makeId;
delimiter $$
create function makeId() returns varchar(6)
begin
 declare max_id varchar(6);
 declare pre_id varchar(2);
 declare num_id varchar(4);
 select max(id) from mj_employee into max_id; /*將結果放入max_id, 若沒有into, 會說不能傳回*/
 set pre_id=left(max_id, 2);
 set num_id=substring(max_id, 3, 4)+1;
 return concat(pre_id, lpad(num_id, 4,'0'));
end$$
delimiter ;

insert into mj_employee values (makeId(),'張無忌'), (makeId(), '趟敏');

底下的程式碼, 產生一組亂數, 長度為10, 其值介於 0-10, A-Z, a-z之間

use mahaljsp;
delimiter $$
drop function if exists makeRand$$
create function makeRand() returns varchar(10)
begin
 declare rnd int;
 declare sn varchar(10);
 declare cnt int;
 set sn=""; /*要先初始化, 不然會null*/
 set cnt=0;
 while cnt<10 do
     set rnd=rand()*61; /*產生0~61的數字*/
     if rnd<10 then
         set sn=concat(sn, char(rnd+48));
     elseif rnd<36 then
         set sn=concat(sn, char(rnd-10+65));
     else
         set sn=concat(sn, char(rnd-36+97));
     end if;
     set cnt=cnt+1;
 end while;
 return sn;
end$$
delimiter ;

select makeRand();

PHP建立function

在php中, 把delimiter移除, 才會正常 //確認中

<?php
 $conn=new mysqli("localhost","root","","mahaljsp");
 $sql="create function makeId() returns varchar(10)
begin
 declare max_id varchar(10);
 declare pre_id varchar(2);
 declare num_id varchar(8);
 select max(id) from mj_employee into max_id;
 set pre_id=left(max_id, 2);
 set num_id=substring(max_id, 3, 10)+1;
 return concat(pre_id, lpad(cast(num_id as char), 4,'0'));
end";
echo $sql."<br>";
 if ($conn->query($sql) === TRUE) {
 echo "Table MyGuests created successfully";
} else {
 echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Stored Routines