PACKAGE分為HEADER和BODY部分。
HEADER
1 CREATE OR REPLACE PACKAGE PACKAGE_NAME IS
2
3 --PUBLIC PROPERTY
4 TYPE LISTDATA_CURSOR IS REF CURSOR;
5
6 --PUBLIC FUNCTION
7 FUNCTION FUNCTION_NAME
8 (
9 --Out Parameter
10 PO_varErrCd OUT VARCHAR2, --例: TABLENAME.COLUMN%TYPE
11 PO_varErrMsg OUT VARCHAR2,
12 --In Parameter
13 PI_varName IN VARCHAR2
14 )
15 RETURN LISTDATA_CURSOR; --return type,例: CURSOR
16
17 END;
18 /
BODY
1 CREATE OR REPLACE PACKAGE BODY PACKAGE_NAME IS
2 FUNCTION FUNCTION_NAME
3 (
4 PO_varErrCd OUT VARCHAR2,
5 PO_varErrMsg OUT VARCHAR2,
6 PI_varName IN VARCHAR2
7 )
8 RETURN LISTDATA_CURSOR IS
9
10 TYPE TYP_Cur IS REF CURSOR;
11 curGetList TYP_Cur;
12
13 BEGIN
14
15 OPEN curGetList FOR --open CURSOR
16 SELECT
17 TABLE_NAME.COLUMN_NAME1,
18 TABLE_NAME.COLUMN_NAME2
19 FROM
20 TABLE_NAME
21 WHERE
22 TABLE_NAME.COLUMN_NAME3 = PI_varName
23 RETURN curGetList; --return CURSOR，相當于返回了一個結果集
24
25 EXCEPTION
26 WHEN OTHERS THEN
27 PO_varErrCd := SQLCODE;
28 PO_varErrMsg := SQLERRM;
29 IF curGetList%ISOPEN THEN
30 CLOSE curGetList;
31 END IF;
32 RETURN NULL;
33 END FUNCTION_NAME;
34
35 END;
36 /
用OJDBC調用的方法
1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.Types;
4
5 import oracle.jdbc.OracleTypes;
6 import oracle.jdbc.driver.OracleCallableStatement;
7 import oracle.jdbc.driver.OracleResultSet;
8
9 public class MyTest {
10
11 private static Connection con;
12
13 public static void main(String[] args) throws Exception {
14
15 String connect = "jdbc:oracle:thin:@IP:PORT:DOMAIN_NAME";
16 Class.forName("oracle.jdbc.driver.OracleDriver");
17 con = DriverManager.getConnection(connect, "USERNAME", "PSW");
18
19 test();
20 }
21
22 public static void test() throws Exception {
23
24 OracleCallableStatement stmt = null;
25 OracleResultSet ors = null;
26
27 try {
28 String strSql = "{? = call PACKAGE_NAME.FUNCTION_NAME( ?, ?, ? ) }";
29 stmt = (OracleCallableStatement) con.prepareCall(strSql);
30
31 // set result type
32 stmt.registerOutParameter(1, OracleTypes.CURSOR);
33 stmt.registerOutParameter(2, Types.VARCHAR);
34 stmt.registerOutParameter(3, Types.VARCHAR);
35
36 // set condition
37 stmt.setString(4, "CONDITION_VALUE");
38
39 stmt.execute();
40 ors = (OracleResultSet) stmt.getCursor(1);
41
42 while (ors.next()) {
43 System.out.print(ors.getString("COLUMN_NAME1") + " ");
44 System.out.print(ors.getString("COLUMN_NAME2") + " ");
45 System.out.println();
46 }
47 } finally {
48 if (ors != null) {
49 ors.close();
50 }
51 if (stmt != null) {
52 stmt.close();
53 }
54 if (con != null) {
55 con.close();
56 }
57 }
58 }
59 }
用FUNCTION調用的方法
1 CREATE OR REPLACE FUNCTION FUNCTION_NAME
2 RETURN BOOLEAN
3 IS
4 TYPE TYP_Cur IS REF CURSOR;
5 curGetList TYP_Cur;
6
7 PO_varErrCd CHARACTER;
8 PO_varErrMsg CHARACTER;
9
10 --定義接收游標返回項目的變量
11 --變量的數量要和游標返回項目的數量一致
12 --接收單條數據
13 varA TABLE_NAME.COLUMN_NAME1%TYPE;
14 varB TABLE_NAME.COLUMN_NAME2%TYPE;
15 --接收多條數據
16 TYPE tableNameArray1 IS TABLE OF TABLE_NAME.COLUMN_NAME1%TYPE
17 INDEX BY BINARY_INTEGER;
18 tableName1 tableNameArray1;
19 TYPE tableNameArray2 IS TABLE OF TABLE_NAME.COLUMN_NAME2%TYPE
20 INDEX BY BINARY_INTEGER;
21 tableName2 tableNameArray2;
22 intCount INTEGER:=0;
23
24 BEGIN
25
26 curGetList := PACKAGE_NAME.FUNCTION_NAME(PO_varErrCd, PO_varErrMsg, 'CONDITION_VALUE');
27 LOOP
28 EXIT WHEN curGetList%NOTFOUND;
29
30 --FETCH curGetList INTO varA,varB;
31 --DBMS_OUTPUT.put_line(varA || ' ' || varB);
32
33 FETCH curGetList INTO tableName1(intCount),tableName2(intCount);
34 intCount := intCount + 1;
35 END LOOP;
36
37 --FOR i IN 1..tableName1.COUNT LOOP
38 FOR i IN 1..intCount LOOP
39 DBMS_OUTPUT.put_line(tableName1(i-1));
40 DBMS_OUTPUT.put_line(tableName2(i-1));
41 END LOOP;
42
43 CLOSE curGetList;
44 RETURN TRUE;
45
46 EXCEPTION
47 --略
48 END;
49 /