Commit and savepoint in Oracle stored procedures

Source: Internet
Author: User
Tags savepoint

Oracle 11g

Create or replace procedure skeleton
IS
BEGIN
Begin
Insert into a values (10 );
Begin
Insert into a values (11 );
End;
End;
Begin
-- Savepoint ps;
Insert into a values (20 );
Commit;
End;

Begin
Insert into a values (30 );
End;
Insert into a values (40 );
-- Commit;
Rollback;
-- Rollback to ps;
END;

In oracle, begin end only serves as a flag. commit submits all the uncommitted data, regardless of the level of the begin and end, rollback cannot be rolled back either,

The savepoint and rollback can be in different begin end, And the savepoint will be invalid once it is committed.

Exec skeleton ();

In postgresql 9.0

Create or replace function skeleton () RETURNS VOID
$
BEGIN
Insert into a values (0 );
Begin
-- Savepoint ps;
Insert into a values (1 );
-- Commit;
End;

Begin
Insert into a values (2 );
End;
Insert into a values (3 );
-- Commit;
-- Rollback to ps;
-- ROLLBACK;
END;
Exception when unique_violation THEN

$ LANGUAGE plpgsql;

Stored procedures are not supported. Only functions are supported,

Rollback, commit, and savepoint are not supported in the function.

Question 1: does pg/psql functions allow "SAVEPOINT/ROLLBACK" functionality? (If so how ?)

Yes. however, you cannot use that syntax directly. you rather use it by establishing EXCEPTION clauses in BEGIN/END blocks. upon entering any BEGIN/END block which has an EXCEPTION clause, an implicit SAVEPOINT

Is executed. If any exception (read: error) is found while executing the block, the savepoint will be automatically rolled back and control passed to the EXCEPTION block.

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. functions and trigger procedures are always executed within a transaction established by an outer query-they cannot start or commit that transaction, since there wocould be no context for them to execute in. however, a block containing an EXCEPTION clause extends tively forms a subtransaction that can be rolled back without affecting the outer transaction.

Mysql

DELIMITER $

Drop procedure if exists 'A'. 'skeleton' $
Create procedure 'A'. 'skeleton '()
BEGIN
Begin
Insert into a values (10 );
Begin
Insert into a values (11 );
End;
-- Rollback;
End;

Begin
Insert into a values (20 );
-- Commit;
End;

Start transaction;
-- Savepoint ps1;
Begin
Insert into a values (30 );
End;
-- Rollback to savepoint ps1;

Insert into a values (40 );
-- Commit;
Rollback;

END $
DELIMITER;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.