Mysql容易存储过程入门示例与Java调用

     昨天看了一篇介绍Mysql存储过程博客，链接如下：

     http://my.oschina.net/u/1264926/blog/199831

     我试着运行了下，一直报错，找了很久才发现Mysql存储过程赋值要用SET 变量名 = 表达式值，很久没有Mysql存储过程，好多东西都忘光了，而是写了本篇博文备忘，我使用的数据库版本是Mysql 5.6.14,使用了Navicat Premium图形界面，首先是我参考的链接：

http://www.cnblogs.com/jevo/p/3271359.html http://phpzf.blog.51cto.com/3011675/793775

欢迎大家阅读《Mysql容易存储过程入门示例与Java调用》,跪求各位点评，by 搞代码

    下面开始介绍Mysql存储过程，语法之类的我就不写了，请自行谷歌，我的存储过程是完成1到limit之间的累加和，所以要用到循环，Mysql存储过程常用的循环语句有:While,Loop,Repeat，下面一一介绍怎么写：

    (一)首先是使用While循环(WHILE……DO……END WHILE)

create procedure proc_mysql_getsum_bywhile(in v_limit int,out sum int) begin    declare i int default 0;    set sum=0;    while i<v_limit do       begin       set sum=sum+i;       set i=i+1;       end;    end while;    /**select sum;**/ end

    这里啰嗦一句，Mysql里面没有类似Oracle的DBMS_OUT.PUT_LINE之类的打印语句，想打印结果，请用select 变量。

   While循环测试：

set @limit=100; set @out=0; call proc_mysql_getsum_bywhile(@limit,@out); select @out

   (二)repeat 循环(REPEAT……END REPEAT)

create procedure proc_mysql_getsum_byrepeat(in v_limit int,out sum int) begin    declare i int default 0;    set sum=0;    repeat        begin       set sum=sum+i;       set i=i+1;        end;       until i>v_limit    end repeat;    /**select sum;**/ end;

    Repeat测试：

set @limit=100; set @out=0; call proc_mysql_getsum_byrepeat(@limit,@out); select @out

    (三)loop循环

create procedure proc_mysql_getsum_byloop(in v_limit int,out sum int) begin    declare i int default 0;    set sum=0;    loop_label:loop         begin         set sum=sum+i;         set i=i+1;       if i>v_limit then              leave loop_label;         end if;         end;    end loop;    /**select sum;**/ end;

    loop 测试:

set @limit=100; set @out=0; call proc_mysql_getsum_byloop(@limit,@out); select @out

    上面介绍的是一个简单的带输入输出的存储过程，下面在介绍一个getUserById的存储过程，和上面的差不多。

create procedure proc_mysql_inout_test(in v_id int,out username varchar(20)) begin    select username into username from user_t2 where id = v_id;     /**select username;**/ end;

    in out参数测试：

    Navicat查询界面测试：

call proc_mysql_inout_test(2,@out); select @out

    返回值很奇怪结果是Blob。

  

    Navicat命令行下测试：返回的是gbk编码的字符串，而直接select * from user_t2;无乱码，如下所示：

   

    cmd 命令行下测试 无乱码，如下所示：

   

    如果想在存储过程中执行sql语句该怎么写呢？请看示例：

    测试新建表并填充值：

drop PROCEDURE proc_mysql_createtb_insert_data; CREATE PROCEDURE proc_mysql_createtb_insert_data(IN loop_times INT)  BEGIN   DECLARE var INT DEFAULT 0;   PREPARE MSQL FROM 'CREATE TABLE IF NOT EXISTS mysql_employee (id INT (10)  NOT NULL AUTO_INCREMENT,empname VARCHAR (16) NOT NULL COMMENT ''名字'',hiredate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id)) ENGINE = INNODB DEFAULT CHARSET = utf8'; EXECUTE MSQL;   deallocate prepare MSQL;  WHILE var<loop_times DO   SET var=var+1;    insert into mysql_employee(empname) values    ((select substr(uuid(),1,15) from dual)); END WHILE;   END 

    测试
   

call proc_mysql_createtb_insert_data(10); select * from mysql_employee; 

   Mysql存储过程想要修改时只能先删除在新建，删除方法为：

drop procedure proc_mysql_getsum_bywhile

   查看某个数据库下面的存储过程方法为：

select name from mysql.proc where db='test'

   如果想和Oracle存储过程一样返回游标，怎么写呢，很遗憾，我所知道的是Mysql不支持Out ref_cur cursor之类的写法的，你可以在存储过程中新建临时表，结束时候删除临时表，方法请参考上面的新建表示例。

   另一种方法是直接select 内容，不写返回结果，如下所示：

CREATE PROCEDURE proc_mysql_return_cursor_method()  begin select * from user_t2; end;

    测试方法为：

call proc_mysql_return_cursor_method();

    下面我简单介绍下Java中怎么调用Mysql存储过程，如果不感兴趣可以不用往下看了。

    首先是公共方法：

public Connection getMysqlConnection() {   String driver = "com.mysql.jdbc.Driver";   String url = "jdbc:mysql://localhost:3306/test";// 要操作的数据库名称   String username = "root";// 数据库用户名   String password = "123";// 密码   return getConnection(driver, url, username, password);  }   public Connection getConnection(String driver, String url, String userName,    String passwd) {   Connection conn = null;   try {    Class.forName(driver);    conn = DriverManager.getConnection(url, userName, passwd);   } catch (Exception e) {    e.printStackTrace();   }   return conn;  } 

   我就以我写的while循环为例，输入int参数，输出int参数：

public void testMysqlProcedureRtnInt(Connection con, CallableStatement cs,    int limit) throws Exception {   cs = con.prepareCall("{call proc_mysql_getsum_bywhile(?,?)}");   // 设置参数   cs.setInt(1, limit);   // 注册输出参数   cs.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);   // 执行过程   cs.execute();   // 获取结果   int result = cs.getInt(2);   System.out.println("结果为:" + result);  }

   输入int,输出varchar类型方法类似：

public void testMysqlProcedureRtnVarchar(Connection con,    CallableStatement cs, int id) throws Exception {   cs = con.prepareCall("{call proc_mysql_inout_test(?,?)}");   // 设置参数   cs.setInt(1, id);   // 注册输出参数   cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);   // 执行过程   cs.execute();   // 获取结果   String result = cs.getString(2);   System.out.println("结果为:" + result);  }

    来看下返回类似游标类型的调用：

public void testMysqlProcedureRtnCursor(Connection con,    CallableStatement cs, ResultSet rs) throws Exception {   cs = con.prepareCall("{call proc_mysql_return_cursor_method()}");   // 执行过程   rs = cs.executeQuery();   System.out.println("id" + "/t" + "username" + "/t" + "passwd");   while (rs.next()) {    System.out.println(rs.getInt(1) + "/t" + rs.getString(2) + "/t"      + rs.getString(3));   }  }

    很简单吧。

    上面的介绍到目前为知该结束了，本文系原创，转载请注明出处，谢谢。

     全文完。