PL/SQL：如何在过程中提示用户输入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32551087/
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
PL/SQL: how do i prompt user input in a procedure?
提问by user3120554
This is a question about a small part of a large project I'm doing. I tried the following but I just get the two errors below it:
这是关于我正在做的一个大型项目的一小部分的问题。我尝试了以下操作，但我只收到下面的两个错误：
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE HELLO AS
DECLARE
variable1 NUMBER(1);
variable2 CHAR(1);
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
variable1 := &please_enter_1_or_0;
variable2 := &please_enter_y_or_n;
END;
/
Error(2,5): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.
Error(10,8): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with
<< continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall merge pipe purge
错误（2,5）：PLS-00103：在期望以下之一时遇到符号“DECLARE”：开始函数编译指示过程子类型当前光标删除存在先前外部语言符号“开始”被替换为“DECLARE”以继续.
错误（10,8）：PLS-00103：在期望以下之一时遇到符号“文件结束”：（如果循环mod null pragma raise return select update while with
<<继续关闭当前删除获取锁定
插入打开回滚保存点设置 sql 执行 commit forall 合并管道清除
We were given a markscheme of how our code would be marked, and for this section, the relevant criteria would be: "Does the script use a procedure?" and "Does the script prompt for right/wrong and team/individual and handle the data provided correctly?".
我们获得了如何标记我们的代码的标记方案，对于这一部分，相关标准是：“脚本是否使用过程？” 和“脚本是否提示正确/错误和团队/个人并正确处理提供的数据？”。
The project brief quotes "Develop a procedure that prompts for RIGHT/WRONG (using &), then updates table" (where table is the name of a table
项目简介引用“Develop a procedure that prompts RIGHT/WRONG (using &), then updates table”（其中 table 是表的名称
The purpose of the variables was to update an existing record attribute. i.e. if user chose 1 and n then update the null in the record to 2. if it was 1 and y then update to 1, and if 0 and y/n then update to 0
变量的目的是更新现有的记录属性。即，如果用户选择 1 和 n 然后将记录中的空值更新为 2。如果它是 1 并且 y 则更新为 1，如果 0 和 y/n 则更新为 0
回答by APC
PL/SQL is a language for writing autonomous programs. It is not designed for user interactivity. Input values are passed as parameters. So your program should look like this
PL/SQL 是一种用于编写自主程序的语言。它不是为用户交互而设计的。输入值作为参数传递。所以你的程序应该是这样的
CREATE OR REPLACE PROCEDURE HELLO
(p1 in number
, p2 in varchar2)
AS
l_salutation varchar2(20) := 'Hello World';
BEGIN
DBMS_OUTPUT.PUT_LINE(l_salutation);
DBMS_OUTPUT.PUT_LINE('p1 = ' || p1);
DBMS_OUTPUT.PUT_LINE('p2 = ' || p2);
END;
/
Note there is no need for DECLARE with a named Procedure. The section between AS and BEGIN is for declaring variables, as I've done with l_salutation.
请注意，不需要带有命名过程的 DECLARE。AS 和 BEGIN 之间的部分用于声明变量，就像我对l_salutation.
You can provide values for those parameters when invoking the program. In SQL*Plus it would work like this:
您可以在调用程序时为这些参数提供值。在 SQL*Plus 中，它会像这样工作：
SET SERVEROUTPUT ON
accept p1 prompt "please enter 1 or 0: "
accept p2 prompt "please enter Y or N: "
exec HELLO (:p1, :p2)
回答by Alessandro Rossi
This piece of code works only in SQL*Plus and can't be used to produce a stored procedure!!!
这段代码只适用于SQL*Plus，不能用于生成存储过程！！！
DECLARE
variable1 NUMBER(1);
variable2 CHAR(1);
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
variable1 := &please_enter_1_or_0;
variable2 := '&please_enter_y_or_n';
END;
Mind the difference in the last statement where the last substitution variable is quoted in a string to be properly accepted by the PL/SQL syntax. Anyway, as I told you in the last comment to your question this is not a user interaction but just the result of a statement preprocessing. Every time you input different values the RDBMS executes a different source code.
请注意最后一个语句中的差异，其中最后一个替换变量在字符串中被引用，以便 PL/SQL 语法正确接受。无论如何，正如我在对您的问题的最后一条评论中告诉您的，这不是用户交互，而只是语句预处理的结果。每次输入不同的值时，RDBMS 都会执行不同的源代码。
Probably your requirement to use a "procedure" doesn't meant to use a STORED procedure(that is impossible to do so), but they just intended a SQL*Plus script, ask for clarifications.
可能您对使用“过程”的要求并不意味着使用 STORED 过程（这是不可能的），但他们只是打算使用 SQL*Plus 脚本，请澄清。
回答by David Aldridge
You cannot directly receive messages from the client in a PL/SQL procedure or package.
您不能直接从 PL/SQL 过程或包中的客户端接收消息。
The best you can do to emulate this is to interface with table data, and have users insert data into the table and react to that, or use Advanced Queueing (which amounts to pretty much the same thing).
模拟这一点的最佳方法是与表数据交互，让用户将数据插入表中并对此做出反应，或者使用高级队列（这几乎是相同的事情）。
Alternatively, accept the user input as parameters when the procedure is called.
或者，在调用过程时接受用户输入作为参数。
回答by Jeremy
You can just Remove the declare to remedy that ora error
您可以删除声明来纠正该 ora 错误
