plsql learning - three:debug

最新推荐文章于 2022-02-25 20:00:37 发布

最新推荐文章于 2022-02-25 20:00:37 发布 · 114 阅读

文章标签：

#VC++ #C #C++ #C# #Eclipse

DataBase 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一个使用PLSQL Developer进行调试的方法，并通过一个批量处理学生数据的包实现示例，展示了如何打开和关闭游标、批量获取数据以及打印数据。

The debug method three: we can use the debug tools of plsql developer,just like we debug our code in eclipse or vc.

create or replace package StudentFetch as
  type t_Students is table of students%rowtype
  index by binary_integer;
  
  --open the cursor for processing
  procedure OpenCursor;
  
  --close the cursor
  procedure CloseCursor;
  
  --return up to p_BatchSize rows in p_Students
  function FetchRows(p_BatchSize in out number,p_Students out t_students)
  return boolean;
  
  --print the p_BatchSize rows from p_Students
  procedure PrintRows(p_BatchSize in number,p_Students in t_students);
  
  end StudentFetch;

create or replace package body StudentFetch as
cursor c_AllStudents is
  select * from students order by id;
  
  --open the cursor for processing
  procedure OpenCursor is
  begin 
      open c_AllStudents;
  end  OpenCursor;
  --close the cursor
  procedure CloseCursor is
    begin
      close c_AllStudents;
    end CloseCursor;
    
  --return up to p_BatchSize rows in p_Students
  function FetchRows(p_BatchSize in out number,p_Students out t_students)
  return boolean is
  v_Finished boolean := true;
  begin 
    for v_Count in 1..p_BatchSize loop
      fetch c_AllStudents into p_Students(v_Count);
        if c_AllStudents%notfound then
          v_Finished := false;
          p_BatchSize := v_Count - 1;
          exit;
        end if;
     end loop;
     return v_Finished;
   end FetchRows;
   
  --print the p_BatchSize rows from p_Students
  procedure PrintRows(p_BatchSize in number,p_Students in t_students) is
  begin 
    for v_Count in 1..p_BatchSize loop
      dbms_output.put('ID: '||p_Students(v_Count).ID);
      dbms_output.put('Name: '||p_Students(v_Count).first_name);
      dbms_output.put_line(' '||p_Students(v_Count).last_name);
    end loop;
  end PrintRows;
 end StudentFetch;
  
  --test procedure
  create or replace procedure TestCredit is
  v_BatchSize number:=5;
  v_Students StudentFetch.t_Students;
  begin
    StudentFetch.OpenCursor;
    while StudentFetch.FetchRows(v_BatchSize,v_Students) loop
      StudentFetch.PrintRows(v_BatchSize,v_Students);
    end loop;
    if v_BatchSize != 0 then
      StudentFetch.PrintRows(v_BatchSize,v_Students);
    end if;
    StudentFetch.CloseCursor;
  end TestCredit;

we can set breakpoint of the procedure,then go to find the value of every step we executed.it is convenient.