- 語法
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 ;
- 調用存儲過程測試
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 |
+------+
- 顯示存儲過程
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
网友评论