執(zhí)行CREATE PROCEDURE和CREATE FUNCTION語句需要CREATE ROUTINE權(quán)限。

查看neo用戶現(xiàn)有權(quán)限


授權(quán)
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)

創(chuàng)建存儲過程



注意：在命令行縮進(jìn)時，不要用tab，要使用空格，否則會報下面的錯

執(zhí)行存儲過程


授權(quá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里面執(zhí)行


編輯存儲過程


調(diào)用存儲過程




創(chuàng)建不含參數(shù)的存儲過程，和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權(quán)限

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客戶端中創(chuàng)建調(diào)用存儲過程

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)

在調(diào)用的時候，如果參數(shù)不帶@，會報下面的錯
mysql> call proc_test(a,b);

查看存儲過程的狀態(tài)

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)
創(chuàng)建函數(shù)

MySQL的傳入?yún)?shù)不能設(shè)置默認(rèn)值，否則會報錯
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里，創(chuàng)建函數(shù)中在函數(shù)聲明后面的返回關(guān)鍵字是RETURNS


執(zhí)行函數(shù)





需要注意的是，函數(shù)或存儲過程里面的參數(shù)聲明順序，順序有誤，容易引起語法報錯
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

創(chuàng)建兩個函數(shù)，返回兩種變量，一種是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 ;

網(wǎng)站欄目：MySQL5.5創(chuàng)建存儲過程和函數(shù)
URL分享：http://jinyejixie.com/article44/gdphhe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、App開發(fā)、網(wǎng)站營銷、Google、網(wǎng)站策劃、動態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)