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 |
优质生活从拆开始
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了