[GitHub] [shardingsphere] FlyingZC opened a new issue, #27055: Support parsing Oracle CREATE OR sql

["FlyingZC (via GitHub)" <gi...@apache.org>]

FlyingZC opened a new issue, #27055:
URL: https://github.com/apache/shardingsphere/issues/27055

   # 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:
   ```sql
   CREATE OR REPLACE FUNCTION compute_bonus (
     emp_id NUMBER,
     bonus NUMBER
   )  RETURN NUMBER
   IS
     emp_sal NUMBER;
   BEGIN
     SELECT salary INTO emp_sal
     FROM employees
     WHERE employee_id = emp_id;
   
     RETURN emp_sal + bonus;
   END compute_bonus;
   ```
   
   ```sql
   CREATE OR REPLACE FUNCTION get_user_orders(
     schema_p   IN VARCHAR2,
     table_p    IN VARCHAR2)
    RETURN VARCHAR2
    AS
     orders_pred VARCHAR2 (400);
    BEGIN
     orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')'; 
    RETURN orders_pred;
   END;
   ```
   
   ```sql
   CREATE OR REPLACE FUNCTION f (n INTEGER)
     RETURN INTEGER
   IS
   BEGIN
     IF n = 0 THEN
       RETURN 1;
     ELSIF n = 1 THEN
       RETURN n;
     ELSE
       RETURN n*n;
     END IF;
   END;
   ```
   
   ```sql
   CREATE OR REPLACE EDITIONING VIEW Contacts AS
     SELECT
       ID                 ID,
       Name_1             Name,
       Phone_Number_1     Phone_Number
     FROM Contacts_Table;
   ```
   
   ```sql
   CREATE OR REPLACE FUNCTION obsolete_products_dml(cur cursor_pkg.strong_refcur_t,
    prod_cat varchar2 DEFAULT 'Electronics') RETURN product_t_table
   PIPELINED
   PARALLEL_ENABLE (PARTITION cur BY ANY) IS
       PRAGMA AUTONOMOUS_TRANSACTION;
       prod_id                   NUMBER(6);
       prod_name                 VARCHAR2(50);
       prod_desc                 VARCHAR2(4000);
       prod_subcategory          VARCHAR2(50);
       prod_subcategory_desc     VARCHAR2(2000);
       prod_category             VARCHAR2(50);
       prod_category_desc        VARCHAR2(2000);
       prod_weight_class         NUMBER(2);
       prod_unit_of_measure      VARCHAR2(20);
       prod_pack_size            VARCHAR2(30);
       supplier_id               NUMBER(6);
       prod_status               VARCHAR2(20);
       prod_list_price           NUMBER(8,2);
       prod_min_price            NUMBER(8,2);
       sales                     NUMBER:=0;
   BEGIN
      LOOP
        -- Fetch from cursor variable
        FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, 
     prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class,
     prod_unit_of_measure, prod_pack_size, supplier_id, prod_status,
        prod_list_price, prod_min_price;
        EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
        IF prod_status='obsolete' THEN
          IF prod_category=prod_cat THEN
             INSERT INTO obsolete_products_errors VALUES
             (prod_id, 'correction: category '||UPPER(prod_cat)||' still
      available');
             COMMIT;
          ELSE
          PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory,
    prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class,
    prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, 
    prod_list_price, prod_min_price));
          END IF;
        END IF;
      END LOOP;
      CLOSE cur;
      RETURN;
   END;
   ```
   
   # 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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] FlyingZC closed issue #27055: Support parsing Oracle CREATE OR sql

["FlyingZC (via GitHub)" <gi...@apache.org>]

FlyingZC closed issue #27055: Support parsing Oracle CREATE OR sql
URL: https://github.com/apache/shardingsphere/issues/27055


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org