Example 1: Deleting all information about an employee

    To delete all the information about an employee, you need to
    delete rows from several tables within a single transaction. This
    program fragment deletes the rows from all the result tables that
    contain information about an employee. Note that all the DELETE
    operations are included in one transaction so that no employee's
    records are only partially deleted.

           DISPLAY "Enter the ID number of employee".
           DISPLAY "whose records you want to delete:  "
              WITH NO ADVANCING.
           ACCEPT EMP-ID.

    EXEC SQL
            DECLARE TRANSACTION READ WRITE
            RESERVING EMPLOYEES      FOR PROTECTED WRITE,
                      JOB_HISTORY    FOR PROTECTED WRITE,
                      SALARY_HISTORY FOR PROTECTED WRITE,
                      DEGREES        FOR PROTECTED WRITE
    END-EXEC

    EXEC SQL
            DELETE FROM EMPLOYEES E
            WHERE E.EMPLOYEE_ID = :EMP-ID
    END-EXEC

    IF SQLCODE < 0 THEN
            EXEC SQL        ROLLBACK        END-EXEC
            GO TO ERROR-PAR
    END-IF

    EXEC SQL
            DELETE FROM JOB_HISTORY JH
            WHERE JH.EMPLOYEE_ID = :EMP-ID
    END-EXEC

    IF SQLCODE < 0 THEN
            EXEC SQL        ROLLBACK        END-EXEC
            GO TO ERROR-PAR
    END-IF

    EXEC SQL
            DELETE FROM SALARY_HISTORY SH
            WHERE SH.EMPLOYEE_ID = :EMP-ID
    END-EXEC

    IF SQLCODE < 0 THEN
            EXEC SQL        ROLLBACK        END-EXEC
            GO TO ERROR-PAR
    END-IF

    EXEC SQL
            DELETE FROM DEGREES D
            WHERE D.EMPLOYEE_ID = :EMP-ID
    END-EXEC

    IF SQLCODE < 0 THEN
            EXEC SQL        ROLLBACK        END-EXEC
            GO TO ERROR-PAR
    END-IF

    Example 2: Deleting selected rows from a table

    The following statement deletes all rows from the EMPLOYEES table
    where the employee SALARY_AMOUNT is greater than $75,000. The
    EMPLOYEES and SALARY_HISTORY tables are both in the database with
    the alias PERS.

    SQL> ATTACH 'ALIAS PERS FILENAME personnel';
    SQL> DELETE FROM PERS.EMPLOYEES E
    cont> WHERE EXISTS ( SELECT *
    cont>                FROM   PERS.SALARY_HISTORY S
    cont>                WHERE  S.EMPLOYEE_ID = E.EMPLOYEE_ID
    cont>                AND    S.SALARY_AMOUNT > 75000
    cont>              ) ;
    7 rows deleted

    Example 3: Deleting rows from a table specifying an outline name

    The following example shows the syntax used to define the DEL_
    EMP_75000 outline:

    SQL> CREATE OUTLINE DEL_EMP_75000
    cont>  FROM
    cont>     (DELETE FROM EMPLOYEES E
    cont>      WHERE EXISTS ( SELECT *
    cont>                FROM   SALARY_HISTORY S
    cont>                WHERE  S.EMPLOYEE_ID = E.EMPLOYEE_ID
    cont>                AND    S.SALARY_AMOUNT > 75000
    cont>              );

    The following query specifies the DEL_EMP_75000 outline:

    SQL> DELETE FROM EMPLOYEES E
    cont> WHERE EXISTS ( SELECT *
    cont>                FROM   SALARY_HISTORY S
    cont>                WHERE  S.EMPLOYEE_ID = E.EMPLOYEE_ID
    cont>                AND    S.SALARY_AMOUNT > 75000
    cont>              )
    cont> OPTIMIZE USING DEL_EMP_75000;
    ~S: Outline DEL_EMP_75000 used
       .
       .
       .
    7 rows deleted