存储过程查询一张表中记录是否连续、重复并且取出对应的数据

时间:2022-02-12 09:22:16

http://blog.csdn.net/tengdazhang770960436/article/details/8185995



创建主表:MESSAGE1

create table MESSAGE1
(
  ID           NUMBER not null,
  RECEIVE_TIME DATE,
  PK_ID        NUMBER,
  CONTENT      VARCHAR2(50)
)

插入测试数据:

insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (1, to_date('15-11-2012', 'dd-mm-yyyy'), 1, 'm1');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (2, to_date('16-11-2012', 'dd-mm-yyyy'), 3, 'm3');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (3, to_date('17-11-2012', 'dd-mm-yyyy'), 6, 'm6');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (4, to_date('18-11-2012', 'dd-mm-yyyy'), 1, 'm11');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (5, to_date('19-11-2012', 'dd-mm-yyyy'), 5, 'm15');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (6, to_date('20-11-2012', 'dd-mm-yyyy'), 5, 'm15');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (7, to_date('21-11-2012', 'dd-mm-yyyy'), 6, 'm16');
insert into MESSAGE1 (ID, RECEIVE_TIME, PK_ID, CONTENT)
values (8, to_date('23-11-2012', 'dd-mm-yyyy'), 14, 'm114');
commit;

创建存放不连续数据表:TEST_SERIES1

create table TEST_SERIES1
(
  BEGINTIME DATE,
  SERIES    NUMBER,
  ENDTIME   DATE
)

创建存放重复数据表:TEST_REPEAT1

create table TEST_REPEAT1
(
  BEGINTIME DATE,
  REPEAT    NUMBER,
  ENDTIME   DATE
)

 

创建计算是否连续的存储过程:

create or replace procedure query_series_number_p_date(beginNum in number,
                                              endNum   in number) as
  t_temp message1%rowtype;
  type c_message is ref cursor; --定义变量
  cur_message c_message; --声明变量
  myIndex        number;
  cha            number;
  cha1           number;
--定义array存放将要查询出来的number类型的数据
  TYPE emp_ssn_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  best_employees emp_ssn_array;
--定义array存放将要查询出来的dsate类型的数据
  TYPE emp_ssn_array_p IS TABLE OF date INDEX BY BINARY_INTEGER;
  best_employees_p emp_ssn_array_p;
begin

  myIndex := 0;
  open cur_message for 'select * from message1';
  loop
  --把查询出来的数据放入array中
    fetch cur_message into t_temp;
    best_employees(myIndex) := t_temp.pk_id;
    best_employees_p(myIndex) := t_temp.receive_time;
    myIndex := myIndex + 1;
    exit when cur_message%notfound;
  end loop;
  close cur_message;

  FOR i IN 0 .. best_employees.count - 1 LOOP
    if i < best_employees.count - 1 then
      if (best_employees(i) + 1) != best_employees(i + 1) then
        DBMS_OUTPUT.PUT_LINE('不等于');
        if (best_employees(i + 1) - best_employees(i)) < 0 then
          --判断最后的数字是不是endNum
          cha := endNum - best_employees(i);
          
          for j in 1 .. cha loop
            insert into test_series1(begintime,series,endtime) values (best_employees_p(i),best_employees(i) + 1 * j,best_employees_p(i+1));
          end loop;
          
          --判断刚开始的数字是不是beginNum
          cha1 := best_employees(i + 1) - beginNum;
          
          for k in 0 .. cha1 - 1 loop
          insert into test_series1(begintime,series,endtime) values (best_employees_p(i),beginNum + 1 * k,best_employees_p(i));
          end loop;
          
        else
          cha := best_employees(i + 1) - best_employees(i);

          for k in 1 .. cha - 1 loop
            insert into test_series1(begintime,series,endtime) values (best_employees_p(i),best_employees(i) + 1 * k,best_employees_p(i+1));
          end loop;

        end if;
      end if;
    end if;

  END LOOP;

end;

 

创建计算重复的存储过程:

create or replace procedure query_repeat_number_p_date as
  t_temp message1%rowtype;
  type c_message is ref cursor; --定义变量
  cur_message c_message; --声明变量

  TYPE emp_ssn_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  best_employees emp_ssn_array;

  TYPE emp_ssn_array_p IS TABLE OF DATE INDEX BY BINARY_INTEGER;
  best_employees_p emp_ssn_array_p;

  myIndex        number;
begin

  myIndex := 0;
  open cur_message for 'select * from message1';
  --自动打开游标,执行动态sql
  --dbms_output.put_line('工资在'||min_sal||'和'||max_sal||'之间的人有:');
  loop
    fetch cur_message
      into t_temp;
    best_employees(myIndex) := t_temp.pk_id;
    best_employees_p(myIndex) := t_temp.receive_time;
    myIndex := myIndex + 1;
    exit when cur_message%notfound;
  end loop;
  close cur_message;

  FOR i IN 0 .. best_employees.count - 1 LOOP
    if i < best_employees.count - 2 then
      if (best_employees(i)) = best_employees(i + 1) then
       --把重复的数据插入表中
            insert into test_repeat1(begintime,repeat,endtime) values (best_employees_p(i),best_employees(i),best_employees_p(i+1));
            end if;
    end if;

  END LOOP;

end;