Oracle 表值函数之多表关联使用
前提条件已创建 表值函数：
APPS.NJ_SOU_PKG.NJ_WIP_OPERATIONS_COLUMN_FT(p_wip_entity_id in number);
--表值函数简单调用
--測試 :JY4153693(ID =>4870414) SELECT * FROM TABLE(APPS.NJ_SOU_PKG.NJ_WIP_OPERATIONS_COLUMN_FT(P_WIP_ENTITY_ID =>4870414) ) ;
-- 表值函数多表关联使用
-- 表值函数多表关联使用
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 ;
以下是通过程序包方式创建表值函数。
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;
优质生活从拆开始
