mysql procedure筆記

edited 十月 2013 in 資料庫
之前因意外研究了mysql procedure,所以把重點整理了一下,希望能幫助有需要的人
只是不知道放這邊對不對,因為其實也不會太難

............................................

MYSQL procedure
可直接在mysql內撰寫類似程式的結構,聽說運算速度是和c語言一樣快,但本人是還沒測過


建立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


原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=7082
Sign In or Register to comment.