点击(此处)折叠或打开

--开发时要注意一些变量是否是系统自带的函数,否则容易出错(10下运行有问题, 11g完全没有问题)
--系统当前时间为2014-01-01
DROP TABLE t1;
CREATE TABLE t1
(
arrangement_id NUMBER(9) NOT NULL,
duty_id NUMBER(9) NOT NULL,
order_id NUMBER(9) NOT NULL,
order_name VARCHAR2(50) NOT NULL,
duty_date DATE NOT NULL
);
DROP TABLE t2;
CREATE TABLE t2
(
arrangement_id NUMBER(9) NOT NULL,
staff_id NUMBER(9) NOT null
);
INSERT INTO t1 (arrangement_id, duty_id, order_id, order_name, duty_date)
VALUES (1, 1, 1, \'白班\', to_date(\'30-12-2013\', \'dd-mm-yyyy\'));
INSERT INTO t1 (arrangement_id, duty_id, order_id, order_name, duty_date)
VALUES (2, 1, 1, \'白班\', to_date(\'31-12-2013\', \'dd-mm-yyyy\'));
INSERT INTO t1 (arrangement_id, duty_id, order_id, order_name, duty_date)
VALUES (3, 1, 1, \'白班\', to_date(\'01-01-2014\', \'dd-mm-yyyy\'));
INSERT INTO t1 (arrangement_id, duty_id, order_id, order_name, duty_date)
VALUES (4, 1, 2, \'夜班\', to_date(\'02-01-2014\', \'dd-mm-yyyy\'));
INSERT INTO t2 (arrangement_id, staff_id) values (1, 2);
INSERT INTO t2 (arrangement_id, staff_id) values (2, 2);
INSERT INTO t2 (arrangement_id, staff_id) values (3, 2);
INSERT INTO t2 (arrangement_id, staff_id) values (4, 2);
COMMIT;
SET serveroutput ON;
DECLARE
v_arrangement_id NUMBER(9) :=2;
v_duty_id NUMBER(9) :=1;
current_date t1.duty_date%TYPE;
BEGIN
SELECT duty_date INTO current_date FROM t1 WHERE arrangement_id = v_arrangement_id;
FOR r in (SELECT t1.arrangement_id FROM t1 WHERE duty_date >=TRUNC(current_date) AND duty_id = v_duty_id
AND EXISTS (SELECT 1 FROM t2 WHERE t2.arrangement_id = t1.arrangement_id)) LOOP
dbms_output.put_line(r.arrangement_id);
END LOOP;
END;
/
2
3
4
PL/SQL procedure successfully completed.

ITPub博客

小心保留字

最新文章