Pasted

    -- Thu Dec 27 22:09:00 2018
    -- -----------------------------------------------------
    -- ALTER TABLE TO DELETE CONSTRAINT
    -- -----------------------------------------------------
    ALTER TABLE FRIENDS DROP CONSTRAINT fk_user_has_user_user;
    ALTER TABLE FRIENDS DROP CONSTRAINT fk_user_has_user_user1;
    ALTER TABLE POST DROP CONSTRAINT fk_post_user1;
    ALTER TABLE COMMENTS DROP CONSTRAINT fk_comments_post1;
    ALTER TABLE COMMENTS DROP CONSTRAINT fk_comments_user1;
    ALTER TABLE SCHOOLING DROP CONSTRAINT fk_user_has_School_user1;
    ALTER TABLE SCHOOLING DROP CONSTRAINT fk_user_has_School_School1;
    ALTER TABLE WORK DROP CONSTRAINT fk_user_has_Company_user1;
    ALTER TABLE WORK DROP CONSTRAINT fk_user_has_Company_Company1;
    ALTER TABLE LIKECOMMENTS DROP CONSTRAINT fk_like_user1;
    ALTER TABLE LIKECOMMENTS DROP CONSTRAINT fk_like_comments1;
    ALTER TABLE LIKEPOST DROP CONSTRAINT fk_likePost_user1;
    ALTER TABLE LIKEPOST DROP CONSTRAINT fk_likePost_post1;
 
    -- -----------------------------------------------------
    -- DROP TABLE TO DELETE TABLE
    -- -----------------------------------------------------
    DROP TABLE USERS;
    DROP TABLE FRIENDS;
    DROP TABLE POST;
    DROP TABLE COMMENTS;
    DROP TABLE SCHOOL;
    DROP TABLE SCHOOLING;
    DROP TABLE COMPANY;
    DROP TABLE WORK;
    DROP TABLE LIKEPOST;
    DROP TABLE LIKECOMMENTS;
 
    -- -----------------------------------------------------
    -- DROP SEQUENCE TO DELETE SEQUENCE
    -- -----------------------------------------------------
    DROP SEQUENCE SEQ_users; 
    DROP SEQUENCE SEQ_company;
    DROP SEQUENCE SEQ_school;
    DROP SEQUENCE SEQ_likeComments;
    DROP SEQUENCE SEQ_likePosts;
    DROP SEQUENCE SEQ_post;
    DROP SEQUENCE SEQ_comments;
 
    -- -----------------------------------------------------
    -- Table user
    -- -----------------------------------------------------
    CREATE TABLE USERS
      (
        iduser       INT NOT NULL,
        email        VARCHAR(100) NOT NULL,
        password     VARCHAR(100) NOT NULL,
        firstname    VARCHAR(45) NULL,
        lastname     VARCHAR(45) NULL,
        address      VARCHAR(500) NULL,
        birthday     DATE NULL,
        registerDate DATE NULL,
        relationship NUMBER(1) NULL,
        phoneNumber  INT NULL,
        gender       NUMBER(1) NULL,
        interestedIn NUMBER(1) NULL,
        PRIMARY KEY (iduser)
      );
 
    -- -----------------------------------------------------
    -- Table friends
    -- -----------------------------------------------------
    CREATE TABLE friends
      (
        iduser   INT NOT NULL,
        iduser1  INT NOT NULL,
        accepted NUMBER(1) NULL,
        PRIMARY KEY (iduser, iduser1),
        CONSTRAINT fk_user_has_user_user FOREIGN KEY (iduser) REFERENCES USERS (iduser),
        CONSTRAINT fk_user_has_user_user1 FOREIGN KEY (iduser1) REFERENCES USERS (iduser)
      );
      
    -- -----------------------------------------------------
    -- Table post
    -- -----------------------------------------------------
    CREATE TABLE post
      (
        idpost   INT NOT NULL ,
        data     VARCHAR(45) NULL,
        TYPE     VARCHAR(45) NULL,
        postDate DATE NULL,
        iduser   INT NOT NULL,
        PRIMARY KEY (idpost),
        CONSTRAINT fk_post_user1 FOREIGN KEY (iduser) REFERENCES USERS (iduser)
      );
      
    -- -----------------------------------------------------
    -- Table comments
    -- -----------------------------------------------------
    CREATE TABLE comments
      (
        idcomments INT NOT NULL ,
        data       VARCHAR(45) NULL,
        TYPE       VARCHAR(45) NULL,
        postDate   DATE NULL,
        idpost     INT NOT NULL,
        iduser     INT NOT NULL,
        PRIMARY KEY (idcomments),
        CONSTRAINT fk_comments_post1 FOREIGN KEY (idpost) REFERENCES post (idpost),
        CONSTRAINT fk_comments_user1 FOREIGN KEY (iduser) REFERENCES USERS (iduser)
      );
      
    -- -----------------------------------------------------
    -- Table School
    -- -----------------------------------------------------
    CREATE TABLE School
      (
        idSchool INT NOT NULL ,
        name     VARCHAR(45) NULL,
        address  VARCHAR(45) NULL,
        TYPE     VARCHAR(45) NULL,
        PRIMARY KEY (idSchool)
      );
      
    -- -----------------------------------------------------
    -- Table Schooling
    -- -----------------------------------------------------
    CREATE TABLE Schooling
      (
        iduser    INT NOT NULL,
        idSchool  INT NOT NULL,
        beginDate DATE NULL,
        endDate   DATE NULL,
        graduate  NUMBER(1) NULL,
        PRIMARY KEY (iduser, idSchool),
        CONSTRAINT fk_user_has_School_user1 FOREIGN KEY (iduser) REFERENCES USERS (iduser),
        CONSTRAINT fk_user_has_School_School1 FOREIGN KEY (idSchool) REFERENCES School (idSchool)
      );
      
    -- -----------------------------------------------------
    -- Table Company
    -- -----------------------------------------------------
    CREATE TABLE Company
      (
        idCompany INT NOT NULL,
        name      VARCHAR(45) NULL,
        address   VARCHAR(45) NULL,
        PRIMARY KEY (idCompany)
      );
      
    -- -----------------------------------------------------
    -- Table work
    -- -----------------------------------------------------
    CREATE TABLE WORK
      (
        iduser    INT NOT NULL,
        idCompany INT NOT NULL,
        beginDate DATE NULL,
        endDate   DATE NULL,
        jobTitle  VARCHAR(45) NULL,
        PRIMARY KEY (iduser, idCompany),
        CONSTRAINT fk_user_has_Company_user1 FOREIGN KEY (iduser) REFERENCES USERS (iduser),
        CONSTRAINT fk_user_has_Company_Company1 FOREIGN KEY (idCompany) REFERENCES Company (idCompany)
      );
      
    -- -----------------------------------------------------
    -- Table likeComments
    -- -----------------------------------------------------
    CREATE TABLE likeComments
      (
        idlike     INT NOT NULL,
        dateLiked  DATE NULL,
        iduser     INT NOT NULL,
        idcomments INT NOT NULL,
        PRIMARY KEY (idlike),
        CONSTRAINT fk_like_user1 FOREIGN KEY (iduser) REFERENCES USERS (iduser),
        CONSTRAINT fk_like_comments1 FOREIGN KEY (idcomments) REFERENCES comments (idcomments)
      );
      
    -- -----------------------------------------------------
    -- Table likePost
    -- -----------------------------------------------------
    CREATE TABLE likePost
      (
        idlikePost INT NOT NULL,
        dateLiked  DATE NULL,
        iduser     INT NOT NULL,
        idpost     INT NOT NULL,
        PRIMARY KEY (idlikePost),
        CONSTRAINT fk_likePost_user1 FOREIGN KEY (iduser) REFERENCES USERS (iduser),
        CONSTRAINT fk_likePost_post1 FOREIGN KEY (idpost) REFERENCES post (idpost)
      );
     
    -- -----------------------------------------------------
    -- CREATE SEQUENCE TO INCREMENT PRIMARY KEY 
    -- -----------------------------------------------------
    CREATE SEQUENCE SEQ_users START WITH 1 INCREMENT BY 1;
    CREATE SEQUENCE SEQ_company START WITH 1 INCREMENT BY 1;
    CREATE SEQUENCE SEQ_school START WITH 1 INCREMENT BY 1;
    CREATE SEQUENCE SEQ_likeComments START WITH 1 INCREMENT BY 1;
    CREATE SEQUENCE SEQ_likePosts START WITH 1 INCREMENT BY 1;
    CREATE SEQUENCE SEQ_post START WITH 1 INCREMENT BY 1;
    CREATE SEQUENCE SEQ_comments START WITH 1 INCREMENT BY 1;
    -- -----------------------------------------------------
    -- CREATE TRIGGER
    -- -----------------------------------------------------
 
    CREATE OR REPLACE TRIGGER NEW_USER BEFORE
      INSERT ON USERS FOR EACH ROW BEGIN :NEW.iduser:=SEQ_users.NEXTVAL;
    END;
    /
    CREATE OR REPLACE TRIGGER NEW_POST BEFORE
      INSERT ON POST FOR EACH ROW BEGIN :NEW.idpost:=SEQ_post.NEXTVAL;
    END;
    /
    CREATE OR REPLACE TRIGGER NEW_COMMENT BEFORE
      INSERT ON COMMENTS FOR EACH ROW BEGIN :NEW.idcomments:=SEQ_comments.NEXTVAL;
    END;
    /
    CREATE OR REPLACE TRIGGER NEW_COMPANY BEFORE
      INSERT ON COMPANY FOR EACH ROW BEGIN :NEW.idCompany:=SEQ_company.NEXTVAL;
    END;
    /
    CREATE OR REPLACE TRIGGER NEW_SCHOOL BEFORE
      INSERT ON SCHOOL FOR EACH ROW BEGIN :NEW.idSchool:=SEQ_school.NEXTVAL;
    END;
    /
    CREATE OR REPLACE TRIGGER NEW_LIKEPOSTS BEFORE
      INSERT ON LIKEPOST FOR EACH ROW BEGIN :NEW.idlikePost:=SEQ_likePosts.NEXTVAL;
    END;
    /
    CREATE OR REPLACE TRIGGER NEW_LIKECOMMENTS BEFORE
      INSERT ON LIKECOMMENTS FOR EACH ROW BEGIN :NEW.idlike:=SEQ_likeComments.NEXTVAL;
    END;
    /
    -- -----------------------------------------------------
    -- 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 * 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 * FROM USERS;
  RETURN user_cursor;
