declare定义的变量名不能带‘@’符号,所以重新写

早上同事要我写个MySQL去除重复数据的SQL,想起来上次写过一篇MySQL去除重复数据的博客,使用导入导出加唯一索引实现的,但是那种方式对业务影响较大,所以重新写一个存储过程来删重复数据,这一写就写了一个上午,这种BUG确实是很令人沮丧和浪费时间的。

MySQL心得7-1-存储过程

MySQL 变量和条件,MySQL变量条件

用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句。言外之意,就是对数据进行修改。在标准的SQL中有3个语句,它们是INSERT、UPDATE以及DELETE。在MySQL中又多了一个REPLACE语句,因此,本文以MySQL为背景来讨论如何使有SQL中的更新语句。

这里把流程简单的描述一下,删重复数据的逻辑很简单:

 

概述  

 变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重要。

 mysql版本:5.6

  一、INSERT和REPLACE

1.根据重复判断条件找出重复记录的最小主键(一般是ID列)。

  1. 使用存储过程的优点有:

变量定义和赋值  

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

declare定义变量

在存储过程和函数中通过declare定义变量在BEGIN...END中,且在语句之前。并且可以通过重复定义多个变量

注意:declare定义的变量名不能带‘@’符号,mysql在这点做的确实不够直观,往往变量名会被错成参数或者字段名。

DECLARE var_name[,...] type [DEFAULT value]

例如:

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

SET变量赋值 

SET除了可以给已经定义好的变量赋值外,还可以指定赋值并定义新变量,且SET定义的变量名可以带‘@’符号,SET语句的位置也是在BEGIN ....END之间的语句之前。

1.变量赋值

SET var_name = expr [, var_name = expr] ...

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

  SELECT @pcount;

图片 1

 2.通过赋值定义变量

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SET @ID=1;
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
SELECT @ID;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

图片 2

  INSERT和REPLACE语句的功能都是向表中插入新的数据。这两条语句的语法类似。它们的主要区别是如何处理重复的数据。

2.在符合重复条件的记录中,把主键大于最小主键的记录全部删掉即可。

 

SELECT ... INTO语句赋值

 通过select into语句可以将值赋予变量,也可以之间将该值赋值存储过程的out参数,上面的存储过程select into就是之间将值赋予out参数。

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
DECLARE Pid INT;
SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;
SELECT Pid;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

这个存储过程就是select into将值赋予变量;

 图片 3

表中并没有depid=101 and name='陈'的记录。 

  1. INSERT的一般用法

假设我有如下表,需要删除start_time和end_time都一样的重复记录。

(1)存储过程在服务器端运行,执行速度快。

条件  

条件的作用一般用在对指定条件的处理,比如我们遇到主键重复报错后该怎样处理。 

定义条件

 定义条件就是事先定义某种错误状态或者sql状态的名称,然后就可以引用该条件名称开做条件处理,定义条件一般用的比较少,一般会直接放在条件处理里面。

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

1.没有定义条件:

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();

#查询变量值
SELECT @ID,@X;

图片 4

 报主键重复的错误,其中1062是主键重复的错误代码,23000是sql错误状态

图片 5

2.定义处理条件

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR 1062;
#引用前面定义的条件名称并做赋值处理
DECLARE EXIT HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;

在执行存储过程的步骤中并没有报错,但是由于我定义的是exit,所以在遇到报错sql就终止往下执行了。

图片 6

接下来看看continue的不同

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR SQLSTATE '23000';
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;

其中红色标示的是和上面不同的地方,这里定义条件使用的是SQL状态,也是主键重复的状态;并且这里使用的是CONTINUE就是遇到错误继续往下执行。

图片 7

图片 8

条件处理

条件处理就是之间定义语句的错误的处理,省去了前面定义条件名称的步骤。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE| EXIT| UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

handler_type:遇到错误是继续往下执行还是终止,目前UNDO还没用到。

CONTINUE:继续往下执行

EXIT:终止执行

condition_values:错误状态

SQLSTATE [VALUE] sqlstate_value:就是前面讲到的SQL错误状态,例如主键重复状态SQLSTATE '23000'

condition_name:上面讲到的定义条件名称;

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记,例如:DECLARE CONTINUE HANDLER FOR SQLWARNING。

NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION:是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

mysql_error_code:是错误代码,例如主键重复的错误代码是1062,DECLARE CONTINUE HANDLER FOR 1062

 

语句:

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN

