Discussion :

[Gugelhupf]

Bonjour,


N'ayant pas Oracle à disposition, je souhaiterais vous demander confirmation concernant le fait si une function ou procedure Oracle est considérée comme un bloc transactionnel.

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE FUNCTION ma_fonction_toto()
RETURN NUMBER
AS
BEGIN
 
    INSERT INTO ma_table values(1);
    RETURN 1 + 1 + 1;
 
END;
 
CREATE FUNCTION ma_fonction_titi()
RETURN NUMBER
AS
    val number;
BEGIN   
    val := ma_fonction_toto(); 
    ROLLBACK;
    RETURN val;
END;

Je souhaiterais donc savoir si par exemple, en exécutant la ligne "val := ma_fonction_toto();" dans ma_fonction_titi j'ai effectué une transaction (transaction imbriquée) ?
Est-ce que mon INSERT dans ma_fonction_toto a bien eu le ROLLBACK, ou bien y a-t-il eu un COMMIT au niveau du END dans ma_fonction_toto ?


Merci

[mnitu]

Pour faire persister/abandonner les modifications vous devez exécuter COMMIT/Rollback d'une manière explicite. Les instruction DDL valident d'une manière implicite. Certaines environnements de développement exécutent les instruction SQL/PL/SQL en mode auto-commit par défaut ( à désactiver).

[Gugelhupf]

Bonjour,


Considérons que nous soyons dans un environnement standard Oracle (auto-commit à false).

Mais que nous soyons en auto-commit à false ou à true, ça ne devrait pas changer le comportement des function/procedure Oracle.

En fait, si je pose cette question, c'est parce qu'avec PostgreSQL, une function encapsule implicitement une transaction (donc effectuer un rollback après l'appel d'une fonction qui a effectué des requêtes DML est inutile), mais pas SQL Server (on peut faire un rollback).

Oracle se comporte plus comme PostgreSQL, ou SQL Server ?


Cordialement,

[mnitu]

Premier point: une fonction/procédure en PL/SQL ne devrait jamais effectuer ni commit ni rollback (il y a toujours quelques exceptions mais peu importe; ex : les procédures qui font du log via une transaction autonome)! La raison est simple : la procédure ne connait rien des étendues de la transaction à laquelle elle participe.

Deuxième point : une procédure/fonction en PL/SQL qui échoue à cause d’une exception effectue un rollback implicite au niveau de l’environnement appelant parce que l’exécution de la procédure est considère comme une instruction, qui pour Oracle doit être consistante. Donc dans ce cas nul besoin d’effectuer un rollback.

[Gugelhupf]

Merci pour votre réponse @mnitu.

Le souci c'est que j'ai toujours du mal à voir la portée des transactions avec les fonctions/procédures Oracle.

Exemple de comportement PostgreSQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE FUNCTION ma_fonction_toto()
RETURN NUMBER
AS
BEGIN --> équivalent à START TRANSACTION (nouvelle transaction)
 
    INSERT INTO ma_table VALUES(1);
    RETURN 1 + 1 + 1;
 
END; --> équivalent à COMMIT (fin de la nouvelle transaction)

Exemple de comportement SQL Server :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE FUNCTION ma_fonction_toto()
RETURN NUMBER
AS
BEGIN --> on récupère la transaction en cours, au dessus de la fonction appelante ma_fonction_toto()
 
    INSERT INTO ma_table VALUES(1);
    RETURN 1 + 1 + 1;
 
END; --> on est toujours la même transaction, et l'identifiant de la transaction en sortie de ma_fonction_toto() sera le même

Dans le cas de PostgreSQL, je ne pourrais pas revenir en arrière une fois avoir appelé ma_fonction_toto(), mais avec SQL Server si.

Donc pour faire plus simple, et par rapport aux exemples que je montre au-dessus :
Est-ce qu'une fonction/procédure Oracle se comporte plus comme PostgreSQL, ou SQL Server ?


Cordialement,

[pachot]

Bonjour,
Pour faire simple, une procédure Oracle s'exécute dans une transaction existante. Il n'y a pas de début ou fin de transaction implicite.
Il n'y a que si elle fait un commit ou rollback (ou un commit implicite en faisant du DDL) qu'elle termine une transaction.
Cordialement,
Franck.

[mnitu]

...

Donc pour faire plus simple, et par rapport aux exemples que je montre au-dessus :
Est-ce qu'une fonction/procédure Oracle se comporte plus comme PostgreSQL, ou SQL Server ?
...

"Par rapport aux exemples que vous avez fournis:"
Une procédure Oracle déclaré en transaction autonome (pragma autonomous transaction) se comporte comme PostgreSql. Mais d'une manière générale les procédures se comportent comme dans SQL Server.

[Gugelhupf]

Merci pour cette réponse concise @mnitu !

Je vais essayer de trouver un SGBDR Oracle et m'exercer un peu.

J'aurais juste une dernière question concernant les instructions DDL (ex: Truncate).

En faite avec Oracle, les instructions DDL ne font pas parti intégrante d'une transaction, ainsi un truncate est équivalent à un delete dans une nouvelle transaction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
START TRANSACTION;
 
    INSERT INTO ...
 
    BEGIN;
        DELETE FROM ma_table;
    COMMIT;
 
    INSERT INTO ...
 
COMMIT;

Est équivalent à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
START TRANSACTION;
 
    INSERT INTO ...
 
    TRUNCATE TABLE ma_table;
 
    INSERT INTO ...
 
COMMIT;

N'est-ce pas ? Donc si je fais un CREATE TABLE et un ROLLBACK, toutes les instructions DML seront annulés, sauf les DDL ?

[mnitu]

Les DDL font un commit implicite avant et après leur exécution et par conséquence ils terminent une transaction et démarrent une autre.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
(Start transaction T1)
  insert ...
  ...
  delete ...
  ...
  truncate -- T1 est finie avec commit
(Start transaction T2)
  insert ..
  ...
  delete ...
  Rollback -- seulement la T2 est annulée

[Gugelhupf]

Merci @mnitu

EDIT: J'en profite pour mettre la source officielle que j'ai trouvé :

Oracle Database implicitly commits the current transaction before and after every DDL statement.

Source : http://docs.oracle.com/cd/E11882_01/...htm#SQLRF30041