数据库存储过程和参数（实现分页小功能以及在java中应用）

一、存储过程

        创建存储过程

CREATE  PROCEDURE 存储过程名()

BEGIN

  过程体;

END

调用存储过程

CALL  存储过程名();

删除存储过程

DROP PROCEDURE 存储过程名;

注意：

     【例3-37】创建存储过程emp_p，在emp表中查询职工编号为1003员工的姓名和工作。

DELIMITER @@
DROP PROCEDURE IF EXISTS emp_p@@#删除存储过程
CREATE PROCEDURE emp_p()
BEGIN
 SELECT  ename,Job FROM  emp   
    WHERE  empno=1003;
END@@
DELIMITER;
CALL emp_p();#调用存储过程

二、存储过程的参数

带参数的存储过程创建语法格式如下：

CREATE PROCEDURE 存储过程名(

 [ IN | OUT | INOUT]  参数1  数据类型,

  [ IN | OUT | INOUT]  参数2  数据类型,……

)

BEGIN

    过程体；

END

1．IN参数

DELIMITER @@
DROP PROCEDURE IF EXISTS dept_p1@@
 CREATE  PROCEDURE dept_p1(
  IN p_deptno  DECIMAL(2,0),
  IN p_dname   VARCHAR(255),
  IN p_loc     VARCHAR(255)
 ) 
 BEGIN
  INSERT INTO dept(deptno,dname,loc)
    VALUES(p_deptno,p_dname,p_loc);
 END@@
 
DELIMITER ;
CALL dept_p1(50,'HR','CHINA');
SELECT * FROM dept  WHERE  deptno=50;
DESC dept;

2．OUT参数

【例3-40】创建存储过程dept_p2，该过程根据提供的部门编号，返回部门的名称和地址。

DELIMITER @@
DROP PROCEDURE IF EXISTS dept_p2@@
CREATE PROCEDURE dept_p2(
  IN  i_no   DECIMAL(2,0),#输入参数
  OUT o_name VARCHAR(14),#输出参数
  OUT o_loc  VARCHAR(13)#输出参数
 )
 BEGIN
  SELECT dname,loc  INTO o_name,o_loc FROM dept  
WHERE deptno=i_no;
 END@@

DELIMITER ;
CALL dept_p2(50,@v_dname,@v_loc);
SELECT @v_dname 名称,@v_loc 地址;

3．INOUT参数

【例3-41】使用INOUT参数实现两个数的交换。

DELIMITER @@
DROP PROCEDURE IF EXISTS swap@@
 CREATE PROCEDURE swap(
  INOUT p_num1  int,#定义INOUT参数
  INOUT p_num2  int
  ) 
  BEGIN
   DECLARE var_temp int;
   SET var_temp=p_num1;
   SET p_num1=p_num2;
   SET p_num2=var_temp;
  END@@

DELIMITER ;
SET @v_num1=1;#定义一个变量，并给它初值
SET @v_num2=2;
CALL swap(@v_num1,@v_num2);
SELECT @v_num1,@v_num2;

实现分页功能：

根据商品名称关键字查询商品，并分页显示：
DROP PROCEDURE IF EXISTS proc_find_Pizza;
CREATE PROCEDURE proc_find_Pizza(
 IN page_index INT,
 IN page_size INT,
 IN pizzaname VARCHAR(100),
 OUT total_count INT,
 OUT total_page INT
)
BEGIN
  DECLARE begin_no INT;
	SET begin_no = (page_index-1)*page_size;
	SELECT * FROM pizza WHERE cname like pizzaname and cid>=(SELECT cid FROM pizza WHERE cname like pizzaname ORDER BY cid ASC LIMIT begin_no,1) ORDER BY cid ASC LIMIT page_size;
	SELECT COUNT(1) INTO total_count FROM pizza WHERE cname like pizzaname;
	SET total_page=FLOOR((total_count+page_size-1)/page_size);
END;
CALL proc_find_Pizza(2,5,'%披萨%',@total_count,@total_page);

在Java中调用：

public Collection<Pizza> findByName(String name,int page, int num){
		Connection conn = null; // 数据库连接对象
		CallableStatement clbStmt = null; // CallableStatement对象
		ResultSet rs = null; // 结果集对象
		ArrayList<Pizza> pizzas = new ArrayList<Pizza>();
		try {
			// 获取数据库连接
			conn = JdbcUtils.getConnection();
			// 创建CallableStatement对象
			clbStmt = conn.prepareCall("{CALL proc_find_Pizza(?,?,?,?,?)}");
			clbStmt.setInt(1, page); // 查询第page页数据
			clbStmt.setInt(2, num); // 每页num条数据
			clbStmt.setString(3, "%"+name+"%"); // 名称
			// 输出参数
			clbStmt.registerOutParameter(4, Types.INTEGER);// 输出整形
			clbStmt.registerOutParameter(5, Types.INTEGER);
			// 执行调用存储过程，并获取结果集
			rs = clbStmt.executeQuery();
			// 循环遍历结果集
			while (rs.next()) {
				Pizza pizza =new Pizza();
				pizza.setId(rs.getInt("cid"));
				pizza.setName(rs.getString("cname"));
				pizza.setSpecification(rs.getString("specification"));
				pizza.setKind(rs.getString("kind"));
				pizza.setPrice(rs.getDouble("price"));
				pizza.setSale(rs.getInt("sale"));
				pizzas.add(pizza);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
			// 关闭数据库操作对象
			try {
				rs.close();
				JdbcUtils.closeDB(conn, clbStmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return pizzas;
	}