#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2;
#开始事务必须在DECLARE之后
START TRANSACTION ;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(7,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

IF @x=2 THEN
  ROLLBACK;
ELSE
  COMMIT;
END IF;  

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();
#查询
SELECT @ID,@X;

图片 9

通过SELECT @ID,@X可以知道存储过程已经执行到了最后,但是因为存储过程后面有做回滚操作整个语句进行了回滚,所以ID=7的符合条件的记录也被回滚了。

  MySQL中的INSERT语句和标准的INSERT不太一样,在标准的SQL语句中,一次插入一条记录的INSERT语句只有一种形式。

图片 10

 

总结  

变量的使用不仅仅只有这些,在光标中条件也是一个很好的功能,刚才测试的是continue如果使用EXIT的话语句执行完“SET @ID=2;”就不往下执行了,后面的IF也不被执行整个语句不会被回滚,但是使用CONTINE当出现错误后还是会往下执行如果后面的语句还有很多的话整个回滚的过程将会很长,在这里可以利用循环,当出现错误立刻退出循环执行后面的if回滚操作,在下一篇讲循环语句会写到,欢迎关注。

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

变量和条件,MySQL变量条件 概述 变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重...

  INSERT INTO tablename(列名…) VALUES(列值);

那么存储过程如下:

(2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。

  而在MySQL中还有另外一种形式。

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
CREATE PROCEDURE Del_Dup_FOR_TEST()
BEGIN
DECLARE min_id INT;
DECLARE v_start_time,v_end_time DATETIME;
DECLARE v_count INT;
DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
  myloop: LOOP
  FETCH my_cur INTO v_start_time,v_end_time,min_id,v_count;
  IF done=1 THEN
  LEAVE myloop;
  END IF;
  DELETE FROM leo.test WHERE start_time=v_start_time AND end_time=v_end_time AND id>min_id;
  COMMIT;
  END LOOP myloop;
CLOSE my_cur;
END;
//
DELIMITER ;

 

  INSERT INTO tablename SET column_name1 = value1, column_name2 = value2,…;

逻辑很清晰,就是根据重复判断条件依次删掉重复组中主键大于最小主键的记录们。

(3)确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上述操作对数据库信息访问的权限。  www.2cto.com  

  第一种方法将列名和列值分开了,在使用时,列名必须和列值的数一致。如下面的语句向users表中插入了一条记录:

但是在编写过程中却遇到一个很恶心的BUG,我最初的内容是这么写的:

 

  INSERT INTO users(id, name, age) VALUES(123, ‘姚明’, 25);

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
CREATE PROCEDURE Del_Dup_FOR_TEST()
BEGIN
DECLARE min_id INT;
DECLARE start_time,end_time DATETIME;
DECLARE count INT;
DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
  myloop: LOOP
  FETCH my_cur INTO start_time,end_time,min_id,count;
  IF done=1 THEN
  LEAVE myloop;
  END IF;
  DELETE FROM leo.test WHERE start_time=start_time AND end_time=end_time AND id>min_id;
  COMMIT;
  END LOOP myloop;
CLOSE my_cur;
END;
//
DELIMITER ;

2.创建存储过程可以使用create procedure语句。

  第二种方法允许列名和列值成对出现和使用,如下面的语句将产生中样的效果。

不同的部分在于变量定义的名称,即:

 

  INSERT INTO users SET id = 123, name = ‘姚明’, age = 25;

FETCH INTO的变量名绝对不能是你定义CURSOR时SQL语句查出来的列名或者列别名,也就说你定义的变量名既不能是表中已经存在的列名,也不能是你定义游标时用过的别名(如本例中的count),只要一个条件不符合,FETCH INTO就把全部的变量赋NULL值,这点你可以尝试在FETCH INTO后加一句Select打印变量名验证。

要在MySQL 5.1中创建存储过程,必须具有CREATE routine权限。要想查看数据库中有哪些存储过程,可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息,可使用SHOWCREATE PROCEDURE sp_name命令,其中sp_name是存储过程的名称。

  如果使用了SET方式,必须至少为一列赋值。如果某一个字段使用了省缺值(如默认或自增值),这两种方法都可以省略这些字段。如id字段上使用了自增值,上面两条语句可以写成如下形式:

在查询到这个BUG之前去官网页面特地看了一下是否是我的语法有错误: ,确信语法没问题,但倒数第二条评论显示可能是列名的隐藏BUG,最后一条评论反驳了BUG说法,但没有办法我还是根据BUG REPORT做了以上修改,然后功能就正常了。

 

  INSERT INTO users (name, age) VALUES(‘姚明’,25);

关于此BUG的BUG报告页面详见MySQL BUG:#28227 和 BUG:#5967

CREATE PROCEDURE的语法格式:

  INSERT INTO uses SET name = ‘姚明’, age = 25;

那么再回头看一下官网文档下的最后一条评论,开始我认为最后一条反驳BUG的评论完全是扯淡,是哪个傻X说这不是个BUG的?后来仔细想了想,他俩都对,这确实也算个BUG,傻X的也是我。

 

  MySQL在VALUES上也做了些变化。如果VALUES中什么都不写,那MySQL将使用表中每一列的默认值来插入新记录。

贴一下页面下最后两条评论(截止2018.08.01):

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

  INSERT INTO users () VALUES();

Posted by Brent Roady on May 9, 2012
It should be noted that the local variable names used in FETCH [cursor] INTO must be different than the variable names used in the SELECT statement 
defining the CURSOR. Otherwise the values will be NULL. 
In this example, 
DECLARE a VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT a FROM table1;
FETCH cur1 INTO a;
the value of a after the FETCH will be NULL.
This is also described here: http://bugs.mysql.com/bug.php?id=28227

Posted by Jérémi Lassausaie on February 3, 2015
Answer for Brent Roady :
I don't see any bug in the bahaviour described.
DECLARE a VARCHAR(255);
/* you declare a variable "a" without a specified default value, a=NULL */
DECLARE cur1 CURSOR FOR 
SELECT a FROM table1;
/* You declare a cursor that selects "a" FROM a table */
OPEN cur1;
/* You execute your cursor query, a warning is raised because a is ambiguously defined but you don't see it */
FETCH cur1 INTO a;
/* you put your unique field in your unique row into a (basically you do "SET a=a;") so a is still NULL */
There is no bug report, just a misunderstanding.

 

  如果表名后什么都不写,就表示向表中所有的字段赋值。使用这种方式,不仅在VALUES中的值要和列数一致,而且顺序不能颠倒。 INSERT INTO users VALUES(123, ‘姚明’, 25);

Brent遇到的现象与我相同,并列出了BUG Report的链接。

   [characteristic ...] routine_body

  如果将INSERT语句写成如下形式MySQL将会报错。

Jeremi(猜测可能是个程序员)回答,这是一个显而易见的误解,当你声明了变量a(初始值为NULL),然后FETCH INTO a就相当于set a=a,在任何程序语言中这都是无解的。

 

  INSERT INTO users VALUES(‘姚明’,25);

因此在编写存储过程中为定义的变量加个前缀标识是很好的习惯,想起以前Oracle写存储过程确实都加v_前缀,SQL Server 都用@前缀,现在轮到mysql却忽略了,确实需要牢记下。

其中,proc_parameter的参数如下:

  2. 使用INSERT插入多条记录

 

   看到这个标题也许大家会问,这有什么好说的,调用多次INSERT语句不就可以插入多条记录了吗!但使用这种方法要增加服务器的负荷,因为,执行每一次 SQL服务器都要同样对SQL进行分析、优化等操作。幸好MySQL提供了另一种解决方案,就是使用一条INSERT语句来插入多条记录。这并不是标准的 SQL语法,因此只能在MySQL中使用。

[ IN | OUT | INOUT ] param_name type

  INSERT INTO users(name, age)
  VALUES(‘姚明’, 25), (‘比尔.盖茨’, 50), (‘火星人’, 600);

 

  上面的INSERT 语句向users表中连续插入了3条记录。值得注意的是,上面的INSERT语句中的VALUES后必须每一条记录的值放到一对(…)中,中间使用”,”分割。假设有一个表table1

characteristic特征如下:

  CREATE TABLE table1(n INT);

 

  如果要向table1中插入5条记录,下面写法是错误的:

  language SQL

  INSERT INTO table1 (i) VALUES(1,2,3,4,5);

 

  MySQL将会抛出下面的错误

 | [NOT] DETERMINISTIC

  ERROR 1136: Column count doesn’t match value count at row 1

 

  而正确的写法应该是这样:

 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  INSERT INTO t able1(i) VALUES(1),(2),(3),(4),(5);

 

  当然,这种写法也可以省略列名,这样每一对括号里的值的数目必须一致,而且这个数目必须和列数一致。如:

 | SQL SECURITY { DEFINER | INVOKER }

  INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);

 

  3. REPLACE语句

 | COMMENT 'string'

   我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一 个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用 INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重 复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

 

  使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。

说明:

  在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

 

   在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了 DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

●   sp_name:存储过程的名称,默认在当前数据库中创建。需要在特定数据库中创建存储过程时,则要在名称前面加上数据库的名称,格式为:db_name.sp_name。值得注意的是,这个名称应当尽量避免取与MySQL的内置函数相同的名称,否则会发生错误。

  REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

 

  REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50);
 
  插入多条记录:

 ●   proc_parameter:存储过程的参数,param_name为参数名,type为参数的类型,当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候,存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数,但是名称后面的括号是不可省略的。

  REPLACE INTO users(id, name, age)
  VALUES(123, ‘赵本山’, 50), (134,’Mary’,15);

 

  REPLACE也可以使用SET语句

