目錄

• 一、概述
• 1、單行子查詢(子查詢只返回一行)
• 2、多行單列子查詢（子查詢返回多行）
• 3、多列子查詢
• 二、在DDL語(yǔ)句中使用子查詢
• 1、create table
• 2、create View
• 3、create materialized view 建立實(shí)體化視圖
• 三、在DML語(yǔ)句中使用子查詢
• 1.INSERT
• 2.UPDATE
• 3.DELETE
• 四、WITH語(yǔ)句
• 五、相關(guān)子查詢。

一、概述

在一個(gè)SQL語(yǔ)句中嵌套另一個(gè)SQL語(yǔ)句成為子查詢。包括單行子查詢，多行子查詢，多列子查詢。

注意，當(dāng)在DDL語(yǔ)句中引用子查詢時(shí)，可以帶有Order By子句；但是當(dāng)在where子句、Set子句中引用子查詢時(shí)，不能帶有Order by 子句。子查詢具有以下一些作用：

• 通過(guò)在insert或create table語(yǔ)句中使用子查詢，可以將源表數(shù)據(jù)插入目標(biāo)表中。
• 通過(guò)在create view或create materialieed view中使用子查詢，可以定義視圖或?qū)嶓w化視圖所對(duì)應(yīng)的select語(yǔ)句。
• 通過(guò)在update語(yǔ)句中使用子查詢可以修改一列或多列數(shù)據(jù)。
• 通過(guò)在where、having、start with子句中使用子查詢，可以提供條件值。

非相關(guān)（非關(guān)聯(lián)）子查詢的使用：

• FROM語(yǔ)句可以包含任意類型的非關(guān)聯(lián)子查詢。
• SELECT和ORDER BY可以包含標(biāo)量子查詢。
• GROUP BY語(yǔ)句不能包含子查詢。
• SHART WITH和CONNECT BY語(yǔ)句可以包含子查詢。
• WITH語(yǔ)句包含一個(gè)命名的非關(guān)聯(lián)子查詢，它可以被父查詢引用多次而只執(zhí)行一次。
• MERGE的USING語(yǔ)句可以包含非關(guān)聯(lián)子查詢。
• UPDATE的SET語(yǔ)句可以包含標(biāo)量子查詢或者單行多列子查詢
• INSERT語(yǔ)句可以在VALUES的位置包含標(biāo)量子查詢。

1、單行子查詢(子查詢只返回一行)

又稱為標(biāo)量子查詢，因?yàn)樗慕Y(jié)果用作父查詢的標(biāo)量。通常與比較運(yùn)算符比如=、>、<、!=、<=、>=聯(lián)合使用。

--查詢scott用戶下的emp表中工資比scott高的員工的信息(此操作中的子查詢只返回一行記錄)
  select * from emp  where sal>(select sal from emp where ename="SCOTT");

2、多行單列子查詢（子查詢返回多行）

使用特定的關(guān)鍵字如IN，ANY和ALL來(lái)將外層查詢的單個(gè)值與子查詢的多行進(jìn)行比較運(yùn)算。

（1）子查詢出現(xiàn)在where子句中的子查詢語(yǔ)法（內(nèi)嵌子查詢）

--查詢scott用戶下的emp表中所有的經(jīng)理的信息(此操作子查詢會(huì)返回多行記錄)
 select * from emp  where empno in ( select mgr from emp);

（2）子查詢出現(xiàn)在from子句中（內(nèi)嵌視圖）

--將scott用戶下的emp表中查詢出的數(shù)據(jù)作為一個(gè)內(nèi)嵌視圖在FROM子句中使用
 select *  from (select empno,ename,job,sal from emp);

（3）子查詢出現(xiàn)在select列表中，此時(shí)子查詢只能是一個(gè)單行子查詢。

select (select job from emp where empno=7369)  from emp;

（4）子查詢也可以出現(xiàn)在having字句中。

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之后

成對(duì)比較（多列子查詢）

select ename,comm,sal form emp where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where deptno=30);

非成對(duì)比較

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之后

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語(yǔ)句中使用子查詢

1、create table

通過(guò)在create table中使用子查詢，可以在建立新表的同時(shí)復(fù)制表的數(shù)據(jù)。

CREATE TABLE new_emp(id,name,sal,job,deptno) AS SELEct empno,ename,sal,job,deptno FROM emp;

2、create View

建立視圖時(shí)，必須指定視圖所對(duì)應(yīng)的子查詢語(yǔ)句。

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 建立實(shí)體化視圖

CREATE MATERIALIZED VIEW summary_emp AS
SELECT deptno,job,avg(sal) avgsal,sum(sal) sumsal FROM emp GROUP BY cube(deptno,job);

三、在DML語(yǔ)句中使用子查詢

1.INSERT

INSERT INTO employee (id,name,title,salary)
SELECT emptno,ename,job,sal FROM emp;

2.UPDATE

UPDATE emp SET (sal,comm)=
(SELECT sal,comm FROM emp WHERE ename="SMITH")
WHERE job=(SELECT job FROM emp WHERE ename="SMITH");

3.DELETE

DELECT FROM emp WHERE deptno=
(SELECT deptno FROM dept WHERE dname="SALES");

四、WITH語(yǔ)句

如果同一個(gè)非關(guān)聯(lián)子查詢?cè)谕淮尾樵冎斜皇褂枚啻危@種情況可以使用ORACLE9I提供的WITH語(yǔ)句，WITH語(yǔ)句創(chuàng)建了命名的一個(gè)臨時(shí)的數(shù)據(jù)集。這個(gè)只產(chǎn)生一次數(shù)據(jù)集可以在整個(gè)查詢中使用多次，使用這個(gè)數(shù)據(jù)集就和使用表一樣。

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);

五、相關(guān)子查詢。

相關(guān)子查詢：是指需要引用主查詢表列的子查詢語(yǔ)句。相關(guān)子查詢是通過(guò)EXISTS謂詞來(lái)實(shí)現(xiàn)的。

SELECT ename,job,sal,deptno FROM emp WHERE EXISTS
(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc="NEW YORK");

找出工資高于其所在部門平均工資的員工

select enam,sal from emp t1 where t1.sal > (select avg(sal) from emp t2 where t1.Deptno = t2.Deptno)

找出換了二次或二次以上工作的員工：

select last_name from employees e where 2<=
  (select count(*) from job_history j where j.employee_id =e.employee_id)

到此這篇關(guān)于Oracle在PL/SQL中使用子查詢的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持。