| SUM & Cursor [message #640224] |
Fri, 24 July 2015 01:48  |
nqtrung
Messages: 25
Registered: April 2007
|
Junior Member |
|
|
Hi all
I have 4 below queries:
Query 1:
DECLARE
v_sal NUMBER;
BEGIN
SELECT SUM(SAL) INTO v_sal FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)
WHERE D.DNAME IN ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');
DBMS_OUTPUT.PUT_LINE('Toatl Sal: ' || v_sal);
END;
/
Result 1: 29025
Query 2:
DECLARE
CURSOR c1(dname VARCHAR) IS
SELECT SUM(SAL) FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)
WHERE D.DNAME=dname;
v_sal NUMBER;
BEGIN
OPEN c1('RESEARCH');
FETCH c1 INTO v_sal;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('Toatl Sal: ' || v_sal);
END;
/
Result 2: 29025
Query 3:
DECLARE
CURSOR c1(p_dname VARCHAR) IS
SELECT SUM(SAL) FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)
WHERE D.DNAME=p_dname;
v_sal NUMBER;
BEGIN
OPEN c1('RESEARCH');
FETCH c1 INTO v_sal;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('Toatl Sal: ' || v_sal);
END;
/
Result 3: 10875
Query 4:
DECLARE
v_sal NUMBER;
BEGIN
SELECT SUM(SAL) INTO v_sal FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)
WHERE D.DNAME='RESEARCH';
DBMS_OUTPUT.PUT_LINE('Toatl Sal: ' || v_sal);
END;
/
Result 4: 10875
I think that query 2 must return the result as same as result 3, but not it returns the result as result 1. I don't understand the reason why the query 2 didn't apply condition for OPEN c1('RESEARCH'). Pls explain for me, thanks so much
[Updated on: Fri, 24 July 2015 01:53] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: SUM & Cursor [message #640227 is a reply to message #640224] |
Fri, 24 July 2015 01:57  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
nqtrung wrote on Fri, 24 July 2015 12:18[code]
WHERE D.DNAME=dname;
dname is the column name used here and not the parameter. It is just like 1 = 1, always true.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