注意:参数的名字不要等于列的名字,否则虽然不会返回出错消息,但是存储过程中的SQL语句会将参数名看做列名,从而引发不可预知的结果。

  REPLACE INTO users SET id = 123, name = ‘赵本山’, age = 50;

 

   上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一 个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

characteristic:存储过程的某些特征设定,下面一一介绍:

  CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

 

  假设table1中已经有了3条记录

language sql:表明编写这个存储过程的语言为SQL语言,目前来讲,MySQL存储过程还不能用外部编程语言来编写,也就是说,这个选项可以不指定。将来将会对其扩展,最有可能第一个被支持的语言是PHP。  www.2cto.com  

  a b c
  1 1 1
  2 2 2
  3 3 3

deterministic:设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果,设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。

  下面我们使用REPLACE语句向table1中插入一条记录。

 

  REPLACE INTO table1(a, b, c) VALUES(1,2,3);

contains SQL:表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句,但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定,默认的是CONTAINS SQL。

  返回的结果如下

 

  Query OK, 4 rows affected (0.00 sec)

SQL SECURITY:SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户(DEFINER)的许可来执行,还是使用调用者(INVOKER)的许可来执行。默认值是DEFINER。

  在table1中的记录如下

 

  a b c
  1 2 3

COMMENT 'string':对存储过程的描述,string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。

  我们可以看到,REPLACE将原先的3条记录都删除了,然后将(1, 2, 3)插入。

 