END getAll;
END USERPACKAGE;
-- -----------------------------------------------------
-- CREATE POSTPACKAGE
-- -----------------------------------------------------
CREATE OR REPLACE PACKAGE POSTPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADD(
        p_data POST.data%TYPE,
        p_type POST.type%TYPE,
        p_postDate POST.postDate%TYPE,
        p_iduser POST.iduser%TYPE )
      RETURN NUMBER;
    PROCEDURE del(
        p_idpost POST.idpost%TYPE ) ;
    PROCEDURE upd(
        p_idpost POST.idpost%TYPE,
        p_data POST.data%TYPE,
        p_type POST.type%TYPE,
        p_postDate POST.postDate%TYPE,
        p_iduser POST.iduser%TYPE ) ;
    FUNCTION get(
        p_idpost POST.idpost%TYPE )
      RETURN find_cursor;
    FUNCTION getAll
      RETURN find_cursor;
  END POSTPACKAGE;
  /
CREATE OR REPLACE PACKAGE BODY POSTPACKAGE
AS
  FUNCTION ADD(
      p_data POST.data%TYPE,
      p_type POST.type%TYPE,
      p_postDate POST.postDate%TYPE,
      p_iduser POST.iduser%TYPE )
    RETURN NUMBER
  AS
    idp NUMBER;
  BEGIN
    INSERT
    INTO POST
      (
        data,
        TYPE,
        postDate,
        iduser
      )
      VALUES
      (
        p_data,
        p_type,
        p_postDate,
        p_iduser
      )
    RETURNING idpost
    INTO idp;
  RETURN idp;
