import java.io.*;
import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class Ex4 {
  static void connexion() throws SQLException
  {
    Oracle.connect(
        "jdbc:oracle:thin:@s10:1521:orcl",
        "senellar_a",
        "senellar_a",
        true /* Autocommit */
        );
  }

  static void deconnexion() throws SQLException
  {
    Oracle.close();
  }

  static void selectInto() throws SQLException
  {
    int c;

    #sql {
      SELECT COUNT(*) INTO :c FROM t
    };

    System.out.println("Nombre de lignes de t : "+c);
  }

  static void miseAJour1() throws SQLException
  {
    #sql {
      UPDATE t SET age=age+1 WHERE nom='toto' OR age<=22
    };
  }

  static void miseAJour2() throws SQLException
  {
    System.out.println("Nom : ");
    String n=lireClavier();
    System.out.println("Âge : ");
    int a=Integer.valueOf(lireClavier());
    #sql {
      UPDATE t SET age=age+1 WHERE nom=:n OR age<=:a
    };
  }

  static void appelProc() throws SQLException
  {
    System.out.println("Nom : ");
    String n=lireClavier();
    #sql {
      CALL rajeunit(:n)
    };
  }

  static void appelFonct() throws SQLException
  {
    int c;
    #sql c = {
      VALUES f()
    };

    System.out.println("Nombre de lignes de t : "+c);
  }

  static void curseur() throws SQLException
  {
    #sql iterator NomIterator(String);

    NomIterator iter;
    
    #sql iter = {
      SELECT nom FROM t WHERE age > 20
    };

    while(true) {
      String n="";
      
      #sql {
        FETCH :iter INTO :n
      };

      if(iter.endFetch())
        break;

      System.out.println(n);
    }
  }

  static void sqldyn() throws SQLException 
  {
    System.out.println("Nom de table : ");
    String t=lireClavier();

    // Impossible en SQLJ, à faire en JDBC.  
  }
  
  static String lireClavier()
  {
    try {
      BufferedReader clavier =
        new BufferedReader(new InputStreamReader(System.in));
      return clavier.readLine();
    } catch(Exception e) {
      e.printStackTrace();
      System.exit(1);
      return "";
    }
  }

  public static void menu() throws Exception
  {
    while (true) {
      System.out.println("-----------------------------");
      System.out.println("Bienvenue dans le menu SQLJ");

      System.out.println("0 : terminer");

      System.out.println("1 : selectInto");

      System.out.println("2 : miseAJour1");
      System.out.println("3 : miseAJour2");

      System.out.println("4 : appelProc");
      System.out.println("5 : appelFonct");
      System.out.println("6 : curseur");

      System.out.println("7 : sqldyn");

      System.out.print("Entrez votre choix : ");

      int n = Integer.parseInt(lireClavier());

      switch (n) {
        case 0 : return;

        case 1: selectInto(); break; 

        case 2: miseAJour1(); break;
        case 3: miseAJour2(); break;

        case 4: appelProc(); break;
        case 5: appelFonct(); break;
        case 6: curseur(); break;

        case 7: sqldyn(); break;
      }
    }
  }
  
  public static void main(String[] args) throws Exception
  {
    connexion();

    menu();
    
    deconnexion();
  }
}