To find out all the procedures of a database:-
SHOW PROCEDURE STATUS WHERE Db = ‘thirstydb’;
To set a new definer:-
mysql> UPDATE `mysql`.`proc` p SET definer = ‘thirstydev@%’ WHERE definer=’thirstylive@localhost’ AND db=’devthirstydb’;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> UPDATE `mysql`.`proc` p SET definer = ‘thirstydev@%’ WHERE definer=’thirstylive@%’ AND db=’devthirstydb’;
Query OK, 85 rows affected (0.04 sec)
Rows matched: 85 Changed: 85 Warnings: 0
To select all the procdure of a particular db:-
select name FROM mysql.proc WHERE db = ‘devthirstydb’ AND type = ‘PROCEDURE’;
select name FROM mysql.proc WHERE db = ‘devthirstydb’ AND type = ‘FUNCTION’;
To delete all the procdure of a particular db:-
DELETE FROM mysql.proc WHERE db = ‘devthirstydb’ AND type = ‘PROCEDURE’;
DELETE FROM mysql.proc WHERE db = ‘devthirstydb’ AND type = ‘FUNCTION’;
Mysql procedures:-
To find out all the procedures:-
mysql> show procedure status;
+————–+—————————————+———–+—————+———————+———————+—————+———+———————-+———————-+——————–+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+————–+—————————————+———–+—————+———————+———————+—————+———+———————-+———————-+——————–+
| thirstydb | advanced_vendor_filtering | PROCEDURE | thirstylive@% | 2016-08-12 05:13:27 | 2016-08-12 05:13:27 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | advanced_vendor_filtering_my_bookings | PROCEDURE | thirstylive@% | 2016-08-12 05:22:29 | 2016-08-12 05:22:29 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | advanced_vendor_filtering_temp | PROCEDURE | thirstylive@% | 2016-08-08 08:29:11 | 2016-08-08 08:29:11 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | all_food_categories | PROCEDURE | thirstylive@% | 2016-08-08 08:29:20 | 2016-08-08 08:29:20 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | apikeyValidity | PROCEDURE | thirstylive@% | 2016-08-08 08:29:29 | 2016-08-08 08:29:29 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | apikeyValidity_vendor | PROCEDURE | thirstylive@% | 2016-08-08 08:29:41 | 2016-08-08 08:29:41 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | directory | PROCEDURE | thirstylive@% | 2016-08-19 03:00:51 | 2016-08-19 03:00:51 | DEFINER | | utf8
To find out a procedure by a certain definer name and dbname:-
mysq> use mysql
mysql> select name from proc where definer = ‘thirstydev@%’ AND db=’devthirstydb’;
+—————————————+
| name |
+—————————————+
| advanced_vendor_filtering |
| advanced_vendor_filtering_my_bookings |
| advanced_vendor_filtering_temp |
| all_food_categories |
| apikeyValidity |
| apikeyValidity_vendor |
| base_url |
| directory |
| facebook_login
To call a procedure:-
mysql> call search_result(1);
Empty set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
To view a procedure:-
mysql> show create procedure user_login;
+————+———-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+———————-+——————–+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+————+———-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+———————-+——————–+
| user_login | | CREATE DEFINER=`thirstylive`@`%` PROCEDURE `user_login`(email_in VARCHAR(255), password_in VARCHAR(255),
api_key_in VARCHAR(50), base_url_in TEXT)
BEGIN
DECLARE email_verified_declared INT(2);
DECLARE user_id_declared BIGINT;
SELECT email_verified INTO email_verified_declared FROM user_profile WHERE email = email_in AND `password` = password_in;
SELECT user_id INTO user_id_declared FROM user_profile WHERE email = email_in AND `password` = password_in;
IF email_verified_declared = 0 THEN
SELECT ‘unverified_email’ AS col_name;
ELSEIF user_id_declared IS NULL THEN
SELECT ‘invalid_credentials’ AS col_name;
ELSE
UPDATE user_profile SET api_key = api_key_in WHERE user_id = user_id_declared;
CALL user_profile(user_id_declared, base_url_in);
END IF;
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+————+———-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+——————-
Leave a Reply