END ADD;
  PROCEDURE del
    (
      p_idpost POST.idpost%TYPE
    )
  AS
  BEGIN
    DELETE FROM POST WHERE idpost=p_idpost;
  END del;
  PROCEDURE upd(
      p_idpost POST.idpost%TYPE,
      p_data POST.data%TYPE,
      p_type POST.type%TYPE,
      p_postDate POST.postDate%TYPE,
      p_iduser POST.iduser%TYPE )
  AS
  BEGIN
    UPDATE POST
    SET data     =p_data,
      TYPE       =p_type,
      postDate   =p_postDate,
      iduser     =p_iduser
    WHERE idpost =p_idpost;
  END upd;
  FUNCTION get(
      p_idpost POST.idpost%TYPE)
    RETURN find_cursor
  AS
    post_cursor find_cursor;
  BEGIN
    OPEN post_cursor FOR SELECT idpost,
    data,
  TYPE,
  postDate,
  iduser FROM POST WHERE idpost=p_idpost;
  RETURN post_cursor;
END get;
  FUNCTION getAll
    RETURN find_cursor
  AS
    post_cursor find_cursor;
  BEGIN
    OPEN post_cursor FOR SELECT idpost,
    data,
  TYPE,
  postDate,
  iduser FROM POST;
  RETURN post_cursor;
