Advanced   Java   Services Oracle Stored Procedures Back Next Up Home


Oracle Stored Procedures

Bevor man Stored Procedures von Java aus verwenden will, muß man einigermaßen wissen, was eine Stored Procedure in SQL ist. Eine Stored Procedure ist das was man in C/C++ eine Funktion nennt oder in PASCAl eine Prozedur oder in Java eine Methode. In Java ist allerdings eine Methode an ein Objekt oder eine Klasse gebunden. Eine Stored Procedure ist also eine Art SQL-Unterprogramm und stellt eine SQL-Spracherweiterung dar, die nicht mehr standardisiert ist. Hier werden einige Beispiele für Stored Procedures unter Oracle vorgestellt. Für andere Datenbanken gilt eine ähnlich Syntax, aber eben nicht die gleiche. Oracle unterscheidet noch ein wenig altmodisch zwischen procedure und function, eine Unterscheidung, die es ja in der C/C++/Java Familie nicht mehr gibt.

Eine Prozedur kann wie eine Funktion keine, einen oder mehrere Eingabeparameter besitzen . Diese Parameter können reiner Eingabeparameter, reine Ausgabeparameter oder auch Ein- und Ausgabeparameter sein und bilden die sog. Parameterliste. Der Unterschied zwischen einer Prozedur und einer Funktion liegt im return-Wert. Eine Prozedur hat keinen Returnwert, eine Funktion genau einen. Der Returwert der Funktion kann nicht als Eingabeparameter verwendet werden. Er ist ein reiner Ausgabeparameter und stellt gewissermaßen das Ergebnis der Funktion dar. Parameter wie auch Returnwert haben jeweils einen bestimmten SQL-Datentyp oder auch einen speziellen Oracle-SQL-Datentyp.

Referenzen auf Datentypen müssen in Oracle als eigener Typ definiert werden. Dies geschieht in einem PACKAGE. Ein Package kann neben Typdefinitionen auch Definitionen von Prozeduren und Funktionen enthalten.

Beispiel:   Eine Stored Procedure, die ein ResultSet zurückgibt.

Ein Java-ResultSet entspricht in Oracle einer Referenz auf einen cursor und muß als eigener Typ vorgestellt werden: 

create or replace package types
as
   type resultset is ref cursor;
end;

Nun schreiben wir uns eine Funktion, die eine komplette Tabelle zurückliefert. Sie erhält keine Übergabeparameter, verwendet obige Typdefinition und liefert die Standard-SUN-Beispiel-Tabelle coffees zurück. 

create or replace function getresultset
   return types.resultset as rc types.resultset;
begin
   open rc for select * from coffees ;
   return rc;
end;

Hinter dem ersten return wird der Typ des Returnwerts festgelegt und eine Variable rc von diesem Typ angelegt. Der Funktionsrumpf wird zwischen begin und end eingeschlossen und enthält zwei Statements.

Die Ergänzung von or replace ist sehr praktisch, da man in Oracle eine Funktion oder Prozedur oder Package nur einmal anlegen kann. So kann man problemlos Variationen herbeiführen. Man kann aber auch per Hand löschen mit 

drop package paket_name;
//oder
drop procedure prozedur_name;
//>oder
drop function funktions_name;

Ausgehend von einem Objekt vom Typ Connection con sieht das ganze von Java aus folgendermaßen aus: 

Statement stmt = con.createStatement();
String typeString = "create or replace package types as " +
                    "type resultset is ref cursor ; " +
                    "end;" ;

stmt.execute(typeString);

Nachdem wir den Typ vereinbart haben, legen wir nun die Stored Procedure an. 

String procString =  "create or replace function getresultset " +
                     "return types.resultset as rc types.resultset; " +
                     "begin " +
                     "  open rc for select * from coffees ; " +
                     "  return rc; " +
                     "end;" ;

stmt.execute(procString);

Damit hat Oracle von der Existenz der Prozedur erfahren. Aufruf und Ausgabe der ResultSet sieht nun wie folgt aus. 

