大家好，又见面了，我是你们的朋友全栈君。
Oracle唯一索引在字段全部为NULL时，不做唯一性判断，允许重复插入，而在8t中即使均为NULL值也会做重复值判断，在某些场景下客户会存在此类需求，在数据量不大不存在性能问题的情况下可以考虑通过如下方式进行替代
示例表
create table “informix”.secconstitute
(
id decimal(20,0) not null ,
code varchar(32),
name varchar(64),
sec_id decimal(20,0),
meas_id decimal(20,0),
constitute_type decimal(10,0),
order_no decimal(10,0),
meas_value decimal(22,6),
ttc_therm decimal(22,6),
region_id decimal(10,0),
replicate_flag decimal(10,0),
download_region decimal(10,0),
operator_str varchar(64),
factor_str varchar(64),
if_reverse decimal(3,0),
condi_type decimal(10,0),
primary key (id) constraint “informix”.pk_secconstitute
) extent size 64 next size 64 lock mode row;
revoke all on “informix”.secconstitute from “public” as “informix”;
create unique index “informix”.idx_secid_measid_constitutetype_orderno
on “informix”.secconstitute (sec_id,meas_id,constitute_type,
order_no) using btree in dbs3;
思路
1.删除原唯一索引替换为普通索引维持索引功能
2.通过触发器调用SPL进行非NULL值的唯一性判断，必要时中止操作
代码如下
drop index if exists index_438_1;
create index index_438_1 on secconstitute(sec_id,meas_id,constitute_type,order_no);
drop procedure if exists p4_ti_secconstitute_proc;
create procedure p4_ti_secconstitute_proc() referencing new as new for secconstitute
define v_str varchar(100);
define v_col varchar(100);
define v_sql lvarchar(500);
define v_count int8;
define v_flag int;
let v_col=”;
let v_str=”;
let v_count=0;
let v_sql=”;
if new.sec_id is not null or new.meas_id is not null or new.constitute_type is not null or new.order_no is not null then
if new.sec_id is not null then
let v_col=’sec_id’;
let v_str=’sec_id=’||new.sec_id;
else
let v_col=’sec_id’;
let v_str=’sec_id is null’;
end if;
if new.meas_id is not null then
if v_col != ” then
let v_col=v_col||’,’||’meas_id’;
else
let v_col=’meas_id’;
end if;
if v_str != ” then
let v_str=v_str||’ and meas_id=’||new.meas_id;
else
let v_str=’meas_id=’||new.meas_id;
end if;
else
if v_col != ” then
let v_col=v_col||’,’||’meas_id’;
else
let v_col=’meas_id’;
end if;
if v_str != ” then
let v_str=v_str||’ and meas_id is null’;
else
let v_str=’meas_id is null’;
end if;
end if;
if new.constitute_type is not null then
if v_col != ” then
let v_col=v_col||’,’||’constitute_type’;
else
let v_col=’constitute_type’;
end if;
if v_str != ” then
let v_str=v_str||’ and constitute_type=’||new.constitute_type;
else
let v_str=’constitute_type=’||new.constitute_type;
end if;
else
if v_col != ” then
let v_col=v_col||’,’||’constitute_type’;
else
let v_col=’constitute_type’;
end if;
if v_str != ” then
let v_str=v_str||’ and constitute_type is null’;
else
let v_str=’constitute_type is null’;
end if;
end if;
if new.order_no is not null then
if v_col != ” then
let v_col=v_col||’,’||’order_no’;
else
let v_col=’order_no’;
end if;
if v_str != ” then
let v_str=v_str||’ and order_no=’||new.order_no;
else
let v_str=’order_no=’||new.order_no;
end if;
else
if v_col != ” then
let v_col=v_col||’,’||’order_no’;
else
let v_col=’order_no’;
end if;
if v_str != ” then
let v_str=v_str||’ and order_no is null’;
else
let v_str=’order_no is null’;
end if;
end if;
if v_str != ” then
let v_sql=’select count(*) from secconstitute where ‘||v_str||’ group by ‘||v_col;
prepare p from v_sql;
declare c cursor for p;
open c;
fetch c into v_count;
free p;
close c;
free c;
end if;
if v_count >1 then
raise exception -746,0,’Duplicated Sec_Id,Meas_Id,Constitue_Type,Order_No Value Founded!!’;
end if;
end if;
end procedure;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/192910.html原文链接：https://javaforall.cn
