PL/SQL 基础---具体问题分析

PL/SQL 基础—具体问题分析

主要讨论PL/SQL中一些典型问题和解决思路；

1.排序问题

假设有以下数据：

	LINE_NUM	CODE	DESCRIPTION	NEW_ORDER
1	1	B	234
2	2	A	3244
3	3	B	255534
4	4	C	26634
5	5	D	23774
6	6	D	56
7	7	C	7
8	8	A	8
9	9	B	9
10	10	B	87
11	11	A	87

问题
1.先按CODE（A,B,C…顺序），再按DESCRIPTION排序，将结果写入到NEW_ORDER列；
2.按CODE字段字母出现次序排序，即B先出现则将所有的B排完，再排A…

问题一
这个问题关键是游标对应的SQL查询结果，需要按CODE和DESCRIPTION两个字段排序，然后遍历更新即可；

    PROCEDURE SORT_1 AS
    CURSOR sort_cursor IS
      SELECT ho.line_num, ho.code, ho.description, ho.new_order
        FROM TEST_ORDER ho
       ORDER BY ho.code, ho.description
         FOR UPDATE NOWAIT;
    v_new_order NUMBER(6) := 1;
   BEGIN
    FOR order_record IN sort_cursor LOOP
      UPDATE TEST_ORDER
         SET NEW_ORDER = v_new_order
       WHERE CURRENT OF sort_cursor;
      v_new_order := v_new_order + 1;
     END LOOP;
   END;

问题二
这个问题有两个思路可以解决：其一可以两层循环遍历，外层循环确定CODE出现顺序，内层循环对CODE相同的记录进行排序（通过NULL判断是否已经排序）；另外一种思路是先确定CODE字段不同字母出现顺序（有序结果），再按其遍历排序；

-----方法一 -----  
  PROCEDURE SORT_2_3 AS 
    v_new_order NUMBER := 1;
  BEGIN
    FOR s1 IN (SELECT ho.code FROM TEST_ORDER ho) LOOP
      FOR s2 IN (SELECT ho.code, ROWID FROM TEST_ORDER ho WHERE ho.code = s1.code AND ho.new_order IS NULL) LOOP
        UPDATE TEST_ORDER SET NEW_ORDER  = v_new_order WHERE ROWID = s2.ROWID;
        v_new_order := v_new_order + 1;
      END LOOP;
    END LOOP;
  END;

-----方法二 ----- 
  PROCEDURE SORT_2 AS
    v_new_order NUMBER(6) := 1;
  BEGIN
    FOR CODE_RCD IN (SELECT ho.code, MIN(ROWNUM)
                       FROM TEST_ORDER ho
                      GROUP BY ho.code
                      ORDER BY MIN(ROWNUM)) LOOP
      FOR order_record IN (SELECT ho.line_num,
                                  ho.code,
                                  ho.description,
                                  ho.new_order,
                                  ROWID
                             FROM TEST_ORDER ho
                            WHERE ho.CODE = CODE_RCD.code
                              FOR UPDATE NOWAIT) LOOP
        UPDATE TEST_ORDER
           SET NEW_ORDER = v_new_order
         WHERE ROWID = order_record.rowid;
        v_new_order := v_new_order + 1;
      END LOOP;
    END LOOP;
  END;

2.插入时间段问题

问题描述

在一张表中（包含不重叠的时间段）插入新的时间段，要求判断新插入的时间段也不会出现重叠；

	START_DATE	END_DATE
1	2016/11/10	2016/11/20
2	2016/10/10	2016/10/20

解题思路

将时间按开始时间排序；
遍历排序好的时间段，找到第一个开始时间大于或等于插入时间段的结束时间，确定插入的时间段在此之前，判断前一时间段的结束时间是否比插入时间的开始时间小即可；
以上判断需要对边界值做特殊处理；

另一种思路

分析得知出现时间段重叠的情况分类；
按分类情况查询结果，如果都为空，则该时间段可以插入，否则不能插入；

