在PL/SQL中的FOR LOOP中使用聚合函数执行GROUP BY子句

Question

我有一个包含一些列的EMPLOYEE表。我对ENTRY_TIME (NUMBER)、EXIT_TIME (NUMBER)、NAME (VARCHAR2)这三个专栏很感兴趣。NAME列没有不同的条目，即一个值可能出现多次。

我已经取了几个不同的NAME值，并且我想要从整个数据中获得这些选定的NAME值中的每一个的最大EXIT_TIME和最小ENTRY_TIME。

我已经编写了以下PL/SQL块：

DECLARE
type namearray IS VARRAY(6) OF VARCHAR2(50);
name namearray;
total INTEGER;
EntryTime NUMBER;
ExitTime NUMBER;
employeeNames VARCHAR2(100);
BEGIN
name := namearray('Peter','Job','George','Hans','Marco','Alison');
total := name.count;
FOR i in 1 .. total LOOP
SELECT min(ENTRY_TIME), max(EXIT_TIME), NAME 
INTO   EntryTime,       ExitTime,       employeeNames  
from EMPLOYEE 
GROUP BY NAME having NAME = name(i);
dbms_output.put_line('EntryTime: ' || EntryTime || 'ExitTime: ' || ExitTime || 'Name: ' || employeeNames);
END LOOP;
END;
/

它提供以下错误：

Error report -
ORA-01403: no data found.
ORA-06512: in line 12
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.

但数据是存在的。我认为查询或块本身有问题。

有人能帮忙/建议吗。

Answer 1

乍一看，我也对@APC评论的HAVING CLAUSE感到困惑，但我认为问题出在您传递的员工姓名上，因为employee表中可能有不可用的姓名，这会导致NO_DATA_FOUND异常，您需要使用exception和其他情况来处理此类情况，即使它有效，我建议将having clause更改为where clause

试试下面，

DECLARE
type namearray IS VARRAY(6) OF VARCHAR2(50);
name namearray;
total INTEGER;
EntryTime NUMBER;
ExitTime NUMBER;
employeeNames VARCHAR2(100);
BEGIN
name :=namearray('Peter','Job','George','Hans','Marco','Alison');
total := name.count;
FOR i in 1 .. total 
LOOP
BEGIN
SELECT min(1), max(1), NAME 
INTO EntryTime, ExitTime, employeeNames 
FROM EMPLOYEE 
GROUP BY NAME 
HAVING NAME = name(i);
dbms_output.put_line('EntryTime: ' || EntryTime || 'ExitTime: ' || ExitTime || 'Name: ' || employeeNames);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No data found for Name: ' || name(i));
END;
END LOOP;
END;
/

考虑使用WHERE子句的建议。：(

我之所以这么说，是因为你可以在db<gt；小提琴

Answer 2

异常处理的另一个选项是将所选员工名称与表外部联接。这是通过在模式级别创建一个集合(数组(，然后对该集合类型的变量使用TABLE函数来实现的。见小提琴。

create type names_att is table of varchar2(50);
declare
name names_att := names_att('Peter','Job','George','Hans','Marco','Alison');
begin
for rec in 
( select min(entrytime)  entrytime
, max(exittime)   exittime
, na.column_value employeenames
from table (name) na 
left join employee  emp  on emp.employeenames = na.column_value
group by na.column_value 
order by na.column_value 
)
loop
dbms_output.put_line('Name: ' ||rec.employeenames) ||
'  EntryTime: ' || nvl(to_char(rec.entrytime), '---') || 
'  ExitTime: '  || nvl(to_char(rec.exittime), '---')
); 

end loop;
end;
/

注意：我为名称定义了一个关联数组，而不是Varray。我不确定这种技术是否适用于varray。我从来没有见过有什么理由要变装。