# Stored Procedure in PL/SQL



## Heraklit1 (9. Jun 2010)

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 (9. Jun 2010)

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 (9. Jun 2010)

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 (9. Jun 2010)

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 (9. Jun 2010)

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 (10. Jun 2010)

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();
  }
}
```


----------

