PROCEDURE p_getsumsorder (p_sordor_no IN VARCHAR2, p_cur OUT curtype) IS BEGIN OPEN p_cur FOR SELECT b.product_no, SUM (b.product_qty) AS product_qty, b.product_unit, product_name, pdt_color, sale_price FROM oms.ptl_sorder_detail b JOIN oms.ptl_sorder_head c ON b.sorder_no = c.sorder_no JOIN oms.oms_product d ON b.product_no = d.product_no WHERE c.sorder_no in (p_sordor_no) GROUP BY b.product_no, b.product_unit, product_name, pdt_color, sale_price; 存储过程!public DataTable getSumOrder(string order_no) { _SP = "scm.pk_agent_mgt.p_getsumsorder"; _spParameters = new Object[] { order_no, DBNull.Value }; return ExecuteDataTable(); }这是代码!
这样试试看看 p_getsumsorder('1234,4567',outvar);
SELECT b.product_no, SUM (b.product_qty) AS product_qty, b.product_unit, product_name, pdt_color, sale_price FROM oms.ptl_sorder_detail b JOIN oms.ptl_sorder_head c ON b.sorder_no = c.sorder_no JOIN oms.oms_product d ON b.product_no = d.product_no WHERE c.sorder_no in (' S101958823',' S101958824') GROUP BY b.product_no, b.product_unit, product_name, pdt_color, sale_price;传进来应该要是这样的!我程序字符串是string str="' S101958823',' S101958824'";这样传进去的
你要是用 IN 的话,我建议把过程中的SQL语句改成动态的,好像传值只能用比较操作。
var_sql:='SELECT b.product_no, SUM (b.product_qty) AS product_qty, b.product_unit, product_name, pdt_color, sale_price FROM oms.ptl_sorder_detail b JOIN oms.ptl_sorder_head c ON b.sorder_no = c.sorder_no JOIN oms.oms_product d ON b.product_no = d.product_no WHERE c.sorder_no in ('||p_sordor_no||') GROUP BY b.product_no, b.product_unit, product_name, pdt_color, sale_price';
IS
BEGIN
OPEN p_cur FOR
SELECT b.product_no, SUM (b.product_qty) AS product_qty,
b.product_unit, product_name, pdt_color, sale_price
FROM oms.ptl_sorder_detail b JOIN oms.ptl_sorder_head c
ON b.sorder_no = c.sorder_no
JOIN oms.oms_product d ON b.product_no = d.product_no
WHERE c.sorder_no in (p_sordor_no)
GROUP BY b.product_no,
b.product_unit,
product_name,
pdt_color,
sale_price;
存储过程!public DataTable getSumOrder(string order_no)
{
_SP = "scm.pk_agent_mgt.p_getsumsorder";
_spParameters = new Object[] { order_no, DBNull.Value };
return ExecuteDataTable();
}这是代码!
p_getsumsorder('1234,4567',outvar);
b.product_unit, product_name, pdt_color, sale_price
FROM oms.ptl_sorder_detail b JOIN oms.ptl_sorder_head c
ON b.sorder_no = c.sorder_no
JOIN oms.oms_product d ON b.product_no = d.product_no
WHERE c.sorder_no in (' S101958823',' S101958824')
GROUP BY b.product_no,
b.product_unit,
product_name,
pdt_color,
sale_price;传进来应该要是这样的!我程序字符串是string str="' S101958823',' S101958824'";这样传进去的
FROM oms.ptl_sorder_detail b JOIN oms.ptl_sorder_head c ON b.sorder_no = c.sorder_no
JOIN oms.oms_product d ON b.product_no = d.product_no
WHERE c.sorder_no in ('||p_sordor_no||') GROUP BY b.product_no, b.product_unit, product_name, pdt_color, sale_price';
execute IMMEDIATE var_sql
OPEN p_cur FOR var_sql