mysql创建存储过程失败 mysql创建存储过程命令

 

 

Mysql命令行创建存储过程时，首先要输入分隔符

DELIMITER // 
CREATE PROCEDURE proc () 
begin 
…… 
end 
// 
OK，可以创建完成了。

显示存储过程命令

show create procedure 存储过程名；

删除存储过程命令

drop procedure 存储过程名；

http://database.51cto.com/art/201011/235017.htm

MYSQL命令行的使用是学习MYSQL数据库过程中的基础知识，那么应该如何用MYSQL命令行创建存储过程呢？

也尝试了一些方法，但是第一个分号mysql就以为语句结束了，就举个例子来说：

1. CREATE PROCEDURE p7 (IN b INTEGER(12))  
2.  
3. begin  
4.  
5. declare a INTEGER(12);  
6.  
7. set a=5;  
8.  
9. INSERT INTO t VALUES (a);  
10.  
11. SELECT s1*a FROM t WHERE b<=s1;  
12.  
13. End  
14.

这个存储过程，在命令行直接创建的话，在第三排你输入分号后就报错了……因为MYSQL以为这个语句结束了，那对于MYSQL而言

1. CREATE PROCEDURE p7 (IN b INTEGER(12))  
2.  
3. begin  
4.  
5. declare a INTEGER(12);  
6.  

肯定是个错误语句。

然后看了些东西，原来要加分隔符- -……….

1. DELIMITER //  
2.  
3. CREATE PROCEDURE p7 (IN b INTEGER(12))  
4.  
5. begin  
6.  
7. declare a INTEGER(12);  
8.  
9. set a=5;  
10.  
11. INSERT INTO t VALUES (a);  
12.  
13. SELECT s1*a FROM t WHERE b<=s1;  
14.  
15. End  
16.  
17. //  
18.

就OK了…………意思就是说在//……//之间的当成一个语句执行所以MYSQL接到的才是这个存储过程完整的创建语句。

http://zhuixue.iteye.com/blog/375353

查询数据库中的存储过程

 

方法一：

       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

 

方法二：

         show procedure status;

 

 

查看存储过程或函数的创建代码

 

show create procedure proc_name;
show create function func_name;

http://www.111cn.net/database/mysql/35817.htm

方法一：（直接查询）

select `specific_name` from mysql.proc where db = 'your_db_name' and `type` = 'procedure'

方法二：（查看数据库里所有存储过程+内容）

show procedure status;

方法三：（查看当前数据库里存储过程列表）

select specific_name from mysql.proc ;

方法四：(查看某一个存储过程的具体内容)

select body from mysql.proc where specific_name = 'your_proc_name';

查看存储过程或函数的创建代码 ：

show create procedure your_proc_name;
show create function your_func_name;

删除存储过程：

drop procedure your_proc_name;

=============

http://543197.blog.51cto.com/533197/114141

文章引用:

[url]http://bbs.tarena.com.cn/viewthread.phptid=166328[/url]

MySQL 存储过程是从 MySQL 5.0 开始增加的新功能。存储过程的优点有一箩筐。不过最主要的还是执行效率和SQL 代码封装。特别是 SQL 代码封装功能，如果没有存储过程，在外部程序访问数据库时（例如 PHP），要组织很多 SQL 语句。特别是业务逻辑复杂的时候，一大堆的 SQL 和条件夹杂在 PHP 代码中，让人不寒而栗。现在有了 MySQL 存储过程，业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。 一、MySQL 创建存储过程 
“pr_add” 是个简单的 MySQL 存储过程，这个存储过程有两个 int 类型的输入参数 “a”、“b”，返回这两个参数的和。 
drop procedure if exists pr_add;
-- 计算两个数之和

create procedure pr_add
(
   a int,
   b int
)
begin
   declare c int;
   if a is null then
      set a = 0;
   end if;
   if b is null then
      set b = 0;
   end if;
   set c = a + b;
   select c as sum;
   /*
   return c; -- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
   */
end;

