MySQL Stored Procedure

Stored Procedures

MySQL-stored procedures can be applied easily in any place. There are many places where MySQL stored procedures can be used. Stored procedures are a set of SQL commands that are stored in the database. Stored procedures are application and platform related, and the task of execution becomes easier; less disordered. It also helps in decreasing the traffic in the networks and reduces the CPU workload. RDBMS supports recursive stored procedures, but MySQL does not support it well.

There are many places MySQL stored procedures can be used.

• Where client applications are using various languages on different platforms.
• If security is of the highest importance, like in financial institutions, then users and applications would have no direct access to the database tables.
• This provides an excellent secured environment.
• The database servers service the client machines, providing efficient performance.

Example

CREATE PROCEDURE Happy(OUT p1 INT)
SELECT COUNT(*) INTO p1 from persons;

ALTER Procedure

To create the procedure then, we used the CREATE PROCEDURE statement, and altering the procedure, we used this statement. Alter Procedure statement is used to change access permissions that secure by the procedure and ALTER PROCEDURE needs the use of the same encryption and recompile option as the original CREATE PROCEDURE command. MySQL automatically grants the ALTER ROUTINE and EXECUTE opportunity to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable.The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time. ALTER PROCEDURE statement can be used for renaming the stored procedure and for changing it characteristics also. We can specify the more than one changes also in an ALTER PROCEDURE statement. But for this according to required the ALTER ROUTINE privilege.

Syntax

ALTER {PROCEDURE} {proc_name } [characteristic ...] characteristic: SQL SECURITY {DEFINER | INVOKER}| COMMENT 'string'

Example 

ALTER PROCEDURE happy SQL SECURITY DEFINER;

Note. The ALTER PROCEDURE Statement, we can change only the characteristics, and if we want to change in statement list, then we have to DROP the procedure and CREATE again.

CALL Statement Syntax The CALL statement is used to call a procedure, which has been defined previously. CALL can return the values to its caller through its parameters that are declared as OUT or INOUT parameters. This statement is also used to return the number of rows affected that a client program can obtain at the SQL level by calling the ROW_COUNT().

Syntax

CALL p_name([parameter[,...]])

Example

 In the following example, we show the use of the CALL statement.

​​​​​​CREATE PROCEDURE abc(OUT a VARCHAR(20),OUT a1 VARCHAR(20),IN a2 INT)
SELECT FirstName,City INTO a,a1 
FROM persons WHERE P_id=a2;
CALL abc(@NAME,@city,2);
select @name,@city;

DROP Procedure

 DROP PROCEDURE Statement is used to drop a Procedure. But for dropping them, you must have the ALTER ROUTINE privilege. If the NOT EXISTS clause is available, then it prevents you from occurring an error when the procedure does not exist it's produced only a warning

Syntax

 DROP {PROCEDURE } [IF EXISTS] {proc_name };

Examples

 In this example, we show the syntax of the Dropping procedure  if it exists in our database 

 DROP PROCEDURE IF EXISTS abc;