imzhangds opened a new pull request, #30729:
URL: https://github.com/apache/shardingsphere/pull/30729
Fixes #27069.
Changes proposed in this pull request:
- Enhanced support for Oracle SQL parsing, fixed some issues with SQL
parsing failure.
- Added test cases for these SQL statements, and all tests passed
successfully when running the test suite.
### Supported SQL cases are as follows
**case1**
```sql
CREATE OR REPLACE TRIGGER check_user
AFTER LOGON ON DATABASE
BEGIN
check_user;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
```
**case2**
```sql
CREATE OR REPLACE TRIGGER dept_cascade3
AFTER UPDATE OF Deptno ON dept
BEGIN UPDATE emp
SET Update_id = NULL
WHERE Update_id = Integritypackage.Updateseq;
END;
```
**case3**
```sql
CREATE OR REPLACE TRIGGER dept_del_cascade
AFTER DELETE ON dept
FOR EACH ROW
-- Before row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from dept table:
BEGIN
DELETE FROM emp
WHERE emp.Deptno = :OLD.Deptno;
END;
```
**case4**
```sql
CREATE OR REPLACE TRIGGER dept_cascade2
AFTER DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- For each department number in dept that is updated,
-- cascade update to dependent foreign keys in emp table.
-- Cascade update only if child row was not updated by this trigger:
BEGIN
IF UPDATING THEN
UPDATE emp
SET Deptno = :NEW.Deptno,
Update_id = Integritypackage.Updateseq --from 1st
WHERE emp.Deptno = :OLD.Deptno
AND Update_id IS NULL;
/* Only NULL if not updated by 3rd trigger
fired by same triggering statement */
END IF;
IF DELETING THEN
-- After row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from dept table:
DELETE FROM emp
WHERE emp.Deptno = :OLD.Deptno;
END IF;
END;
```
**case5**
```sql
CREATE OR REPLACE TRIGGER dept_restrict
BEFORE DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- Before row is deleted from dept or primary key (DEPTNO) of dept is
updated,
-- check for dependent foreign key values in emp;
-- if any are found, roll back.
DECLARE
Dummy INTEGER; -- Use for cursor fetch
Employees_present EXCEPTION;
employees_not_present EXCEPTION;
-- Cursor used to check for dependent foreign key values.
CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM emp WHERE Deptno = Dn;
BEGIN
OPEN Dummy_cursor (:OLD.Deptno);
FETCH Dummy_cursor INTO Dummy;
-- If dependent foreign key is found, raise user-specified
-- error code and message. If not found, close cursor
-- before allowing triggering statement to complete.
IF Dummy_cursor%FOUND THEN
RAISE Employees_present; -- Dependent rows exist
ELSE
RAISE Employees_not_present; -- No dependent rows exist
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN Employees_present THEN
CLOSE Dummy_cursor;
Raise_application_error(-20001, 'Employees Present in'
|| ' Department ' || TO_CHAR(:OLD.DEPTNO));
WHEN Employees_not_present THEN
CLOSE Dummy_cursor;
END;
```
---
Before committing this PR, I'm sure that I have checked the following
options:
- [x] My code follows the [code of
conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/)
of this project.
- [x] I have self-reviewed the commit code.
- [ ] I have (or in comment I request) added corresponding labels for the
pull request.
- [x] I have passed maven check locally : `./mvnw clean install -B -T1C
-Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
- [ ] I have made corresponding changes to the documentation.
- [x] I have added corresponding unit tests for my changes.
--
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]
