PLSQL: ORACLE表值函数及调用

 

ORACLE 表值函数应用

程序包头:

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
create or replace package kl_lead_time_pkg is
 
  -- Author  : samrv
  -- Created : 2023/5/3 14:23:58
  -- Purpose : 预加工周期
   
  -- Public type declarations
  type leadtime_rec_type is record(
    COMPONENT_ITEM_ID number:= fnd_api.g_miss_num,
    PLAN_LEVEL number:= fnd_api.g_miss_num,
    GROUP_ID number:= fnd_api.g_miss_num,
    ASSEMBLY_ITEM_ID number:= fnd_api.g_miss_num,
    COMPONENT_QUANTITY number:= fnd_api.g_miss_num,
    TOP_ITEM_ID number:= fnd_api.g_miss_num,
    LINE_NUM varchar2(4000):= fnd_api.g_miss_char,
    TOTAL_QTY number:= fnd_api.g_miss_num,
    ORGANIZATION_ID number:= fnd_api.g_miss_num,
    WIP_SUPPLY_TYPE number:= fnd_api.g_miss_num,
    PLANNING_MAKE_BUY_CODE number:= fnd_api.g_miss_num,
    LEAD_TIME  varchar2(4000):= fnd_api.g_miss_char
  );
  
  TYPE leadtime_tbl_type IS TABLE OF leadtime_rec_type ;--INDEX BY BINARY_INTEGER;
   
  -- Public constant declarations
  --<ConstantName> constant <Datatype> := <Value>;
 
  -- Public variable declarations
  --<VariableName> <Datatype>;
 
  -- Public function and procedure declarations
  --function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
  function get_lead_time_ft(
  p_top_item_id  in number,
  p_organization_id in number,
  p_quantity in number
) return leadtime_tbl_type pipelined;
 
/*
  function lead_time_ft(
  p_top_item_id  in number,
  p_organization_id in number,
  p_quantity in number
) return sys_refcursor;
*/
end kl_lead_time_pkg;
/

  