END getAll;
END POSTPACKAGE;
-- -----------------------------------------------------
-- CREATE COMMENTPACKAGE
-- -----------------------------------------------------
CREATE OR REPLACE PACKAGE COMMENTPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADD(
        p_data COMMENTS.data%TYPE,
        p_type COMMENTS.type%TYPE,
        p_postDate COMMENTS.postDate%TYPE,
        p_idpost COMMENTS.idpost%TYPE,
        p_iduser COMMENTS.iduser%TYPE )
      RETURN NUMBER;
    PROCEDURE del(
        p_idcomments COMMENTS.idcomments%TYPE ) ;
    PROCEDURE upd(
        p_idcomments COMMENTS.idcomments%TYPE,
        p_data COMMENTS.data%TYPE,
        p_type COMMENTS.type%TYPE,
        p_postDate COMMENTS.postDate%TYPE,
        p_idpost COMMENTS.idpost%TYPE,
        p_iduser COMMENTS.iduser%TYPE ) ;
    FUNCTION get(
        p_idcomments COMMENTS.idcomments%TYPE )
      RETURN find_cursor;
    FUNCTION getAll
      RETURN find_cursor;
  END COMMENTPACKAGE;
  /
CREATE OR REPLACE PACKAGE BODY COMMENTPACKAGE
AS
  FUNCTION ADD(
      p_data COMMENTS.data%TYPE,
      p_type COMMENTS.type%TYPE,
      p_postDate COMMENTS.postDate%TYPE,
      p_idpost COMMENTS.idpost%TYPE,
      p_iduser COMMENTS.iduser%TYPE )
    RETURN NUMBER
  AS
    comid NUMBER;
  BEGIN
    INSERT
    INTO COMMENTS
      (
        data,
        TYPE,
        postDate,
        idpost,
        iduser
      )
      VALUES
      (
        p_data,
        p_type,
        p_postDate,
        p_idpost,
        p_iduser
      )
    RETURNING idcomments
    INTO comid;
  RETURN comid;
END ADD;
  PROCEDURE del
    (
      p_idcomments COMMENTS.idcomments%TYPE
    )
  AS
  BEGIN
    DELETE FROM COMMENTS WHERE idcomments=p_idcomments;
  END del;
  PROCEDURE upd(
      p_idcomments COMMENTS.idcomments%TYPE,
      p_data COMMENTS.data%TYPE,
      p_type COMMENTS.type%TYPE,
      p_postDate COMMENTS.postDate%TYPE,
      p_idpost COMMENTS.idpost%TYPE,
      p_iduser COMMENTS.iduser%TYPE )
  AS
  BEGIN
    UPDATE COMMENTS
    SET data     =p_data,
      TYPE       =p_type,
      postDate   =p_postDate,
      idpost     =p_idpost,
      iduser     =p_iduser
    WHERE idcomments =p_idcomments;
  END upd;
  FUNCTION get(
      p_idcomments COMMENTS.idcomments%TYPE)
    RETURN find_cursor
  AS
    comments_cursor find_cursor;
  BEGIN
    OPEN comments_cursor FOR SELECT idcomments,
    data,
  TYPE,
  postDate,
  idpost,
  iduser FROM COMMENTS WHERE idcomments=p_idcomments;
  RETURN comments_cursor;
END get;
  FUNCTION getAll
    RETURN find_cursor
  AS
    comments_cursor find_cursor;
  BEGIN
    OPEN comments_cursor FOR SELECT idcomments,
    data,
  TYPE,
  postDate,
  idpost,
  iduser FROM COMMENTS;
  RETURN comments_cursor;
END getAll;
END COMMENTPACKAGE;
-- -----------------------------------------------------
-- CREATE FRIENDSPACKAGE
-- -----------------------------------------------------
CREATE OR REPLACE PACKAGE FRIENDSPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADD(
        p_asker FRIENDS.iduser%TYPE,
        p_receiver FRIENDS.iduser1%TYPE,
        p_accepted FRIENDS.accepted%TYPE )
        RETURN NUMBER;
    PROCEDURE del(
        p_asker FRIENDS.iduser%TYPE,
        p_receiver FRIENDS.iduser1%TYPE ) ;
    PROCEDURE upd(
        p_asker FRIENDS.iduser%TYPE,
        p_receiver FRIENDS.iduser1%TYPE,
        p_accepted FRIENDS.accepted%TYPE ) ;
    FUNCTION get(
        p_asker FRIENDS.iduser%TYPE,
        p_receiver FRIENDS.iduser1%TYPE )
      RETURN find_cursor;
    FUNCTION getAll
      RETURN find_cursor;
  END FRIENDSPACKAGE;
  /
