PLSQL adding a value to a variable

Question

I am new to PLSQL and I am slightly confused on how to add to increase decrease a variable value.

I have an employee table where I am trying to increase the commission rate and decrease some of the commission rates. However when ruining the script the changes to the commission rates are not changed on the employees table e.g one employees initial commission rate is 0.2 I want to add an increase by 0.4.

Employee table

At the moments this is the PLSQL that I wrote:

SET ECHO OFF
SET VERIFY OFF
SET DOCUMENT OFF
SET SERVEROUTPUT OFF


/* Ask for the employee ID number */
PROMPT Please enter the employees id number:
ACCEPT p_emp_id

/* Declarative section */
DECLARE
    /* Declare local block variables */
    /* Set local v_employee_id to that inputted by the user */
    v_employee_id       employees.employee_id%TYPE := &p_emp_id;
    v_job_id        employees.job_id%TYPE;
    v_salary        employees.salary%TYPE;
    v_commission_pct    employees.commission_pct%TYPE;



BEGIN

    SELECT job_id, salary, COMMISSION_PCT
    INTO v_job_id, v_salary, v_commission_pct 
    FROM employees
    WHERE employee_id = v_employee_id;


    IF v_job_id = 'SA MANG' AND Salary = 10500
    THEN    v_commission_pct :=     v_commission_pct + 0.4


    ELSE

    IF v_job_id = 'SA MANG' AND  v_salary = 10500
    THEN    v_commission_pct :=     v_commission_pct + 0.4;

        ELSIF v_job_id = 'SA_REP' AND v_salary = 9000 
    THEN
            v_commission_pct := v_commission_pct - 0.25;

            END IF;
        END IF;
    END IF;
    /* Update the corresponding row for the employee with 
       the new commission */
    UPDATE employees
    SET commission_pct = v_commission_pct
    WHERE employee_id = v_employee_id;

/* End anonymous PL/SQL block */
END;
/

/* Display the details of the new employee */
SELECT  employee_id, job_id, salary, commission_pct
FROM    employees
WHERE   employee_id = '&p_emp_id';

SET SERVEROUTPUT ON
SET DOCUMENT ON
SET VERIFY ON
SET ECHO ON

I have an employee table where I am trying to increase the commission rate and decrease some of the commission rates. However when ruining the script the changes to the commission rates are not changed on the employees table e.g one employees initial commission rate is 0.2 I want to add an increase by 0.4. — Sky
– Sky, Commented Mar 5, 2014 at 23:03
Are you querying in the same session - you don't seem to have committed the changes? But from the screenshot all the existing values are null; you might need to nvl(COMMISSION_PCT, 0), and null + 0.4 is null. — Alex Poole
– Alex Poole, Commented Mar 5, 2014 at 23:06
some of the values are null and some are not I've updated the image of the table — Sky
– Sky, Commented Mar 5, 2014 at 23:06
You might try adding a COMMIT; at the end of your anonymous block, right before the END; of the block. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Mar 6, 2014 at 3:00

Richard Pascual · Accepted Answer · 2014-03-06 04:53:44Z

Manipulating Variables through an Anonymous PL/SQL Block

Your anonymous block of PL/SQL is copied here below, with some comments in between. Any of the mistakes pointed out would have interrupted the completion of your intended operations. Were you able to run this successfully to completion?

If you're running this on the SQL*Plus prompt, it is possible the script output scrolled off the screen without your knowledge of any problems. Use the following command: SHOW ERRORS immediately after the prompt returns to you. This should provide some feedback and a warning if things are not right.

Quick Notes on Incrementing Variables

Rather than hardcode your values throughout the script, consider the possibility of anchoring them to CONSTANT typed VARIABLES. They look like regular variables, but since they are not expected to change for the duration of the script, Oracle handles them differently and more efficiently than hard-coded values scattered throughout your PL/SQL block. So, in the case of your code:

/* Declare local block variables */
/* Set local v_employee_id to that inputted by the user */
v_employee_id       employees.employee_id%TYPE := &p_emp_id;
v_job_id            employees.job_id%TYPE;
v_salary            employees.salary%TYPE;
v_commission_pct    employees.commission_pct%TYPE;

-- We would add...

c_comm_pct_sa_mang_adjustment   CONSTANT  employees.commission_pct%TYPE:= 0.4;
c_comm_pct_sa_rep_adjustment    CONSTANT  employees.commission_pct%TYPE:= -0.25;

-- Deeper within your block, you would now reference constant variables instead of
-- raw values...

v_commission_pct:=   v_commission_pct + c_comm_pct_sa_mang_adjustment;

Through this approach, you would push ALL potentially variable increment amounts to the top of the script, in the declaration block, so you're less likely to need changes within the code that's been figured out... less room for error or getting confused if changes are necessary and too much time has passed since you last ran this...

