在Oracle 11g中忽略大小写

Question

有没有什么方法可以设置表或字段忽略大小写？我不想使用较低或较高的功能。如果他们不支持忽略大小写，我对它感到震惊，但我仍然在寻找，但我没有找到任何解决方案

Answer 1

正如你所说的，你不能明确地创建一个“不区分大小写”的列。

但是，您可以通过使用虚拟列，触发器，检查约束或仅使用不区分大小写的谓词和上/下函数来查询它。

一些例子：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as [email protected] 

SQL> 
SQL> create table job (JOB_TITLE varchar2(40)); 
Table created 
SQL> alter table job add constraint ck_job_upper check (job_title = upper(job_title)); 
Table altered 
SQL> insert into job values ('clerk'); 
insert into job values ('clerk') 
ORA-02290: restrição de verificação (FSITJA.CK_JOB_UPPER) violada 
SQL> insert into job values ('CLERK'); 
1 row inserted 
SQL> commit; 
Commit complete 
SQL> create or replace trigger tr_br_upper_job_title before insert or update of job_title on job 
    2 for each row 
    3 begin 
    4 :new.job_title := upper(:old.job_title); 
    5 end; 
    6/
Trigger created 
SQL> insert into job values ('clerk'); 
1 row inserted 
SQL> select * from job; 
JOB_TITLE 
---------------------------------------- 
CLERK 

SQL> commit; 
Commit complete 

SQL> 
SQL> drop table job; 
Table dropped 
SQL> create table job (JOB_TITLE varchar2(40), upper_job_t as (upper(job_title))); 
Table created 
SQL> insert into job (job_title) values ('CLERK'); 
1 row inserted 
SQL> insert into job (job_title) values ('clerk'); 
1 row inserted 
SQL> commit; 
Commit complete 
SQL> select * from job where regexp_like(job_title, 'CLERK', 'i'); 
JOB_TITLE        UPPER_JOB_T 
---------------------------------------- ---------------------------------------- 
CLERK         CLERK 
clerk         CLERK 

SQL>