What are stored procedures? Explain the use of stored procedures.
A stored procedure is a set of sql statements stored on the server. It has a name, parameters and some sql statements. Statements that need to be frequently executed can be included in a stored procedure.
Use:1. When multiple applications need to perform common database operations.
2. Stored procedures can be used in scenarios when security is of a high priority.
3. Stored routines also allow you to have libraries of functions in the database server.
State an example of MySQL Stored procedure.
Create procedure with IN parameter : Here the variable x is passed as a parameter to insert values.
CREATE OR REPLACE PROCEDURE sample (x VARCHAR) IS
"BEGIN "
"INSERT INTO sample_table VALUES(x); "
"END;";
Define SHOW PROCEDURE STATUS and SHOW CREATE PROCEDURE command.
1. SHOW PROCEDURE STATUS shows the procedure details like database, name, type, creator, creation and modification dates, and character set information.
Example:LIKE clause can be used to find details of specific procedures.
SHOW PROCEDURE STATUS LIKE ‘sample’\G
2. SHOW CREATE PROCEDURE is used re-create the named stored procedure.
Example:SHOW CREATE PROCEDURE sample.sampleproc\G
What are the three types of parameter of MySQL Stored procedure? Explain them
1. Create procedure with IN parameter : Here the variable x is passed as a parameter to insert values. IN indicates that you must supply a value for the argument when calling the procedure.
CREATE OR REPLACE PROCEDURE sample (x VARCHAR) IS
"BEGIN "
"INSERT INTO sample_table VALUES(x); "
"END;";
2. Create procedure with OUT parameter : Here the variable x is called passed OUT of the stored procedure. OUT indicates that the procedure passes a value for this argument back to its calling environment after execution.
CREATE OR REPLACE PROCEDURE sample1(x OUT VARCHAR) IS
"BEGIN "
"INSERT INTO sample1_table VALUES('string 2'); "
"x := 'outvalue'; " // Assign a value to x
"END;";
3. Create procedure with IN OUT parameter : Indicates that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.
Provide an example of a stored procedure with In and Out parameter with its working explanation.
1. Create procedure with IN parameter:Here the variable x is passed as a parameter to insert values.
CREATE OR REPLACE PROCEDURE sample (x VARCHAR) IS
"BEGIN "
"INSERT INTO sample_table VALUES(x); "
"END;";
2. Create procedure with OUT parameter:Here the variable x is called passed OUT of the stored procedure
CREATE OR REPLACE PROCEDURE sample1(x OUT VARCHAR) IS
"BEGIN "
"INSERT INTO sample1_table VALUES('string 2'); "
"x := 'outvalue'; " // Assign a value to x
"END;";
What are stored routines? Stored Routine Syntax
Stored routines can either be functions or procedures stored on the server. Once stored, they can be reused as and when required rather than executing individual statements. They are most commonly used where security is required because they provide a consistent and secure environment.
Syntax:Stored routines being procedures and functions are created using the same syntax. CALL can be used to invoke them.
CREATE OR REPLACE PROCEDURE sample (x VARCHAR) IS
"BEGIN "
"INSERT INTO sample_table VALUES(x); "
"END;";