二、调用 MySQL 存储过程 
call pr_add(10, 20);
执行 MySQL 存储过程，存储过程参数为 MySQL 用户变量。 
set @a = 10;
set @b = 20;
call pr_add(@a, @b);
三、MySQL 存储过程特点 
创建 MySQL 存储过程的简单语法为： 
create procedure 存储过程名字()
(
   [in|out|inout] 参数 datatype
)
begin
   MySQL 语句;
end;
MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。 
1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()” 
2. MySQL 存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。 

create procedure pr_add
(
   @a int,  -- 错误
   b int    -- 正确
)

3. MySQL 存储过程的参数不能指定默认值。 
4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。 

create procedure pr_add
(
   a int,
   b int
)
as              -- 错误，MySQL 不需要 “as”
begin
   mysql statement ...;
end;

5. 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。 

create procedure pr_add
(
   a int,
   b int
)
begin
   mysql statement 1 ...;
   mysql statement 2 ...;
end;

6. MySQL 存储过程中的每条语句的末尾，都要加上分号 “;” 

...
   declare c int;
   if a is null then
      set a = 0;
   end if;
   ...
end;

7. MySQL 存储过程中的注释。 

/*
     这是个
     多行 MySQL 注释。
   */
   declare c int;     -- 这是单行 MySQL 注释 （注意 -- 后至少要有一个空格）
   if a is null then  # 这也是个单行 MySQL 注释
      set a = 0;
   end if;
   ...
end;

8. 不能在 MySQL 存储过程中使用 “return” 关键字。 

set c = a + b;
   select c as sum;
   /*
   return c; -- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
   */
end;

9. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()” 
call pr_no_param();
10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。 
call pr_add(10, null);

===================================================

一.本文所涉及的内容（Contents）

1. 本文所涉及的内容（Contents）
2. 背景（Contexts）
3. 设计思路（Design）
4. 迁移自动化特点（Points）
5. 实现代码（SQL Codes）
6. 参考文献（References）

二.背景（Contexts）

SQL Server 数据库迁移偏方，在上篇文章中设计了一张临时表，这个临时表记录搬迁的配置信息，用一个存储过程读取这张表进行数据的迁移，再由一个Job进行迭代调用这个存储过程。

　　在这次MySQL的实战中，我的数据库已经做了4个分片，分布在不同的4台机器上，每台机器上的数据量有1.7亿（1.7*4=6.8亿），占用空间260G（260*4=1040G），这次迁移的目的就是删除掉一些历史记录，减轻数据库压力，有人说这为什么不使用表分区呢？这跟我们的业务逻辑有关造成无法使用表分区，至于为什么，参考阅读：MySQL表分区实战，其中最重要就是唯一索引的问题，扩展阅读：MySQL当批量插入遇上唯一索引，这篇文章需要了解MySQL的定时器的一些知识：MySQL定时器Events

　　本文与SQL Server 数据库迁移偏方最大的不同就是MySQL的Events不是串行执行的，当作业调用的存储过程还没有执行完毕，但又到了调度的时间，MySQL不会等待上次作业完成之后再调度，所以会造成重复调用读取到相同的数据；而SQL Server并不存在上面的问题。

三.设计思路（Design）

1. 创建一个临时表TempBlog_Log，这个表用于保存每次转移数据的ID起始值和结束值，以及搬迁的开始时间和结束时间；（这个ID是我们要迁移表的主键，自增字段，唯一标识）

2. 创建一个存储过程InsertData()，这个存储过程用于在TempBlog_Log表中插入记录，创建这个存储过程是因为MySQL跟SQL Server有些不同，MySQL不支持匿名存储过程，SQL Server直接执行SQL就可以了，无需为这些SQL再创建一个存储过程，这就是匿名存储过程了；

3. 创建一个存储过程MoveBlogData()，这个存储过程用于在TempBlog_Log表中读取记录，再批量把BlogA数据转移到BlogB中；这个是核心逻辑，解决了定时器重复调度的问题，详情见代码的解释；

