MySQL procedure | 牛的大腦

MYSQL procedure
可直接在mysql內撰寫類似程式的結構

建立procedure
CREATE PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body
ex:
DELIMITER $$
CREATE PROCEDURE generate_data()
select now();
$$
ps:
執行procedure
CALL generate_data();

刪除procedure
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
ex:
DROP PROCEDURE generate_data

查看procedure
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
ex:
SHOW PROCEDURE STATUS

#################################################################

變數部份

變數宣告
DECLARE < var_name[,…]> < type > [DEFAULT value]
DECLARE指令必需在BEGIN….END區塊內使用, 不能放區塊外, 否則會有錯誤訊息,
變數前加’@’符號用來變識出告的區域變數,而兩個’@’是指系統變數
SET指令, 這是指派變數值的指令,
ex:
DECLARE @total_year int;
DECLARE @unit_name varchar(10);
ex:
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
　DECLARE @x int;
　SET @x = 0;
　REPEAT SET @x = @x + 1;
　UNTIL @x > p1 END REPEAT;
END

設定變數
SET < var_name > = < expr [, var_name = expr] … >
ex:
set var1=’100′,var2=’200′, var3=’300′;

…

接收變數傳回值的三種方法

1
將結果直接輸出
CREATE PROCEDURE mysp_QryData (id int)
BEGIN
　SELECT LastName, FirstName FROM Customer WHERE CustomerID = id;
END;
呼叫該procedure會直接輸出結果
CALL mysp_QryData(1);

2
將結果存進變數
CREATE PROCEDURE mysp_QryData2 (id int,out lname varchar(30))
BEGIN
　SELECT LastName INTO lname FROM Customer WHERE CustomerID = id;
END;
在從變數取出結果
CALL mysp_QryData2(1, @lname);
SELECT @lname;

3
將結果回傳
CREATE FUNCTION func_QryData3 (id int)
RETURNS varchar(30)
BEGIN
　DECLARE lname varchar(30);
　SELECT LastName INTO lname FROM Customer WHERE CustomerID = id;
//等同於SET=lname( SELECT LastName FROM Customer WHERE CustomerID = id);
　RETURN lname;
END;
設定接收結果的方式
SET @lname = func_QryData3(1);
SELECT @lname;

#################################################################

流程控制部份

IF
語法如下
IF < search_condition> THEN
　< statement_list
　　[ELSEIF < search_condition> THEN < statement_list>] …
　　[ELSE < statement_list>] >
END IF;

ex:
CREATE PROCEDURE addcol()
BEGIN
　IF NOT EXISTS(
　　SELECT * FROM information_schema.COLUMNS
　　WHERE COLUMN_NAME='rtsp_port' AND TABLE_NAME='device' AND TABLE_SCHEMA='nevionvr'
　)THEN
　　ALTER TABLE `device` ADD `rtsp_port` INT( 16 ) UNSIGNED NOT NULL AFTER `ftp_port` ;
END IF;
END;

case
語法1如下

case < various >
　when < value1 > then < statement >
　[ when < value2 > then < statement > ]...
　ELSE < statement >
end case;

語法2如下

case
　when < search_condition > then < statement >
　[ when < search_condition > then < statement > ]...
　ELSE < statement >
end case;
ex:
CREATE PROCEDURE p()
BEGIN
　DECLARE v INT DEFAULT 1;
　CASE v
　　WHEN 2 THEN SELECT v;
　　WHEN 3 THEN SELECT 0;
　　ELSE
　　　BEGIN
　　　END;
　END CASE;
END;

WHILE
語法如下
while < search_condition > do
　< statement_list>
end while;

REPEAT
語法如下
[ begin_label ]: REPEAT
　< statement_list >
　UNTIL < search_condition >
END REPEAT [ end_label];
ex:
REPEAT
　SET @x = @x + 1;
　UNTIL @x > p1
END REPEAT;

LOOP
語法如下
< loop_label>: LOOP
　< statement_list>
END LOOP < loop_label>;
ps:
LEAVE < loop_label> 是離開loop

ex:loop and if

DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
　DECLARE a INT Default 0 ;
　simple_loop: LOOP
　　SET a=a+1;
　　INSERT INTO data(`num`) VALUES (a);
　　IF a=10 THEN
　　　LEAVE simple_loop;
　　END IF;
　END LOOP simple_loop;
END $$

ex:新增1-365天的日期

DELIMITER $$
CREATE PROCEDURE gen_date_template()
BEGIN
　DECLARE a INT Default 0 ;
　simple_loop: LOOP
　　INSERT INTO date_template(`date`) VALUES(DATE_ADD('2005-01-01',INTERVAL a DAY));
　　SET a=a+1;
　　IF a=365 THEN
　　　LEAVE simple_loop;
　　END IF;
　END LOOP simple_loop;
END $$

reference:
http://ithelp.ithome.com.tw/question/10032363
http://www.dotblogs.com.tw/walter/archive/2009/07/05/how-to-get-mysql-store-procedure-return-value.aspx.aspx

變數部份

流程控制部份

分享此文：