FlyingZC opened a new issue, #27059: URL: https://github.com/apache/shardingsphere/issues/27059
# 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 home_phones(allphones IN phone_ntabtyp) RETURN phone_ntabtyp IS homephones phone_ntabtyp := phone_ntabtyp(); indx1 number; indx2 number := 0; BEGIN FOR indx1 IN 1..allphones.count LOOP IF allphones(indx1).location = 'home' THEN homephones.extend; -- extend the local collection indx2 := indx2 + 1; homephones(indx2) := allphones(indx1); END IF; END LOOP; RETURN homephones; END; ``` ```sql CREATE OR REPLACE FUNCTION get_product_name_2 (prod_id NUMBER, lang_id VARCHAR2) RETURN NVARCHAR2 IS TYPE product_names IS TABLE OF NVARCHAR2(50) INDEX BY PLS_INTEGER; FUNCTION all_product_names (lang_id NUMBER) RETURN product_names RESULT_CACHE IS all_names product_names; BEGIN FOR c IN (SELECT * FROM Product_Descriptions WHERE LANGUAGE_ID = lang_id) LOOP all_names(c.PRODUCT_ID) := c.TRANSLATED_NAME; END LOOP; RETURN all_names; END; BEGIN RETURN all_product_names(lang_id)(prod_id); END; ``` ```sql CREATE OR REPLACE FUNCTION obsolete_products(cur cursor_pkg.refcur_t) RETURN product_t_table IS 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; objset product_t_table := product_t_table(); i 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 -- Category Electronics is not meant to be obsolete and will be suppressed IF prod_status='obsolete' AND prod_category != 'Electronics' THEN -- append to collection i:=i+1; objset.extend; objset(i):=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 LOOP; CLOSE cur; RETURN objset; END; ``` ```sql CREATE OR REPLACE FUNCTION get_param_value ( param_name VARCHAR, appctx VARCHAR DEFAULT SYS_CONTEXT('Config', 'App_ID') ) RETURN VARCHAR RESULT_CACHE IS rec VARCHAR(2000); BEGIN SELECT val INTO rec FROM config_tab WHERE name = param_name; RETURN rec; END; ``` ```sql CREATE OR REPLACE PACKAGE BODY My_Types IS FUNCTION Init_My_Rec RETURN My_Rec IS Rec My_Rec; BEGIN Rec.a := 0; Rec.b := 1; RETURN Rec; END Init_My_Rec; END My_Types; ``` # 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
