several tables in PL/SQL on Cursor

Question

I need to put the SELECT below to work within a cursor, but I’m not able to join the 3 tables, someone can help me?

SELECT DISTINCT PROFESSOR.NOME, ESTUDANTE.NOME
FROM PROFESSOR INNER JOIN SUPERVISIONA ON 
PROFESSOR.ID_PROF=SUPERVISIONA.ID_PROF INNER JOIN ESTUDANTE ON SUPERVISIONA.ID_EST=ESTUDANTE.ID_EST
WHERE ESTUDANTE.NOME='RITA'

Answer 1

Based on the little information passed, I believe that your query is already correct, the suggestion would be to put an UPPER in the STUDENT clause.NAME because you are passing the string 'RITA' but it may be that in the bank is 'Rita'. Another query suggestion would be:

SELECT P.NOME NOME_PROFESSOR, E.NOME NOME_ESTUDANTE
FROM PROFESSOR P, ESTUDANTE E, SUPERVISIONA S
WHERE S.ID_PROF = P.ID_PROF
AND S.ID_EST = E.ID_EST
AND UPPER(E.NOME) = 'RITA';

This taking into account that the SUPERVISE table has the ids of TEACHER and STUDENT.

Upshot:

An example of a pl using the query on a cursor would look like this:

DECLARE

    v_nome_professor VARCHAR(30);
    v_nome_estudante VARCHAR(30);

    CURSOR C_TESTE IS
        SELECT P.NOME NOME_PROFESSOR, E.NOME NOME_ESTUDANTE
        FROM PROFESSOR P, ESTUDANTE E, SUPERVISIONA S
        WHERE S.ID_PROF = P.ID_PROF
        AND S.ID_EST = E.ID_EST
        AND UPPER(E.NOME) = 'RITA';
BEGIN
    FOR c IN C_TESTE
    LOOP
        BEGIN
            v_nome_professor := c.NOME_PROFESSOR;
            v_nome_estudante := c.NOME_ESTUDANTE;

            DBMS_OUTPUT.PUT_LINE('Valores retornados:');
            DBMS_OUTPUT.PUT_LINE('Professor: ' || v_nome_professor || ' / Estudante: ' || v_nome_estudante);

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('Nenhum dado encontrado!');
        END;
    END LOOP;
END;
/

Upshot: