我在oracle-sql server中的for循环中遇到了异常的语法代码问题

Question

当我的经理计数等于0时，我想添加异常。但是我的代码有语法错误，我不知道该怎么办

错误：

PLS-00103：在预期以下项之一时遇到符号"EXCEPTION"以下内容：

(如果循环mod为null杂注，则开始大小写为goto声明结束退出在<lt；持续关闭当前删除获取锁定插入打开回滚存储点集sql执行所有合并管道的提交清除jsonexists jsonvaluejsonquery jsonobject jsonarray06550.000000-"行%s，列%s：\n%s"*原因：通常是PL/SQL编译错误。*行动：

FOR l in c LOOP
select count(*) into variable
from table where job = 'Manager'
and condition;

if SQL%NOTFOUND
EXCEPTION
then 
RAISE e_my_exception;
end if;
//the code to check salary of employee greater than president or lower than 100 is here //
WHEN e_my_exception THEN
DBMS_OUTPUT.PUT_LINE('ERROR!!! One department has no manager ');  
END LOOP;

我希望打印出"ERROR！！"！！！一个部门没有经理，但却出现了一个错误：

Answer 1

您以错误的格式实现了它，如下所示。。。

DECLARE 
<declarations section> 
BEGIN 
<executable command(s)> //your logic
EXCEPTION 
<exception handling> 
END;

和循环是这样实现的。。。例如

DECLARE 
i number(1); 
j number(1); 
BEGIN 
FOR i IN 1..3 LOOP 
dbms_output.put_line('i is: '|| i ); 
END loop; 
END; 

关于处理异常，请参阅。。。

DECLARE 
c_id customers.id%type := 8; 
c_name customerS.Name%type; 
c_addr customers.address%type; 
BEGIN 
SELECT  name, address INTO  c_name, c_addr 
FROM customers 
WHERE id = c_id;  
DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
EXCEPTION 
WHEN no_data_found THEN 
dbms_output.put_line('No such customer!'); 
WHEN others THEN 
dbms_output.put_line('Error!'); 
END; 

Answer 2

您没有正确处理异常。我建议如下：

FOR l in c LOOP
BEGIN
select count(*)
into variable
from table where job = 'Manager'
and condition;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR!!! One department has no manager');  
END;
END LOOP;

Answer 3

没有所谓的"oracle-sql-server"(正如您的标题所示(。它是"Oracle"或"(Microsoft(SQL Server"。你发布的代码是Oracle，所以我建议你修改标题。

---

现在，您的问题是：由于count(*(在"未找到数据"或"sql%notfound"时都不起作用(结果为0(-请参阅演示：

SQL> select count(*) from dual where 1 = 2;
COUNT(*)
----------
0
SQL>

然后你可以做这样的事情：

FOR l in c LOOP
begin
select count(*) into variable
from table where job = 'Manager'
and condition;
if variable = 0 then
RAISE e_my_exception;
end if;
--the code to check salary of employee greater than president or lower than 100 is here //
exception
WHEN e_my_exception THEN
DBMS_OUTPUT.PUT_LINE('ERROR!!! One department has no manager ');  
END LOOP;  

这样的代码将显示消息(如果您的工具支持它(，但循环将继续循环，即您的代码不会停止。

---

不过，由于您实际上并没有提出任何东西，一个更简单的选项是

FOR l in c LOOP
select count(*) into variable
from table where job = 'Manager'
and condition;
if variable = 0 then
DBMS_OUTPUT.PUT_LINE('ERROR!!! One department has no manager ');  
end if;
--the code to check salary of employee greater than president or lower than 100 is here //
END LOOP;