/* 插入时间段 */
  PROCEDURE INSERT_DATE_SEC(start_date IN DATE, end_date IN DATE) IS
    v_start    DATE;
    v_end      DATE;
    before_end DATE;
    OUTDATE_EXCEPTION   EXCEPTION;
    COVERDATE_EXCEPTION EXCEPTION;
    CURSOR sec_cursor IS
      SELECT p.START_DATE, p.END_DATE
        FROM TEST_PERIOD p
       ORDER BY p.START_DATE;
  BEGIN
    -- 检查是否超过总的限制
    IF start_date < to_date('2016-10-01', 'YYYY-MM-DD') OR
       end_date > to_date('2016-12-01', 'YYYY-MM-DD') OR start_date IS NULL OR
       end_date IS NULL THEN
      RAISE OUTDATE_EXCEPTION;
    END IF;

    -- 检查是否有重叠
    IF NOT sec_cursor%ISOPEN THEN
      OPEN sec_cursor;
    END IF;

    LOOP
      --保存前一条结束时间
      before_end := v_end;
      FETCH sec_cursor
        INTO v_start, v_end;
      EXIT WHEN sec_cursor%NOTFOUND;
      DBMS_OUTPUT.put_line(v_start);
      IF v_start >= end_date THEN
        IF before_end IS NULL OR before_end <= start_date THEN
          --插入数据 ，退出循环
          INSERT INTO TEST_PERIOD p VALUES (start_date, end_date);
          EXIT;
        ELSE
          RAISE COVERDATE_EXCEPTION;
        END IF;
      END IF;
    END LOOP;

    -- 判断右边界插入
    IF sec_cursor%NOTFOUND THEN
      IF before_end IS NULL OR before_end <= start_date THEN
        INSERT INTO TEST_PERIOD p VALUES (start_date, end_date);
      ELSE
        RAISE COVERDATE_EXCEPTION;
      END IF;
    END IF;

    CLOSE sec_cursor;

  EXCEPTION
    WHEN OUTDATE_EXCEPTION THEN
      DBMS_OUTPUT.put_line('超出总时间限制...');
    WHEN COVERDATE_EXCEPTION THEN
      DBMS_OUTPUT.put_line('时间有重叠...');
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('出错...');
  END INSERT_DATE_SEC;

3.利用函数递归调用完成统计

除了Oracle中的递归查询还能通过函数的递归调用实现对”树结构表“（包含父子关系）的层级遍历，但是，函数的递归调用比递归查询更加灵活，可以添加更多的条件和计算；

问题描述

物料表（TEST_BOM）

	ASSEMBLY_ITEM	COMPONENT_ITEM	QUANTITY
1	A	B	2
2	A	C	3
3	A	D	1
4	C	E	12
5	C	F	1
6	C	G	4
7	E	H	2
8	E	I	2
9	E	J	200
10	E	K	22
11	K	L	1
12	K	M	1

组装表（TEST_TREE）

	ASSEMBLY_ITEM	COMPONENT_ITEM	BOM_LEVEL	QUANTITY
	物料	子物料	层次	数量

通过物料表计算组装任意一个物料所需要的子物料的数目和其所在的层次，保存到TEST_TREE表中；

解题思路

递归函数，传入上一层物料名，数量和层次，通过物料名查询子物料的数目，如果为0，计算数量并填入到TEST_TREE中，如果不为0，递归调用本函数（层次加1，计算新数量）；

 PROCEDURE RE_FIND(top_item IN VARCHAR2, a IN TEST_BOM.ASSEMBLY_ITEM%TYPE, assem_qty IN NUMBER,lv IN NUMBER ) 
    IS
    v_com_count NUMBER(10);
  BEGIN
    FOR bom IN (SELECT * FROM TEST_BOM hb WHERE hb.assembly_item = a) LOOP
      -- 子节点数量
      SELECT COUNT(*) INTO v_com_count FROM TEST_BOM hb WHERE hb.assembly_item = bom.component_item;
      IF v_com_count = 0 THEN 
        -- 子节点数量为0，叶子节点
        INSERT INTO TEST_TREE ht VALUES(top_item, bom.component_item,lv+1,bom.quantity * assem_qty);
      ELSE
        RE_FIND(top_item,bom.component_item, bom.quantity * assem_qty, lv+1);
      END IF;
    END LOOP;
  END;


  PROCEDURE CAL_COMPONENT_3(a TEST_BOM.ASSEMBLY_ITEM%TYPE) IS
  BEGIN
    RE_FIND(a, a,1, 1);
  END;

4.任务安排类：工作安排

问题描述

现有一批工作需要完成，完成每一项工作有固定的时间，同时每天有固定的班次，每个班次时长在TEST_SHIFT中，现需要将工作安排到班次中，并将详情保存在TEST_SHIFT_DETAIL表中；

TEST_JOB表

	JOB_CODE	NEED_HOURS	START_DATE	END_DATE
1	J1	6
2	J2	1
3	J3	10
4	J4	4

TEST_SHIFT表

	SHIFT_DAY	SHIFT_CODE	START_DATE	END_DATE	ACTUAL_END_DATE
