Oracle 存储进程的运用ITeye - 威尼斯人

Oracle 存储进程的运用ITeye

2019年03月25日12时43分55秒 | 作者: 运华 | 标签: 存储,进程,参数 | 浏览: 2255

本文主要是总结 怎么完成 JDBC调用Oracle的存储进程,从以下状况别离介绍:
[1]、只要输入IN参数,没有输出OUT参数
[2]、既有输入IN参数,也有输出OUT参数,输出是简略值(非列表)
[3]、既有输入IN参数,也有输出OUT参数,输出是列表
[4]、输入输出参数是同一个(IN OUT)
[5]、存储进程中 运用 truncate 切断表中的数据
【准备工作】
  创立一个测验表TMP_MICHAEL ,并刺进数据,SQL如下:
create table TMP_MICHAEL 
 USER_ID VARCHAR2(20), 
 USER_NAME VARCHAR2(10), 
 SALARY NUMBER(8,2), 
 OTHER_INFO VARCHAR2(100) 
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO) 
values (michael, Michael, 5000, http://sjsky.iteye.com); 
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO) 
values (zhangsan, 张三, 10000, null); 
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO) 
values (aoi_sola, 苍井空, 99999.99, twitter account); 
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO) 
values (李四, 李四, 2500, null); 


Oracle jdbc 常量:
private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; 
private final static String DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1521:Ora11g"; 
private final static String DB_NAME = "mytest"; 
private final static String DB_PWd = "111111"; 


[一]、只要输入IN参数,没有输出OUT参数
CREATE OR REPLACE PROCEDURE TEST_MICHAEL_NOOUT(P_USERID IN VARCHAR2, 
 P_USERNAME IN VARCHAR2, 
 P_SALARY IN NUMBER, 
 P_OTHERINFO IN VARCHAR2) IS 
BEGIN 
 INSERT INTO TMP_MICHAEL 
 (USER_ID, USER_NAME, SALARY, OTHER_INFO) 
 VALUES 
 (P_USERID, P_USERNAME, P_SALARY, P_OTHERINFO); 
END TEST_MICHAEL_NOOUT; 


