样本数据：

SQL> set ver off
SQL> undefine par_ename
SQL> select * from employee order by deptno, ename;

    DEPTNO ENAME      JOB              SAL
---------- ---------- --------- ----------
        10 CLARK      MANAGER         2450  --> department 10: if KING is a parameter,
        10 KING       PRESIDENT       5001  --> then CLARK and MILLER will be
        10 MILLER     CLERK           1300  --> deleted, but KING won't
        20 ADAMS      CLERK           1100
        20 FORD       ANALYST         3000
        20 JONES      MANAGER         2975
        20 SCOTT      ANALYST         3000
        20 SMITH      CLERK           1000
        30 ALLEN      SALESMAN        1600
        30 BLAKE      MANAGER         2850
        30 JAMES      CLERK            950
        30 MARTIN     SALESMAN        1250
        30 TURNER     SALESMAN        1500
        30 WARD       SALESMAN        1250

14 rows selected.

查询：

SQL> delete from employee e
  2    where e.deptno = (select a.deptno
  3                      from employee a
  4                      where a.ename = '&&par_ename'
  5                     )
  6      and e.ename <> '&&par_ename';
Enter value for par_ename: KING

2 rows deleted.

结果：

SQL> select * from employee order by deptno, ename;

    DEPTNO ENAME      JOB              SAL
---------- ---------- --------- ----------
        10 KING       PRESIDENT       5001  --> only KING remains in dept. 10
        20 ADAMS      CLERK           1100
        20 FORD       ANALYST         3000
        20 JONES      MANAGER         2975
        20 SCOTT      ANALYST         3000
        20 SMITH      CLERK           1000
        30 ALLEN      SALESMAN        1600
        30 BLAKE      MANAGER         2850
        30 JAMES      CLERK            950
        30 MARTIN     SALESMAN        1250
        30 TURNER     SALESMAN        1500
        30 WARD       SALESMAN        1250

12 rows selected.

SQL>