1. Write a script to declare and use a cursor for the following SELECT statement
ID: 3790365 • Letter: 1
Question
1. Write a script to declare and use a cursor for the following SELECT statement. Use a WHILE loop to fetch each row in the result set. Omit the INTO clause to fetch directly to the Grids tab.
SELECT VendorName, AVG(InvoiceTotal) AS InvoiceAVG
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
2. Modify the solution to exercise 1 to fetch each row into a set of local variables. Use the PRINT statement to return each row in the format "Name, $0.00" to the Messages tab.
Explanation / Answer
DECLARE
CURSOR NAME
IS SELECT VendorName,Avg(InvoiceTotal)
from Vendorss JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
group by VendorName;
A varchar(20);
B varchar(20);
Begin
LOOP
fetch NAME into A,B;
DBMS_OUTPUT_PRINT_LINE ('vENDORnAME is' ||A || 'avg of invoicesTotal is' || B);
end loop;
end;
/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.