# Thread: Need help in CURSOR !

1. Registered User
Join Date
Apr 2004
Location
USA
Posts
28

I have a table called TOP_DOGS with the following informations.

NAME SALARY
------------------------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850

I want to update each salary using CURSOR and FOR LOOP to the following rules:

1. If Salary is less than 1000 then give them a 10% raise.
2. If Salary is between 1000 and 3000, then give them raise of 8%.
3. If Salary is greater than 3000 then their raise is 5%.

Can anyone help me with this problems.?? Thanks in advance!
Last edited by GorkhaliDBA; 05-01-04 at 21:48.

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
If (salary > ??? )
Then
...
Else

End If;

3. Registered User
Join Date
Apr 2004
Location
USA
Posts
28
how do I use CURSOR FOR LOOP in this problems??? anyone....I couldn't figure it out.
I AM JUST A BIGINEER FOR THIS PROBLEMS ...PLEASE SOME ONE HELP ME OUT............
DECLARE
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE;

CURSOR emp_cursor IS
IF (sal<1000) THEN
salary := sal +sal*.10;
ELSEIF v_sal BETWEEN 1000 and 3000 THEN
SALARY := sal + sal*.08;
ELSE
salary := sal + sal*.05;
END IF;

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
http://asktom.oracle.com has many examples; such as
do a keyword search on "explicit & implicit cursor"

5. Registered User
Join Date
Apr 2004
Location
USA
Posts
28
Can anyone help me with this problems...........? I have been suffering with this. I didn' t find in it in asktom
Buddhi

6. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776

## most folks......

7. Registered User
Join Date
Apr 2004
Posts
246
There's no reason to use pl/sql at all. It can be done in an update (for us old guys, there was time before pl/sql, so we had to learn all the functions). Using the decode and sign functions is all you need. Also, an update will use much less i/o than pl/sql, and is much more efficient.

update top_dogs
set salary = salary *
decode( sign(salary-1000), -1, 1.10,
decode( sign(3000-salary), -1, 1.05,
1.08
)
)

8. Registered User
Join Date
Apr 2004
Location
USA
Posts
28

## Important

I have this code and still suffering...., I need to do this in pl/sql ..using cursor loop.
DECLARE
CURSOR C1 IS
SELECT NAME, SALARY FROM TWO_DOGS;
BEGIN
FOR EMP_RECORD IN C1 LOOP
IF V_SAL <1000 THEN
SALARY := SALARY + SALARY * 0.10;
ELSEIF V_SAL BETWEEN 1000 AND 3000 THEN
SALARY := SALARY + SALARY * 0.08;
ELSE V_SAL >3000
SALARY := SALARY + SALARY * 0.05;
END IF
END

9. Registered User
Join Date
May 2004
Location
Redwood Shores, CA
Posts
68
Why are you so stubborn?
shoblock told you do it in sql so do it in sql. Are you doing homework and the instructor wants you to use cursors?

10. Registered User
Join Date
Apr 2002
Location
California, USA
Posts
482
Originally Posted by GorkhaliDBA
I have this code and still suffering...., I need to do this in pl/sql ..using cursor loop.
DECLARE
CURSOR C1 IS
SELECT NAME, SALARY FROM TWO_DOGS;
BEGIN
FOR EMP_RECORD IN C1 LOOP
IF V_SAL <1000 THEN
SALARY := SALARY + SALARY * 0.10;
ELSEIF V_SAL BETWEEN 1000 AND 3000 THEN
SALARY := SALARY + SALARY * 0.08;
ELSE V_SAL >3000
SALARY := SALARY + SALARY * 0.05;
END IF
END
You have some problems in your code:

where the v_sal come from? it's not defined anywhere !!! also salary !!

Here is the problem solution in a test case with the right procedure (with cursor loop) code:

SQL> create table two_dogs (
2 name varchar2(100),
3 salary number);

Table created.

SQL> desc two_dogs
Name Null? Type
----------------------------------------- -------- --------------------------

NAME VARCHAR2(100)
SALARY NUMBER

SQL>

SQL> insert into two_dogs values ('test',500);

1 row created.

SQL> insert into two_dogs values ('test1',1500);

1 row created.

SQL> insert into two_dogs values ('test2',4500);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from two_dogs;

NAME SALARY
-------------------------------------------------- ----------
test 500
test1 1500
test2 4500

SQL>

SQL> DECLARE
2
3 CURSOR C1 IS
4 SELECT NAME, SALARY FROM TWO_DOGS;
5
6 BEGIN
7
8 FOR EMP_RECORD IN C1 LOOP
9
10 IF emp_record.SALARY < 1000 THEN
11
12 update two_dogs
13 set salary = emp_record.salary + (emp_record.salary * 0.10)
14 where salary < 1000;
15 commit;
16
17 ELSIF emp_record.SALARY BETWEEN 1000 AND 3000 THEN
18
19 update two_dogs
20 set salary = emp_record.salary + (emp_record.salary * 0.08)
21 where salary between 1000 and 3000;
22 commit;
23
24 ELSIF emp_record.SALARY > 3000 THEN
25
26 update two_dogs
27 set salary = emp_record.salary + (emp_record.salary * 0.05)
28 where salary > 3000;
29 commit;
30
31 END IF;
32
33 END LOOP;
34
35 END;
36 /

