使用check SQL> create table test (sfz varchar2(18));Table createdSQL> alter table test add constraint ck_test_sfz check (length(sfz)=15 or length(sfz)=18);Table altered
能达到要求的笨方法 create table test (sfz varchar2(18)); alter table test add constraint ck_test_sfz check (DECODE(length(sfz), 15, 1, DECODE(length(sfz), 18, 1, 0)) = 1 and (NVL(LENGTH(REPLACE(TRANSLATE(sfz, '1234567890',' '), ' ','')), 0) = 0));
不知道你的里面能不能用TRANSLATE函数,可以的话可以加约束 SQL> create table t(x number check (TRANSLATE(x,'0123456789','999999999999999')=999999999999999 o r TRANSLATE(x,'0123456789','999999999999999999')=999999999999999999 ));表已创建。SQL> insert into t values(123456789012345);已创建 1 行。SQL> insert into t values(1234567890123456); insert into t values(1234567890123456) * 第 1 行出现错误: ORA-02290: 违反检查约束条件 (WF.SYS_C0010137) SQL> insert into t values(123456789012345678);已创建 1 行。
SQL> create table test (sfz varchar2(18));Table createdSQL> alter table test add constraint ck_test_sfz check (length(sfz)=15 or length(sfz)=18);Table altered
create table test (sfz varchar2(18));
alter table test add constraint ck_test_sfz check (DECODE(length(sfz), 15, 1, DECODE(length(sfz), 18, 1, 0)) = 1 and (NVL(LENGTH(REPLACE(TRANSLATE(sfz, '1234567890',' '), ' ','')), 0) = 0));
SQL> create table t(x number check (TRANSLATE(x,'0123456789','999999999999999')=999999999999999 o
r TRANSLATE(x,'0123456789','999999999999999999')=999999999999999999 ));表已创建。SQL> insert into t values(123456789012345);已创建 1 行。SQL> insert into t values(1234567890123456);
insert into t values(1234567890123456)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (WF.SYS_C0010137)
SQL> insert into t values(123456789012345678);已创建 1 行。