CREATE OR REPLACE PACKAGE BODY FRIENDSPACKAGE
AS
  FUNCTION ADD(
      p_asker FRIENDS.iduser%TYPE,
      p_receiver FRIENDS.iduser1%TYPE,
      p_accepted FRIENDS.accepted%TYPE )
    RETURN NUMBER
  AS
    alreadyAsk_Ex EXCEPTION;
    PRAGMA EXCEPTION_INIT (alreadyAsk_Ex, -1996);
    p_count       NUMBER;
  BEGIN
    SELECT COUNT(*) INTO p_count FROM FRIENDS WHERE iduser=p_asker AND iduser1=p_receiver;
  IF p_count > 0 THEN
    RAISE alreadyAsk_Ex;
  ELSE
    INSERT
    INTO FRIENDS
      (
        iduser,
        iduser1,
        accepted
      )
      VALUES
      (
        p_asker,
        p_receiver,
        p_accepted
      );
  END IF;
  RETURN 1;
   EXCEPTION
        WHEN alreadyAsk_Ex THEN
        RETURN -1;
END ADD;
  PROCEDURE del
    (
      p_asker FRIENDS.iduser%TYPE,
      p_receiver FRIENDS.iduser1%TYPE
    )
  AS
  BEGIN
    DELETE FROM FRIENDS WHERE iduser=p_asker AND iduser1=p_receiver;
  END del;
  PROCEDURE upd(
      p_asker FRIENDS.iduser%TYPE,
        p_receiver FRIENDS.iduser1%TYPE,
        p_accepted FRIENDS.accepted%TYPE )
  AS
  BEGIN
    UPDATE FRIENDS
    SET accepted     =p_accepted
    WHERE iduser =p_asker AND iduser1=p_receiver;
  END upd;
  FUNCTION get(
      p_asker FRIENDS.iduser%TYPE,
        p_receiver FRIENDS.iduser1%TYPE)
    RETURN find_cursor
  AS
    friends_cursor find_cursor;
  BEGIN
    OPEN friends_cursor FOR SELECT iduser,
    iduser1,
  accepted FROM FRIENDS WHERE iduser =p_asker AND iduser1=p_receiver;
  RETURN friends_cursor;
END get;
  FUNCTION getAll
    RETURN find_cursor
  AS
    friends_cursor find_cursor;
  BEGIN
    OPEN friends_cursor FOR SELECT iduser,
    iduser1,
  accepted FROM FRIENDS;
  RETURN friends_cursor;
END getAll;
END FRIENDSPACKAGE;
-- -----------------------------------------------------
-- CREATE SCHOOLINGPACKAGE
-- -----------------------------------------------------
CREATE OR REPLACE PACKAGE SCHOOLINGPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADD(
        p_user SCHOOLING.iduser%TYPE,
        p_name SCHOOL.name%TYPE,
        p_address SCHOOL.address%TYPE,
        p_type SCHOOL.type%TYPE,
        p_beginDate SCHOOLING.beginDate%TYPE,
        p_endDate SCHOOLING.endDate%TYPE,
        p_graduate SCHOOLING.graduate%TYPE)
      RETURN NUMBER;
    PROCEDURE del(
        p_iduser SCHOOLING.iduser%TYPE,
        p_idschool SCHOOLING.idSchool%TYPE ) ;
    PROCEDURE upd(
        p_iduser SCHOOLING.iduser%TYPE,
        p_idschool SCHOOLING.idSchool%TYPE,
        p_beginDate SCHOOLING.beginDate%TYPE,
        p_endDate SCHOOLING.endDate%TYPE,
        p_graduate SCHOOLING.graduate%TYPE ) ;
    FUNCTION get(
        p_iduser SCHOOLING.iduser%TYPE,
        p_idschool SCHOOLING.idSchool%TYPE )
      RETURN find_cursor;
    FUNCTION getAll
      RETURN find_cursor;
  END SCHOOLINGPACKAGE;
  /
