Oracle SQL * Plus ACCEPT语句 [英] Oracle SQL*Plus ACCEPT Statements

问题描述

我需要一点帮助我的SQL * Plus脚本。是否有任何方式我可以有它，以便它将接受一个变量，检查表匹配，如果它发现退出程序，而不继续其余的接受语句？

I need a bit of help with my SQL*Plus script. Is there any way I could have it so that it will accept a variable, check the table for a match and if it finds it exit the program and not continue with the rest of the accept statements?

我有这个代码到目前为止：

I have this code so far:

ACCEPT p_cname PROMPT 'Enter Customer Name: '
DECLARE
     v_cname CHAR(20);
BEGIN
     SELECT cname INTO v_cname
            FROM customer
     WHERE cname = '&p_cname';

     IF v_name = '&p_cname' THEN
            -- Exit the program
     END IF;
END;
/

-- Other ACCEPT statements if a match was not found.

如果找到匹配项，我不希望它继续执行程序的其余部分。有什么办法吗？

I don't want it to continue with the rest of the program if the match is found. Is there any way of doing this?

推荐答案

你可以通过启用错误检查然后引发一个错误。

You can do it by enabling error-checking then raising an error.

ACCEPT p_cname PROMPT 'Enter Customer Name: '

WHENEVER SQLERROR EXIT SUCCESS ROLLBACK;

DECLARE
     v_count  INTEGER;
BEGIN
     SELECT COUNT(*) INTO v_count
            FROM customer
     WHERE cname = '&p_cname';

     IF v_count > 0 THEN
            raise_application_error( -20100, 'Customer already exists' );
     END IF;

END;
/

-- Issue a new WHENEVER statement here if you want different error-handling for
-- the rest of the script

-- Other ACCEPT statements if a match was not found.

在 WHENEVER 命令中， SUCCESS 关键字意味着SQLPlus将返回一个成功代码回到调用它的shell。您还可以使用 FAILURE 返回通用失败代码或其他选项返回特定值。

In the WHENEVER command, the SUCCESS keyword means that SQLPlus will return a success code back to the shell from which it was invoked. You can also use FAILURE to return a generic failure code, or other options to return specific values.

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