SQL Stored Procedure Reference

Procedures

A stored procedure is a set of SQL statements that is stored in association with a database. It is an object that is created with the CREATE PROCEDURE statement and invoked with the CALL statement. A procedure can have zero or many input parameters and zero or many output parameters.

Syntax:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

DELIMITER

To define a stored procedure it is necessary to temporarily modify the separator character used to delimit SQL statements.

The default separator character used in SQL is the semicolon (;). In the examples we are going to perform we are going to use the characters $$ to delimit SQL statements, but it is possible to use any other character.

Example :

In this example we are setting the $$ characters as the separators between SQL statements.

DELIMITER $$

Tagged in : Php

SQL Stored Procedure Reference.

SQL Stored Procedure Reference

Procedures

Syntax:

DELIMITER

Dario L. Mindoro