二、UPDATE

●   routine_body:这是存储过程的主体部分,也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句,这个部分总是以begin开始,以end结束。当然,当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。

  UPDATE的功能是更新表中的数据。这的语法和INSERT的第二种用法相似。必须提供表名以及SET表达式,在后面可以加WHERE以限制更新的记录范围。

 

  UPDATE table_anem SET column_name1 = value1, column_name2 = value2, …

  1.   在开始创建存储过程之前,先介绍一个很实用的命令,即delimiter命令。在MySQL中,服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候,存储过程体中可能包含多个SQL语句,每个SQL语句都是以分号为结尾的,这时服务器处理程序的时候遇到第一个分号就会认为程序结束,这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。

  WHERE … ;

 

  如下面的语句将users表中id等于123的记录的age改为24

DELIMITER语法格式为:DELIMITER $$

  UPDATE users SET age = 24 WHERE id = 123;

 

  同样,可以使用UPDATE更新多个字段的值 UPDATE users SET age = 24, name = ‘Mike’ WHERE id = 123;

说明:$$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”,一个“¥”、数字、字母等都可以。当使用DELIMITER命令时,应该避免使用反斜杠(“”)字符,因为那是MySQL的转义字符。

  上面的UPDATE语句通过WHERE指定一个条件,否则,UPDATE将更新表中的所有记录的值。

 

   在使用UPDATE更新记录时,如果被更新的字段的类型和所赋的值不匹配时,MySQL将这个值转换为相应类型的值。如果这个字段是数值类型,而且所赋 值超过了这个数据类型的最大范围,那么MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长,MySQL就将多余的字符串截去。如果设置非空 字段为空,那么将这个字段设置为它们的默认值,数字的默认值是0,字符串的默认值是空串(不是null,是”")。    

例:创建存储过程,实现的功能是删除一个特定学生的信息。

#如果定义为整形,给定一个值是
 
  有两种情况UPDATE不会对影响表中的数据。

 

  1. 当WHERE中的条件在表中没有记录和它匹配时。

DELIMITER $$

  2. 当我们将同样的值赋给某个字段时,如将字段abc赋为’123′,而abc的原值就是’123′。

 

  和INSERT、REPLACE一样,UPDATE也返回所更新的记录数。但这些记录数并不包括满足WHERE条件的,但却未被更新的记录。如下同的UPDATE语句就未更新任何记录。

CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))

  UPDATE users SET age = 30 WHERE id = 12;
  Query OK, 0 rows affected (0.00 sec)

 

  需要注意的时,如果一个字段的类型是TIMESTAMP,那么这个字段在其它字段更新时自动更新。

