In order to use dbms_output.put_line to print the debug info on screen,we need to fix the procedure debug:
create or replace package Debug as
procedure Debug(p_Description in varchar2,p_Value in varchar2);
procedure Reset;
end Debug;
create or replace package body Debug as
--procedure debug
procedure Debug(p_Description in varchar2,p_Value in varchar2) is
begin
dbms_output.put_line(p_Description||':'||p_Value);
end Debug;
--procedure reset
procedure Reset is
begin
--v_LineCount:=1;
--delete from debug_table;
dbms_output.disable;
dbms_output.enable(1000000);
end Reset;
begin
Reset;
end Debug;
So we put the log info not into the table ,but the cache of dbms_output.then write a function to test this:
create or replace function CountCredits(
p_StudentID in students.id%type
)return number as
v_TotalCredits number :=0;--total number of credits
v_CourseCredits number;--credits for one course
cursor c_RegisteredCourses is
select department,course from registered_students
where student_id = p_StudentID;
begin
--clear
Debug.Reset;
for v_CourseRec in c_RegisteredCourses loop
select t.num_credits into v_CourseCredits
from classes t
where t.department = v_CourseRec.department
and course = v_CourseRec.course;
--print the coursecredits
Debug.Debug('Inside loop,v_CourseCredits',v_CourseCredits);
v_TotalCredits := v_TotalCredits+v_CourseCredits;
end loop;
--pirnt the total credits
Debug.Debug('After loop,returning ',v_TotalCredits);
return v_TotalCredits;
end CountCredits;
Test sql is:
select id,CountCredits(id) total from students
the total credits will be show up.