Examples of Java calling Oracle PLSQL anonymous blocks

来源：互联网发布：华为云计算大会2016 编辑：程序博客网时间：2024/06/15 15:41

原址如下：

http://quoiquilensoit.blogspot.com/2012/05/examples-of-java-calling-oracle-plsql.html

Examples of Java calling Oracle PLSQL anonymous blocks

Why would you do this? Answer: Developement agility

An Oracle DBA might say that Java should not use anonymous plsql blocks as (a) it embeds embeds database logic into Java code, and (b) is bad for performance as a stored procedure would have a precompiled execution plan.

But in the organization where I am currently consulting:

iBatis and hibernate (arguably) embed database logic into Java applications. In theory its done in a "portable" way that is not tied to the database implementation. Like thats ever going to change!
Logistically and bureaucratically, it takes weeks to get a packaged stored procedure created and installed. In my experience this is typical of most large organizations that separate Java developers from database developers and dbas. The human communication in itself between the teams, creates a bottleneck.
The PLSQL blocks are stored in seperate files and loaded from files. Database gurus tweak the SQL and hand it over for complex queries and updates.
Performance is actually not bad, because Oracle bind variables are used in the plsql. This means that oracle sees the same text every time and reuses execution plans.
Over time, if found to be durable, the PLSQL can be converted to a stored procedure and the anonmous plsql files are replaced with simple procedure calls.

Example 1: Call an anonymous PLSQL Block with one input string and one output string parameter :

01import java.sql.CallableStatement;
02import java.sql.Connection;
03import java.sql.DriverManager;
04import java.sql.SQLException;
05import java.sql.Types;
06 
07public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameter {
08 
09    // Warning: this is a simple example program : In a long running application,
10    // exception handlers MUST clean up connections statements and result sets.
11    public static void main(String[] args) throws SQLException {
12 
13        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
14 
15        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
16        String plsql = "" +
17        " declare " + 
18        "      p_id varchar2(20) := null; " +
19        " begin " +
20        "    p_id := ?; " +
21        "    ? := 'input parameter was = ' || p_id;" +
22        " end;";
23        CallableStatement cs = c.prepareCall(plsql);
24        cs.setString(1, "12345");
25        cs.registerOutParameter(2, Types.VARCHAR);
26        cs.execute();
27         
28        System.out.println("Output parameter was = '" + cs.getObject(2) + "'");
29         
30        cs.close();
31        c.close();
32    }
33 
34}

Java: Call an anonymous PLSQL Block with one input string and one output string parameter and one output cursor (query result) parameter :

1

01import java.sql.CallableStatement;
02import java.sql.Connection;
03import java.sql.DriverManager;
04import java.sql.ResultSet;
05import java.sql.Types;
06 
07import oracle.jdbc.OracleTypes;
08 
09public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameterAndOneOutputCursorParameter {
10 
11 
12<pre class="brush: java">    // Warning: this is a simple example program : In a long running application,
13    // exception handlers MUST clean up connections statements and result sets.
14</pre>
15public static void main(String[] args) throws Exception {
16 
17        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
18         
19        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
20        String plsql = "" +
21        " declare " + 
22        "    p_id varchar2(20) := null; " +
23        "     l_rc sys_refcursor;" +
24        " begin " +
25        "    p_id := ?; " +
26        "    ? := 'input parameter was = ' || p_id;" +
27        "    open l_rc for " +
28        "        select 1 id, 'hello' name from dual " +
29        "        union " +
30        "        select 2, 'peter' from dual; " +
31        "    ? := l_rc;" +
32        " end;";
33 
34        CallableStatement cs = c.prepareCall(plsql);
35        cs.setString(1, "12345");
36        cs.registerOutParameter(2, Types.VARCHAR);
37        cs.registerOutParameter(3, OracleTypes.CURSOR);
38         
39        cs.execute();
40         
41        System.out.println("Result = " + cs.getObject(2));
42         
43        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
44        while (cursorResultSet.next ())
45        {
46            System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
47        }
48        cs.close();
49        c.close();
50    }
51}

Example: Call an anonymous PLSQL Block with one input string array and one output string parameter and one output cursor (query result) parameter :

