Derek Pike Derek Pike - 1 year ago 77
SQL Question

Explicit cursor pl/sql

Build a PL?SQL anonymous block that changes the credit limits for customers. Use and explicit cursor and the loop of your choice to accomplish the task. Also, keep trak of how many records were updaated and display the following message: "XX customer records were updated". make sure to commit all of hte records after your loop is finished. Below is a chart of the current credit limit and the new credit limit.

(old)10,000 (new)25,000,

(old)5,000 (new)10,000,

(old)7,000 (new)15,000,

(old)3,000 (new)5,000, (there are more in this table than just these 4).

Here's the code I have that obviously isn't working. Thank you in advance for your help. It's greatly appreciated.

set serveroutput on
Cursor c_credit_cursor is
Select custid, creditlimit
From customer
where creditlimit = 10000
or creditlimit = 5000
or creditlimit = 7000
or creditlimit = 3000
and customer.custid = v_custid;
v_creditlimit customer.creditlimit%TYPE;
v_custid customer.custid%TYPE;
open c_credit_cursor;
Loop Fetch c_credit_cursor INTO v_custid, v_creditlimit;
If v_creditlimit = 10000 then
update c_credit_cursor
set v_creditlimit = 25000;
elsif v_creditlimit = 5000 then
update c_credit_cursor
set v_creditlimit = 10000;
elsif v_creditlimit = 7000 then
update c_credit_cursor
set v_creditlimit = 15000;
elsif v_creditlimit = 3000 then
update c_credit_cursor
set v_creditlimit = 5000;
End If;
DBMS_OUTPUT.PUT_LINE (v_custid ||' customer records were updated.');
End Loop;
Exit When c_credit_cursor%NOTFOUND;
Close c_credit_cursor;
END Loop;

Answer Source

There are a number of issues with your code:

For a start, your cursor has the condition customer.custid = v_custid. You want to run through all customers regardless of what their customer ID is: you're only interested in those that have one of the specified credit limits. (In fact, later on in your code you are reading customer IDs out of cursor into v_custid, so it doesn't make sense to try to put v_custid back into the cursor. I would recommend deleting this condition from your cursor.

Next, the reason you need two END LOOP statements is that you have two LOOP statements. Each LOOP must be terminated with an END LOOP. I don't see why you have the outer loop — you should only need one — so remove the first LOOP (just after Begin) and the second END LOOP.

The next problem is with the update statements. Each of the update c_credit_cursor statements will give an error table or view does not exist. The reason for this is that you can't update a cursor like this, you can only update a table. The error is telling your that it couldn't find a table named c_credit_cursor. You also need to specify the name of the column in the table to update: you can't use v_creditlimit as that's a local variable. You want to use creditlimit instead.

There's two ways to fix up the update statements. You can update the customer with the customer ID you read out of the cursor:

update customer
   set credit_limit = (new credit limit)
 where custid = v_custid;

Alternatively, you can update the current row of the cursor:

update customer
   set credit_limit = (new credit limit)
 where current of c_credit_cursor;

This second approach requires you to add FOR UPDATE to the end of your CURSOR declaration.

Next, you'll get an error about EXIT being used outside a loop. You have an Exit When statement towards the bottom, but to be honest it's in the wrong place. You really want your Exit When statement to be the first thing after the Fetch statement: if we tried to read data out of the cursor but got no more data, then there's no reason for us to stay in the loop any more, so we need to get out of it.

Finally, I'll point out that the line that prints out the number of customer records updated won't always print out the right output. It's not printing out the number of records updated, it's only printing out the customer ID of the last record updated. If you have five customer records in your database, with IDs 1 to 5, all five have credit limits that your loop would update, and the database happens to return the record with ID 5 last, then your code would output 5 customer records were updated. In that case, your code would have printed the correct answer, but that would only be by coincidence. What would happen instead if the customers had IDs 1001 to 1005?

You'll need to add another local variable to count the number of records your cursor reads, and use that instead.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download