proc cont_autores

CREATE OR REPLACE PROCEDURE CONT_AUTORES IS
  
CURSOR c_autores IS
    SELECT a.ID, 
           a.FIRST_NAME, 
           (SELECT COUNT(*) 
            FROM BOOKS b 
            WHERE b.author1 = a.ID OR b.author2 = a.ID OR b.author3 = a.ID) cont
    FROM AUTHORS a
    WHERE (SELECT COUNT(*) 
            FROM BOOKS b 
            WHERE b.author1 = a.ID OR b.author2 = a.ID OR b.author3 = a.ID) > 2
          AND ROWNUM < 5;

  lr_autor c_autores%ROWTYPE;

BEGIN

  OPEN c_autores;
  
  LOOP
    FETCH c_autores INTO lr_autor;
    EXIT WHEN c_autores%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(lr_autor.ID || ' ' || lr_autor.first_name || ' libros: ' || lr_autor.cont);
  END LOOP;
  
  CLOSE c_autores;

END;