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)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
where creditlimit = 10000
or creditlimit = 5000
or creditlimit = 7000
or creditlimit = 3000
and customer.custid = v_custid;
Loop Fetch c_credit_cursor INTO v_custid, v_creditlimit;
If v_creditlimit = 10000 then
set v_creditlimit = 25000;
elsif v_creditlimit = 5000 then
set v_creditlimit = 10000;
elsif v_creditlimit = 7000 then
set v_creditlimit = 15000;
elsif v_creditlimit = 3000 then
set v_creditlimit = 5000;
DBMS_OUTPUT.PUT_LINE (v_custid ||' customer records were updated.');
Exit When c_credit_cursor%NOTFOUND;
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
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
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
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.