程序包体:

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
create or replace package body kl_lead_time_pkg is
 
  -- Private type declarations
  --type <TypeName> is <Datatype>;
   
  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;
 
  -- Private variable declarations
  --<VariableName> <Datatype>;
 
  -- Function and procedure implementations
  --function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
  --  <LocalVariable> <Datatype>;
  --begin
  --  <Statement>;
  --  return(<Result>);
  --end;
   function get_lead_time_ft(
      p_top_item_id  in number,
      p_organization_id in number,
      p_quantity in number
    return leadtime_tbl_type pipelined is
     
    ref_cursor sys_refcursor;
    leadtime_rec leadtime_rec_type;
    leadtime_tbl leadtime_tbl_type := leadtime_tbl_type();  
  cursor  c1 is
       SELECT betr.component_item_id,
              betr.plan_level,
              betr.GROUP_ID,
              betr.assembly_item_id,
              betr.component_quantity,
              betr.top_item_id,
              TO_CHAR (betr.rn1) || SYS_CONNECT_BY_PATH (TO_CHAR (betr.rn), '.') as line_num,             
              betr.extended_quantity as total_qty,    
              betr.organization_id,
              betr.wip_supply_type,
              betr.planning_make_buy_code,
              '0' || SYS_CONNECT_BY_PATH (TO_CHAR (NVL (betr.lead_time, 0)), '*')
               as  lead_time
         FROM (SELECT ROW_NUMBER ()
                      OVER (
                         PARTITION BY t.GROUP_ID,
                                      t.plan_level,
                                      t.assembly_item_id
                         ORDER BY
                            t.plan_level, t.parent_item, t.component_item_id)
                         rn,
                      DENSE_RANK () OVER (ORDER BY t.GROUP_ID) rn1,
                      t.plan_level,
                      t.GROUP_ID,
                      t.assembly_item_id,
                      t.component_quantity,
                      t.top_item_id,
                      t.parent_item,
                      t.component_item_id,
                      t.lead_time,
                      t.extended_quantity,
                      t.organization_id,
                      t.wip_supply_type,
                      t.planning_make_buy_code,
                      t.component_code
                 FROM (    SELECT bet.component_item_id,
                                  bet.plan_level,
                                  bet.GROUP_ID,
                                  bet.assembly_item_id,
                                  bet.component_quantity,
                                  bet.top_item_id,
                                  CONNECT_BY_ROOT bet.component_item_id parent_item,                                 
                                  NVL (brlt.quantity, 0) as lead_time,
                                  bet.extended_quantity,
                                  bet.component_sequence_id,
                                  --add below 3 columns 2013/11/14
                                  bet.organization_id,
                                  bet.wip_supply_type,
                                  msib.planning_make_buy_code,
                                  bet.component_code
                             FROM                -- kl_bom_explosion_temp bet,
                                  apps.kl_bom_explosion_temp_grp_v bet, --add by: zaoru,20230503
                                  mtl_system_items_b msib,
                                  apps.kl_bom_routing_lead_time_v brlt
                            WHERE     bet.component_item_id = msib.inventory_item_id
                                  AND bet.organization_id = msib.organization_id
                                  and bet.assembly_item_id = brlt.assembly_item_id(+)
                                 and bet.organization_id = brlt.organization_id(+)
                                  --AND BET.GROUP_ID = 18508477
                                 and bet.organization_id = p_organization_id
                                 and bet.top_item_id = p_top_item_id  -- 1224243 
                                 and brlt.low_bound(+) <= p_quantity  -- 24
                                 and brlt.upper_bound(+) > p_quantity -- 24
                       --AND msib.planning_make_buy_code = 1
                       START WITH bet.plan_level = 1
                       CONNECT BY     PRIOR bet.component_item_id =
                                         bet.assembly_item_id
                                  AND PRIOR bet.GROUP_ID = bet.GROUP_ID
                                  --hzq 20160622 增加递归条件
                                  AND PRIOR bet.component_code =
                                         SUBSTR (
                                            bet.component_code,
                                            1,
                                              INSTR (bet.component_code, '-', -1)
                                            - 1)) t) betr
        WHERE betr.wip_supply_type <> 6                   --2013/11/14 virtual
   -- and betr.assembly_item_id=474992
   START WITH plan_level = 1
   CONNECT BY     PRIOR betr.component_item_id = betr.assembly_item_id
              AND PRIOR betr.GROUP_ID = betr.GROUP_ID
              --hzq 20160622 增加递归条件
              AND PRIOR betr.component_code =
                     SUBSTR (betr.component_code,
                             1,
                             INSTR (betr.component_code, '-', -1) - 1);
                              
  begin
    for r1 in c1 loop
      leadtime_rec := leadtime_rec_type(r1.COMPONENT_ITEM_ID,
                      r1.PLAN_LEVEL,
                      r1.GROUP_ID,
                      r1.ASSEMBLY_ITEM_ID,
                      r1.COMPONENT_QUANTITY,
                      r1.TOP_ITEM_ID,
                      r1.LINE_NUM,
                      r1.TOTAL_QTY,
                      r1.ORGANIZATION_ID,
                      r1.WIP_SUPPLY_TYPE,
                      r1.PLANNING_MAKE_BUY_CODE,
                      r1.LEAD_TIME
                      );
     -- leadtime_tbl.EXTEND;
       --leadtime_tbl(leadtime_tbl.count) := leadtime_rec;
       pipe row(leadtime_rec);
    end loop;
     
    return;
    --return ;
  end get_lead_time_ft;
  /*
  function lead_time_ft(
  p_top_item_id  in number,
  p_organization_id in number,
  p_quantity in number
) return sys_refcursor is
   
   res sys_refcursor;
    
begin
  open res for  select * from  klcux.kl_lead_time_ft ;
  return res;
  end lead_time_ft;
  */
begin
  -- Initialization
  --<Statement>;
  null;
end kl_lead_time_pkg;
/

  

调用或调试

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
--调试
-- Created on 2023/5/3 by WZAORU
declare
  -- Local variables here
  i integer;
  V_TOP_ITEM_ID NUMBER;
  v_refcursor kl_lead_time_pkg.leadtime_tbl_type;
  ft_row klcux.kl_lead_time_ft%rowtype ;
begin
  -- Test statements here
  /*
  SELECT TB.TOP_ITEM_ID
   INTO V_TOP_ITEM_ID
   FROM
 TABLE(kl_lead_time_pkg.KL_LEAD_TIME_FT( 1224243,  167 , 24) )  TB
  WHERE ROWNUM= 1 ;
   DBMS_OUTPUT.PUT_LINE('V_TOP_ITEM_ID :'|| V_TOP_ITEM_ID);
   */
  v_refcursor := kl_lead_time_pkg.LEAD_TIME_FT( 1224243,  167 , 24);
  dbms_output.put_line( v_refcursor%rowcount);
  loop
    fetch v_refcursor into ft_row ;
     exit when v_refcursor%notfound;
    dbms_output.put_line(ft_row.TOP_ITEM_ID);
  end loop; 
  
end;

  多表关联 使用例子:

1
2
3
4
5
6
7
select wpb.* ,ft.lead_time as lead_time2 from
 APPS.KL_WIP_PR_BOMNUM_V wpb ,
   TABLE(kl_lead_time_pkg.get_LEAD_TIME_FT( wpb.top_item_id , wpb.organization_id , p_quantity=> 2400) ) ft
    where  ft.component_item_id = wpb.component_item_id
and ft.organization_id = wpb.organization_id
and ft.top_item_id = wpb.top_item_id
 and wpb.top_item_id =1224243 -- 1225241

  

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