调试经验——将PL/SQL代码块转换为存储过程

问题描述：

有一段略微复杂的PL/SQL，如果通过肉眼走查代码的运行的过程的话，比较费力气。然后就想到了能否在Toad中调试代码，即，在调试器中进行断点设置、单步执行的步骤。貌似存储过程是可以进行调试的。所以，面临的问题就是如何将这段PL/SQL代码转换为存储过程。

PL/SQL代码如下：

-- For Example ch08_7b.sql
DECLARE
   v_student_id  NUMBER := &sv_student_id;
   v_name        VARCHAR2(30);
   v_enrollments NUMBER;
BEGIN
   SELECT s.first_name||' '||s.last_name, COUNT(*)
     INTO v_name, v_enrollments
     FROM student s, enrollment e
    WHERE s.student_id = e.student_id
      AND s.student_id = v_student_id
   GROUP BY s.first_name||' '||s.last_name;
   
   DBMS_OUTPUT.PUT_LINE 
      ('Student '||v_name||' has '||v_enrollments||' enrollments');
EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN 
      BEGIN
         SELECT first_name||' '||last_name
           INTO v_name
           FROM student
          WHERE student_id = v_student_id;

        DBMS_OUTPUT.PUT_LINE ('Student '||v_name||' is not enrolled');
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.PUT_LINE ('This student does not exist');
      END;
END;

解决方法：

1. 增加一句话： CREATE OR REPLACE PROCEDURE TEST_PROCEDURE (
   SV_STUDENT_ID    STUDENT.STUDENT_ID%TYPE)
AS
   V_STUDENT_ID    STUDENT.STUDENT_ID%TYPE;

2. 去掉Declare关键字

3. 其他部分不变

转换后的存储过程的DDL如下：

CREATE OR REPLACE PROCEDURE C##STUDENT.TEST_PROCEDURE (
   SV_STUDENT_ID    number)
AS
   V_STUDENT_ID    number:=SV_STUDENT_ID; 
   V_NAME          VARCHAR2 (30);
   V_ENROLLMENTS   NUMBER;
BEGIN
     SELECT S.FIRST_NAME || ' ' || S.LAST_NAME, COUNT (*)
       INTO V_NAME, V_ENROLLMENTS
       FROM STUDENT S, ENROLLMENT E
      WHERE S.STUDENT_ID = E.STUDENT_ID AND S.STUDENT_ID = V_STUDENT_ID
   GROUP BY S.FIRST_NAME || ' ' || S.LAST_NAME;

   DBMS_OUTPUT.PUT_LINE (
      'Student ' || V_NAME || ' has ' || V_ENROLLMENTS || ' enrollments');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      BEGIN
         SELECT FIRST_NAME || ' ' || LAST_NAME
           INTO V_NAME
           FROM STUDENT
          WHERE STUDENT_ID = V_STUDENT_ID;

         DBMS_OUTPUT.PUT_LINE ('Student ' || V_NAME || ' is not enrolled');
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.PUT_LINE ('This student does not exist');
      END;
END;

创建好存储过程后，可在schema browser中查看：