Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10411

sql_mode=ORACLE: Providing compatibility for basic PL/SQL constructs

Details

    • 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

          CREATE PROCEDURE sp1 (p1 IN VARCHAR2, p2 OUT VARCHAR2)
          IS
            v1 VARCHAR2(100);
          BEGIN
            v1 := p1;
            p2 := v1;
          END;
          

          dmtolpeko Dmitry Tolpeko added a comment - CREATE PROCEDURE sp1 (p1 IN VARCHAR2, p2 OUT VARCHAR2) IS v1 VARCHAR2(100); BEGIN v1 := p1; p2 := v1; END;
          bar Alexander Barkov added a comment - - edited

          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.

          bar Alexander Barkov added a comment - - edited 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 .
          Krishnadas Krishnadas added a comment -

          DBS Test cases MDEV-10411.txt

          Krishnadas Krishnadas added a comment - DBS Test cases MDEV-10411.txt

          People

            bar Alexander Barkov
            dmtolpeko Dmitry Tolpeko
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.