Oracle 表值函数之多表关联使用

前提条件已创建 表值函数： 

?

1	APPS.NJ_SOU_PKG.NJ_WIP_OPERATIONS_COLUMN_FT(p_wip_entity_id in number); <br><br>--表值函数简单调用

?

1 2	--測試 :JY4153693(ID =>4870414) SELECT * FROM TABLE(APPS.NJ_SOU_PKG.NJ_WIP_OPERATIONS_COLUMN_FT(P_WIP_ENTITY_ID =>4870414) ) ;

　　

-- 表值函数多表关联使用 

?

1 2 3 4 5 6

-- 表值函数多表关联使用  select we.wip_entity_name, nwoc.*    from  wip_entities we,          TABLE( APPS.NJ_SOU_PKG.NJ_WIP_OPERATIONS_COLUMN_FT(we.wip_entity_id) ) nwoc   where WE.wip_entity_name like 'JY415369_'   and we.wip_entity_id = nwoc.wip_entity_id ;

　　

以下是通过程序包方式创建表值函数。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229

CREATE OR REPLACE PACKAGE APPS.NJ_SOU_PKG AS /* -- 为解决"NJ_SOU_PROJECT_V  NSP"视图效率差问题，将 -- NJ_WIP_OPERATIONS_COLUMN_V视图改用 表值函数。 -- 以500笔记录为例，从5分钟提升到26秒。 -- samrv8, 2022/01/04, ADD */ TYPE TP_NJ_WIP_OP_COLUMN_ROW IS RECORD (    WIP_ENTITY_NAME VARCHAR2(50),    WIP_ENTITY_ID NUMBER,    ORGANIZATION_ID NUMBER,    CUSTOMER_SERVICE DATE,    WAX_INJECT DATE,    GOLD_CASTING DATE,    FIRST_FILING DATE,    STONE_MATCH DATE,    STONE_SETTING DATE,    PLATING DATE,    WAX_SETTING DATE,    CAD DATE,    WAX_ENGRAVING DATE,    SILVER_CASTING DATE,    MOULD_FILLING DATE,    HAND_MADE DATE,    RUBBER_MOULD_MAKING DATE,    UNDEFINED DATE );   TYPE TP_NJ_WIP_OP_COLUMN_DATA IS TABLE OF TP_NJ_WIP_OP_COLUMN_ROW;     FUNCTION NJ_WIP_OPERATIONS_COLUMN_FT(P_WIP_ENTITY_ID IN NUMBER) RETURN TP_NJ_WIP_OP_COLUMN_DATA     PIPELINED;   END NJ_SOU_PKG;       CREATE OR REPLACE PACKAGE BODY APPS.NJ_SOU_PKG AS     FUNCTION NJ_WIP_OPERATIONS_COLUMN_FT(P_WIP_ENTITY_ID IN NUMBER) RETURN TP_NJ_WIP_OP_COLUMN_DATA     PIPELINED IS     V_NJ_WIP_OP_COLUMN_ROW TP_NJ_WIP_OP_COLUMN_ROW;    CURSOR c1 IS      --( /*-- ===========================================================================      -- Copyright(c) : Noble Jewelry Limited All rights reserved.      -- -----------------------------------------------------------------------------      -- Program name           Creation Date    Version      Created by      -- NJ_WIP_OPERATIONS       2021-06-23       1.00         wzru      --  _COLUMN_V      -- -----------------------------------------------------------------------------      -- Usage:        -- -----------------------------------------------------------------------------      -- Description:      -- wzru,2021/06/21      -- 工單各工序完成時間(橫排).sql      -- 工單各工序完成時間,當工序有回頭時,獲取未完成的的工序時間或同工序中最後完成      -- 的時間 .      -- -----------------------------------------------------------------------------      -- Modification History:      -- Modified Date     Version   Done by      Change Description      -- 2021/06/23        1.00       wzru         Created      -- ===========================================================================*/     SELECT /*+INDEX(WIP_DISCRETE_JOBS WIP_DISCRETE_JOBS_N15 ) */  wip_entity_name,              wip_entity_id,              organization_id,              MAX (                 DECODE (                    piv.operation_id,                    1, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS customer_service,                                  -- "客服",              MAX (                 DECODE (                    piv.operation_id,                    2, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS wax_inject,                                        -- "注蠟",              MAX (                 DECODE (                    piv.operation_id,                    3, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS gold_casting,                                      -- "鑄造",              MAX (                 DECODE (                    piv.operation_id,                    4, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS first_filing,                                      -- "執模",              MAX (                 DECODE (                    piv.operation_id,                    5, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS stone_match,                                       -- "配石",              MAX (                 DECODE (                    piv.operation_id,                    6, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS stone_setting,                                     -- "鑲石",              MAX (                 DECODE (                    piv.operation_id,                    7, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS plating,                                          --"麼打電金",              MAX (                 DECODE (                    piv.operation_id,                    8, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS wax_setting,                                       -- "蠟鑲",              MAX (                 DECODE (                    piv.operation_id,                    9, NULLIF (date_last_moved,                               TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY')),                    DECODE (                       piv.operation_id,                       10, NULLIF (date_last_moved,                                   TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY')))))                 AS CAD,              --       MAX(decode(piv.operation_id, 10, nullif(date_last_moved, trunc(add_months(SYSDATE, 36), 'YYYY')))) AS CAD,              MAX (                 DECODE (                    piv.operation_id,                    11, NULLIF (date_last_moved,                                TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS Wax_engraving,                                      --"出蠟",              MAX (                 DECODE (                    piv.operation_id,                    12, NULLIF (date_last_moved,                                TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS silver_casting,                                    -- "倒銀",              MAX (                 DECODE (                    piv.operation_id,                    13, NULLIF (date_last_moved,                                TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS Mould_filling,                                      --"執版",              MAX (                 DECODE (                    piv.operation_id,                    14, NULLIF (date_last_moved,                                TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS Hand_made,                                         --"全手造",              MAX (                 DECODE (                    piv.operation_id,                    15, NULLIF (date_last_moved,                                TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS Rubber_mould_making,                               -- "壓模",              MAX (                 DECODE (                    piv.operation_id,                    99, NULLIF (date_last_moved,                                TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY'))))                 AS undefined                                          -- "未定義"         FROM (                                   -- DATE類型, 使用MAX()時,空值不參與比較 .               SELECT   wdj.wip_entity_name,                        wdj.wip_entity_id,                        wdj.organization_id,                        DECODE (npbd.operation_id, NULL, 99, npbd.operation_id)                           AS operation_id,                        DECODE (npbd.operation_code,                                NULL, '未定義',                                npbd.operation_code)                           AS operation_code,                        MAX (                           CASE                              WHEN     wo.quantity_completed =                                          wo.scheduled_quantity                                   AND wo.scheduled_quantity > 0                              THEN                                 wo.date_last_moved                              ELSE                                 TRUNC (ADD_MONTHS (SYSDATE, 36), 'YYYY')                           END)                           AS date_last_moved                   --MAX(nvl(wo.date_last_moved, trunc(add_months(SYSDATE, 36), 'YYYY'))) AS date_last_moved                   FROM apps.wip_operations_v wo,                        apps.wip_discrete_jobs_v wdj,                        apps.nj_pp_bom_departments_v npbd                  WHERE     wo.wip_entity_id = wdj.wip_entity_id                        AND wo.department_id = npbd.department_id                        AND wdj.creation_date >=                               TRUNC (ADD_MONTHS (SYSDATE, -60), 'YYYY')               --   AND WDJ.CREATION_DATE>= TRUNC(SYSDATE,'MM')                  AND WDJ.WIP_ENTITY_ID = P_WIP_ENTITY_ID -- 4880402 -- 3446188               GROUP BY wdj.wip_entity_name,                        wdj.wip_entity_id,                        wdj.organization_id,                        DECODE (npbd.operation_id, NULL, 99, npbd.operation_id),                        DECODE (npbd.operation_code,                                NULL, '未定義',                                npbd.operation_code)) piv     GROUP BY wip_entity_name, wip_entity_id, organization_id;     -- );         BEGIN      FOR R1 IN C1 LOOP        V_NJ_WIP_OP_COLUMN_ROW.WIP_ENTITY_NAME := R1.WIP_ENTITY_NAME;        V_NJ_WIP_OP_COLUMN_ROW.WIP_ENTITY_ID := R1.WIP_ENTITY_ID;        V_NJ_WIP_OP_COLUMN_ROW.ORGANIZATION_ID := R1.ORGANIZATION_ID;        V_NJ_WIP_OP_COLUMN_ROW.CUSTOMER_SERVICE := R1.CUSTOMER_SERVICE;        V_NJ_WIP_OP_COLUMN_ROW.WAX_INJECT := R1.WAX_INJECT;        V_NJ_WIP_OP_COLUMN_ROW.GOLD_CASTING := R1.GOLD_CASTING;        V_NJ_WIP_OP_COLUMN_ROW.FIRST_FILING := R1.FIRST_FILING;        V_NJ_WIP_OP_COLUMN_ROW.STONE_MATCH := R1.STONE_MATCH;        V_NJ_WIP_OP_COLUMN_ROW.STONE_SETTING := R1.STONE_SETTING;        V_NJ_WIP_OP_COLUMN_ROW.PLATING := R1.PLATING;        V_NJ_WIP_OP_COLUMN_ROW.WAX_SETTING := R1.WAX_SETTING;        V_NJ_WIP_OP_COLUMN_ROW.CAD := R1.CAD;        V_NJ_WIP_OP_COLUMN_ROW.WAX_ENGRAVING := R1.WAX_ENGRAVING;        V_NJ_WIP_OP_COLUMN_ROW.SILVER_CASTING := R1.SILVER_CASTING;        V_NJ_WIP_OP_COLUMN_ROW.MOULD_FILLING := R1.MOULD_FILLING;        V_NJ_WIP_OP_COLUMN_ROW.HAND_MADE := R1.HAND_MADE;        V_NJ_WIP_OP_COLUMN_ROW.RUBBER_MOULD_MAKING := R1.RUBBER_MOULD_MAKING;        V_NJ_WIP_OP_COLUMN_ROW.UNDEFINED := R1.UNDEFINED;                 PIPE ROW(V_NJ_WIP_OP_COLUMN_ROW);      END LOOP;      RETURN ;     END NJ_WIP_OPERATIONS_COLUMN_FT;   END NJ_SOU_PKG;