การทำธุรกรรมอัตโนมัติใน Oracle PL / SQL

คำสั่ง TCL ใน PL/SQL คืออะไร

TCL ย่อมาจาก Transaction Control Statements (คำสั่งควบคุมธุรกรรม) คำสั่งเหล่านี้จะบันทึกธุรกรรมที่กำลังดำเนินการอยู่ หรือยกเลิกธุรกรรมที่กำลังดำเนินการอยู่ คำสั่งเหล่านี้มีบทบาทสำคัญมาก เพราะหากไม่บันทึกธุรกรรม การเปลี่ยนแปลงที่เกิดขึ้นจะไม่มีผล คำสั่ง DML ข้อมูลจะไม่ถูกจัดเก็บอย่างถาวรในฐานข้อมูล ด้านล่างนี้คือคำสั่ง TCL ต่างๆ ใน PL / SQL.

การทำธุรกรรมจะเสร็จสมบูรณ์ภายใต้สถานการณ์ต่อไปนี้:

• เมื่อมีการออกแถลงการณ์ใดๆ ข้างต้น (ยกเว้น SAVEPOINT)
• เมื่อมีการออกคำสั่ง DDL (DDL คือคำสั่งที่ยืนยันการดำเนินการโดยอัตโนมัติ)
• เมื่อมีการออกคำสั่ง DCL (DCL คือคำสั่งยืนยันอัตโนมัติ)

การใช้ SAVEPOINT และ ROLLBACK TO

ตารางด้านบนแนะนำคำสั่ง SAVEPOINT และ ROLLBACK TO ซึ่งเมื่อใช้ร่วมกันจะช่วยให้คุณควบคุมธุรกรรมได้บางส่วน คำสั่ง SAVEPOINT จะทำเครื่องหมายจุดที่กำหนดชื่อไว้ภายในธุรกรรมปัจจุบัน การใช้คำสั่ง ROLLBACK TO ที่จุดบันทึกนั้นในภายหลังจะยกเลิกการเปลี่ยนแปลงทั้งหมดที่เกิดขึ้นหลังจากนั้น ในขณะที่ยังคงรักษาการเปลี่ยนแปลงอื่นๆ ไว้ping งานที่ทำก่อนหน้านั้นยังคงสภาพเดิม

วิธีนี้มีประโยชน์เมื่อธุรกรรมขนาดใหญ่ดำเนินการหลายขั้นตอน SQL ทำตามขั้นตอนไปเรื่อยๆ และมีเพียงขั้นตอนสุดท้ายเท่านั้นที่ล้มเหลว แทนที่จะยกเลิกธุรกรรมทั้งหมด คุณสามารถย้อนกลับไปยังจุดบันทึกที่ใช้งานได้ล่าสุดและดำเนินการต่อได้

ไวยากรณ์:

SAVEPOINT <savepoint_name>;
   -- one or more DML statements
ROLLBACK TO <savepoint_name>;

ข้อควรจำเกี่ยวกับจุดบันทึกเกม:

• จุดบันทึก (SAVEPOINT) จะมีอยู่เฉพาะภายในธุรกรรมปัจจุบันเท่านั้น การยืนยัน (COMMIT) หรือการย้อนกลับ (ROLLBACK) จะลบจุดบันทึกทั้งหมด
• เมื่อคุณย้อนกลับไปยังจุดบันทึกใด ๆ จุดบันทึกที่สร้างขึ้นหลังจากนั้นจะถูกลบออก แต่จุดบันทึกที่คุณย้อนกลับไปจะยังคงอยู่
• คำสั่ง ROLLBACK TO ไม่ได้เป็นการยุติธุรกรรม การเปลี่ยนแปลงที่เกิดขึ้นก่อนจุดบันทึกจะยังคงอยู่ในสถานะรอการดำเนินการจนกว่าคุณจะทำการยืนยัน (COMMIT) หรือย้อนกลับ (ROLLBACK)
• หากคุณใช้ชื่อจุดบันทึกซ้ำ จุดบันทึกใหม่จะย้ายเครื่องหมายไปยังตำแหน่งที่ใหม่กว่า

เนื่องจากคำสั่ง ROLLBACK TO ยังคงเปิดธุรกรรมไว้ คุณจึงยังสามารถตัดสินใจได้ในตอนท้ายว่าจะ COMMIT การเปลี่ยนแปลงที่เหลืออยู่ หรือจะยกเลิกการเปลี่ยนแปลงเหล่านั้นด้วยคำสั่ง ROLLBACK ทั้งหมด

ธุรกรรมอัตโนมัติคืออะไร