PL/SQL procedure successfully completed.

SQL> select * from two_dogs;

NAME SALARY
-------------------------------------------------- ----------
test 550
test1 1620
test2 4725

---------- source code - START -------------------

DECLARE

CURSOR C1 IS
SELECT NAME, SALARY FROM TWO_DOGS;

BEGIN

FOR EMP_RECORD IN C1 LOOP

IF emp_record.SALARY < 1000 THEN

update two_dogs
set salary = emp_record.salary + (emp_record.salary * 0.10)
where salary < 1000;
commit;

ELSIF emp_record.SALARY BETWEEN 1000 AND 3000 THEN

update two_dogs
set salary = emp_record.salary + (emp_record.salary * 0.08)
where salary between 1000 and 3000;
commit;

ELSIF emp_record.SALARY > 3000 THEN

update two_dogs
set salary = emp_record.salary + (emp_record.salary * 0.05)
where salary > 3000;
commit;

END IF;

END LOOP;

END;
/

---------- source code - END -------------------

HTH,

clio_usa - OCP 8/8i/9i DBA

11. Registered User
Join Date
Jan 2004
Posts
492
See I dont quite agree with the last post - If you are going to loop through every record, yet update the whole table and commit for every single record, your database will become incapactitated.

Lets say there are 20,000 recs... First record is under 1000 salary. Using the last post's theory, you are then going to update every single record in the table under 1000 with the new salary. You are then going to commit that. Lets say the 2nd record is also under 1000 salary. You are going to do the exact same thing, but the records have already been updated for this, so it is a waste.

I was thinking more like this - and this is just my idea, you are free to disagree with it as well. I am assuming in my code that name is a primary key and therefore is unique.

Code:
```DECLARE

V_NEW_SAL TWO_DOGS.SALARY%TYPE;

CURSOR C1 IS
SELECT NAME, SALARY FROM TWO_DOGS;

BEGIN

FOR R1 IN C1
LOOP

IF R1.SALARY <1000 THEN
V_NEW_SAL := R1.SALARY + R1.SALARY * 0.10;

ELSIF R1.SALARY BETWEEN 1000 AND 3000 THEN
V_NEW_SAL := R1.SALARY + R1.SALARY * 0.08;

ELSE
V_NEW_SAL := R1.SALARY + R1.SALARY * 0.05;

END IF;

UPDATE TWO_DOGS
SET SALARY = V_NEW_SAL
WHERE NAME = R1.NAME;

END LOOP;
COMMIT; --SUCCESSFUL PROCESSING

EXCEPTION -- UNSUCCESSFUL
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM);
ROLLBACK;

END;```
Last edited by ss659; 05-03-04 at 07:31.

12. Drunkard
Join Date
Nov 2002
Location
Desk, slightly south of keyboard
Posts
697
Hi,

No only is it wasteful it would cause the salaries to be update by far more than they should be (due to the repeated increases).

Cheers
Bill

13. Registered User
Join Date
Apr 2004
Location
USA
Posts
28
Thanks to all of you. Great.

14. Registered User
Join Date
May 2004
Location
Redwood Shores, CA
Posts
68
Originally Posted by ss659
See I dont quite agree with the last post - If you are going to loop through every record, yet update the whole table and commit for every single record, your database will become incapactitated.

Lets say there are 20,000 recs... First record is under 1000 salary. Using the last post's theory, you are then going to update every single record in the table under 1000 with the new salary. You are then going to commit that. Lets say the 2nd record is also under 1000 salary. You are going to do the exact same thing, but the records have already been updated for this, so it is a waste.

I was thinking more like this - and this is just my idea, you are free to disagree with it as well. I am assuming in my code that name is a primary key and therefore is unique.

Code:
```DECLARE

V_NEW_SAL TWO_DOGS.SALARY%TYPE;

CURSOR C1 IS
SELECT NAME, SALARY FROM TWO_DOGS;

BEGIN

FOR R1 IN C1
LOOP

IF R1.SALARY <1000 THEN
V_NEW_SAL := R1.SALARY + R1.SALARY * 0.10;

ELSIF R1.SALARY BETWEEN 1000 AND 3000 THEN
V_NEW_SAL := R1.SALARY + R1.SALARY * 0.08;

ELSE
V_NEW_SAL := R1.SALARY + R1.SALARY * 0.05;

END IF;

UPDATE TWO_DOGS
SET SALARY = V_NEW_SAL
WHERE NAME = R1.NAME;

END LOOP;
COMMIT; --SUCCESSFUL PROCESSING

EXCEPTION -- UNSUCCESSFUL
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM);
ROLLBACK;

END;```
ss659
If you are so worried about performance why did you not suggest a FORALL thingy.

15. Registered User
Join Date
Apr 2002
Location
California, USA
Posts
482
I see many opinions now. Where they were before ??

The code could be modified by the end user for performance at anytime. He could even use three cursors that select the salaries in three buckets and then loop through the three cursors and update the records inside.

Many posibilities. The example was intended to show how to use PL/SQL cursors. I don't give any guarantees for the final results - It's everyone's job to do that.

HTH,

clio_usa - OCP 8/8i/9i DBA

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•