How Can I declare multiple cursors based on the values passed through a procedure and only the WHERE condition will change ?
Lets see how this can be achieved using Secure application contexts to " bind " inputs and using String concatenation (||)
Using secure application context it will be.
rajesh@10GR2> create or replace context my_ctx using p1;
Context created.
Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> create or replace procedure p1(
2 p_empno in number default null,
3 p_sal in number default null,
4 p_deptno in number default null
5 )
6 as
7 l_query varchar2(4000) default ' select * from emp p1 where 1 = 1 ';
8 l_rec emp%rowtype;
9 l_cursor sys_refcursor;
10 begin
11
12 if p_empno is not null then
13 dbms_session.set_context('my_ctx','l_empno',p_empno);
14 l_query := l_query ||' and empno = sys_context(''my_ctx'',''l_empno'') ' ;
15 end if;
16
17 if p_sal is not null then
18 dbms_session.set_context('my_ctx','l_sal',p_sal);
19 l_query := l_query ||' and sal > sys_context(''my_ctx'',''l_sal'') ' ;
20 end if;
21
22 if p_deptno is not null then
23 dbms_session.set_context('my_ctx','l_deptno',p_deptno);
24 l_query := l_query ||' and deptno = sys_context(''my_ctx'',''l_deptno'') ' ;
25 end if;
26
27 open l_cursor for l_query;
28 loop
29 fetch l_cursor into l_rec;
30 exit when l_cursor%notfound;
31 dbms_output.put_line (l_rec.empno||','||l_rec.ename||','||to_char(l_rec.hiredate,'dd-mon-yyyy'));
32 end loop;
33
34 close l_cursor;
35 end;
36 /
Procedure created.
Elapsed: 00:00:00.07
rajesh@10GR2>
Using String concatenation (||) it will be
rajesh@10GR2> create or replace procedure p2(
2 p_empno in number default null,
3 p_sal in number default null,
4 p_deptno in number default null
5 )
6 as
7 l_query varchar2(4000) default ' select * from emp p2 where 1 = 1 ';
8 l_rec emp%rowtype;
9 l_cursor sys_refcursor;
10 begin
11
12 if p_empno is not null then
13 l_query := l_query ||' and empno ='||p_empno ;
14 end if;
15
16 if p_sal is not null then
17 l_query := l_query ||' and sal > '||p_sal ;
18 end if;
19
20 if p_deptno is not null then
21 l_query := l_query ||' and deptno = '||p_deptno ;
22 end if;
23
24 open l_cursor for l_query;
25 loop
26 fetch l_cursor into l_rec;
27 exit when l_cursor%notfound;
28 dbms_output.put_line (l_rec.empno||','||l_rec.ename||','||to_char(l_rec.hiredate,'dd-mon-yyyy'));
29 end loop;
30
31 close l_cursor;
32 end;
33 /
Procedure created.
Elapsed: 00:00:00.04
rajesh@10GR2>
Now both the procedure P1 and P2 remain the same functionality but their implementation differs. Lets see how Shared pool is getting utilized during these routines ( P1, P2) are execute in database.
rajesh@10GR2>
rajesh@10GR2> begin
2 for i in 1..10
3 loop
4 p1(p_deptno=>i);
5 p2(p_deptno=>i);
6 end loop;
7 end;
8 /
7782,CLARK,09-JUN-81
7839,KING,17-NOV-81
7934,MILLER,23-JAN-82
7782,CLARK,09-JUN-81
7839,KING,17-NOV-81
7934,MILLER,23-JAN-82
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
rajesh@10GR2>
Now looking at the Shared Pool contents, the query generated by the procedure P1 using Secure Application context is
rajesh@10GR2>
rajesh@10GR2> select sql_text
2 from v$sqlarea
3 where upper(sql_text) like '%SELECT%FROM%EMP%P1%'
4 and upper(sql_text) not like '%V$SQLAREA%'
5 /
SQL_TEXT
-----------------------------------------------------------------------------------------------
select * from emp p1 where 1 = 1 and deptno = sys_context('my_ctx','l_deptno')
Elapsed: 00:00:00.04
rajesh@10GR2>
Now looking at the Shared Pool contents, the query generated by the procedure P2 NOT using Secure Application will be
rajesh@10GR2> select sql_text
2 from v$sqlarea
3 where upper(sql_text) like '%SELECT%FROM%EMP%P2%'
4 and upper(sql_text) not like '%V$SQLAREA%'
5 /
SQL_TEXT
-----------------------------------------------------------------------------------------------
select * from emp p2 where 1 = 1 and deptno = 7
select * from emp p2 where 1 = 1 and deptno = 9
select * from emp p2 where 1 = 1 and deptno = 2
select * from emp p2 where 1 = 1 and deptno = 4
select * from emp p2 where 1 = 1 and deptno = 3
select * from emp p2 where 1 = 1 and deptno = 8
select * from emp p2 where 1 = 1 and deptno = 6
select * from emp p2 where 1 = 1 and deptno = 5
select * from emp p2 where 1 = 1 and deptno = 10
select * from emp p2 where 1 = 1 and deptno = 1
10 rows selected.
Elapsed: 00:00:00.06
rajesh@10GR2>
So, Using BIND ( Secure Application context ) you will be having Single SQL in shared pool where it will get reused. Without BIND variables you will be ended up with different SQL in shared pool for each values in predicates.
No comments:
Post a Comment