-- ====================================================
-- Suppression des objets de schéma
-- ====================================================
DROP TABLE exe_ami;
DROP SEQUENCE sq_exe_ami;
-- ====================================================
-- Table : exe_AMI
-- ====================================================
CREATE TABLE exe_ami (
		ami_no NUMBER(8) CONSTRAINT pk_exe_ami PRIMARY KEY,
    ami_nom VARCHAR2(30),
		ami_pre VARCHAR2(30),
		ami_mai VARCHAR2(50)		
);

-- ====================================================
-- Insertion Table : exe_AMI
-- ====================================================
INSERT INTO exe_ami VALUES (1, 'Dupont', 'Jean', 'dupont86@gmail.com');
INSERT INTO exe_ami VALUES (2, 'Sinclair', 'François', 'sinclair@bluewin.ch');
INSERT INTO exe_ami VALUES (3, 'Blaise', 'Pascal', 'blaise345@caramail.com');
INSERT INTO exe_ami VALUES (4, 'Coste', 'Jeanne', 'costej@msn.com');
INSERT INTO exe_ami VALUES (5, 'Schmidt', 'Pascal', 'pascals@hotmail.com');
INSERT INTO exe_ami VALUES (6, 'Martin', 'Elodie', 'elodie.martin@gmail.com');
INSERT INTO exe_ami VALUES (7, 'Nicolet', 'Anne', 'nico.anne@caramail.com');
INSERT INTO exe_ami VALUES (8, 'Bergeret', 'Laurent', 'laurent456@gmail.com');
INSERT INTO exe_ami VALUES (9, 'Ingold', 'Samuel', 'samingold@bluewin.ch');
INSERT INTO exe_ami VALUES (10, 'Maret', 'Alexandre', 'alex.maret@gmai.com');

COMMIT;

-- ====================================================
-- Vue d'affichage des amis
-- ====================================================
CREATE OR REPLACE VIEW vw_exe_ami(Nom, Prénom, Adresse_Mail) AS
       SELECT ami_nom, ami_pre, ami_mai
       FROM exe_ami 
       ORDER BY ami_nom
;
-- ====================================================
-- Sequence + Trigger Ami
-- ====================================================

CREATE SEQUENCE   sq_exe_ami  MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 11 CACHE 20 NOORDER  NOCYCLE;
/

CREATE OR REPLACE TRIGGER  tr_exe_ami 
  BEFORE INSERT ON exe_ami              
  FOR EACH ROW
BEGIN   
    SELECT sq_exe_ami.nextval INTO :NEW.ami_no FROM dual; 
END; 

/
ALTER TRIGGER  tr_exe_ami ENABLE;
/

-- ====================================================
-- PACKAGE + FUNCTION + PROCEDURE
-- ====================================================


CREATE OR REPLACE PACKAGE pkg_functions AS 
  FUNCTION fnc_getNombreAmis RETURN NUMBER;   
  PROCEDURE prc_insertAmi(i_ami_nom IN exe_ami.ami_nom%TYPE, i_ami_prenom IN exe_ami.ami_pre%TYPE, i_ami_mai IN exe_ami.ami_mai%TYPE);    
END pkg_functions;
/
CREATE OR REPLACE PACKAGE BODY pkg_functions AS

  FUNCTION fnc_getNombreAmis RETURN NUMBER IS
    nombre NUMBER;
  BEGIN
      SELECT COUNT(*) INTO nombre FROM vw_exe_ami;
      RETURN (nombre);
  END fnc_getNombreAmis;

  PROCEDURE prc_insertAmi(i_ami_nom IN exe_ami.ami_nom%TYPE, i_ami_prenom IN exe_ami.ami_pre%TYPE, i_ami_mai IN exe_ami.ami_mai%TYPE) IS
  BEGIN
    INSERT INTO exe_ami (ami_nom, ami_pre, ami_mai) VALUES (i_ami_nom, i_ami_prenom, i_ami_mai);
  END prc_insertAmi;

END pkg_functions;