ใน PL/SQL การเปลี่ยนแปลงข้อมูลทั้งหมดเรียกว่าธุรกรรม ธุรกรรมจะถือว่าเสร็จสมบูรณ์เมื่อมีการบันทึกหรือยกเลิกการเปลี่ยนแปลง หากไม่มีการบันทึกหรือยกเลิกการเปลี่ยนแปลง ธุรกรรมนั้นจะไม่ถือว่าเสร็จสมบูรณ์ และการเปลี่ยนแปลงข้อมูลจะไม่ถูกบันทึกอย่างถาวรบนเซิร์ฟเวอร์

โดยค่าเริ่มต้น PL/SQL จะถือว่าการแก้ไขทั้งหมดในระหว่างเซสชันเป็นธุรกรรมเดียว และการบันทึกหรือยกเลิกธุรกรรมนั้นจะส่งผลกระทบต่อการเปลี่ยนแปลงที่รอดำเนินการทั้งหมดในเซสชันนั้น ธุรกรรมอิสระช่วยให้นักพัฒนาสามารถทำการเปลี่ยนแปลงในธุรกรรมแยกต่างหาก และบันทึกหรือยกเลิกธุรกรรมนั้นได้โดยไม่ส่งผลกระทบต่อธุรกรรมหลักของเซสชัน

• สามารถกำหนดธุรกรรมอัตโนมัติได้ในระดับโปรแกรมย่อย
• ที่จะทำให้การใดๆ โปรแกรมย่อย หากต้องการทำงานในธุรกรรมอื่น จะต้องระบุคำหลัก PRAGMA AUTONOMOUS_TRANSACTION ในส่วนประกาศของบล็อกนั้น
• คำสั่งนี้จะสั่งให้คอมไพเลอร์ถือว่านี่เป็นธุรกรรมแยกต่างหาก และการบันทึกหรือยกเลิกข้อมูลภายในบล็อกนี้จะไม่ส่งผลต่อธุรกรรมหลัก
• การออกคำสั่ง COMMIT หรือ ROLLBACK เป็นสิ่งจำเป็นก่อนที่จะออกจากธุรกรรมอิสระนี้และกลับไปยังธุรกรรมหลัก เนื่องจากในแต่ละครั้งจะมีเพียงธุรกรรมเดียวที่กำลังทำงานอยู่
• ดังนั้น เมื่อเริ่มธุรกรรมอัตโนมัติแล้ว จะต้องบันทึกและดำเนินการให้เสร็จสมบูรณ์ก่อน จึงจะสามารถโอนการควบคุมกลับไปยังธุรกรรมหลักได้

ไวยากรณ์:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
.
BEGIN
<execution_part>
[COMMIT|ROLLBACK]
END;
/

ในไวยากรณ์ข้างต้น บล็อกดังกล่าวได้ถูกทำให้เป็นธุรกรรมอิสระแล้ว

1 ตัวอย่าง: ในตัวอย่างนี้ เราจะมาทำความเข้าใจวิธีการทำงานของธุรกรรมอัตโนมัติกัน

ภาพหน้าจอด้านล่างแสดงตัวอย่างธุรกรรมอัตโนมัติและผลลัพธ์ที่ได้ Oracle.

DECLARE
   l_salary   NUMBER;
   PROCEDURE nested_block IS
   PRAGMA autonomous_transaction;
    BEGIN
     UPDATE emp
       SET salary = salary + 15000
       WHERE emp_no = 1002;
   COMMIT;
   END;
BEGIN
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;
   dbms_output.put_line('Before Salary of 1001 is'|| l_salary);
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
   dbms_output.put_line('Before Salary of 1002 is'|| l_salary);    
   UPDATE emp 
   SET salary = salary + 5000 
   WHERE emp_no = 1001;

nested_block;
ROLLBACK;

 SELECT salary INTO  l_salary FROM emp WHERE emp_no = 1001;
 dbms_output.put_line('After Salary of 1001 is'|| l_salary);
 SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
 dbms_output.put_line('After Salary of 1002 is '|| l_salary);
end;

เอาท์พุต

Before:Salary of 1001 is 15000 
Before:Salary of 1002 is 10000 
After:Salary of 1001 is 15000 
After:Salary of 1002 is 25000

Code คำอธิบาย:

• Code สาย 2: ประกาศตัวแปร l_salary เป็นประเภท NUMBER
• Code สาย 3: ประกาศขั้นตอน nested_block
• Code สาย 4: กำหนดให้ขั้นตอน nested_block เป็น AUTONOMOUS_TRANSACTION
• Code บรรทัดที่ 7-9: เพิ่มเงินเดือนสำหรับพนักงานหมายเลข 1002 เป็น 15000
• Code สาย 10: ดำเนินการทำธุรกรรมแบบอัตโนมัติ
• Code บรรทัดที่ 13-16: พิมพ์รายละเอียดเงินเดือนของพนักงานหมายเลข 1001 และ 1002 ก่อนการเปลี่ยนแปลง
• Code บรรทัดที่ 17-19: เพิ่มเงินเดือนสำหรับพนักงานหมายเลข 1001 เป็น 5000
• Code สาย 20: กำลังเรียกใช้โปรซีเดอร์ nested_block
• Code สาย 21: ยกเลิกธุรกรรมหลัก
• Code บรรทัดที่ 22-25: พิมพ์รายละเอียดเงินเดือนของพนักงานหมายเลข 1001 และ 1002 หลังจากทำการเปลี่ยนแปลงแล้ว

