Oracle資料庫基本操作 （五） —— 使用java調用存儲過程

 一、環境準備

　登錄Oracle資料庫scott賬號，利用emp進行操作。

1、創建 proc_getyearsal 存儲過程

1 -- 獲取指定員工年薪
2 create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
3 is
4        
5 begin
6    select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
7 end;

 2、創建 proc_gettemps 存儲過程（游標）

1 create or replace procedure proc_gettemps(vemps out sys_refcursor)
2 is
3 BEGIN
4 open vemps for select * from emp where deptno = 20;
5 end;

3、導入資料庫驅動包 —— ojdbc14.jar

 二、java代碼示例

 1 package com.pri.test;
 2 
 3 public class TestProcedure {
 4 
 5 　　/*
 6 　　　　java調用存儲過程模板（一）
 7 　　　　獲取單值操作
 8 　　*/
 9     @Test
10     public void test01() throws Exception {
11         //1.註冊驅動
12         Class.forName("oracle.jdbc.driver.OracleDriver");
13         //2.獲取連接
14         String url = "jdbc:oracle:thin:@10.211.55.29:1521/orcl";
15         String user = "zhangsan";
16         String password = "zs123";
17         Connection conn = DriverManager.getConnection(url, user, password);
18         //3.獲取執行SQL的對象
19         String sql = "{call proc_getyearsal(?,?)}";
20         CallableStatement callableStatement = conn.prepareCall(sql);
21         //3.1 設置輸出參數
22         callableStatement.setInt(1,7369);
23         //3.2 註冊輸出類型
24         callableStatement.registerOutParameter(2, Types.DOUBLE);
25         //4.執行SQL
26         callableStatement.execute();
27         //5.執行結果
28         double yearsal = callableStatement.getDouble(2);
29         System.out.println("年薪:"+yearsal);
30         //6.釋放資源
31         callableStatement.close();
32         conn.close();
33     }
34 　　
35 　　/*
36 　　   java調用存儲過程模板（二）
37 　　　　多行記錄（游標）操作
38 　　*/
39     @Test
40     public void test03() throws Exception {
41         //1.註冊驅動
42         Class.forName("oracle.jdbc.driver.OracleDriver");
43         //2.獲取連接
44         String url = "jdbc:oracle:thin:@10.211.55.29:1521/orcl";
45         String user = "zhangsan";
46         String password = "zs123";
47         Connection conn = DriverManager.getConnection(url, user, password);
48         //3.獲取執行SQL的對象
49         String sql = "{call proc_gettemps(?)}";
50         CallableStatement callableStatement = conn.prepareCall(sql);
51         //3.1 註冊輸出類型
52         callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
53         //4.執行SQL
54         callableStatement.execute();
55         //5.獲取結果
56         System.out.println(callableStatement.getClass().getName());
57 //        T4CCallableStatent call2 = () callableStatement;
58         OracleCallableStatement call2 = (OracleCallableStatement) callableStatement;
59         ResultSet rs = call2.getCursor(1);
60 
61         while(rs.next()){
62             System.out.println(rs.getObject("empno"));
63             System.out.println(rs.getObject("ename"));
64             System.out.println(rs.getObject("sal"));
65             System.out.println("------------------------");
66         }
67         //6.釋放資源
68         rs.close();
69         callableStatement.close();
70         conn.close();
71     }
72 
73 }