Oracle在将工作SQL移植到过程时出现问题

Question

我有一些SQL，它工作得很好。在SQL中是硬编码的日期，我想将其删除并从过程中传递给它们。

当我尝试创建插入过程时，我得到以下错误，我不明白为什么会发生这种情况。SELECT语句中的代码是相同的，只是在SELECT语句的正上方有一个INSERT语句。

Errors: PROCEDURE CREATE_TIMEOFF_REQUESTS
Line/Col: 7/1 PL/SQL: SQL Statement ignored
Line/Col: 10/19 PL/SQL: ORA-01744: inappropriate INTO

下面是我的测试用例。我在实时SQL测试，所以我们都可以有相同的Oracle版本。任何帮助修复我的代码将非常感激。

CREATE OR REPLACE TYPE obj_date IS OBJECT (
date_val DATE
);
CREATE OR REPLACE TYPE nt_date IS TABLE OF obj_date;
create or replace function generate_dates_pipelined(
p_from  in date,
p_to    in date
)
return nt_date 
pipelined
is
begin
for c1 in (
with calendar (start_date, end_date ) as (
select trunc(p_from), trunc(p_to) from dual
union all
select start_date + 1, end_date
from   calendar
where  start_date + 1 <= end_date
)
select start_date as day
from   calendar
) loop
pipe row  (obj_date(c1.day));
end loop;

