我需要创建一个程序来概述给定国家/地区的所有员工。概览不能包括没有任何雇员的城市。
如果我不包含 if 语句，我的代码就可以工作，但它返回一个没有员工在那里工作的城市。
这是未完成的代码：
create or replace procedure get_emp_overview
(p_co_id locations.country_id%TYPE)
AS
v_emp_count NUMBER;
BEGIN
for rec in (select l.location_id,l.city,c.country_name from locations l inner join countries c on c.country_id=l.country_id where l.country_id=p_co_id order by l.location_id desc) LOOP
DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name || ' - ' || rec.location_id || ' ' || rec.city);
for rec2 in (select count(e.employee_id) as empCount, d.department_name from departments d inner join employees e on e.department_id=d.department_id where d.location_id = rec.location_id group by d.department_name) LOOP
IF rec2.empCount != 0 THEN
DBMS_OUTPUT.PUT_LINE(rec2.department_name || ': ' || rec2.empCount || ' werknemers');
END IF;
END LOOP;
END LOOP;
END get_emp_overview;
/
输出：
SQL> exec get_emp_overview('US')
==> United States of America - 1700 Seattle
Administration: 1 werknemers
Accounting: 2 werknemers
Purchasing: 6 werknemers
Executive: 3 werknemers
Finance: 6 werknemers
==> United States of America - 1600 South Brunswick <<<<<<< problem
==> United States of America - 1500 South San Francisco
Shipping: 45 werknemers
==> United States of America - 1400 Southlake
IT: 5 werknemers
所以我尝试了以下代码：
create or replace procedure get_emp_overview
(p_co_id locations.country_id%TYPE)
AS
v_emp_count NUMBER;
BEGIN
for rec in (select l.location_id,l.city,c.country_name from locations l inner join countries c on c.country_id=l.country_id where l.country_id=p_co_id order by l.location_id desc) LOOP
IF (select count(e.employee_id) from departments d inner join employees e on e.department_id=d.department_id where d.location_id=rec.location_id) != 0 THEN
DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name || ' - ' || rec.location_id || ' ' || rec.city);
END IF;
for rec2 in (select count(e.employee_id) as empCount, d.department_name from departments d inner join employees e on e.department_id=d.department_id where d.location_id = rec.location_id group by d.department_name) LOOP
IF rec2.empCount != 0 THEN
DBMS_OUTPUT.PUT_LINE(rec2.department_name || ': ' || rec2.empCount || ' werknemers');
END IF;
END LOOP;
END LOOP;
END get_emp_overview;
/
预期输出：与给定的相同，不带"==> 美利坚合众国 - 1600 南不伦瑞克">
errors:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/24 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set
specification>
<an alternat
8/67 PLS-00103: Encountered the symbol "INNER" when expecting one of
the following:
, ; for group having intersect minus order start union where
connect
您不能在if语句中包含查询 - 语法不允许这样做。您必须查询一个变量，并检查：
...
FOR rec IN (
select l.location_id, l.city, c.country_name
from locations l
inner join countries c on c.country_id = l.country_id
where l.country_id = p_co_id
order by l.location_id desc
)
LOOP
select count(*)
into v_emp_count
from departments d
inner join employees e on e.department_id = d.department_id
where d.location_id = rec.location_id;
IF v_emp_count = 0 THEN
CONTINUE; -- to next iteration of cursor loop
END IF;
DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
|| ' - ' || rec.location_id || ' ' || rec.city);
...
还有其他方法可以解决这个问题。您可以在第一个游标查询中添加exists子句，这样您甚至看不到没有员工的位置：
...
FOR rec IN (
select l.location_id, l.city, c.country_name
from locations l
inner join countries c on c.country_id = l.country_id
where l.country_id = p_co_id
and exists (
select *
from departments d
inner join employees e on e.department_id = d.department_id
where d.location_id = l.location_id
)
order by l.location_id desc
)
LOOP
DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
|| ' - ' || rec.location_id || ' ' || rec.city);
...
您还可以通过排除该点的位置来简化第二个循环，使用having子句：
create or replace procedure get_emp_overview
(p_co_id locations.country_id%TYPE)
AS
BEGIN
FOR rec IN (
select l.location_id, l.city, c.country_name
from locations l
inner join countries c on c.country_id = l.country_id
where l.country_id = p_co_id
and exists (
select *
from departments d
inner join employees e on e.department_id = d.department_id
where d.location_id = l.location_id
)
order by l.location_id desc
)
LOOP
DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
|| ' - ' || rec.location_id || ' ' || rec.city);
FOR rec2 IN (
select count(e.employee_id) as empCount, d.department_name
from departments d
inner join employees e on e.department_id = d.department_id
where d.location_id = rec.location_id
group by d.department_name
having count(e.employee_id) > 0
)
LOOP
DBMS_OUTPUT.PUT_LINE(rec2.department_name || ': ' || rec2.empCount || ' werknemers');
END LOOP;
END LOOP;
END get_emp_overview;
/
获得输出：
exec get_emp_overview('US');
==> United States of America - 1700 Seattle
Administration: 1 werknemers
Accounting: 2 werknemers
Purchasing: 6 werknemers
Executive: 3 werknemers
Finance: 6 werknemers
==> United States of America - 1500 South San Francisco
Shipping: 45 werknemers
==> United States of America - 1400 Southlake
IT: 5 werknemers
PL/SQL procedure successfully completed.
你的任务可能是使用嵌套循环;但你也可以使用单个循环来跟踪你以前是否见过这个位置：
create or replace procedure get_emp_overview
(p_co_id locations.country_id%TYPE)
AS
l_last_location_id locations.location_id%TYPE;
BEGIN
FOR rec IN (
select l.location_id, l.city, c.country_name, d.department_name,
count(e.employee_id) as empcount
from locations l
inner join countries c on c.country_id = l.country_id
inner join departments d on d.location_id = l.location_id
inner join employees e on e.department_id = d.department_id
where l.country_id = p_co_id
and exists (
select *
from departments d
inner join employees e on e.department_id = d.department_id
where d.location_id = l.location_id
)
group by l.location_id, l.city, c.country_name, d.department_name
having count(e.employee_id) > 0
order by l.location_id desc
)
LOOP
IF l_last_location_id IS NULL OR rec.location_id != l_last_location_id THEN
DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
|| ' - ' || rec.location_id || ' ' || rec.city);
l_last_location_id := rec.location_id;
END IF;
DBMS_OUTPUT.PUT_LINE(rec.department_name || ': ' || rec.empCount || ' werknemers');
END LOOP;
END get_emp_overview;
/
获得相同的输出。