SELECT * FROM spectator;
 CREATE SEQUENCE id_seq
    START WITH 100
    increment BY -1
    minvalue 1
    maxvalue 100
    cycle;

CREATE TABLE verif_14(
    id_14 NUMBER(10) primary key,
    nume VARCHAR2(50) ,
    prenume VARCHAR2(50),
    rand INTEGER,
    piesa NUMBER(10,0)
);

CREATE OR REPLACE PACKAGE pachet_14 IS

  TYPE t_rec IS RECORD (
    nume VARCHAR2(50),
    prenume VARCHAR2(50),
    rand INTEGER,
    piesa NUMBER(10,0)
    );

    coincidenta t_rec;

    TYPE t_rec_table IS TABLE OF t_rec;
    ntable t_rec_table := t_rec_table();

  FUNCTION f_14
    RETURN NUMBER;

END pachet_14;
/


CREATE OR REPLACE PACKAGE BODY pachet_14 IS

    FUNCTION f_14
        RETURN NUMBER IS
            ans NUMBER := 0 ;
            vechi NUMBER := 0;
            i NUMBER := 0;
    BEGIN

        ntable.extend;
        i := i + 1;
        coincidenta.nume := 'asfasf';
        coincidenta.prenume := 'asfasf';
        coincidenta.rand := 100;
        coincidenta.piesa := 1001;
        ntable(i) := coincidenta;

        FOR c IN ( SELECT nume, prenume, rand, cod_piesa
        FROM spectator s JOIN rezervare r ON (s.cnp = r.sp_id)
        ) LOOP
            coincidenta.nume := c.nume;
            coincidenta.prenume := c.prenume;
            coincidenta.rand := c.rand;
            coincidenta.piesa := c.cod_piesa;

            FOR j IN ntable.FIRST..i LOOP
                IF ntable(j).nume = coincidenta.nume AND ntable(j).prenume = coincidenta.prenume AND ntable(j).rand = coincidenta.rand AND ntable(j).piesa = coincidenta.piesa THEN
                    DBMS_OUTPUT.put_line(c.nume || ' ' || c.prenume);
                    ans := ans + 1;
                    INSERT INTO verif_14 VALUES(id_seq.NEXTVAL, coincidenta.nume, coincidenta.prenume, coincidenta.rand, coincidenta.piesa);
                END IF;
            END LOOP;


            IF ans = vechi THEN
                ntable.extend;
                i := i + 1;
                ntable(i) := coincidenta;
            END IF;

          END LOOP;

          --eliberez memoria
          ntable.DELETE(ntable.FIRST, ntable.LAST);

         RETURN ans;
    END f_14;

END pachet_14;
/

SELECT *
FROM spectator s JOIN rezervare r ON (s.cnp = r.sp_id);

DECLARE
  -- Non-scalar parameters require additional processing
  result NUMBER;
BEGIN
  -- Call the function
  result := pachet_14.f_14;
  DBMS_OUTPUT.put_line('Numarul de coincidente este: ' || result);
END;
/

SELECT * FROM verif_14;