文档章节

MySQL存储过程学习笔记

文文1
 文文1
发布于 2016/01/29 11:10
字数 1725
阅读 84
收藏 6
点赞 1
评论 0

一、基本语法及简单实例

1、创建简单的测试环境

mysql> use test;  
Database changed  
mysql> show tables;  
Empty set (0.00 sec)  
   
mysql> CREATE TABLE t(s1 INT);  
Query OK, 0 rows affected (0.06 sec)  
   
mysql> INSERT INTO t VALUES(5);  
Query OK, 1 row affected (0.02 sec)

2、选择分隔符

mysql> DELIMITER //

我们一般使用";"作为分隔符,但是在编写存储过程的时候这会带来一些问题,因为存储过程中有许多语句,修改会";"作为分隔符可使用语句"DELIMITER ;//"。

3、创建存储过程

mysql> CREATE PROCEDURE p1() SELECT * FROM t;//  
Query OK, 0 rows affected (0.08 sec)

"CREATE PROCEDURE"即为SQL语句部分,第二部分是过程名"p1"(这里需要注意的是存储过程名对大小写不敏感)。

第三部分 () 是参数列表,通常需要在其中添加参数,这里参数为空,但是"()"必须存在。

"SELECT * FROM t;"是存储过程的主体,注意哦,";"是主体的一部分哦,创建该存储过程的语句的真正结束符为"//"。

另外需要注意的一点是,和我们创建表一样,在创建存储过程前面需要检查是否存在同名的存储过程,即" DROP PROCEDURE IF EXISTS p1;",没错这正是删除一个存储过程的SQL语句。另外,不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程。

4、调用存储过程

mysql> CALL p1()//  
+------+  
| s1   |  
+------+  
|    5 |  
+------+  
1 row in set (0.00 sec)  
   
Query OK, 0 rows affected (0.00 sec)

这里只是简单的调用,在下一点关于参数的使用办法中有更为复杂的调用。

5、参数(Parameter)

mysql> CREATE PROCEDURE p2(p INT) SET @x = p ;//  
Query OK, 0 rows affected (0.02 sec)  
   
mysql> CALL p2(123)//  
Query OK, 0 rows affected (0.00 sec)  
   
mysql> SELECT @x//  
+------+  
| @x   |  
+------+  
|  123 |  
+------+  
1 row in set (0.01 sec)

这是输入参数的例子,我们选择了会话变量@x证明成功的将参数传入了改变量。

mysql> CREATE PROCEDURE p3(OUT p INT)  
    -> SET p = -5;//  
Query OK, 0 rows affected (0.00 sec)  
   
mysql> CALL p3(@y)//  
Query OK, 0 rows affected (0.01 sec)  
   
mysql> SELECT @y//  
+------+  
| @y   |  
+------+  
|   -5 |  
+------+  
1 row in set (0.00 sec)

这是输出参数的例子,我们选择会话变量@y去接收存储过程p3输出参数的值。

6、变量(Variables)

CREATE PROCEDURE P5()  
BEGIN  
 DECLARE a INT;  
 DECLARE b INT;  
 SET a = 5;  
 SET b = 5;  
 INSERT INTO t VALUES(a);  
 SELECT s1 FROM t WHERE s1>= b;  
END;  
-------------------------------------------------  
mysql> CALL p5();  
+----+  
| s1 |  
+----+  
|  5 |  
|  5 |  
+----+  
2 rows in set  
   
Query OK, 0 rows affected

在过程中定义的变量并不是真正的定义,你只是在BEGIN/END(即复合语句)块内定义了而已。注意这些变量和会话变量不一样,不能使用修饰符@你必须清楚的在BEGIN/END块中声明变量和它们的类型。变量一旦声明,你就能在任何能使用会话变量、文字、列名的地方使用。还需要注意的一点是,在一个块内,我们需要把所有要使用的变量先声明,才能在后面使用,并且不能在声明变量的语句间夹杂其他使用变量的语句,否会报语法错误。

CREATE PROCEDURE P6()  
BEGIN  
 DECLARE a,b INT DEFAULT 5;  
 INSERT INTO t VALUES(a);  
 SELECT s1 * a FROM t WHERE s1>= b;  