4. 创建一个定时器e_Blog, 这个定时器定时调用存储过程MoveBlogData()，但是这里存在重复调度的问题，只能通过存储过程MoveBlogData()进行控制。

四.迁移自动化特点（Points）

1. 该设计适应于大数据的迁移；

2. 可以最小化宕机时间（在转移的过程中BlogA还是一直在进数据的，只是在最后一部分数据的时候需要短时间的停入库操作）；

3. 可以防止MySQL定时器重复执行所带来的问题；

4. 可以实时监控数据转移的进度；

5. 数据迁移可能需要持续好几天的时间，它能保证BlogB的数据会无限的接近BlogA的数据；

五.实现代码（SQL Codes）

(一) 创建临时表TempBlog_Log

-- 创建表
CREATE TABLE TempBlog_Log(
    BeginId INT NOT NULL,
    EndId INT NOT NULL,
    IsDone BIT DEFAULT b'0' NOT NULL,
    BeginTime DATETIME DEFAULT NULL,
    EndTime DATETIME DEFAULT NULL,
PRIMARY KEY(BeginId) 
);

下面就对表结构进行字段解释：

1) BeginId、EndId都是ServerA迁移表的主键值，BeginId表示一次数据迁移的起始值，EndId表示一次数据迁移的结束值，两个值的差就是这次数据转移的数据量；

2) IsDone 表示是否已经成功转移数据；

3) BeginTime表示转移的开始时间，EndTime表示转移的结束时间，这两个字段设置缺省值为NULL很关键，是后面进行判断是否重复执行的依据；

 

(二) 创建存储过程InsertData()

-- 存储过程
DELIMITER $$
USE `DataBaseName`$$
DROP PROCEDURE IF EXISTS `InsertData`$$

CREATE DEFINER=`root`@`%` PROCEDURE `InsertData`()
BEGIN
    DECLARE ids_begin,ids_end,ids_increment INT;
    SET ids_begin=130000000;-- 需要转移开始Id值
    SET ids_end=210000000;-- 需要转移结束Id值
    SET ids_increment=200000;-- 每次转移的Id量
    WHILE ids_begin < ids_end DO 
        INSERT INTO TempBlog_Log(BeginId,EndId) VALUES(ids_begin,ids_begin+ids_increment);
        SET ids_begin = ids_begin + ids_increment;
    END WHILE; 
END$$
    
DELIMITER ;

MySQL中不支持匿名存储过程，所以为了在临时表TempBlog_Log插入记录，只能创建一个存储过程了，如果你还没写过MySQL的存储过程，那么这是一个很好的例子。

1) 为了能在存储过程中使用MySQL的分隔符“;”，DELIMITER $$表示你以“$$”作为分隔符，你也可以使用“//”；

2) 定义变量时，你需要把所有的变量定义完了，之后再进行赋值，不然会报错，这跟SQL Server是有区别的；

3) WHILE条件后面需要加DO，而且要以END WHILE;作为结束标记；

4) 作为存储过程的结束，再次出现“$$”表示已经结束，跟上一个“$$”形成一个整体、过程，并重新设置“;”为分隔符；

5) 执行CALL InsertData();调用上面的存储过程，插入数据，调用完毕的结果如下图Figure1所示：

（Figure1：转移前状态）

 

(三) 创建保留数据的新表BlogB

　　做完上面的准备工作，接下来就是创建与BlogA相同结构的BlogB表了，有些不同的就是不需要在BlogB创建太多的索引，只需要存储两个索引就可以了，一个是ID的聚集索引，一个是唯一索引（在批量插入的时候需要判重）；

　　上面索引是根据我业务上的需求决定的，你需要视情况而定；

 

(四) 创建存储过程MoveBlogData()

