Oracle 表分区 函数列

mp7x6996jj

create table TEST_PART2
(
mobile        VARCHAR2(20) not NULL,
servicecode   VARCHAR2(30) not NULL,
servicefix VARCHAR2(100) GENERATED ALWAYS AS  (  
CASE
WHEN LENGTH(servicecode) = 1 THEN '00'||servicecode
WHEN LENGTH(servicecode) = 2 THEN '0'||servicecode
ELSE SUBSTR(servicecode,LENGTH(servicecode)-2)
END   
)VIRTUAL
)
PARTITION BY RANGE(servicefix)
(  
PARTITION part200000 VALUES LESS THAN('000') ,
PARTITION part200001 VALUES LESS THAN('001'),
PARTITION part200002 VALUES LESS THAN('100'),
PARTITION part200003 VALUES LESS THAN('999'),
PARTITION part200004 VALUES LESS THAN(MAXVALUE)
);
INSERT INTO test_part2(mobile,servicecode) VALUES('0', '0');
INSERT INTO test_part2(mobile,servicecode) VALUES('1', '1');
INSERT INTO test_part2(mobile,servicecode) VALUES('2', '10');
INSERT INTO test_part2(mobile,servicecode) VALUES('3', '19');
INSERT INTO test_part2(mobile,servicecode) VALUES('4', '000');
INSERT INTO test_part2(mobile,servicecode) VALUES('5', '1004');
INSERT INTO test_part2(mobile,servicecode) VALUES('6', '1014');
INSERT INTO test_part2(mobile,servicecode) VALUES('7', '1104');
INSERT INTO test_part2(mobile,servicecode) VALUES('8', '11004');
INSERT INTO test_part2(mobile,servicecode) VALUES('9', '12004');
INSERT INTO test_part2(mobile,servicecode) VALUES('10', '13004');
SELECT mobile,servicecode,servicefix,SUBSTR(servicecode,LENGTH(servicecode)-2) FROM test_part2 PARTITION(part200002)