CREATE OR REPLACE PACKAGE BODY SCHOOLINGPACKAGE
AS
  FUNCTION ADD(
      p_user SCHOOLING.iduser%TYPE,
      p_name SCHOOL.name%TYPE,
      p_address SCHOOL.address%TYPE,
      p_type SCHOOL.type%TYPE,
      p_beginDate SCHOOLING.beginDate%TYPE,
      p_endDate SCHOOLING.endDate%TYPE,
      p_graduate SCHOOLING.graduate%TYPE )
    RETURN NUMBER
  AS
    CURSOR c_school
    IS
      SELECT idSchool
      FROM SCHOOL
      WHERE name =p_name
      AND address=p_address
      AND TYPE   =p_type;
    cur_school SCHOOL.IDSCHOOL%TYPE;
    c_idschool NUMBER;
    idschling  NUMBER;
  BEGIN
    OPEN c_school;
    FETCH c_school INTO cur_school;
    IF c_school%FOUND THEN
      c_idschool:=cur_school;
    ELSE
      INSERT
      INTO SCHOOL
        (
          name,
          address,
          TYPE
        )
        VALUES
        (
          p_name,
          p_address,
          p_type
        )
      RETURNING idSchool
      INTO c_idSchool;
    END IF;
    INSERT
    INTO SCHOOLING
      (
        iduser,
        idSchool,
        beginDate,
        endDate,
        graduate
      )
      VALUES
      (
        p_user,
        c_idschool,
        p_beginDate,
        p_endDate,
        p_graduate
      );
    RETURN 1;
  END ADD;
  PROCEDURE del
    (
      p_iduser SCHOOLING.iduser%TYPE,
      p_idschool SCHOOLING.idSchool%TYPE
    )
  AS
  BEGIN
    DELETE FROM SCHOOLING WHERE iduser=p_iduser AND idSchool=p_idschool;
  END del;
  PROCEDURE upd(
      p_iduser SCHOOLING.iduser%TYPE,
      p_idschool SCHOOLING.idSchool%TYPE,
      p_beginDate SCHOOLING.beginDate%TYPE,
      p_endDate SCHOOLING.endDate%TYPE,
      p_graduate SCHOOLING.graduate%TYPE )
  AS
  BEGIN
    UPDATE SCHOOLING
    SET beginDate =p_beginDate,
      endDate     =p_endDate,
      graduate    =p_graduate
    WHERE iduser  =p_iduser
    AND idSchool  =p_idschool;
  END upd;
  FUNCTION get(
      p_iduser SCHOOLING.iduser%TYPE,
      p_idschool SCHOOLING.idSchool%TYPE)
    RETURN find_cursor
  AS
    schooling_cursor find_cursor;
  BEGIN
    OPEN schooling_cursor FOR SELECT SCHOOLING.iduser,
    SCHOOLING.idSchool,
    SCHOOLING.beginDate,
    SCHOOLING.endDate,
    SCHOOLING.graduate,
    SCHOOL.name,
    SCHOOL.address,
    SCHOOL.TYPE FROM SCHOOLING INNER JOIN SCHOOL ON SCHOOLING.idSchool=SCHOOL.idSchool WHERE iduser =p_iduser AND SCHOOLING.idSchool=p_idschool;
    RETURN schooling_cursor;
  END get;
  FUNCTION getAll
    RETURN find_cursor
  AS
    schooling_cursor find_cursor;
  BEGIN
    OPEN schooling_cursor FOR SELECT SCHOOLING.iduser,
    SCHOOLING.idSchool,
    SCHOOLING.beginDate,
    SCHOOLING.endDate,
    SCHOOLING.graduate,
    SCHOOL.name,
    SCHOOL.address,
    SCHOOL.TYPE FROM SCHOOLING INNER JOIN SCHOOL ON SCHOOLING.idSchool=SCHOOL.idSchool;
    RETURN schooling_cursor;
  END getAll;
END SCHOOLINGPACKAGE;
-- -----------------------------------------------------
-- CREATE WORKPACKAGE
-- -----------------------------------------------------
CREATE OR REPLACE PACKAGE WORKPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADD(
        p_user WORK.iduser%TYPE,
        p_name COMPANY.name%TYPE,
        p_address COMPANY.address%TYPE,
        p_beginDate WORK.beginDate%TYPE,
        p_endDate WORK.endDate%TYPE,
        p_jobTitle WORK.jobTitle%TYPE)
      RETURN NUMBER;
    PROCEDURE del(
        p_iduser WORK.iduser%TYPE,
        p_idCompany WORK.idCompany%TYPE) ;
    PROCEDURE upd(
        p_iduser WORK.iduser%TYPE,
        p_idCompany WORK.idCompany%TYPE,
        p_beginDate WORK.beginDate%TYPE,
        p_endDate WORK.endDate%TYPE,
        p_jobTitle WORK.jobTitle%TYPE ) ;
    FUNCTION get(
        p_iduser WORK.iduser%TYPE,
        p_idCompany WORK.idCompany%TYPE )
      RETURN find_cursor;
    FUNCTION getAll
      RETURN find_cursor;
  END WORKPACKAGE;
  /