DELIMITER $$
USE `DataBaseName`$$
DROP PROCEDURE IF EXISTS `MoveBlogData`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `MoveBlogData`()
BEGIN
    DECLARE blog_ids_begin INT;-- Id起始值
    DECLARE blog_ids_end INT;-- Id结束值
    DECLARE blog_ids_max INT;-- BlogA表现在的最大值
    DECLARE blog_begintime INT;-- 执行开始时间
    DECLARE blog_endtime INT;-- 执行结束时间
    -- 查询TempBlog_Log表还没有done的记录
    SELECT BeginId,EndId,BeginTime,EndTime INTO blog_ids_begin,blog_ids_end,blog_begintime,blog_endtime FROM TempBlog_Log WHERE IsDone = 0 ORDER BY BeginId LIMIT 0,1;
    
    -- 防止了定时器的重复执行
    IF(blog_begintime IS NULL AND blog_endtime IS NULL) THEN
        -- 设置当前最大的Id值
        SELECT MAX(ids) INTO blog_ids_max FROM BlogA;
        -- 防止转移超过当前最大值的Id数据
        IF(blog_ids_begin != 0 AND blog_ids_end != 0 AND blog_ids_max >= blog_ids_end) THEN
            -- 更新执行开始时间
            UPDATE TempBlog_Log SET BeginTime = NOW() WHERE BeginId = blog_ids_begin;
            -- 插入Id段数据，忽略重复值
            INSERT IGNORE INTO BlogB (ID,AuthorID,Content,QUOTE,QuoteID,Author,TIME,Url,ImageUrl,Transmits,Comments,HASH,Site,AuthorUID,TYPE,HotTopic,AddOn,QuoteAuthorID,IDs)
            SELECT ID,AuthorID,Content,QUOTE,QuoteID,Author,TIME,Url,ImageUrl,Transmits,Comments,HASH,Site,AuthorUID,TYPE,HotTopic,AddOn,QuoteAuthorID,IDs
                FROM BlogA WHERE IDs >= blog_ids_begin AND IDs < blog_ids_end;
            -- 更新执行结束时间
            UPDATE TempBlog_Log SET IsDone = 1,EndTime = NOW() WHERE BeginId = blog_ids_begin;
        END IF;
    END IF;
END$$

DELIMITER ;

这个存储过程是整个搬迁数据的核心代码，之所以说是核心，是因为它把比较多的细节考虑进去，基本上实现自动化的目的。

1) 代码中IF(blog_begintime IS NULL AND blog_endtime IS NULL) 防止了定时器的重复执行，两个值都为NULL的时候表示这个Id段的数据还没有被转移，这样就可以跳过，不执行下面的逻辑；

2) 查询BlogA的最大值可以防止转移超过当前BlogA最大值的Id数据，只有当blog_ids_max>=blog_ids_end才符合转移的条件；

3) 在MySQL中对唯一索引约束的数据操作有很多的关键字支持，INSERT IGNORE INTO就是在批量插入过程中只插入没有的数据，忽略重复的数据；更多唯一索引的信息：MySQL当批量插入遇上唯一索引

4) 查询中FROM BlogA WHERE IDs >= blog_ids_begin AND IDs < blog_ids_end;需要注意IDs值的闭合关系，不然造成重复数据或者丢失数据；

 

(五) 创建定时器e_Blog

DELIMITER $$

CREATE DEFINER=`root`@`localhost` EVENT `e_blog` 
ON SCHEDULE EVERY 30 SECOND 
STARTS '2012-12-07 14:58:53' 
ON COMPLETION PRESERVE DISABLE 
DO CALL MoveBlogData()$$

DELIMITER ;

这定时器e_Blog的作用是在每隔30 SECOND调用一次存储过程MoveBlogData()，至于有没转移数据那就是存储过程判断了，跟定时器的调度频率完全没有关系，更多关于定时器的信息：MySQL定时器Events

 

(六) 监控数据转移的状态

当定时器启动后，可以查看TempBlog_Log表监控调度的进度：

（Figure2：转移中状态）

Figure2表示正在转移Id>=225200000到Id<225400000这20W的数据；

你也可以通过下面的SQL进行统计：

SELECT IsDone,COUNT(1) FROM tempblog_log 
GROUP BY IsDone ORDER BY IsDone DESC;

 

(七) 创建索引

