java—数据存储过程 （54）

 存储过程：procedure：就是一段可执行程序。这个程序运行在数据中。

       Begin = {

End = }

If

Else

也可以接收参数。

1 定义一个procedure:

CREATE PROCEDURE proc1()
BEGIN  ――{
  SELECT * FROM users;
END // ―― }

DELIMITER ;


CALL proc1();

2 定义一个接收参数的:

DELIMITER $$
CREATE PROCEDURE proc2(IN _id VARCHAR(32),IN _nm VARCHAR(30))
BEGIN
  INSERT INTO users(id,NAME) VALUES(_id,_nm);
END $$

CALL proc2('U003','Rose');

3 定义一个返回类型的参数：

DELIMITER $$
CREATE PROCEDURE proc3(IN _id VARCHAR(32),IN _nm VARCHAR(30),OUT _size INT)
BEGIN
  INSERT INTO users(id,NAME) VALUES(_id,_nm);
  SELECT COUNT(1) INTO _size FROM users;
  IF(_size=10) THEN 
    INSERT INTO users VALUES('U0000','ddd','ddddd');
  END IF;
END $$


CALL proc3('U002882','Rose',@wj);
SELECT @wj;

在Java代码中调用Procedure

package cn.hx.demo;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Types;

import org.junit.Test;

import cn.itcast.utils.DataSourceUtils;

public class ProceDemo {

    @Test

    public void proc1() throws Exception{

       //dbutils不提供调用存储过程的能力

        Connection con = DataSourceUtils.getDatasSource().getConnection();

       //获取调用过程的对象

       CallableStatement cs =

              con.prepareCall("{call proc1()}");

       //执行

       boolean boo = cs.execute();//如果返回true,指最后一句执行的是select语句

       if(boo){

           ResultSet rs = cs.getResultSet();

           while(rs.next()){

              System.err.println(rs.getString("name"));

           }

       }

       con.close();

    }

   

    @Test

    public void proc2() throws Exception{

       Connection con = DataSourceUtils.getDatasSource().getConnection();

       //获取调用过程的对象

       CallableStatement cs =

               con.prepareCall("{call proc2(?,?)}");

       cs.setString(1,"UAAA");

       cs.setString(2, "11");

       boolean boo = cs.execute();

       System.err.println(boo);

       con.close();

    }

   

    @Test

    public void proc3() throws Exception{

       Connection con = DataSourceUtils.getDatasSource().getConnection();

       //获取调用过程的对象

       CallableStatement cs =

              con.prepareCall("{call proc5(?,?,?)}");

       cs.setString(1,"UBDDB");

       cs.setString(2, "张三");

       cs.registerOutParameter(3,Types.INTEGER);//--int,

       boolean boo = cs.execute();

       System.err.println(">>:"+boo);//true

       //从call中获取返回的值

       int size = cs.getInt(3);

       System.err.println("行数:"+size);

       if(boo){

           ResultSet rs= cs.getResultSet();

           rs.next();

           int ss = rs.getInt(1);

           System.err.println("sss:"+ss);

       }

       con.close();

    }

    @Test

    public void proc6() throws Exception{

       Connection con = DataSourceUtils.getDatasSource().getConnection();

       //获取调用过程的对象

       CallableStatement cs =

              con.prepareCall("{call proc6(?,?,?,?)}");

       cs.setString(1,"UBafadsB");

       cs.setString(2, "张三");

       cs.registerOutParameter(3,Types.INTEGER);//--int,

       cs.registerOutParameter(4, Types.INTEGER);

       boolean boo = cs.execute();

       System.err.println(">>:"+boo);//faluse

       //从call中获取返回的值

       int size = cs.getInt(3);

       int _s = cs.getInt(4);

       System.err.println("行数:"+size+","+_s);

       con.close();

    }

}