I'm new to PL/SQL and trying to convert an SQL statement into the PL/SQL format. I have the SQL statement as:
SELECT StudentIDNumber
FROM Lease
INNER JOIN Invoice
ON Invoice.LeaseID = Lease.LeaseID
WHERE IsPaid = 'N';
How would I go about this? Any solutions would be greatly appreciated!
I think you would only need to specify your schema in Oracle and that's about it. The other syntax should be correct (provided your tables are coming from the same Schema/table):
SELECT StudentIDNumber
FROM PRODDATA.Lease
INNER JOIN Invoice
ON Invoice.LeaseID = Lease.LeaseID
WHERE IsPaid = 'N';
This document offered by Oracle also has some basic syntax changes that could prove useful to you https://docs.oracle.com/cd/E10405_01/appdev.120/e10379/ss_oracle_compared.htm
may be try below PlSQL snippet?
DECLARE
TYPE lv_student_ID IS TABLE OF NUMBER;
BEGIN
-- assuming you are trying to find all students who have not yet paid, you can use a nested table
SELECT StudentIDNumber
BULK COLLECT INTO lv_student_ID
FROM Lease INNER JOIN Invoice ON Invoice.LeaseID = Lease.LeaseID
WHERE IsPaid = 'N';
-- use value as you want
DBMS_OUTPUT.put_line (lv_student_ID.COUNT);
END;
