CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
chintogtokh
chintogtokh

Reputation: 803

PL/SQL script throws errors

I'm using PLSQL to create a procedure to update a table based on querying another table, and I'm using a loop for the purpose. FOr testing, I'm holding off on the procedure code, and just trying to make the following script work. But it keeps throwing errors including "Encountered the symbol "end-of-file"" or "invalid SQL statement", based on minor tweaks. Where am I going wrong?

DECLARE CURSOR cur IS
    SELECT * FROM Summary;
BEGIN
FOR rec in cur
LOOP
UPDATE Award
SET monthly_sales = (
   SELECT COUNT(*)
   FROM Sales
   WHERE employee_id = rec.employee_id
     AND to_char(Sales.SALES_DATE,'YY/MM')=to_char(SYSDATE,'YY/MM')
)
WHERE Summary.employee_id = rec.employee_id
END LOOP
END;
/

Upvotes: 1

Views: 60

Answers (2)

Boneist
Boneist

Reputation: 23588

As well as the missing semicolons that Sentinel pointed out, your where clause is incorrect:

WHERE Summary.employee_id = rec.employee_id;

Perhaps you meant:

WHERE award.employee_id = rec.employee_id;

since you're updating the AWARD table?

However, I would question why you're using a row-by-row (aka slow-by-slow) approach for this, when you could easily do it in a single statement? Perhaps something like the following would give you the correct results (untested, since you gave no table create scripts or sample input data etc):

merge into award tgt
using (select sls.employee_id,
              count(*) monthly_sales
       from   sales sls
       where  trunc(sls.sales_date,'mm') = trunc(sysdate, 'mm')
       and    sls.employee_id in (select employee_id from summary)
       group by sls.employee_id) src
  on (tgt.employee_id = src.employee_id)
when matched then
update set tgt.monthly_sales = src.monthly_sales;

Upvotes: 5

Sentinel
Sentinel

Reputation: 6459

You are missing semicolons at the end of lines 12 and 13:

    DECLARE CURSOR cur IS
    SELECT * FROM Summary;
BEGIN
  FOR rec in cur
  LOOP
    UPDATE Award
    SET monthly_sales = (
       SELECT COUNT(*)
       FROM Sales
       WHERE employee_id = rec.employee_id
         AND trunc(Sales.SALES_DATE,'month') = trunc(SYSDATE,'month')
    )
    WHERE Summary.employee_id = rec.employee_id;
  END LOOP;
END;
/

Upvotes: 4

Related Questions