Oracle在PL/SQL中使用子查詢
一、概述
在一個(gè)SQL語句中嵌套另一個(gè)SQL語句成為子查詢。包括單行子查詢,多行子查詢,多列子查詢。
注意,當(dāng)在DDL語句中引用子查詢時(shí),可以帶有Order By子句;但是當(dāng)在where子句、Set子句中引用子查詢時(shí),不能帶有Order by 子句。子查詢具有以下一些作用:
- 通過在insert或create table語句中使用子查詢,可以將源表數(shù)據(jù)插入目標(biāo)表中。
- 通過在create view或create materialieed view中使用子查詢,可以定義視圖或?qū)嶓w化視圖所對應(yīng)的select語句。
- 通過在update語句中使用子查詢可以修改一列或多列數(shù)據(jù)。
- 通過在where、having、start with子句中使用子查詢,可以提供條件值。
非相關(guān)(非關(guān)聯(lián))子查詢的使用:
- FROM語句可以包含任意類型的非關(guān)聯(lián)子查詢。
- SELECT和ORDER BY可以包含標(biāo)量子查詢。
- GROUP BY語句不能包含子查詢。
- SHART WITH和CONNECT BY語句可以包含子查詢。
- WITH語句包含一個(gè)命名的非關(guān)聯(lián)子查詢,它可以被父查詢引用多次而只執(zhí)行一次。
- MERGE的USING語句可以包含非關(guān)聯(lián)子查詢。
- UPDATE的SET語句可以包含標(biāo)量子查詢或者單行多列子查詢
- INSERT語句可以在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來將外層查詢的單個(gè)值與子查詢的多行進(jìn)行比較運(yùn)算。
(1)子查詢出現(xiàn)在where子句中的子查詢語法(內(nèi)嵌子查詢)
--查詢scott用戶下的emp表中所有的經(jīng)理的信息(此操作子查詢會返回多行記錄) 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之后
成對比較(多列子查詢)
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中使用子查詢,可以在建立新表的同時(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í),必須指定視圖所對應(yīng)的子查詢語句。
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語句中使用子查詢
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語句
如果同一個(gè)非關(guān)聯(lián)子查詢在同一次查詢中被使用多次,這種情況可以使用ORACLE9I提供的WITH語句,WITH語句創(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)子查詢:是指需要引用主查詢表列的子查詢語句。相關(guān)子查詢是通過EXISTS謂詞來實(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中使用子查詢的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
相關(guān)文章
Oracle數(shù)據(jù)庫中如何給表賦予權(quán)限
賦權(quán)是指將特定的權(quán)限授予用戶或用戶組,以便他們可以執(zhí)行特定的操作,如查詢、插入、更新和刪除數(shù)據(jù),創(chuàng)建和修改表結(jié)構(gòu),以及執(zhí)行其他管理任務(wù),這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫中如何給表賦予權(quán)限的相關(guān)資料,需要的朋友可以參考下2024-01-01oracle中得到一條SQL語句的執(zhí)行時(shí)間的兩種方式
這篇文章主要介紹了oracle中如何得到一條SQL語句的執(zhí)行時(shí)間,有兩種可行方式,大家可以參考下2014-05-05Oracle數(shù)據(jù)庫密碼文件的使用與維護(hù)
Oracle數(shù)據(jù)庫密碼文件的使用與維護(hù)...2007-03-03oracle數(shù)據(jù)庫查看鎖表的sql語句整理
Oracle數(shù)據(jù)庫操作中,我們有時(shí)會用到鎖表查詢以及解鎖和kill進(jìn)程等操作,這篇文章主要給大家介紹了關(guān)于oracle數(shù)據(jù)庫查看鎖表的sql語句的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-10-10oracle 在一個(gè)存儲過程中調(diào)用另一個(gè)返回游標(biāo)的存儲過程
實(shí)際項(xiàng)目當(dāng)中經(jīng)常需要在一個(gè)存儲過程中調(diào)用另一個(gè)存儲過程返回的游標(biāo),本文列舉了兩種情況講述具體的操作方法。2009-09-09oracle創(chuàng)建數(shù)據(jù)庫和用戶的方法
這篇文章主要介紹了oracle創(chuàng)建數(shù)據(jù)庫和用戶的方法,mysql和sqlserver創(chuàng)建數(shù)據(jù)庫可以直接通過create database “數(shù)據(jù)庫名”,而oracle創(chuàng)建數(shù)據(jù)庫要對應(yīng)一個(gè)用戶,數(shù)據(jù)庫和用戶一般是一一對應(yīng)的,下面把oracle創(chuàng)建數(shù)據(jù)庫和用戶的步驟分享給大家。2015-07-07解決The?Network?Adapter?could?not?establish?the?conn問題
這篇文章主要介紹了解決The?Network?Adapter?could?not?establish?the?conn問題,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-02-02Oracle數(shù)據(jù)庫中外鍵的相關(guān)操作整理
這篇文章主要介紹了Oracle數(shù)據(jù)庫中外鍵的相關(guān)操作整理,包括對外鍵參照的主表記錄進(jìn)行刪除的操作方法等,需要的朋友可以參考下2016-01-01