Oracle PL SQL中的ACCEPT语句 [英] ACCEPT statement in Oracle PL SQL

问题描述

我是PL SQL的初学者.

I am a beginner in PL SQL.

我正在尝试使用PL SQL ACCEPT语句接受变量中的字符串. 这是代码-

I'm trying to accept a string in a variable using the PL SQL ACCEPT statement. Here is the code -

ACCEPT lastname CHAR FORMAT 'A20' PROMPT 'Enter employee lastname:  '
DECLARE
BEGIN

DBMS_OUTPUT.PUT_LINE(lastname);  
END;

我在SQL Developer中没有收到任何错误或输出.我不明白我在这里想念什么.

I'm not getting any error or output in SQL Developer. I am not able to understand what am I missing here.

基本上，我想做的是从用户读取一个值(字符串)，并将其用于对表的查询中.

Basically, what I am trying to do is read a value (String) from a user and use that in my queries against a table.

推荐答案

ACCEPT是 SQL * Plus和SQL Developer客户端命令，而不是PL/SQL命令.您正在设置一个替换变量，您可以使用在带有以下内容的匿名块中:

ACCEPT is a SQL*Plus and SQL Developer client command, not a PL/SQL command. You are setting a substitution variable, which you could use in an anonymous block with:

ACCEPT lastname CHAR FORMAT 'A20' PROMPT 'Enter employee lastname:  '

SET serveroutput on;

BEGIN
  DBMS_OUTPUT.PUT_LINE('&lastname');  
END;
/

尽管在SQL中使用它会更常见:

It would be more common to use it in SQL though:

select '&lastname' from dual;

PL/SQL并非设计为可交互使用，但不清楚您将要真正做什么.

PL/SQL isn't designed to be used interactively, but it isn't clear what you will really be doing.

SQL Developer(至少为4.1.3版)似乎不能像处理SQL * Plus一样处理format，这可能是一个错误.如果您使用问题和上面的代码所示的ACCEPT，则脚本中将不会显示任何提示或输出；如果您使用接受"，则不会显示任何提示或脚本.日志记录窗格显示来自Accept.java:341的严重"消息.如果您还提供了默认值，它确实可以工作:

SQL Developer (version 4.1.3, at least) doesn't seem to handle format quite the same as SQL*Plus, which may be a bug. If you use ACCEPT as shown in the question and the code above there is no prompt or output from the script; the logging pane shows a 'severe' message from Accept.java:341. It does work if you also supply a default:

ACCEPT lastname CHAR FORMAT 'A20' DEFAULT 'dummy' PROMPT 'Enter employee lastname:  '

默认值未显示在提示对话框中，如果您仅单击对话框，则替换变量将包含该默认值-这是如果未给出答复"的预期行为.如果您不希望使用默认值，则可能会遇到麻烦-指定空默认值(使用'')也会收到相同的严重消息，这可能是相关的.

The default value is not shown in the prompt dialog box, and your substitution variable will contain that default value if you just OK the dialog - which is the expected behaviour "if a reply is not given". If you don't want a default you're a bit stuck though - specifying a null default (with '') also gets the same severe message, which may be related.

这篇关于Oracle PL SQL中的ACCEPT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！