CREATE OR REPLACE TRIGGER NEW_USER BEFORE
  INSERT ON USERS FOR EACH ROW BEGIN :NEW.iduser:=SEQ_users.NEXTVAL;
END;
/
CREATE OR REPLACE PACKAGE USERPACKAGE
IS
TYPE RFC
IS
  REF
  CURSOR;
    FUNCTION ADD
      (
        firstname USERS.firstname%TYPE,
        lastname USERS.lastname%TYPE,
        address USERS.address%TYPE,
        birthday USERS.birthday%TYPE,
        registerDate USERS.registerDate%TYPE,
        relationship USERS.relationshio%TYPE,
        phoneNumber USERS.phoneNumber%TYPE,
        gender USERS.gender%TYPE,
        interestedIn USERS.interestedIn%TYPE
      )
      RETURN NUMBER;
    PROCEDURE del
      (
        userid USERS.iduser%TYPE
      )
      ;
    PROCEDURE upd
      (
        userid USERS.iduser%TYPE,
        firstname USERS.firstname%TYPE,
        lastname USERS.lastname%TYPE,
        address USERS.address%TYPE,
        birthday USERS.birthday%TYPE,
        registerDate USERS.registerDate%TYPE,
        relationship USERS.relationshio%TYPE,
        phoneNumber USERS.phoneNumber%TYPE,
        gender USERS.gender%TYPE,
        interestedIn USERS.interestedIn%TYPE
      )
      ;
    FUNCTION get
      (
        userid USERS.iduser%TYPE
      )
      RETURN rfc;
    FUNCTION getAll
      RETURN rfc;
  END GETALL;
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;
    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_password,
        p_address,
        p_birthday,
        p_registerDate,
        p_relationship,
        p_phoneNumber,
        p_gender,
        p_interestedIn
      )
    RETURNING iduser
    INTO usrid;
  END IF;
RETURN usrid;
END ADD;