# PL/SQL Anfänger



## Optiplex9020 (21. Jun 2017)

Hallo.
Ich versuche mich momentan in Richtung Datenbankprogrammierung weiterzubilden. Ich habe meine Anfänge in JDBC gemacht und bin darin nun schon ziemlich sicher, jedoch habe ich Probleme bei dem Übergang in PL/SQL. Momentan versuche ich meine bisherigen Abfragen in PL/SQL zu übersetzen aber komme leider auf keine sinnvolle Lösung. Eventuell kann mir hier jemand dabei helfen.

Mein bisheriger Code in JDBC

```
package de.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import de.TableDataGateways.PlaceTDG;
import de.TableDataGateways.SampleTDG;
import de.TableDataGateways.TrayTDG;
import de.exceptions.CoolingSystemException;
import de.exceptions.DataException;
import de.utils.DateUtils;


public class CoolingService implements ICoolingService {
    private static final Logger L = LoggerFactory.getLogger(CoolingService.class);
    private Connection connection;
    private TrayTDG trayTDG = new TrayTDG();
    private SampleTDG sampleTDG = new SampleTDG();
    private PlaceTDG placeTDG = new PlaceTDG();

    @Override
    public void setConnection(Connection connection) {
        this.connection = connection;
        trayTDG.setConnection(connection);
        sampleTDG.setConnection(connection);
        placeTDG.setConnection(connection);
    }

    protected Connection getConnection() {
        if (connection == null) {
            throw new DataException("Connection not set");
        }
        return connection;
    }

    @SuppressWarnings("resource")
    @Override
    public void transferSample(Integer sampleId, Integer diameterInCM) {
        L.info("Start");
        ResultSet rs = null;
        try {
            // Suche nach passenden Tabletts
            rs = trayTDG.findByDiameterInCM(diameterInCM);
            List<Integer> tablettListe = new ArrayList<Integer>();
            if (!rs.next()) {
                throw new CoolingSystemException("Kein passendes Tablett gefunden");
            } else {
                do {
                    tablettListe.add(rs.getInt("TrayID"));
                    //System.out.println("TrayID: " + r.getInt("TrayID")); //Test
                } while (rs.next());
            }
            // Ablaufdatum erhalten
            rs = sampleTDG.findBySampleId(sampleId);
            LocalDate ablaufdatum = null;
            if (rs.next()) {
                ablaufdatum = DateUtils.sqlDate2LocalDate(rs.getDate("ExpirationDate"));
            } else {
                throw new CoolingSystemException("Keine Probe zu dieser SampleId gefunden");
            }
            // Kapazität prüfen
            List<Integer> tablettstmitCapacity = new ArrayList<Integer>();
            List<Integer> freieTabletts = new ArrayList<Integer>();
            Iterator<Integer> trayIdIterator = tablettListe.iterator();

            int capacity = 0;
            //
            while (trayIdIterator.hasNext()) {
                int trayID = trayIdIterator.next();
                //System.out.println("TrayID: " + trayID); // Test
                rs = trayTDG.findByTrayID(trayID);
                if (rs.next()) {
                    capacity = rs.getInt("capacity");
                    //System.out.println("Kapazität: " + kapazitaet); // Test
                }

                rs = placeTDG.countPlaceNoByTrayId(trayID);
                int belegtePlaetze = -1;
                if (rs.next()) {
                    belegtePlaetze = rs.getInt(1);
                    //System.out.println("Belegte Plätze: " + belegtePlaetze);
                }
                //
                if (belegtePlaetze == 0) {
                    freieTabletts.add(trayID);
                //
                } else if (capacity > belegtePlaetze) {
                    tablettstmitCapacity.add(trayID);
                }
            }
            //System.out.println("Freie Tabletts: " + freieTabletts.size()); // Test
            if (tablettstmitCapacity.isEmpty() && freieTabletts.isEmpty()) {
                throw new CoolingSystemException("Keine passenden Tabletts vorhaben");
            }
            // Ablaufdatum wird geprueft
            List<Integer> passendesTablettmitAblaufdatumListe = new ArrayList<Integer>();
            Iterator<Integer> kapazitaetsIterator = tablettstmitCapacity.iterator();
            if (!tablettstmitCapacity.isEmpty()) {
                //wenn Capacity is leer
                while (kapazitaetsIterator.hasNext()) {
                    int trayID = kapazitaetsIterator.next();
                    rs = trayTDG.findByTrayID(trayID);
                    LocalDate expirationDate = null;
                    //
                    if (rs.next()) {
                        expirationDate = DateUtils.sqlDate2LocalDate(rs.getDate("ExpirationDate"));
                    }
                    //
                    if (ablaufdatum.compareTo(expirationDate) <= 0 && expirationDate != null) {
                        passendesTablettmitAblaufdatumListe.add(trayID);
                    }
                }
            }
            // Leeres Tablett nehmen und einfügen
            boolean tablettNeu = false;
            //
            if (passendesTablettmitAblaufdatumListe.isEmpty() && !freieTabletts.isEmpty()) {
                int freieTrayID = freieTabletts.get(0);
                trayTDG.update(freieTrayID, ablaufdatum);
                placeTDG.insert(freieTrayID, 1, sampleId);
                tablettNeu = true;
            }
            // kleinstes Ablaufdatum von Tabletts mit Kapazität
            if(!tablettNeu){
            List<Date> ablaufdatenTabletts = new ArrayList<Date>();
            Iterator<Integer> ablaufdatumIterator = passendesTablettmitAblaufdatumListe.iterator();
            Date kleinstesAblaufdatum = null;
            if (!passendesTablettmitAblaufdatumListe.isEmpty()) {
                //
                while (ablaufdatumIterator.hasNext()) {
                    int trayID = ablaufdatumIterator.next();
                    rs = trayTDG.findByTrayID(trayID);
                    //
                    if (rs.next()) {
                        ablaufdatenTabletts.add(rs.getDate("ExpirationDate"));
                    }
                }
                Collections.sort(ablaufdatenTabletts);
           
                if (!ablaufdatenTabletts.isEmpty()) {
                    kleinstesAblaufdatum = ablaufdatenTabletts.get(0);
                    //
                } else {
                    throw new CoolingSystemException("Kein Ablaufdatum vorhanden");
                }
            }
            // TrayID zum kleinstem Ablaufdatum
            //System.out.println("kleinstes Ablaufdatum: " + kleinstesAblaufdatum); //Test
            int kleinsteTrayID = 0;
            if (kleinstesAblaufdatum != null) {
                rs = trayTDG.findByExpirationDate(kleinstesAblaufdatum);
            } else {
                rs = trayTDG.findByNullExpirationDate();
            }
            if (rs.next()) {
                kleinsteTrayID = rs.getInt("TrayID");
            } else {
                throw new CoolingSystemException("Kleinste TrayID nicht vorhanden");
            }

            // kleinsten verfügbaren Platz ermitteln
            List<Integer> platznummern = new ArrayList<Integer>();
            int kleinsteNummer = 1;
            int kleinsterPlatz = 0;
            rs = placeTDG.findByTrayIdOrderByPlaceNoASC(kleinsteTrayID);
            //
            while (rs.next()) {
                platznummern.add(rs.getInt("PlaceNo"));
            }
            //
            for (int i = 0; i < platznummern.size(); i++) {
                if (kleinsteNummer != platznummern.get(i)) {
                    //
                    if (kleinsterPlatz == 0) {
                        kleinsterPlatz = kleinsteNummer;
                    }
                } else {
                    kleinsteNummer++;
                    //
                    if (kleinsteNummer > platznummern.size()) {
                        kleinsterPlatz = kleinsteNummer;
                    }
                }
            }

            // Einfügen in die Tabelle
            placeTDG.insert(kleinsteTrayID, kleinsterPlatz, sampleId);
            }
            rs.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
```


