时间戳

首页 > 解决方案 > Oracle 过程变量尚不可表示

oracle - Oracle 过程变量尚不可表示

问题描述

我有一个接受 start_date 的程序。如果 end_date 未传递到过程中，我正在尝试修改过程以根据 start_date 设置默认 end_date。我收到一个错误（见下文）。

错误：PROCEDURE XXX 行/列：0/0 PL/SQL：编译单元分析终止行/列：4/35 PLS-00227：子程序“在”正式的 I_START_DATE 尚未表示

有没有办法解决这个问题？下面是我的测试用例，它也会产生错误。我正在测试实时 SQL，因此我们的环境可以相同。

提前感谢您的专业知识和所有回答的人。


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

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, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
          SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
        SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
         SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' 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 <= 5;


     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 xxx (
  i_start_date in date,
 
 i_end_date IN DATE DEFAULT TRUNC(i_start_date) + 1, 

 i_interval         IN PLS_INTEGER DEFAULT 10
) as
BEGIN

INSERT INTO access_history (employee_id, card_num, location_id, access_date)
WITH date_rows ( start_date, end_date ) AS (
 SELECT i_start_date,
                i_end_date
 FROM   DUAL
UNION ALL
 SELECT start_date + 
NUMTODSINTERVAL(i_interval, 'MINUTE'),
        end_date
 FROM   date_rows
 WHERE  start_date +
NUMTODSINTERVAL(i_interval, 'MINUTE') < end_date
)
SELECT     e.employee_id
,        e.card_num
,       l.location_id
,       d.start_date
FROM       employees e
CROSS JOIN locations l
CROSS JOIN date_rows d;
END;
/


EXEC xxx(timestamp '2021-08-21 22:37:12');

标签： oraclecommon-table-expressionproceduredefault-value

解决方案

您不能根据过程规范中的另一个参数为参数分配默认值。

做你的定义如下：

create or replace procedure xxx (
  i_start_date in date,
  i_end_date IN DATE default null, 
  i_interval         IN PLS_INTEGER DEFAULT 10
) as
  l_end_date date := nvl(i_end_date, TRUNC(i_start_date) + 1);
begin
  ...
end;

推荐阅读