maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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-mysql/
> 
>>
>> Best regards,
>> Jérôme.
>>
>>