END;  
------------------------------------------------------  
mysql> CALL p6();  
+--------+  
| s1 * a |  
+--------+  
|     25 |  
|     25 |  
|     25 |  
+--------+

这里使用DEFAULT子句来设定初始值,如此我们可以不需要把DECLARE和SET语句的实现分开。

7、区块的定义使用

一般形式为

begin  
......  
end;

也可以给区块起别名,如:

lable:begin  
...........  
end lable;

可以用leave lable;跳出区块,执行区块以后的代码。

8、条件语句

一般形式为

if 条件 then  
statement  
else  
statement  
end if;

实例:

CREATE PROCEDURE p7(IN param1 INT)  
BEGIN  
 DECLARE v1 INT;  
 SET v1 = param1 + 1;  
 IF v1 = 0 THEN  
   INSERT INTO t VALUES(17);  
 END IF;  
 IF param1 = 0 THEN  
   UPDATE t SET s1 = s1 + 1;  
 ELSE  
   UPDATE t SET s1 = s1 + 2;  
 END IF;  
END;//  
-----------------------------------------------------------  
mysql> SELECT * FROM t;  
+----+  
| s1 |  
+----+  
|  6 |  
|  6 |  
|  6 |  
+----+  
3 rows in set  
   
mysql> CALL p7(0);  
Query OK, 3 rows affected  
   
mysql> CALL p7(0);  
Query OK, 3 rows affected  
   
mysql> SELECT * FROM t;  
+----+  
| s1 |  
+----+  
|  8 |  
|  8 |  
|  8 |  
+----+  
3 rows in set

过程很简单,可以看出调用两次即执行了两次UPDATE t SET s1= s1 + 1;语句。另外还有CASE指令,使用办法和IF一样简单,简单实例如下:

CREATE PROCEDURE p8(IN param1 INT)  
BEGIN  
 DECLARE v1 INT;  
 SET v1 = param1 + 1;  
 CASE v1  
   WHEN 0 THEN INSERT INTO tVALUES(17);  
   WHEN 1 THEN INSERT INTO tVALUES(18);  
   ELSE INSERT INTO tVALUES(19);  
 END CASE;  
END;//

9、循环语句

1)while循环

[label:] WHILE expression DO  
statements  
END WHILE [label] ;

实例:

CREATE PROCEDURE p9 ()   
BEGIN   
  DECLARE v INT;   
  SET v = 0;   
  WHILE v < 5 DO   
    INSERT INTO t VALUES(v);   
    SET v = v + 1;   
  END WHILE;   
END; //

2)repeat until循环

[label:] REPEAT  
statements  
UNTIL expression  
END REPEAT [label] ;

实例:

CREATE PROCEDURE p10 ()   
BEGIN   
  DECLARE v INT;   
  SET v = 0;   
  REPEAT   
    INSERT INTO t VALUES(v);   
    SET v = v + 1;   
    UNTIL v >= 5                                       
  END REPEAT;   
END; //

3)loop循环

[label:] LOOP  
statements  
END LOOP[label];

实例:

CREATE PROCEDUREp11 ()   
BEGIN   
  DECLARE v INT;   
  SET v = 0;   
  loop_label: LOOP   
    INSERT INTO t VALUES (v);   
    SET v = v + 1;   
    IF v >= 5 THEN   
      LEAVE loop_label;   
    END IF;   
  END LOOP;   
END; //

10、其他常用命令

1)showprocedure status

显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等

2)show createprocedure sp_name

显示某一个存储过程的详细信息

 

二、常见错误及处理办法

1、[Err] 1064 -You have an error in your SQL syntax; check the manual that corresponds to yourMySQL server version for the right syntax to use near '***'

很简单,1064即为SQL语法错误,仔细检查错误提示信息所指语句附近改正即可。

例:

CREATE PROCEDURE P12()  
BEGIN  
 DECLARE a INT;  
 SET a = 5;  
 DECLARE b INT;  
 SET b = 5;  
 INSERT INTO t VALUES(a);  
 SELECT s1 FROM t WHERE s1>= b;  
END;

提示信息为:

[Err] 1064 - You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax to use near'DECLARE b INT;

        SET b = 5;

        INSERT INTO t VALUES(a);

        SELECT s1 FROM t WHE' at line 5

提示在第5行,我们发现在变量声明语句"DECLARE b INT;"的前面有一条赋值语句"SET a = 5;",只需将其放到所有变量声明语句之后即可。


2、[Err] 1318 -Incorrect number of arguments for PROCEDURE *.*; expected *, got *

如提示信息,database_name.procedure_name的存储过程传入的参数个数不对。

例:

CREATE PROCEDURE p13(OUT p INT)  
SET p = -5;  
CALL p13();

提示信息为:

[Err] 1318 - Incorrect number of arguments for PROCEDURE test.p13;expected 1, got 0

改为CALL p13(@a); 即可。


3、[Err] 1414 -OUT or INOUT argument 1 for routine *.* is not a variable or NEWpseudo-variable in BEFORE trigger

此信息也是提示我们传入的参数不对,*.*的存储过程参数为输出(或输入)参数,而我们可能传入相反的参数,例如要求为输出参数,而我们传入的参数非会话变量,即会报此错。

例:

CALL p13(a); -- 或者CALL p13(0);

提示信息:

[Err] 1414 - OUT or INOUT argument 1 for routine test.p13 is not avariable or NEW pseudo-variable in BEFORE trigger

改正:

CALL p13(@a);

SELECT @a;

 

参考资料:

1、《mysql 5.0存储过程学习总结》--平凡的世界http://www.ccvita.com/100.html

2、《MYSQL 5.0存储过程》--Peter Gulutzan 著 陈朋奕 译


© 著作权归作者所有

共有 人打赏支持
文文1
粉丝 19
博文 328
码字总数 113821
作品 0
长沙
程序员
MySQL入门很简单-学习笔记 - 索引页

索引一下,方便阅读,后面有完整的PDF下载地址 MySQL入门很简单-学习笔记 - 第1 章 数据库概述 MySQL入门很简单-学习笔记 - 第 2 章 Windows平台下安装与配置MySQL MySQL入门很简单-学习笔...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

MySQL存储过程教程

在本节中,您将逐步学习如何在MySQL中开发存储过程。 首先,我们向您介绍存储过程概念,并讨论何时应该使用它。然后,我们向您演示如何使用过程代码的基本元素,例如:创建存储过程,if-else...

易百教程 ⋅ 2016/10/15 ⋅ 0

JDBC--BLOB

一、BLOB(二进制数据) 1. MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。 2.BLOB字段适用于存储大量的二进制数据,如图像、视频、音频,文件...

大道无名 ⋅ 2016/07/02 ⋅ 0

如何学习Liunx和个人学习大纲

提醒不甘平凡的我 2009大学刚刚毕业(不是正规大学,没来北京之前,在家里不是打架就是闲逛,家里人怕早晚出事,索性花钱上了这个“大学”)找了一家IT培训机构。当时的培训机构出名两家:“...

jcpokai521 ⋅ 2017/02/28 ⋅ 0

mysql 学习笔记

一、数据库简单介绍 1、 按照数据库的发展时间顺序,主要出现了以下类型数据库系统: Ø 网状型数据库 Ø 层次型数据库 Ø 关系型数据库 Ø 面向对象数据库 上面4中数据库系统中,关系型数据...

五大三粗 ⋅ 2015/02/11 ⋅ 0

《MySQL必知必会》笔记2

2016/2/26-27 第二十一章创建和操纵表 创建表 CREATE TABLE语句 IF NOT EXISTS:查看表名是否存在,并且仅在表名不存在时创建它。 NULL列和NOT NULL列。(NULL为默认设置) PRIMARY KEY:定义...

fxdhdu ⋅ 2016/03/01 ⋅ 0

Mysql存储过程 之 “Hello,World”

前天到亚信联创面试,应聘他们互联网产品部的java开发实习生,我有一段时间没关系数据库方面的知识了,而面试时这方面的问题又非常多,面试过程可想而知的悲催了,哎,不提了 ,好好学习才是...

YueZheng ⋅ 2012/03/31 ⋅ 0

