Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18
Description
Add support for PL/SQL stored procedures in MariaDB.
Attachments
Activity
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.
CREATE PROCEDURE sp1 (p1 IN VARCHAR2, p2 OUT VARCHAR2)
IS
v1 VARCHAR2(100);
BEGIN
v1 := p1;
p2 := v1;
END;