1、在Oracle数据库创建一个存储过程,可直接复制,代码如下:
/**
*存储过程名称: Cal_Empsums
*输入参数:Pn_Param1 NUMBER
* Pn_Deptno NUMBER
*输出参数:Xn_Empsals NUMBER
* Xn_Empsums NUMBER
* 说明:输入判断值Pn_Param1和部门号Pn_Deptno来输出部门的员工数量或员工工资的总值
**/
CREATE OR REPLACE PROCEDURE Cal_Empsums(Pn_Param1 IN NUMBER
,Pn_Deptno IN Scott.Dept.Deptno%TYPE
,Xn_Empsals OUT Scott.Emp.Sal%TYPE
,Xn_Empsums OUT NUMBER) IS
Ln_Empsums NUMBER;
Ln_Empsals Scott.Emp.Sal%TYPE;
BEGIN
--(1)判断要是Pn_Param1 为1就返回员工数量
IF Pn_Param1 = 1 THEN
SELECT COUNT(e.Empno)
INTO Ln_Empsums
FROM Scott.Emp e
,Scott.Dept d
WHERE e.Deptno = d.Deptno
AND d.Deptno = Pn_Deptno
GROUP BY d.Dname;
Xn_Empsums := Ln_Empsums;
--(2)判断要是Pn_Param1为2就返回员工工资总值
ELSIF Pn_Param1 = 2 THEN
SELECT SUM(e.Sal)
INTO Ln_Empsals
FROM Scott.Emp e
,Scott.Dept d
WHERE e.Deptno = d.Deptno
AND d.Deptno = Pn_Deptno
GROUP BY d.Dname;
Xn_Empsals := Ln_Empsals;
--(3)判断要是Pn_Param1为其他值就输出为错误
ELSE
Dbms_Output.Put_Line('parameter is wrong!');
END IF;
END;
2、在Eclipse里面创建一个类DeptDBPkg
代码如下,里面已附上说明:
package com.sie.train.pkg;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DeptDBPkg {
public static void main(String[] args) {
try {
//(1)加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//(2)建立连接通道
String url = "jdbc:oracle:thin:@61.145.96.125:1522:DEMO";
String user = "apps";
String password = "apps";
Connection conn = DriverManager.getConnection(url, user, password);
//========使用存储过程的步骤===========
//(3)定义调用存储过程的语句
String sql = "call Cal_Empsums(?,?,?,?)";
//(4)创建CallStatement
CallableStatement cstmt = conn.prepareCall(sql);
//(5)设置输入参数
cstmt.setInt(1, 1);
cstmt.setInt(2, 20);
//(6)注册输出参数,目的是为了与占位符匹配
cstmt.registerOutParameter(3, java.sql.Types.DECIMAL);
cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
//(7)执行调用存储过程
cstmt.executeQuery();
//(8)接收存储过程返回结果
BigDecimal empSals = cstmt.getBigDecimal(3);
int empSums = cstmt.getInt(4);
//(9)打印返回的结果
System.out.println("empSals:" + empSals + "\t" + "empSums:"
+ empSums);
cstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3、结果