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;