CREATE OR REPLACE PACKAGE BODY WORKPACKAGE
AS
  FUNCTION ADD(
      p_user WORK.iduser%TYPE,
      p_name COMPANY.name%TYPE,
      p_address COMPANY.address%TYPE,
      p_beginDate WORK.beginDate%TYPE,
      p_endDate WORK.endDate%TYPE,
      p_jobTitle WORK.jobTitle%TYPE )
    RETURN NUMBER
  AS
    CURSOR c_company
    IS
      SELECT idCompany FROM COMPANY WHERE name =p_name AND address=p_address;
    cur_company COMPANY.idCompany%TYPE;
    c_idCompany NUMBER;
  BEGIN
    OPEN c_company;
    FETCH c_company INTO cur_company;
    IF c_company%FOUND THEN
      c_idCompany:=cur_company;
    ELSE
      INSERT
      INTO COMPANY
        (
          name,
          address
        )
        VALUES
        (
          p_name,
          p_address
        )
      RETURNING idCompany
      INTO c_idCompany;
    END IF;
    INSERT
    INTO WORK
      (
        iduser,
        idCompany,
        beginDate,
        endDate,
        jobTitle
      )
      VALUES
      (
        p_user,
        c_idCompany,
        p_beginDate,
        p_endDate,
        p_jobTitle
      );
    RETURN 1;
  END ADD;
  PROCEDURE del
    (
      p_iduser WORK.iduser%TYPE,
      p_idCompany WORK.idCompany%TYPE
    )
  AS
  BEGIN
    DELETE FROM WORK WHERE iduser=p_iduser AND idCompany=p_idCompany;
  END del;
  PROCEDURE upd(
      p_iduser WORK.iduser%TYPE,
      p_idCompany WORK.idCompany%TYPE,
      p_beginDate WORK.beginDate%TYPE,
      p_endDate WORK.endDate%TYPE,
      p_jobTitle WORK.jobTitle%TYPE )
  AS
  BEGIN
    UPDATE WORK
    SET beginDate =p_beginDate,
      endDate     =p_endDate,
      jobTitle    =p_jobTitle
    WHERE iduser  =p_iduser
    AND idCompany =p_idCompany;
  END upd;
  FUNCTION get(
      p_iduser WORK.iduser%TYPE,
      p_idCompany WORK.idCompany%TYPE)
    RETURN find_cursor
  AS
    work_cursor find_cursor;
  BEGIN
    OPEN work_cursor FOR SELECT WORK.iduser,
    WORK.idCompany,
    WORK.beginDate,
    WORK.endDate,
    WORK.jobTitle,
    COMPANY.name,
    COMPANY.address FROM WORK INNER JOIN COMPANY ON WORK.idCompany=COMPANY.idCompany WHERE WORK.iduser =p_iduser AND WORK.idCompany=p_idCompany;
    RETURN work_cursor;
  END get;
  FUNCTION getAll
    RETURN find_cursor
  AS
    work_cursor find_cursor;
  BEGIN
    OPEN work_cursor FOR SELECT WORK.iduser,
    WORK.idCompany,
    WORK.beginDate,
    WORK.endDate,
    WORK.jobTitle,
    COMPANY.name,
    COMPANY.address FROM WORK INNER JOIN COMPANY ON WORK.idCompany=COMPANY.idCompany;
    RETURN work_cursor;
  END getAll;
END WORKPACKAGE;
-- -----------------------------------------------------
-- CREATE LIKEPACKAGE
-- -----------------------------------------------------
CREATE OR REPLACE PACKAGE LIKEPACKAGE
IS
TYPE find_cursor
IS
  REF
  CURSOR;
    FUNCTION ADDLikeComment(
        p_dateLiked LIKECOMMENTS.dateLiked%TYPE,
        p_userid LIKECOMMENTS.iduser%TYPE,
        p_commentsid LIKECOMMENTS.idcomments%TYPE)
        RETURN NUMBER;
    FUNCTION ADDLikePost(
        p_dateLiked LIKEPOST.dateLiked%TYPE,
        p_userid LIKEPOST.iduser%TYPE,
        p_postid LIKEPOST.idpost%TYPE)
        RETURN NUMBER;
    PROCEDURE delLikeComment(
        p_likeid LIKECOMMENTS.idlike%TYPE) ;
    PROCEDURE delLikePost(
        p_likeid LIKEPOST.idlikePost%TYPE) ;
    PROCEDURE updLikeComment(
        p_likeid LIKECOMMENTS.idlike%TYPE,
        p_dateLiked LIKECOMMENTS.dateLiked%TYPE,
        p_userid LIKECOMMENTS.iduser%TYPE,
        p_commentsid LIKECOMMENTS.idcomments%TYPE ) ;
    PROCEDURE updLikePost(
        p_likeid LIKEPOST.idlikePost%TYPE,
        p_dateLiked LIKEPOST.dateLiked%TYPE,
        p_userid LIKEPOST.iduser%TYPE,
        p_postid LIKEPOST.idpost%TYPE ) ;
    FUNCTION getLikeComment(
        p_likeid LIKECOMMENTS.idlike%TYPE )
      RETURN find_cursor;
    FUNCTION getLikePost(
        p_likeid LIKEPOST.idlikePost%TYPE )
      RETURN find_cursor;
    FUNCTION getAllLikeComment
      RETURN find_cursor;
    FUNCTION getAllLikePost
      RETURN find_cursor;
  END LIKEPACKAGE;
  /
