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