There is a table "Lease" in which data on contracts are stored. There is a table "Tenant" in which data about tenants are stored.

You need to write a cursor with which, from Lease, select data about tenants whose last name begins with a certain letter. Actually writes that the PL / SQL command completed successfully, but nothing at the output. Thank you in advance.

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 answer 1

    Something like that:

     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
      Wash in the cursor declaration you forgot the link with the tenant table: cursor lease_current select * from tenant join tenant on tenant.NTn = lease.NTn where like 'S%'; - UserTest
    • thank! with this specification works! - Gonza12
    • True, UserTest, missed. But it was important to show that all predicates should be in the cursor itself and described. Thanks for clarifying! - orkaan