MySQL: sp statement

建立一個SP:

DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(
    IN countryName VARCHAR(255)
)

BEGIN
    SELECT *
     FROM offices
    WHERE country = countryName;
END //
DELIMITER ;


先刪除舊SP:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;


加入鎖, 防止同一個SP被多次執行:

SELECT GET_LOCK('THIS_IS_A_NAMED_LOCK', 0) INTO @got_lock;
IF @got_lock = 1 THEN
  select 'do something here';
  SELECT RELEASE_LOCK('THIS_IS_A_NAMED_LOCK') INTO @discard;
END IF;

MySQL: stored procedure (SP)

DELIMITER $$

DROP PROCEDURE IF EXISTS spFoo $$
CREATE PROCEDURE spFoo ()
BEGIN
SELECT 'x' FROM xxx;
END $$

DELIMITER ;

===========================================
參數可以是IN, OUT, INOUT
===========================================

DELIMITER $$
DROP PROCEDURE IF EXISTS CountOrderByStatus$$CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;

===========================================
獲得參數
===========================================

CALL CountOrderByStatus('Shipped',@total);
SELECT @total;