Save
Databases Final
STORED PROCEDURE
Save
Share
Learn
Content
Leaderboard
Learn
Created by
tiana okane
Visit profile
Cards (9)
Stored
Procedures
A pre-written block of SQL statements that is saved in the database (pre-compiled), that you can
call time
and
time
again
Stored
Procedures
They can be invoked with a
call
statement
They are used to encapsulate
repetitive
or complex
database
operations
Creating
a Stored Procedure
1.
Change
the delimiter
2. CREATE
PROCEDURE
procedure_name (param_spec1, param_spec2, ... )
3.
BEGIN
4.
SQL
statements
5.
END
6.
Change
the delimiter back
IN
parameter
Allows you to
pass
values into the
procedure
OUT
parameter
Allows you to pass value back from procedure to the
calling
program
INOUT
parameter
Allows you to both
pass
and
extract
values
Calling
a Stored Procedure
CALL procedure_name (
param_spec1
,
param_spec2
, ...);
There are
2
ways to view the created Stored Procedures:
ROUTINES & PROCEDURES buttons
Dropping
a Stored Procedure
DROP
procedure_
name
;