MySQL入门很简单-学习笔记 - 第14章 存储过程和函数

避免编写重复的语句 安全性可控 执行效率高 14.1、创建存储过程和函数 14.1.1、创建存储过程 CREATE PROCEDUREspname ([procparameter[,...]]) [characteristic...] routine_body procedure ...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

MySQL入门教程系列-1.5 如何学习MySQL

在这里持续更新 MySQL入门教程系列-1.5 如何学习MySQL 如何学习 MySQL 这是一个伪命题,每个人都有适合自己的一套学习方法,各

同一种调调 ⋅ 2016/09/28 ⋅ 0

读《PHP和MySQL Web开发》

【第一部分】PHP基础与入门 1、PHP学习笔记1:基础知识快速浏览 http://my.oschina.net/bluefly/blog/472673 2、PHP学习笔记2:文件 http://my.oschina.net/bluefly/blog/477601 3、PHP学习笔...

slyso ⋅ 2015/07/14 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

十五周二次课

十五周二次课 17.1mysql主从介绍 17.2准备工作 17.3配置主 17.4配置从 17.5测试主从同步 17.1mysql主从介绍 MySQL主从介绍 MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主...

河图再现 ⋅ 56分钟前 ⋅ 0

docker安装snmp rrdtool环境

以Ubuntu16:04作为基础版本 docker pull ubuntu:16.04 启动一个容器 docker run -d -i -t --name flow_mete ubuntu:16.04 bash 进入容器 docker exec -it flow_mete bash cd ~ 安装基本软件 ......

messud4312 ⋅ 今天 ⋅ 0

OSChina 周一乱弹 —— 快别开心了,你还没有女友呢。

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @莱布妮子 :分享吴彤的单曲《好春光》 《好春光》- 吴彤 手机党少年们想听歌,请使劲儿戳(这里) @clouddyy :小萝莉街上乱跑,误把我认错成...

小小编辑 ⋅ 今天 ⋅ 7

mysql in action / alter table

change character set ALTER SCHEMA `employees` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci ;ALTER TABLE `employees`.`t2` CHARACTER SET = utf8mb4 , COLLAT......

qwfys ⋅ 今天 ⋅ 0

Java 开发者不容错过的 12 种高效工具

Java 开发者常常都会想办法如何更快地编写 Java 代码,让编程变得更加轻松。目前,市面上涌现出越来越多的高效编程工具。所以,以下总结了一系列工具列表,其中包含了大多数开发人员已经使用...

jason_kiss ⋅ 昨天 ⋅ 0

Linux下php访问远程ms sqlserver

1、安装freetds(略,安装在/opt/local/freetds 下) 2、cd /path/to/php-5.6.36/ 进入PHP源码目录 3、cd ext/mssql进入MSSQL模块源码目录 4、/opt/php/bin/phpize生成编译配置文件 5、 . ./...

wangxuwei ⋅ 昨天 ⋅ 0

如何成为技术专家

文章来源于 -- 时间的朋友 拥有良好的心态。首先要有空杯心态,用欣赏的眼光发现并学习别人的长处,包括但不限于工具的使用,工作方法,解决问题以及规划未来的能力等。向别人学习的同时要注...

长安一梦 ⋅ 昨天 ⋅ 0

Linux vmstat命令实战详解

vmstat命令是最常见的Linux/Unix监控工具,可以展现给定时间间隔的服务器的状态值,包括服务器的CPU使用率,内存使用,虚拟内存交换情况,IO读写情况。这个命令是我查看Linux/Unix最喜爱的命令...

刘祖鹏 ⋅ 昨天 ⋅ 0

MySQL

查看表相关命令 - 查看表结构    desc 表名- 查看生成表的SQL    show create table 表名- 查看索引    show index from  表名 使用索引和不使用索引 由于索引是专门用于加...

stars永恒 ⋅ 昨天 ⋅ 0

easyui学习笔记

EasyUI常用控件禁用方法 combobox $("#id").combobox({ disabled: true }); ----- $("#id").combobox({ disabled: false}); validatebox $("#id").attr("readonly", true); ----- $("#id").r......

miaojiangmin ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部