返回一个最终记录 - sys_context

　　here is the table:
　　create table t1 (code varchar2(10), language varchar2(10),
　　dscr varchar2(10));
　　
　　with this data:
　　insert into t1 values ('X','EN','english_dscr');
　　insert into t1 values ('X','FR','french_dscr');
　　
　　how can i write a query or create a view to return the english record
　　when i do not in clude the language column in the where part of the query.
　　
　　example.
　　select * from t1 where code='X';
　　to return the english record.
　　or
　　select * from t1 where code='X' and language='FR'
　　to return the french record.
　　
　　thanks for your solution.
　　Unfortunately, it won't solve my problem.
　　i need to query, not based on my default language but on the language as
　　requested by the user who desires the info.
　　
　　------------------------------------------------------------------------------------
　　
　　You can use your "own" context as well via create context -- this uses the
　　default one that is there for all (but could be unreliable for the same reason
　　-- anything could change it) -- but shows the concept:
　　
　　ops$tkyte@ORA9IR2> drop table t1;
　　
　　Table dropped.
　　
　　ops$tkyte@ORA9IR2>
　　ops$tkyte@ORA9IR2>
　　ops$tkyte@ORA9IR2> create table t1 (code varchar2(10), language varchar2(10),
　　2　dscr varchar2(20));
　　
　　Table created.
　　
　　ops$tkyte@ORA9IR2>
　　ops$tkyte@ORA9IR2> insert into t1 values ('X','EN','english_dscr');
　　
　　1 row created.
　　
　　ops$tkyte@ORA9IR2> insert into t1 values ('X','FR','french_dscr');
　　
　　1 row created.
　　
　　ops$tkyte@ORA9IR2>
　　ops$tkyte@ORA9IR2> create or replace view v
　　2　as
　　3　select * from t1
　　4　where language = case when sys_context( 'userenv', 'client_info') in (
　　'EN', 'FR' )
　　5　　　　　　　　　　　　then sys_context( 'userenv', 'client_info' )
　　6　　　　　　　　　　　　else 'EN'
　　7　　　　　　　　　 end;
　　
　　View created.
　　
　　ops$tkyte@ORA9IR2>
　　ops$tkyte@ORA9IR2> select * from v;
　　
　　CODE　　　 LANGUAGE　 DSCR
　　---------- ---------- --------------------
　　X　　　　　EN　　　　 english_dscr
　　
　　ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 'FR' );
　　
　　PL/SQL procedure successfully completed.
　　
　　ops$tkyte@ORA9IR2> select * from v;
　　
　　CODE　　　 LANGUAGE　 DSCR
　　---------- ---------- --------------------
　　X　　　　　FR　　　　 french_dscr
　　
　　ops$tkyte@ORA9IR2>

【责编:admin】

--------------------next---------------------