CREATE PROCEDURE

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

mysql> delimiter // mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)    -> BEGIN    ->   SELECT COUNT(*) INTO param1 FROM t;    -> END    -> //Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL simpleproc(@a);Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a;+------+| @a   |+------+| 3    |+------+1 row in set (0.00 sec)

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数 默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT 

注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）

procedure需要使用call来调用

CALL sp_name([parameter[,...]])

CALL语句调用一个先前用CREATE PROCEDURE创建的程序。

CALL语句可以用 声明为OUT或的INOUT参数的参数给它的调用者传回值

CREATE FUNCTION

CREATE FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

    proc_parameter:

    [ IN | OUT | INOUT ] param_name type

    func_parameter:

    param_name type

type:

    Any valid MySQL data type

characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

routine_body:

    Valid SQL procedure statement or statements

mysql> delimiter // mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)    -> RETURN CONCAT('Hello, ',s,'!');    -> //Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> SELECT hello('world');+----------------+| hello('world') |+----------------+| Hello, world!  |+----------------+1 row in set (0.00 sec)

RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句 

 

 

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

mysql> SHOW CREATE FUNCTION test.hello\G*************************** 1. row ***************************       Function: hello       sql_mode:Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)RETURN CONCAT('Hello, ',s,'!')

　　

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G*************************** 1. row ***************************           Db: test         Name: hello         Type: FUNCTION      Definer: testuser@localhost     Modified: 2004-08-03 15:29:37      Created: 2004-08-03 15:29:37Security_type: DEFINER      Comment: