|
|
51CTO旗下网站
|
|
移动端

MySQL数据库之存储过程的创建和调用

存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。

作者:架构师之旅来源:今日头条|2018-11-05 15:14

MySQL数据库之存储过程的创建和调用

MySQL 5.0 版本开始支持存储过程。

存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。

优点

  •  存储过程可封装,并隐藏复杂的商业逻辑。
  •  存储过程可以回传值,并可以接受参数。
  •  存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
  •  存储过程可以用在数据检验,强制实行商业逻辑等。

缺点

  •  存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
  •  存储过程的性能调校与撰写,受限于各种数据库系统。

存储过程的创建和调用

创建存储过程

  1. CREATE  
  2.  [DEFINER = { user | CURRENT_USER }]  
  3.  PROCEDURE sp_name ([proc_parameter[,...]])  
  4.  [characteristic ...] routine_body   
  5. proc_parameter:  
  6.  [ IN | OUT | INOUT ] param_name type   
  7. characteristic:  
  8.  COMMENT 'string'  
  9.  | LANGUAGE SQL  
  10.  | [NOT] DETERMINISTIC  
  11.  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  
  12.  | SQL SECURITY { DEFINER | INVOKER }   
  13. routine_body:  
  14. Valid SQL routine statement   
  15. [begin_label:] BEGIN  
  16. [statement_list]  
  17. ……  
  18. END [end_label] 

MYSQL 存储过程中的关键语法

1.声明语句结束符,可以自定义:

  1. DELIMITER $$  
  2. 或  
  3. DELIMITER // 

2.声明存储过程:

  1. CREATE PROCEDURE demo_in_parameter(IN p_in int)  

3.存储过程开始和结束符号:

  1. BEGIN .... END  

4.变量赋值:

  1. SET @p_in=1  

5.变量定义:

  1. DECLARE l_int int unsigned default 4000000;  

6.创建mysql存储过程、存储函数:

  1. create procedure 存储过程名(参数) 

7.存储过程体:

  1. create function 存储函数名(参数) 

实例

1.创建数据库,备份数据表用于示例操作:

  1. mysql> create database db1;  
  2. mysql> use db1;   
  3. mysql> create table PLAYERS as select * from TENNIS.PLAYERS;  
  4. mysql> create table MATCHES as select * from TENNIS.MATCHES; 

2.下面是存储过程的例子,删除给定球员参加的所有比赛:

  1. mysql> delimiter $$#将语句的结束符号从分号;临时改为两个$$(可以是自定义)  
  2. mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)  
  3.  -> BEGIN  
  4.  -> DELETE FROM MATCHES  
  5.  -> WHERE playerno = p_playerno;  
  6.  -> END$$  
  7. Query OK, 0 rows affected (0.01 sec)  
  8. mysql> delimiter;#将语句的结束符号恢复为分号 

解析:默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀。 在定义过程时,使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。

调用存储过程:

  1. call sp_name[(传参)];  
  1. mysql> select * from MATCHES;  
  2. +---------+--------+----------+-----+------+  
  3. | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |  
  4. +---------+--------+----------+-----+------+  
  5. | 1 | 1 | 6 | 3 | 1 |  
  6. | 7 | 1 | 57 | 3 | 0 |  
  7. | 8 | 1 | 8 | 0 | 3 |  
  8. | 9 | 2 | 27 | 3 | 2 |  
  9. | 11 | 2 | 112 | 2 | 3 |  
  10. +---------+--------+----------+-----+------+  
  11. 5 rows in set (0.00 sec)  
  12. mysql> call delete_matches(57);  
  13. Query OK, 1 row affected (0.03 sec)  
  14. mysql> select * from MATCHES;  
  15. +---------+--------+----------+-----+------+  
  16. | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |  
  17. +---------+--------+----------+-----+------+  
  18. | 1 | 1 | 6 | 3 | 1 |  
  19. | 8 | 1 | 8 | 0 | 3 |  
  20. | 9 | 2 | 27 | 3 | 2 |  
  21. | 11 | 2 | 112 | 2 | 3 |  
  22. +---------+--------+----------+-----+------+  
  23. 4 rows in set (0.00 sec) 

解析:在存储过程中设置了需要传参的变量p_playerno,调用存储过程的时候,通过传参将57赋值给p_playerno,然后进行存储过程里的SQL操作。

存储过程体

  •  存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等
  •  过程体格式:以begin开始,以end结束(可嵌套) 
  1. BEGIN  
  2. BEGIN  
  3. BEGIN  
  4. statements;   
  5. END  
  6. END  
  7. END 

注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的begin-end块(又叫做复合语句compound statement),则不需要分号。

为语句块贴标签:

  1. [begin_label:] BEGIN  
  2. [statement_list]  
  3. END [end_label] 

例如:

  1. label1: BEGIN  
  2. label2: BEGIN  
  3. label3: BEGIN  
  4. statements;   
  5. END label3 ;  
  6. END label2;  
  7. END label1 

标签有两个作用:

  1、增强代码的可读性

  2、在某些语句(例如:leave和iterate语句),需要用到标签

【编辑推荐】

  1. 没遇到过这十个MySQL数据库经典错误,你一定不是个好工程师
  2. 实现故障恢复自动化:详解Redis哨兵技术
  3. MySQL数据库命令窗口中文乱码或插入中文数据失败
  4. 基于Centos7系统-MYSQL主从复制偏运维全实战教程
  5. SQL之经典SQL语句大全「MySQL」
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

视频课程+更多

讲师:Udemy-国际畅销 3069人学习过

基于SSM+Maven+BootStrap+MySQL的在线商城系统[实战视频]

基于SSM+Maven+BootStrap+MySQL的在线商城系

讲师:汤小洋12586人学习过

网络瑞士军刀-netcat的秘诀

网络瑞士军刀-netcat的秘诀

讲师:刘晓阳5351人学习过

读 书 +更多

网管员成长手记——网络组建、配置与应用

本书主要以“网管员的成长经历”为线索展开,虚拟出一个“新手”网管员的工作和学习环境,将网管员的成长分为4个阶段,以“网管入职充电→...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