Home » SQL & PL/SQL » SQL & PL/SQL » group function in cursors
group function in cursors [message #197667] Thu, 12 October 2006 04:24 Go to next message
amena
Messages: 14
Registered: August 2005
Location: pak
Junior Member
can i use group fuction in cursors

suppose i write a cursor through which i want to update comm so that it is equal to sum of sal;


declare
cursor c1 is
select sum(sal) from emp;
begin
for emp_rec in c1 loop
update emp
set comm =emp_rec.sum(sal).......it give an error ---
end loop;
end;

i hope some one might have answer

Re: group function in cursors [message #197669 is a reply to message #197667] Thu, 12 October 2006 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I was going to write a witty little response, but I can't be bothered.
You need to give the sum(sal) column an alias in your cursor and then use that alias in the update.

declare 
  cursor c1 is 
    select sum(sal) sum_sal 
    from   emp;
begin
  for emp_rec in c1 loop 
    update emp 
    set comm =emp_rec.sum_sal
  end loop;
end;


I'd have thought that the error message
PLS-00302: component 'SUM' must be declared
ORA-06550: line 7, column 13:
PL/SQL: ORA-00904: "EMP_REC"."SUM": invalid identifier
ORA-06550: line 6, column 1:
would have given you a clue though.
Re: group function in cursors [message #197681 is a reply to message #197669] Thu, 12 October 2006 05:07 Go to previous message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Just a little correction with the code: One Semi Colon is missing.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> declare 
  2    cursor c1 is 
  3      select sum(sal) sum_sal 
  4      from   emp;
  5  begin
  6    for emp_rec in c1 loop 
  7      update emp 
  8      set comm =emp_rec.sum_sal
  9    end loop;
 10  end;
 11  /
    from   emp;
  *
ERROR at line 4:
ORA-06550: line 9, column 3:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop


SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is
  3      select sum(sal) sum_sal
  4      from   emp;
  5  begin
  6    for emp_rec in c1 loop
  7      update emp
  8      set comm =emp_rec.sum_sal;     --Semicolon comes here
  9    end loop;
 10* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800      29025         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600      29025         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250      29025         30
      7566 JONES      MANAGER         7839 02-APR-81       2975      29025         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250      29025         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850      29025         30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450      29025         10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000      29025         20
      7839 KING       PRESIDENT            17-NOV-81       5000      29025         10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      29025         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100      29025         20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950      29025         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000      29025         20
      7934 MILLER     CLERK           7782 23-JAN-82       1300      29025         10

14 rows selected.


regards
Saadat Ahmad
Previous Topic: Please help me out
Next Topic: RE:PL/SQL Table
Goto Forum:
  


Current Time: Fri Dec 09 13:34:13 CST 2016

Total time taken to generate the page: 0.08773 seconds