您的位置: 旅游网 > 女人

返回一个最终记录 - sys_context

发布时间:2019-09-13 20:02:27

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> 查看本文来源

宝宝脾虚吃什么
治疗小儿便秘的口服药
3岁小孩咳嗽有痰吃什么好的快
五个月宝宝发烧怎么办
猜你会喜欢的
猜你会喜欢的