Exercices corrigés de Oracle

SQL pour Oracle C.Soutou, O.Teste © Eyrolles 2004 19 FOR EACH ROW BEGIN IF DELETING THEN UPDATE Poste SET nbLog=nbLog – 1 WHERE nPoste = :OLD.nPoste; UPDATE Logiciel SET nbInstall = nbInstall – 1 WHERE nLog = :OLD.nLog; ELSE IF INSERTING THEN UPDATE Poste SET nbLog = nbLog + 1 WHERE nPoste = :NEW.nPoste; UPDATE Logiciel SET nbInstall = nbInstall + 1 WHERE nLog = :NEW.nLog; END IF; END IF; END; / CREATE OR REPLACE TRIGGER Trig_Après_DI_Poste AFTER INSERT OR DELETE ON Poste FOR EACH ROW BEGIN IF DELETING THEN UPDATE Salle SET nbPoste = nbPoste – 1 WHERE nSalle = :OLD.nSalle; ELSE UPDATE Salle SET nbPoste = nbPoste + 1 WHERE nSalle = :NEW.nSalle; END IF; END; / CREATE OR REPLACE TRIGGER Trig_Après_U_Salle AFTER UPDATE OF nbPoste ON Salle FOR EACH ROW DECLARE differ NUMBER; BEGIN differ := :NEW.nbPoste – :OLD.nbPoste; UPDATE Segment SET nbPoste = nbPoste + differ WHERE indIP = :NEW.indIP; END; / Programmation de contraintes CREATE OR REPLACE TRIGGER Trig_Avant_UI_Installer BEFORE INSERT OR UPDATE OF nPoste, nLog ON installer FOR EACH ROW DECLARE type_log Types.typeLP%TYPE; type_pos Types.typeLP%TYPE; date_achat DATE; BEGIN SELECT typeLog, dateAch INTO type_log,date_achat FROM logiciel WHERE :NEW.nLog = nLog; SELECT typePoste INTO type_pos FROM Poste WHERE :NEW.nPoste = nPoste; SQL pour Oracle C.Soutou, O.Teste © Eyrolles 2004 20 IF type_log != type_pos THEN RAISE_APPLICATION_ERROR(-20111,’Poste : ‘||type_pos||’ différent du logiciel : ‘||type_log); END IF; IF :NEW.dateIns IS NOT NULL THEN IF :NEW.dateIns < date_achat THEN RAISE_APPLICATION_ERROR(-20112,'Installation ('|| :NEW.dateIns ||') inférieure a la date d''achat ('||date_achat||')'); END IF; END IF; END; / Chapitre 9 (JDBC) Curseur statique import java.util.ArrayList; public class ExoJDBC { public static Connection cx; public static ResultSet rs, rs2; public static Statement etat; public static Statement etatModifiable; public static CallableStatement cetat; public static ArrayList getSalles() { ArrayList tableauRésultat = new ArrayList(); try { etat = cx.createStatement(); rs = etat.executeQuery("SELECT * FROM Salle"); String [] ligne = null; while (rs.next()) { ligne = new String[4]; ligne[0] = rs.getString(1); ligne[1] = rs.getString(2); ligne[2] = (new Integer(rs.getInt(3))).toString(); ligne[3] = rs.getString(4); tableauRésultat.add(ligne); } rs.close(); etat.close(); } catch (SQLException ex) { while (ex != null) { System.out.println ("Statut SQL : "+ex.getSQLState()); System.out.println ("Message : "+ex.getMessage()); System.out.println ("Code erreur : "+ex.getErrorCode()); ex = ex.getNextException(); } } return tableauRésultat; } public static void main(String args[]) {try { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); cx = DriverManager.getConnection ("jdbc:oracle:thin:@CAMPAROLS:1521:BDSoutou","soutou","ingres");