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;
    

  

posted @   samrv  阅读(167)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示