Курсор выполняется в блоке, который завершается успешно, но на выходе ничего нет

#oracle #plsql #sql

Есть таблица Lease в которой хранятся данные о договорах,
и есть таблица Tenant в которой хранятся данные об арендаторах.

Нужно написать курсор, с помощью которого, из Lease выбираются данные об арендаторах,
фамилия которых начинается с определенной буквы. 

Собственно пишет, что команда PL/SQL завершена успешно, но на выходе ничего:

SET SERVEROUTPUT ON;

--accept firstchar prompt 'Введите первую букву фамилии арендатора: ';
DECLARE 
CURSOR lease_curs2 IS SELECT* FROM Lease;
row_lease lease_curs2%ROWTYPE; 
BEGIN
    OPEN lease_curs2;
    WHILE lease_curs2%FOUND 
        LOOP
        BEGIN
            FETCH lease_curs2 INTO row_lease;
            FOR char IN (SELECT* FROM Tenant WHERE Tenant.NTn = row_lease.NTn)
            LOOP
                IF char.Tn LIKE 'S%' THEN
            DBMS_OUTPUT.PUT_LINE ('Lease number: ' || row_lease.NLease);         
        END IF;
            END LOOP;
        END;
        END LOOP;
CLOSE lease_curs2;
END;
/

    

Ответы

Ответ 1

Что-то вроде этого:
declare
    cursor lease_cur is
        select * from lease
        where tn like 'S%'
        ;
    lease_rec lease_cur%rowtype;

begin
    open lease_cur;
    loop
        fetch lease_cur into lease_rec;
        dbms_output.put_line('Lease number: '||lease_rec.nlease);
        exit when lease_cur%NOTFOUND;
    end loop;
    close lease_cur;
end;


Ответ 2

Кроме отсутствующих чтения данных с fetch и закрытия курсора с close, блок в вопросе
имеет ряд серьёзных недостатков:


Вложенные циклы с открытием нового курсора внутри цикла - плохая практика. Надо по
возможности ограничиться одним запросом, и пусть цикл выполняется в SQL движке, он
сделает это наиболее эффективно. 
Не надо держать курсоры открытыми обрабатывая пошагово данные в цикле. Это не клиентское
приложение, блок выполняется на сервере. Лучше считать все данные в табличную переменную
и потом работать с ней.


Рабочий пример учитывая вышесказанное:

declare  
    cursor cur (prefix varchar2) is
    select *
    from tenant t
    join lease l on l.ntn = t.ntn 
    where t.tn like prefix||'%'
    ;
    type arr is table of cur%rowtype;
    rows arr;
begin
    open cur ('S');
    fetch cur bulk collect into rows;
    close cur;
    for ix in 1..rows.count loop 
        dbms_output.put_line ('Lease number: '||rows(ix).nlease||' for '||rows(ix).tn);
    end loop;
end;
/


Вывод:


  Lease number: 10 for Smith
  Lease number: 20 for Sue




Тестовые данные для примера: 

with 
tenant (ntn, tn) as (
    select rownum, trim (column_value) from xmltable ('"Smith", "Sue", "Woo"')),
lease (nlease, ntn) as (
    select ntn*10, ntn from tenant
)


Ответ 3

Ваша ошибка в том, что вы забыли перед WHILE lease_curs2%FOUND добавить 
FETCH lease_curs2 INTO row_lease;

DECLARE 
CURSOR lease_curs2 IS SELECT* FROM Lease;
row_lease lease_curs2%ROWTYPE; 
BEGIN
    OPEN lease_curs2;
    FETCH lease_curs2 INTO row_lease;
    WHILE lease_curs2%FOUND 
        LOOP
        BEGIN
            FETCH lease_curs2 INTO row_lease;
            FOR char IN (SELECT* FROM Tenant WHERE Tenant.NTn = row_lease.NTn)
            LOOP
                IF char.Tn LIKE 'S%' THEN
                DBMS_OUTPUT.PUT_LINE ('Lease number: ' || row_lease.NLease);         
                END IF;
            END LOOP;
        END;
    END LOOP;
CLOSE lease_curs2;
END;
/