MySQL 存储过程和函数（转）

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
    proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
    func_parameter:
    param_name type
 
type:
    Any valid MySQL data type
 
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
 
routine_body:
    Valid SQL procedure statement or statements

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

#插入测试数据
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

#创建存储过程
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee(101,@pcount);

SELECT @pcount;

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

1.将log_bin_trust_function_creators参数设置为ON，这样一来开启了log-bin的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题，除非明确的知道创建的function在master和slave上的行为完全一致。
  设置该参数可以用动态的方式或者指定该参数来启动数据库服务器或者修改配置文件后重启服务器。需注意的是，动态设置的方式会在服务器重启后失效。
  mysql> show variables like 'log_bin_trust_function_creators';
  mysql> set global log_bin_trust_function_creators=1;
  另外如果是在master上创建函数，想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON（变量的设置不会从master复制到slave上，这点需要注意），否则主从复制会报错。

2.明确指明函数的类型
  1 DETERMINISTIC 不确定的
  2 NO SQL 没有SQl语句，当然也不会修改数据
  3 READS SQL DATA 只是读取数据，当然也不会修改数据
比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
这样一来相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。

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

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

show procedure status like 'Pro_Employee' \G

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' \G;