```
package de.TableDataGateways;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class PlaceTDG {
    private Connection connection;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public void insert(int trayId, int placeNo, int sampleId) {
        PreparedStatement ps = null;

        try {
            ps = this.getConnection().prepareStatement(
                    "INSERT INTO Place(TrayID, PlaceNo, SampleID) VALUES(?,?,?)");
            ps.setInt(1, trayId);
            ps.setInt(2, placeNo);
            ps.setInt(3, sampleId);
            //System.out.println("PlaceTDG: Tray ID: " + trayId + " Platz: " + placeNo + " SampleID: " + sampleId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
   
    public ResultSet findByTrayId(int trayId) {
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = this.getConnection().prepareStatement("SELECT * FROM Place WHERE TrayID = ?");
            ps.setInt(1, trayId);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
   
    public ResultSet findByTrayIdOrderByPlaceNoASC(int trayId) {
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = this.getConnection().prepareStatement("SELECT * FROM Place WHERE TrayID = ? ORDER BY PlaceNo ASC");
            ps.setInt(1, trayId);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
   
    public ResultSet countPlaceNoByTrayId(int trayId) {
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = this.getConnection().prepareStatement("SELECT COUNT(PlaceNo) FROM Place WHERE TrayID = ?");
            ps.setInt(1, trayId);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
}
```


