问题描述:
有一段略微复杂的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中查看: