Oracle PL/SQL: Subquery 子查詢的使用

在 Oracle PL/SQL 中, 可以在 5 個地方使用 Subquery,

也就是將 "Select" 語法寫在 "Select" / "From" / "Where" / "Insert" / "Update ... Set" 中, 如下 :

 應用 1:
Select 中, 把 subquery 當 column 使用

 範例 1:
SELECT OOTT.NAME                 LINE_TYPE
, OOTT.DESCRIPTION LINE_TYPE_DESC
, OOTT.TRANSACTION_TYPE_ID LINE_TYPE_ID
, (SELECT WF.DISPLAY_NAME
FROM WF_ACTIVITIES_VL WF
WHERE WF.TYPE = 'PROCESS'
AND WF.ITEM_TYPE = 'OEOL'
AND WF.RUNNABLE_FLAG = 'Y'
AND WF.NAME = OWA.PROCESS_NAME
AND ROWNUM =1
)
WORKFLOW_DISPLAY_NAME
FROM OE_TRANSACTION_TYPES_TL OOTT
, OE_TRANSACTION_TYPES_ALL OOTA
, OE_WORKFLOW_ASSIGNMENTS OWA
, OE_LOOKUPS OL
WHERE 1 = 1
AND SYSDATE BETWEEN OWA.START_DATE_ACTIVE AND NVL(OWA.END_DATE_ACTIVE,SYSDATE)
......


 應用 2:
From 中, 把 subquery 當 data source 使用

 範例 2:
SELECT AT1.OWNER, COUNT(*), AT2.CNT
FROM ALL_TABLES AT1
, (SELECT COUNT(*) AS CNT
FROM ALL_TABLES
)
AT2
GROUP BY AT1.OWNER, AT2.CNT;


 應用 3:
Where 中, 把 subquery 當 condition 使用

 範例 3:
SELECT OPERATION_SEQ_NUM, STANDARD_OPERATION_CODE
FROM BOM_OPERATION_SEQUENCES_V
WHERE DISABLE_DATE IS NULL
AND OPERATION_SEQ_NUM =
(SELECT OPERATION_SEQ_NUM
FROM BOM_INVENTORY_COMPONENTS_V
WHERE COMPONENT_ITEM_ID = '&COMPONENT_ITEM_ID'
AND BILL_SEQUENCE_ID =
(SELECT COMMON_BILL_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS
WHERE ORGANIZATION_ID = '&ORG_ID'
AND ASSEMBLY_ITEM_ID = '&ASSEMBLY_ITEM_ID')
)

AND ROUTING_SEQUENCE_ID =
(SELECT ROUTING_SEQUENCE_ID
FROM BOM_OPERATIONAL_ROUTINGS
WHERE ORGANIZATION_ID = '&ORG_ID'
AND ASSEMBLY_ITEM_ID = '&ASSEMBLY_ITEM_ID')
;


 應用 4:
Insert 中, 把 subquery 當 data 使用

 範例 4:
insert into tom1
values( 'aa'
, nvl( (select max( bb ) + 1
from tom1
where aa = 'aa'
)
, 1 )
);


 應用 5:
Update ... Set 中, 把 subquery 當 data 使用

 範例 5:
update tom_l t1
set t1.aa = ( select th.aa
from tom_h th
where th.header_id = tl.header_id
)
;

Related Posts Plugin for WordPress, Blogger...