MySql stored procedures

By Peter

Stored procedures in MySQL can encapsulate statements which can be used later. I think the real power of using stored procedures is in the amount of time can be saved by using one line against maybe dozens of insert, delete etc.

Benefits of Stored Procedures in MySQL:

  •  Stored Routines can be created with error handlers.
  • Code packaging and encapsulation
  • Separation of the logic
  • Reduction of network bandwidth
  • Security by giving access to a specific routine handling the data.

A procedure can be invoked with a CALL statement to “execute” the encapsulated statements which can modify tables or retrieve data. The procedure can have input, output and both in and output parameters. In other words we can send values to be modified or write to tables within the procedure.

A simple Stored Procedure:

When we want to create a procedure we always need to start end finish with setting the default delimiter. This is necessary because the procedure has executable statements to run so those are finished with ‘;’. If new delimiter is not set for creation time MySQL will try to run when the first ‘;’ is written in followed by a ‘return’. Delimiter can be any character but it’s good practice to use double charasters such as ‘$$’,’//’ etc.
Another must is to specify the statements we want to run, between a ‘begin’ and ‘end’ keyword. This is called the routine body. Procedure can have many levels of ‘begin & end’, in this case we need to give unique names to every level like ‘inner_block: begin … end inner_block’.

Declaring parameters and DECLARE statement

Parameter declaration is useful because variables can be used inside rather than hard-coded values. The type of value need to be specified always, what value to ‘listen’ for eg.: int, varchar etc. As mentioned above we can have 3 types:

  • IN – indicates an input parameter, this is the default if non specified. The value is passed in from the caller.
  • OUT – indicates an output parameter. Any value is ignored from the caller it’ null until the procedure set’s a value, this can be send back to the caller.
  • INOUT – in and output at the same time.

DECLARE statement is used for declare several types of items:

  • Local Variables
  • Conditions (Warning or Exceptions)
  • Handlers for conditions
  • Cursors