Giao dịch tự chủ trong Oracle PL / SQL

Câu lệnh TCL trong PL/SQL là gì?

TCL là viết tắt của Transaction Control Statements (Câu lệnh kiểm soát giao dịch). Những câu lệnh này có chức năng lưu hoặc hoàn tác các giao dịch đang chờ xử lý. Chúng đóng vai trò rất quan trọng, bởi vì nếu giao dịch không được lưu, những thay đổi được thực hiện thông qua giao dịch đó sẽ không được lưu lại. Các câu lệnh DML sẽ không được lưu trữ vĩnh viễn trong cơ sở dữ liệu. Dưới đây là các câu lệnh TCL khác nhau trong PL / SQL.

Giao dịch sẽ được hoàn tất trong các trường hợp sau:

• Khi bất kỳ câu lệnh nào nêu trên được phát hành (ngoại trừ SAVEPOINT).
• Khi các câu lệnh DDL được thực thi (DDL là các câu lệnh tự động cam kết).
• Khi các câu lệnh DCL được phát hành (DCL là các câu lệnh tự động xác nhận).

Sử dụng SAVEPOINT và ROLLBACK TO

Bảng trên giới thiệu về SAVEPOINT và ROLLBACK TO, và cả hai cùng nhau cho phép bạn kiểm soát một phần giao dịch. SAVEPOINT đánh dấu một điểm được đặt tên bên trong giao dịch hiện tại. Một thao tác ROLLBACK TO sau đó đến điểm lưu này sẽ hoàn tác mọi thay đổi được thực hiện sau đó, trong khi vẫn giữ nguyên các thay đổi cần thiết.ping Công việc đã hoàn thành trước đó vẫn được giữ nguyên.

Điều này hữu ích khi một giao dịch dài thực hiện nhiều thao tác. SQL Các bước thực hiện và chỉ bước cuối cùng bị lỗi. Thay vì hủy bỏ toàn bộ giao dịch, bạn có thể quay lại điểm lưu thành công gần nhất và tiếp tục.

Cú pháp:

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

Những điểm quan trọng cần nhớ về điểm lưu:

• Một SAVEPOINT chỉ tồn tại trong giao dịch hiện tại; thao tác COMMIT hoặc ROLLBACK toàn bộ sẽ xóa mọi savepoint.
• Khi bạn khôi phục về một điểm lưu trước đó, tất cả các điểm lưu được tạo sau đó sẽ bị xóa, nhưng điểm lưu mà bạn khôi phục về sẽ được giữ lại.
• Lệnh ROLLBACK TO không kết thúc giao dịch; các thay đổi được thực hiện trước điểm lưu vẫn ở trạng thái chờ xử lý cho đến khi bạn thực hiện lệnh COMMIT hoặc ROLLBACK.
• Nếu bạn sử dụng lại tên điểm lưu, lệnh SAVEPOINT mới hơn sẽ di chuyển điểm đánh dấu đến vị trí sau.

Vì lệnh ROLLBACK TO vẫn để giao dịch mở, bạn vẫn phải quyết định ở cuối xem có nên COMMIT các thay đổi còn lại hay loại bỏ chúng bằng lệnh ROLLBACK toàn bộ.

Giao dịch tự chủ là gì

Trong PL/SQL, tất cả các thao tác sửa đổi dữ liệu được gọi là một giao dịch. Một giao dịch được coi là hoàn tất khi có lệnh lưu hoặc hủy bỏ được áp dụng. Nếu không có lệnh lưu hoặc hủy bỏ nào được đưa ra, thì giao dịch không được coi là hoàn tất, và các sửa đổi được thực hiện trên dữ liệu sẽ không được lưu vĩnh viễn trên máy chủ.

Theo mặc định, PL/SQL coi tất cả các thay đổi trong một phiên làm việc là một giao dịch duy nhất, và việc lưu hoặc hủy bỏ giao dịch đó sẽ ảnh hưởng đến mọi thay đổi đang chờ xử lý trong phiên. Giao dịch tự động cung cấp cho nhà phát triển khả năng thực hiện các thay đổi trong một giao dịch riêng biệt và lưu hoặc hủy bỏ giao dịch cụ thể đó mà không ảnh hưởng đến giao dịch chính của phiên.

