mysql - 通过 mysql 存储过程的 'where clause' 中的未知列

Question

我正在使用带有准备好的语句的存储过程来从字符串中查找行。

看起来像这样

CREATE DEFINER=`y0y0`@`%` PROCEDURE `SP_GEN_CASH_SOD_TXT_FILE`(IN oRETURNNO varchar(20))
BEGIN
    SET @returnNo = oRETURNNO;
    SET @sitePath = (select site_path from sap_transfer_data WHERE payer_payment_type = 'C' and trans_status ='I' and return_no = @returnNo LIMIT 1);
    SET @outputPath = CONCAT("/cash/","SAP_",@sitePath,"_SP_",@returnNo,"_", date_format(CURDATE(), '%Y%m%d'),".txt");

    SET @row_number = 0;
    SET @sqlCommand = CONCAT('SELECT "IsCashYesOrNo^Transaction type^Line No.^SAP Sales Organization^Date of Receive^HIS Unique ID^Paycode code (Internal)^Lab Number^Test Item Code^Test Item Description^Sequence^Customer Material No.^Lab site code^Sent Test^Lab Department Code^Lab Department Name^Hospital Number^Address2^IN/OUT^Priority^Auto Add TC^Patient first name^Patient last name^Patient address^Hospital Department^Hospital Location code^Hospital Location description^Quantity^UOM^Ref. No.^Date of Enter^Pathologist Code 1^Pathologist Level 1^Pathologist Code 2^Pathologist Level 2^Pathologist Code 3^Pathologist Level 3^Pathologist Code 4^Pathologist Level 4^Pathologist Code 5^Pathologist Level 5^Pathologist Code 6^Pathologist Level 6^Pathologist Code 7^Pathologist Level 7^Pathologist Code 8^Pathologist Level 8" as texts from dual
    UNION ALL
    SELECT CONCAT("Yes", "^", trans_status, "^", CAST((@row_number:=@row_number + 1) as char), "^", "0105", "^", return_date, "^", return_no, "|",  item_code, "|", "1", "^", payer_ship_to, "^", return_no, "^", item_code, "^",
    "^", "1", "^", "^", lab_site_code, "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", cust_code, "^", cust_name, "^", return_qty, "^", "^", ref_req_no, "^",  
    "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^") as texts 
    INTO OUTFILE ', char(39), @outputPath, char(39),
    ' LINES TERMINATED BY ', char(39),'\r\n', char(39),
    ' FROM sap_transfer_data
    WHERE payer_payment_type = "C" and trans_status = "I" and return_no = ', @returnNo);

    prepare s1 from @sqlCommand;
    execute s1; deallocate prepare s1;

    update sap_transfer_data set delivery_date = CURDATE(), trans_fag = true where return_no = @returnNo;
END

然后我使用以下方法调用该过程:

call SP_GEN_CASH_SOD_TXT_FILE ('RT20190101039354');

但是，我收到此错误:

Error Code: 1054. Unknown column 'RT20190101039354' in 'where clause'

您有什么想法可以建议我吗？

提前谢谢您。 ^_^

Answer 1

SET @sqlCommand = CONCAT('......WHERE payer_payment_type = "C" and trans_status = "I" and return_no = ', @returnNo);

对于 @returnNo = 'RT20190101039354'，这使得 @sqlCommand 的结尾看起来像

WHERE payer_payment_type = "C" and trans_status = "I" and return_no = RT20190101039354

因此引擎认为它是一个列名称。

您想要 a) 停止在字符串文字周围使用双引号，而仅使用单引号，b) 特别将您的代码更改为:

SET @sqlCommand = CONCAT('...
...
WHERE payer_payment_type = ''C'' and trans_status = ''I'' and return_no = ?');

execute s1 USING @returnNo; deallocate prepare s1;

问号是一个占位符，通过使用USING子句来EXECUTE填充正确的值。