一、概述
在一个SQL语句中嵌套另一个SQL语句成为子查询。包括单行子查询，多行子查询，多列子查询。
注意，当在DDL语句中引用子查询时，可以带有Order By子句；但是当在where子句、Set子句中引用子查询时，不能带有Order by 子句。子查询具有以下一些作用：
通过在insert或create table语句中使用子查询，可以将源表数据插入目标表中。 通过在create view或create materialieed view中使用子查询，可以定义视图或实体化视图所对应的select语句。 通过在update语句中使用子查询可以修改一列或多列数据。 通过在where、having、start with子句中使用子查询，可以提供条件值。非相关（非关联）子查询的使用：
FROM语句可以包含任意类型的非关联子查询。 SELECT和ORDER BY可以包含标量子查询。 GROUP BY语句不能包含子查询。 SHART WITH和CONNECT BY语句可以包含子查询。 WITH语句包含一个命名的非关联子查询，它可以被父查询引用多次而只执行一次。 MERGE的USING语句可以包含非关联子查询。 UPDATE的SET语句可以包含标量子查询或者单行多列子查询 INSERT语句可以在VALUES的位置包含标量子查询。
1、单行子查询(子查询只返回一行)
又称为标量子查询，因为它的结果用作父查询的标量。通常与比较运算符比如 = 、 > 、 < 、 != 、 <= 、 >= 联合使用。
|
1 2 |
--查询scott用户下的emp表中工资比scott高的员工的信息(此操作中的子查询只返回一行记录) select * from emp where sal>( select sal from emp where ename= 'SCOTT' ); |
2、多行单列子查询（子查询返回多行）
使用特定的关键字如IN，ANY和ALL来将外层查询的单个值与子查询的多行进行比较运算。
（1）子查询出现在where子句中的子查询语法（内嵌子查询）
|
1 2 |
--查询scott用户下的emp表中所有的经理的信息(此操作子查询会返回多行记录) select * from emp where empno in ( select mgr from emp); |
（2）子查询出现在from子句中（内嵌视图）
|
1 2 |
--将scott用户下的emp表中查询出的数据作为一个内嵌视图在FROM子句中使用 select * from ( select empno,ename,job,sal from emp); |
（3）子查询出现在select列表中，此时子查询只能是一个单行子查询。
|
1 |
select ( select job from emp where empno=7369) from emp; |
（4）子查询也可以出现在having字句中。
|
1 2 3 |
select empno,ename, sal,deptno from emp group by deptno,empno,ename,sal having deptno in ( select deptno from emp where deptno=10 or deptno=20) order by deptno,sal; |
3、多列子查询
1、where之后
成对比较（多列子查询）
|
1 |
select ename,comm,sal form emp where (sal,nvl(comm,-1)) in ( select sal,nvl(comm,-1) from emp where deptno=30); |
非成对比较
|
1 |
select ename,sal,comm from emp where sal in ( select sal from emp where deptno=30) and nvl(comm,-1) in ( select nvl(comm,-1) from emp where deptno=30) |
2、set之后
|
1 2 3 4 5 |
update monthly_orders set (tot_orders, max_order_amt, min_order_amt, tot_amt) = ( select count (*), max (sale_price), min (sale_price), sum (sale_price) from cust_order where order_dt >= TO_DATE( '01-JUL-2001' , 'DD-MON-YYYY' )) where month = 7 and year = 2001; |
二、在DDL语句中使用子查询
1、create table
通过在create table中使用子查询，可以在建立新表的同时复制表的数据。
|
1 |
CREATE TABLE new_emp(id, name ,sal,job,deptno) AS SELEct empno,ename,sal,job,deptno FROM emp; |
2、create View
建立视图时，必须指定视图所对应的子查询语句。
|
1 |
CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno; |
3、create materialized view 建立实体化视图
|
1 2 |
CREATE MATERIALIZED VIEW summary_emp AS SELECT deptno,job, avg (sal) avgsal, sum (sal) sumsal FROM emp GROUP BY cube (deptno,job); |
三、在DML语句中使用子查询
1.INSERT
|
1 2 |
INSERT INTO employee (id, name ,title,salary) SELECT emptno,ename,job,sal FROM emp; |
2.UPDATE
|
1 2 3 |
UPDATE emp SET (sal,comm)= ( SELECT sal,comm FROM emp WHERE ename= 'SMITH' ) WHERE job=( SELECT job FROM emp WHERE ename= 'SMITH' ); |
3.DELETE
|
1 2 |
DELECT FROM emp WHERE deptno= ( SELECT deptno FROM dept WHERE dname= 'SALES' ); |
四、WITH语句
如果同一个非关联子查询在同一次查询中被使用多次，这种情况可以使用ORACLE9I提供的WITH语句，WITH语句创建了命名的一个临时的数据集。这个只产生一次数据集可以在整个查询中使用多次，使用这个数据集就和使用表一样。
|
1 2 3 4 |
WITH avg_sal AS ( SELECT AVG (salary) val FROM employee) SELECT e.emp_id, e.lname, e.fname, ( SELECT ROUND(e.salary - val) FROM avg_sal) above_avg FROM employee e WHERE e.salary > ( SELECT val FROM avg_sal); |
五、相关子查询。
相关子查询：是指需要引用主查询表列的子查询语句。相关子查询是通过EXISTS谓词来实现的。
|
1 2 |
SELECT ename,job,sal,deptno FROM emp WHERE EXISTS ( SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc= 'NEW YORK' ); |
找出工资高于其所在部门平均工资的员工
|
1 |
select enam,sal from emp t1 where t1.sal > ( select avg (sal) from emp t2 where t1.Deptno = t2.Deptno) |
找出换了二次或二次以上工作的员工：
|
1 2 |
select last_name from employees e where 2<= ( select count (*) from job_history j where j.employee_id =e.employee_id) |
到此这篇关于Oracle在PL/SQL中使用子查询的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/springsnow/p/9394755.html
查看更多关于Oracle在PL/SQL中使用子查询的详细内容...