return;
end       generate_dates_pipelined;
create table holidays(
holiday_date DATE not null,
holiday_name VARCHAR2(20),
constraint holidays_pk primary key (holiday_date),
constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
);
INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
WITH dts as (
select to_date('01-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'August  1st 2021' from dual union all
select to_date('05-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'August  5th 2021' from dual
)
SELECT * from dts;
Create table employees(
employee_id NUMBER(6), 
first_name VARCHAR2(20),
last_name VARCHAR2(20),
card_num VARCHAR2(10),
work_days VARCHAR2(7)
);
ALTER TABLE employees
ADD (
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
INSERT INTO employees (
EMPLOYEE_ID,
first_name, 
last_name,
card_num,
work_days
)
WITH names AS ( 
SELECT 1, 'Jane',     'Doe',      'F123456', 'NYYYYYN'   FROM dual UNION ALL 
SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN' FROM dual UNION ALL 
SELECT 3, 'Justin',   'Case',     'C765341','NYYYYYN' FROM dual UNION ALL 
SELECT 4, 'Mike',     'Jones',      'D564311','NYYYYYN' FROM dual 
)
SELECT * FROM names;  

create table timeoff(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
timeoff_date DATE,
timeoff_type VARCHAR2(1) DEFAULT 'V',
constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),
constraint timeoff_pk primary key (employee_id, timeoff_date)
);

SQL工作原理:

INSERT INTO timeoff (employee_id, timeoff_date)
SELECT e.employee_id,
c.date_val
FROM   employees e
INNER JOIN table(generate_dates_pipelined(date '2021-08-01', DATE '2021-08-10')) c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y')
WHERE  NOT EXISTS (
SELECT 1
FROM   holidays h
WHERE  c.date_val = h.holiday_date
)
ORDER BY
e.employee_id,
c.date_val
;
SELECT * from timeoff;
SEQ_NUM    EMPLOYEE_ID    TIMEOFF_DATE    TIMEOFF_TYPE
1    1    03-AUG-21    V
2    1    04-AUG-21    V
3    1    06-AUG-21    V
4    1    07-AUG-21    V
5    1    10-AUG-21    V
6    2    03-AUG-21    V
7    2    04-AUG-21    V
8    2    06-AUG-21    V
...
...
TRUNCATE table timeoff;

SELECT创建和RUN的过程

CREATE OR REPLACE PROCEDURE create_timeoff_requests (start_date DATE, end_date DATE)
IS
type t_date is table of date;
l_res t_date;
BEGIN
SELECT 
c.date_val
BULK COLLECT INTO l_res
FROM   employees e
INNER JOIN  TABLE (generate_dates_pipelined (start_date, end_date))c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y')
WHERE  NOT EXISTS (
SELECT 1
FROM   holidays h
WHERE    c.date_val = h.holiday_date
)
ORDER BY
e.employee_id,
c.date_val;

-- debug
for i in 1..l_res.count  loop
dbms_output.put_line(l_res(i));
end loop;
END;

EXEC create_timeoff_requests (DATE '2021-08-01', DATE '2021-08-10');
03-AUG-21
04-AUG-21
06-AUG-21
07-AUG-21
10-AUG-21
03-AUG-21
04-AUG-21
06-AUG-21
07-AUG-21
10-AUG-21
...
...

添加INSERT语句创建失败。

CREATE OR REPLACE PROCEDURE create_timeoff_requests (start_date DATE, end_date DATE)
IS
type t_date is table of date;
l_res t_date;
BEGIN
INSERT INTO timeoff (employee_id, timeoff_date)  
SELECT 
c.date_val
BULK COLLECT INTO l_res
FROM   employees e
INNER JOIN  TABLE (generate_dates_pipelined (start_date, end_date))c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y')
WHERE  NOT EXISTS (
SELECT 1
FROM   holidays h
WHERE  c.date_val = h.holiday_date
)
ORDER BY
e.employee_id,
c.date_val;

-- debug
for i in 1..l_res.count  loop
dbms_output.put_line(l_res(i));
end loop;
END;

Answer 1

您不需要一个对象来包装DATE，只需使用:

CREATE TYPE nt_date IS TABLE OF DATE;

您可以将日历函数重写为:

CREATE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to   IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start + INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;

插入time off的函数几乎肯定要将员工作为参数(否则您将为ALL插入leave)。员工)，你可能想要使用MERGE，这样员工就不会在同一天插入多个请求，如果他们提交重叠的请求。

CREATE PROCEDURE create_timeoff_requests (
p_employee_id IN TIMEOFF.EMPLOYEE_ID%TYPE,
p_start_date  IN TIMEOFF.TIMEOFF_DATE%TYPE,
p_end_date    IN TIMEOFF.TIMEOFF_DATE%TYPE
)
IS
BEGIN
MERGE INTO timeoff dst
USING (
SELECT e.employee_id,
c.COLUMN_VALUE AS timeoff_date
FROM   employees e
INNER JOIN TABLE(
generate_dates_pipelined(p_start_date, p_end_date)
) c
ON (SUBSTR(e.work_days, TRUNC(c.COLUMN_VALUE) - TRUNC(c.COLUMN_VALUE, 'IW') + 1, 1) = 'Y')
WHERE  NOT EXISTS (
SELECT 1
FROM   holidays h
WHERE  c.COLUMN_VALUE = h.holiday_date
)
AND    e.employee_id = p_employee_id
) src
ON (    src.employee_id  = dst.employee_id
AND src.timeoff_date = dst.timeoff_date )
WHEN NOT MATCHED THEN
INSERT (employee_id, timeoff_date)
VALUES (src.employee_id, src.timeoff_date);
END;
/

Answer 2

可以直接将查询结果插入到表中;或者你可以将它们查询成一个集合;但是你不能在同一条语句中同时做这两件事。

我需要集合，因为我正在调用一个返回1个或多个日期的流水线函数，我不知道如何捕获结果。

管道函数不需要集合;你已经从你的SQL工作中调用它了;独立的版本。你可以直接把它移到你的过程中:

CREATE OR REPLACE PROCEDURE create_timeoff_requests (start_date DATE, end_date DATE)
IS
type t_date is table of date;
l_res t_date;
BEGIN
INSERT INTO timeoff (employee_id, timeoff_date)
SELECT e.employee_id,
c.date_val
FROM   employees e
INNER JOIN table(generate_dates_pipelined(start_date, end_date)) c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y')
WHERE  NOT EXISTS (
SELECT 1
FROM   holidays h
WHERE  c.date_val = h.holiday_date
);
END create_timeoff_requests;
/

唯一的区别是改变:

INNER JOIN table(generate_dates_pipelined(date '2021-08-01', DATE '2021-08-10')) c

INNER JOIN table(generate_dates_pipelined(start_date, end_date)) c

所以它使用过程参数(尽管您可以考虑将它们重命名为p_start_date和p_end_date，以匹配generate_dates_pipelined函数使用的前缀模式，并减少参数/列名冲突的可能性)。当插入。

时，order by是冗余的。