Cursor Question 1

[CTO1]

I've written a simple cursor to update one or two columns dependant upon criteria.
This works fine,

DECLARE
v_playerid player_details.playerid%TYPE;
v_wage player_details.wage%TYPE;
v_nationalityid player_details.nationalityid%TYPE;
CURSOR c_Wagecalc IS
SELECT Playerid, Wage, Nationalityid FROM Player_Details;

BEGIN
OPEN c_Wagecalc;
LOOP
FETCH c_Wagecalc INTO v_playerid, v_wage, v_nationalityid;
EXIT WHEN c_Wagecalc%NOTFOUND;
IF v_nationalityid = '1'
THEN UPDATE Player_Details
SET Basic_wage = v_wage
WHERE Playerid = v_playerid;

ELSE
UPDATE Player_details
SET Basic_wage = (v_wage*0.80), Bonus = (v_wage*0.20)
WHERE Playerid = v_playerid;

END IF;
END LOOP;
END;
/


Yet this doesn't

DECLARE
v_playerid player_details.playerid%TYPE;
v_wage player_details.wage%TYPE;
v_nationalityid player_details.nationalityid%TYPE;
CURSOR c_Wagecalc IS
SELECT Playerid, Wage, Nationalityid FROM Player_Details;

BEGIN
OPEN c_Wagecalc;
LOOP
FETCH c_Wagecalc INTO v_playerid, v_wage, v_nationalityid;
EXIT WHEN c_Wagecalc%NOTFOUND;
IF v_nationalityid = '1'
THEN UPDATE Player_Details
SET Basic_wage = v_wage
WHERE Playerid = v_playerid;

ELSE
UPDATE Player_details
SET Basic_wage = (v_wage*0.80), Bonus = (v_wage - Basic_wage)
WHERE Playerid = v_playerid;

END IF;
END LOOP;
END;
/

The only difference being
Bonus = (v_wage - Basic_wage)


Is this failing to populate a column because of the order of precedence of operators ?

 

[lewisp]

How are you expecting this to work? Do you want the value of basic wage to be used in the bonus calculation before you set it with v_wage*0.80 or after?

If after, you need to change the bonus calc to

[tt]Bonus = (v_wage - (v_wage*0.80))[/tt]

If before, you need

[tt]Bonus = (v_wage - Nvl(Basic_wage,0))[/tt]

 

[CTO1]

It is after so it would be
Bonus = (v_wage - (v_wage*0.80))

I see, so the basic_wage column would not be updated and available to reference at this point, though I can of course use the value stored in the variable.
At what point would the value placed into the column be available to use;
Would it be at the
END;
of the PL/SQL Block ?

 

[lewisp]

It would be available locally to the PL/SQL block the next time round the loop (ie to the current session), but not to the wider database schema (other sessions) until you commit the data.

 

[w860098]

Would the original have worked if the statement had read
SET Basic_wage = (v_wage*0.80),Bonus=(v_wage*0.20)

 

[lewisp]

No because the bonus is calculated using the old value of basic wage (prior to the update), not the new one which is a result of the update.

 

[lewisp]

Sorry - having a bad day today.

I just re-read CT01's response and, yes, your calculation is the same as [tt]v_wage - (v_wage * 0.8)[/tt]