存儲過程：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();

}

}