Commit and savepoint in Oracle stored procedures

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;