MySQL 5.5 建立儲存過程和函式
執行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);
*************************** 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 ;
檢視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)
建立包含傳入引數的儲存過程
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 ;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2106133/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 儲存過程和函式MySql儲存過程函式
- MySQL儲存過程和函式MySql儲存過程函式
- Mysql 的儲存過程和儲存函式MySql儲存過程儲存函式
- mysql和orcale的儲存過程和儲存函式MySql儲存過程儲存函式
- mysql儲存函過程和儲存函式都屬於儲存程式MySql儲存函式
- MySQL4：儲存過程和函式MySql儲存過程函式
- MySQL儲存過程 (即函式)MySql儲存過程函式
- MySQL入門--儲存過程(PROCEDURE)和儲存函式(FUNCTION)MySql儲存過程儲存函式Function
- mysql儲存過程基本函式（轉）MySql儲存過程函式
- MySQL儲存過程的建立和使用MySql儲存過程
- 儲存過程 函式儲存過程函式
- 儲存過程與儲存函式儲存過程儲存函式
- MySQL自定義函式與儲存過程MySql函式儲存過程
- (9)mysql 中的儲存過程和自定義函式MySql儲存過程函式
- php呼叫mysql儲存過程和函式的方法(轉)PHPMySql儲存過程函式
- 深入mysql建立自定義函式與儲存過程的詳解MySql函式儲存過程
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- 【MySQL】MySQL（三）儲存過程和函式、觸發器、事務MySql儲存過程函式觸發器
- 儲存過程與函式儲存過程函式
- mysql儲存過程及日期函式實踐MySql儲存過程函式
- mysql儲存過程procedure、函式function的用法MySql儲存過程函式Function
- 儲存過程和函式的區別儲存過程函式
- MySQL 建立儲存過程報錯MySql儲存過程
- MySQL優化---儲存過程和儲存函式-1-轉自部落格園MySql優化儲存過程儲存函式
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- 我的MYSQL學習心得（10） ： 自定義儲存過程和函式MySql儲存過程函式
- mysql 儲存過程和事件排程MySql儲存過程事件
- 在Oracle中查詢儲存過程和函式Oracle儲存過程函式
- SQL中儲存過程和函式的區別SQL儲存過程函式
- day25-索引和函式及儲存過程索引函式儲存過程
- 儲存過程vs.函式QM儲存過程函式
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- Mysql 儲存過程MySql儲存過程
- Oracle建立儲存過程Oracle儲存過程
- 七、函式-儲存過程-觸發器函式儲存過程觸發器
- PLSQL學習-【7儲存過程、函式】SQL儲存過程函式
- openGauss 函式及儲存過程支援函式儲存過程
- 匯出系統表中的儲存過程和函式儲存過程函式