//import oracle.jdbc.driver.*;  !!
CallableStatement cs = con.prepareCall("{ ? = call getresultset }");
// OUT-parameter registrieren
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();

Jeder Ausgabeparameter muß mit einer Methode registerOutParameter() von Callable Statement registriert werden. Die Numerierung beginnt stets mit 1 und dem Returnwert, falls vorhanden. Nach dem Registrieren kann die Funktion ausgeführt werden. Dies geschieht mit execute() oder executeUpdate(), aber nicht mit executeQuery(). Obwohl das Ergebnis eine ResultSet ist, liefert executeQuery() hier null ! Das richtige Ergebnis liefert entweder die Methode getObject() oder die Methode getCursor() mit dem Index 1. 

ResultSet rs = (ResultSet)cs.getObject(1);

oder 

import oracle.jdbc.driver.*;
ResultSet rs = ( (OracleCallableStatement) cs).getCursor(1);

Die Ausgabe des ResultSets ist straightforward 

ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount() ;

while (rs.next())
{
   for(int i=1; i<cols; i++)
      System.out.print( rs.getString(i)+'\t' );

   System.out.println( rs.getString(cols) );
}

Das vollständige Beispiel

Hier nun das komplette Beispiel. Das Anlegen der Prozedur und das Ausgeben des ResultSets wird jeweils in eine Methode verlagert. 

import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;

public class CallableStatementDemo
{
   public static void main(String args[])
   {
      //Treiber laden
      Driver drv = new oracle.jdbc.driver.OracleDriver() ;

      System.out.println("Treiberliste");
      for( Enumeration en = DriverManager.getDrivers(); en.hasMoreElements() ; )
         System.out.println( en.nextElement().getClass().getName() );

      try
      {
         // Verbindung zu oracle herstellen.
         Connection con =
            DriverManager.getConnection("jdbc:oracle:thin:@:1521:oracle", "scott", "tiger");

         // Anlegen der stored procedure.
         createStoredProcedure(con);

         // Vorbereiten der stored procedure
         CallableStatement cs = con.prepareCall("{ ? = call getresultset }");

         // OUT-parameter registrieren
         cs.registerOutParameter(1, OracleTypes.CURSOR);
         cs.execute();   // keine executeQuery() hier!!

         // beide möglichkeiten flutschen !!
         ResultSet rs = (ResultSet)cs.getObject(1);
         //ResultSet rs = ( (OracleCallableStatement) cs).getCursor(1);

         // ResultSet ausgeben
         printResultSet(rs);
      }
      catch(SQLException ex)
      {
         System.out.println(ex);
      }

   }  // end main

   // ------------------------------ createStoredProcedure ------------------------------
   static void createStoredProcedure(Connection conn) throws SQLException
   {
      Statement stmt = conn.createStatement();

      // typ muß angelegt werden
      // resultset ist eine referenz auf cursor
      // ref cursor ist nämlich kein typ, sondern muß erst als solcher angelegt werden
      String typeString = "create or replace package types as " +
                   " type resultset is ref cursor ; " +
                   "end;" ;
      stmt.execute(typeString);

      // funktion verwendet diesen typ
      // returntyp ist vom typ types.resultset,
      // ref cursor kann kein returntyp sein !!!
      // die variable rc vom typ types.resultset wird angelegt,
      // erhält einen wert und wird zurückgegeben
      String procString = "create or replace function getresultset " +
                   " return types.resultset as rc types.resultset; " +
                   " begin " +
                   "    open rc for select * from coffees ; " +
                   "    return rc; " +
                   " end;" ;

      stmt.execute(procString);

   }  // end createStoredProcedure

   // ---------------------------------- printResultSet ---------------------------------
   public static void printResultSet(ResultSet rs) throws SQLException
   {
      ResultSetMetaData rsmd = rs.getMetaData();
      int cols = rsmd.getColumnCount() ;

      while (rs.next())
      {
         for(int i=1; i<cols; i++)
            System.out.print( rs.getString(i)+'\t' );

         System.out.println( rs.getString(cols) );
      }

   } // end printResultSet

} // end class

Valid XHTML 1.0 Strict top Back Next Up Home