SQL - 在预期以下情况之一时遇到符号"EXCEPTION"

Question

我正在尝试创建一个过程。当我运行代码时，出现错误：

21/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         ( begin case declare 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

也许这与我的BEGIN和END陈述有关？我是SQL的新手，一直无法弄清楚。

这是我的代码：

CREATE OR REPLACE PROCEDURE inserttocasting(idofmovie  IN casting.movieid%TYPE,
                                            idofactor  IN casting.actorid%TYPE,
                                            newordinal IN casting.ordinal%TYPE)
IS
  identical_ordinal EXCEPTION;
  too_many_movies EXCEPTION;
BEGIN
  SELECT cast_year(idofmovie) INTO v_year FROM dual;
  IF ordinal_count(newordinal, idofmovie) >= 1 THEN
    RAISE identical_ordinal;
  ELSIF cast_count(v_year, idofactor) >= 3 THEN
    RAISE too_many_movies;
  ELSE
    INSERT INTO casting (movieid, actorid, ordinal)
    VALUES (idofmovie, idofactor, newordinal);
  END IF;
  EXCEPTION WHEN identical_ordinal THEN
    dbms_output.PUT_LINE('Ordinal already exists for this film!');
  EXCEPTION WHEN too_many_movies THEN
    DBMS_OUTPUT.PUT_LINE('This actor has already been cast in more than three movies this year.');
    COMMIT;
END;
/

以下是必要时使用的函数的代码：

CREATE OR REPLACE FUNCTION ordinal_count(
newordinal IN CASTING.ORDINAL%TYPE, idofmovie IN CASTING.MOVIEID%TYPE
) RETURN NUMBER AS 
p_ordinal_number NUMBER;
BEGIN
SELECT COUNT(ACTORID)
INTO p_ordinal_number
FROM CASTING
WHERE ORDINAL = newordinal
AND MOVIEID = idofmovie;
RETURN p_ordinal_number;
END ordinal_count;
/
CREATE OR REPLACE FUNCTION cast_year(idofmovie IN CASTING.MOVIEID%TYPE
) RETURN NUMBER AS 
v_year NUMBER;
BEGIN
SELECT YEAR
INTO v_year
FROM MOVIE
WHERE MOVIEID = idofmovie;
RETURN v_year;
END cast_year;
/
CREATE OR REPLACE FUNCTION cast_count(v_year IN MOVIE.YEAR%TYPE, idofactor IN CASTING.ACTORID%TYPE
) RETURN NUMBER AS 
v_count NUMBER;
BEGIN
SELECT COUNT(v_year)
INTO v_count
FROM CASTING
WHERE ACTORID = idofactor;
RETURN v_count;
END cast_count;
/

Answer 1

将第一个过程更改为：

CREATE OR REPLACE PROCEDURE insertToCasting(
  idofmovie IN CASTING.MOVIEID%TYPE,
  idofactor IN CASTING.ACTORID%TYPE,
  newordinal IN CASTING.ORDINAL%TYPE)
IS
  identical_ordinal EXCEPTION;
  too_many_movies EXCEPTION;
BEGIN
  SELECT cast_year(idofmovie)
    INTO v_year
    FROM dual;
  IF ordinal_count(newordinal, idofmovie) >= 1 THEN
    RAISE identical_ordinal;
  ELSIF cast_count(v_year, idofactor) >= 3 THEN
    RAISE too_many_movies;
  ELSE
    INSERT INTO CASTING (MOVIEID, ACTORID, ORDINAL)
      VALUES (idofmovie, idofactor, newordinal);
    COMMIT;
  END IF;
EXCEPTION
  WHEN identical_ordinal THEN
    DBMS_OUTPUT.PUT_LINE('Ordinal already exists for this film!');
  WHEN too_many_movies THEN
    DBMS_OUTPUT.PUT_LINE('This actor has already been cast in more than three movies this year.');
END;

您可以在单个 EXCEPTION 处理程序中放置多个 WHEN 块。此外，您在WHEN too_many_movies...处理程序的末尾有 COMMIT 语句，当我怀疑您希望它遵循 INSERT 时。它的定位方式只有在输入too_many_movies处理程序时才会执行，这似乎没有意义。