1	2016/12/2	A	2016/12/2 08:00:00	2016/12/2 12:00:00
2	2016/12/2	B	2016/12/2 13:00:00	2016/12/2 15:00:00
3	2016/12/3	A	2016/12/3 08:00:00	2016/12/3 12:00:00
4	2016/12/3	B	2016/12/3 13:00:00	2016/12/3 15:00:00
5	2016/12/4	A	2016/12/4 08:00:00	2016/12/4 12:00:00
6	2016/12/4	B	2016/12/4 13:00:00	2016/12/4 15:00:00
7	2016/12/5	A	2016/12/5 08:00:00	2016/12/5 12:00:00
8	2016/12/5	B	2016/12/5 13:00:00	2016/12/5 17:00:00

TEST_SHIFT_DETAIL表

	JOB_CODE	SHIT_DAY	SHIFT_CODE	START_DATE	END_DATE	ROWID

解题思路

遍历每一项工作，在每一项工作中包含一个WHILE循环（剩余工作量为0退出）；
在安排工作的过程中需要两个变量分别保存剩余工作量（小时）,以及当前班次剩余时间；
当当前班次剩余时间小于剩余工作量，以班次内剩余时间为基准，作为一个保存点，完成剩余工作量的更新，否则，剩余工作量为0，剩余时间一工作量为基准向后推移，并且开始下一项工作已安排；

  FUNCTION add_hours(s IN DATE, n IN NUMBER) RETURN DATE IS
  BEGIN
    RETURN(s + n / 24);
  END add_hours;

  FUNCTION between_hours(s IN DATE, e IN DATE) RETURN NUMBER IS
  BEGIN
    IF s > e THEN
      RAISE date_minus_exception;
    END IF;
    RETURN(e - s) * 24;
  END between_hours;

  PROCEDURE do_job_plan IS

    CURSOR cur_shift IS
      SELECT hs.shift_code,
             hs.shift_day,
             hs.start_date,
             hs.end_date,
             hs.actual_end_date
        FROM hand_shift hs
       ORDER BY hs.shift_day;

    v_shift      hand_shift%ROWTYPE;
    v_save_point DATE;
    v_rest_hours NUMBER(6) := 0;
    v_rest_jobs  NUMBER(6) := 0;
  BEGIN
    OPEN cur_shift;
    -- 遍历所有JOB
    FOR job IN (SELECT hj.job_code,
                       hj.need_hours,
                       hj.start_date,
                       hj.end_date
                  FROM hand_job hj
                   FOR UPDATE NOWAIT) LOOP

      v_rest_jobs := job.need_hours;
      -- 没有时间
      WHILE v_rest_jobs != 0 LOOP
        -- 获取新的时间段
        IF v_rest_hours = 0 THEN
          FETCH cur_shift
            INTO v_shift.shift_code,
                 v_shift.shift_day,
                 v_shift.start_date,
                 v_shift.end_date,
                 v_shift.actual_end_date;

          IF cur_shift%NOTFOUND THEN
            RAISE not_enough_time;
          END IF;

          v_shift.actual_end_date := v_shift.start_date;
          v_rest_hours            := between_hours(v_shift.start_date,
                                                   v_shift.end_date);
        END IF;

        -- 安排工作 
        IF v_rest_hours >= v_rest_jobs THEN
          /*JOB安排完成*/

          --首次安排完成，需要写入开始时间
          IF job.start_date IS NULL THEN
            job.start_date := v_shift.actual_end_date;
          END IF;

          v_save_point := add_hours(v_shift.actual_end_date, v_rest_jobs);
          dbms_output.put_line('工作完成的savepoint- - - - -:' ||
                               to_char(v_save_point,
                                       'YYYY_MM_DD HH24:MI;SS'));
          INSERT INTO hand_shift_detail
          VALUES
            (job.job_code,
             v_shift.shift_day,
             v_shift.shift_code,
             v_shift.actual_end_date,
             v_save_point);
          UPDATE hand_job hj
             SET hj.start_date = job.start_date, hj.end_date = v_save_point
           WHERE hj.job_code = job.job_code;
          UPDATE hand_shift hs
             SET hs.actual_end_date = v_save_point
           WHERE hs.shift_day = v_shift.shift_day
             AND hs.shift_code = v_shift.shift_code;

          --更新时间段
          v_shift.actual_end_date := v_save_point;
          v_rest_hours            := v_rest_hours - v_rest_jobs;

          --更新工作安排进度
          job.end_date := v_save_point;
          v_rest_jobs  := 0;

        ELSE
          /*JOB安排未完成*/

          --首次安排完成，需要写入开始时间
          IF job.start_date IS NULL THEN
            job.start_date := v_shift.actual_end_date;
          END IF;

          v_save_point := add_hours(v_shift.actual_end_date, v_rest_hours);
          dbms_output.put_line('工作未完成的savepoint- - :' ||
                               to_char(v_save_point,
                                       'YYYY_MM_DD HH24:MI;SS'));

          INSERT INTO hand_shift_detail
          VALUES
            (job.job_code,
             v_shift.shift_day,
             v_shift.shift_code,
             v_shift.actual_end_date,
             v_save_point);
          UPDATE hand_shift hs
             SET hs.actual_end_date = v_save_point
           WHERE hs.shift_day = v_shift.shift_day
             AND hs.shift_code = v_shift.shift_code;

          --更新工作安排进度
          job.end_date := v_save_point;
          v_rest_jobs  := v_rest_jobs - v_rest_hours;

          --更新时间段
          v_shift.actual_end_date := v_save_point;
          v_rest_hours            := 0;

        END IF;
      END LOOP;
    END LOOP;
    CLOSE cur_shift;

  EXCEPTION
    WHEN date_minus_exception THEN
      dbms_output.put_line('日期相减出错');
    WHEN not_enough_time THEN
      dbms_output.put_line('排班不够');
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE);
      dbms_output.put_line(SQLERRM);
      dbms_output.put_line('出错');

  END;