```
package de.TableDataGateways;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class SampleKindTDG {
    private Connection connection;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public void insert(int sampleKindId, String text, int validNoOfDays) {
        PreparedStatement ps = null;

        try {
            ps = this.getConnection().prepareStatement(
                    "INSERT INTO SampleKind(SampleKindID, Text, ValidNoOfDays) VALUES(?,?,?)");
            ps.setInt(1, sampleKindId);
            ps.setString(2, text);
            ps.setInt(3, validNoOfDays);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
```


```
package de.TableDataGateways;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class SampleTDG {
    private Connection connection;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public void insert(int sampleId, int sampleKindId, Date expirationDate) {
        PreparedStatement ps = null;

        try {
            ps = this.getConnection().prepareStatement(
                    "INSERT INTO Sample(SampleID, SampleKindID, ExpirationDate) VALUES(?,?,?)");
            ps.setInt(1, sampleId);
            ps.setInt(2, sampleKindId);
            ps.setDate(3, expirationDate);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
   
    public ResultSet findBySampleId(int sampleId) {
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = this.getConnection().prepareStatement("SELECT * FROM Sample WHERE SampleID = ?");
            ps.setInt(1, sampleId);
            rs= ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
}
```


```
package de.TableDataGateways;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;

import de.utils.DateUtils;


public class TrayTDG {
    private Connection connection;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public void insert(int trayId, int diameterInCM, int capacity, Date expirationDate) {
        PreparedStatement ps = null;
        try {
            ps = this.getConnection().prepareStatement(
                    "INSERT INTO Tray(TrayID, DiameterInCM, Capacity, ExpirationDate) VALUES(?,?,?,?)");
            ps.setInt(1, trayId);
            ps.setInt(2, diameterInCM);
            ps.setInt(3, capacity);
            ps.setDate(4, expirationDate);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public void update(int trayId, LocalDate expirationDate) {
        PreparedStatement ps = null;

        try {
            ps = this.getConnection().prepareStatement("UPDATE Tray SET ExpirationDate =?+30 WHERE TrayID=?");
            ps.setDate(1, DateUtils.localDate2SqlDate(expirationDate));
            ps.setInt(2, trayId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public ResultSet findByTrayID(int trayId) {
        PreparedStatement p = null;
        ResultSet rs;
        try {
            p = this.getConnection().prepareStatement("SELECT * FROM Tray WHERE TrayID = ?");
            p.setInt(1, trayId);
            rs = p.executeQuery();
        } catch (SQLException e) {
            try {
                if (p != null) {
                    p.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }

    public ResultSet findByDiameterInCM(int diameterInCM) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = this.getConnection().prepareStatement("SELECT * FROM Tray WHERE DiameterInCM = ?");
            ps.setInt(1, diameterInCM);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
   
    public ResultSet findByExpirationDate(Date expirationDate) {
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = this.getConnection().prepareStatement("SELECT * FROM Tray WHERE ExpirationDate = ?");
            ps.setDate(1, expirationDate);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
   
    public ResultSet findByNullExpirationDate() {
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = this.getConnection().prepareStatement("SELECT * FROM Tray WHERE ExpirationDate IS NULL");
            rs = ps.executeQuery();
        } catch (SQLException e) {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e2) {
                throw new RuntimeException(e2);
            }
            throw new RuntimeException(e);
        }
        return rs;
    }
}
```

