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;