411 REFERENCE · Wikibooks
SQL Dialects Reference/Procedural language/Stored procedures
Reference material by Wikibooks contributors. This retains its source identity and is not 411 news reporting.
Stored procedures
Database Create syntax Calling DB2CREATE PROCEDURE procedure_name(...)
BEGIN
/* SQL code */
ENDCALL procedure_name(...) FirebirdSET 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(...) MonetDBCREATE [ OR REPLACE ] PROCEDURE procedure_name(...)
BEGIN
/* SQL code */
END
CREATE [ OR REPLACE ] PROCEDURE procedure_name(...)
EXTERNAL NAME MAL_procedure_nameCALL procedure_name(...) MySQLDELIMITER $$
CREATE PROCEDURE nameprocedure
(input_parameter_name datatype, ... )
BEGIN
/* SQL code */
END$$
DELIMITER ;CALL nameprocedure(...) LinterCREATE [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 */
ENDCALL 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: Oracle SQL Stored Procedures Call vs. Execute discussion on CALL vs EXEC[ute] command in SQL*Plus and other clients.
CALL syntax can't pass BOOLEAN and other PL/SQL-only types: PostgreSQLCREATE 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 ServerCREATE PROCEDURE nameprocedure
(input_parameter_name datatype, ... )
AS
/* SQL code */
GOEXEC nameprocedure(...) SQLiteN/AN/A