创建和调用MySQL存储过程

一个，MySQL存储过程_create-call

1.1存储过程: SQL中的“脚本”

1. 创建一个存储过程

2. 调用存储过程

3. 存储过程正文

4. 语句标签块

存储过程（存储过程）:

提示: #SQL语句: 首先编译并执行

一组可编程函数是一组用于完成特定功能的SQL语句. 它是通过编译创建的，并存储在中. 用户可以通过指定存储过程的名称并提供参数（在需要时）来调用和执行.

优点（为什么要使用存储过程？）:

①一些高度重复的操作被封装到存储过程中，从而简化了对这些SQL的调用.

②批处理: SQL +循环，减少流量，即“运行批处理”

③统一的界面，确保数据安全

与oracle相比，MySQL的存储过程相对较弱且使用较少.

描述: 存储过程是一段带有用于完成特定功能的名称的代码. 创建的存储过程将保存在的数据字典中.

语法介绍:

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
proc_parameter:
    [ IN | OUT | INOUT ] param_name 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
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

示例1演示:

创建测试表:

CREATE TABLE `test1_event` (
`id` int(8) NOT NULL AUTO_INCREMENT, 
`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(20) COLLATE utf8_unicode_ci NOT NULL, 
`create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL, 
PRIMARY KEY (`id`) #主键ID
) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入数据:

mysql>  INSERT INTO test1_event(username,password,create_time) values(‘tomcat‘, ‘xiaohuahua‘,now());
Query OK, 1 row affected (0.00 sec)
mysql>  INSERT INTO test1_event(username,password,create_time) values(‘tomcat‘, ‘xiaohuahua‘,now());
Query OK, 1 row affected (0.00 sec)
mysql>  select * from test1_event;
+----+----------+------------+---------------------+
| id | username | password   | create_time         |
+----+----------+------------+---------------------+
|  1 | tomcat   | xiaohuahua | 2018-09-12 17:18:01 |
|  2 | tomcat   | xiaohuahua | 2018-09-12 17:18:39 |
|  3 | tomcat   | xiaohuahua | 2018-09-12 17:18:45 |
+----+----------+------------+---------------------+
3 rows in set (0.00 sec)

#创建一个新表来备份旧表的数据，例如操作:

mysql> create table test_event as select * from test.test1_event;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql>  select * from test_event;
+----+----------+------------+---------------------+
| id | username | password   | create_time         |
+----+----------+------------+---------------------+
|  1 | tomcat   | xiaohuahua | 2018-09-12 17:18:01 |
|  2 | tomcat   | xiaohuahua | 2018-09-12 17:18:39 |
|  3 | tomcat   | xiaohuahua | 2018-09-12 17:18:45 |
+----+----------+------------+---------------------+
3 rows in set (0.00 sec)

示例2: 创建存储过程（向数据表中添加用户和密码并添加创建时间）

DELIMITER //   ###　将语句的结束符号从分号;临时改为两个//(可以是自定义)
DROP PROCEDURE IF EXISTS p_test1//
CREATE PROCEDURE p_test1() 
BEGIN
INSERT INTO test_event(username,password,create_time) values(‘tomcat‘, ‘xiaohuahua‘,now());
END//
delimiter ; ####将语句的结束符号恢复为分号

查看创建的存储过程的详细信息:

mysql> show procedure status;
+------+---------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name    | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p_test1 | PROCEDURE | [emailprotected] | 2018-09-12 17:44:40 | 2018-09-12 17:44:40 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| test | p_test2 | PROCEDURE | [emailprotected] | 2018-09-07 18:25:54 | 2018-09-07 18:25:54 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+---------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

删除创建的存储过程:

mysql> drop procedure p_test2 ;
Query OK, 0 rows affected (0.00 sec)
mysql> show procedure status;
+------+---------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name    | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p_test1 | PROCEDURE | [emailprotected] | 2018-09-12 17:44:40 | 2018-09-12 17:44:40 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+---------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/tongxinshuyu/article-212926-1.html