plsql 表中有表

1 在from中的表中表

SELECT 
       PRL.ITEM_DESCRIPTION,
       PRL.UNIT_MEAS_LOOKUP_CODE,
       PRL.QUANTITY,
       TO_CHAR(PRL.NEED_BY_DATE, 'yyyy/mm/dd') NEED_BY_DATE,
       PRL.VENDOR_ID,
       PRL.SUGGESTED_VENDOR_NAME VENDOR_NAME

  FROM PO.PO_REQUISITION_HEADERS_ALL PRH,
       PO.PO_REQUISITION_LINES_ALL PRL,
       APPS.PER_PEOPLE_F PPF1,
       (SELECT DISTINCT AGENT_ID, AGENT_NAME FROM APPS.PO_AGENTS_V) PPF2,----表中表
       PO.PO_REQ_DISTRIBUTIONS_ALL PRD,
       INV.MTL_SYSTEM_ITEMS_B MSI,
       PO.PO_LINE_LOCATIONS_ALL PLL,
       PO.PO_LINES_ALL PL,
       PO.PO_HEADERS_ALL PH
 WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
   AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
   AND PPF1.PERSON_ID = PRH.PREPARER_ID
   AND PRH.CREATION_DATE BETWEEN PPF1.EFFECTIVE_START_DATE AND
       PPF1.EFFECTIVE_END_DATE
   AND PPF2.AGENT_ID(+) = MSI.BUYER_ID

2 在select中的表中表

SELECT
      poh.po_header_id
      ,poh.segment1 po_number
      ,poh.currency_code
      ,to_char(poh.creation_date,'YYYY-MM-DD') po_date
      ,pov.vendor_name
      ,pov.segment1 vendor_number
      ,pvs.address_line1 vendor_address
      ,pvc.area_code || pvc.phone vendor_phone
      ,pvc.fax_area_code || pvc.fax vendor_fax     
      ,ppf.full_name buyer
      ,pvc.last_name || pvc.first_name contact_name
      ,hrl1.location_code ship_to_location
      ,hrl2.location_code bill_to_location
      ,poh.attribute5 discount
      /*,decode(hrl.location_code
              ,'香港建勝'
             ,'香港九龍灣宏冠道6號鴻力工業中心A座1樓A1&A21室'
             ,'台北公司'
             ,'台北市內湖區洲子街88號9樓'
             ,'台北工廠'
             ,'新竹縣芎林鄉上山村文德路231號') location_code*/
      ,hla1.address_line_1||hla1.address_line_2||hla1.address_line_3 ship_address
      ,hla1.telephone_number_1 ship_tel
      ,hla1.telephone_number_2||hla1.telephone_number_3 ship_fax
      ,hla2.address_line_1||hla2.address_line_2||hla2.address_line_3 bill_address
      ,hla2.telephone_number_1 bill_tel
      ,hla2.telephone_number_2||hla2.telephone_number_3 bill_fax
      ,hrl.ADDRESS_LINE_1 location_code
      ,poh.REVISION_NUM revision
      ,to_char(poh.REVISED_DATE,'YYYY-MM-DD') revision_date   
      ,(SELECT MAX(ffv.description)           -------------------------------表中表 这里用到了from中的表po_headers_all        poh    ，所以不能放在from中。
          FROM fnd_flex_values_vl  ffv
              ,fnd_flex_value_sets ffvs
         WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
           AND ffvs.flex_value_set_name = '3G_PO_PAYMENT_TERM'
           AND ffv.flex_value = poh.attribute4) payment
      ,(SELECT MAX(ffv.description)
          FROM fnd_flex_values_vl  ffv
              ,fnd_flex_value_sets ffvs
         WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
           AND ffvs.flex_value_set_name = '3G_PO_DELIVERY_TERM'
           AND ffv.flex_value = poh.attribute3) delivery
      ,(SELECT MAX(ffv.description)
          FROM fnd_flex_values_vl  ffv
              ,fnd_flex_value_sets ffvs
         WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
           AND ffvs.flex_value_set_name = '3G_PO_SHIPMENT'
           AND ffv.flex_value = poh.attribute2) shipment
      ,(SELECT MAX(ffv.description)
          FROM fnd_flex_values_vl  ffv
              ,fnd_flex_value_sets ffvs
         WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
           AND ffvs.flex_value_set_name = '3G_PO_PACKING'
           AND ffv.flex_value = poh.attribute1) packing
  FROM po_headers_all        poh     
      ,po_vendors            pov
      ,po_vendor_sites_all   pvs
      ,po_vendor_contacts    pvc
      ,per_people_f          ppf
      ,hr_locations_all      hrl
      ,hr_locations_all      hla1
      ,hr_locations_all      hla2
      ,hr_locations_all_tl hrl1
      ,hr_locations_all_tl hrl2
 WHERE poh.vendor_id = pov.vendor_id
   AND pov.vendor_id = pvs.vendor_id
   AND poh.agent_id = ppf.person_id  
   AND pvs.vendor_site_id(+) = poh.vendor_site_id
   AND pvc.vendor_contact_id(+) = poh.vendor_contact_id
   AND pvc.vendor_site_id(+) = poh.vendor_site_id
   AND poh.ship_to_location_id = hrl.location_id(+)
   AND poh.ship_to_location_id = hla1.location_id(+)
   AND poh.bill_to_location_id = hla2.location_id(+)
   AND hrl1.location_id(+) = poh.ship_to_location_id
   AND hrl1.LANGUAGE(+) = userenv('LANG')
   AND hrl2.location_id(+) = poh.bill_to_location_id
   AND hrl2.LANGUAGE(+) = userenv('LANG')
   AND ppf.EFFECTIVE_END_DATE >= SYSDATE
   AND poh.authorization_status = 'APPROVED'
   AND poh.segment1 between nvl(:p_po_from ,poh.segment1)  and nvl(:p_po_to ,poh.segment1)
   AND pov.vendor_id = nvl(:p_vendor_id,pov.vendor_id)
   AND poh.creation_date between nvl(:p_date_form,poh.creation_date) AND nvl(:p_date_to,poh.creation_date)
   AND ppf.full_name = nvl(:p_buyer,ppf.full_name)
 ORDER BY poh.segment1;
  

两者有区别：

放在select中可以引用from中的表来筛选数据，而且（好像）一次只跑出一笔，

放在form中不可以引用from中的表作为条件，会跑出所以满足条件的数据，在from中和其他表的作用一样。在where中再筛选数据