一、存储过程
创建存储过程
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;
}