Support parsing Oracle CREATE OR sql

[FlyingZC]

Background

Hi community, This issue is for #26878.

ShardingSphere parser engine helps users parse a SQL to get the AST (Abstract Syntax Tree) and visit this tree to get SQLStatement (Java Object). Currently, we are planning to enhance the support for Oracle SQL parsing in ShardingSphere.

More details: https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/

Task

This issue is to support more oracle sql parse, as follows:

CREATE OR REPLACE PACKAGE BODY p IS
  FUNCTION f (
    n NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    java_sleep(n);
     RETURN n;
  END f;
END p;

CREATE OR REPLACE PRIVATE OUTLINE my_salaries
   FROM salaries;

CREATE OR REPLACE PACKAGE aa_pkg IS
  TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15);
END;

CREATE OR REPLACE TRIGGER Contacts_Fwd_Xed
  BEFORE INSERT OR UPDATE ON Contacts_Table
  FOR EACH ROW
  FORWARD CROSSEDITION
  DISABLE
BEGIN
  Set_First_And_Last_Name(
    :NEW.Name_1,
    :NEW.First_Name_2,
    :NEW.Last_Name_2
  );
  Set_Country_Code_And_Phone_No(
    :NEW.Phone_Number_1,
    :NEW.Country_Code_2,
    :NEW.Phone_Number_2
  );
END Contacts_Fwd_Xed;

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
  FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
  Ten_Percent                 CONSTANT NUMBER := 0.1;
  TYPE Salaries_t             IS TABLE OF Employees.Salary%TYPE;
  Avg_Salaries                Salaries_t;
  TYPE Department_IDs_t       IS TABLE OF Employees.Department_ID%TYPE;
  Department_IDs              Department_IDs_t;

  -- Declare collection type and variable:

  TYPE Department_Salaries_t  IS TABLE OF Employees.Salary%TYPE
                                INDEX BY VARCHAR2(80);
  Department_Avg_Salaries     Department_Salaries_t;

  BEFORE STATEMENT IS
  BEGIN
    SELECT               AVG(e.Salary), NVL(e.Department_ID, -1)
      BULK COLLECT INTO  Avg_Salaries, Department_IDs
      FROM               Employees e
      GROUP BY           e.Department_ID;
    FOR j IN 1..Department_IDs.COUNT() LOOP
      Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
    END LOOP;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.Salary - :Old.Salary >
      Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
    THEN
      Raise_Application_Error(-20000, 'Raise too big');
    END IF;
  END AFTER EACH ROW;
END Check_Employee_Salary_Raise;

Process

1. First confirm that this is a correct oracle sql syntax, if not please ignore;
2. Compare SQL definitions in Oficial SQL Doc and ShardingSphere SQL Doc;
3. If there is any difference in ShardingSphere SQL Doc, please correct them by referring to the Official SQL Doc;
4. Run mvn install the current_file_module;
5. Check whether there are any exceptions. If indeed, please fix them. (Especially xxxVisitor.class);
6. Add new corresponding SQL case in SQL Cases and expected parsed result in Expected Statment XML;
7. Run SQLParserParameterizedTest to make sure no exceptions.

Relevant Skills

1. Master JAVA language
2. Have a basic understanding of Antlr g4 file
3. Be familiar with Oracle SQLs

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[zzzk1]

Hi, @strongduanmu pls assign this issue to me.

[strongduanmu]

@zzzk1 Good job, I will assign it to you. Besides, can you send your wechat number to me? I might be able to help you more.

[zzzk1]

@zzzk1 Good job, I will assign it to you. Besides, can you send your wechat number to me? I might be able to help you more. sure, pls add my wechat at1742694934

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.