01import java.sql.Array;
02import java.sql.CallableStatement;
03import java.sql.Connection;
04import java.sql.DriverManager;
05import java.sql.ResultSet;
06import java.sql.Types;
07 
08import oracle.jdbc.OracleTypes;
09import oracle.sql.ARRAY;
10import oracle.sql.ArrayDescriptor;
11 
12public class CallPLSQLBlockWithOneInputStringArrayAndOneOutputStringParameterAndOneOutputCursorParameter {

01<pre class="brush: java">    // Warning: this is a simple example program : In a long running application,
02    // exception handlers MUST clean up connections statements and result sets.</pre>
03public static void main(String[] args) throws Exception {
04 
05        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
06         
07        // Warning: this is a simple example program : In a long running application,
08        // error handlers MUST clean up connections statements and result sets.
09         
10        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
11        String plsql = "" +
12        " declare " + 
13        "    p_id string_array := null; " +
14        "     l_rc sys_refcursor;" +
15        " begin " +
16        "    p_id := ?; " +
17        "    ? := 'input parameter first element was = ' || p_id(1);" +
18        "    open l_rc for select * from table(p_id) ; " +
19        "    ? := l_rc;" +
20        " end;";
21 
22        String[] stringArray = new String[]{ "mathew", "mark"};
23         
24        // MUST CREATE THIS IN ORACLE BEFORE RUNNING
25        System.out.println("(This should be done once in Oracle)");
26        c.createStatement().execute("create or replace type string_array is table of varchar2(32)");
27         
28        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "STRING_ARRAY", c );
29         
30        Array array_to_pass = new ARRAY( descriptor, c, stringArray );
31         
32        CallableStatement cs = c.prepareCall(plsql);
33        cs.setArray( 1, array_to_pass );
34        cs.registerOutParameter(2, Types.VARCHAR);
35        cs.registerOutParameter(3, OracleTypes.CURSOR);
36         
37        cs.execute();
38         
39        System.out.println("Result = " + cs.getObject(2));
40         
41        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
42        while (cursorResultSet.next ())
43        {
44            System.out.println (cursorResultSet.getString(1));
45        }
46        cs.close();
47        c.close();
48    }
49} 

Example: Call an anonymous PLSQL Block with one input structure array and one output string parameter and one output cursor (query result) parameter :

01import java.sql.Array;
02import java.sql.CallableStatement;
03import java.sql.Connection;
04import java.sql.DriverManager;
05import java.sql.ResultSet;
06import java.sql.SQLException;
07import java.sql.Types;
08 
09import oracle.jdbc.OracleTypes;
10import oracle.sql.ARRAY;
11import oracle.sql.ArrayDescriptor;
12import oracle.sql.STRUCT;
13import oracle.sql.StructDescriptor;
14 
15public class CallPLSQLBlockWithOneInputStructureArrayAndOneOutputStringParameterAndOneOutputCursorParameter {
16 
17    public static void main(String[] args) throws Exception {
18 
19        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
20         
21        // Warning: this is a simple example program : In a long running application,
22        // error handlers MUST clean up connections statements and result sets.
23         
24        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
25        String plsql = "" +
26        " declare " + 
27        "    p_id student_array := null; " +
28        "     l_rc sys_refcursor;" +
29        " begin " +
30        "    p_id := ?; " +
31        "    ? := 'input parameter first element was = (' || p_id(1).id_num || ', ' || p_id(1).name || ')'; " +
32        "    open l_rc for select * from table(p_id) ; " +
33        "    ? := l_rc;" +
34        " end;";
35 
36         
37        // MUST CREATE ORACLE TYPES BEFORE RUNNING
38        setupOracleTypes(c);
39         
40        StructDescriptor structDescr = StructDescriptor.createDescriptor("STUDENT", c);
41        STRUCT s1struct = new STRUCT(structDescr, c, new Object[]{1, "mathew"});
42        STRUCT s2struct = new STRUCT(structDescr, c, new Object[]{2, "mark"});
43        ArrayDescriptor arrayDescr = ArrayDescriptor.createDescriptor( "STUDENT_ARRAY", c );
44        Array array_to_pass = new ARRAY( arrayDescr, c, new Object[]{s1struct, s2struct} );
45         
46        CallableStatement cs = c.prepareCall(plsql);
47        cs.setArray( 1, array_to_pass );
48        cs.registerOutParameter(2, Types.VARCHAR);
49        cs.registerOutParameter(3, OracleTypes.CURSOR);
50         
51        cs.execute();
52         
53        System.out.println("Result = " + cs.getObject(2));
54         
55        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
56        while (cursorResultSet.next ())
57        {
58            System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
59        }
60        cs.close();
61        c.close();
62    }
63 
64    private static void setupOracleTypes(final Connection c)
65            throws SQLException {
66        System.out.println("(This should be done once in Oracle)");
67        try {
68            c.createStatement().execute("drop type student_array ");
69        } catch (Exception e) {
70            // ignore
71        }
72        c.createStatement().execute("create or replace type student as object (id_num integer(4), name varchar2(25))");
73        c.createStatement().execute("create or replace type student_array is table of student");
74    } 
75 
76} 