Stored Procedure in PL/SQL

[Heraklit1]

Hallo,

ich habe ein Code-Beispiel vorliegen, in dem eine Select-Anweisung in einer MySQL-Procedure aufgerufen wird:

delimiter $$
create procedure zeigeBuecher (in j int)
begin
select isbn, autor, titel from buch where jahr >= j;
end $$
delimiter ;

In Java wird dann das Ergebnis über ein CallableStatement ausgelesen. Nun möchte ich jedoch die obige Stored Procedure für die Oracle-Datenbank verwenden. Wie lautet der entsprechende PL/SQL Code?

Viele Grüße!

 

[Dracon_Draconus]

Hallo,

wenn du die Prozedur direkt nach Oracle schreiben willst dann sollte das so gehen:

create or replace procedure zeigeBuecher (v_j PLS_INTEGER) as
begin
execute immediate 'SELECT isbn, autor, titel FROM buch WHERE jahr >= :bindvar' using v_j;
end;

Mfg
Dracon_Draconus

 

[Heraklit1]

Hallo,

ich habe dein SQL-Skript ausprobiert, leider ohne Erfolg.
Zunächst habe ich die Prozedur erfolgreich erstellt:

SQL> desc zeigeBuecher
PROCEDURE zeigeBuecher
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
V_J BINARY_INTEGER IN

Mit dem folgenden Java-Programm wollte ich dann die Prozedur ausführen:

import java.sql.*;
import java.io.*;
import java.util.*;

public class ZeigeBuecher {
  public static void main(String[] args) throws Exception {
    int jahr = Integer.parseInt(args[0]);
    
    FileInputStream in = new FileInputStream("dbconnect.properties");
    Properties prop = new Properties();
    prop.load(in);
    in.close();

    String driver = prop.getProperty("driver");
    String url = prop.getProperty("url");
    String user = prop.getProperty("user");
    String password = prop.getProperty("password");

    Class.forName(driver);
    Connection con = DriverManager.getConnection(url, user, password);
    String str = "{call zeigeBuecher (?)}";
    CallableStatement stmt = con.prepareCall(str);
    stmt.setInt(1, jahr);

    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
      System.out.println(rs.getString(1) + " " + rs.getString(2) + " " +
        rs.getString(3));
    }
    
    rs.close();
    stmt.close();
    con.close();
  }
}

Jedoch habe ich dann folgende Fehlermeldung erhalten:

Exception in thread "main" java.sql.SQLException: Abrufen von PLSQL-Anweisung nicht möglich: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:247)
at ZeigeBuecher.main(Unknown Source)

Ich vermute, dass die Übergabe der Datensätze fehlschlägt.
Habt ihr eine Idee?

Viele Grüße

 

[Dracon_Draconus]

Hallo,

das leiegt daran das bei der prozedur kein Rückgabe definiert ist. Hier musss ein Out definiert werden. Wobei sich hier die Frage stellt wozu überhaubt bei einer derartig einfachen Abfrage eine Prozedur genommen werden muss...
Ich würde das als direkte Abfrage via Java machen....dann habe ich auch geleich das Ergebnis in der entsprechenden Variablen ohne viel Probleme.

Mfg
Dracon_Draconus

 

[Gast2]

Nebenbei funktioniert bei ORACLE PL/SQL kein [c]"call procedurename"[/c]. Die Procedure oder Function muss in einem anonymen Block ausgeführt werden:

String query = "BEGIN "+
                    "   MYSCHEMA.zeigeBuecher(?);"
                    "END;"

Siehe auch hier:
http://www.java-forum.org/datenbankprogrammierung/99210-procedureaufruf-oracle.html

Die Procedure macht aber wirklich wenig Sinn. Da ist ein ganz normales JDBC Query da auf jeden Fall vorzuziehen.

 

[Heraklit1]

Hallo,

danke für eure Antworten. Zunächst einmal funktioniert der "call"-Aufruf sehr wohl bei pl/sql, wie folgendes Beispiel verdeutlicht.
Zunächst habe ich eine stored procedure in oracle angelegt:

CREATE OR REPLACE PROCEDURE updateBestand(
buchnr IN VARCHAR2, menge IN NUMBER, code OUT NUMBER, alt OUT NUMBER, neu OUT NUMBER)
IS
cnt NUMBER;
BEGIN
SELECT count(*) INTO cnt FROM buch WHERE isbn = buchnr;
IF cnt = 0 THEN
code := 1;
RETURN;
END IF;

SELECT bestand INTO alt FROM buch WHERE isbn = buchnr;
neu := alt + menge;
UPDATE buch SET bestand = neu WHERE isbn = buchnr;
code := 0;
END;
/

Mit folgendem Programm kann man dann sehr schön auf die Out-Parameter zugreifen. Es funktioniert also einwandfrei. Da ich mich mit PL/SQL und JDBC nur sehr wenig auskenne, frage ich mich, wie die Procedure und das Java-Programm aussehen muss, damit die Abfrage funktioniert. Dass hier ein normaler JDBC-Query sinnvoller ist, ist mir klar. Ich möchte nur ein Beispiel umschreiben.

import java.sql.*;
import java.io.*;
import java.util.*;

public class UpdateBestand {
  public static void main(String[] args) throws Exception {
    String isbn = args[0];
    int menge = Integer.parseInt(args[1]);
    
    FileInputStream in = new FileInputStream("dbconnect.properties");
    Properties prop = new Properties();
    prop.load(in);
    in.close();

    String driver = prop.getProperty("driver");
    String url = prop.getProperty("url");
    String user = prop.getProperty("user");
    String password = prop.getProperty("password");

    Class.forName(driver);
    Connection con = DriverManager.getConnection(url, user, password);
    String str = "{call updateBestand (?,?,?,?,?)}";
    CallableStatement stmt = con.prepareCall(str);
    stmt.setString(1, isbn);
    stmt.setInt(2, menge);
    stmt.registerOutParameter(3, Types.INTEGER);
    stmt.registerOutParameter(4, Types.INTEGER);
    stmt.registerOutParameter(5, Types.INTEGER);

    stmt.executeUpdate();
    int rc = stmt.getInt(3);
    if (rc == 0) {
      int alt = stmt.getInt(4);
      int neu = stmt.getInt(5);
      System.out.println("Bestand alt: " + alt);
      System.out.println("Bestand neu: " + neu);
    }
    else {
      System.out.println("Buch nicht vorhanden");
    }
    
    stmt.close();
    con.close();
  }
}