在过去的2个小时里，我一直在动脑筋，无法找到解决此错误的方法。我正在创建一个简单的程序来查找雇员。PL / SQL一直给我错误。问题是什么 ？我在这里做错了什么？
这是我的程序：
create or replace PROCEDURE find_employee (employeeNo IN number) as
INVALID_ID exception;
TOO_MANY_ROWS exception;
res number;
BEGIN
dbms_output.enable;
Select count(*) into res from employee where ID=employeeNo;
if (res>1)then -- Checking that the total count of the employee is 1 or not
raise TOO_MANY_ROWS; -- if greater then 1 then it raise TOO_MANY_ROWS error
ELSE IF (NOT EXISTS (Select ID from employee where ID=employeeNo)) -- Checking that the employeeNo user passes exist or not
then
raise INVALID_ID; -- if employeeNo doesnot exit then display invalid id message
ELSE
Select* from Employee where ID=employeeNo; -- else return employee info whose id==employeeNo
END IF;
EXCEPTION
when TOO_MANY_ROWS then
DBMS_OUTPUT.PUT_LINE ('Too many Rows with same employee id');
when INVALID_ID then
DBMS_OUTPUT.PUT_LINE ('Invalid employee id');
END find_employee;
错误是这样的：
Error(15,1): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
Error(20,18): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map
请上帝帮助我：'（
您丢失了END IF（第16行）。如果您编写格式化的代码（嵌套IF应缩进），则更容易发现它。
SQL> create or replace PROCEDURE find_employee (employeeNo IN number) as
2 INVALID_ID exception;
3 TOO_MANY_ROWS exception;
4 res number;
5 BEGIN
6 dbms_output.enable;
7 Select count(*) into res from employee where ID=employeeNo;
8 if (res>1)then -- Checking that the total count of the employee is 1 or not
9 raise TOO_MANY_ROWS; -- if greater then 1 then it raise TOO_MANY_ROWS error
10 ELSE IF (NOT EXISTS (Select ID from employee where ID=employeeNo)) -- Checking that the employeeNo user passes exist or not
11 then
12 raise INVALID_ID; -- if employeeNo doesnot exit then display invalid id message
13 ELSE
14 Select* from Employee where ID=employeeNo; -- else return employee info whose id==employeeNo
15 END IF;
16 END IF; --> this is missing
17 EXCEPTION
18 when TOO_MANY_ROWS then
19 DBMS_OUTPUT.PUT_LINE ('Too many Rows with same employee id');
20 when INVALID_ID then
21 DBMS_OUTPUT.PUT_LINE ('Invalid employee id');
22 END find_employee;
正如@Dornaut所评论的那样，该代码可能不是最好的代码。这是另一个选择；看看是否有帮助。
CREATE OR REPLACE PROCEDURE find_employee (employeeNo IN NUMBER)
AS
res NUMBER;
e_row employee%ROWTYPE;
BEGIN
SELECT *
INTO e_row
FROM employee
WHERE id = employeeNo;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Invalid employee ID');
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line ('Too many rows with same employee ID');
END find_employee;
因此：如果SELECT返回NO_DATA_FOUND或TOO_MANY_ROWS，它将得到处理。否则，它将把整个行提取到一个变量中。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句