Question

Write a mysql script that creates and calls a stored procedure named test. THis procedure should create a cursor for a result set consists of the vendor_name, invoice_number, and balance_due which is (invoice_total - payment_due - credits_due) with a balance due greater than or equal to 5,000,

The results should be sorted in decending sequence by balance due.

The invoice data should be placed in 3 groups based on the balance due, 20,000, or more, 10,000 - 20,000 or 5-10,000.

When your done the string variable thats returned should be in this format:

20,000 more more: 10,000 to 20,000: 11130.70|P-0680|Malloy Lithographing INC// 5,000 to 10,000: 6586.62|O-2436|Malloy Lithographing INC//

Answer 1

• 

  CREATE PROCEDURE test() DECLARE CURSOR invoices_cursor IS SELECT vendor_name, invoice_number, invoice_total - payment_due - credit_due AS balance_due FROM vendors v JOIN invoices i     ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_due - credit_due>= 5000 ORDER BY balance_due DESC; invoice_rowinvoices%ROWTYPE; BEGIN FOR invoice_row IN invoices_cursor LOOP       DBMS_OUTPUT.PUT_LINE(       TO_CHAR(invoice_row.balance_due, '$99,999.99') || '    ' || invoice_row.invoice_number || '    ' || invoice_row.vendor_name); END LOOP; END; END;