MySQL5.5创建存储过程和函数

执行CREATE PROCEDURE和CREATE FUNCTION语句需要CREATE ROUTINE权限。

查看neo用户现有权限
MySQL 5.5 创建存储过程和函数

授权
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)

创建存储过程

MySQL 5.5 创建存储过程和函数

注意:在命令行缩进时,不要用tab,要使用空格,否则会报下面的错
DATE INNER MULTILINESTRING SET UNICODE warnings
DATEDIFF INNOBASE MULTILINESTRINGFROMTEXT SHA UNION
DATETIME INNODB MULTILINESTRINGFROMWKB SHA1 UNIQUE
DATE_ADD INOUT MULTIPOINT SHARE UNIQUE_USERS
-> Info;
->
Display all 903 possibilities? (y or n)

执行存储过程
MySQL 5.5 创建存储过程和函数

授权
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)

MySQL 5.5 创建存储过程和函数

MySQL 5.5 创建存储过程和函数

也可以直接在Navicat里面执行
MySQL 5.5 创建存储过程和函数

编辑存储过程
MySQL 5.5 创建存储过程和函数

调用存储过程
MySQL 5.5 创建存储过程和函数

MySQL 5.5 创建存储过程和函数

创建不含参数的存储过程,和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 $$
create procedure proc_Subscribers_update(IN v_fetch_cnt INT, IN v_sleep_secs INT)
begin
DECLARE v_count INT;
DECLARE v_times INT DEFAULT 1;
DECLARE v_max_value INT;
/*compute the times that the loop runs*/
select ceil(count(MSISDN))/v_fetch_cnt into v_count from tmp_Subscribers_01;
/*compute the maximum rows that have been already updated*/
WHILE v_times < v_count DO
select ifnull(max(id),0) into v_max_value from tmp_Subscribers_02;
if v_max_value < v_fetch_cnt * v_count then
SET v_times = 1 + floor(v_max_value/v_fetch_cnt);
update Subscribers s,tmp_Subscribers_01 t set s.LastAccessTimeStamp=1420066800
where s.MSISDN=t.MSISDN and t.id > v_max_value and t.id <= v_fetch_cnt * v_times;
/*record the processing rows*/
insert into tmp_Subscribers_02 select id, MSISDN, now() from tmp_Subscribers_01 where id = v_fetch_cnt * v_times;
select concat('The job',' has already updated ', v_fetch_cnt * v_times, ' rows..') as Info;
select sleep(v_sleep_secs);
end if;
commit;
END WHILE;
select concat('The job',' is ','finished!') as Info;
commit;
end$$

删除存储过程
MySQL 5.5 创建存储过程和函数

需要授予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 5.5 创建存储过程和函数

在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);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.proc_test is not a variable or NEW pseudo-variable in BEFORE trigger

查看存储过程的状态
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)

通过information_schema的视图查看存储过程的相关信息

MariaDB [test]> select * from information_schema.routines where routine_name='simpleproc'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: simpleproc
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: simpleproc
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
select count(*) into param1 from t;
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2016-07-01 08:16:20
            LAST_ALTERED: 2016-07-01 08:16:20
                SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    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
MySQL 5.5 创建存储过程和函数

执行函数
MySQL 5.5 创建存储过程和函数


MySQL 5.5 创建存储过程和函数

需要注意的是,函数或存储过程里面的参数声明顺序,顺序有误,容易引起语法报错
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 ;

分享名称:MySQL5.5创建存储过程和函数
当前网址:http://scyanting.com/article/gdphhe.html