Re: update statement PL/SQL

Użytkownik Reallybig napisał:

> Like I mentioned that i'm a newbie.
>
> I'm using oracle 10g Enterprise.
> My OS is win 2003 server with 2.8 ghz and 512 of ram.
> Index are on empid field for both tables.
> The actual code would be sth like
>
> DECLARE
>
> TYPE emp_record_type IS record(
> empno Emp.empno%type,
> sales Emp.amount%type);

No need.

> emp_record emp_record_type;
>
> CURSOR c1 IS
> SELECT emp.empno, emp.sale from sales;

emp_record c1%ROWTYPE;

> BEGIN
> OPEN c1;
> FETCH c1 INTO emp_record;
> while c1%FOUND
> loop
> update employee
> set total_sales = emp_record.sales
> where empid = emp_record.empno;
> FETCH c1 INTO emp_record;
> END LOOP;
> CLOSE c1;
> END;
> /
>
> Thanks in advance.

My Crystal Ball said you would love to store summary of sales.

  UPDATE EMPLOYEE E

     SET E.TOTAL_SALES =
       (
        SELECT SUM(S.SALE)
          FROM SALES S
         WHERE S.EMPNO = E.EMPID
       );

Make sure that SALES.EMPNO is indexed.

-- 
Noel