Some Comments on the Code Presented

BEGIN

SELECT job_id, salary, COMMISSION_PCT
  INTO v_job_id, v_salary, v_commission_pct 
  FROM employees
 WHERE employee_id = v_employee_id;

-- This segment appears to repeat the following block, though the expression
-- "Salary = 10500" should have errored out because the typo as it is now makes 
-- Salary a undeclared variable. 

     IF v_job_id = 'SA MANG' AND Salary = 10500
     THEN    
        v_commission_pct :=     v_commission_pct + 0.4

     ELSE

-- If the intent is TWO commission increases of 0.4 for the same employee, it would
-- not get to the second increase because the ELSE clause makes each half of the
-- IF-THEN-ELSE statement mutually exclusive.  The procedure would exit out of this
-- clause the first time its criteria for the data is met.



IF v_job_id = 'SA MANG' AND  v_salary = 10500
THEN    
   v_commission_pct :=     v_commission_pct + 0.4;

ELSIF v_job_id = 'SA_REP' AND v_salary = 9000 
THEN
   v_commission_pct := v_commission_pct - 0.25;

        END IF;
    END IF;
END IF;


-- There is one too many "END IF" clauses which would make this piece of PL/SQL not
-- runnable through completion.

/* Update the corresponding row for the employee with the new commission */

UPDATE employees
SET commission_pct = v_commission_pct
WHERE employee_id = v_employee_id;

/* End anonymous PL/SQL block */

END;
/

The DBMS_OUTPUT Built-in Package: A Programmer's Debugging Friend

Some additional notes to help you in the long run so that you can isolate errors faster if you're new to SQL Plus...

A useful tool to get better feedback on your PL/SQL code is to set up check-points in your code which dumps a notice as screen output. The command to enable this in your SQL Plus session is:

EXEC DBMS_OUTPUT.ENABLE;

Or, alternatively

SET SERVEROUTPUT ON

The typical usage is to insert a line in your PL/SQL block formatted as:

DBMS_OUTPUT.PUT_LINE('Completed this block of instructions.');

Some additional info on this utility can be found in the Oracle Documentation on the DBMS_OUTPUT Package.

Ashish sinha · Accepted Answer · 2014-03-06 04:38:01Z

1

In your code in the if condition you have mentioned salary but it should be v_salary and v_job_id the code will work try this:

DECLARE

v_employee_id employees.employee_id%TYPE := 149;

v_job_id        employees.job_id%TYPE;

v_salary employees.salary%TYPE;

v_commission_pct   employees.COMMISSION_PCT%TYPE;

BEGIN

SELECT job_id, salary,commission_pct

INTO v_job_id, v_salary,v_commission_pct

FROM employees

WHERE employee_id = v_employee_id;


IF   v_job_id = 'SA_MAN' and v_salary = 10500 then

v_commission_pct := nvl(v_commission_pct,0) + 0.4;

v_salary := v_salary + 100;

END IF;

dbms_output.put_line(v_commission_pct|| ' ' ||v_salary);

END;

answered Mar 6, 2014 at 4:38

Ashish sinha

1481 gold badge2 silver badges9 bronze badges

1 Comment

Ashish sinha Over a year ago

In one of the if statement you have written salary := 10500 but you have to write v_salary := 10500 thats it

Eng. Samer T · Accepted Answer · 2014-03-06 05:07:16Z

0

you may see the changes in same session, but when you finished or end the session your modifications will discard. to save changes to "employee" table permanently you have to commit it after update statement.

commit;

answered Mar 6, 2014 at 5:07

Eng. Samer T

6,5666 gold badges39 silver badges45 bronze badges

Comments

Ranjith M R · Accepted Answer · 2017-09-15 06:54:46Z

0

Use the update statement

UPDATE employee_table SET commision_pct = commision_pct + 0.2 WHERE employee_id = XXX;

You can replace the XXX with the employee_id of ur desired one whose commision u need to change and you can give more conditions also if you need

Hope this will helps

edited Sep 15, 2017 at 6:54

answered Sep 15, 2017 at 6:49

Ranjith M R

313 bronze badges

Collectives™ on Stack Overflow

PLSQL adding a value to a variable

4 Answers 4

Manipulating Variables through an Anonymous PL/SQL Block

Quick Notes on Incrementing Variables

Some Comments on the Code Presented

The DBMS_OUTPUT Built-in Package: A Programmer's Debugging Friend

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Manipulating Variables through an Anonymous PL/SQL Block

Quick Notes on Incrementing Variables

Some Comments on the Code Presented

The DBMS_OUTPUT Built-in Package: A Programmer's Debugging Friend

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related