MYSQL 8 基本操作之06 (存儲過程)

1. 語法

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = 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

Characteristic的取值如下 
LANGUAGE SQL 存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言
CONTAINS SQL，表示子程序包含SQL语句，但是，不包含读或写数据的语句 
NO SQL，表示子程序中，不包含SQL语句 
READS SQL DATA，表示子程序中，包含读数据的语句 
MODIFIES DATA，表示子程序中，包含写数据的语句 
SQL SECURITY {DEFINER | INVOKER}，指明谁有权限来执行 
DEFINER，表示只有定义者，自己才能够执行 
INVOKER，表示调用者可以执行 
COMMENT’string’，表示注释信息

1.創建存儲過程

DROP PROCEDURE IF EXISTS  proc_my_acc_rollback3;

DELIMITER $$

CREATE PROCEDURE Proc_Mp_User_Query
(
  in v_mp_user_seq int,
  out v_count int
)
READS SQL DATA
BEGIN
  select mp_user_seq,mp_user_name from mp_user where mp_user_seq<=v_mp_user_seq;
  select FOUND_ROWS() into v_count;
END$$

DELIMITER ;

2. 調用存儲過程測試

root@127.0.0.1 : testdb【11:11:27】150 SQL->call Proc_Mp_User_Query(102,@a);
Empty set (0.12 sec)

root@127.0.0.1 : testdb【11:12:47】152 SQL->select @a;
+------+
| @a   |
+------+
|    0 |
+------+

root@127.0.0.1 : testdb【11:11:29】151 SQL->insert into mp_user(mp_user_name) values('xag'),('yyc');

root@127.0.0.1 : testdb【11:13:02】153 SQL->call Proc_Mp_User_Query(102,@a);
+-------------+--------------+
| mp_user_seq | mp_user_name |
+-------------+--------------+
|         100 | xag          |
|         101 | yyc          |
+-------------+--------------+

root@127.0.0.1 : testdb【11:13:10】154 SQL->select @a;
+------+
| @a   |
+------+
|    2 |
+------+

3. 顯示存儲過程

root@127.0.0.1 : testdb【01:01:19】201 SQL->show create procedure Proc_Mp_User_Query \G;
*************************** 1. row ***************************
           Procedure: Proc_Mp_User_Query
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_Mp_User_Query`(
  in v_mp_user_seq int,
  out v_count int
)
    READS SQL DATA
BEGIN
  select mp_user_seq,mp_user_name from mp_user where mp_user_seq<=v_mp_user_seq;
  select FOUND_ROWS() into v_count;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

#通過 information_schema.routines 查詢
root@127.0.0.1 : testdb【04:21:26】205 SQL->select * from information_schema.routines where routine_name='Proc_Mp_User_Query' \G;
*************************** 1. row ***************************
           SPECIFIC_NAME: Proc_Mp_User_Query
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: testdb
            ROUTINE_NAME: Proc_Mp_User_Query
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  select mp_user_seq,mp_user_name from mp_user where mp_user_seq<=v_mp_user_seq;
  select FOUND_ROWS() into v_count;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: READS SQL DATA
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2019-07-05 23:10:56
            LAST_ALTERED: 2019-07-05 23:10:56
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci