sql - Oracle 错误创建需要 iNTO 的过程
问题描述
我有一些运行良好的 SQL 代码。当我尝试将其包装在一个过程中时,它无法创建并且我收到以下错误。有人可以解释一下问题是什么以及如何解决吗?
下面是我的测试用例。提前感谢所有回答的人。
Errors: PROCEDURE CREATE_ACCESS_HISTORY
Line/Col: 4/1 PLS-00428: an INTO clause is expected in this SELECT statement
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
CREATE OR REPLACE 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;
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 locations AS
SELECT level AS location_id,
'Door ' || level AS location_name,
CASE round(dbms_random.value(1,3))
WHEN 1 THEN 'A'
WHEN 2 THEN 'T'
WHEN 3 THEN 'T'
END AS location_type
FROM dual
CONNECT BY level <= 50;
ALTER TABLE locations ADD (CONSTRAINT locations_pk PRIMARY KEY (location_id));
create table access_history(seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
card_num varchar2(10),
location_id number(4),
access_date date,
processed NUMBER(1) default 0
);
create or replace procedure create_access_history(p_start_date date, p_end_date date)
IS
BEGIN
WITH cntr AS(SELECT LEVEL - 1 AS n
FROM dual
CONNECT BY LEVEL <= 25 -- Max number of rows per employee per date
)
,got_location_num AS(SELECT location_id,
ROW_NUMBER() OVER (ORDER BY location_id) AS location_num,
COUNT(*) OVER () AS max_location_num
FROM locations)
,employee_days AS(SELECT e.employee_id,
e.card_num,
d.column_value AS access_date,
dbms_random.value (0, 25) AS rn -- 0 to max number of rows per employee per date
FROM employees e
CROSS JOIN TABLE (generate_dates_pipelined (p_start_date, p_end_date)) d)
,employee_n_days AS (SELECT ed.employee_id,
ed.card_num,
ed.access_date,
dbms_random.value (0, 1) AS lrn
FROM employee_days ed
JOIN cntr c ON c.n <= ed.rn
)
SELECT n.employee_id,
n.card_num,
l.location_id,
n.access_date + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS ACCESS_DATE
FROM employee_n_days n
JOIN got_location_num l ON l.location_num = CEIL (n.lrn * l.max_location_num);
END;
EXEC create_access_history (DATE '2021-08-01', DATE '2021-08-10');
解决方案
您的过程 create_access_history 应该看起来像 -
create or replace procedure create_access_history(p_start_date date,
p_end_date date,
result out sys_refcursor)
IS
BEGIN
OPEN result FOR
WITH cntr AS(SELECT LEVEL - 1 AS n
FROM dual
CONNECT BY LEVEL <= 25 -- Max number of rows per employee per date
)
,got_location_num AS(SELECT location_id,
ROW_NUMBER() OVER (ORDER BY location_id) AS location_num,
COUNT(*) OVER () AS max_location_num
FROM locations)
,employee_days AS(SELECT e.employee_id,
e.card_num,
d.column_value AS access_date,
dbms_random.value (0, 25) AS rn -- 0 to max number of rows per employee per date
FROM employees e
CROSS JOIN TABLE (generate_dates_pipelined (p_start_date, p_end_date)) d)
,employee_n_days AS (SELECT ed.employee_id,
ed.card_num,
ed.access_date,
dbms_random.value (0, 1) AS lrn
FROM employee_days ed
JOIN cntr c ON c.n <= ed.rn
)
SELECT n.employee_id,
n.card_num,
l.location_id,
n.access_date + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS ACCESS_DATE
FROM employee_n_days n
JOIN got_location_num l ON l.location_num = CEIL (n.lrn * l.max_location_num);
END;
然后你必须调用你的程序,比如 -
DECLARE resultset SYS_REFCURSOR;
BEGIN
EXEC create_access_history (DATE '2021-08-01',
DATE '2021-08-10',
resultset);
FOR I IN 1..resultset.count LOOP
DBMS_OUTPUT.PUT_LINE(I.employee_id || ' ' || I.card_num || ' ' || I.location_id || ' ' || I.ACCESS_DATE);
END LOOP;
END;
推荐阅读
- python - 如何在 Odoo 13 中修改日历事件对象弹出框的值
- python - Pycharm 不更新类输入变量的缓存
- angular - Angular 在 NGINX 反向代理 404 后面的 Docker 中运行
- windows - VS 控制台中的应用程序验证程序报告和错误:删除具有无效锁定计数的关键部分
- python - Python 3.9 x64 执行错误的 pip install (x32)
- android-studio - 是否建议为每个项目单击使用多个 viewpager 适配器?
- java - 当我尝试在 android Studio 中运行我的模拟器时,出现以下错误?如何解决
- python - 如何让程序根据答案列表要求输入一个或两个答案
- javascript - 如何将导入javascript附加到窗口
- react-native - Twilio React Native 视频通话冻结问题