SET SERVEROUTPUT ON;

DROP TABLE emp;
CREATE TABLE emp
( matr NUMBER(10) NOT NULL,
  nom VARCHAR2(50) NOT NULL,
  sal number(7,2),  
  adresse VARCHAR2(96),
  dep NUMBER(10) NOT NULL,
  CONSTRAINT emp_pk PRIMARY KEY (matr)
);

INSERT INTO EMP VALUES (0,'Turing, Alan', 2446.13, 'Manchester, GB', 1);
INSERT INTO EMP VALUES (1,'Dijkstra, Edsger', 1779.88, 'Rotterdam, NL', 1);
insert into EMP VALUES (2,'Mac Lane, Saunders', 3141.16, 'Chicago, US', 2);

------------------------------------------------------------------------------------------------------------------------
-- procédure nommée

CREATE OR REPLACE PROCEDURE list_nom_emps IS
BEGIN
  DECLARE
    CURSOR c_nom_emps IS
      SELECT nom,adresse
      FROM emp;
  BEGIN
    FOR v_emp IN c_nom_emps LOOP
      dbms_output.put_line(
        'Client : ' || UPPER(v_emp.nom) ||
        ' Ville : ' || v_emp.adresse);
    END LOOP;
  END;
END;
/

CALL list_nom_emps();

------------------------------------------------------------------------------------------------------------------------
-- procédure nommée avec paramètres

CREATE OR REPLACE PROCEDURE
  liste_nom_emps(ville IN varchar2, v_result OUT number) IS
BEGIN
  BEGIN
      SELECT COUNT(*) INTO v_result
      FROM emp
      WHERE adresse LIKE ('%' || ville || '%') ;
  END;
END;
/


DECLARE
  v_result number;
BEGIN
  liste_nom_emps('Manchester',v_result);
  dbms_output.put_line(v_result);
END;
/

------------------------------------------------------------------------------------------------------------------------
-- fonction nommée
CREATE OR REPLACE FUNCTION nb_emps
  RETURN NUMBER -- Type de retour
  IS
BEGIN
  DECLARE
    i NUMBER;
  BEGIN
    SELECT COUNT(*) INTO i
    FROM emp;
    RETURN i;
  END;
END;
/

DECLARE
BEGIN
  dbms_output.put_line(nb_emps());
END;
/
------------------------------------------------------------------------------------------------------------------------
-- fonction nommée avec paramètre 
CREATE OR REPLACE FUNCTION euro_to_fr(v_somme IN number)
  RETURN NUMBER
  IS
BEGIN    
  DECLARE
    taux CONSTANT number := 6.55957;
  BEGIN
    RETURN v_somme * taux;
  END;
END;
/

DECLARE
BEGIN
  dbms_output.put_line(euro_to_fr(15.24));
END;
/
------------------------------------------------------------------------------------------------------------------------
-- quelques fonctionnalités pratiques
SELECT * FROM user_source;

DESC liste_nom_emps;

SELECT euro_to_fr(15.24)
FROM DUAL;

SELECT euro_to_fr(:nb)
FROM DUAL;

DROP FUNCTION euro_to_fr;

------------------------------------------------------------------------------------------------------------------------
-- gestion des exceptions
DECLARE
  e_custom EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_custom, -20001 );
BEGIN
  -- raise_application_error(-20001, 'My custom error');
  RAISE e_custom;
EXCEPTION
  WHEN e_custom THEN
    dbms_output.put_line('A /custom/ error was encountered- '
      ||SQLCODE||' : '||SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line('An error was encountered - '
      ||SQLCODE||' : '||SQLERRM);
END;
/

------------------------------------------------------------------------------------------------------------------------
-- exemple d'exceptions
DECLARE
  v_salaire emp.sal%TYPE;  
  e_trop_bas EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_trop_bas, -20001 );
BEGIN
  SELECT sal INTO v_salaire
  FROM emp
  WHERE matr = 1;
  IF v_salaire < 2000 THEN
    RAISE e_trop_bas;
  END IF;
EXCEPTION
  WHEN e_trop_bas THEN
    dbms_output.put_line('A /custom/ error was encountered - '
      ||SQLCODE||' : salaire trop bas');
  WHEN OTHERS THEN
    dbms_output.put_line('An error was encountered - '
      ||SQLCODE||' : '||SQLERRM);
END;
/
