Queries regarding Mysql Proceduers

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