จะใช้ทริกเกอร์และเคอร์เซอร์ร่วมกับ for loop ใน oracle ได้อย่างไร

การอัปเดตคอลัมน์ total_student และ total_credit ของตาราง class จะเกี่ยวข้องกับการเขียนแบบสอบถามกับตาราง student ในทริกเกอร์ที่กำหนดไว้ในตาราง student การทำเช่นนี้จะนำไปสู่ข้อผิดพลาด ORA-04091: ชื่อตารางกำลังเปลี่ยนแปลง ทริกเกอร์/ฟังก์ชันอาจไม่เห็นข้อผิดพลาด เพื่อหลีกเลี่ยงข้อผิดพลาดดังกล่าว มีอย่างน้อยสามวิธีในการรับ total_student และ total_credits อัปเดต ทุกครั้งที่มีการเปลี่ยนแปลงบางอย่าง (ลบ/แทรก/อัปเดต) ในตาราง student มีการสันนิษฐานว่าตารางในความสัมพันธ์หลัก(class)/รายละเอียด(student):

1. วิธีแรก (วิธีที่ใหญ่ที่สุด) จะเกี่ยวข้องกับการสร้างวัตถุฐานข้อมูลหลายรายการ:

   1. ประเภท SQL ตารางที่ซ้อนกัน

      create or replace type T_clasids is table of number;
      

   2. แพ็คเกจซึ่งจะมีตัวแปรประเภท SQL นั้นเพื่อจัดเก็บคลาส IDs ที่ได้รับผลกระทบจากคำสั่ง DML

      ข้อกำหนดแพ็คเกจ:

      create or replace package Pkg
      is
        procedure reset_list_of_ids;      -- empties out the list of class ids
        procedure add_id(p_id in number); -- add new class id to a list if row
                                          -- with this ID has been affected by  
                                          -- a DML statement
        procedure update_class;           -- updates class table
      end;
      

      ตัวแพ็คเกจ:

      create or replace package body PKG
      is
      
         g_classids T_clasids := T_clasids();
      
      procedure reset_list_of_ids
      is
      begin
        g_classids.delete;
      end;
      
      procedure add_id(p_id in number)
      is
      begin
        g_classids.extend;
        g_classids(g_classids.count) := p_id;
      end;
      
      procedure update_class
      is
      begin
        update class t
           set (  t.total_student
                , t.total_credits ) = ( select count(*)
                                             , sum(s.credits)
                                          from student s
                                         where s.class = t.class)
         where t.class in (select column_value
                             from table(g_classids));
      
       end;
      

      จบ;

   3. ทริกเกอร์สามตัว: ก) ก่อนคำสั่ง; b) หลังแถว; c) หลังจากคำสั่ง

      -- before insert/update/delete statement level trigger 
      -- to empty out the class id list
      create or replace trigger tr_bs_initialize
      before insert or delete or update on student
      begin
        pkg.reset_list_of_ids;
      end;
      
      
      
      -- after insert/update/delete statement level trigger
      -- to update class table with new information 
      create or replace trigger tr_as_update_class
      after insert or delete or update on student
      begin
        pkg.update_class;
      end;
      
      
      -- after insert/update/delete row level trigger
      -- to populate class id collection with ids of 
      -- rows which has been affected by a DML statement
      create or replace trigger tr_ar_populate
      after insert or delete or update on student
      for each row
      begin
         -- nvl(:new.class, :old.class)
         -- in a case :new.clas happens to be null
         pkg.add_id(nvl(:new.class, :old.class));
      end;
      

      นี่คือตัวอย่างวิธีการทำงาน:

      select t.* from class t;
      
      CLASS      TOTAL_STUDENT TOTAL_CREDITS
      ---------- ------------- -------------
       1         null          null     
       2         null          null 
       3         null          null
      
       insert into student(roll_no, class, credits)
          values(1, 2, 3);
      
      
       select t.* from class t;
      
       CLASS      TOTAL_STUDENT TOTAL_CREDITS
       ---------- ------------- -------------
       1             null          null 
       2             1             3
       3             null          null
      

2. วิธีที่สอง (วิธีที่สั้นที่สุดและเป็นวิธีที่ดีกว่าเป็นการส่วนตัว) คือการลบ total_student และ total_credits ออกจากตาราง class สร้างมุมมองที่จะคำนวณและเก็บข้อมูลล่าสุดเกี่ยวกับจำนวนนักเรียนทั้งหมดในชั้นเรียนและผลรวมหน่วยกิต:

       create or replace view v_class as
          select c.class
               , count(s.class) as total_students
               , sum(s.credits) as total_credits
            from student s
           right join class c
              on (c.class = s.class)
           group by c.class
   
        select t.* from v_class t;
   
        CLASS      TOTAL_STUDENTS TOTAL_CREDITS
        ---------- -------------  -------------
        1             null          null 
        2             1             3
        3             null          null
   

3. แนวทางที่สาม กำหนดหนึ่งรายการหลังจากแทรก/อัปเดต/ลบทริกเกอร์ระดับคำสั่งบนตาราง sudent และใช้คำสั่ง merge เพื่ออัปเดตตาราง class:

        create or replace trigger tr_aiudsl
        after insert or update or delete on student
        begin
           merge into class c
           using (select t.class
                       , count(*)      as total_students
                       , sum(t.credits)as total_credit
                    from student t
                   group by t.class) q
                      on (q.class = c.class)
            when matched
            then update
              set c.total_student = q.total_students
                , c.total_credits  = q.total_credit;
        end;
   

   ดูข้อมูลเพิ่มเติมเกี่ยวกับคำสั่ง merge