plsql learning - two:debug

最新推荐文章于 2022-10-23 20:29:19 发布

最新推荐文章于 2022-10-23 20:29:19 发布 · 129 阅读

文章标签：

#SQL #C #C++ #C# #UP

DataBase 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一种使用Oracle PL/SQL中的dbms_output工具进行调试的方法。通过创建Debug包，可以方便地输出变量值和流程信息，从而帮助开发者更好地理解和追踪程序的执行过程。文章还提供了一个具体的例子，演示了如何在学生学分计数函数中应用此调试方法。

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.