A SAVEPOINT Example

The following code sample is a simple example of how to use savepoints:

CREATE PROCEDURE add_emp_proj2 (emp_no SMALLINT, emp_name VARCHAR(20), proj_id CHAR(5)) AS
BEGIN
BEGIN
SAVEPOINT EMP_PROJ_INSERT;
INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no,:proj_id);
WHEN SQLCODE -530 DO
BEGIN
ROLLBACK TO SAVEPOINT EMP_PROJ_INSERT;
EXCEPTION unknown_emp_id;
END
END
SUSPEND;
END;