mysql 5.5 存储过程_MySQL 5.5 创建存储过程和函数

MySQL 5.5 创建存储过程和函数

发布时间：2020-08-09 08:51:34

来源：ITPUB博客

阅读：58

作者：feelpurple

执行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，要使用空格，否则会报下面的错

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> 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 $$

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$$

删除存储过程

需要授予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);

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

执行函数

需要注意的是，函数或存储过程里面的参数声明顺序，顺序有误，容易引起语法报错

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 ;