结合网页查找的绑定变量

Question

i具有此SQL，该sql在where子句中使用相同的变量（：item）。它在Oracle SQL开发人员中运行良好。它弹出并要求该项目并返回正确的数据。
我想使用此SQL创建一个网页，以允许用户查找项目。这是针对Oracle数据库的查询。有没有办法将其重写，因此将3：项目结合到1？
然后，我将其用作网页的搜索字段。
我是新手，所以要友善。

SELECT ITEM_C.SEGMENT1 "Chain (Top) Item",
  ITEM_C.DESCRIPTION,
  ITEM_F.SEGMENT1 "Newer Item",
  ITEM_F.DESCRIPTION,
  item_t.SEGMENT1 "Older Item",
  item_t.DESCRIPTION "Older Description",
  ITEM_C.ITEM_TYPE TOP_ITEM_TYPE,
  ITEM_F.ITEM_TYPE NEWER_ITEM_TYPE,
  item_t.ITEM_TYPE Older_Item_Type,
  DOF.LINK_NUMBER,
  DOF.SUPERCESSION_TYPE,
  (SELECT fu.USER_NAME
  FROM apps.fnd_user fu
  WHERE fu.USER_ID = DOF.CREATED_BY
  ) "DOF Created By",
  DOF.CREATION_DATE "DOF Created",
  DOF.LAST_UPDATE_DATE "DOF Updated",
  item_t.CREATION_DATE "Older Created",
  item_t.LAST_UPDATE_DATE "Older Updated",
  ITEM_C.CREATION_DATE "Top Created",
  TRUNC(ITEM_C.CREATION_DATE - DOF.CREATION_DATE, 1) "Chain Lag",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = ITEM_C.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 2470383
  ), NULL, 'No', 'Yes') "Top in P01",
  DECODE(
  (SELECT 1 FROM INV.MTL_SYSTEM_ITEMS_B a WHERE a.INVENTORY_ITEM_ID = ITEM_C.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 98
  ), NULL, 'No', 'Yes') "Top in FTE",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = ITEM_F.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 2470383
  ), NULL, 'No', 'Yes') "Newest in P01",
  DECODE(
  (SELECT 1 FROM INV.MTL_SYSTEM_ITEMS_B a WHERE a.INVENTORY_ITEM_ID = ITEM_F.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 98
  ), NULL, 'No', 'Yes') "Newest in FTE",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = item_t.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 2470383
  ), NULL, 'No', 'Yes') "Oldest in P01",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = item_t.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 98
  ), NULL, 'No', 'Yes') "Oldest in FTE",
  ITEM_C.PURCHASING_ENABLED_FLAG "Top Purchasing Enabled",
  ITEM_F.PURCHASING_ENABLED_FLAG "Newer Purchasing Enabled",
  item_t.PURCHASING_ENABLED_FLAG "Older Purchasing Enabled",
  ITEM_C.FIXED_LOT_MULTIPLIER CHAIN_FLM,
  ITEM_F.FIXED_LOT_MULTIPLIER NEWER_FLM,
  item_t.FIXED_LOT_MULTIPLIER Older_FLM,
  SysDate "Current Date"
FROM IKNDOF.IKN_DOF_ITEM_CHAINS DOF
LEFT OUTER JOIN INV.MTL_SYSTEM_ITEMS_B ITEM_F
ON DOF.FROM_ITEM_ID = ITEM_F.INVENTORY_ITEM_ID
AND ITEM_F.ORGANIZATION_ID = 86
LEFT OUTER JOIN INV.MTL_SYSTEM_ITEMS_B ITEM_C
ON DOF.CHAIN_ITEM_ID = ITEM_C.INVENTORY_ITEM_ID
AND ITEM_C.ORGANIZATION_ID = 86
LEFT OUTER JOIN inv.mtl_system_items_b item_t
ON DOF.TO_ITEM_ID = item_t.INVENTORY_ITEM_ID
AND item_t.ORGANIZATION_ID = 86
WHERE 1 = 1
AND DOF.CHAIN_ITEM_ID     IN
  (SELECT DISTINCT df.CHAIN_ITEM_ID
  FROM inv.mtl_system_items_b topi,
    inv.mtl_system_items_b fri,
    inv.mtl_system_items_b toi,
    ikndof.ikn_dof_item_chains df
  WHERE df.FROM_ITEM_ID    = fri.INVENTORY_ITEM_ID(+)
  AND df.TO_ITEM_ID        = toi.INVENTORY_ITEM_ID(+)
  AND df.CHAIN_ITEM_ID     = topi.INVENTORY_ITEM_ID(+)
  AND fri.ORGANIZATION_ID  = 86
  AND toi.ORGANIZATION_ID  = 86
  AND topi.ORGANIZATION_ID = 86
  AND (fri.SEGMENT1 LIKE :Item
  OR toi.SEGMENT1 LIKE :Item
  OR topi.SEGMENT1 LIKE :Item)
  )
ORDER BY "Chain (Top) Item",
  DOF.LINK_NUMBER

谢谢，斯科特

Answer 1

您有2个选项 -

选项1.根据您使用的脚本语言，使用语法将此查询放入网页中。不建议此选项。

选项2.在Oracle中创建存储过程（SP），然后在您的网页中调用它。这是更好的选择，因为

a。）sp被编译，因此运行速度更快

b。）可以由多个页面/客户端重复使用。

