Oracle 数字查询问题,我的比较笨,求助有没有更好的办法。
存储数据的表 :TEST, 字段名NUM_ID (为5-13位数字)取出结果存放到表: TEST_NUM
运行环境: ORACLE
要求: 取出NUM_ID的任意6位数满足下列条件之一的所有编号,存入结果表中。
1)ABCDEF 连续递增 2)AAABBB A!=4,B=0,1,2,3,5 3)AAAAAB A!=4, B!=4
以下是我写的代码,在使用to_number()进行递增判断时,会报错:无效数字,所以采用了to_char处理。
程序代码:
DECLARE POS NUMBER; BEGIN POS:=1; FOR POS IN 1 .. 8 LOOP INSERT INTO TEST_NUM SELECT T.NUM_ID FROM TEST T WHERE ( (--ABCDEF '递增' LENGTH(SUBSTR(T.NUM_ID, POS, 6)) >= 6 AND TO_CHAR(SUBSTR(T.NUM_ID, POS, 6)) IN ('123456', '234567', '345678', '456789','012345') ) OR (--AAABBB LENGTH(SUBSTR(T.NUM_ID, POS, 6)) >= 6 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 1,3)) != '444' AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 4,1)) = TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 5,1))--4=5 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 4,1)) = TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 6,1))--4=6 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 4,1)) IN ('0','1','2','3','5') ) OR (--AAAAAB LENGTH(SUBSTR(T.NUM_ID, POS, 6)) >= 6 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 6,1)) != 4--B!=4 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 1,1)) != 4--A!=4 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 1,1)) = TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 2,1))--1=2 AND TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 1,1)) = TO_CHAR(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 3,1))--1=3 AND SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 1,5) = REVERSE(SUBSTR(SUBSTR(T.NUM_ID, POS, 6), 1,5))--1=5,2=4 ) ); COMMIT; END LOOP; END;