Syntax difference:

1. [DONE] Labels

MariaDB:

label:

Oracle:

<<label>>

2. [DONE] Different order of IN, OUT, INOUT keywords

MariaDB:

CREATE PROCEDURE p1 (OUT param INT)

Oracle:

CREATE PROCEDURE p1 (param OUT INT)

3. [DEFERRED] IN, OUT, INOUT in CREATE FUNCTION.

See MDEV-10654.

4. [DONE] AS/IS keyword before a function or a procedure body

Oracle requires AS or IS keyword before the body:

CREATE FUNCTION f1 RETURN NUMBER

AS

BEGIN

  RETURN 10;

END; 

CREATE PROCEDURE sp1 (p1 IN VARCHAR2(20), p2 OUT VARCHAR2(30))

IS

 BEGIN

 END

5. [DONE] EXIT statement

Oracle supports this syntax to leave a loop block:

EXIT [ label ] [ WHEN bool_expr ]

label is optional.
The WHEN clause is optional.

EXIT label WHEN bool_expr

is similar to MariaDB syntax:

IF bool_expr THEN LEAVE label

Note, unlike LEAVE, EXIT is valid only inside a loop block. This definition is not valid:

CREATE PROCEDURE p1

AS

BEGIN;

  EXIT;

END;

because EXIT is not inside a loop.

6. [DONE] Assignment operator

MariaDB:

SET var= 10;

Oracle:

var:= 10;

Assignment operator will work for system variables:

max_sort_length:=1025;

7. [DONE] Variable declarations

In MariaDB, the DECLARE statements are listed inside BEGIN..END blocks. One block can have multiple DECLARE statements:

BEGIN

  DECLARE a INT;

  DECLARE b VARCHAR(10);

  v= 10;

END;

In Oracle, DECLARE is a special optional section in the beginning of a DECLARE .. BEGIN .. END block. One block can have only one DECLARE section, with multiple declarations.
If a block does not need any declarations, then the DECLARE section is omitted.
If the DECLARE section presents, then at least one declaration is required.

DECLARE 

  a INT;

  b VARCHAR(10);

BEGIN

  v:= 10;

END;

In the top level block variables are declared directly after the "AS" keyword:

CREATE PROCEDURE p1()

AS

  a INT;

  b VARCHAR(10);

BEGIN

  v:= 10;

END;

8. [DEREFFED] Anonymous blocks

See MDEV-10655.

9. [DONE] EXCEPTION handlers

MariaDB uses DECLARE HANDLER to catch exceptions:

BEGIN

  DECLARE EXIT HANDLER FOR SQLEXCEPTION

  BEGIN

  ..

  END;

END;

In Oracle, exception handlers are declared in the end of a block:

BEGIN

  ...

EXCEPTION

  WHEN OTHERS THEN

  BEGIN ..

  END;

END;

OTHERS should catch all exceptions, including warnings. For example, although NO_DATA_FOUND is more like a warning than an error, it should still be handled by OTHERS.

DROP TABLE t1;

DROP FUNCTION f1;

CREATE TABLE t1 (a INT);

CREATE FUNCTION f1 RETURN VARCHAR

AS

  a INT:=10;

BEGIN

  SELECT a INTO a FROM t1;

  RETURN 'OK';

EXCEPTION

  WHEN OTHERS THEN RETURN 'Exception';

END;

/

SHOW ERRORS;

SELECT f1 FROM DUAL;

F1

---------

Exception

10 [DONE] Default variable value: x INT := 10;

In addition to the DEFAULT keyword, Oracle supports the := operator to assign a default value to a variable:

a NUMBER(10) := 10;

11. [DONE] NULL as a statement

Oracle does not support empty blocks with BEGIN immediately followed by END.
There must be at least one statement.
NULL is a valid statement. BEGIN NULL; END; is actively used in Oracle:

DROP PROCEDURE p1;

CREATE PROCEDURE p1() AS

BEGIN

  NULL;

END;

/

NULL can appear in other syntactic constructs as a statement:

DROP PROCEDURE p1;

CREATE PROCEDURE p1() AS

  a INT:=10;

BEGIN

  IF a=10 THEN NULL; ELSE NULL; END IF;

END;

/

12. [DONE] No parentheses if no arguments

If a function or a procedure has no parameters, then parentheses must be omitted:

DROP PROCEDURE p1;

CREATE PROCEDURE p1 AS

BEGIN

  NULL;

END;

/

13. [DONE] RETURN vs RETURNS

Oracle uses RETURN rather than RETURNS:

CREATE FUNCTION f1(a INT) RETURN INT ...

14. [DONE] IN OUT instead of INOUT

Instead of INOUT, Oracle uses two separate keywords: IN followed by OUT in an SP parameter declaration:

CREATE PROCEDURE p1 (a IN OUT INT)

AS

BEGIN

END;

15. [DONE] ELSIF vs ELSEIF

DROP FUNCTION f1;

CREATE FUNCTION f1(a INT) RETURN VARCHAR

AS

BEGIN

  IF a=1 THEN RETURN 'a is 1';

  ELSIF a=2 THEN RETURN 'a is 2';

  ELSE RETURN 'a is unknown';

  END IF;

END;

/

SELECT f1(1) FROM DUAL;

F1(1)

--------------------------------------------------------------------------------

a is 1

16. [DONE] Cursor declaration

MariaDB:

DECLARE cr CURSOR FOR SELECT * FROM t1;

Oracle:

CURSOR cr IS SELECT * FROM t1;

Example:

DROP TABLE t1;

CREATE TABLE t1 (a INT);

DECLARE

  CURSOR cr IS SELECT * FROM t1;

BEGIN

  NULL;

END;

/

17. [DONE] RETURN in stored procedures

MariaDB supports RETURN only in stored functions.
Oracle supports RETURN in stored procedures as well, including the EXCEPTION section:

DROP PROCEDURE p1;

CREATE PROCEDURE p1 (a IN OUT INT)

AS

BEGIN

  IF a < 10 THEN

    RETURN;

  END IF;

  a:=a+1;

EXCEPTION

  WHEN OTHERS THEN RETURN;

END;

/

18. [DONE] WHILE syntax

MariaDB:

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

Oracle

[<<label>>]

WHILE boolean_expression

  LOOP statement... END LOOP [ label ] ;

19. [DONE] CONTINUE statement

CONTINUE [ label ] [ WHEN boolean_expression ] ;

This is a replacement for the ITERATE statement in MariaDB.
CONTINUE is valid only inside a LOOP.

DBS Test cases MDEV-10411.txt