Würde mich freuen wenn mir jemand dabei hilft.


----------



## VfL_Freak (21. Jun 2017)

Moin,

habe mir jetzt den Code nicht wirklich angeschaut !



Optiplex9020 hat gesagt.:


> Momentan versuche ich meine bisherigen Abfragen in PL/SQL zu übersetzen aber komme leider auf keine sinnvolle Lösung


Wie sehen denn die bisherigen Abfragen aus?? 
Was funktioniert denn nicht (was erwartest du und was passiert stattdessen)? 

Ein paar mehr Info dürften es schon sein! 
http://www.java-forum.org/forum-faq-beitraege/7407-man-fragen-richtig-stellt.html

VG Klaus


----------



## Optiplex9020 (21. Jun 2017)

Es geht mir darum mal ein Beispiel zu sehen wie korrekter PL/SQL Code aussieht.


----------



## Dukel (21. Jun 2017)

Es gibt sicher genügend Doku zu Pl/Sql.
http://www.oracle.com/technetwork/database/features/plsql/index.html
https://www.tutorialspoint.com/plsql/


----------



## fhoffmann (21. Jun 2017)

Es ist keine gute Idee, dass deine Methoden zum Lesen aus der Datenbank ein ResultSet zurückgeben.
In diesen Methoden schließt du das PreparedStatement nie. Das ist nicht gut.
Würdest du es jedoch schließen, würde das ResultSet mit geschlossen. Das ist auch nicht gut.


----------



## togal (5. Jul 2017)

Wenn Du plsql lernen willst, dann schreib auch plsql code.
Ich hab mal irgendwas gegoogled:
https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/a_samps.htm

```
-- available online in file 'sample1'
--- **
DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..10 LOOP
      IF MOD(i,2) = 0 THEN     -- i is even
         INSERT INTO temp VALUES (i, x, 'i is even');
      ELSE
         INSERT INTO temp VALUES (i, x, 'i is odd');
      END IF;
      x := x + 100;
   END LOOP;
   COMMIT;  --- *
END;
```

Wenn Du bei Java bleiben willst, dann lieber den Standardweg, native java code und Daten per jdbc aus der DB holen.
Wenn Du embedded Java in PLSQL machen willst, findet sich sicher auch dafür irgendwo ein funktionierendes Beispiel. Frage wäre, was es bringen soll.
Embedded Java in der Datenbank ist m.E. etwas exotisch und für den ein oder anderen Spezialfall mag es okay sein.
Ach und noch ein Tipp:
Wenn Du etwas neues probierst, mach Dir ein kleines, überschaubares Beispiel. Z.B. ein Stück Code, das einen vorhanden Datensatz updated. Sobald das läuft, erweitern.



* Das steht zwar so im Beispiel, commit im DB Code würde ich aber nienieniemals machen, weiß gar nicht, warum ich ausgerechnet das als Beispiel poste.
** Das ist pl/sql code, sozusagen "headless". Pack es in ein Statement und Execute es (Der Code macht natürlich nur Sinn, wenn die Table da ist). Wenn man noch ein "Create Procedure .. " davor packt und etwas justiert, hat man eine ausführbare, benannte Prozedur. Das kann man dann noch in ein Package stecken und mehrere Prozeduren und Funktionen bündeln. Link zu Beispielen hast Du ja schon erhalten.


----------

