Jump to content

SQL Dialects Reference/Procedural language/Stored procedures

Add links
From Wikibooks, open books for an open world

Stored procedures

[edit | edit source]
Database Create syntax Calling
DB2 
CREATE PROCEDURE procedure_name(...)
   BEGIN
   /* SQL code */
   END

CALL procedure_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(...)
MonetDB 
CREATE [ OR REPLACE ] PROCEDURE procedure_name(...)
BEGIN
   /* SQL code */
END


CREATE [ OR REPLACE ] PROCEDURE procedure_name(...)
EXTERNAL NAME  MAL_procedure_name

CALL procedure_name(...)
MySQL 
DELIMITER $$

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

DELIMITER ;

CALL 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(...) = ...
OpenLink Virtuoso
Oracle 
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    [EXCEPTION
        exception_section]
END [procedure_name];

CALL [PACKAGE_NAME.]procedure_name(...);

BEGIN
  [PACKAGE_NAME.]procedure_name(...);
END;
  • EXEC[ute] is another (client) option, which is implemented by some Oracle clients: [1]
  • CALL syntax can't pass BOOLEAN and other PL/SQL-only types: [1]
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(...)
SQL Server 
CREATE PROCEDURE nameprocedure
  (input_parameter_name datatype, ... )
AS
  /* SQL code */
GO

EXEC nameprocedure(...)
SQLite

N/A

N/A

  1. ↑ a b Oracle SQL Stored Procedures Call vs. Execute discussion on CALL vs EXEC[ute] command in SQL*Plus and other clients.
Retrieved from "https://en.wikibooks.org/w/index.php?title=SQL_Dialects_Reference/Procedural_language/Stored_procedures&oldid=3708787"