最後更新: 2020-11-13

介紹

stored routine = stored procedure / stored function

By default, a stored routine is associated with the default database.

To associate the routine explicitly with a given database,
specify the name as db_name.sp_name when you create it.

目錄

• Function
• 建立 Stored Function
• Stored Function 應用
   - rbcc
• Function vs Procedure
• Stored routine parameter name is the same as table column name
• Backquote/Backticks
• Procedure

 

---

FUNCTION

 

查看有什麼 Function

SHOW FUNCTION STATUS;

| Db          | Name | Type     | Definer        | Modified            | Created             
+-------------+------+----------+----------------+---------------------+---------------------
| dbispconfig | rbcc | FUNCTION | [email protected] | 2023-10-09 20:46:28 | 2023-10-09 20:46:28 

| Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------------+---------+----------------------+----------------------+--------------------+
| DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |

# 查看它的 Code

SHOW CREATE FUNCTION fname;

i.e.

| Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |

# Drop it

DROP FUNCTION [IF EXISTS] fname;

 

 

---

建立 Stored Function

 

Syntax

DELIMITER $$

CREATE FUNCTION function_name(
    param1,
    param2,…
)
RETURNS datatype
[[NOT] DETERMINISTIC]
BEGIN
 -- statements
END $$

DELIMITER ;

Notes

DETERMINISTIC

A deterministic function always returns the same result for the same input parameters
whereas a non-deterministic function returns different results for the same input parameters.

If you don’t use DETERMINISTIC or NOT DETERMINISTIC, MySQL uses the NOT DETERMINISTIC option by default.

i.e.

DELIMITER $$

CREATE FUNCTION rbcc(
    mail_to VARCHAR(255)
)
RETURNS VARCHAR(255)

BEGIN
    -- return the input
    RETURN (mail_to);
END $$

DELIMITER ;

Run Function

SELECT rbcc("[email protected]");

 

---

Variable

 

Local Variables (no prefix)

Local variables needs to be declared using DECLARE before accessing it.

DECLARE VAR_Email VARCHAR(255);

SET VAR_Email = '[email protected]';

SELECT email INTO VAR_Email from mail_exclude_bcc where active = 1;

User-defined variables (prefixed with @)

If you refer to a variable that has not been initialized,
 it has a value of NULL and a type of string.

i.e.

SET @email = 0;
SELECT @email;

0

SELECT COUNT(email) INTO @email from mail_exclude_bcc where active = 1;
SELECT @email;

3

SELECT email INTO @email from mail_exclude_bcc where active = 1;

ERROR 1172 (42000): Result consisted of more than one row

 

---

If

 

IF condition1 THEN
   {statements to execute when condition1 is TRUE}
ELSEIF condition2 THEN
   {statements to execute when condition1 is FALSE and condition2 is TRUE}
ELSE
   {statements to execute when both condition1 and condition2 are FALSE}
END IF;

i.e

IF credit < 1000 THEN
    SET customerLevel = 'SILVER';
ELSEIF (credit >= 1000 AND credit <= 9999) THEN
    SET customerLevel = 'GOLD';
ELSEIF credit > 10000 THEN
    SET customerLevel = 'PLATINUM';
END IF;

 

---

Stored Function 應用

 

1) Except 某些 mailbox 不用被 BCC

Function: rbcc(varchar)

CREATE FUNCTION `rbcc`(`mail_to` VARCHAR(255))
 RETURNS varchar(255) CHARSET utf8
BEGIN

SET @bcc_to = "[email protected]";

SELECT COUNT(email) INTO @count
 FROM mail_exclude_bcc AS m
 WHERE m.email = mail_to AND m.active = 1;

IF @count = 1 THEN
   RETURN(NULL);
ELSE
   RETURN(@bcc_to);
END IF;

END

Table: mail_exclude_bcc

CREATE TABLE `mail_exclude_bcc` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM

/etc/postfix/main.cf

#always_bcc = [email protected]
recipient_bcc_maps = proxy:mysql:/etc/postfix/mysql-recipient_bcc_maps_user.cf
sender_bcc_maps = proxy:mysql:/etc/postfix/mysql-sender_bcc_maps_user.cf

proxy_read_maps = ... $recipient_bcc_maps $sender_bcc_maps

 

---

Function vs Procedure

 

Function

It returns a single value as the result.

Procedure

It does not necessarily return a value, but it can return multiple result sets.

 

---

Stored routine parameter name is the same as table column name

 

Fix) Add table name in your column name.

 ... where mail_recipient_bcc.mail_to = mail_to;

 

---

Using backquote/backticks for mysql queries

 

"``" are called quoted identifiers

they tell the parser to handle the text between them as a literal string.

功能:

1) Allow spaces and other special characters (except for backticks, obviously) in table/column names

CREATE TABLE `my table` (id INT);

2) If you have a column with name that is reserved

select * from tablename group by `group`;

 

---

PROCEDURE

 

查看有什麼 procedure:

show procedure status;

stored procedures 是跟 DB 的 !!

show procedure status where Name = 'name';

建立 procedure 要的 permission

#1227 - Access denied; you need the SUPER privilege for this operation

查看 stored procedure 的 Code

SHOW CREATE PROCEDURE Statement \G

Drop it

DROP PROCEDURE [IF EXISTS] procedure_name

 

---