目錄

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

一、概述

在一個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、單行子查詢(子查詢只返回一行)

又稱為標量子查詢，因為它的結果用作父查詢的標量。通常與比較運算符比如=、>、<、!=、<=、>=聯合使用。

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

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

使用特定的關鍵字如IN，ANY和ALL來將外層查詢的單個值與子查詢的多行進行比較運算。

（1）子查詢出現在where子句中的子查詢語法（內嵌子查詢）

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

（2）子查詢出現在from子句中（內嵌視圖）

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

（3）子查詢出現在select列表中，此時子查詢只能是一個單行子查詢。

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

（4）子查詢也可以出現在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之后

成對比較（多列子查詢）

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

非成對比較

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

1、create table

通過在create table中使用子查詢，可以在建立新表的同時復制表的數據。

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

2、create View

建立視圖時，必須指定視圖所對應的子查詢語句。

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 建立實體化視圖

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

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語句

如果同一個非關聯子查詢在同一次查詢中被使用多次，這種情況可以使用ORACLE9I提供的WITH語句，WITH語句創建了命名的一個臨時的數據集。這個只產生一次數據集可以在整個查詢中使用多次，使用這個數據集就和使用表一樣。

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謂詞來實現的。

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)

到此這篇關于Oracle在PL/SQL中使用子查詢的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。