一:无返回值的存储过程
存储过程为:
01.CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
02.
03.BEGIN
04.
05.INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
06.
07.END TESTA;
然后呢,在java里调用时就用下面的代码:
01.package com.hyq.src;
02.
03.import java.sql.*;
04.
05.import java.sql.ResultSet;
06.
07.
08.
09.public class TestProcedureOne {
10.
11.public TestProcedureOne() {
12.
13.}
14.
15.public static void main(String[] args ){
16.
17.String driver = "oracle.jdbc.driver.OracleDriver";
18.
19.String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
20.
21.Statement stmt = null;
22.
23.ResultSet rs = null;
24.
25.Connection conn = null;
26.
27.CallableStatement cstmt = null;
28.
29.
30.
31.try {
32.
33.Class.forName(driver);
34.
35.conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
36.
37.CallableStatement proc = null;
38.
39.proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
40.
41.proc.setString(1, "100");
42.
43.proc.setString(2, "TestOne");
44.
45.proc.execute();
46.
47.}
48.
49.catch (SQLException ex2) {
50.
51.ex2.printStackTrace();
52.
53.}
54.
55.catch (Exception ex2) {
56.
57.ex2.printStackTrace();
58.
59.}
60.
61.finally{
62.
63.try {
64.
65.if(rs != null){
66.
67.rs.close();
68.
69.if(stmt!=null){
70.
71.stmt.close();
72.
73.}
74.
75.if(conn!=null){
76.
77.conn.close();
78.
79.}
80.
81.}
82.
83.}
84.
85.catch (SQLException ex1) {
86.
87.}
88.
89.}
90.
91.}
92.
93.}
当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。
二:有返回值的存储过程(非列表)
存储过程为:
01.CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
02.
03.BEGIN
04.
05.SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
06.
07.END TESTB;
在java里调用时就用下面的代码:
01.package com.hyq.src;
02.
03.public class TestProcedureTWO {
04.
05.public TestProcedureTWO() {
06.
07.}
08.
09.public static void main(String[] args ){
10.
11.String driver = "oracle.jdbc.driver.OracleDriver";
12.
13.String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
14.
15.Statement stmt = null;
16.
17.ResultSet rs = null;
18.
19.Connection conn = null;
20.
21.try {
22.
23.Class.forName(driver);
24.
25.conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
26.
27.CallableStatement proc = null;
28.
29.proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");
30.
31.proc.setString(1, "100");
32.
33.proc.registerOutParameter(2, Types.VARCHAR);
34.
35.proc.execute();
36.
37.String testPrint = proc.getString(2);
38.
39.System.out.println("=testPrint=is="+testPrint);
40.
41.}
42.
43.catch (SQLException ex2) {
44.
45.ex2.printStackTrace();
46.
47.}
48.
49.catch (Exception ex2) {
50.
51.ex2.printStackTrace();
52.
53.}
54.
55.finally{
56.
57.try {
58.
59.if(rs != null){
60.
61.rs.close();
62.
63.if(stmt!=null){
64.
65.stmt.close();
66.
67.}
68.
69.if(conn!=null){
70.
71.conn.close();
72.
73.}
74.
75.}
76.
77.}
78.
79.catch (SQLException ex1) {
80.
81.}
82.
83.}
84.
85.}
86.
87.}
88.
89.
90.
91.}
注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1, 建一个程序包。如下:
01.CREATE OR REPLACE PACKAGE TESTPACKAGE AS
02.
03.TYPE Test_CURSOR IS REF CURSOR;
04.
05.end TESTPACKAGE;
2,建立存储过程,存储过程为:
01.CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
02.
03.BEGIN
04.
05.OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
06.
07.END TESTC;
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
在java里调用时就用下面的代码:
01.package com.hyq.src;
02.
03.import java.sql.*;
04.
05.import java.io.OutputStream;
06.
07.import java.io.Writer;
08.
09.import java.sql.PreparedStatement;
10.
11.import java.sql.ResultSet;
12.
13.import oracle.jdbc.driver.*;
14.
15.
16.public class TestProcedureTHREE {
17.
18.public TestProcedureTHREE() {
19.
20.}
21.
22.public static void main(String[] args ){
23.
24.String driver = "oracle.jdbc.driver.OracleDriver";
25.
26.String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
27.
28.Statement stmt = null;
29.
30.ResultSet rs = null;
31.
32.Connection conn = null;
33.
34.
35.
36.try {
37.
38.Class.forName(driver);
39.
40.conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
41.
42.
43.
44.CallableStatement proc = null;
45.
46.proc = conn.prepareCall("{ call hyq.testc(?) }");
47.
48.proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
49.
50.proc.execute();
51.
52.rs = (ResultSet)proc.getObject(1);
53.
54.
55.
56.while(rs.next())
57.
58.{
59.
60.System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
61.
62.}
63.
64.}
65.
66.catch (SQLException ex2) {
67.
68.ex2.printStackTrace();
69.
70.}
71.
72.catch (Exception ex2) {
73.
74.ex2.printStackTrace();
75.
76.}
77.
78.finally{
79.
80.try {
81.
82.if(rs != null){
83.
84.rs.close();
85.
86.if(stmt!=null){
87.
88.stmt.close();
89.
90.}
91.
92.if(conn!=null){
93.
94.conn.close();
95.
96.}
97.
98.}
99.
100.}
101.
102.catch (SQLException ex1) {
103.
104.}
105.
106.}
107.
108.}
109.
110.}