Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Function
PL/SQL Function [message #201174] Thu, 02 November 2006 20:40 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I created a user defined function func1 as follows and expected output sum of salary Group By Deptno but I got Total Salare 1.e 5000 and expected was 3000 for deptno 60 and 2000 for deptno 80
EMP has 5 records
Deptno Salary
---------- ----------
0 0
0 0
0 0
81 2000
60 3000

Also DBMS message is not geeting printed

CREATE OR REPLACE FUNCTION FUNC1(L_DEPTNO VARCHAR2) RETURN NUMBER
AS
CURSOR C_AGG IS
SELECT SUM(SAL)
FROM EMP
GROUP BY L_DEPTNO;
L_SUM EMP.SAL%TYPE;
L_SUMM1 NUMBER;
BEGIN
OPEN C_AGG;
LOOP
FETCH C_AGG INTO L_SUM;
L_SUMM1 := L_SUMM1 ||' '||L_SUM;
DBMS_OUTPUT.PUT_LINE('L_SUM '||L_SUM);
EXIT WHEN C_AGG%NOTFOUND;
END LOOP;
CLOSE C_AGG;
RETURN(L_SUMM1);
END;


Please help me out
Re: PL/SQL Function [message #201210 is a reply to message #201174] Fri, 03 November 2006 01:47 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Just To Display sum of salaries through plsql code use the following procedure

Create the procedure in the database

CREATE OR REPLACE PROCEDURE PROD1 AS
CURSOR c_agg IS
SELECT deptno,SUM(sal)
FROM emp
GROUP BY depno;
l_Sum emp.sal%TYPE;
dno number;
BEGIN
OPEN c_agg;

LOOP
FETCH c_agg INTO dno,l_Sum;

EXIT WHEN c_agg%NOTFOUND;

dbms_Output.Put_Line('Sum of salary of deptno '||dno||' is '||l_Sum);

END LOOP;

CLOSE c_agg;

END;


for displaying through dbms_output.put_line serveroutput must be on as follows

SQL> set serverout on
SQL> execute prod1

-Ramesh
Re: PL/SQL Function [message #201387 is a reply to message #201210] Fri, 03 November 2006 19:58 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Thank you, but i want to know whether i can use Function with input parameter and use that parametrer in Group By clause.I want to know this please.Why I am not getting expected output

Why it not groupiung it by DEPTNO?
Is that function can Return only one record?
Re: PL/SQL Function [message #201616 is a reply to message #201387] Mon, 06 November 2006 01:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your original query doesn't ask the cursor to group by DEPNO. that would be
GROUP BY depno
. Your original query asks the cursor to group the values by the input parameter L_Depno, which is the same for each row that is returned.

If you want to return the total salary paid to employees in a given department, a query that would do this would be
SELECT SUM(SAL)
FROM   EMP
WHERE deptno = l_deptno;
Previous Topic: Ref Cursor SQL*plus
Next Topic: ora-00022
Goto Forum:
  


Current Time: Sun Dec 04 22:57:06 CST 2016

Total time taken to generate the page: 0.12267 seconds