sql - Oracle 问题将工作 SQL 移植到过程
问题描述
我有一些 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
下面是我的测试用例。我在 Live 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 创建和运行的过程
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;
解决方案
您不需要对象来包装 aDATE
并且可以使用:
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;
您插入休假的函数几乎肯定要将员工作为参数(否则您将为所有员工插入休假),并且您可能希望使用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;
/
推荐阅读
- powershell - PowerShell - 格式卷内的变量
- jmeter - 无法在 JMeter 4.0 中启动从站
- webpack - 组合多个 webpack 块
- python-3.x - Keras model.fit_generator 引发 Stopiteration 错误
- python - Pytest,你如何测试一个函数而不运行文件的其余部分
- tensorflow - 在视频流中检测到对象后,我想裁剪并保存这些对象
- lambda - 如何使用 lambda Dsl for Pact 为“arrayMinLike”生成相同的匹配规则?
- botframework - 如何在 Microsoft Bot Framwork 中使用 C# 将 Chatbot 集成到 Skype for business On Premises
- javascript - 如何从 Javascript 函数执行 Angular 函数?
- python - 在peewee(sqlite3)中按binned列分区