oracle PLSQL 过程在运行时获取用户输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13683939/
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
PLSQL Procedure get user input at runtime
提问by ToniHopkins
Im doing a college assignment that requires me to create a PLSQL procedure that where the user can add a new customer order containing a number of items and the quantity for each item. I came up with the following that would ask the user to input a number of items to be added to the order and would then use a loop to ask for specific details such as product no and quantity. Im having problems with the user input at runtime though... When compiling the code it asks for the product code and quantity and wont ask again at runtime instead it saves the values given earlier at compile...
我在做一个大学作业，需要我创建一个 PLSQL 过程，用户可以在其中添加一个新的客户订单，其中包含多个项目和每个项目的数量。我想出了以下内容，要求用户输入要添加到订单中的一些项目，然后使用循环来询问特定的详细信息，例如产品编号和数量。我在运行时遇到了用户输入的问题......在编译代码时，它会询问产品代码和数量，并且不会在运行时再次询问，而是保存先前在编译时给出的值......
CREATE OR REPLACE
PROCEDURE Add_Order (Item_amount IN NUMBER, CustNo IN NUMBER) AS
ItemNo NUMBER;
var_Quantity NUMBER;
var_PONo NUMBER;
BEGIN
IF Item_amount BETWEEN 2 AND 9 THEN
SELECT seq_PONo.NEXTVAL INTO var_PONo from dual;
INSERT INTO PurchaseOrder_tab
SELECT var_PONo, REF(C),
SYSDATE,
ItemList_tab()
FROM Customer_tab C
WHERE C.CustNo = CustNo;
FOR i IN 1..Item_amount LOOP
DBMS_OUTPUT.PUT_LINE('INSIDE LOOP');
ItemNo := &Enter_ProdCode;
var_Quantity := &Quantity_Amount;
INSERT INTO TABLE (
SELECT P.ItemList
FROM PurchaseOrder_tab P
WHERE P.PONo = var_PONo
)
SELECT seq_ItemNo.nextval, REF(Pr), var_Quantity
FROM Products_tab Pr
WHERE Pr.ProductCode = ItemNo ;
DBMS_OUTPUT.PUT_LINE('Added '||var_Quantity ||' items of '||ItemNo||' to order No: '||var_PONo);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('Amount of items entered onto an order must be between 2 - 9. Please try again with correct amount.');
END IF;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed '||'SQLCODE: '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQL Error Message '||SQLERRM);
ROLLBACK;
END;
/
回答by Erich Kitzmueller
Short answer: you can't. PL/SQL is executed inside the database engine, and the database engine has no access to the terminal window (or database tool) you are using to start the procedure.
简短的回答：你不能。PL/SQL 在数据库引擎内部执行，数据库引擎无法访问您用来启动该过程的终端窗口（或数据库工具）。
The code in your quesion seems to partially work, because it asks for input once, but what really happens is that: the tool (SQL*Plus, SQL Developer or whatever) parses over the PL/SQL block and sees the &-Signs, so it asks what to replace them with. Once the input is given, the PL/SQL-Block - including the entered values - is given to the database for execution.
您问题中的代码似乎部分工作，因为它要求输入一次，但真正发生的是：工具（SQL*Plus、SQL Developer 或其他）解析 PL/SQL 块并看到 &-Signs，所以它询问用什么替换它们。一旦给出输入，PL/SQL-Block——包括输入的值——就会被提供给数据库执行。
Since you can't do that in PL/SQL, better create a front-end program first that collects the values, then sends them to the database.
由于您无法在 PL/SQL 中执行此操作，因此最好先创建一个前端程序来收集值，然后将它们发送到数据库。
回答by jorj
Try to play around with :
尝试玩弄：
BEGIN
DBMS_OUTPUT.GET_LINE(:buffer, :status);
END;
Instead of using &refferences
而不是使用&参考
回答by vini
To Get data input from the USer,
从用户获取数据输入，
SET SERVEROUTPUT ON;
设置服务器输出；
ACCEPT Enter_ProdCode VARCHAR2 PROMPT "Please enter Product code : "; ItemNo := &Enter_ProdCode;
ACCEPT Enter_ProdCode VARCHAR2 PROMPT "请输入产品代码："; ItemNo := &Enter_ProdCode;
