FlyingZC opened a new issue, #27060: URL: https://github.com/apache/shardingsphere/issues/27060
# 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 ret_warehouse_typ(x warehouse_typ) RETURN warehouse_typ IS BEGIN RETURN x; END; ``` ```sql CREATE OR REPLACE FUNCTION get_product_name_1 (prod_id NUMBER, lang_id VARCHAR2) RETURN NVARCHAR2 RESULT_CACHE IS result VARCHAR2(50); BEGIN SELECT translated_name INTO result FROM Product_Descriptions WHERE PRODUCT_ID = prod_id AND LANGUAGE_ID = lang_id; RETURN result; END; ``` ```sql CREATE OR REPLACE FUNCTION hide_sal_comm ( v_schema IN VARCHAR2, v_objname IN VARCHAR2) RETURN VARCHAR2 AS con VARCHAR2 (200); BEGIN con := 'deptno=30'; RETURN (con); END hide_sal_comm; ``` ```sql CREATE OR REPLACE PACKAGE BODY pkg1 AS FUNCTION f1 (p1 NUMBER) RETURN t1 IS n NUMBER; BEGIN n := m; -- Unqualified variable name n := pkg1.m; -- Variable name qualified by package name n := pkg1.f1.p1; -- Parameter name qualified by function name, -- which is qualified by package name n := v1.a; -- Variable name followed by component name n := pkg1.v1.a; -- Variable name qualified by package name -- and followed by component name n := v2(10).a; -- Indexed name followed by component name n := f1(10).a; -- Function invocation followed by component name n := f2(10)(10).a; -- Function invocation followed by indexed name -- and followed by component name n := hr.pkg1.f2(10)(10).a; -- Schema name, package name, -- function invocation, index, component name v1.a := p1; RETURN v1; END f1; FUNCTION f2 (q1 NUMBER) RETURN t2 IS v_t1 t1; v_t2 t2; BEGIN v_t1.a := q1; v_t2(1) := v_t1; RETURN v_t2; END f2; END pkg1; ``` ```sql CREATE OR REPLACE FUNCTION session_and_current_editions RETURN VARCHAR2 IS BEGIN RETURN 'Session: '|| SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') || ' / ' || 'Current: '|| SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME'); END session_and_current_editions; ``` # 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]
