| CASE Statement on a Cursor [message #681025] |
Mon, 08 June 2020 05:16  |
 |
ajayhareesh
Messages: 24
Registered: September 2016
|
Junior Member |
|
|
Is it possible to use case statement in a cursor?
Just a sample thought:
CURSOR cur IS
CASE WHEN ledger_id = 1000
SELECT emp_name FROM employees
ELSE
SELECT emp_name FROM managers
END
;
|
|
|
|
| Re: CASE Statement on a Cursor [message #681026 is a reply to message #681025] |
Mon, 08 June 2020 07:40  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
no but you can do the following
CURSOR Cur (P_ledger_id IN NUMBER)
IS
SELECT Emp_name
FROM Employees
WHERE P_ledger_id = 1000
UNION ALL
SELECT Emp_name
FROM Managers
WHERE P_ledger_id <> 1000; |
|
|
|
|
|
| Re: CASE Statement on a Cursor [message #681045 is a reply to message #681036] |
Tue, 09 June 2020 06:37 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I'd use cursor variables:
DECLARE
V_CUR SYS_REFCURSOR;
V_LEDGER_ID NUMBER;
BEGIN
IF V_LEDGER_ID = 1000
THEN
OPEN V_CUR FOR 'SELECT emp_name FROM employees';
ELSE
OPEN V_CUR FOR 'SELECT emp_name FROM managers';
END IF;
Using UNION ALL will scan both tables. Also, UNION ALL Michel posted isn't equivalent to your CASE statement when LEDGER_ID is NULL. In general, it should be:
CURSOR Cur (P_ledger_id IN NUMBER)
IS
SELECT Emp_name
FROM Employees
WHERE P_ledger_id = 1000
UNION ALL
SELECT Emp_name
FROM Managers
WHERE P_ledger_id <> 1000
OR P_ledger_id IS NULL;
|
|
|
|
| Re: CASE Statement on a Cursor [message #681048 is a reply to message #681045] |
Tue, 09 June 2020 07:11 |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
For once, it is was not me but Bill. 
Quote:Using UNION ALL will scan both tables.
This is not true.
As the WHERE clauses are exclusive and static, the optimizer knows (since version 7) that only one part of the UNION ALL needs to be executed (this is how we could emulate partitions in version 7).
But I agree with you about NULL which has to be specified and the use of cursor variables for code readability.
[Updated on: Tue, 09 June 2020 11:26] Report message to a moderator |
|
|
|
| Re: CASE Statement on a Cursor [message #681049 is a reply to message #681048] |
Tue, 09 June 2020 07:39 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Agreed:
SQL> EXPLAIN PLAN FOR
2 SELECT deptno
3 FROM emp
4 WHERE :P_ledger_id = 1000
5 UNION ALL
6 SELECT deptno
7 FROM dept
8 WHERE :P_ledger_id <> 1000
9 OR :P_ledger_id IS NULL;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3683458442
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 54 | 4 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(:P_LEDGER_ID)=1000)
4 - filter(TO_NUMBER(:P_LEDGER_ID)<>1000 OR :P_LEDGER_ID IS NULL)
18 rows selected.
SQL>
[Updated on: Tue, 09 June 2020 07:41] Report message to a moderator |
|
|
|
] 
Search
Help
Members
Register
Login
Home
