MySQL 5.5 创建存储过程和函数

ゝ一世哀愁。 2024-04-17 23:58 62阅读 0赞

**执行CREATE PROCEDURE和CREATE FUNCTION语句** **需要CREATE ROUTINE权限。**  
  
查看neo用户现有权限  
![Image 1][]  
  
授权  
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)  
  
**创建存储过程**  
  
![Image 1][]  
  
注意：在命令行缩进时，不要用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)

**执行存储过程**  
![Image 1][]  
  
授权  
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)  
  
![Image 1][]  
  
![Image 1][]  
  
也可以直接在Navicat里面执行  
![Image 1][]  
  
编辑存储过程  
![Image 1][]  
  
调用存储过程  
![Image 1][]  
  
![Image 1][]  
  
**创建不含参数的存储过程，和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$$

**删除存储过程**  
![Image 1][]  
  
需要授予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)  
  
![Image 1][]  
  
**在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  
![Image 1][]  
  
**执行函数  
![Image 1][]**  
  
  
![Image 1][]  
  
需要注意的是，函数或存储过程里面的参数声明顺序，顺序有误， 容易引起语法报错  
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2106133/

[Image 1]: