执行CREATE PROCEDURE和CREATE FUNCTION语句 需要CREATE ROUTINE权限。 查看neo用户现有权限 授权 mysql> grant create routine on fire.* to neo; Query OK, 0 rows affected (0.12 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) 创建存储过程 注意：在命令行缩进时，不要用tab，要使用空格，否则会报下面的错 执行存储过程 授权 mysql> grant execute on fire.* to neo; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 也可以直接在Navicat里面执行 编辑存储过程 调用存储过程

创建不含参数的存储过程，和Oracle不同的是，存储过程名字后面必须要有() mysql> delimiter $$ mysql> create procedure proc_Subscribers_update()     -> begin     -> DECLARE v_count INT;     -> select ifnull(max(a),0) into v_count from t2;     -> while v_count < 2 do     -> select concat('the maximum value is ',v_count);     -> set v_count = v_count+1;     -> end while;     -> end$$ Query OK, 0 rows affected (0.06 sec) delimiter $$ 删除存储过程 需要授予alter routine权限 mysql> grant alter routine on fire.* to neo; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 在mysql客户端中创建调用存储过程 MariaDB [test]> delimiter // MariaDB [test]> create procedure simpleproc(out param1 int)     -> begin     -> select count(*) into param1 from t;     -> end// Query OK, 0 rows affected (0.12 sec) MariaDB [test]> delimiter ; MariaDB [test]> CALL simpleproc(@a); Query OK, 1 row affected (0.08 sec) MariaDB [test]> select @a; +------+ | @a   | +------+ |    1 | +------+ 1 row in set (0.00 sec) 在调用的时候，如果参数不带@，会报下面的 错 mysql> call proc_test(a,b);

查看存储过程的状态

MariaDB [test]> show procedure status like 'simpleproc'\G *************************** 1. row ***************************                   Db: test                 Name: simpleproc                 Type: PROCEDURE              Definer: root@localhost             Modified: 2016-07-01 08:16:20              Created: 2016-07-01 08:16:20        Security_type: DEFINER              Comment:  character_set_client: utf8 collation_connection: utf8_general_ci   Database Collation: utf8_general_ci 1 row in set (0.00 sec) 查看存储过程的定义 MariaDB [test]> show create procedure simpleproc\G *************************** 1. row ***************************            Procedure: simpleproc             sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION     Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(out param1 int) begin select count(*) into param1 from t; end character_set_client: utf8 collation_connection: utf8_general_ci   Database Collation: utf8_general_ci 1 row in set (0.00 sec) 创建函数

MySQL的传入参数不能设置默认值，否则会报错 mysql> delimiter $$ mysql> CREATE FUNCTION format_selectQuery (THE_TABLE_NAME VARCHAR(75), THE_COLUMNS_NAME VARCHAR(75), THE_CONDITION VARCHAR(75) DEFAULT NULL) RETURNS VARCHAR(200) DETERMINISTIC     -> BEGIN     ->     /*SELECT concat(' WHERE ', THE_CONDITION) INTO @WHERE_CLAUSE;    /*>     IF THE_CONDITION IS NULL THEN    /*> SET @WHERE_CLAUSE = NULL;    /*> END IF;    /*>     RETURN concat('SELECT ', THE_COLUMNS_NAME, ' FROM ', THE_TABLE_NAME, @WHERE_CLAUSE);*/     ->     RETURN 1;     -> END $$ ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT '') RETURNS VARCHAR(200) DETERMINISTIC BEGIN          RETURN 1; END' at line 1 mysql> delimiter ; 需要注意的是，在MySQL里，创建函数中在函数声明后面的返回关键字是RETURNS 执行函数

需要注意的是，函数或存储过程里面的参数声明顺序，顺序有误， 容易引起语法报错 delimiter $$ CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(35)) RETURNS INT DETERMINISTIC     BEGIN /* 声明变量 */     DECLARE nbr INT; /* 声明异常 */     DECLARE CONTINUE HANDLER FOR NOT FOUND set nbr = 1; /* 给变量赋值 */     SET nbr =0;                select count(*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and         (column_name ='C_CHANGE_DESCRIPTION');         IF nbr = 1 THEN             RETURN 1;         ELSE                     RETURN 0;         END IF;     END$$ delimiter ; 将上面的顺序放置错误，则会引起报错 mysql> CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(35)) RETURNS INT DETERMINISTIC     ->     BEGIN     ->     DECLARE nbr INT;     ->     SET nbr =0;     ->     DECLARE CONTINUE HANDLER FOR NOT FOUND set nbr = 10;     ->                ->         select count(*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and     ->         (column_name ='C_CHANGE_DESCRIPTION');     ->         IF nbr = 1 THEN     ->             RETURN 1;     ->         ELSE ELSE    ELSEIF       ->         ELSE     ->             RETURN 0;     ->         END IF;     ->     END$$ ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND set nbr = 10;                    select c' at line 5

创建两个函数，返回两种变量，一种是DECLARE变量，一种是@变量

delimiter $$

    CREATE FUNCTION is_TableMigrated (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(20)) RETURNS INTEGER DETERMINISTIC

    BEGIN

    DECLARE nbr integer;

    SET nbr =222;

RETURN nbr;

END$$

delimiter ;

delimiter $$

    CREATE FUNCTION is_TableMigrated (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(20)) RETURNS INTEGER DETERMINISTIC

    BEGIN

    DECLARE nbr integer;

    SET @nbr2 =22222;

RETURN @nbr2;

END$$

delimiter ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2106133/，如需转载，请注明出处，否则将追究法律责任。