MySQL数据库新特性之存储过程入门教程
http://database.51cto.com/art/201107/273027_2.htm
在MYSQL 5中,终于引入了存储过程这一新特性,这将大大增强MYSQL 的数据库处理能力,在本文中,将指导读者快速掌握MYSQL 5的存储过程的基本知识,带领用户入门。
存储过程介绍
存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量 。同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。
存储过程的特点
作为存储过程,有以下这些优点:
(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。
(2)执行速度更快。存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接中读取。
(3)更强的安全性。存储过程是通过向用户授予权限(而不是基于表),它们可以提供对特定数据的访问,提高代码安全,比如防止 SQL注入。
(4) 业务逻辑可以封装存储过程中,这样不仅容易维护,而且执行效率也高
当然存储过程也有一些缺点,比如:
1 可移植性方面:当从一种数据库迁移到另外一种数据库时,不少的存储过程的编写要进行部分修改。
2 存储过程需要花费一定的学习时间去学习,比如学习其语法等。
在MYSQL中,推荐使用MYSQL Query Browswer(http://dev.mysql.com/doc/query-browser/en/)这个工具去进行存储过程的开发和管理。
下面分步骤来学习MYSQL中的存储过程。
1 定义存储过程的结束符
在存储过程中,通常要输入很多SQL语句,而SQL语句中每个语句以分号来结束,因此要告诉存储过程,什么位置是意味着整个存储过程结束,所以我们在编写存储过程前,先定义分隔符,我们这里定义“//”为分隔符,我们使用DELIMITER //这样的语法,就可以定义结束符了,当然你可以自己定义其他喜欢的符号。
2 如何创建存储过程
下面先看下一个简单的例子,代码如下:
- DELIMITER //
- CREATE PROCEDURE `p2` ()
- LANGUAGE SQL
- DETERMINISTIC
- SQL SECURITY DEFINER
- COMMENT 'A procedure'
- BEGIN
- SELECT 'Hello World !';
- END//
下面讲解下存储过程的组成部分:
1)首先在定义好终结符后,使用CREATE PROCEDURE+存储过程名的方法创建存储过程,LANGUAGE选项指定了使用的语言,这里默认是使用SQL。
2)DETERMINISTIC关键词的作用是,当确定每次的存储过程的输入和输出都是相同的内容时,可以使用该关键词,否则默认为NOT DETERMINISTIC。
3) SQL SECURITY关键词,是表示调用时检查用户的权限。当值为INVOKER时,表示是用户调用该存储过程时检查,默认为DEFINER,即创建存储过程时检查。
4) COMMENT部分是存储过程的注释说明部分。
5)在BEGIN END部分中,是存储过程的主体部分。
3 调用存储过程的方法
调用存储过程的方法很简单,只需要使用call命令即可,后面跟要调用存储过程的名称及输入的变量列表,比如:
- CALL stored_procedure_name (param1, param2, ....)
- CALL procedure1(10 , 'string parameter' , @parameter_var);
4 修改和删除存储过程
可以用ALTER的语法去修改存储过程的主要特征和参数,要修改其存储过程的主体部分的话,必须要先删除然后再重建。比如下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA,并指明调用者可以执行。代码执行如下:
- ALTER PROCEDURE num_from_employee
- MODIFIES SQL DATA SQL SECURITY INVOKER ;
而删除存储过程的语法为使用DROP关键词即可。如下
- DROP PROCEDURE IF EXISTS p2;
5 存储过程的参数
下面来学习下存储过程中的参数,先看下存储过程中的参数形式,如下:
- CREATE PROCEDURE proc1 ()-----这个存储过程中是空的参数列表
- CREATE PROCEDURE proc1 (IN varname DATA-TYPE)-----这个存储过程中有一个输入参数,名称为varname,后面是跟数据类型DATA-TYPE,IN参数是默认的,因此可以省略不写
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)-----这个存储过程中varname为输出参数
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)-----这个存储过程中,varname既是输入参数也是输出参数
下面具体看个例子,首先是IN输入参数的例子,如下:
- DELIMITER //
- CREATE PROCEDURE `proc_IN` (IN var1 INT)
- BEGIN
- SELECT var1 + 2 AS result;
- END//
输出OUT参数例子如下:
- DELIMITER //
- CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
- BEGIN
- SET var1 = 'This is a test';
- END //
IN-OUT的例子:
- DELIMITER //
- CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
- BEGIN
- SET var1 = var1 * 2;
- END //
6 如何定义变量
下面讲解下MYSQL 5存储过程中,如何定义变量。
必须显式地在存储过程的一开始声明变量,并指出它们的数据类型,一但声明了变量后,就可以在存储过程中使用,定义变量的语法如下:
- DECLARE varname DATA-TYPE DEFAULT defaultvalue
举例说明:
- DECLARE a, b INT DEFAULT 5;
- DECLARE str VARCHAR(50);
- DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
- DECLARE v1, v2, v3 TINYINT;
一旦定义好变量,就可以在存储过程中对其进行赋初值,并进行各类相关的操作,比如:
- DELIMITER //
- CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
- BEGIN
- DECLARE a, b INT DEFAULT 5;
- DECLARE str VARCHAR(50);
- DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
- DECLARE v1, v2, v3 TINYINT;
- INSERT INTO table1 VALUES (a);
- SET str = 'I am a string';
- SELECT CONCAT(str,paramstr), today FROM table2 WHERE b>=5;
- END //
MYSQL存储过程的语法结构
MYSQL存储过程中支持IF,CASE,ITERATE,LEAVE LOOP,WHILE和REPEAT等语法结构和语句,在本文中,着重介绍IF,CASE和WHILE语法,因为它们使用的最为广泛。
IF 语句
if语句使用的是if…then end if的语法结构,例子如下:
- DELIMITER //
- CREATE PROCEDURE `proc_IF` (IN param1 INT)
- BEGIN
- DECLARE variable1 INT;
- SET variable1 = param1 + 1;
- IF variable1 = 0 THEN
- SELECT variable1;
- END IF;
- IF param1 = 0 THEN
- SELECT 'Parameter value = 0';
- ELSE
- SELECT 'Parameter value <= 0';
- END IF;
- END //
CASE语句
当有很多IF语句时,就应该考虑使用CASE语句了,它是多分支选择语句,有两种写法:
第一种写法:
- DELIMITER //
- CREATE PROCEDURE `proc_CASE` (IN param1 INT)
- BEGIN
- DECLARE variable1 INT;
- SET variable1 = param1 + 1;
- CASE variable1
- WHEN 0 THEN
- INSERT INTO table1 VALUES (param1);
- WHEN 1 THEN
- INSERT INTO table1 VALUES (variable1);
- ELSE
- INSERT INTO table1 VALUES (99);
- END CASE;
- END //
另外一种写法:
- DELIMITER //
- CREATE PROCEDURE `proc_CASE` (IN param1 INT)
- BEGIN
- DECLARE variable1 INT;
- SET variable1 = param1 + 1;
- CASE
- WHEN variable1 = 0 THEN
- INSERT INTO table1 VALUES (param1);
- WHEN variable1 = 1 THEN
- INSERT INTO table1 VALUES (variable1);
- ELSE
- INSERT INTO table1 VALUES (99);
- END CASE;
- END //
WHILE语句
WHILE语句跟普通编程语言中的while语句差不多,例子如下:
- DELIMITER //
- CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
- BEGIN
- DECLARE variable1, variable2 INT;
- SET variable1 = 0;
- WHILE variable1
- INSERT INTO table1 VALUES (param1);
- SELECT COUNT(*) INTO variable2 FROM table1;
- SET variable1 = variable1 + 1;
- END WHILE;
- END //
8 MYSQL存储过程中的游标
MYSQL中的游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。MYSQL中的游标的语法如下:
- DECLARE cursor-name CURSOR FOR SELECT ...; /* 声明一个游标,名称为cursor-name,并用CURSOR FOR SELECT*/
- DECLARE CONTINUE HANDLER FOR NOT FOUND /*指定当遍历完结果集后,游标如何继续处理*/
- OPEN cursor-name; /*打开游标 */
- FETCH cursor-name INTO variable [, variable]; /* 将变量赋值给游标*/
- CLOSE cursor-name; /*使用后关闭游标*/
一个具体的例子如下:
- DELIMITER //
- CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
- BEGIN
- DECLARE a, b, c INT;
- DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
- OPEN cur1;
- SET b = 0;
- SET c = 0;
- WHILE b = 0 DO
- FETCH cur1 INTO a;
- IF b = 0 THEN
- SET c = c + a;
- END IF;
- END WHILE;
- CLOSE cur1;
- SET param1 = c;
- END //
其中,DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
表示将从table1表中选取col1列的内容放到游标curl中,即每次游标遍历的结果都放在curl中,要注意游标只能向前遍历,而不能向后,并且注意,游标不能更新,最后关闭游标。
发表评论
-
Android中SQLiteOpenHelper类的onUpgrade方法的作用
2012-02-09 11:50 4552Android中SQLiteOpenHelper类的onUpg ... -
在线升级Android应用程序的思路
2012-02-07 11:34 813在线升级Android应用程序的思路 http://www. ... -
Android数据库内容变化的监听
2012-02-07 11:31 5979Android数据库内容变化的监听 首先介绍内容监 ... -
android中的数据库操作
2012-02-07 10:50 1399android中的数据库操作 ... -
SQLiteOpenHelper类与自动升级数据库
2012-02-07 10:31 2226SQLiteOpenHelper类与自动升级数据库 S ... -
SQLite外键的实现
2012-02-07 10:30 1640SQLite外键的实现 SQLite现在的版本还不支持 ... -
29日修改数据库设计
2012-01-29 15:24 0数据表设计: 账号信息表account ... -
最新数据库设计
2012-01-28 21:21 0数据表设计: 工资管理 账号信息表Acc ... -
数据库设计
2012-01-11 15:27 0数据库设计 信息表:infotypecreate ta ... -
cmd下操作mysql
2011-12-20 14:11 914cmd下操作mysql 连接mysql服务器 mysql ... -
你能说出SQL聚集索引和非聚集索引的区别吗?
2011-12-07 15:18 0你能说出SQL聚集索引和非聚集索引的区别吗? http ... -
打造自己的数据访问层
2011-12-07 15:01 1242打造自己的数据访问层 http://database. ... -
论MySQL何时使用索引,何时不使用索引
2011-12-07 14:03 899论MySQL何时使用索引, ... -
详细讲解如何将数据模型转换成数据库设计
2011-12-07 12:42 1076详细讲解如何将数据模 ... -
讲解SQL与Oracle外键约束中的级联删除
2011-12-07 12:33 0讲解SQL与Oracle外键约束中的级联删除 当代 ... -
SQLite-CREATE TRIGGER
2011-12-07 09:07 1307SQLite-CREATE TRIGGER http: ... -
主键与外键的关系、级联保存、更新、删除
2011-12-06 14:16 7172主键与外键的关系、级联保存、更新、删除 http://www ... -
MySQL 数据类型
2011-12-06 10:18 744MySQL 数据类型 数值类型 MySQL 的数 ... -
MyISAM InnoDB 区别
2011-12-02 17:03 724MyISAM InnoDB 区别 http://www.ph ... -
事务的原理
2011-12-02 17:01 1220(1):事务(Transaction)是 ...
相关推荐
完整版 MySQL8.0从入门到精通 MySQL数据库教程 第10章 存储过程和函数(共20页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第11章 视图(共20页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第12章...
完整版 MySQL8.0从入门到精通 MySQL数据库教程 第10章 存储过程和函数(共20页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第11章 视图(共20页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第12章...
完整版 MySQL8.0从入门到精通 MySQL数据库教程 第10章 存储过程和函数(共20页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第11章 视图(共20页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第12章...
高级部分的内容包含了视图的使用,存储过程的创建使用,索引,触发器,事务的使用,安全管理,数据库的备份与还原,最后对于提高性能简单总结 能学到什么:1. mysql的启动和关闭,使用数据库,对库的基本操作以及...
《MySQL数据库入门》教学设计 课程名称: MySQL开发入门教程 授课年级: XXXX年级 授课学期: XXXX学年第一学期 教师姓名: 某某老师 《MySQL数据库入门》教学设计全文共7页,当前为第1页。2014年09月09日 《MySQL...
第09章 索引(共11页).pptMySQL从入门到精通 第10章 存储过程和函数(共19页).pptMySQL从入门到精通 第11章 视图(共20页).pptMySQL从入门到精通 第12章 触发器(共11页).pptMySQL从入门到精通 第13章 用户管理...
第09章 索引(共11页).pptMySQL从入门到精通 第10章 存储过程和函数(共19页).pptMySQL从入门到精通 第11章 视图(共20页).pptMySQL从入门到精通 第12章 触发器(共11页).pptMySQL从入门到精通 第13章 用户管理...
1-Mysql介绍与环境安装 2-图形化工具介绍,库、表与数据的关系 3-库与表的创建 4-添加数据与删除数据 5-简单查询语句 6-作业讲解与修改语句 7-DDL之数据库 8-DDL之表操作 ...33-存储过程之Loop(大结局)
第09章 索引(共11页).pptMySQL从入门到精通 第10章 存储过程和函数(共19页).pptMySQL从入门到精通 第11章 视图(共20页).pptMySQL从入门到精通 第12章 触发器(共11页).pptMySQL从入门到精通 第13章 用户管理...
1.mysql 备份数据 2.Mysql 更新数据 ...10.浅谈MySQL存储引擎选择InnoDB还是MyISAM 11.浅谈unique列上插入重复值的MySQL解决方案 12.深入了解MySQL 5.5分区功能增强.doc 13.在MySQL中操作日期和时间
完成课程的学习可以帮助同学们快速掌握和入门MySQL数据库,并能够快速建立起对数据库世界的认知。课程内容讲解了MySQL一系列的基础操作及数据库的查询,数据类型, DDL的操作数据库和表操作,数据库的设计与数据库的...
动力节点的杜老师讲述的mysql教程,详细讲解了MySQL的相关知识,包括MySQL概述,MySQL应用环境,MySQL系统特性,MySQL初学基础,MySQL管理工具,如何安装MySQL及MySQL新特性,通过观看可掌握MySQL全套知识。
动力节点的杜老师讲述的mysql教程,详细讲解了MySQL的相关知识,包括MySQL概述,MySQL应用环境,MySQL系统特性,MySQL初学基础,MySQL管理工具,如何安装MySQL及MySQL新特性,通过观看可掌握MySQL全套知识。
第09章 索引(共11页).pptMySQL从入门到精通 第10章 存储过程和函数(共19页).pptMySQL从入门到精通 第11章 视图(共20页).pptMySQL从入门到精通 第12章 触发器(共11页).pptMySQL从入门到精通 第13章 用户管理...
动力节点的杜老师讲述的mysql教程,详细讲解了MySQL的相关知识,包括MySQL概述,MySQL应用环境,MySQL系统特性,MySQL初学基础,MySQL管理工具,如何安装MySQL及MySQL新特性,通过观看可掌握MySQL全套知识。
动力节点的杜老师讲述的mysql教程,详细讲解了MySQL的相关知识,包括MySQL概述,MySQL应用环境,MySQL系统特性,MySQL初学基础,MySQL管理工具,如何安装MySQL及MySQL新特性,通过观看可掌握MySQL全套知识。
这个ppt主要是讲解mysql数据库的, 包括数据库安装,配置文件,表,存储过程,视图,函数等,以及优化,主从同步。
动力节点的杜老师讲述的mysql教程,详细讲解了MySQL的相关知识,包括MySQL概述,MySQL应用环境,MySQL系统特性,MySQL初学基础,MySQL管理工具,如何安装MySQL及MySQL新特性,通过观看可掌握MySQL全套知识。
第09章 索引(共11页).pptMySQL从入门到精通 第10章 存储过程和函数(共19页).pptMySQL从入门到精通 第11章 视图(共20页).pptMySQL从入门到精通 第12章 触发器(共11页).pptMySQL从入门到精通 第13章 用户管理...
- 学习MySQL数据库的基本概念、特性、语法 - 了解如何通过Node.js程序操作MySQL数据库 - 掌握SQL语句的具体语法,会使用SELECT、INSERT等语句操作数据库 其他说明: - 文件内容比较全面地概述了MySQL数据库的知识...