3. Write an anonymous PL/SQL script that creates a cursor consisting of vendor_n
ID: 3762211 • Letter: 3
Question
3. Write an anonymous PL/SQL script that creates a cursor consisting of vendor_name, invoice_number, and balance_due for each invoice with a balance due that’s greater than or equal to $5,000. The rows in this cursor should be sorted in descending sequence by balance due. Then, for each invoice, display the balance due, invoice number, and vendor name so it looks something like this: Use a CURSOR $19,351.18 P-0608 Malloy Lithographing Inc 4. Enhance your solution to exercise 3 so it shows the invoice data in three groups based on the balance due amounts with these headings: You will use the CURSOR three different times to loop through using the different criteria as defined below with the headings. $20,000 or More $10,000 to $20,000 $5,000 to $10,000 Each group should have a heading followed by the data for the invoices that fall into that group. Also, the groups should be separated by one blank line. 5. Enhance your solution to exercise 3 so it uses a substitution variable to set a bind variable that you use to determine what the minimum balance due should be for the invoices that the SELECT statement is going to retrieve. You should also use this bind variable to display a heading like this before the list of invoices: You are using the bind variable to set the balance due minimum. In this case 2000 is not a hard coded value but is set using a bind variable. Invoice amounts greater than or equal to $2,000 ================================================== where 2,000 is the value of the bind variable.
Explanation / Answer
begin
DBMS_OUTPUT.PUT_LINE('Amount due greater than 20000');
for I in (select INVOICE_NBR,INVOICE_AMOUNT,VENDOR_NAME from INVOICE where INVOICE_AMOUNT > 20000 order by INVOICE_AMOUNT desc)
LOOP
DBMS_OUTPUT.PUT_LINE(I.INVOICE_NBR||' '||I.INVOICE_AMOUNT||' '||I.VENDOR_NAME);
end LOOP;
DBMS_OUTPUT.PUT_LINE('Amount dues between 10000 and 20000');
DBMS_OUTPUT.PUT_LINE('');
for I in (select INVOICE_NBR,INVOICE_AMOUNT,VENDOR_NAME from INVOICE where INVOICE_AMOUNT between 10000 and 20000 order by INVOICE_AMOUNT desc)
LOOP
DBMS_OUTPUT.PUT_LINE(I.INVOICE_NBR||' '||I.INVOICE_AMOUNT||' '||I.VENDOR_NAME);
end LOOP;
DBMS_OUTPUT.PUT_LINE('Amount dues between 5000 and 10000');
DBMS_OUTPUT.PUT_LINE('');
for I in (select INVOICE_NBR,INVOICE_AMOUNT,VENDOR_NAME from INVOICE where INVOICE_AMOUNT between 5000 and 10000 order by INVOICE_AMOUNT desc)
LOOP
DBMS_OUTPUT.PUT_LINE(I.INVOICE_NBR||' '||I.INVOICE_AMOUNT||' '||I.VENDOR_NAME);
end loop;
end;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.