mysql - 当我调用存储过程 1 时,我想阅读有关它的特定列并将它们作为存储过程 2 的输入
问题描述
外部存储过程为我提供产品名称和所需的原材料,内部存储过程为我提供仓库中存储过程的数量。(例如:当我在外部存储过程中搜索巧克力(即 name_grey)时,我得到原料列用于制作巧克力(即)牛奶和糖。现在我想将牛奶和糖作为内部存储过程的输入。(其中 yarnstock1 是我的内部存储过程)我该怎么做?
我试图在外部存储过程中调用完整的内部存储过程。在这里,当我将输入巧克力提供给外部存储过程时,我得到了适当的列详细信息,并且我得到了我不想要的所有原材料的详细信息。
----------------------整个存储过程 -------------- --------
CREATE DEFINER=`root`@`localhost` PROCEDURE `finalgrey`(IN name_grey varchar(250))
BEGIN
SELECT
orderDate,
productname,
warpyarnid,
warpyarn ,
sum(warpneeded * meters_left) as warpneeded ,
weftyarnid,
weftyarn,
sum(weftneeded * meters_left) as weftneeded,
sum(order_quantity), sum(invoice_quantity), sum(meters_left)
FROM
(SELECT
s.orderDate,s.orderToDate,
s.orderid AS saudaid,
c.orderId AS challanid,
pm.productname,
qc.warpyarnid,
pwarp.productname AS warpyarn,
(warperwt / 100) AS warpneeded,
qc.weftyarnid,
pweft.productname AS weftyarn,
(weftwt / 100) AS weftneeded,
(sum(s.totalQuantity)/count(i.invoiceId)) AS order_quantity,
sum(i.totalQuantity) AS invoice_quantity,
IF(((sum(s.totalQuantity)/count(i.invoiceId)) - SUM(i.totalQuantity)) <0, "0", ((sum(s.totalQuantity)/count(i.invoiceId)) - SUM(i.totalQuantity)) ) as meters_left
FROM
sauda AS s
LEFT JOIN
challan c ON s.orderid = c.orderId
LEFT JOIN
invoice AS i ON c.challanid = i.challanid
LEFT JOIN
productmaster pm ON pm.productmasterid = s.itemId
LEFT JOIN
qualitymaster q ON q.productid = pm.productmasterid
LEFT JOIN
qualityconfig qc ON qc.qualityid = q.qualitymasterid and qc.active = 0
LEFT JOIN
productmaster pweft ON pweft.productmasterid = qc.weftyarnid
LEFT JOIN
productmaster pwarp ON pwarp.productmasterid = qc.warpyarnid
WHERE
s.active = 0 AND i.active = 0
AND c.active = 0
AND s.orderDate >= '2018-04-01' and s.orderType = 'Sales' and orderToDate >= CURDATE()
group by s.orderid ) xcx
where productname LIKE CONCAT('%', name_grey , '%')
group by productname;
call yarnstock1('');
END
---------------------------------inner stored procedure-----------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `yarnstock1`(in warp_yarn varchar(250))
BEGIN
select sum(itna_aayega) , itemId ,productmasterid,productname,sum(itna_bacha)
from
(select s.orderId,s.orderDate,s.orderToDate,s.itemId,py.purchaseyarnid, iys.inwardyarnstockid,
iys.purchaseorderid,(s.totalQuantity),sum(iys.totalkgs) as itna_aaya, pm.productmasterid,pm.productname,
CASE WHEN orderToDate<current_date() THEN "0"
WHEN orderToDate>current_date() AND s.totalQuantity < sum(iys.totalkgs) THEN "0"
ELSE s.totalQuantity - sum(iys.totalkgs) END AS itna_aayega,
if(orderToDate<current_date(),"0", (sum(iys.finalkgs) * 1)) as itna_bacha #group_concat(iys.totalkgs)
from sauda s
LEFT JOIN purchaseyarn py
ON s.orderId = py.purchaseorderid
LEFT JOIN inwardyarnstock iys
on py.purchaseyarnid = iys.purchaseyarnid
left join productmaster pm on s.itemId = pm.productmasterid
where iys.active =0 and s.active =0 and py.active = 0 and s.orderDate >= '2018-04-01' and s.type = 'yarn' and s.orderType = 'purchase'
group by s.orderId) xxx
where productname LIKE CONCAT( warp_yarn, '%')
group by itemId ;
END[]
解决方案
如果要向/从另一个存储过程传递/接收表结构,可以使用临时表。临时表在整个连接中可见,在存储过程中创建的临时表对同一连接中的其他存储过程可见。
推荐阅读
- java - 在while循环中替换字符串的最佳方法是什么
- sas - 如何通过避免符号来计算相同的数字?
- ios - 当前位置蓝点显示在标记下方
- c++ - 针对给定问题的优化算法?
- python - 在二维列表中搜索一维列表的元素
- python - 将 os.system 函数的输出传递给另一个用户定义函数
- android - 在没有 Google Play 商店的情况下更新客户端的 Android 应用程序
- asp.net - Asp.Net Core 使用 Cookie 授权和 JWT 授权
- reactjs - 有没有办法在反应中包含 index.jsp 而不是 index.html
- python - 从 numpy 数组加载变量。张量流 2.0