mysql 存储过程语法学习笔记

CREATE PROCEDURE and CREATE FUNCTION Syntax
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    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:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

DELIMITER $$ /*改变语句的结束符*/

USE `test`$$ /*选择数据库*/

DROP PROCEDURE IF EXISTS `outgo`$$ /*存在outgo存储过程就删除*/

CREATE DEFINER=`root`@`%` PROCEDURE `outgo`(IN v_table CHAR(10), IN v_id INT(2), OUT v_value VARCHAR(32))

mysql> call outgo('user', 2, @a);
---- --------------
| id | title        |
---- --------------
|  2 | 你大爷的     |
---- --------------
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call outgo('user', 1, @a);
---- --------------------
| id | title              |
---- --------------------
|  1 | 我来测试一记       |
---- --------------------
1 row in set (0.00 sec)