STORED PROCEDURE

• 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 ;