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

小灰灰 2021-09-21 06:38 202阅读 0赞

### 一、**存储过程** ###

> **       ** **创建存储过程**

**CREATE  PROCEDURE 存储过程名()**

**BEGIN**

**  过程体;**

**END**

> **调用存储过程**

**CALL  存储过程名();**

> **删除存储过程**

**DROP PROCEDURE 存储过程名;**

**注意：**

![20200421164410375.png][]

![20200421164331760.png][]

【例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;
    	}

[20200421164410375.png]: /images/20210920/2b479029b13f4d3f9135abc7b0d62cd1.png
[20200421164331760.png]: /images/20210920/e8807be9652c45c28f10eb46990e8564.png