Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Apr 2004
    Location
    USA
    Posts
    28

    Unanswered: Need help in CURSOR Please !

    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 22:48.
    ___________
    GorkhaliOCA
    USA

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

    End If;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    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;
    ___________
    GorkhaliOCA
    USA

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com has many examples; such as
    http://asktom.oracle.com/pls/ask/f?p...:1544606261686
    do a keyword search on "explicit & implicit cursor"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    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
    ___________
    GorkhaliOCA
    USA

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    most folks......

    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    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. #8
    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
    ___________
    GorkhaliOCA
    USA

  9. #9
    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. #10
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Quote 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. #11
    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 08:31.

  12. #12
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Apr 2004
    Location
    USA
    Posts
    28
    Thanks to all of you. Great.
    ___________
    GorkhaliOCA
    USA

  14. #14
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote 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.
    My way or the highway. Yeah

  15. #15
    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
  •