http://blog.itpub.net/post/901/5665
下面是TOM使用set_client_info优化SQL函数调用的程序
ops$tkyte@ORA920> create or replace function myinstr( s1 in varchar2, s2 in
varchar,
2 n1 in number default null, n2 in numberdefault null) return number
3is
4begin
5
dbms_application_info.set_client_info(userenv('client_info')+1);
6
return instr(s1,s2,n1,n2);
7end;
8/
Function created.
ops$tkyte@ORA920> drop table t;
Table dropped.
ops$tkyte@ORA920> create table t ( x varchar2(15) );
Table created.
ops$tkyte@ORA920> insert into t values ( '1.1.1.1' );
1 row created.
ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select substr(x, 1, d1-1) x1,
2 substr(x, d1+1, d2-d1-1) x2,
3 substr(x, d2+1, d3-d2-1) x3,
4 substr(x, d3+1) x4
5from
6 (select x,
7
myinstr(x,'.',1,1) d1,
8
myinstr(x,'.',1,2) d2,
9
myinstr(x,'.',1,3) d3
10from t);
X1
X2
X3
X4
--------------- --------------- --------------- ---------------
1
1
1
1
ops$tkyte@ORA920> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
8
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select x,
2 substr( x, 1, myinstr(x,'.')-1 ) x1,
3 substr( x, myinstr(x,'.')+1, myinstr(x,'.',1,2)-myinstr(x,'.')-1 ) x2,
4 substr( x, myinstr(x,'.',1,2)+1, myinstr(x,'.',1,3)-myinstr(x,'.',1,2)-1
) x3,
5 substr( x, myinstr(x,'.',1,3)+1 ) x4
6from t
7/
X
X1
X2
X3
X4
--------------- --------------- --------------- --------------- ---------------
1.1.1.1
1
1
ops$tkyte@ORA920> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
6
set_client_info对SESSION来说 是很好的全局变量。
|
