SQL Dialects Reference/Procedural language/Stored procedures

Stored proceduresEdit

Database Create syntax Calling
DB2
CREATE PROCEDURE procedure_name(...)
   BEGIN
   /* SQL code */
   END
CALL procedure_name(...)
SQLite

N/A

N/A

MonetDB
CREATE PROCEDURE procedure_name(...)
   BEGIN
   /* SQL code */
   END
CALL procedure_name(...)
MySQL
DELIMITER $$

CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... )
BEGIN
  /* SQL code */
END$$

DELIMITER ;
CALL nameprocedure(...)
PostgreSQL
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
AS $$
DECLARE
  variable_name datatype;
BEGIN
  /* SQL code */
END;
$$ LANGUAGE plpgsql;
SELECT function_name(...)
Firebird
SET TERM $$ ;

CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... ) 
RETURNS 
  (output_parameter_name datatype, ... )
AS 
DECLARE VARIABLE variable_name datatype;
BEGIN
  /* SQL code */
END$$

SET TERM ; $$
SELECT ... FROM function_name(...)
EXECUTE function_name(...)
OpenLink Virtuoso
Oracle
SQL Server
CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... )
AS
  /* SQL code */
GO
EXEC nameprocedure(...)
Linter
CREATE [OR REPLACE] PROCEDURE procedure_name([IN/OUT/INOUT] parameter_name datatype, ...) [RESULT datatype] [FOR DEBUG]
   DECLARE 
   /* variables declaration */
   CODE
   /* stored procedure code 
      (including SQL code)*/
   EXCEPTIONS
   /* exceptions declarations */
   END
CALL procedure_name(...)

EXECUTE procedure_name(...)

EXECUTE procedure_name(...) AS OWNER

SELECT procedure_name(...)
FROM ...
WHERE
procedure_name(...) = ...
Last modified on 24 May 2013, at 09:04