CREATE OR REPLACE PACKAGE BODY LIKEPACKAGE
AS
  FUNCTION ADDLikeComment(
        p_dateLiked LIKECOMMENTS.dateLiked%TYPE,
        p_userid LIKECOMMENTS.iduser%TYPE,
        p_commentsid LIKECOMMENTS.idcomments%TYPE )
    RETURN NUMBER
  AS
  BEGIN
    INSERT
    INTO LIKECOMMENTS
      (
        dateLiked,
        iduser,
        idcomments
      )
      VALUES
      (
        p_dateLiked,
        p_userid,
        p_commentsid
      );
  RETURN 1;
END ADDLikeComment;
FUNCTION ADDLikePost(
        p_dateLiked LIKEPOST.dateLiked%TYPE,
        p_userid LIKEPOST.iduser%TYPE,
        p_postid LIKEPOST.idpost%TYPE )
    RETURN NUMBER
  AS
  BEGIN
    INSERT
    INTO LIKEPOST
      (
        dateLiked,
        iduser,
        idpost
      )
      VALUES
      (
        p_dateLiked,
        p_userid,
        p_postid
      );
  RETURN 1;
END ADDLikePost;
  PROCEDURE delLikeComment
    (
      p_likeid LIKECOMMENTS.idlike%TYPE
    )
  AS
  BEGIN
    DELETE FROM LIKECOMMENTS WHERE idlike=p_likeid;
  END delLikeComment;
  PROCEDURE delLikePost
    (
      p_likeid LIKEPOST.idlikePost%TYPE
    )
  AS
  BEGIN
    DELETE FROM LIKEPOST WHERE idlikePost=p_likeid;
  END delLikePost;
  PROCEDURE updLikeComment(
        p_likeid LIKECOMMENTS.idlike%TYPE,
        p_dateLiked LIKECOMMENTS.dateLiked%TYPE,
        p_userid LIKECOMMENTS.iduser%TYPE,
        p_commentsid LIKECOMMENTS.idcomments%TYPE )
  AS
  BEGIN
    UPDATE LIKECOMMENTS
    SET dateLiked     =p_dateLiked,
    iduser=p_userid,
    idcomments=p_commentsid WHERE idlike =p_likeid;
  END updLikeComment;
  PROCEDURE updLikePost(
        p_likeid LIKEPOST.idlikePost%TYPE,
        p_dateLiked LIKEPOST.dateLiked%TYPE,
        p_userid LIKEPOST.iduser%TYPE,
        p_postid LIKEPOST.idpost%TYPE )
  AS
  BEGIN
    UPDATE LIKEPOST
    SET dateLiked     =p_dateLiked,
    iduser=p_userid,
    idpost=p_postid WHERE idlikePost =p_likeid;
  END updLikePost;
  FUNCTION getLikeComment(
      p_likeid LIKECOMMENTS.idlike%TYPE)
    RETURN find_cursor
  AS
    like_cursor find_cursor;
  BEGIN
    OPEN like_cursor FOR SELECT idlike,
    dateLiked,
    iduser,
  idcomments FROM LIKECOMMENTS WHERE idlike =p_likeid;
  RETURN like_cursor;
END getLikeComment;
FUNCTION getLikePost(
      p_likeid LIKEPOST.idlikePost%TYPE)
    RETURN find_cursor
  AS
    like_cursor find_cursor;
  BEGIN
    OPEN like_cursor FOR SELECT idlikePost,
    dateLiked,
    iduser,
  idpost FROM LIKEPOST WHERE idlikePost =p_likeid;
  RETURN like_cursor;
END getLikePost;
  FUNCTION getAllLikeComment
    RETURN find_cursor
  AS
    like_cursor find_cursor;
  BEGIN
    OPEN like_cursor FOR SELECT idlike,
    dateLiked,
    iduser,
  idcomments FROM LIKECOMMENTS;
  RETURN like_cursor;
END getAllLikeComment;
FUNCTION getAllLikePost
    RETURN find_cursor
  AS
    like_cursor find_cursor;
  BEGIN
    OPEN like_cursor FOR SELECT idlikePost,
    dateLiked,
    iduser,
  idpost FROM LIKEPOST;
  RETURN like_cursor;
END getAllLikePost;
END LIKEPACKAGE;