Cursor verwenden und Ergebnis in Oracle PL / SQL mit Java / JDBC erhalten

Ich habe eine PL / SQL-Abfrage wie folgt aufgebaut:

DECLARE a NUMBER; B NUMBER; CURSOR cursor IS ( SOME SELECT QUERY); BEGIN OPEN cursor; LOOP SOME STUFF; END LOOP; CLOSE cursor; END 

Wie kann ich diese Abfrage von einem Java-Code mit jdbc ausführen und die Ergebnismenge erhalten? Ich habe versucht, die Abfrage auszuführen, ohne den Cursor zu verwenden, und es wird korrekt ausgeführt. Ich konnte keinen path finden, dies im Java-Code zu tun. Wenn ich die Abfrage direkt auf Oracle-Client ausführen, funktioniert es ohne Probleme. Es gibt also kein Problem mit der Abfrage.

PS Ich möchte den Code nicht als gespeicherte Prozedur speichern und wegen einiger Einschränkungen aufrufen.

Das ist nicht möglich. Sie können eine Ergebnismenge nicht aus einem anonymen PL / SQL-Block zurückgeben (und daher gibt es keine Möglichkeit, sie von JDBC abzurufen).

Sie müssen die Auswahl direkt von JDBC aus ausführen.

Die einzige, wirklich hässliche Problemumgehung wäre die Verwendung von dbms_output.put_line() und dem danach gelesenen. Aber das ist ein wirklich hässlicher Hack und die Verarbeitung des Ergebnisses der SELECT-Abfrage direkt in JDBC ist viel besser.


Bearbeiten 1

Hier ist ein kleines Beispiel mit dbms_output:

 Connection con = ....; // turn on support for dbms_output CallableStatement cstmt = con.prepareCall("{call dbms_output.enable(32000) }"); cstmt.execute(); // run your PL/SQL block Statement stmt = con.createStatement(); String sql = "declare \n" + " a number; \n" + " cursor c1 is select id from foo; \n" + "begin \n" + " open c1; \n" + " loop \n" + " fetch c1 into a; \n" + " exit when c1%notfound; \n" + " dbms_output.put_line('ID: '||to_char(a)); \n" + " end loop; \n" + "end;"; stmt.execute(sql); // retrieve the messages written with dbms_output cstmt = con.prepareCall("{call dbms_output.get_line(?,?)}"); cstmt.registerOutParameter(1,java.sql.Types.VARCHAR); cstmt.registerOutParameter(2,java.sql.Types.NUMERIC); int status = 0; while (status == 0) { cstmt.execute(); String line = cstmt.getString(1); status = cstmt.getInt(2); if (line != null && status == 0) { System.out.println(line); } } 

Edit 2 (das ist zu lang für einen Kommentar)

Das Verschachteln von loops zum Abrufen von data ist fast immer eine schlechte Idee. Wenn Sie sich selbst so etwas finden:

 begin for data_1 in (select id from foo_1) loop dbms_output.put_line(to_char(data_1.id)); for data_2 in (select f2.col1, f2.col2 from foo_2 f2 where f2.id = data_1.id) loop ... do something else end loop; end loop; end; / 

Es wird viel effizienter sein, es so zu machen:

 begin for data_1 in (select f2.col1, f2.col2 from foo_2 f2 where f2.id in (select f1.id from foo_1 f1)) loop ... do something end loop; end; / 

Dies kann ohne übermäßigen memory in JDBC mit etwas wie diesem verarbeitet werden:

 String sql = "select f2.col1, f2.col2 from foo_2 f2 where f2.id in (select f1.id from foo_1 f1)"; Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { String col1_value = rs.getString(1); int col2_value = rs.getInt(2); ... do something } 

Der obige Code speichert nur eine Zeile im memory, auch wenn Sie Milliarden von Zeilen verarbeiten. Um genau zu sein: Der JDBC-Treiber wird tatsächlich mehr als eine Zeile vorausholen. Der Standardwert ist 10 und kann geändert werden. Aber selbst dann haben Sie keinen übermäßigen memoryverbrauch.

@ Rajat,

Könnten Sie unter Methode versuchen:

Um den Cursor abzurufen, sollten Sie ihn in der Paketspezifikation als REF CURSOR deklarieren.

  --Creating the REF CURSOR type type g_cursor is ref cursor; 

In beiden, spec und body, müssen Sie eine REF CURSOR-Variable in Prozedur-Signatur deklarieren, wie oben zitiert.

  procedure PRO_RETURN_CARS( i_id in tbl_car.car_id%type, o_cursor in out g_cursor); 

Der Cursor muss im Prozedurkörper geöffnet werden, um auf diese Weise zurückzukehren:

 open o_cursor for select car_id, company, model, color, hp, price from tbl_car where car_id = i_id; 