การขึ้นเงินเดือนของพนักงานหมายเลข 1001 ไม่ปรากฏในรายการ เนื่องจากธุรกรรมหลักถูกยกเลิกไปแล้ว ส่วนการขึ้นเงินเดือนของพนักงานหมายเลข 1002 ปรากฏในรายการ เนื่องจากส่วนนั้นถูกแยกเป็นธุรกรรมต่างหากและบันทึกไว้ในตอนท้ายแล้ว

ดังนั้นไม่ว่าจะบันทึกหรือยกเลิกในธุรกรรมหลัก การเปลี่ยนแปลงในธุรกรรมอิสระจะถูกบันทึกโดยไม่ส่งผลกระทบต่อธุรกรรมหลัก

ควรใช้ธุรกรรมอัตโนมัติเมื่อใด

ธุรกรรมแบบอิสระมีประสิทธิภาพสูง ดังนั้นจึงควรทราบว่าควรใช้เมื่อใด ควรสงวนไว้สำหรับงานที่ต้องสำเร็จหรือล้มเหลวโดยอิสระจากธุรกรรมหลัก ไม่ใช่สำหรับตรรกะทางธุรกิจหลัก กรณีการใช้งานทั่วไป ได้แก่:

• การบันทึกการตรวจสอบ: บันทึกว่าใครเป็นผู้เปลี่ยนแปลงข้อมูลสำคัญ เมื่อใด และค่าเก่าและค่าใหม่ เพื่อให้บันทึกยังคงอยู่แม้ว่าธุรกรรมหลักจะถูกยกเลิกก็ตาม
• การบันทึกข้อผิดพลาด: เขียนบันทึกข้อผิดพลาดไว้ภายใน ข้อยกเว้น ใช้ handler และ COMMIT เพื่อเก็บรายละเอียดการวินิจฉัยไว้ ในขณะที่ธุรกรรมที่ล้มเหลวจะถูกยกเลิก
• ตัวนับและสถิติ: เพิ่มค่าตัวนับการใช้งานหรือจำนวนครั้งที่เข้าชม ซึ่งจะต้องคงอยู่ต่อไปไม่ว่าผู้โทรจะเรียกใช้ฟังก์ชันนี้ด้วยผลลัพธ์ใดก็ตาม
• COMMIT ภายในทริกเกอร์: ตัวกระตุ้นไม่สามารถออกคำสั่ง COMMIT ได้โดยตรง การทำธุรกรรมแบบอิสระเป็นวิธีเดียวที่รองรับการทำเช่นนั้นได้

ควรหลีกเลี่ยงธุรกรรมแบบอิสระสำหรับการอัปเดตทั่วไปที่ควรมีผลพร้อมกับธุรกรรมหลัก การใช้งานมากเกินไปอาจซ่อนข้อมูลไว้เบื้องหลังการยืนยัน (commit) ที่แยกต่างหาก และทำให้การแก้ไขข้อผิดพลาดทำได้ยากขึ้น โดยทั่วไปแล้ว บล็อกอิสระทุกบล็อกจะต้องลงท้ายด้วยคำสั่ง COMMIT หรือ ROLLBACK อย่างชัดเจน

ธุรกรรมอัตโนมัติเทียบกับธุรกรรมปกติ

ความแตกต่างระหว่างธุรกรรมปกติ (หลัก) กับธุรกรรมอิสระนั้นอยู่ที่ขอบเขตและความเป็นอิสระ ตารางด้านล่างนี้เปรียบเทียบธุรกรรมทั้งสองประเภท

แตกต่างจากปกติ บล็อกซ้อนกันในขณะที่บล็อกอิสระนั้น การเปลี่ยนแปลงของบล็อกอิสระมักจะใช้ผลลัพธ์เดียวกับธุรกรรมหลัก แต่บล็อกอิสระนั้นจะยืนหยัดอยู่ได้ด้วยตัวเอง การเข้าใจความแตกต่างนี้จะช่วยให้คุณตัดสินใจได้ว่าเมื่อใดที่บล็อกควรเป็นอิสระ และเมื่อใดที่บล็อกควรใช้ผลลัพธ์เดียวกับธุรกรรมหลัก

คำถามที่พบบ่อย