求oracle 语句 或 过程

Question

表 A 
qshm  zzhm  zt 1001  1010  zc 
1003  1004  zf 
1007  1007  zf 3条记录
正常使用号码为1001 到 1010 
其中作废号码为1003 到 1004 
              1007 到 1007 需要正常 1001  1010  减去 1003  1004  和 1007  1007结果应该是 
            1001 - 1002 
            1005 - 1006 
            1008 - 1010 
oracle 语句 或 过程

Answer 1

http://blog.csdn.net/wh62592855/archive/2010/03/15/5383249.aspx

Answer 2

WITH a AS(
SELECT 1001 qshm,1010 zzhm,'zc' zt FROM dual UNION all 
SELECT 1003,1004,'zf' FROM dual UNION ALL 
SELECT 1007,1007,'zf' FROM dual    
),
b AS(SELECT qshm num FROM a UNION SELECT zzhm num FROM a ),
c AS(SELECT num,lead(num)over(ORDER BY 1) num2 FROM b)
SELECT decode(num-1,1000,num,num+1) num1,Decode(num2-1,1009,num2,num2-1) num2 
FROM c  WHERE num2-1!=num AND num!=num-1

Answer 3

SQL> select * from tb1;
 
 QSHM  ZZHM ZT
----- ----- ----------
 1001  1010 zc
 1003  1004 zf
 1007  1007 zf
 
SQL> 
SQL> with t1 as (select rownum-1 rn from dual connect by rownum<=(select max(zzhm-qshm) from tb1)),
  2  t2 as (select qshm+rn qshm ,decode(zt,'zc',0,1) zt from tb1,t1 where tb1.qshm+t1.rn<=tb1.zzhm),
  3  t3 as (select qshm,sum(zt) zt from t2 group by qshm having sum(zt)=0),
  4  t4 as (select qshm,qshm+row_number()over(order by qshm desc) rn from t3)
  5  select min(qshm),max(qshm) from t4 group by rn;
 
 MIN(QSHM)  MAX(QSHM)
---------- ----------
      1001       1002
      1005       1006
      1008       1010
 
SQL> 

Answer 4

create table A (qshm number(4),zzhm number(4),zt char(2))
/insert into A values(1001,1010,'zc');
insert into A values(1003,1004,'zf');
insert into A values(1007,1007,'zf');
commit;with t1 as(
select qshm hm,zt,0 st from A
union
select zzhm,zt,1 from A
),
t2 as(
select hm hm1, zt zt1,
lead(hm) over (order by rownum) hm2,
lead(zt) over (order by rownum) zt2,
lead(st) over (order by rownum) st2
from t1
),
t3 as(
select 
decode(zt1,'zc',hm1,hm1+1) hm1,
decode(zt2,'zc',hm2,decode(st2,0,hm2-1,null)) hm2
from t2
)
select hm1,hm2 from t3 where hm2 is not null;
/*
1001 1002
1005 1006
1008 1010
*/drop table A purge
/

Answer 5

采用范围比较的方法比较麻烦
WITH TMP AS
 (SELECT X.*,
         LEAD(HM) OVER(ORDER BY HM) LD_HM,
         LAG(HM) OVER(ORDER BY HM) LG_HM
    FROM (SELECT HM,
                 FLAG,
                 LAG(FLAG) OVER(ORDER BY HM) LG,
                 LEAD(FLAG) OVER(ORDER BY HM) LD
            FROM (SELECT QSHM - 1 HM, '1' FLAG
                    FROM A
                   WHERE ZT = 'zf'
                  UNION ALL
                  SELECT ZZHM + 1, '2' FROM A WHERE ZT = 'zf')) X
   WHERE NVL(DECODE(FLAG, 1, LG, 2, LD), '0') <> FLAG)SELECT DISTINCT CASE T2.FLAG WHEN '1' THEN TO_CHAR (GREATEST(T1.QSHM,
                                                              NVL(T2.LG_HM,
                                                                  -9999)), '9999') || '-' || TO_CHAR (T2.HM, '9999') ELSE TO_CHAR (T2.HM, '9999') || '-' || TO_CHAR (LEAST(T1.ZZHM,
                                                                                                                                                                            NVL(T2.LD_HM,
                                                                                                                                                                                9999)), '9999') END T
  FROM A T1, TMP T2
 WHERE T2.HM >= T1.QSHM AND T2.HM <= T1.ZZHM
 ORDER BY T这个要分析多种可能性，比如如果zt为'zf'的其中两条记录的号码范围出现交集，则4楼的代码就会报错
如果固定号码为整数，且号码量不会太大，可以采取创建虚拟号码，根据条件来过滤掉不符合要求的部分，类似3楼的做法
WITH TMP AS
 (SELECT ROWNUM + QSHM - 1 HM
    FROM (SELECT MIN(QSHM) QSHM, MAX(ZZHM) ZZHM FROM A WHERE ZT = 'zc')
  CONNECT BY ROWNUM <= ZZHM - QSHM + 1)
