maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Hello Alexander,
Thanks for these news and sub-task !

Yes, it commit top-level transaction (as long as you are not in an autonomous transaction).

The main difference between Mariadb and Oracle transaction is that Oracle implicitly starts a transaction when the first SQL statement (which need to hold locks) is encountered (MDEV-10572)
Pays attention to "autonomous transaction" (there is MDEV-10155 for this task). 

Take a look at https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT016

Jérôme.

> -----Message d'origine-----
> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
> Envoyé : jeudi 9 février 2017 13:54
> À : jerome brauge; MariaDB Developers (maria-
> developers@xxxxxxxxxxxxxxxxxxx)
> Objet : Re: bb-10.2-compatibility
> 
> Hello Jerome,
> 
> 
> On 02/09/2017 04:44 PM, Alexander Barkov wrote:
> > Hello Jerome,
> >
> >
> > On 02/09/2017 02:54 PM, jerome brauge wrote:
> >> Hello,
> >>
> >> I come back with a question on functions.
> >>
> >> On Oracle, there is only one difference between a function and a stored
> procedure : the return code.
> >>
> >> On Mariadb, stored functions are more limited :
> >>  - Recursive stored functions are not allowed
> >>  - Explicit or implicit commit is not allowed in stored function
> >>  - in out parameter are not allowed (MDEV-10654)
> >>  - no dynamic sql (execute immediate)
> >
> > Thanks for bringing this up!
> >
> > Gnerally the intent is to be as compatible with Oracle as possible
> > when running with sql_mode=ORACLE.
> > So we should definitely implement this eventually.
> >
> > I have created these sub-tasks under "MDEV-10764 PL/SQL parser - Phase
> 2":
> >
> > MDEV-12032 sql_mode=ORACLE: recursive stored functions
> > MDEV-12033 sql_mode=ORACLE: transactions in stored functions
> 
> By the way, how does COMMIT inside stored functions and procedures
> work?
> Does it commit the top-level transaction? Or does it commit some sort of
> sub-transaction?
> 
> 
> > MDEV-12034 Dynamic SQL in stored functions
> >
> > But I can't give a precise time frame estimation when we'll be able to
> > work on this.
> >
> > Currently we're working on MDEV-10142 and its sub-tasks.
> > There are still 15 out of 42 sub-tasks to be done.
> > But some subtasks will probably move from MDEV-10142 (phase#1) to
> > MDEV-10764 (phase#2).
> >
> >
> >>
> >> Have you planned to remove these limits?
> >>
> >> If not, we'll have to find the best workarounds to use stored
> >> procedures (and find a solution to mimic a return code)
> >
> > Right, workarounds with wrapping a procedure into a function are
> > possible. For example, I found this page:
> >
> > http://ethellenterprises.com/2011/05/recursive-stored-functions-in-mys
> > ql/
> >
> >>
> >> Best regards,
> >> Jérôme.
> >>
> >>