Use the RELEASE SAVEPOINT statement to delete a savepoint.
Syntax
RELEASE SAVEPOINTsavepoint-name
savepoint-name ::= user-defined-name
Remarks
RELEASE, ROLLBACK, and SAVEPOINT and are supported at the session level (outside of stored procedures) only if AUTOCOMMIT is off. Otherwise, RELEASE, ROLLBACK, and SAVEPOINT must be used within a stored procedure.
Any committed statements within a stored procedure are controlled by the outermost transaction of the calling SQL application.
Examples
The following example sets a SAVEPOINT then checks a condition to determine whether to ROLLBACK or to RELEASE the SAVEPOINT.
CREATE PROCEDURE Enroll_student( IN :student ubigint, IN :classnum INTEGER);
BEGIN
DECLARE :CurrentEnrollment INTEGER;
DECLARE :MaxEnrollment INTEGER;
SAVEPOINT SP1;
INSERT INTO Enrolls VALUES (:student,:classnum, 0.0);
SELECT COUNT(*) INTO :CurrentEnrollment FROM Enrolls WHERE class_id = :classnum;
SELECT Max_size INTO :MaxEnrollment FROM Class WHERE ID = :classnum;
IF :CurrentEnrollment >= :MaxEnrollment THEN
ROLLBACK TO SAVEPOINT SP1;
ELSE
RELEASE SAVEPOINT SP1;
END IF;
END;
Note that COUNT(expression) counts all nonnull values for an expression across a predicate. COUNT(*) counts all values, including null values.