PLSQL异常处理

Question

我需要让我的程序打印一个错误时，学生号码是无效的使用例外部分在两个地方。一个在主程序中，一个在子程序中。

如何才能使子程序中的异常运行，而不是在主程序中运行，反之亦然？

CREATE OR REPLACE PROCEDURE validate_student(
  p_snum IN students.snum%TYPE,
  p_student_error out VARCHAR2) AS

  v_snum students.snum%TYPE;

BEGIN
  --count the number of student with id p_snum and assign it to v_snum
  SELECT count(snum) INTO v_snum
  FROM students
  WHERE snum = p_snum;

  --if count is 0 then the student does not exist in the database, return error.
  IF v_snum = 0 THEN
    p_student_error := 'Student number ' || p_snum || ' is invalid. ';
  END IF;
END;
/

--This procedure checks if course id is valid
CREATE OR REPLACE PROCEDURE validate_course(
  p_callnum IN enrollments.callnum%TYPE,
  p_course_error out VARCHAR2) AS
  v_callnum enrollments.callnum%TYPE;
BEGIN
  --count the number of course with id p_callnum and assign it to v_callnum
  SELECT count(callnum) INTO v_callnum
  FROM schclasses
  WHERE callnum = p_callnum;

  --if count(callnum) = 0 then course does not exists in database, return error.
  IF v_callnum = 0 THEN
    p_course_error := 'Call number '|| p_callnum ||' is invalid .';
  END IF;
END;
/

--This function check the current amount of students enrolled in the course
CREATE OR REPLACE FUNCTION course_capacity(
  p_callnum enrollments.callnum%TYPE)
  RETURN VARCHAR2 IS

  v_current_enrolled NUMBER(3);
  v_capacity NUMBER(3);
  v_capacity_error VARCHAR2(200);
BEGIN
  --count number of students enrolled and assign it to v_current_enrolled
  SELECT count(snum) INTO v_current_enrolled
  FROM enrollments
  WHERE callnum = p_callnum
  AND grade is null;

  --check maximum capacity of the course and assign it to v_capacity
  SELECT capacity INTO v_capacity
  FROM schclasses
  WHERE callnum = p_callnum;

  --if current amount of students enrolled < maximum capacity then class is not full
  --else class is full.
  IF v_current_enrolled < v_capacity THEN
    v_capacity_error := NULL;
  ELSE
    v_capacity_error := 'Course number ' || p_callnum || ' is full. ';
  END IF;

  RETURN v_capacity_error;
END;
/

--This function check the units a student currently enrolls.
CREATE OR REPLACE FUNCTION student_unit_limit(
  p_snum students.snum%TYPE,
  p_callnum schclasses.callnum%TYPE)

  RETURN VARCHAR2 IS

  v_student_crhr NUMBER(2);
  v_course_crhr courses.crhr%TYPE;
  v_crhr_error VARCHAR2(200);

BEGIN
  --count the current units the student has, and assign it to v_student_crhr
  SELECT nvl(sum(crhr),0) INTO v_student_crhr
  FROM courses c INNER JOIN schclasses sc ON c.dept = sc.dept
                                                              AND c.cnum = sc.cnum
                         INNER JOIN enrollments e ON sc.callnum = e.callnum
  WHERE e.snum = p_snum
  AND grade IS NULL;

  --find the credit hour of the course
  SELECT crhr INTO v_course_crhr
  FROM courses c INNER JOIN schclasses sc ON c.dept = sc.dept
                                                            AND c.cnum = sc.cnum
  WHERE sc.callnum = p_callnum;

  --if current credit hour of student + the credit of the class <= 15 then
  --student hasn't reached maximum allowed units and can enroll in the course.
  IF v_student_crhr + v_course_crhr <= 15 THEN
    v_crhr_error := NULL;
  ELSE
    v_crhr_error := 'Max units allowed is exceeded. ';
  END IF;
  RETURN v_crhr_error;
END;
/

--This procedure combine all 4 of procedures and functions above,
CREATE OR REPLACE PROCEDURE addme(
  p_snum students.snum%TYPE,
  p_callnum schclasses.callnum%TYPE) AS

  v_error_text VARCHAR2(200);
  v_error_msg VARCHAR2(200);

BEGIN
    validate_student(
    p_snum,
    v_error_text);

    v_error_msg := v_error_text;

    validate_course(
    p_callnum,
    v_error_text);

    v_error_msg := v_error_msg || v_error_text;

    IF v_error_msg IS NOT NULL THEN
      dbms_output.put_line(v_error_msg);
    ELSE
      v_error_msg := course_capacity(p_callnum) || student_unit_limit(p_snum, p_callnum);
      IF v_error_msg IS NOT NULL THEN
        dbms_output.put_line(v_error_msg);
      ELSE
        INSERT INTO enrollments VALUES (p_snum, p_callnum, NULL);
        COMMIT;
        dbms_output.put_line('You have successfully enrolled in course number ' || p_callnum);
      END IF;
    END IF;
