How to use CURSORS to find No. of rows affected by UPDATE query

1
votes
CREATE TABLE cursor_example(
emp_id NUMBER(10) PRIMARY KEY,
emp_name VARCHAR2(30),
emp_salary NUMBER(6)
);

SELECT * FROM cursor_example;

INSERT INTO cursor_example VALUES(1234,'apple',1250);
INSERT INTO cursor_example VALUES(1235,'banana',1500);
INSERT INTO cursor_example VALUES(1236,'carrot',1750);
INSERT INTO cursor_example VALUES(1237,'donkey',2050);
INSERT INTO cursor_example VALUES(1238,'elixr',15075);

UPDATE cursor_example 
SET emp_salary = emp_salary + (.25*emp_salary);

DECLARE affected_emp NUMBER(2);
BEGIN
UPDATE cursor_example 
SET emp_salary = emp_salary + (.25*emp_salary);
    IF sql%notfound THEN 
    DBMS_OUTPUT.PUTLINE('NO PEOPLE AFFECTED');
    ELSEIF sql%found THEN
    DBMS_OUTPUT.PUTLINE(affected_emp || 'PEOPLE AFFECTED');
    ENDIF;
END;

The error message I got is :

ORA-06550: line 7, column 12: PLS-00103: Encountered the symbol "SQL" when expecting one of the following: := . ( @ % ;

2
oracleplsql

2 Answers

4
votes

Your actual error is caused by invalid syntax. In PL/SQL it's ELSIF not ELSEIF, although as you're not implementing a multi-branched switch you just need ELSE. Also END IF is two words. Furthermore it's dbms_output.put_line() not putline.

If you fix all those errors you routine will run.

However, sql%found tells us whether our DML hit any records but doesn't tell us how many records. So affected_emp will be null in your code.

The easiest way to find the number of records affected is sql%rowcount which gives us the number of records inserted, updated or deleted by the preceding DML statement. Zero means no records were changed.

Obviously you could just output that figure but here's how it looks in your code:

DECLARE
     affected_emp NUMBER(2);
BEGIN

    UPDATE cursor_example 
    SET emp_salary = emp_salary + (.25*emp_salary);

    affected_emp := sql%rowcount;

    IF affected_emp = 0 THEN 
        DBMS_OUTPUT.PUT_LINE('NO PEOPLE AFFECTED');
    ELSE
        DBMS_OUTPUT.PUT_LINE(affected_emp || ' PEOPLE AFFECTED');
    END IF;
END;
1
votes

You should use ELSIF (or ELSE) :

ELSIF sql%found THEN
DBMS_OUTPUT.put_line(affected_emp || 'PEOPLE AFFECTED');
END IF;

Or ELSE:

ELSE
DBMS_OUTPUT.put_line(affected_emp || 'PEOPLE AFFECTED');
END IF;

syntax for IF-THEN-ELSIF-ELSE in Oracle/PLSQL is:

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

 ELSIF condition2 THEN
   {...statements to execute when condition2 is TRUE...}

ELSE
   {...statements to execute when both condition1 and condition2 are FALSE...}

END IF;