MySQL语法模板 SQL语句:函数、存储过程

1. 创建函数

CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
 routine_body

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

2. 创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...]
 routine_body
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

3. 修改函数

ALTER FUNCTION sp_name [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

4. 修改存储过程

ALTER PROCEDURE sp_name [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

5. 调用

CALL sp_name([parameter[,...]])

6. 删除函数

DROP FUNCTION [IF EXISTS] sp_name

7. 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name