END;
/

AddMe过程是主程序，其他程序是子程序。

Answer 1

我建议制订一套方案：

create or replace package students_pkg as 

-- declare an exception
student_enroll_error exception;
-- bind error code -20001 to your custom exception
pragma exception_init (student_enroll_error, -20001);

-- the only visible procedure
procedure addme(
  p_snum students.snum%type,
  p_callnum schclasses.callnum%type);

end students_pkg;
/

create or replace package body students_pkg as 

procedure validate_student(
  p_snum in students.snum%type) as

  v_snum students.snum%type;
begin
  -- try to find a student
  select snum into v_snum
  from students
  where snum = p_snum;

exception
  -- if student not found, an no_data_found exception is rised
  when no_data_found then
    -- re-raise exception with custom code:
    raise_application_error(-20001, 'Student number ' || p_snum || ' is invalid.');
end;

-- the same logic as in the previous procedure
procedure validate_course(p_callnum in enrollments.callnum%type) is
  v_callnum enrollments.callnum%type;
begin
  select callnum into v_callnum
  from schclasses
  where callnum = p_callnum;

exception
  when no_data_found then
    raise_application_error(-20001, 'Call number '|| p_callnum || ' is invalid .');
end;

--This function check the current amount of students enrolled in the course
procedure check_course_capacity(p_callnum enrollments.callnum%type) is
  v_current_enrolled number(3);
begin
  -- here I combined queries. The idea is to make one query, which 
  -- returns one row if a course is not full and 0 rows, if it is full
  select count(e.snum) into v_current_enrolled
  from enrollments e
       join schclasses s on e.callnum = s.callnum
  where grade is null
  group by s. capacity
  having s. capacity > count(e.snum);

exception
  -- here no_data_found means that course is full
  when no_data_found then
    raise_application_error(-20001,  'Course number ' || p_callnum || ' is full. ');
end;

-- the same idea as in previous procedure, but I am not sure I fully understood the logic.
-- You can elaborate it
procedure check_student_unit_limit(
  p_snum students.snum%type,
  p_callnum schclasses.callnum%type) is

  v_course_num courses.cnum%type;
begin

  select c.cnum into v_student_crhr
  from courses c inner join schclasses sc on c.dept = sc.dept and c.cnum = sc.cnum
                 inner join enrollments e on sc.callnum = e.callnum
  where e.snum = p_snum
    and grade is null
  group by c.cnum
 having sum(crhr) <= 15;

exception
  when no_data_found then
    raise_application_error(-20001,  'Max units allowed is exceeded. ');
end;

procedure addme(
  p_snum students.snum%type,
  p_callnum schclasses.callnum%type) is

begin
  validate_student(p_snum);
  validate_course(p_callnum);
  check_course_capacity(p_callnum);
  check_student_unit_limit(p_snum, p_callnum);

  insert into enrollments values (p_snum, p_callnum, null);
  -- I would not recommend you to use commit here. Call it outside this procedure
  commit;
  dbms_output.put_line('You have successfully enrolled in course number ' || p_callnum);

exception
  -- if custom exception had been raised, you will get the second parameter,
  -- passed to raise_application_error, in SQLERRM function:
  when student_enroll_error then
    dbms_output.put_line(sqlerrm);
end;

end students_pkg;
/

Answer 2

除了@Dmitry的回答。对于每个call.So，最好将每个过程都包含一个开始和结束块以及异常句柄，您知道异常发生在哪里。

    procedure addme(
    p_snum students.snum%type,
    p_callnum schclasses.callnum%type) is

    -- bind error code -20001 to your custom exception
    pragma exception_init (student_enroll_error, -20001);
    begin

    Begin
      validate_student(p_snum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;

      Begin
      validate_course(p_callnum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;

      Begin
    check_course_capacity(p_callnum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;

      Begin
    check_student_unit_limit(p_snum, p_callnum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;


    insert into enrollments values (p_snum, p_callnum, null);
    -- I would not recommend you to use commit here. Call it outside this procedure
    commit;
    dbms_output.put_line('You have successfully enrolled in course number ' || p_callnum);

    exception
    -- if custom exception had been raised, you will get the second parameter,
    -- passed to raise_application_error, in SQLERRM function:
    when student_enroll_error then
      dbms_output.put_line(sqlerrm);
    end;