oracle 如何在 SQL*Plus 中将参数（值）从 Function 传递/调用到“Select”stmt？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10503226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass/call parameters (values) from Function to 'Select' stmt in SQL*Plus?
提问by valmont74
I have a problem passing parameters from function that I created into SQL*Plus 'Select' statement in the 'Where' clause. However, it seems that whatever syntax I utilized is either incorrect or not to use at all in this 'Where' clause situation.
我在将参数从我创建的函数传递到 SQL*Plus 的“Where”子句中的“Select”语句中时遇到问题。但是，在这种“Where”子句情况下，我使用的任何语法似乎都不正确或根本不使用。
Here is my function I created and it looks OK being compiled and ran good:
这是我创建的函数，它看起来可以编译并运行良好：
CREATE OR REPLACE FUNCTION SA_BILL_CNTRL_EXTRNL_FXN(p_client_code VARCHAR2
,p_bill_seq VARCHAR2
,p_bill_control_seq VARCHAR2)
RETURN VARCHAR2 IS
v_external_id VARCHAR2(30);
v_client_code VARCHAR2(4) := p_client_code;
v_bill_seq NUMBER := to_number(p_bill_seq);
v_bill_control_seq NUMBER := to_number(p_bill_control_seq);
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM BillControl@SQL_SAREP bc
WHERE bc."ExternalID" IS NOT NULL
AND bc."ExternalID" != ''
AND bc."ClientCode" = p_client_code
AND bc."BillControlSeq" = v_bill_control_seq
AND bc."BillSeq" = v_bill_seq;
IF v_cnt = 1 THEN
SELECT bc."ExternalID"
INTO v_external_id
FROM BillControl@SQL_SAREP bc
WHERE bc."ExternalID" IS NOT NULL
AND bc."ExternalID" != ''
AND bc."ClientCode" = p_client_code
AND bc."BillControlSeq" = v_bill_control_seq
AND bc."BillSeq" = v_bill_seq;
ELSE
v_external_id := NULL;
END IF;
RETURN v_external_id;
END SA_BILL_CNTRL_EXTRNL_FNX;
/
Also, when I used 'IN OUT' parameter in function it failed after running it.
此外，当我在函数中使用“IN OUT”参数时，它在运行后失败。
Now, here is the 'Select' statement I'm having difficulty passing parameters with:
现在，这是我在传递参数时遇到困难的“选择”语句：
SELECT NVL(TRIM(eb.CASE_NUM_REEVALUATED), TRIM(eb.CASE_NUM_DUPLICATED)) CaseNumReev
,gnc.BILL_CONTROL_SEQ
FROM EPE_Bill eb NETWORK_CROSSWALK gnc
WHERE substr(eb.MIC_BILL_ID, 5, 4) = SA_BILL_CNTRL_EXTRNL_FNX(p_client_code)
AND gnc.BILL_CONTROL_SEQ = SA_BILL_CNTRL_EXTRNL_FNX(p_bill_seq)
AND (to_number(substr(eb.MIC_BILL_ID, 10, 10), '9999999999') = SA_BILL_CNTRL_EXTRNL_FNX(p_bill_control_seq)
GROUP BY CaseNumReev, gnc.BILL_CONTROL_SEQ;
For the last two days I was left myself thoroughly perflexed. With that, I would really appreciate if you help me with issue.
在过去的两天里，我让自己彻底不知所措。有了这个，如果你能帮我解决问题，我将不胜感激。
Thanks again...
再次感谢...
回答by psaraj12
Try the below function,ALso i made assumption that since you are passing only one parameter the remaining must be NULL
试试下面的函数，所以我假设因为你只传递一个参数，剩下的必须是 NULL
CREATE OR REPLACE FUNCTION SA_BILL_CNTRL_EXTRNL_FXN(p_client_code IN VARCHAR2 DEFAULT NULL
,p_bill_seq IN VARCHAR2 DEFAULT NULL
,p_bill_control_seq IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 IS
v_external_id VARCHAR2(4000);
v_client_code VARCHAR2(4) := p_client_code;
v_bill_seq NUMBER := to_number(p_bill_seq);
v_bill_control_seq NUMBER := to_number(p_bill_control_seq);
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM BillControl@SQL_SAREP bc
WHERE bc."ExternalID" IS NOT NULL
AND bc."ExternalID" != ''
AND bc."ClientCode" = NVL(p_client_code,bc."ClientCode")
AND bc."BillControlSeq" = NVL(v_bill_control_seq,bc."BillControlSeq")
AND bc."BillSeq" = NVL(v_bill_seq,bc."BillSeq");
IF v_cnt = 1 THEN
SELECT bc."ExternalID"
INTO v_external_id
FROM BillControl@SQL_SAREP bc
WHERE bc."ExternalID" IS NOT NULL
AND bc."ExternalID" != ''
AND bc."ClientCode" = NVL(p_client_code,bc."ClientCode")
AND bc."BillControlSeq" = NVL(v_bill_control_seq,bc."BillControlSeq")
AND bc."BillSeq" = NVL(v_bill_seq,bc."BillSeq");
ELSE
v_external_id := NULL;
END IF;
RETURN v_external_id;
END SA_BILL_CNTRL_EXTRNL_FNX;
/
And your SQL SHOULD BE
你的 SQL 应该是
SELECT REC.CaseNumReev,REC.BILL_CONTROL_SEQ FROM
(
SELECT NVL(TRIM(eb.CASE_NUM_REEVALUATED), TRIM(eb.CASE_NUM_DUPLICATED)) CaseNumReev
,gnc.BILL_CONTROL_SEQ AS BILL_CONTROL_SEQ
FROM EPE_Bill eb ,NETWORK_CROSSWALK gnc
WHERE substr(eb.MIC_BILL_ID, 5, 4) = SA_BILL_CNTRL_EXTRNL_FNX(p_client_code)
AND gnc.BILL_CONTROL_SEQ = SA_BILL_CNTRL_EXTRNL_FNX(p_bill_seq)
AND (to_number(substr(eb.MIC_BILL_ID, 10, 10), '9999999999') =SA_BILL_CNTRL_EXTRNL_FNX(p_bill_control_seq))rec
GROUP BY REC.CaseNumReev,REC.BILL_CONTROL_SEQ
