在pl/sql中的BEGIN块中使用ACCEPT [英] Using ACCEPT within a BEGIN block in pl/sql

问题描述

我正在尝试在我的BEGIN块中使用ACCEPT，但我不断收到错误消息.如果if条件被触发，我想接受用户输入.我怎样才能解决这个问题?下面是我的脚本:

I am trying to use ACCEPT in my BEGIN block but I keep getting errors. I want to accept user input if the if condition is fired. How can I fix this? Below is my script:

ACCEPT ExpNum NUMBER PROMPT 'Enter an expense number:  ';

SELECT *
FROM ExpByCC
WHERE ExpNum = &ExpNum;

SELECT IsCash,CashAmt
FROM EXPMAST
WHERE ExpNum = &ExpNum
AND IsCash = 'Y';

ACCEPT CashAmount PROMPT 'Enter the amount of cash used for the expense:  ';

DECLARE
    CashTotal NUMBER(11,2);

BEGIN
    SELECT SUM(Amt)
    INTO CashTotal
    FROM EXPDET
    WHERE ExpNum = &ExpNum;

    IF &CashAmount < (CashTotal - 0)
    THEN
            ACCEPT CType PROMPT 'Enter a credit card type:  ';
            ACCEPT CCNum PROMPT 'Enter a credot card number:  ';
            ACCEPT Amt Prompt 'Enter an amount:  ';


    END IF;

END;
/

推荐答案

ACCEPT是SQL * Plus命令.客户端(SQL * Plus)在将代码发送到数据库之前对其进行解释. PL/SQL是专门在服务器上运行的语言.因此，您不能将两者交织在一起.您不能将SQL * Plus命令嵌入PL/SQL块中.通常，PL/SQL块不能与用户交互.

ACCEPT is a SQL*Plus command. It is interpreted by the client (SQL*Plus) before code is sent to the database. PL/SQL is a language that runs exclusively on the server. You cannot, therefore, intertwine the two. You cannot embed SQL*Plus commands inside a PL/SQL block. As a general rule, PL/SQL blocks cannot interact with users.

如果您想真正深入了解杂草，可以在SQL * Plus脚本中构建相当复杂的分支逻辑，以便运行仅包含三个ACCEPT语句的第二个脚本当且仅当&CashAmount < (CashTotal - 0)时，但这很少是一种适当的方法.那可能涉及创建一个新的SQL * Plus变量，例如next_script，将该变量作为块的一部分进行填充，然后在脚本中动态调用next_script.但是，这不是构建可靠，易于维护的可靠系统的方法.

If you want to get really, really down in the weeds, it would possible to build a rather complicated bit of branching logic into your SQL*Plus script so that a second script with only the three ACCEPT statements would be run if and only if &CashAmount < (CashTotal - 0) but that is rarely an appropriate approach. That would probably involve creating a new SQL*Plus variable called, say, next_script, populating that variable as part of your block, and then dynamically calling next_script in your script. That is not a way, however, to build solid, reliable, easy to maintain systems.

这篇关于在pl/sql中的BEGIN块中使用ACCEPT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！