FlyingZC opened a new issue, #27071: URL: https://github.com/apache/shardingsphere/issues/27071
# 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 TRIGGER my_trigger AFTER CREATE ON DATABASE BEGIN NULL; END; ``` ```sql CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; FUNCTION Init_My_AA RETURN My_AA; END My_Types; ``` ```sql CREATE OR REPLACE TRIGGER emp_dept_check BEFORE INSERT OR UPDATE OF Deptno ON emp FOR EACH ROW WHEN (NEW.Deptno IS NOT NULL) -- Before row is inserted or DEPTNO is updated in emp table, -- fire this trigger to verify that new foreign key value (DEPTNO) -- is present in dept table. DECLARE Dummy INTEGER; -- Use for cursor fetch Invalid_department EXCEPTION; Valid_department EXCEPTION; Mutating_table EXCEPTION; PRAGMA EXCEPTION_INIT (Mutating_table, -4091); -- Cursor used to verify parent key value exists. -- If present, lock parent key's row so it cannot be deleted -- by another transaction until this transaction is -- committed or rolled back. CURSOR Dummy_cursor (Dn NUMBER) IS SELECT Deptno FROM dept WHERE Deptno = Dn FOR UPDATE OF Deptno; BEGIN OPEN Dummy_cursor (:NEW.Deptno); FETCH Dummy_cursor INTO Dummy; -- Verify parent key. -- If not found, raise user-specified error code and message. -- If found, close cursor before allowing triggering statement to complete: IF Dummy_cursor%NOTFOUND THEN RAISE Invalid_department; ELSE RAISE valid_department; END IF; CLOSE Dummy_cursor; EXCEPTION WHEN Invalid_department THEN CLOSE Dummy_cursor; Raise_application_error(-20000, 'Invalid Department' || ' Number' || TO_CHAR(:NEW.deptno)); WHEN Valid_department THEN CLOSE Dummy_cursor; WHEN Mutating_table THEN NULL; END; ``` ```sql CREATE OR REPLACE TRIGGER print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON employees FOR EACH ROW WHEN (NEW.job_id <> 'AD_PRES') -- do not print information about President DECLARE sal_diff NUMBER; BEGIN sal_diff := :NEW.salary - :OLD.salary; DBMS_OUTPUT.PUT(:NEW.last_name || ': '); DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', '); DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', '); DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff); END; ``` ```sql CREATE OR REPLACE TRIGGER salary_check BEFORE INSERT OR UPDATE OF Sal, Job ON Emp FOR EACH ROW DECLARE Minsal NUMBER; Maxsal NUMBER; Salary_out_of_range EXCEPTION; BEGIN /* Retrieve minimum & maximum salary for employee's new job classification from SALGRADE table into MINSAL and MAXSAL: */ SELECT Minsal, Maxsal INTO Minsal, Maxsal FROM Salgrade WHERE Job_classification = :NEW.Job; /* If employee's new salary is less than or greater than job classification's limits, raise exception. Exception message is returned and pending INSERT or UPDATE statement that fired the trigger is rolled back: */ IF (:NEW.Sal < Minsal OR :NEW.Sal > Maxsal) THEN RAISE Salary_out_of_range; END IF; EXCEPTION WHEN Salary_out_of_range THEN Raise_application_error ( -20300, 'Salary '|| TO_CHAR(:NEW.Sal) ||' out of range for ' || 'job classification ' ||:NEW.Job ||' for employee ' || :NEW.Ename ); WHEN NO_DATA_FOUND THEN Raise_application_error(-20322, 'Invalid Job Classification'); 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
