创建过程Oracle SQL Developer时出现异常错误

debugcn 发表于 Dev

拉米什

在过去的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] 删除。