SELECT MIN (HM) || '-' || MAX (HM)
  FROM (SELECT HM
          FROM TMP T
         WHERE EXISTS (SELECT 1
                  FROM A
                 WHERE T.HM BETWEEN QSHM AND ZZHM
                   AND ZT = 'zc')
           AND NOT EXISTS (SELECT 1
                  FROM A
                 WHERE T.HM BETWEEN QSHM AND ZZHM
                   AND ZT = 'zf')
         ORDER BY HM)
 GROUP BY HM - ROWNUM
 ORDER BY MIN (HM)

Answer 6

--trycreate or replace procedure pro_result
is
counter number;
type v_hm_table is table of a.zzhm%type
index by binary_integer;
v_hm v_hm_table;
type v_hm_table_zf is table of a.zzhm%type;
v_hm_zf v_hm_table_zf;
begin
v_hm_zf:=v_hm_table_zf(1003,1004,1007);
for i in 1001..1010 loop
 v_hm(i-1000):=i;
 end loop;  
 
if v_hm is not null then
counter:=v_hm.first;
while counter <=10 loop   for i in 1..3 loop
  case when v_hm(counter) = v_hm_zf(i) then
  v_hm.delete (counter);
  counter:= v_hm.next(counter);
  else
    dbms_output.put_line(v_hm(counter));
    counter:=v_hm.next(counter);  
 end case;
  end loop; 
  end loop;
  end if;
end;

Answer 7

--try
create or replace procedure pro_result
is
counter number;
type v_hm_table is table of a.zzhm%type
index by binary_integer;
v_hm v_hm_table;
type v_hm_table_zf is table of a.zzhm%type;
v_hm_zf v_hm_table_zf;
begin
v_hm_zf:=v_hm_table_zf(1003,1004,1007);
for i in 1001..1010 loop
 v_hm(i-1000):=i;
 end loop;  
 
if v_hm is not null then
counter:=v_hm.first;
while counter <=10 loop   for i in 1..3 loop
  case when v_hm(counter) = v_hm_zf(i) then
  v_hm.delete (counter);
  counter:= v_hm.next(counter);
  else
    dbms_output.put_line(v_hm(counter));
    counter:=v_hm.next(counter);  
 end case;
  end loop; 
  end loop;
  end if;
end;

Answer 8

SQL> --创建一存储过程，check号码是否是要求的号码
SQL> CREATE OR REPLACE PROCEDURE check_hm
  2  (i_zt varchar2,--号码状态
  3   i_hm number,  --check的号码
  4   out_flg out number)--是否为合法号码的标志位：0，号码不合法，不输出；1，号码合法，输出。
  5  IS
  6    TYPE t IS TABLE OF a%ROWTYPE;
  7    tmp_hm t;
  8  BEGIN
  9    SELECT * BULK COLLECT INTO tmp_hm FROM a
 10    WHERE zt != i_zt;
 11    out_flg := 1;
 12    FOR j IN 1 .. tmp_hm.COUNT LOOP
 13      IF i_hm >= tmp_hm(j).qshm AND i_hm <= tmp_hm(j).zzhm THEN
 14        out_flg := 0;
 15      END IF;
 16    END LOOP;
 17  END;
 18  /Procedure createdSQL> CREATE OR REPLACE PROCEDURE get_hm
  2  (i_zt varchar2) --查询号码的状态
  3  IS
  4    TYPE t IS TABLE OF a%ROWTYPE;
  5    ret_hm t;
  6    out_flg number := 0;
  7  BEGIN
  8    SELECT * BULK COLLECT INTO ret_hm FROM a
  9    WHERE zt = i_zt;
 10    FOR i IN 1 .. ret_hm.COUNT LOOP
 11      FOR o IN ret_hm(i).qshm .. ret_hm(i).zzhm LOOP
 12        out_flg := 1;
 13        check_hm(i_zt,o,out_flg);--调用存储过程check_hm
 14      IF out_flg = 1  THEN
 15        DBMS_OUTPUT.PUT_LINE(o);
 16      END IF;
 17      END LOOP;
 18    END LOOP;
 19  END;
 20  /Procedure createdSQL> --调用存储过程get_hm
SQL> exec get_hm('zc');1001
1002
1005
1006
1008
1009
1010PL/SQL procedure successfully completedSQL> 

Answer 9

create or replace procedure pro_result
is
counter number;
type v_hm_table is table of a.zzhm%type
index by binary_integer;
v_hm v_hm_table;
type v_hm_table_zf is table of a.zzhm%type;
v_hm_zf v_hm_table_zf;
begin
v_hm_zf:=v_hm_table_zf(1003,1004,1007);
for i in 1001..1010 loop
 v_hm(i-1000):=i;
 end loop;  
 
if v_hm is not null then
counter:=v_hm.first;
while counter <=10 loop   for i in 1..3 loop
  if v_hm(counter)=v_hm_zf(i) then
  v_hm.delete (counter);
   counter:=v_hm.next(counter); 
  else
  null;
 end if; 
  end loop;
  dbms_output.put_line(v_hm(counter));
  counter:=v_hm.next(counter); 
  end loop;
  else
  dbms_output.put_line('v_hm is null!');
  end if;
end;