c。）如果查询更改，您只需要在一个地方更改它-s。

下面是我为您制定的存储过程，它应该在您的IDE中进行编译，希望没有修改。

CREATE OR REPLACE PROCEDURE yourdb.yourproc
(
        i_search            IN varchar2,
        o_return_cursor     OUT  GLOBAL.GenericCursorType
) AS
BEGIN

    OPEN o_return_cursor FOR 
SELECT ITEM_C.SEGMENT1 "Chain (Top) Item",
  ITEM_C.DESCRIPTION,
  ITEM_F.SEGMENT1 "Newer Item",
  ITEM_F.DESCRIPTION,
  item_t.SEGMENT1 "Older Item",
  item_t.DESCRIPTION "Older Description",
  ITEM_C.ITEM_TYPE TOP_ITEM_TYPE,
  ITEM_F.ITEM_TYPE NEWER_ITEM_TYPE,
  item_t.ITEM_TYPE Older_Item_Type,
  DOF.LINK_NUMBER,
  DOF.SUPERCESSION_TYPE,
  (SELECT fu.USER_NAME
  FROM apps.fnd_user fu
  WHERE fu.USER_ID = DOF.CREATED_BY
  ) "DOF Created By",
  DOF.CREATION_DATE "DOF Created",
  DOF.LAST_UPDATE_DATE "DOF Updated",
  item_t.CREATION_DATE "Older Created",
  item_t.LAST_UPDATE_DATE "Older Updated",
  ITEM_C.CREATION_DATE "Top Created",
  TRUNC(ITEM_C.CREATION_DATE - DOF.CREATION_DATE, 1) "Chain Lag",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = ITEM_C.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 2470383
  ), NULL, 'No', 'Yes') "Top in P01",
  DECODE(
  (SELECT 1 FROM INV.MTL_SYSTEM_ITEMS_B a WHERE a.INVENTORY_ITEM_ID = ITEM_C.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 98
  ), NULL, 'No', 'Yes') "Top in FTE",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = ITEM_F.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 2470383
  ), NULL, 'No', 'Yes') "Newest in P01",
  DECODE(
  (SELECT 1 FROM INV.MTL_SYSTEM_ITEMS_B a WHERE a.INVENTORY_ITEM_ID = ITEM_F.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 98
  ), NULL, 'No', 'Yes') "Newest in FTE",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = item_t.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 2470383
  ), NULL, 'No', 'Yes') "Oldest in P01",
  DECODE(
  (SELECT 1 FROM inv.mtl_system_items_b a WHERE a.INVENTORY_ITEM_ID = item_t.INVENTORY_ITEM_ID
  AND a.ORGANIZATION_ID = 98
  ), NULL, 'No', 'Yes') "Oldest in FTE",
  ITEM_C.PURCHASING_ENABLED_FLAG "Top Purchasing Enabled",
  ITEM_F.PURCHASING_ENABLED_FLAG "Newer Purchasing Enabled",
  item_t.PURCHASING_ENABLED_FLAG "Older Purchasing Enabled",
  ITEM_C.FIXED_LOT_MULTIPLIER CHAIN_FLM,
  ITEM_F.FIXED_LOT_MULTIPLIER NEWER_FLM,
  item_t.FIXED_LOT_MULTIPLIER Older_FLM,
  SysDate "Current Date"
FROM IKNDOF.IKN_DOF_ITEM_CHAINS DOF
LEFT OUTER JOIN INV.MTL_SYSTEM_ITEMS_B ITEM_F
ON DOF.FROM_ITEM_ID = ITEM_F.INVENTORY_ITEM_ID
AND ITEM_F.ORGANIZATION_ID = 86
LEFT OUTER JOIN INV.MTL_SYSTEM_ITEMS_B ITEM_C
ON DOF.CHAIN_ITEM_ID = ITEM_C.INVENTORY_ITEM_ID
AND ITEM_C.ORGANIZATION_ID = 86
LEFT OUTER JOIN inv.mtl_system_items_b item_t
ON DOF.TO_ITEM_ID = item_t.INVENTORY_ITEM_ID
AND item_t.ORGANIZATION_ID = 86
WHERE 1 = 1
AND DOF.CHAIN_ITEM_ID     IN
  (SELECT DISTINCT df.CHAIN_ITEM_ID
  FROM inv.mtl_system_items_b topi,
    inv.mtl_system_items_b fri,
    inv.mtl_system_items_b toi,
    ikndof.ikn_dof_item_chains df
  WHERE df.FROM_ITEM_ID    = fri.INVENTORY_ITEM_ID(+)
  AND df.TO_ITEM_ID        = toi.INVENTORY_ITEM_ID(+)
  AND df.CHAIN_ITEM_ID     = topi.INVENTORY_ITEM_ID(+)
  AND fri.ORGANIZATION_ID  = 86
  AND toi.ORGANIZATION_ID  = 86
  AND topi.ORGANIZATION_ID = 86
  AND (fri.SEGMENT1 LIKE i_search
  OR toi.SEGMENT1 LIKE i_search
  OR topi.SEGMENT1 LIKE i_search)
  )
ORDER BY "Chain (Top) Item",
  DOF.LINK_NUMBER ;

EXCEPTION
    WHEN  OTHERS THEN 
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||'  yourproc WHEN OTHERS  '); 
END; -- Procedure 
/