-- -----------------------------------------------------
    -- CREATE USERPACKAGE
    -- -----------------------------------------------------

    CREATE OR REPLACE PACKAGE USERPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADD
      (
        p_firstname USERS.firstname%TYPE,
        p_lastname USERS.lastname%TYPE,
        p_mail USERS.email%TYPE,
        p_mdp USERS.password%TYPE,
        p_address USERS.address%TYPE,
        p_birthday USERS.birthday%TYPE,
        p_registerDate USERS.registerDate%TYPE,
        p_relationship USERS.relationship%TYPE,
        p_phoneNumber USERS.phoneNumber%TYPE,
        p_gender USERS.gender%TYPE,
        p_int   erestedIn USERS.interestedIn%TYPE
      )
      RETURN NUMBER;
    PROCEDURE del
      (
        userid USERS.iduser%TYPE
      )
      ;
    PROCEDURE upd
      (
        userid USERS.iduser%TYPE,
        p_firstname USERS.firstname%TYPE,
        p_lastname USERS.lastname%TYPE,
        p_mail USERS.email%TYPE,
        p_mdp USERS.password%TYPE,
        p_address USERS.address%TYPE,
        p_birthday USERS.birthday%TYPE,
        p_registerDate USERS.registerDate%TYPE,
        p_relationship USERS.relationship%TYPE,
        p_phoneNumber USERS.phoneNumber%TYPE,
        p_gender USERS.gender%TYPE,
        p_interestedIn USERS.interestedIn%TYPE
      )
      ;
    FUNCTION get
      (
        userid USERS.iduser%TYPE
      )
      RETURN find_cursor;
    FUNCTION getAll
      RETURN find_cursor;
END USERPACKAGE;
/
CREATE OR REPLACE PACKAGE BODY USERPACKAGE
AS
  FUNCTION ADD
    (
      p_firstname USERS.firstname%TYPE,
      p_lastname USERS.lastname%TYPE,
      p_mail USERS.email%TYPE,
      p_mdp USERS.password%TYPE,
      p_address USERS.address%TYPE,
      p_birthday USERS.birthday%TYPE,
      p_registerDate USERS.registerDate%TYPE,
      p_relationship USERS.relationship%TYPE,
      p_phoneNumber USERS.phoneNumber%TYPE,
      p_gender USERS.gender%TYPE,
      p_interestedIn USERS.interestedIn%TYPE
    )
    RETURN NUMBER
  AS
    User_Existing EXCEPTION;
    PRAGMA EXCEPTION_INIT (User_Existing, -1998);
    p_count       NUMBER;
    usrid         NUMBER;
  BEGIN
    SELECT COUNT(*) INTO p_count FROM USERS WHERE email=p_mail;
  IF p_count > 0 THEN
    RAISE User_Existing;
  ELSE
    INSERT
    INTO USERS
      (
        firstname,
        lastname,
        email,
        password,
        address,
        birthday,
        registerDate,
        relationship,
        phoneNumber,
        gender,
        interestedIn
      )
      VALUES
      (
        p_firstname,
        p_lastname,
        p_mail,
        p_mdp,
        p_address,
        p_birthday,
        p_registerDate,
        p_relationship,
        p_phoneNumber,
        p_gender,
        p_interestedIn
      )
    RETURNING iduser
    INTO usrid;
  END IF;
  RETURN usrid;
   EXCEPTION
        WHEN User_Existing THEN
        RETURN -1;
END ADD;
PROCEDURE del
  (
    userid USERS.iduser%TYPE
  )
AS
BEGIN
  DELETE FROM users WHERE iduser=userid;
END del;
PROCEDURE upd(
    userid USERS.iduser%TYPE,
    p_firstname USERS.firstname%TYPE,
    p_lastname USERS.lastname%TYPE,
    p_mail USERS.email%TYPE,
    p_mdp USERS.password%TYPE,
    p_address USERS.address%TYPE,
    p_birthday USERS.birthday%TYPE,
    p_registerDate USERS.registerDate%TYPE,
    p_relationship USERS.relationship%TYPE,
    p_phoneNumber USERS.phoneNumber%TYPE,
    p_gender USERS.gender%TYPE,
    p_interestedIn USERS.interestedIn%TYPE)
AS
BEGIN
  UPDATE USERS
  SET firstname =p_firstname,
    lastname    =p_lastname,
    email       =p_mail,
    password    =p_mdp,
    address     =p_address,
    birthday    =p_birthday,
    registerDate=p_registerDate,
    relationship=p_relationship,
    phoneNumber =p_phoneNumber,
    gender      =p_gender,
    interestedIn=p_interestedIn
  WHERE iduser  =userid;
END upd;
FUNCTION get(
    userid USERS.iduser%TYPE)
  RETURN find_cursor
AS
  user_cursor find_cursor;
BEGIN
  OPEN user_cursor FOR SELECT iduser,
  firstname,
  lastname,
  email,
  password,
  address,
  birthday,
  registerDate,
  relationship,
  phoneNumber,
  gender,
  interestedIn FROM USERS WHERE iduser=userid;
  RETURN user_cursor;
END get;
FUNCTION getAll
  RETURN find_cursor
AS
  user_cursor find_cursor;
BEGIN
  OPEN user_cursor FOR SELECT iduser,
  firstname,
  lastname,
  email,
  password,
  address,
  birthday,
  registerDate,
  relationship,
  phoneNumber,
  gender,
  interestedIn FROM USERS;
  RETURN user_cursor;
END getAll;
END USERPACKAGE;