BEGIN

  在有些时候我们需要得到UPDATE所选择的行数,而不是被更新的行数。我们可以通过一些API来达到这个目的。如MySQL提供的C API提供了一个选项可以得到你想要的记录数。而MySQL的JDBC驱动得到的默认记录数也是匹配的记录数。

 

  UPDATE和REPLACE基本类似,但是它们之间有两点不同。

DELETE FROM XS WHERE 学号=XH;

  1. UPDATE在没有匹配记录时什么都不做,而REPLACE在有重复记录时更新,在没有重复记录时插入。

 

  2. UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除,再插入新的记录。也就是说,将所有的字段都更新了。

END $$

 

DELIMITER ;

 

说明:当调用这个存储过程时,MySQL根据提供的参数XH的值,删除对应在XS表中的数据。

 

在关键字BEGIN和END之间指定了存储过程体,当然,BEGIN-END复合语句还可以嵌套使用。

 

  1.  局部变量

 

在存储过程中可以声明局部变量,它们可以用来存储临时结果。要声明局部变量必须使用declare语句。在声明局部变量的同时也可以对其赋一个初始值。

 

DECLARE语法格式:DECLARE var_name[,...] type [DEFAULT value]

 

说明:var_name为变量名;type为变量类型;default子句给变量指定一个默认值,如果不指定默认为NULL的话。可以同时声明多个类型相同的局部变量,中间用逗号隔开。

 

例: 声明一个整型变量和两个字符变量。

 

DECLARE num INT(4);

 

DECLARE str1, str2 VARCHAR(6);

 

declare n char(10) default ‘abcdefg’;

 

说明:局部变量只能在BEGIN…END语句块中声明。

 

局部变量必须在存储过程的开头就声明,声明完后,可以在声明它的BEGIN…END语句块中使用该变量,其他语句块中不可以使用它。  www.2cto.com  

 

在存储过程中也可以声明用户变量,不过千万不要将这两个混淆。局部变量和用户变量的区别在于:局部变量前面没有使用@符号,局部变量在其所在的BEGIN…END语句块处理完后就消失了,而用户变量存在于整个会话当中。

 

  1.  使用SET语句赋值

 

要给局部变量赋值可以使用SET语句,SET语句也是SQL本身的一部分。语法格式为:SET  var_name = expr [,var_name = expr] ...

 

例: 在存储过程中给局部变量赋值。

 

SET num=1, str1= 'hello';

 

说明:与声明用户变量时不同,这里的变量名前面没有@符号。注意,例中的这条语句无法单独执行,只能在存储过程和存储函数中使用。

 

  1. SELECT...INTO语句(重点)

 

使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此,返回的结果只能有一行。语法格式为:

 

SELECT col_name[,...] INTO var_name[,...]  table_expr

 

说明:col_name是列名,var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分,这里不再叙述。

 

例: 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。

 

SELECT 姓名,专业名 INTO name, project

 

   FROMXS;  WHERE 学号= '081101';

 

  1.  流程控制语句

 

在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。例如:IF语句、CASE语句、LOOP语句、WHILE语句、iterate语句和LEAVE语句。

 

(1)IF语句

 

IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

 

语法格式为:

 

IF 判断的条件THEN 一个或多个SQL语句

 

[ELSEIF判断的条件THEN一个或多个SQL语句] ...

 

[ELSE一个或多个SQL语句]

 

END IF

 

说明:当判断条件为真时,就执行相应的SQL语句。

 

IF语句不同于系统的内置函数IF()函数,IF()函数只能判断两种情况,所以请不要混淆。

 

例: 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。

 

DELIMITER $$  www.2cto.com  

 

CREATE PROCEDURE XSCJ.COMPAR

 

(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )

 

BEGIN

 

IFK1>K2 THEN

本文由必威发布于必威-数据,转载请注明出处:declare定义的变量名不能带‘@’符号,所以重新写

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。