Das komplette Paket:

 create or replace package PAC_CURSOR is --Creating REF CURSOR type type g_cursor is ref cursor; --Procedure that return the cursor procedure PRO_RETURN_CARS( i_id in tbl_car.car_id%type, o_cursor in out g_cursor); -- Our cursor end PAC_CURSOR; / create or replace package body PAC_CURSOR is procedure PRO_RETURN_CARS( i_id in tbl_car.car_id%type, o_cursor in out g_cursor) is begin --Opening the cursor to return matched rows open o_cursor for select car_id, company, model, color, hp, price from tbl_car where car_id = i_id; end PRO_RETURN_CARS; end PAC_CURSOR; 

Wir haben Oracle-Seite bereit, jetzt müssen wir Java-Aufruf erstellen

Wie die Cursor von einer Prozedur zurückgegeben werden, verwenden wir eine java.sql.CallableStatement Instanz:

 CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}"); 

Der oracle.jdbc.OracleTypes.CURSOR registerOutParameter oracle.jdbc.OracleTypes.CURSOR Typ oracle.jdbc.OracleTypes.CURSOR und gibt eine java.sql.ResultSet Instanz zurück. Wir können das ResultSet wie einen gewöhnlichen Iterator ResultSet .

Jede Zeilenspalte, die von SELECT zurückgegeben wird, wird wie eine Karte dargestellt, wobei der entsprechende Getter verwendet wird. Zum Beispiel rufen wir die Methode getString () auf, wenn der Wert der Spalte ein varchar ist, getDate () wann ein date ist usw.

Der vollständige Code wird wie folgt aussehen:

 //Calling Oracle procedure CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}"); //Defining type of return cs.registerOutParameter("o_cursor", OracleTypes.CURSOR); cs.setLong("i_id", id); cs.execute();//Running the call //Retrieving the cursor as ResultSet ResultSet rs = (ResultSet)cs.getObject("o_cursor"); //Iterating the returned rows while(rs.next()){ //Getting column values System.out.println("ID: " + rs.getLong("car_id")); System.out.println("Manufacturer: " + rs.getString("company")); System.out.println("Model: " + rs.getString("model")); System.out.println("Color: " + rs.getString("color")); System.out.println("HP: " + rs.getString("hp")); System.out.println("Price: " + rs.getFloat("price")); } 

Am Ende get jeden Wert, der in einer SELECT-Klausel zurückgegeben wird.

Die anderen Antworten hier scheinen sehr kompliziert zu sein.

Verwenden von SYS_REFCURSOR

Seit SYS_REFCURSOR können Sie SYS_REFCURSOR Typen sehr einfach von JDBC abrufen:

 DECLARE cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT ...; ? := cur; END; 

Führen Sie nun das obige von Java so aus:

 try (CallableStatement c = con.prepareCall(sql)) { c.registerOutParameter(1, OracleTypes.CURSOR); // -10 c.execute(); try (ResultSet rs = (ResultSet) c.getObject(1)) { ... } } 

Natürlich können Sie auch eigene Cursor in Paketen deklarieren, wie von pmrs Antwort vorgeschlagen , aber warum sollten Sie einen anonymen Block von JDBC ausführen?

Oracle 12c implizite Ergebnismengen verwenden

Oracle 12c hat für diese Fälle eine praktische neue function hinzugefügt, die der Vorgehensweise von SQL server / Sybase und MySQL bei Prozeduren / Batches ähnelt, die Ergebnisse zurückgeben. Sie können jetzt die DBMS_SQL.RETURN_RESULT Prozedur für jeden Cursor verwenden, der sie "durch Magie" zurückgibt:

 DECLARE cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT ...; DBMS_SQL.RETURN_RESULT(cur); END; 

Aufgrund eines Fehlers (oder "Features") im Oracle JDBC-Treiber ist es etwas komplizierter, diesen Cursor korrekt von JDBC zu holen, aber es kann sicherlich getan werden, wie ich es in diesem Artikel hier gezeigt habe . So können Sie eine beliebige Anzahl impliziter Cursor aus einem anonymen PL / SQL-Block und / oder einer Prozedur, einem Trigger usw. ermitteln:

 try (PreparedStatement s = cn.prepareStatement(sql)) { // Use good old three-valued boolean logic Boolean result = s.execute(); fetchLoop: for (int i = 0;; i++) { // Check for more results if not already done in this iteration if (i > 0 && result == null) result = s.getMoreResults(); System.out.println(result); if (result) { result = null; try (ResultSet rs = s.getResultSet()) { System.out.println("Fetching result " + i); } catch (SQLException e) { // Ignore ORA-17283: No resultset available if (e.getErrorCode() == 17283) continue fetchLoop; else throw e; } } else if (s.getUpdateCount() == -1) // Ignore -1 value if there is one more result! if (result = s.getMoreResults()) continue fetchLoop; else break fetchLoop; } } 

Da die Signatur von java.sql.PreparedStatement.execute () "boolean execute ()" nicht "Boolean execute ()" ist, kann die Variable "result" niemals null sein, da der zurückgegebene Wert von s.execute ( ), so könnte der Test "i> 0 && result == null" "result == null" sein