视图调用存储过程全局变量
来源:互联网 发布:黑客帝国进入矩阵 编辑:程序博客网 时间:2024/05/25 21:32
刚学到的一个用法,具体好处不知道,可能是为了满足业务需求的一种实现方法吧,拿来分享下。
FORM:
xxrdc550.SP_SetCurrParameter ( :parameter.org_id,
:query.qINVENTORY_ITEM_ID ,
:query.qPO_HEADER_ID,
:query.qPO_LINE_ID,
:query.qPO_RELEASE_ID,
:query.qPO_LINE_LOCATION_ID,
:query.qCARD_NUMBER,
:query.qTRANSACTION_TYPE,
:query.qRDC_INVENTORY,
:query.qRDC_LOCATION,
:query.QRDC_MANAGER) ;
go_block('XXINV_R_INV_MTL_QUANITY_V');
EXECUTE_QUERY;
存储过程:xxrdc550
create or replace package xxrdc550 is
G_ORGANIZATION_ID Number;
G_INVENTORY_ITEM_ID NUMBER ;
G_PO_HEADER_ID NUMBER;
G_PO_LINE_ID NUMBER;
G_PO_RELEASE_ID NUMBER;
G_PO_LINE_LOCATION_ID NUMBER;
G_CARD_NUMBER VARCHAR2(60);
G_TRANSACTION_TYPE VARCHAR2(60);
G_RDC_INVENTORY VARCHAR2(60);
G_RDC_LOCATION VARCHAR2(60);
G_RDC_MANAGER VARCHAR2(60);
Procedure SP_SetCurrParameter ( P_ORGANIZATION_ID Number,
P_INVENTORY_ITEM_ID NUMBER ,
P_PO_HEADER_ID NUMBER,
P_PO_LINE_ID NUMBER,
P_PO_RELEASE_ID NUMBER,
P_PO_LINE_LOCATION_ID NUMBER,
P_CARD_NUMBER VARCHAR2,
P_TRANSACTION_TYPE VARCHAR2,
P_RDC_INVENTORY varchar2,
P_RDC_LOCATION varchar2,
P_RDC_MANAGER varchar2) ;
Function FN_Get_ORGANIZATION_ID
Return Number;
Function FN_Get_INVENTORY_ITEM_ID
Return Number;
Function FN_Get_PO_HEADER_ID
Return Number;
Function FN_Get_PO_LINE_ID
Return Number;
Function FN_Get_PO_RELEASE_ID
Return Number;
Function FN_Get_PO_LINE_LOCATION_ID
Return Number;
Function FN_Get_CARD_NUMBER
Return VARCHAR2;
Function FN_Get_TRANSACTION_TYPE
Return VARCHAR2;
Function FN_Get_RDC_INVENTORY
Return VARCHAR2;
Function FN_Get_RDC_LOCATION
Return VARCHAR2;
Function FN_Get_RDC_MANAGER
Return VARCHAR2;
end xxrdc550;
/
create or replace package body xxrdc550 is
Function FN_Get_ORGANIZATION_ID
Return Number
Is
Begin
Return G_ORGANIZATION_ID;
End FN_Get_ORGANIZATION_ID;
Function FN_Get_INVENTORY_ITEM_ID
Return Number
Is
Begin
Return G_INVENTORY_ITEM_ID;
End FN_Get_INVENTORY_ITEM_ID;
Function FN_Get_PO_HEADER_ID
Return Number
Is
Begin
Return G_PO_HEADER_ID ;
End FN_Get_PO_HEADER_ID;
Function FN_Get_PO_LINE_ID
Return Number
Is
Begin
Return G_PO_LINE_ID;
End FN_Get_PO_LINE_ID ;
Function FN_Get_PO_RELEASE_ID
Return Number
Is
Begin
Return G_PO_RELEASE_ID;
End FN_Get_PO_RELEASE_ID;
Function FN_Get_PO_LINE_LOCATION_ID
Return Number
Is
Begin
Return G_PO_LINE_LOCATION_ID;
End FN_Get_PO_LINE_LOCATION_ID ;
Function FN_Get_CARD_NUMBER
Return VARCHAR2
Is
Begin
Return G_CARD_NUMBER ;
End FN_Get_CARD_NUMBER;
Function FN_Get_TRANSACTION_TYPE
Return VARCHAR2
Is
Begin
Return G_TRANSACTION_TYPE;
End FN_Get_TRANSACTION_TYPE ;
Function FN_Get_RDC_INVENTORY
Return VARCHAR2
Is
Begin
Return G_RDC_INVENTORY ;
End FN_Get_RDC_INVENTORY;
Function FN_Get_RDC_LOCATION
Return VARCHAR2
Is
Begin
Return G_RDC_LOCATION;
End FN_Get_RDC_LOCATION ;
Function FN_Get_RDC_MANAGER
Return VARCHAR2
Is
Begin
Return G_RDC_MANAGER;
End FN_Get_RDC_MANAGER ;
Procedure SP_SetCurrParameter ( P_ORGANIZATION_ID Number,
P_INVENTORY_ITEM_ID NUMBER ,
P_PO_HEADER_ID NUMBER,
P_PO_LINE_ID NUMBER,
P_PO_RELEASE_ID NUMBER,
P_PO_LINE_LOCATION_ID NUMBER,
P_CARD_NUMBER VARCHAR2,
P_TRANSACTION_TYPE VARCHAR2,
P_RDC_INVENTORY varchar2,
P_RDC_LOCATION varchar2,
P_RDC_MANAGER varchar2)
Is
Begin
G_ORGANIZATION_ID :=P_ORGANIZATION_ID;
G_INVENTORY_ITEM_ID := P_INVENTORY_ITEM_ID;
G_PO_HEADER_ID :=P_PO_HEADER_ID;
G_PO_LINE_ID :=P_PO_LINE_ID;
G_PO_RELEASE_ID :=P_PO_RELEASE_ID;
G_PO_LINE_LOCATION_ID :=P_PO_LINE_LOCATION_ID;
G_CARD_NUMBER :=P_CARD_NUMBER;
G_TRANSACTION_TYPE:= P_TRANSACTION_TYPE;
G_RDC_INVENTORY:=P_RDC_INVENTORY;
G_RDC_LOCATION:=P_RDC_LOCATION;
G_RDC_MANAGER :=P_RDC_MANAGER;
End SP_SetCurrParameter;
End xxrdc550;
/
视图:XXINV_R_INV_MTL_QUANITY_V
CREATE OR REPLACE VIEW XXINV_R_INV_MTL_QUANITY_V AS
SELECT XRT.ORGANIZATION_ID
,XRT.INVENTORY_ITEM_ID
,msi.segment1
,msi.description
,XRT.PRIMARY_UNIT_OF_MEASURE
,sum(XRT.QUANTITY) mtl_count
,xrm.rdc_manager
FROM XXINV_R_RDC_TRANSACTIONS XRT,
mtl_system_items_b msi,
xxinv_r_item_master xrm
where xrt.INVENTORY_ITEM_ID=xrm.inventory_item_id
and xrt.organization_id=xrm.organization_id
and xrm.organization_id=msi.organization_id
and xrm.inventory_item_id=msi.inventory_item_id
AND XRT.ORGANIZATION_ID=XXRDC550.FN_Get_ORGANIZATION_ID
and xrt.inventory_item_id=NVL(XXRDC550.FN_Get_INVENTORY_ITEM_ID,xrt.inventory_item_id)
and xrt.TRANSACTION_TYPE=nvl(xxrdc550.FN_Get_TRANSACTION_TYPE,xrt.transaction_type)
and xrt.po_header_id+0=nvl(xxrdc550.FN_Get_PO_HEADER_ID,xrt.po_header_id)
and NVL(xrt.po_release_id,0)=nvl(xxrdc550.FN_Get_PO_RELEASE_ID,NVL(xrt.po_release_id,0))
and NVL(xrt.po_line_id,0)=nvl(xxrdc550.FN_Get_PO_LINE_ID,NVL(xrt.po_line_id,0))
and NVL(xrt.po_line_location_id,0)=nvl(xxrdc550.FN_Get_PO_LINE_LOCATION_ID,NVL(xrt.po_line_location_id,0))
and NVL(xrt.card_number,'?')=nvl(xxrdc550.FN_Get_CARD_NUMBER,NVL(xrt.card_number,'?'))
and NVL(xrt.rdc_inventory,'?')=nvl(xxrdc550.FN_Get_RDC_INVENTORY,NVL(xrt.rdc_inventory,'?'))
and NVL(xrt.rdc_location,'?')=nvl(xxrdc550.FN_Get_RDC_LOCATION,NVL(xrt.rdc_location,'?'))
and nvl(xrm.rdc_manager,'?')=nvl(xxrdc550.FN_Get_RDC_MANAGER,nvl(xrm.rdc_manager,'?'))
group by XRT.ORGANIZATION_ID
,XRT.INVENTORY_ITEM_ID
,msi.segment1
,msi.description
,XRT.PRIMARY_UNIT_OF_MEASURE
,xrm.rdc_manager;
由FORM调用包xxrdc550的SP_SetCurrParameter的存储过程,在包xxrdc550中定义全局变量,都在存储过程赋值完成,再由FUNCTION返回。视图调用包中的FUNCTION获取返回值。
0 0
- 视图调用存储过程全局变量
- 用调用的视图和存储过程
- mybatis调用视图和存储过程
- 求关于JAVA后台调用视图,后台调用存储过程,视图调用存储过程的书,越详细越好
- ASP调用SQL Server视图和存储过程
- 用ASP调用SQLServer的视图和存储过程
- 运用ASP调用数据库中视图及存储过程
- Hibernate 调用视图 (View )存储过程 (Procedure)
- Oracle创建和调用存储过程,函数,视图
- 视图和存储过程
- 视图,存储过程,任务
- 存储过程,视图
- 视图、存储过程解密
- 视图,存储过程
- SQL存储过程+视图
- 存储过程与视图
- 存储过程及视图
- 存储过程,函数,视图
- RGB颜色查询对照表
- 3Sum Closest
- 面向对象----C#运算符重载
- A. Winner Codeforences
- 如何聚集高水平研发人员
- 视图调用存储过程全局变量
- 使用EF6和MVC5实现一个简单的选课系统--使用EF6实现继承(11/12)
- Java synchronized详解
- SEO时代已成为过去,??已经到来
- STL源码剖析笔记
- Hibernate数据持久化及update更新问题
- 使用EF6和MVC5实现一个简单的选课系统--EF6的高级用法(12/12)
- 九度题目1173:查找
- [ACM] hdu 1342 Lotto (排列)