STORED PROCEDURE
STORED PROCEDURE
• It is a subroutine like a subprogram in a regular computing language, stored in
database.
• Is has a name, a parameter list, and SQL statement(s).
• Stored procedures is invoked using the CALL statement.
WHY STORED PROCEDURES?
• Stored procedures are fast.
• MySQL server do cache the data. Repetitive task that requires checking, looping,
multiple statements, do it with a single call to a procedure that's stored on the server.
• Stored procedures are portable.
• It runs on every platform that MySQL runs on, without obliging you to install an
additional runtime-environment package.
• Stored procedures are always available as 'source code' in the database itself.
And it makes sense to link the data with the processes that operate on the data.
CREATE PROCEDURE
• By default, a procedure is associated with the default database (currently used
database).
• To associate the procedure with a given database, specify the name as
database_name.stored_procedure_name when you create it.
• Before creating stored procedure
• Check database version: select version();
• Check the privileges assigned: show privileges;
• CREATE PROCEDURE, CREATE FUNCTION require the CREATE ROUTINE privilege.
• Pick a delimiter
DELIMITER
• The delimiter is the character or string of characters which is used to complete an
SQL statement.
• By default we use semicolon (;) as a delimiter.
• This causes problem in stored procedure because a procedure can have many
statements, and everyone must end with a semicolon.
• Pick a string which is rarely occur within statement or within procedure.
• You can use whatever you want.
DELIMITER CONT…
• Here, double dollar sign i.e. $$ is
used as a delimiter.
• DELIMITER $$
• Now, the default delimiter is $$
• Select * from table_name $$
• Now execute the following command
to resume ";" as a delimiter :
• DELIMITER ;
CREATE PROCEDURE
• CREATE PROCEDURE command
creates the stored procedure.
• Next part is the procedure name
(here, np).
• Parentheses, "()" holds the
parameter(s) list as there are no
parameters in this procedure
• Here, $$ is a real statement ender.
CREATE PROCEDURE THROUGH WORKBENCH TOOL
Step 1:
Click on Apply button
Step 2:
Write Your SQL Query within
BEGIN and END statement
SQL Query
Stored Procedure (np1) is
not created yet
Click on
Now, np1 is created
After clicking on Finish button. You can
do changes here and click on apply
changes
This window
will open
Stored procedure is called
Click
SHOW PROCEDURE
Declare Statement
• It is used to define various items local to a program
• E.g., local variables, conditions, handler and cursors.
• It is used only inside a BEGIN … END statement.
• Declarations follow the following order :
• Cursor declarations must appear before handler declarations.
• Variable and condition declarations must appear before cursor or handler declarations.
Variables in Stored Programs
• System variables and user-defined variables can be used in stored programs
(SP).
• SP uses DECLARE to define local variables
• Declare a Variable:
• DECLARE var_name [, var_name] ... type [DEFAULT value]
• To provide a default value for a variable, include a DEFAULT clause.
• If the DEFAULT clause is missing, the initial value is NULL.
LOCAL VARIABLES AND GLOBAL VARIABLES
USER
VARIABLES
PROCEDURE PARAMETERS
• CREATE PROCEDURE statement in the following ways :
• CREATE PROCEDURE sp_name () → the parameter list is empty.
• CREATE PROCEDURE sp_name ([IN] param_name type) → IN parameter passes a value into a
procedure but the modification is not visible to the caller when the procedure returns.
• CREATE PROCEDURE sp_name ([OUT] param_name type) → OUT parameter passes a value from
the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to
the caller when the procedure returns.
• CREATE PROCEDURE sp_name ([INOUT] param_name type) → INOUT parameter is initialized by
the caller, can be modified by the procedure, and any change made by the procedure is visible to the
caller when the procedure returns.
• In a procedure, each parameter is an IN parameter by default.
PARAMETER ‘IN’ EXAMPLE
• IN parameter name → 'var1’
• Type →
• The SELECT statement fetches rows from
‘employees’ table and the number of rows is
limited by the user.
PARAMETER ‘OUT’ EXAMPLE
User Variable
PARAMETER
‘INOUT’
EXAMPLE
FLOW CONTROL
STATEMENTS: IF
STATEMENT
FLOW CONTROL
STATEMENTS: CASE
STATEMENT
FLOW CONTROL
STATEMENTS:LOOP
STATEMENT
FLOW CONTROL
STATEMENT:
WHILE
STATEMENT
DROP PROCEDURE
• You can drop a procedure by writing this command:
• Drop procedure procedure_name;
• You can check the existence of a procedure
• SHOW CREATE PROCEDURE procedure_name