　　创建保留数据的新表BlogB的时候不要创建不必要的索引，等转移完数据之后再创建回相关的索引；这样做的目的是在插入数据的时候不需要对索引进行维护，并且到转移完之后再创建索引可以让索引更加没有索引碎片；

 

(八) 禁用定时器

　　当TempBlog_Log表不再更新的时候，我们就可以禁用定时器了。因为BlogA表是一直在进数据的，所以当TempBlog_Log不再更新就说明数据已经基本转移完毕了（新增的数据量小于20W），这个时候就可以禁用定时器了。

 

(九) 转移最后数据

　　首先停止对BlogA表的入库操作，通过SQL转移最后一部分的数据到BlogB中，转移完之后修改表名就大功告成了。

六.参考文献（References）

SQL Server 数据库迁移偏方

MYSQL插入处理重复键值的几种方法

 

=========================================================

http://liuer.blog.51cto.com/1519559/1084499

http://blog.chinaunix.net/uid-23354495-id-3031556.html

 

mysqldump--导出工具

 

#导出某个数据库－－结构+数据

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt db_name |gzip -9 > /db_bakup/db_name.gz

 

#导出某个数据库的表－－结构+数据+函数+存储过程

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt -R db_name |gzip -9 > /db_backup/db_name.gz

 

#导出多个数据库

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt --databases db_name1 db_name2 db_name3 |gzip -9 > /db_backup/mul_db.gz 

 

#导出所有的数据库

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt --all-databases |gzip -9 > /db_bak/all_db.gz

 

#导出某个数据库的结构

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt --no-data db_name|gzip -9 > /db_bak/db_name.strcut.gz

 

#导出某个数据库的数据

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt --no-create-info db_name|gzip -9 > /db_bak/db_naem.data.gz

 

#导出某个数据库的某张表

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt db_name tbl_name |gzip -9 > /db_bak/db_name.tal_name.gz

 

# 导出某个数据库的某张表的结构

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt --no-data db_name tal_name | gzip -9 > /db_bak/db_name.tal_name.struct.gz

 

#导出某个数据库的某张表的数据

shell>mysqldump -h192.168.161.124 -uroot -pxxxxxx --opt --no-create-info db_name tbl_name | gzip -9 > /db_bak/db_name.tbl_name.data.gz

 

##--opt==--add-drop-table + --add-locks + --create-options + --disables-keys + --extended-insert + --lock-tables + --quick + --set+charset

##默认使用--opt，--skip-opt禁用--opt参数

--------------------------------------------------------------------------------

 

表结构等已经在目标数据库中存在，不想拷贝数据库过去，MySQL的存储过程导出和导入，Mysqldump工具可以实现，具体用法为：
 ?［root@localhost bin]# mysqldump -uroot -p -hlocalhost -P3306 -n -d -t -R DBName > procedure_name.sql
参数说明：
-n: --no-create-db
-d: --no-data
-t: --no-create-info
-R: --routines Dump stored routines (functions and procedures)

Mysqldump是客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建活装载表的SQL语句:
主要参数介绍：
1.连接选项
-u,--user=name
-p,--password=name
-h,--host=name
-P,--port=#
2.输出内容选项
--add-drop-database
--add-drop-table
-n;--no-create-db
-d;--no-data
-t;--no-create-info
3.输出格式选项
--compact
-c --complete-insert
-T（指定数据表中的数据备份为单纯的数据文件和建表SQL两个文件）
注意：xx.sql建表文件是以linux的root用户创建，而xx.txt文件则是一linux的mysql用户创建，因此这两个文件的存放路径一定要保证mysql用户有读写创建文件的权限。
--fields-terminated-by=name（域分隔符）
--fields-enclosed-by=name（域引用符）
--fields-optionally-enclosed-by=name（域引用可选字符）
--fields-escaped-by=name(转义字符)
4.字符集选项
--default--character-set=xx
5.其他选项
-F --flush-logs（备份前刷新日志）
-l --lock-tables(给所有的表加读锁)

http://www.netingcn.com/mysqldump-procedure.html

mysqldump是mysql自带的一个备份数据库的工具，使用mysqldump可以很方便的把已有的数据库导出来，使用

