WITH T AS (SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005011' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL) SELECT LPAD(MIN(T1.C1) + 1, 6, 0) FROM (SELECT C1, LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP FROM T WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('00401', 1, 3)) T1 WHERE T1.GAP > 1 OR T1.GAP IS NULL;
WITH T AS (SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005011' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL) SELECT LPAD(MIN(T1.C1) + 1, 6, 0) FROM (SELECT C1, LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP FROM T WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('00401', 1, 3)) T1 WHERE T1.GAP > 1 OR T1.GAP IS NULL;谢谢回答,但是这个算法只保证了增加方向的取得,要求往前也能取。 例如: 020002 020003的情况下,输入02可以返回020001
WITH T AS (SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003007' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005001' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL UNION SELECT '020002' C1 FROM DUAL UNION ALL SELECT '020003' C1 FROM DUAL) SELECT LPAD(DECODE(T2.GAP, NULL, (SELECT DECODE(SUBSTR(MIN(T3.C1), -3), '001', MIN(T3.C1) + 1, MIN(T3.C1) - 1) FROM T T3 WHERE SUBSTR(T3.C1, 1, 3) = SUBSTR('020', 1, 3)), T2.C1 + 1), 6, 0) FROM (SELECT T1.C1, T1.GAP FROM (SELECT C1, LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP FROM T WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('020', 1, 3)) T1 WHERE T1.GAP > 1 OR T1.GAP IS NULL ORDER BY T1.C1) T2 WHERE ROWNUM = 1;
WITH DATASET AS ( SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION ALL SELECT '004012' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL UNION ALL SELECT '004111' C1 FROM DUAL UNION ALL SELECT '004019' C1 FROM DUAL UNION ALL SELECT '005011' C1 FROM DUAL)SELECT CASE WHEN T_CNT = 0 THEN &tp||LPAD('1', 6 - LENGTH(&tp), '0') WHEN F_FLG = 0 THEN &tp||LPAD('1', 6 - LENGTH(&tp), '0') ELSE R_MIN END AS R_MIN FROM ( SELECT COUNT(*) AS T_CNT , NVL(SUM(DECODE(RE, NULL, 0, 1)), 0) AS R_CNT , MIN(RE) AS R_MIN , SUM(DECODE(SUBSTR(C1, LENGTH(&tp) + 1, 6 - LENGTH(&tp)), 1, 1, 0)) AS F_FLG FROM( SELECT TA.*, CASE WHEN AC IS NOT NULL THEN NULL ELSE LPAD(C1 + 1, 6, '0') END AS RE FROM ( SELECT ROW_NUMBER()OVER(ORDER BY T1.C1) AS RID, T1.C1, T2.C1 AS AC FROM DATASET T1 LEFT JOIN DATASET T2 ON T1.C1 = T2.C1 - 1 WHERE SUBSTR(T1.C1, 0, LENGTH(&tp)) = &tp ) TA ) TB ) TC
CREATE TABLE tmp AS (SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005011' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL UNION ALL SELECT '000021' c1 FROM dual UNION ALL SELECT '000028' c1 FROM dual) WITH tmp1 AS ( SELECT regexp_substr(to_char(c1),'[0]+')AS z_c1, regexp_substr(to_char(c1),'[0]+[^0]')AS z_c2, to_number(regexp_substr(to_char(c1),'[^0]\d+')) AS z_c3, c1 FROM tmp ),tmp2 AS (SELECT a.*, MIN(z_c3) OVER(PARTITION BY z_c2 ) AS z_c4 FROM tmp1 a) SELECT z_c1||to_char(z_c4+1) FROM tmp2 WHERE z_c2='0000' AND z_c3=z_c4
CREATE TABLE tmp AS (SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005011' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL UNION ALL SELECT '000021' c1 FROM dual UNION ALL SELECT '000028' c1 FROM dual) WITH tmp1 AS ( SELECT regexp_substr(to_char(c1),'[0]+')AS z_c1, regexp_substr(to_char(c1),'[0]+[^0]')AS z_c2, to_number(regexp_substr(to_char(c1),'[^0]\d+')) AS z_c3, c1 FROM tmp ),tmp2 AS (SELECT a.*, MIN(z_c3) OVER(PARTITION BY z_c2 ) AS z_c4 FROM tmp1 a) SELECT z_c1||to_char(z_c4+1) FROM tmp2 WHERE z_c2='00002' AND z_c3=z_c4
CREATE TABLE tmp AS (SELECT '003001' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005011' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL UNION ALL SELECT '000021' c1 FROM dual UNION ALL SELECT '000028' c1 FROM dual)WITH tmp1 AS ( SELECT a.*,LENGTH('00002') AS in_lenght FROM tmp a ),tmp2 AS ( SELECT c1,SUBSTR(c1,1,in_lenght) AS c1_z1,to_number(SUBSTR(c1,in_lenght+1,LENGTH(c1))) c1_z2, LENGTH(c1)-in_lenght c1_lenght FROM tmp1 ),tmp3 AS (SELECT c1_z1,c1_z2,MIN(c1_z2) OVER(PARTITION BY c1_z1) c1_min,c1_lenght FROM tmp2) --SELECT * FROM tmp3; SELECT c1_z1||LPAD(c1_min+1,c1_lenght,0) FROM tmp3 WHERE c1_z1='00002' AND c1_min=c1_z2;
呵呵。别那么复杂WITH T AS (SELECT '003002' C1 FROM DUAL UNION ALL SELECT '003003' C1 FROM DUAL UNION ALL SELECT '003004' C1 FROM DUAL UNION ALL SELECT '004011' C1 FROM DUAL UNION SELECT '005011' C1 FROM DUAL UNION ALL SELECT '004013' C1 FROM DUAL) select min(c2) from ( select c1,'003'||lpad(rownum,3,'0') c2 from t where substr(c1,1,3)='003')--‘003’作为变量 where c1<>c2不过,2楼也可以完善的。一看各位就是在周围也懂sql的地方。我这里,一次我写分析函数,用了4分钟跑完了。一个博士说,这个程序是拿给别人看的,你这个人家看不懂,你要用最简单的sql写。最后,写plsql,跑完需要4个小时。但是客户表示很满意。
select min(key) mk from (select key a,lead(key,1,null) b over(order by key) from (selet key from tab where key like input% order by key)) tab where tab.b is null结果=mk+1.先找到所有符合输入条件的KEY 然后查询出这些KEY和与其对应的下一条记录,没有的补空。然后查询出条件是空的记录里最小的KEY。也不知道对否,刚来个新公司木有按数据库还无法尝试
个人觉得用 union all这种不可取啊,万一表里数据很多你这么一条一条的 union疯了要。union一般用作表和表之间数据的联合吧。也不符合实际用途。当然纯粹是搞定这个题这个题又不是项目中遇到的哪还差不多
试试 with a as (select '003001' as c from dual union all select '003003' as c from dual union all select '003004' as c from dual union all select '004011' as c from dual union all select '004013' as c from dual) select t2.rk,t2.c from (select t.*, rownum rn from (select rank() over(partition by c order by rownum) rk, a.* from a where a.c like '003%') t) t2 where t2.rn = t2.rk;
表结构如下 KEY1 VARHCAR2(6) KEY2 VARCHAR2(6)数据上KEY2的值全部是补0满6位的数值,但是存在不连续的,例如 003001 003003 003004 004011 004013现在要通过SQL实现用户输入003我就要找到最小的空号“003002”,用户输入00401我就要找到“004012”select c.KEY2 from (select KEY2 from t where instr(KEY2 ,'&UserInput') = 1 order by KEY2 ) c where rownum =1;不知道我写的符合你的要求不! 我有2年没有接触过数据库了
with a as (select '003001' as c from dual union all select '003003' as c from dual union all select '003004' as c from dual union all select '004011' as c from dual union all select '004013' as c from dual) select case when min(a.lv) is null then null else concat('00',min(a.lv)) end as "如果有空,测试空值,丢弃", concat('00',min(a.lv)) as "输出不排除空" from (select level lv from (select min(to_number(c)) as nc, max(to_number(c)) as xc from a where c like '005%') t --var where level >= t.nc connect by level <= t.xc) a where a.lv not in (select to_number(c) as c from a where c is not null);
---lead 取对比 with a as (select '003001' as c from dual union all select '003003' as c from dual union all select '003004' as c from dual union all select '004011' as c from dual union all select '004013' as c from dual) select case when min(c) is null then null else concat('00', min(c) + 1) end as xx from (select c, next_c from (select c, lead(c) over(order by c) next_c from a) where c >= (select min(c) from a where a.c like '005%') and next_c - c > 1);
CREATE OR REPLACE PROCEDURE PRC_TEST(P_INPUT IN VARCHAR2) AS V_INPUT VARCHAR2(3); V_OUTPUT VARCHAR(6); V_MIN INT; V_MAX INT; V_EXE_SQL1 VARCHAR2(32767); V_EXE_SQL2 VARCHAR2(32767);BEGIN V_INPUT := NVL(P_INPUT, '003'); V_EXE_SQL1 := ' SELECT MIN(TAIL) VALUE_MIN, MAX(TAIL) VALUE_MAX FROM (WITH M AS (SELECT ''003001'' KEY2 FROM DUAL UNION ALL SELECT ''003003'' KEY2 FROM DUAL UNION ALL SELECT ''003004'' KEY2 FROM DUAL UNION ALL SELECT ''004011'' KEY2 FROM DUAL UNION ALL SELECT ''004013'' KEY2 FROM DUAL UNION ALL
SELECT ''005993'' KEY2 FROM DUAL UNION ALL SELECT ''005995'' KEY2 FROM DUAL UNION ALL SELECT ''005996'' KEY2 FROM DUAL) SELECT SUBSTR(KEY2, 0, 3) HEAD, TO_NUMBER(SUBSTR(KEY2, 4, 3)) TAIL FROM M WHERE SUBSTR(KEY2, 0, 3) = :V1 ) '; V_EXE_SQL2 := ' SELECT MIN(T.RN) FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 999) T WHERE T.RN >:V1 AND T.RN <:V2 '; EXECUTE IMMEDIATE V_EXE_SQL1 INTO V_MIN, V_MAX USING V_INPUT; EXECUTE IMMEDIATE V_EXE_SQL2 INTO V_OUTPUT USING V_MIN, V_MAX; V_OUTPUT := V_INPUT||trim(TO_CHAR(V_OUTPUT, '099')); DBMS_OUTPUT.PUT_LINE(V_INPUT); DBMS_OUTPUT.PUT_LINE(V_OUTPUT);END; /
这道题不错 select to_char(nvl(max(c1) + 1, rpad(:inputstr, 6, '0') + 1), 'fm000000') from t start with c1 = to_char(rpad(:inputstr, 6, '0') + 1, 'fm000000') connect by to_char(prior c1 + 1) = c1
WITH T AS
(SELECT '003001' C1
FROM DUAL
UNION ALL
SELECT '003003' C1
FROM DUAL
UNION ALL
SELECT '003004' C1
FROM DUAL
UNION ALL
SELECT '004011' C1
FROM DUAL
UNION
SELECT '005011' C1
FROM DUAL
UNION ALL
SELECT '004013' C1
FROM DUAL)
SELECT LPAD(MIN(T1.C1) + 1, 6, 0)
FROM (SELECT C1,
LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP
FROM T
WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('00401', 1, 3)) T1
WHERE T1.GAP > 1
OR T1.GAP IS NULL;
WITH T AS
(SELECT '003001' C1
FROM DUAL
UNION ALL
SELECT '003003' C1
FROM DUAL
UNION ALL
SELECT '003004' C1
FROM DUAL
UNION ALL
SELECT '004011' C1
FROM DUAL
UNION
SELECT '005011' C1
FROM DUAL
UNION ALL
SELECT '004013' C1
FROM DUAL)
SELECT LPAD(MIN(T1.C1) + 1, 6, 0)
FROM (SELECT C1,
LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP
FROM T
WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('00401', 1, 3)) T1
WHERE T1.GAP > 1
OR T1.GAP IS NULL;谢谢回答,但是这个算法只保证了增加方向的取得,要求往前也能取。
例如: 020002 020003的情况下,输入02可以返回020001
WITH T AS
(SELECT '003001' C1
FROM DUAL
UNION ALL
SELECT '003003' C1
FROM DUAL
UNION ALL
SELECT '003007' C1
FROM DUAL
UNION ALL
SELECT '004011' C1
FROM DUAL
UNION
SELECT '005001' C1
FROM DUAL
UNION ALL
SELECT '004013' C1
FROM DUAL
UNION
SELECT '020002' C1
FROM DUAL
UNION ALL
SELECT '020003' C1
FROM DUAL)
SELECT LPAD(DECODE(T2.GAP,
NULL,
(SELECT DECODE(SUBSTR(MIN(T3.C1), -3),
'001',
MIN(T3.C1) + 1,
MIN(T3.C1) - 1)
FROM T T3
WHERE SUBSTR(T3.C1, 1, 3) = SUBSTR('020', 1, 3)),
T2.C1 + 1),
6,
0)
FROM (SELECT T1.C1, T1.GAP
FROM (SELECT C1,
LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP
FROM T
WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('020', 1, 3)) T1
WHERE T1.GAP > 1
OR T1.GAP IS NULL
ORDER BY T1.C1) T2
WHERE ROWNUM = 1;
SELECT '003001' C1 FROM DUAL
UNION ALL
SELECT '003003' C1 FROM DUAL
UNION ALL
SELECT '003004' C1 FROM DUAL
UNION ALL
SELECT '004011' C1 FROM DUAL
UNION ALL
SELECT '004012' C1 FROM DUAL
UNION ALL
SELECT '004013' C1 FROM DUAL
UNION ALL
SELECT '004111' C1 FROM DUAL
UNION ALL
SELECT '004019' C1 FROM DUAL
UNION ALL
SELECT '005011' C1 FROM DUAL)SELECT
CASE
WHEN T_CNT = 0 THEN &tp||LPAD('1', 6 - LENGTH(&tp), '0')
WHEN F_FLG = 0 THEN &tp||LPAD('1', 6 - LENGTH(&tp), '0')
ELSE R_MIN
END AS R_MIN
FROM (
SELECT COUNT(*) AS T_CNT
, NVL(SUM(DECODE(RE, NULL, 0, 1)), 0) AS R_CNT
, MIN(RE) AS R_MIN
, SUM(DECODE(SUBSTR(C1, LENGTH(&tp) + 1, 6 - LENGTH(&tp)), 1, 1, 0)) AS F_FLG
FROM(
SELECT TA.*,
CASE
WHEN AC IS NOT NULL THEN NULL
ELSE LPAD(C1 + 1, 6, '0')
END AS RE
FROM (
SELECT ROW_NUMBER()OVER(ORDER BY T1.C1) AS RID, T1.C1, T2.C1 AS AC
FROM DATASET T1
LEFT JOIN DATASET T2 ON T1.C1 = T2.C1 - 1
WHERE SUBSTR(T1.C1, 0, LENGTH(&tp)) = &tp
) TA
) TB
) TC
AS
(SELECT '003001' C1 FROM DUAL
UNION ALL
SELECT '003003' C1 FROM DUAL
UNION ALL
SELECT '003004' C1 FROM DUAL
UNION ALL
SELECT '004011' C1 FROM DUAL
UNION
SELECT '005011' C1 FROM DUAL
UNION ALL
SELECT '004013' C1 FROM DUAL
UNION ALL
SELECT '000021' c1 FROM dual
UNION ALL
SELECT '000028' c1 FROM dual)
WITH tmp1 AS (
SELECT regexp_substr(to_char(c1),'[0]+')AS z_c1,
regexp_substr(to_char(c1),'[0]+[^0]')AS z_c2,
to_number(regexp_substr(to_char(c1),'[^0]\d+')) AS z_c3,
c1 FROM tmp
),tmp2 AS (SELECT a.*, MIN(z_c3) OVER(PARTITION BY z_c2 ) AS z_c4 FROM tmp1 a)
SELECT z_c1||to_char(z_c4+1) FROM tmp2 WHERE
z_c2='0000' AND z_c3=z_c4
CREATE TABLE tmp
AS
(SELECT '003001' C1 FROM DUAL
UNION ALL
SELECT '003003' C1 FROM DUAL
UNION ALL
SELECT '003004' C1 FROM DUAL
UNION ALL
SELECT '004011' C1 FROM DUAL
UNION
SELECT '005011' C1 FROM DUAL
UNION ALL
SELECT '004013' C1 FROM DUAL
UNION ALL
SELECT '000021' c1 FROM dual
UNION ALL
SELECT '000028' c1 FROM dual)
WITH tmp1 AS (
SELECT regexp_substr(to_char(c1),'[0]+')AS z_c1,
regexp_substr(to_char(c1),'[0]+[^0]')AS z_c2,
to_number(regexp_substr(to_char(c1),'[^0]\d+')) AS z_c3,
c1 FROM tmp
),tmp2 AS (SELECT a.*, MIN(z_c3) OVER(PARTITION BY z_c2 ) AS z_c4 FROM tmp1 a)
SELECT z_c1||to_char(z_c4+1) FROM tmp2 WHERE
z_c2='00002' AND z_c3=z_c4
AS
(SELECT '003001' C1 FROM DUAL
UNION ALL
SELECT '003003' C1 FROM DUAL
UNION ALL
SELECT '003004' C1 FROM DUAL
UNION ALL
SELECT '004011' C1 FROM DUAL
UNION
SELECT '005011' C1 FROM DUAL
UNION ALL
SELECT '004013' C1 FROM DUAL
UNION ALL
SELECT '000021' c1 FROM dual
UNION ALL
SELECT '000028' c1 FROM dual)WITH tmp1 AS (
SELECT a.*,LENGTH('00002') AS in_lenght FROM tmp a
),tmp2 AS (
SELECT c1,SUBSTR(c1,1,in_lenght) AS c1_z1,to_number(SUBSTR(c1,in_lenght+1,LENGTH(c1))) c1_z2, LENGTH(c1)-in_lenght c1_lenght FROM tmp1
),tmp3 AS (SELECT c1_z1,c1_z2,MIN(c1_z2) OVER(PARTITION BY c1_z1) c1_min,c1_lenght FROM tmp2)
--SELECT * FROM tmp3;
SELECT c1_z1||LPAD(c1_min+1,c1_lenght,0) FROM tmp3
WHERE c1_z1='00002' AND c1_min=c1_z2;
(SELECT '003002' C1
FROM DUAL
UNION ALL
SELECT '003003' C1
FROM DUAL
UNION ALL
SELECT '003004' C1
FROM DUAL
UNION ALL
SELECT '004011' C1
FROM DUAL
UNION
SELECT '005011' C1
FROM DUAL
UNION ALL
SELECT '004013' C1
FROM DUAL)
select min(c2) from (
select c1,'003'||lpad(rownum,3,'0') c2 from t
where substr(c1,1,3)='003')--‘003’作为变量
where c1<>c2不过,2楼也可以完善的。一看各位就是在周围也懂sql的地方。我这里,一次我写分析函数,用了4分钟跑完了。一个博士说,这个程序是拿给别人看的,你这个人家看不懂,你要用最简单的sql写。最后,写plsql,跑完需要4个小时。但是客户表示很满意。
select min(key) mk from
(select key a,lead(key,1,null) b over(order by key) from
(selet key from tab where key like input% order by key)) tab where tab.b is null结果=mk+1.先找到所有符合输入条件的KEY 然后查询出这些KEY和与其对应的下一条记录,没有的补空。然后查询出条件是空的记录里最小的KEY。也不知道对否,刚来个新公司木有按数据库还无法尝试
with a as
(select '003001' as c
from dual
union all
select '003003' as c
from dual
union all
select '003004' as c
from dual
union all
select '004011' as c
from dual
union all
select '004013' as c
from dual)
select t2.rk,t2.c
from (select t.*, rownum rn
from (select rank() over(partition by c order by rownum) rk, a.*
from a
where a.c like '003%') t) t2
where t2.rn = t2.rk;
KEY1 VARHCAR2(6)
KEY2 VARCHAR2(6)数据上KEY2的值全部是补0满6位的数值,但是存在不连续的,例如
003001
003003
003004
004011
004013现在要通过SQL实现用户输入003我就要找到最小的空号“003002”,用户输入00401我就要找到“004012”select
c.KEY2
from
(select KEY2 from t
where instr(KEY2 ,'&UserInput') = 1
order by KEY2 ) c
where rownum =1;不知道我写的符合你的要求不!
我有2年没有接触过数据库了
(select '003001' as c
from dual
union all
select '003003' as c
from dual
union all
select '003004' as c
from dual
union all
select '004011' as c
from dual
union all
select '004013' as c
from dual)
select case when min(a.lv) is null then null else concat('00',min(a.lv)) end as "如果有空,测试空值,丢弃",
concat('00',min(a.lv)) as "输出不排除空"
from (select level lv
from (select min(to_number(c)) as nc, max(to_number(c)) as xc
from a
where c like '005%') t --var
where level >= t.nc
connect by level <= t.xc) a
where a.lv not in (select to_number(c) as c from a where c is not null);
with a as
(select '003001' as c
from dual
union all
select '003003' as c
from dual
union all
select '003004' as c
from dual
union all
select '004011' as c
from dual
union all
select '004013' as c
from dual)
select case
when min(c) is null then
null
else
concat('00', min(c) + 1)
end as xx
from (select c, next_c
from (select c, lead(c) over(order by c) next_c from a)
where c >= (select min(c) from a where a.c like '005%')
and next_c - c > 1);
V_OUTPUT VARCHAR(6);
V_MIN INT;
V_MAX INT;
V_EXE_SQL1 VARCHAR2(32767);
V_EXE_SQL2 VARCHAR2(32767);BEGIN V_INPUT := NVL(P_INPUT, '003'); V_EXE_SQL1 := ' SELECT MIN(TAIL) VALUE_MIN, MAX(TAIL) VALUE_MAX
FROM (WITH M AS (SELECT ''003001'' KEY2
FROM DUAL
UNION ALL
SELECT ''003003'' KEY2
FROM DUAL
UNION ALL
SELECT ''003004'' KEY2
FROM DUAL
UNION ALL
SELECT ''004011'' KEY2
FROM DUAL
UNION ALL
SELECT ''004013'' KEY2
FROM DUAL
UNION ALL
SELECT ''005993'' KEY2
FROM DUAL
UNION ALL
SELECT ''005995'' KEY2
FROM DUAL
UNION ALL
SELECT ''005996'' KEY2
FROM DUAL)
SELECT SUBSTR(KEY2, 0, 3) HEAD,
TO_NUMBER(SUBSTR(KEY2, 4, 3)) TAIL
FROM M
WHERE SUBSTR(KEY2, 0, 3) = :V1 ) '; V_EXE_SQL2 := '
SELECT MIN(T.RN)
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 999) T
WHERE T.RN >:V1 AND T.RN <:V2 '; EXECUTE IMMEDIATE V_EXE_SQL1
INTO V_MIN, V_MAX
USING V_INPUT; EXECUTE IMMEDIATE V_EXE_SQL2
INTO V_OUTPUT
USING V_MIN, V_MAX; V_OUTPUT := V_INPUT||trim(TO_CHAR(V_OUTPUT, '099'));
DBMS_OUTPUT.PUT_LINE(V_INPUT);
DBMS_OUTPUT.PUT_LINE(V_OUTPUT);END;
/
select to_char(nvl(max(c1) + 1, rpad(:inputstr, 6, '0') + 1), 'fm000000')
from t
start with c1 = to_char(rpad(:inputstr, 6, '0') + 1, 'fm000000')
connect by to_char(prior c1 + 1) = c1
客户不是应该更关注性能吗? 难道你们的业务是帮别人写SQL?
客户不是应该更关注性能吗? 难道你们的业务是帮别人写SQL?
那次是出去进行算法交流