美文网首页
MYSQL 8 基本操作之06 (存儲過程)

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

作者: 轻飘飘D | 来源:发表于2019-08-17 19:53 被阅读0次
    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 ;
    
    1. 調用存儲過程測試
    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 |
    +------+
    
    1. 顯示存儲過程
    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
    
    

    相关文章

      网友评论

          本文标题:MYSQL 8 基本操作之06 (存儲過程)

          本文链接:https://www.haomeiwen.com/subject/nrwskctx.html