function - pl/sql 在函数中返回区间计算

Question

我有一些表格可以跟踪公交线路、站点以及它们的时间表。我正在尝试创建一个函数，该函数返回同一线路上两个巴士站之间的旅行时间，但我无法让该函数按预期工作。

功能代码：

create or replace function total_time_func(sch_id in sched.shid%type, stop_start in stops.sname%type, stop_end in stops.sname%type)
     return interval day to second is
     t_time interval day to second;
     start_time stop_sched.scheduled_arrival%type;
     end_time stop_sched.scheduled_arrival%type;

begin

     select scheduled_arrival 
     into start_time
     from stop_sched, stops
     where stop_sched.sid = stops.sid
     and stop_sched.shid = sch_id
     and stops.sname = stop_start;

     select scheduled_arrival
     into end_time
     from stop_sched, stops
     where stop_sched.sid = stops.sid
     and stop_sched.shid = sch_id
     and stops.sname = stop_end;

     t_time := end_time - start_time;

     return t_time;

end; 

函数编译不返回错误；但是，一旦我运行程序来调用函数的返回值，我就会收到一个错误。

从函数调用返回值的程序：

declare
     total_time interval day to second;

begin 
     total_time := total_time_func(1, '5th', '7th');

     if total_time > 0 
         then dbms_output.put_line('The total time is: ' || total_time);
    else dbms_output.put_line('Stops Not Found');
    end if;
end;

这是我收到的错误：

Error report -
ORA-01403: no data found
ORA-06512: at line 16
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

我不确定我做错了什么，我已经多次重写并从头开始，但没有成功。同样值得注意的是，我知道我可能不使用函数就可以完成此任务；但是，我是使用函数的新手，我希望练习编写它们而不是放弃。

这些是我的参考表：

create table lines 
(lid int, 
lname varchar(30),  
num_station int, 
status int,
primary key(lid));

create table stops
(sid int, 
sname varchar(30), 
address varchar(100), 
status int, 
primary key(sid));

create table stop_line
(lid int, 
sid int,  
seq int, 
primary key(lid,sid),
foreign key(lid) references lines,
foreign key(sid) references stops);

create table sched
(shid int,  
lid int,  
direction int,   
primary key (shid), 
foreign key (lid) references lines);

create table stop_sched 
(shid int, 
sid int, 
scheduled_arrival interval day to second, 
primary key (shid, sid),
foreign key(shid) references sched, 
foreign key(sid) references stops);

作为旁注，我最初还在函数体中包含以下语句；但是，我根本无法在不返回错误的情况下编译该函数：

select shid 
into sch_id
from sched
where sch_id = shid;

select sname
into stop_start
from stops
where stop_start = sname;

select sname
into stop_end
from stops
where stop_end = sname;

添加这些语句时返回的错误：

LINE/COL  ERROR
--------- -------------------------------------------------------------
9/5       PL/SQL: SQL Statement ignored
10/10     PLS-00403: expression 'SCH_ID' cannot be used as an INTO-target of a SELECT/FETCH 
statement
11/5      PL/SQL: ORA-00904: : invalid identifier
14/5      PL/SQL: SQL Statement ignored
15/10     PLS-00403: expression 'STOP_START' cannot be used as an INTO-target of a 
SELECT/FETCH statement
16/5      PL/SQL: ORA-00904: : invalid identifier
19/5      PL/SQL: SQL Statement ignored
20/10     PLS-00403: expression 'STOP_END' cannot be used as an INTO-target of a 
SELECT/FETCH statement
21/5      PL/SQL: ORA-00904: : invalid identifier
Errors: check compiler log

非常感谢您提供的任何指导，因为我在使用函数时遇到了很多麻烦。谢谢！

编辑以包括示例表值。stop_sched 表的第一行和最后一行应该满足参数（sch_id = 1，stop_start = '5th'，stop_end = '7th'），但函数返回没有找到数据。

insert into stops values(1,'5th', '42 5th Avenue, Buffalo, NY 14201,1);
insert into stops values(2,'10th', ‘889 10th Avenue, Buffalo, NY 14201',1);
insert into stops values(3,'Main', '10 Main Street, Buffalo, NY 14201',1);
insert into stops values(4,'7th', '900 7th Avenue, Buffalo, NY 14201',0);

insert into schedule values(1,1, 1);
insert into schedule values(2,1, 1);

insert into stop_sched values(1, 1, interval '7:30:00.00' hour to second);
insert into stop_sched values(1, 2, interval '7:40:00.00' hour to second);
insert into stop_sched values(1, 3, interval '7:50:00.00' hour to second);
insert into stop_sched values(1, 4, interval '8:10:00.00' hour to second);

标签： functionplsql

Answer 1

Error report -
ORA-01403: no data found
ORA-06512: at line 16
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

此错误清楚地表明您在函数内的查询没有return任何行，并且您的匿名阻止了执行。

代码按预期工作。请看下面的演示：

表准备：

CREATE  TABLE stops (
    sid       INT,
    sname     VARCHAR(30),
    address   VARCHAR(100),
    status    INT
);

insert into stops values (1,'A','DFDFD',1);
insert into stops values (2,'B','FDKJH',3);
insert into stops values (3,'C','IOIOS',4);
insert into stops values (4,'D','LKJJA',5);


CREATE   TABLE stop_sched (
    sid                 INT,
    scheduled_arrival   INTERVAL DAY TO SECOND
);

insert into stop_sched values (1,'3 12:30:06.7');
insert into stop_sched values (2,'4 12:30:06.7');

功能：

CREATE OR REPLACE FUNCTION total_time_func
(
    -- sch_id       IN  number, --sched.shid%TYPE,
     stop_start   IN  varchar2,--stops.sname%TYPE,
     stop_end      IN  varchar2
) 
RETURN INTERVAL DAY TO SECOND IS
    t_time       INTERVAL DAY TO SECOND;
    start_time   stop_sched.scheduled_arrival%TYPE;
    end_time     stop_sched.scheduled_arrival%TYPE;
BEGIN

     select scheduled_arrival 
     into start_time
     from stop_sched
     Inner join  stops
     ON stop_sched.sid = stops.sid
    -- and stop_sched.shid = sch_id
     and stops.sname = stop_start;

     select scheduled_arrival
     into end_time
     from stop_sched
     inner join stops
     ON stop_sched.sid = stops.sid
     --and stop_sched.shid = sch_id
     and stops.sname = stop_end;

    t_time   := end_time - start_time;    

    RETURN t_time;
END;

执行：

select total_time_func('A','B') OUTPUT from dual;

输出：

SQL> /
     OUTPUT 
     -------
     +01 00:00:00.000000



 SQL> 
    DECLARE
        total_time   INTERVAL DAY TO SECOND;
    BEGIN
        total_time   := total_time_func('A', 'B');
        IF
            total_time > 0
        THEN
            dbms_output.put_line('The total time is: ' || total_time);
        ELSE
            dbms_output.put_line('Stops Not Found');
        END IF;

    END;

输出：

Stops Not Found