mysqldump --help
如果MYSQL_HOME/bin没有加入PATH，需要指定去路径

可以看到有很多可选参数。

需要注意：默认情况下，mysqldump并不会导出数据库的存储过程和函数，如果数据库上创建了存储过程且备份是需要备份存储过程，那就需要用参数 -R 来指定，例如：

mysqldump -u root -p passwd -R testDB > db.sql


http://www.2cto.com/database/201211/168571.html

4.如果需要导出mysql里面的函数或者存储过程

      mysqldump -h  hostname -u  username   -ntd -R   databasename  > backupflie.sql  （包括存过过程和函数一次性完整导出）

     

        其中的 -ntd 是表示导出表结构和数据；-R是表示导出函数、存储过程

 

     可以参照mysqldump --help

 

mysql常用导入数据的命令：

      mysql数据库导入导出：

      mysqldump -u 用户名 -p 数据库名 > 数据库名.sql

      如：

   mysqldump -u root   -p         testdb  > testdb.sql (不包括存储过程和函数)

   mysqldump -u root   -p  -R   testdb  > testdb.sql (**包括存储过程和函数**)

 

     MySQL source命令向数据库中导入数据：

     mysql>use testdb;

    mysql>set names  utf8;

     mysql>source /tmp/bdc.sql;

 

http://www.111cn.net/database/mysql/53769.htm

需要注意：默认情况下，mysqldump并不会导出数据库的存储过程和函数，如果数据库上创建了存储过程且备份是需要备份存储过程，那就需要用参数 -R 来指定，

例如：

代码如下	复制代码
root@localhost bin]# mysqldump -uroot -p -hlocalhost -P3306 -n -d -t -R DBName > procedure_name.sql

http://www.linuxidc.com/Linux/2012-07/66140.htm

今天使用mysqldump导出数据库，导入到新库的时候，发现没有存储过程。

默认居然不是完整的啊。

加上“-R”，就好了。

-R, --routines      Dump stored routines (functions and procedures).

现在用：

mysqldump -uroot -ppassword -R dbname> dbname.sql

导入更方便：

mysql -uroot -ppassword dbname< dbname.sql

记得导入之前先建库。

CREATE DATABASE dbname DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

 

 

==================================================

 

-- 大表数据迁移,每天凌晨1点到5点执行,执行间隔时间10分钟，迁移旧数据到历史表。

DELIMITER $$

 

USE `dbx`$$

 

DROP PROCEDURE IF EXISTS `pro_xx`$$

 

CREATE  PROCEDURE `pro_xx`()

BEGIN  

   DECLARE p_oalid INT DEFAULT 0;

   DECLARE STOP INT DEFAULT 0; 

   

   DECLARE cur_oalid CURSOR FOR

         SELECToal.id FROM oal_xxx oal WHERE oal.`ymd` <CONCAT(YEAR(DATE_ADD(NOW(),INTERVAL -1 MONTH)),'-',MONTH(DATE_ADD(NOW(),INTERVAL -1 MONTH )),'-',DAY(DATE_ADD(NOW(),INTERVAL-1 MONTH ))) LIMIT 1000;  

   DECLARE EXIT HANDLER FOR SQLSTATE '02000'  /**包含游标not found*/

   BEGIN

        SET STOP=1;

         INSERTINTO db_logs(log_type,table_name,action_name,log_msg,create_time)

         SELECT1, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 游标执行正常结束！'),NOW();       

   END;

       

   DECLARE EXIT HANDLER FOR SQLEXCEPTION

   BEGIN

         SETSTOP=1;

         INSERTINTO db_logs(log_type,table_name,action_name,log_msg,create_time)

         SELECT2, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 移动执行失败'),NOW();

   END; 

   

   OPEN cur_oalid;      

      

    -- 读取一行数据到变量  

   FETCH cur_oalid INTO p_oalid;

 

     -- 这个就是判断是否游标已经到达了最后  

   WHILE STOP <> 1 DO 

         -- select p_id;

         START TRANSACTION; 

     --   进行数据迁移

             REPLACE INTO oal_xxx_history SELECT oal.*FROM oal_xxx oal WHERE oal.id=p_oalid ;

             DELETE FROM oal_xxx WHERE id=p_oalid;

                          

             -- INSERT INTO t (tid) VALUES (p_tid);

         COMMIT;  

            

           

        -- 读取下一行的数据   

       FETCH cur_oalid INTO p_oalid;

      END WHILE;         

   CLOSE cur_oalid; -- 关闭游标  

 END$$

 

