简简单单储存过程

 摘要：本文主要讲解了存储过程的创建、调用、以及游标的使用 ，相信掌握了游标      会对你有不错的帮助，有不足之处还请指教

 

导航 ： 一、存储过程的创建及调用

            二 、游标的使用

            三、  示例

            四、补充

 

说明：

        1、用到的两个数据表：

 

       from_data  
      

 

       to_data

      
        

       2、示例需求 ： 将表from_data 的select结果集循环插入到表 to_data；

 

                              伪代码：   while 循环 select id ,name from_data

 

                                          insert into to_data(id,name) value(from_data.id,from_data.name)

 

                                end

 

        3、环境： mysql

 

 

 

 一、存储过程的创建及调用

 

           我们创建一个名叫 add_test的存储过程

 

       1 、检查是否有 add_test

Sql代码  

1. drop procedure if exists add_test;    

       2、创建

 

 

Sql代码  

1. create procedure add_test()     
2.    (     
3.    #[in|out|inout] 参数 datatype   
4.      
5.    
6.      a int;  
7.      b int;  
8.    )     
9.    begin     
10.    #SQL 语句;  
11.   
12.     
13.       
14.    end;    

 

   3、调用

 

Sql代码  

1. call add_test(1,2  
2.   
3. );  

   

 

   以上就是基本的创建方法，注意已下几点：

        1 、在建立和调用时，add_test后面的“（）”是必须的

        2、MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”,并且参数不能指定默认值 。

        3、包含多条 SQL 语句时，需要 begin end 关键字，在begin end里面的每条语句的末尾，都要加上分号 “;”

        4、在begin end里面声明变量，使用关键字 DECLARE ，如：

 

Sql代码  

1. begin   
2.   
3.   #声明一个name变量，类型是varchar(记得分号)  
4.   name varchar(32);     
5. end;  

 

 

二 、游标的使用

 

         1、定义游标

Sql代码  

1. /*  
2.     定义游标的关键字：CURSOR。  
3.     定义游标cursor_name，  
4.     游标cursor_name当前指针的记录  
5.     是一个表from_data的多行结果集  
6.  */  
7.   DECLARE cursor_name CURSOR FOR select id,name  
8.   
9. from from_data;  

 

 

 

       2、打开游标

 

Sql代码  

1. #关键字：OPEN  
2. OPEN cursor_name;   

 

   3、 获取游标

 

Sql代码  

1. #声明两个变量  
2.   DECLARE a int  
3.   
4. ;  
5.   DECLARE b varchar(32)  
6.   
7. ;  
8.   
9. /*  
10.    FETCH 获取游标当前指针的记录，并传给指定变量 a 、b  
11. */  
12. FETCH cursor_name INTO a,b;  

 

   注意：（1、此处很重要，我们在后面的循环例子中会详细讲解如何用，

           （2、注意变量数必须与MySQL游标返回的字段数以及类型一致,请看2，3步的标红处，

           a的类型对应 id,b类型对应name

 

  4、关闭游标

 

Sql代码  

1. CLOSE cursor_name ;  

 

 

    以上就是游标的常见使用方法，关键的部分我已在每一步中说明，就不在多说了，现在我们看下例子：

 

三、示例

 

Sql代码  

1. drop procedure if exists add_test;  
2. # 创建存储过程 add_test  
3.   
4.   
5. CREATE PROCEDURE add_test()  
6.   
7.     BEGIN  
8.            #定义 变量  
9.   
10.   
11.            DECLARE a int;  
12.            DECLARE b VARCHAR(30);  
13.              
14.            #此变可有可无，为了给个该存储函数执行成功后给个提示，运行下便知道  
15.   
16.   
17.            DECLARE str VARCHAR(300);  
18.            DECLARE x int;  
19.              
20.            #这个用于处理游标到达最后一行的情况  
21.   
22.      
23.            DECLARE s int default 0;  
24.              
25.            #声明游标cursor_name（cursor_name是个多行结果集）  
26.   
27.   
28.            DECLARE cursor_name CURSOR FOR select id ,name from from_data;  
29.              
30.            #设置一个终止标记   
31.   
32.     
33.            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;  
34.   
35.   
36.             set str = "--";  
37.                 #打开游标  
38.   
39.   
40.                 OPEN cursor_name;  
41.                       
42.                     #获取游标当前指针的记录，读取一行数据并传给变量a,b  
43.   
44.   
45.                     fetch  cursor_name into a,b;  
46.                     #开始循环，判断是否游标已经到达了最后作为循环条件   
47.   
48.   
49.                     while s <> 1 do  
50.                             set str =  concat(str,x);  
51.   
52.                             insert into to_data(id,name) values(a,b);  
53.                             #读取下一行的数据  
54.   
55.     
56.                             fetch  cursor_name into a,b;  
57.                       
58.                     end while;  
59.                    
60.                  #关闭游标  
61.   
62.   
63.                  CLOSE cursor_name ;  
64.               
65.             select str;  
66.                
67.     #语句执行结束  
68.   
69.   
70.     END;  
71.       
72.     #调用存储函数add_test  
73.   
74.   
75.     CALL add_test()  

   

 

 

   四、补充-关于ssh上运行

 

         由于mysql的解释器默认情况下，delimiter是分号; 。在命令行客户端中，如果有一行命令以分号结束，
     那么回车后，mysql将会执行该命令 ，我们在此处有很多分号，这样很是不方便， 这种情况下，我只需

     执行如下命令：

                   执行delimiter //

     即可把分号结束换成//结束，然后在换回

                  delimiter ;