END;

5.任务安排类：发货任务

问题描述

假设原数据库中又一批发货计划，但为了是成本最低且不在周末发货，需要做以下调整，要求每天发货以整车（480）为单位,最后一天除外，原计划中如果有周末的需要调整到本周周五（原来可能没计划）,制定新的计划，PLAN_TYPE为‘NEW’;

TEST_SHIP_PLAN表

	SHIP_DATE	SHIP_QTY	PLAN_TYPE
1	2016/12/6	200	OLD
2	2016/12/7	300	OLD
3	2016/12/18	1200	OLD
4	2016/12/9	20	OLD
5	2016/12/21	10	OLD
6	2016/12/12	30	OLD
7	2016/12/14	200	OLD
8	2016/12/8	500	OLD
9	2016/12/19	400	OLD
10	2016/12/20	300	OLD
11	2016/12/22	2000	OLD
12	2016/12/23	2	OLD
13	2016/12/13	21	OLD
14	2017/1/5	200	OLD
15	2017/1/7	100	OLD

解题思路

由于需要将周末挪到本周周五，主要是周五可能原本没有计划，所以需要提前处理（否则判断逻辑非常复杂），在进行每天凑整遍历即可；

SQL查询完成周末计划前移和每天计划汇总，PLSQL完成每天计划凑整；

-- 判断是否周末函数
CREATE OR REPLACE FUNCTION ISWEEKEND (d IN DATE) RETURN NUMBER IS
BEGIN
  IF to_char(d,'D') IN ('1','7') THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;

-- 获取上一个周五的日期
CREATE OR REPLACE FUNCTION BEFORE_FRIDAY (d IN DATE) RETURN DATE IS
BEGIN
    RETURN NEXT_DAY(d,'星期五');
END;



--主要逻辑
CREATE OR REPLACE PROCEDURE TEST_SHIP_PLAN_PRO IS
       CURSOR l_cursor IS
       SELECT hspp.new_date,SUM(hspp.ship_qty) new_qty
        FROM
        (
        SELECT DECODE(isweekend(hsp.ship_date),1,before_friday(hsp.ship_date),hsp.ship_date ) AS new_date,
               hsp.ship_qty
          FROM hand_ship_plan hsp) hspp
        GROUP BY hspp.new_date ORDER BY hspp.new_date;

        l_last_date DATE;
        l_last_qty  NUMBER := 0;
BEGIN
        FOR l_plan IN l_cursor LOOP
          IF l_last_date IS NULL THEN
            l_last_date := l_plan.new_date;
            l_last_qty  := l_plan.new_qty;
          ELSE
            l_last_qty  := l_last_qty + l_plan.new_qty;
            dbms_output.put_line(l_last_qty);
            IF l_last_qty >= 480 THEN
              INSERT INTO TEST_SHIP_PLAN VALUES(l_last_date,ceil((l_last_qty-l_plan.new_qty)/480)*480,'NEW');
              l_last_qty  := l_last_qty-ceil((l_last_qty-l_plan.new_qty)/480)*480;
              l_last_date := l_plan.new_date;
            END IF;
          END IF;

        END LOOP;

        --最后一次
        IF l_last_qty != 0 THEN
        INSERT INTO TEST_SHIP_PLAN VALUES(l_last_date,l_last_qty,'NEW');
        END IF;
END;