DELIMITER ;

 

mysql 数据迁移用的一个存储过程，需要的朋友可以收藏下。

代码如下:

DELIMITER $$ 
USE `servant_591up`$$ 
DROP PROCEDURE IF EXISTS `sp_move_data`$$ 
CREATE PROCEDURE `sp_move_data`() 
BEGIN 
DECLARE v_exit INT DEFAULT 0; 
DECLARE v_spid BIGINT; 
DECLARE v_id BIGINT; 
DECLARE i INT DEFAULT 0; 
DECLARE c_table INT; 
DECLARE v_UniqueKey VARCHAR(57); 
DECLARE v_TagCatalogId INT; 
DECLARE v_RootCatalogId INT; 
DECLARE v_UserId BIGINT; 
DECLARE v_QuestionId CHAR(36); 
DECLARE v_CorrectCount INT; 
DECLARE v_ErrorCount INT; 
DECLARE v_LastIsCorrect INT; 
DECLARE v_LastAnswerXML TEXT CHARSET utf8; 
DECLARE v_TotalCostTime INT; 
DECLARE v_Reviews VARCHAR(200) CHARSET utf8; 
DECLARE v_AnswerResultCategory INT; 
DECLARE v_LastCostTime INT; 
DECLARE v_LastAnswerTime DATETIME; 
DECLARE v_IsPublic INT; 
DECLARE v_SUBJECT INT; 
DECLARE v_TotalCount INT; 
DECLARE v_AnswerMode SMALLINT(6); 
DECLARE v_ExerciseWeight FLOAT; 
DECLARE c_ids CURSOR FOR SELECT UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight FROM ol_answerresult_56; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit=1; 
OPEN c_ids; 
REPEAT 
FETCH c_ids INTO v_UniqueKey,v_TagCatalogId,v_RootCatalogId,v_UserId,v_QuestionId,v_CorrectCount,v_ErrorCount,v_LastIsCorrect,v_LastAnswerXML,v_TotalCostTime,v_Reviews,v_AnswerResultCategory,v_LastCostTime,v_LastAnswerTime,v_IsPublic,v_SUBJECT,v_TotalCount,v_AnswerMode,v_ExerciseWeight; 
IF v_exit = 0 THEN 
SET @vv_id = v_id; 
SELECT MOD(v_UserId,100) INTO c_table; 
SET @SQL_CONTEXT = 
CONCAT('INSERT INTO new_answerresult_', 
c_table,' 
(UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight)values(', 
'''',v_UniqueKey,'''',',', 
v_TagCatalogId,',', 
v_RootCatalogId,',', 
v_UserId,',', 
'''',v_QuestionId,'''',',', 
v_CorrectCount,',', 
v_ErrorCount,',', 
v_LastIsCorrect,',', 
'''',v_LastAnswerXML,'''',',', 
v_TotalCostTime,',', 
'''',REPLACE(IFNULL(v_Reviews,''),'''',''),'''',',', 
v_AnswerResultCategory,',', 
v_LastCostTime,',', 
'''',v_LastAnswerTime,'''',',', 
v_IsPublic,',', 
v_SUBJECT,',', 
v_TotalCount,',', 
v_AnswerMode,',', 
v_ExerciseWeight,')'); 
PREPARE STMT FROM @SQL_CONTEXT; 
EXECUTE STMT ; 
DEALLOCATE PREPARE STMT; 
END IF; 
SET i=i+1; 
#100 
#IF MOD(i,100)=0 THEN COMMIT; 
#END IF; 
UNTIL v_exit=1 
END REPEAT; 
CLOSE c_ids; 
#COMMIT; 
END$$ 
DELIMITER ;  
 
=================

存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。

--------------------基本语法--------------------

一.创建存储过程
create procedure sp_name()
begin
.........
end

二.调用存储过程
1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程
1.基本语法：
drop procedure sp_name//

2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name
显示某一个mysql存储过程的详细信息

--------------------数据类型及运算符--------------------
一、基本数据类型：
略

二、变量：

自定义变量：DECLARE   a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;

变量分为用户变量和系统变量，系统变量又分为会话和全局级变量

用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理

1、 在mysql客户端使用用户变量

mysql> SELECT 'Hello World' into @x;
mysql> SELECT @x;mysql> SET @y='Goodbye Cruel World';
mysql> select @y;mysql> SET @z=1+2+3;
mysql> select @z;

2、 在存储过程中使用用户变量

mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql> SET @greeting='Hello';
mysql> CALL GreetWorld( );

3、 在存储过程间传递全局范围的用户变量
mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';
mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
mysql> CALL p1( );
mysql> CALL p2( );

 

三、运算符：
1.算术运算符
+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*      乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3; 3
%     取模 SET var6=10%3 ;     1

2.比较运算符
>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=             等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE          简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL True

3.逻辑运算符

4.位运算符
|   或
&   与
<< 左移位
>> 右移位
~   非(单目运算，按位取反)

注释：

mysql存储过程可使用两种风格的注释
双横杠：--

该风格一般用于单行注释
c风格：/* 注释内容 */ 一般用于多行注释

--------------------流程控制--------------------
一、顺序结构
二、分支结构
if
case

三、循环结构
for循环
while循环
loop循环
repeat until循环

注：
区块定义，常用
begin
......
end;
也可以给区块起别名，如：
lable:begin
...........
end lable;
可以用leave lable;跳出区块，执行区块以后的代码

begin和end如同C语言中的{ 和 }。

--------------------输入和输出--------------------

mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUTCreate procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数
该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数
调用时指定，并且可被改变和返回

IN参数例子：
CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
SELECT p_in; --查询输入参数
SET p_in=2; --修改
select p_in;--查看修改后的值
END;

执行结果:
mysql> set @p_in=1
mysql> call sp_demo_in_parameter(@p_in)
略
mysql> select @p_in;
略
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT参数例子
创建:
mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
BEGIN
SELECT p_out;/*查看输出参数*/
SET p_out=2;/*修改参数值*/
SELECT p_out;/*看看有否变化*/
END;

执行结果:
mysql> SET @p_out=1
mysql> CALL sp_demo_out_parameter(@p_out)
略

mysql> SELECT @p_out;
略

INOUT参数例子：
mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;

执行结果：
set @p_inout=1
call sp_demo_inout_parameter(@p_inout) //
略
select @p_inout;
略

 

 

附：函数库
mysql存储过程基本函数包括：字符串类型，数值类型，日期类型

一、字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,… ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

mysql> select substring(’abcd’,0,2);
+———————–+
| substring(’abcd’,0,2) |
+———————–+
|                       |
+———————–+
1 row in set (0.00 sec)mysql> select substring(’abcd’,1,2);
+———————–+
| substring(’abcd’,1,2) |
+———————–+
| ab                    |
+———————–+
1 row in set (0.02 sec)TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

二、数值类型

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如：

(1)默认变为整形值

mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
|           1 |
+————-+
1 row in set (0.00 sec)mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
|           2 |
+————-+
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据

mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
|           1.57 |
+—————-+
1 row in set (0.00 sec)SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方

三、日期类型

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

注：可用在INTERVAL中的类型：DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR
DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; 
其中，datatype为mysql的数据类型，如:INT, FLOAT, DATE, VARCHAR(length)

例：

DECLARE l_int INT unsigned default 4000000; 
DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; 
DECLARE l_date DATE DEFAULT '1999-12-31'; 
DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded';