Oracle SQL*Plus ACCEPT Statements

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开发者_JS百科;
/

-- 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.

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.

Using the whenever SQL*Plus command, you can cause SQL*Plus to exit when an error occurs in SQL or PL/SQL. This means that all you would need to do is raise a custom exception to force the script to exit.

WHENEVER SQLERROR EXIT;
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
         raise_application_error(-2000,'Your error Message here');
     END IF;
END;
/

The quick answer is "Not Really". SQL*Plus is a pretty simple client. It can throw a query or PL/SQL block to the server and show a returning dataset, but SQL*Plus (not SQL or PL/SQL) doesn't have plain conditional statements like IF or iterative LOOP structures.

Rather than using SQL*Plus, you should consider a scripting language like Perl or Python.

Why not simply switch the condition?

 IF v_name <> '&p_cname' THEN
        -- Do whatever you want
 END IF;

That way nothing will happen if v_name does match the input because there is no ELSE part

Gary is correct you will need to script up the solution.

something like this under linux

uid="userid"
pwd="password"
echo "enter name:"
read name

dbname=`sqlplus -s <<EOF
$uid/$pwd @part1.sql $name
EOF`

if [$dbname == $name ] 
then
    echo name found
    exit
fi

in your script you can use &1 to get the input parameter (I would also limit the results to 1) e.g:

set heading off
set pagesize 0
set tab off

SELECT cname
FROM customer
    WHERE cname = '&1' and rownum <2; 
/
exit
# add other reads & execure part2,3, etc