sql - 提示不接受输入并获得编译器错误

Question

我正在尝试使用 Oracle Developer 在 PL/SQL 中编写一个过程，该过程从 PROMPT 获取输入，如果没有输入输入，则使用异常处理程序输出。我的提示窗口显示“请输入 p_first_name 的值”，而不是我在“...”中的值。在提示中输入数据后，出现以下错误：

Error(6,21): PLS-00103: Encountered the symbol "PROMPT" when expecting 
one of the following:     := . ( @ % ; not null range default character 

代码

SET SERVEROUTPUT ON


CREATE PROCEDURE print_name (first_name IN varchar, last_name varchar, 
title varchar) IS

ACCEPT p_first_name PROMPT 'Please enter a first name:'

ACCEPT p_last_name PROMPT 'Please enter a second name:'

ACCEPT p_title PROMPT 'Please enter a second name:'

DECLARE

first_name varchar  :=&p_first_name;
last_name varchar   :=&p_last_name;
title varchar       :=&p_title;


first_null EXCEPTION;
last_null EXCEPTION;
title_null EXCEPTION;

BEGIN



IF first_name IS NULL THEN
raise first_null;
END IF;

IF last_name IS NULL THEN
raise last_null;
END IF;

IF title IS NULL THEN
raise title_null;
END IF;

DBMS_OUTPUT.PUT_LINE(last_name || ',' || first_name || ' ' || title);

EXCEPTION
WHEN first_null THEN
DBMS_OUTPUT.PUT_LINE('You must enter a first name.');

WHEN last_null THEN
DBMS_OUTPUT.PUT_LINE('You must enter a last name.');

WHEN title_null THEN
DBMS_OUTPUT.PUT_LINE('You must enter a title.');

END;
/

输入第一个、最后一个和标题后，我希望打印：

Doe, Jane Ms.

如果任何值为 NULL，我希望异常处理程序能够打印。

到目前为止，我只收到以下错误：

Error(6,21): PLS-00103: Encountered the symbol "PROMPT" when expecting 
one of the following:     := . ( @ % ; not null range default character

标签： sql

Answer 1

尝试这个

    CREATE OR REPLACE PROCEDURE print_name (first_name IN varchar, last_name 
            varchar, title varchar) IS






first_null EXCEPTION;
last_null EXCEPTION;
title_null EXCEPTION;

BEGIN




IF first_name IS NULL THEN
raise first_null;
END IF;

IF last_name IS NULL THEN
raise last_null;
END IF;

IF title IS NULL THEN
raise title_null;
END IF;

DBMS_OUTPUT.PUT_LINE(last_name || ',' || first_name || ' ' || title);

EXCEPTION
WHEN first_null THEN
DBMS_OUTPUT.PUT_LINE('You must enter a first name.');

WHEN last_null THEN
DBMS_OUTPUT.PUT_LINE('You must enter a last name.');

WHEN title_null THEN
DBMS_OUTPUT.PUT_LINE('You must enter a title.');

END;
/     

然后在执行过程时使用提示符作为提示符在外部工作而不是在存储过程中

喜欢

 ACCEPT p_first_name  char PROMPT 'Please enter a first name:';
 ACCEPT p_last_name char PROMPT 'Please enter a second name:';

       ACCEPT p_title_name char  PROMPT 'Please enter a second name:';


 exec print_name('&p_first_name','&p_last_name','&p_title_name');