CREATE PROCEDURE
Syntax:
CREATE [DEFINER = { utente | CURRENT_USER }] PROCEDURE nome_sp ([parametro_sp[, ...]]) [caratteristica ...] corpo_routine CREATE [DEFINER = { utente | CURRENT_USER }] FUNCTION nome_sp ([parametro_funz[, ...]]) RETURNS tipo [caratteristica ...] corpo_routine parametro_sp: [ IN | OUT | INOUT ] nome_param tipo parametro_funz: nome_param tipo tipo: Qualsiasi tipo valido di MariaDB characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'stringa' corpo_routine: Istruzione SQL valida
Spiegazione
Queste istruzioni creano le Stored Routine. Per default, una routine viene associata al database corrente. Per associarla esplicitamente ad un altro database, nel crearla si può specificare un nome qualificato con la sintassi nome_db.nome_routine.
L'istruzione CREATE FUNCTION
può essere usata anche per creare le UDF
(User Defined Function, funzioni definite dall'utente). Si veda la pagina http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html. Una UDF può essere considerata come una Stored Function esterna. Si noti che le Stored Function condividono lo stesso namespace delle UDF. Si veda http://dev.mysql.com/doc/refman/5.1/en/function-resolution.html per sapere in che modo il server interpreta i riferimenti a diversi tipi di funzioni.
When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. USE statements within stored routines are disallowed.
When a stored function has been created, you invoke it by referring to
it in an expression. The function returns a value during expression
evaluation. When a stored procedure has been created, you invoke it by
using the CALL
statement (see CALL).
To execute the CREATE PROCEDURE
or CREATE FUNCTION
statement, it is
necessary to have the CREATE ROUTINE
privilege. By default, MySQL
automatically grants the ALTER ROUTINE
and EXECUTE
privileges to the
routine creator. See also
http://dev.mysql.com/doc/refman/5.1/en/stored-routines-privileges.html. If
binary logging is enabled, the CREATE FUNCTION
statement might also require
the SUPER privilege, as described in
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html.
The DEFINER
and SQL SECURITY clauses specify the security context to
be used when checking access privileges at routine execution time, as
described later.
If the routine name is the same as the name of a built-in SQL function, you must use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs. This is also true when you invoke the routine later. For this reason, we suggest that it is better to avoid re-using the names of existing SQL functions for your own stored routines.
The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. It is always allowable to have spaces after a routine name, regardless of whether IGNORE_SPACE is enabled.
The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.
Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.
Each parameter is an IN
parameter by default. To specify otherwise for
a parameter, use the keyword OUT
or INOUT
before the parameter name.
Note: Specifying a parameter as IN
, OUT
, or INOUT
is valid only for
a PROCEDURE
. (FUNCTION
parameters are always regarded as IN
parameters.)
An IN
parameter passes a value into a procedure. The procedure might
modify the value, but the modification is not visible to the caller
when the procedure returns. An OUT
parameter passes a value from the
procedure back to the caller. Its initial value is NULL within the
procedure, and its value is visible to the caller when the procedure
returns. An INOUT
parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is
visible to the caller when the procedure returns.
For each OUT
or INOUT
parameter, pass a user-defined variable in the
CALL
statement that invokes the procedure so that you can obtain its
value when the procedure returns. If you are calling the procedure
from within another stored procedure or function, you can also pass a
routine parameter or local routine variable as an IN
or INOUT
parameter.
The RETURNS
clause may be specified only for a FUNCTION
, for which it
is mandatory. It indicates the return type of the function, and the function
body must contain a RETURN
value statement. If the RETURN
statement
returns a value of a different type, the value is coerced to the proper type.
For example, if a function specifies an ENUM
or SET
value in the
RETURNS
clause, but the RETURN
statement returns an integer, the value
returned from the function is the string for the corresponding ENUM
member
of set of SET
members.
The routine_body consists of a valid SQL procedure statement. This can
be a simple statement such as SELECT
or INSERT
, or it can be a
compound statement written using BEGIN
and END
. Compound statements
can contain declarations, loops, and other control structure
statements. The syntax for these statements is described in
http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-compound-statements.html
MySQL allows routines to contain DDL statements, such as CREATE
and
DROP. MySQL also allows stored procedures (but not stored functions)
to contain SQL transaction statements such as COMMIT
. Stored functions
may not contain statements that perform explicit or implicit commit or
rollback. Support for these statements is not required by the SQL
standard, which states that each DBMS vendor may decide whether to
allow them.
Statements that return a result set cannot be used within a stored
function. This includes SELECT
statements that do not have an INTO
var_list clause and other statements such as SHOW, EXPLAIN, and CHECK
TABLE. For statements that can be determined at function definition
time to return a result set, a Not allowed to return a result set from
a function error occurs (ER_SP_NO_RETSET). For statements that can be
determined only at runtime to return a result set, a PROCEDURE
%s
can't return a result set in the given context error occurs
(ER_SP_BADSELECT).
For additional information about statements that are not allowed in stored routines, see http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html .
The following example shows a simple stored procedure that uses an OUT
parameter:
MariaDB [test]> delimiter // MariaDB [test]> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END; -> // Query OK, 0 rows affected (0.03 sec) MariaDB [test]> delimiter ; MariaDB [test]> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT @a; +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.00 sec) MariaDB [test]>
The example uses the mysql client delimiter command to change the statement
delimiter from ;
to //
while the procedure is being defined. This
allows the ;
delimiter used in the procedure body to be passed through to
the server rather than being interpreted by mysql itself. See
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html
For information about invoking stored procedures from within programs written in a language that has a MySQL interface, see CALL.
The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:
MariaDB [test]> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT hello('world'); +----------------+ | hello('world') | +----------------+ | Hello, world! | +----------------+ 1 row in set (0.00 sec) MariaDB [test]>
MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.