We can see only the list of stored procedures in a particular MySQL database by the following query −mysql> SHOW PROCEDURE STATUS WHERE db = 'query'\G *************************** 1. row ***************************                   Db: query                 Name: allrecords                 Type: PROCEDURE              Definer: root@localhost             Modified: 2017-11-11 09:56:11              Created: 2017-11-11 09:56:11        Security_type: DEFINER              Comment: character_set_client: cp850 collation_connection: cp850_general_ci   Database Collation: latin1_swedish_ci 1 row in set (0.01 sec)

Suppose if we are calculating the sum of the values of a column which also have NULL values then MySQL SUM() function ignores the NULL values and does the sum of the rest of the values. To understand it, consider the following example of table ‘employee’, having following details − mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | ... Read More

We can see only the list of stored procedures in a particular MySQL database by the following query −mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'query'AND ROUTINE_TYPE = 'PROCEDURE'// +--------------+------------------------+ | ROUTINE_TYPE | ROUTINE_NAME           | +--------------+------------------------+ | PROCEDURE    | allrecords             | +--------------+------------------------+ 1 row in set (0.05 sec)

We can write the following query to see only the name and types of procedures in a particular MySQL database. To make it understand we are using the database named ‘query’ −mysql> Select Name, Type from mysql.proc where db = 'query'; +------------+-----------+ | Name       | Type      | +------------+-----------+ | allrecords | PROCEDURE | | Hello      | FUNCTION  | +------------+-----------+ 2 rows in set (0.18 sec)

As we know that the SUM() function returns NULL if there is no matching row but sometimes we want it to return zero instead of NULL. For this purpose, we can use the MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id   | name | work_date  | daily_typing_pages | +------+------+------------+--------------------+ | 1    | John | 2007-01-24 |       ... Read More

We can see the list of the stored procedure and stored functions in a particular database by using the following query on INFORMATION_SCHEMA.ROUTINES as follows −mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'query'; +--------------+--------------+ | ROUTINE_TYPE | ROUTINE_NAME | +--------------+--------------+ | PROCEDURE    | allrecords   | | FUNCTION     | Hello        | +--------------+--------------+ 2 rows in set (0.04 sec)The above query returns the procedure named ‘allrecords’, and function named ‘Hello’ which are stored in the database named ‘query’.

By using MySQL SUM() function with the HAVING clause, it filters the result based on a specific condition given after the HAVING clause. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id   | name | work_date  | daily_typing_pages | +------+------+------------+--------------------+ | 1    | John | 2007-01-24 |        250         | | 2    | Ram  | 2007-05-27 |        220         | | 3    | Jack | 2007-05-06 |       ... Read More

When we use MySQL SUM() function with GROUP BY Clause the SUM() function evaluates the sum for every group specified in the GROUP BY clause. The benefit of using SUM() with GROUP BY clause is that we can easily find the total of a particular group. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id   | name | work_date  | daily_typing_pages | +------+------+------------+--------------------+ | 1    | John | 2007-01-24 |        250         | | 2    | Ram ... Read More

There are numerous advantages and disadvantages of using MySQL stored procedures which are as follows −MySQL Stored Procedure AdvantagesFollowings are the advantages of using MySQL Stored Procedures −Increasing the performance of applications − As we know that after creating the stored procedure it is compiled and stored in the database. But MySQL implements stored procedures slightly different which helps in increasing the performance of the applications. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses ... Read More

We must have the following prerequisites before starting writing and using MySQL stored procedures −MySQL VersionAs we know that MySQL 5 introduced stored procedures, hence first of all we need to check for the version of MySQL before staring writing and using stored procedures. It can be done with the following query −mysql> Select VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.20    | +-----------+ 1 row in set (0.10 sec)Privileges for the current userActually, CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. ... Read More