• Giao dịch tự động có thể được chỉ định ở cấp độ chương trình con.
• Để thực hiện bất kỳ chương trình con Để thực hiện một giao dịch khác, từ khóa PRAGMA AUTONOMOUS_TRANSACTION cần được cung cấp trong phần khai báo của khối lệnh đó.
• Lệnh này hướng dẫn trình biên dịch coi đây là một giao dịch riêng biệt, và việc lưu hoặc loại bỏ dữ liệu bên trong khối này sẽ không được phản ánh trong giao dịch chính.
• Việc thực hiện lệnh COMMIT hoặc ROLLBACK là bắt buộc trước khi kết thúc giao dịch tự động này và quay lại giao dịch chính, bởi vì tại bất kỳ thời điểm nào chỉ có thể có một giao dịch đang hoạt động.
• Vì vậy, một khi giao dịch tự động được bắt đầu, nó phải được lưu lại và hoàn tất trước khi quyền điều khiển có thể chuyển trở lại giao dịch chính.

Cú pháp:

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

Trong cú pháp trên, khối lệnh đã được chuyển thành giao dịch tự chủ.

Ví dụ 1: Trong ví dụ này, chúng ta sẽ tìm hiểu cách thức hoạt động của một giao dịch tự động.

Ảnh chụp màn hình bên dưới hiển thị ví dụ về giao dịch tự động này và kết quả đầu ra của nó. 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;

Đầu ra

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 Giải thích:

• Code dòng 2: Khai báo l_salary là kiểu NUMBER.
• Code dòng 3: Khai báo thủ tục nested_block.
• Code dòng 4: Biến thủ tục nested_block thành một AUTONOMOUS_TRANSACTION.
• Code dòng 7-9: Tăng lương cho nhân viên số 1002 lên 15000.
• Code dòng 10: Thực hiện giao dịch tự chủ.
• Code dòng 13-16: In chi tiết lương của nhân viên 1001 và 1002 trước khi thay đổi.
• Code dòng 17-19: Tăng lương cho nhân viên số 1001 lên 5000.
• Code dòng 20: Gọi thủ tục nested_block.
• Code dòng 21: Loại bỏ giao dịch chính.
• Code dòng 22-25: In chi tiết lương của nhân viên 1001 và 1002 sau khi thực hiện các thay đổi.

Việc tăng lương cho nhân viên số 1001 không được phản ánh vì giao dịch chính đã bị hủy bỏ. Việc tăng lương cho nhân viên số 1002 được phản ánh vì khối giao dịch đó đã được tách ra và lưu lại ở cuối.

Vì vậy, bất kể việc lưu hay hủy bỏ ở giao dịch chính, các thay đổi trong giao dịch tự động đều được lưu lại mà không ảnh hưởng đến giao dịch chính.

Khi nào nên sử dụng giao dịch tự động?

Các giao dịch tự động rất mạnh mẽ, vì vậy điều quan trọng là phải biết khi nào nên sử dụng chúng. Hãy dành chúng cho những công việc phải thành công hoặc thất bại độc lập với giao dịch chính, chứ không phải cho logic nghiệp vụ cốt lõi. Các trường hợp sử dụng phổ biến bao gồm:

• Ghi nhật ký kiểm tra: Ghi lại thông tin về người đã thay đổi dữ liệu nhạy cảm, thời điểm thay đổi, cũng như giá trị cũ và giá trị mới, để nhật ký vẫn được lưu giữ ngay cả khi giao dịch chính bị hủy bỏ.
• Lỗi khi ghi nhật ký: Viết bản ghi lỗi bên trong một ngoại lệ trình xử lý và COMMIT nó, để chi tiết chẩn đoán được giữ lại trong khi giao dịch thất bại bị loại bỏ.
• Bộ đếm và số liệu thống kê: Tăng bộ đếm lượt sử dụng hoặc số lần truy cập, giá trị này phải được duy trì bất kể kết quả của người gọi như thế nào.
• COMMIT bên trong một trigger: Một trigger không thể trực tiếp phát lệnh COMMIT; giao dịch tự động là cách duy nhất được hỗ trợ để thực hiện điều đó.

Tránh sử dụng các giao dịch tự động cho các bản cập nhật thông thường mà lẽ ra phải chia sẻ số phận với giao dịch chính. Lạm dụng chúng có thể che giấu dữ liệu đằng sau các lệnh commit độc lập và làm cho việc gỡ lỗi trở nên khó khăn hơn. Theo nguyên tắc, mỗi khối tự động phải kết thúc bằng lệnh COMMIT hoặc ROLLBACK rõ ràng.

Giao dịch tự động so với giao dịch thông thường

Sự khác biệt giữa giao dịch thông thường (chính) và giao dịch tự chủ nằm ở phạm vi và tính độc lập. Bảng dưới đây so sánh chúng.

Không giống như một người bình thường khối lồng nhauTrong khi các khối có sự thay đổi luôn chia sẻ kết quả của giao dịch bao quanh, thì một khối tự chủ đứng độc lập. Hiểu được sự khác biệt này giúp bạn quyết định khi nào một khối nên độc lập và khi nào nó nên chia sẻ kết quả của giao dịch chính.

Câu Hỏi Thường Gặp