Question

Write a script that declares and sets a variable that’s equal to the total outstanding balance due. If that balance due is greater than $10,000.00, the script should return a result set consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance for each invoice with a balance due, sorted with the oldest due date first. If the total outstanding balance due is less than $10,000.00, return the message "Balance due is less than $10,000.00."

Answer 1

see explaination

Step-by-step explanation:

SCRIPT :

drop table Vendor;

create table Vendor (VendorName varchar2(20), InvoiceNumber number (20), InvoiceDueDate date, Balance number(32,6));

insert into VENDOR (VENDORNAME,INVOICENUMBER,INVOICEDUEDATE,BALANCE) values ('ABC',121,TO_TIMESTAMP('01-01-18','DD-MM-RR HH12:MI:SSXFF AM'),1000);

Insert into Vendor (VENDORNAME,INVOICENUMBER,INVOICEDUEDATE,BALANCE) values ('XYZ',1212121,to_timestamp('02-01-18','DD-MM-RR HH12:MI:SSXFF AM'),100000);

declare

cursor c1 is select * from Vendor;

begin

for i in c1

loop

if I.BALANCE < 1001 then

DBMS_OUTPUT.PUT_LINE('Vendor Name '||I.VENDORNAME||', Invoice Number '||I.INVOICENUMBER||', Invoice DueDate '||I.INVOICEDUEDATE||',BALANCE '||I. BALANCE);

else

dbms_output.put_line('Vendor Name '||i.VendorName||', Invoice Number '||i.InvoiceNumber||', Invoice DueDate '|| i.InvoiceDueDate||' Balance due is less than $1000');

end if;

end LOOP;

end;

output : SCRIPT OUTPUT

table VENDOR dropped.

table VENDOR created.

1 rows inserted.

1 rows inserted.

anonymous block completed

dbms output :

Vendor Name ABC, Invoice Number 121, Invoice DueDate 01-JAN-18,BALANCE 1000

Vendor Name XYZ, Invoice Number 1212121, Invoice DueDate 02-JAN-18 Balance due is less than $1000