| Cursor [message #441037] |
Thu, 28 January 2010 06:45  |
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
|
Member |
|
|
How can we change the WHERE condition in normal cursor dynamically.
Consider the below example
DECLARE
CURSOR C IS
SELECT * FROM emp WHERE deptno=&a;
Begin
FOR i in C
LOOP
DBMS_OUTPUT.PUT_LINE(i.empno||i.ename);
END LOOP;
END;
/
IN this i want to change the deptno to job.
My requirement is like this
SELECT * FROM emp WHERE job=&a
Please help me on this scenario .
Thanks in advance
|
|
|
|
| Re: Cursor [message #441040 is a reply to message #441037] |
Thu, 28 January 2010 06:57  |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
SQL> DECLARE
2 CURSOR C IS
3 SELECT * FROM sriram WHERE &icond=&a;
4 Begin
5 FOR i in C
6 LOOP
7 DBMS_OUTPUT.PUT_LINE(i.empno||i.ename);
8 END LOOP;
9 END;
10 /
Enter value for icond: sal
Enter value for a: 800
old 3: SELECT * FROM sriram WHERE &icond=&a;
new 3: SELECT * FROM sriram WHERE sal=800;
7369SMITH
PL/SQL procedure successfully completed.
SQL>
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR C IS
3 SELECT * FROM sriram WHERE &icond=&a;
4 Begin
5 FOR i in C
6 LOOP
7 DBMS_OUTPUT.PUT_LINE(i.empno||i.ename);
8 END LOOP;
9* END;
SQL> /
Enter value for icond: job
Enter value for a: 'CLERK'
old 3: SELECT * FROM sriram WHERE &icond=&a;
new 3: SELECT * FROM sriram WHERE job='CLERK';
7369SMITH
7876ADAMS
7900JAMES
7934MILLER
PL/SQL procedure successfully completed.
SQL>
It depends on you...AND THE SITUATION( As based on the information provided)
sriram 
[Updated on: Thu, 28 January 2010 07:03] Report message to a moderator |
|
|
|
|
|
| Re: Cursor [message #441058 is a reply to message #441037] |
Thu, 28 January 2010 07:52 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Of course, you can also have cursors with parameters.
SQL> SET serverout ON;
SQL>
SQL> DECLARE
2 CURSOR C ( p_rows number ) IS
3 SELECT ROWNUM rn FROM all_objects WHERE ROWNUM <= p_rows;
4 Begin
5 FOR i in C (5)
6 LOOP
7 DBMS_OUTPUT.PUT_LINE('A' || i.rn);
8 END LOOP;
9
10
11 FOR i in C (2)
12 LOOP
13 DBMS_OUTPUT.PUT_LINE('B' || i.rn);
14 END LOOP;
15
16 END;
17 /
A1
A2
A3
A4
A5
B1
B2
PL/SQL procedure successfully completed.
SQL>
Having an "&" Client parameter in a stored procedure makes not much sense anyway.
|
|
|
|
| Re: Cursor [message #441060 is a reply to message #441058] |
Thu, 28 January 2010 07:58 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ThomasG wrote on Thu, 28 January 2010 13:52Of course, you can also have cursors with parameters.
That's irrelevant to the OPs question since he wants to change the columns referenced in the where clause.
ThomasG wrote on Thu, 28 January 2010 13:52
Having an "&" Client parameter in a stored procedure makes not much sense anyway.
Would have thought it just won't work, unless you fancy recompiling the procedure each time you want to run it.
|
|
|
|
|
|
|
|
|
|
| Re: Cursor [message #441213 is a reply to message #441037] |
Fri, 29 January 2010 08:53 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
dynamic sql should be used sparingly and with good reason. Most often you don't really need dynamic sql to do what you want. Sometimes you have to write a bit of code but in the end you won't have to use dynamic sql.
For example, is your query expression really unknown till query time, or is it that you just pick from a list of known possible expressions. For example: do you have 30 columns and your query could be against any of these 30 columns? If so you do not need dynamic sql. One way to avoid it would be to use a manually partitioned query with short circuiting. Here is an example:
select ... from table where :1 = 'C1' and c1 = :2 union all
select ... from table where :1 = 'C2' and c2 = :2 union all
...
select ... from table where :1 = 'C30' and c30 = :2
Notice that for 30 different columns, there are 30 different select statements, one each for each of the columns.
Notice also that there is a paramter = constant test that checks to see if the column name held in the parameter is the column relevant to the specific select. This is our short circuiting mechanism. Oracle will evaluate at runtime if the parameter = constant is true or not for each sub query and only run those queries where it is true. In this case each sub query is mutually exclusive to all the others and thus oracle will only execute one of the select statements of our 30 select union all query.
Now this might seem like some work but in reality it is not. Your query is the same all the time. That means this code will scale for you as your system concurrancy increases. It also means that people maintaining this code and debugging errors in this code do not have to guess what the actually query was which was executed. Lastly, there is no chance of "SQL INJECTION" which might be possible in your dynamic solution.
Even though there may be lots more lines of code, the 30 select solution is better than the one select dynamic solution. This is hard for the simple minded to grasp at first but it is 99% always so that a static solution is better than a dynamic solution.
Of course we must evaluate each situation for ourselves.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