调用代码如下:
 * 测验调用存储进程:无返回值 
 * @blog http://sjsky.iteye.com 
 * @author Michael 
 * @throws Exception 
 public static void testProcNoOut() throws Exception { 
 System.out.println("- start 测验调用存储进程:无返回值"); 
 Connection conn = null; 
 CallableStatement callStmt = null; 
 try { 
 Class.forName(DB_DRIVER); 
 conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd); 
 // 存储进程 TEST_MICHAEL_NOOUT 其实是向数据库刺进一条数据 
 callStmt = conn.prepareCall("{call TEST_MICHAEL_NOOUT(?,?,?,?)}"); 
 // 参数index从1开端,顺次 1,2,3... 
 callStmt.setString(1, "jdbc"); 
 callStmt.setString(2, "JDBC"); 
 callStmt.setDouble(3, 8000.00); 
 callStmt.setString(4, "http://sjsky.iteye.com"); 
 callStmt.execute(); 
 System.out.println("- Test End."); 
 } catch (Exception e) { 
 e.printStackTrace(System.out); 
 } finally { 
 if (null != callStmt) { 
 callStmt.close(); 
 if (null != conn) { 
 conn.close(); 

[二]、既有输入IN参数,也有输出OUT参数,输出是简略值(非列表)
CREATE OR REPLACE PROCEDURE TEST_MICHAEL(P_USERID IN VARCHAR2, 
 P_SALARY IN NUMBER, 
 P_COUNT OUT NUMBER) IS 
 V_SALARY NUMBER := P_SALARY; 
BEGIN 
 IF V_SALARY IS NULL THEN 
 V_SALARY := 0; 
 END IF; 
 IF P_USERID IS NULL THEN 
 SELECT COUNT(*) 
 INTO P_COUNT 
 FROM TMP_MICHAEL T 
 WHERE T.SALARY = V_SALARY; 
 ELSE 
 SELECT COUNT(*) 
 INTO P_COUNT 
 FROM TMP_MICHAEL T 
 WHERE T.SALARY = V_SALARY 
 AND T.USER_ID LIKE % || P_USERID || %; 
 END IF; 
 DBMS_OUTPUT.PUT_LINE(v_count=: || P_COUNT); 
END TEST_MICHAEL; 

调用程序如下
 * 测验调用存储进程:返回值是简略值非列表 
 * @blog http://sjsky.iteye.com 
 * @author Michael 
 * @throws Exception 
 public static void testProcOutSimple() throws Exception { 
 System.out.println("- start 测验调用存储进程:返回值是简略值非列表"); 
 Connection conn = null; 
 CallableStatement stmt = null; 
 try { 
 Class.forName(DB_DRIVER); 
 conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd); 
 stmt = conn.prepareCall("{call TEST_MICHAEL(?,?,?)}"); 
 stmt.setString(1, ""); 
 stmt.setDouble(2, 3000); 
 // out 注册的index 和取值时要对应 
 stmt.registerOutParameter(3, Types.INTEGER); 
 stmt.execute(); 
 // getXxx(index)中的index 需求和上面registerOutParameter的index对应 
 int i = stmt.getInt(3); 
 System.out.println("符号条件的查询成果 count := " + i); 
 System.out.println("- Test End."); 
 } catch (Exception e) { 
 e.printStackTrace(System.out); 
 } finally { 
 if (null != stmt) { 
 stmt.close(); 
 if (null != conn) { 
 conn.close(); 

测验程序便是查询薪水3000以上人员的数量 ,运转成果如下:
- start 测验调用存储进程:返回值是简略值非列表
符号条件的查询成果 count := 4
- Test End.


[三]、既有输入IN参数,也有输出OUT参数,输出是列表
  首要需求创立PACKAGE TEST_PKG_CURSOR 的SQL如下:
首要需求创立PACKAGE TEST_PKG_CURSOR 的SQL如下:
Sql代码 
CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS 
  Author : MICHAEL http://sjsky.iteye.com 
 TYPE TEST_CURSOR IS REF CURSOR; 
END TEST_PKG_CURSOR; 
再创立存储进程 TEST_P_OUTRS 的SQL如下:
Sql代码 
CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER, 
 P_OUTRS OUT TEST_PKG_CURSOR.TEST_CURSOR) IS 
 V_SALARY NUMBER := P_SALARY; 
BEGIN 
 IF P_SALARY IS NULL THEN 
 V_SALARY := 0; 
 END IF; 
 OPEN P_OUTRS FOR 
 SELECT * FROM TMP_MICHAEL T WHERE T.SALARY V_SALARY; 
END TEST_P_OUTRS; 


调用存储进程的代码如下:
 * 测验调用存储进程:有返回值且返回值为列表的 
 * @blog http://sjsky.iteye.com 
 * @author Michael 
 * @throws Exception 
 public static void testProcOutRs() throws Exception { 
 System.out.println("- start 测验调用存储进程:有返回值且返回值为列表的"); 
 Connection conn = null; 
 CallableStatement stmt = null; 
 ResultSet rs = null; 
 try { 
 Class.forName(DB_DRIVER); 
 conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd); 
 stmt = conn.prepareCall("{call TEST_P_OUTRS(?,?)}"); 
 stmt.setDouble(1, 3000); 
 stmt.registerOutParameter(2, OracleTypes.CURSOR); 
 stmt.execute(); 
 // getXxx(index)中的index 需求和上面registerOutParameter的index对应 
 rs = (ResultSet) stmt.getObject(2); 
 // 获取列名及类型 
 int colunmCount = rs.getMetaData().getColumnCount(); 
 String[] colNameArr = new String[colunmCount]; 
 String[] colTypeArr = new String[colunmCount]; 
 for (int i = 0; i colunmCount; i++) { 
 colNameArr[i] = rs.getMetaData().getColumnName(i + 1); 
 colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1); 
 System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")" 
 + " | "); 
 System.out.println(); 
 while (rs.next()) { 
 StringBuffer sb = new StringBuffer(); 
 for (int i = 0; i colunmCount; i++) { 
 sb.append(rs.getString(i + 1) + " | "); 
 System.out.println(sb); 
 System.out.println("- Test Proc Out is ResultSet end. "); 
 } catch (Exception e) { 
 e.printStackTrace(System.out); 
 } finally { 
 if (null != rs) { 
 rs.close(); 
 if (null != stmt) { 
 stmt.close(); 
 if (null != conn) { 
 conn.close(); 
 运转成果如下:
- start 测验调用存储进程:有返回值且返回值为列表的 
USER_ID(VARCHAR2) | USER_NAME(VARCHAR2) | SALARY(NUMBER) | OTHER_INFO(VARCHAR2) | 
michael | Michael | 5000 | null | 
zhangsan | 张三 | 10000 | null | 
aoi_sola | 苍井空 | 99999.99 | null | 
jdbc | JDBC | 8000 | http://sjsky.iteye.com | 
- Test Proc Out is ResultSet end.


[四]、输入输出参数是同一个(IN OUT)
CREATE OR REPLACE PROCEDURE TEST_P_INOUT(P_USERID IN VARCHAR2, 
 P_NUM IN OUT NUMBER) IS 
 V_COUNT NUMBER; 
 V_SALARY NUMBER := P_NUM; 
BEGIN 
 IF V_SALARY IS NULL THEN 
 V_SALARY := 0; 
 END IF; 
 SELECT COUNT(*) 
 INTO V_COUNT 
 FROM TMP_MICHAEL 
 WHERE USER_ID LIKE % || P_USERID || % 
 AND SALARY = V_SALARY; 
 P_NUM := V_COUNT; 
END TEST_P_INOUT; 


调用存储进程的代码:
 * 测验调用存储进程: INOUT同一个参数: 
 * @blog http://sjsky.iteye.com 
 * @author Michael 
 * @throws Exception 
 public static void testProcInOut() throws Exception { 
 System.out.println("- start 测验调用存储进程:INOUT同一个参数"); 
 Connection conn = null; 
 CallableStatement stmt = null; 
 try { 
 Class.forName(DB_DRIVER); 
 conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd); 
 stmt = conn.prepareCall("{call TEST_P_INOUT(?,?)}"); 
 stmt.setString(1, "michael"); 
 stmt.setDouble(2, 3000); 
 // 留意此次注册out 的index 和上面的in 参数index 相同 
 stmt.registerOutParameter(2, Types.INTEGER); 
 stmt.execute(); 
 // getXxx(index)中的index 需求和上面registerOutParameter的index对应 
 int count = stmt.getInt(2); 
 System.out.println("符号条件的查询成果 count := " + count); 
 System.out.println("- Test End."); 
 } catch (Exception e) { 
 e.printStackTrace(System.out); 
 } finally { 
 if (null != stmt) { 
 stmt.close(); 
 if (null != conn) { 
 conn.close(); 
 运转成果如下:
- start 测验调用存储进程:INOUT同一个参数 
符号条件的查询成果 count := 1 
- Test End.


[五] 存储进程中运用 truncate  清空表中的数据
create or replace procedure PROC_INSERT_BLDAREN(rownums in number) is
begin
 EXECUTE IMMEDIATE TRUNCATE TABLE BI_BAOLIAO_DAREN;
 insert into BI_BAOLIAO_DAREN (ID,USERID,USERNAME,BAOLIAONUM,CREDITS) select bi_baoliao_sequence.nextval,bl.* from (select b.userid,b.username,count(b.id),sum(b.credits) credits from bi_baoliao b group by b.userid,b.username order by credits desc) bl where rownum =rownums;
end PROC_INSERT_BLDAREN;

java 调用
* 运用 truncate 先清空表中的数据
* 然后 刺进数据
public static boolean updateData1(int rownum){
 boolean result=true;
 Connection conn=null;
 CallableStatement cs=null;
 try {
 Date stime=new Date();
 conn=DBConnection.getConnection();
 cs=conn.prepareCall("{call PROC_INSERT_BLDAREN(?)}");
 cs.setInt(1, rownum);
 result=cs.execute();
 Date etime=new Date();
 System.out.println(etime.getTime()-stime.getTime());
 }catch(Exception e){
 e.printStackTrace();
 }finally{
 DBConnection.cleanUp(null, null, cs, null);
 return result;
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章