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

发表于:2018-11-06 10:09

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:架构师之旅    来源:今日头条

分享:
  MySQL 5.0 版本开始支持存储过程。
  存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
  存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
  存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
  优点
  存储过程可封装,并隐藏复杂的商业逻辑。
  存储过程可以回传值,并可以接受参数。
  存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
  存储过程可以用在数据检验,强制实行商业逻辑等。
  缺点
  存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
  存储过程的性能调校与撰写,受限于各种数据库系统。
  存储过程的创建和调用
  创建存储过程
  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]
  MYSQL 存储过程中的关键语法
  1.声明语句结束符,可以自定义:
  DELIMITER $$
  或
  DELIMITER //
  2.声明存储过程:
  CREATE PROCEDURE demo_in_parameter(IN p_in int)
  3.存储过程开始和结束符号:
  BEGIN .... END
  4.变量赋值:
  SET @p_in=1
  5.变量定义:
  DECLARE l_int int unsigned default 4000000;
  6.创建mysql存储过程、存储函数:
  create procedure 存储过程名(参数)
  7.存储过程体:
  create function 存储函数名(参数)
  实例
  1.创建数据库,备份数据表用于示例操作:
  mysql> create database db1;
  mysql> use db1;
  mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
  mysql> create table MATCHES as select * from TENNIS.MATCHES;
  2.下面是存储过程的例子,删除给定球员参加的所有比赛:
  mysql> delimiter $$#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
  mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
  -> BEGIN
  -> DELETE FROM MATCHES
  -> WHERE playerno = p_playerno;
  -> END$$
  Query OK, 0 rows affected (0.01 sec)
  mysql> delimiter;#将语句的结束符号恢复为分号
  解析:默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀。 在定义过程时,使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。
  调用存储过程:
  call sp_name[(传参)];
  mysql> select * from MATCHES;
  +---------+--------+----------+-----+------+
  | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
  +---------+--------+----------+-----+------+
  | 1 | 1 | 6 | 3 | 1 |
  | 7 | 1 | 57 | 3 | 0 |
  | 8 | 1 | 8 | 0 | 3 |
  | 9 | 2 | 27 | 3 | 2 |
  | 11 | 2 | 112 | 2 | 3 |
  +---------+--------+----------+-----+------+
  5 rows in set (0.00 sec)
  mysql> call delete_matches(57);
  Query OK, 1 row affected (0.03 sec)
  mysql> select * from MATCHES;
  +---------+--------+----------+-----+------+
  | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
  +---------+--------+----------+-----+------+
  | 1 | 1 | 6 | 3 | 1 |
  | 8 | 1 | 8 | 0 | 3 |
  | 9 | 2 | 27 | 3 | 2 |
  | 11 | 2 | 112 | 2 | 3 |
  +---------+--------+----------+-----+------+
  4 rows in set (0.00 sec)
  解析:在存储过程中设置了需要传参的变量p_playerno,调用存储过程的时候,通过传参将57赋值给p_playerno,然后进行存储过程里的SQL操作。
  存储过程体
  存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等
  过程体格式:以begin开始,以end结束(可嵌套)
  BEGIN
  BEGIN
  BEGIN
  statements;
  END
  END
  END
  注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的begin-end块(又叫做复合语句compound statement),则不需要分号。
  为语句块贴标签:
  [begin_label:] BEGIN
  [statement_list]
  END [end_label]
  例如:
  label1: BEGIN
  label2: BEGIN
  label3: BEGIN
  statements;
  END label3 ;
  END label2;
  END label1
  标签有两个作用:
  1、增强代码的可读性
  2、在某些语句(例如:leave和iterate语句),需要用到标签

   上文内容不用于商业目的,如涉及知识产权问题,请权利人联系博为峰小编(021-64471599-8017),我们将立即处理。
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号