CODEWITHSUNDEEP
oracle-databaseplsql
Anto Alukka Davies
Anto Alukka Davies

Reputation: 11

Errors with identifiers in PL/SQL

I am trying to run some PL/SQL code but it contains some errors about identifiers plese help me with it. The code is not running

DECLARE
   a := customer.purchase%TYPE;
   id := &employee.empno;
BEGIN
   UPDATE employee SET salary = salary + 5000;
   UPDATE employee SET bonus = bonus + 1000 WHERE empno = &id;
   SAVEPOINT sumeet;
   UPDATE customer SET purchase = purchase + 5000 WHERE custid = a;

   SELECT SUM(purchase) INTO a;

   IF (a < 11000) THEN
      ROLLBACK sumeet;
   END IF;
   COMMIT;

END;
/

Upvotes: 1

Views: 74

Answers (4)

Alen Oblak
Alen Oblak

Reputation: 3325

Try with this block, but first change the values (1, 2) in the declaration block.

DECLARE
   a customer.purchase%TYPE := 1;
   id employee.empno%TYPE := 2;
BEGIN
   UPDATE employee SET salary = salary + 5000;
   UPDATE employee SET bonus = bonus + 1000 WHERE empno = id;
   SAVEPOINT sumeet;
   UPDATE customer SET purchase = purchase + 5000 WHERE custid = a;

   SELECT SUM(purchase) INTO a FROM customer;

   IF (a < 11000) THEN
      ROLLBACK sumeet;
   END IF;
   COMMIT;

END;
/

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52386

Here's a tip: Combine your two updates on employee into one.

 UPDATE employee
 SET    salary = salary + 5000,
        bonus  = bonus  + case when empno = &id then 1000 else 0 end;

Also, start using meaningful variable names.

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

1: This is wrong:

DECLARE
  a := customer.purchase%TYPE;
  id := &employee.empno;

You don't put a := after the variable name, & is invalid, and employee.empno is not a valid data type. i.e.:

DECLARE
  a customer.purchase%TYPE;
  id employee.empno%TYPE;

2: You don't need & to refer to the id variable:

UPDATE employee SET bonus = bonus + 1000 WHERE empno = &id;

i.e.:

UPDATE employee SET bonus = bonus + 1000 WHERE empno = id;

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

in addition to Alen's fix, instead of the ROLLBACK why don't you do :

   UPDATE customer SET purchase = purchase + 5000 
    WHERE custid = a
      AND (select sum(purchase) from customer) + 5000 < 11000;

  COMMIT;

Upvotes: 1

Related Questions