Reset Variable Value with prompt SQL - Stack Overflow

admin管理员组

文章数量:1122832

I am new to using SQL Developer and I have tried Googling but nothing seems to be working for me. I am trying to use a variable twice in a statement and only be prompted once for the variable. However when I try using && even if I add undefine. The code will run twice for me asking for the variable but after the second time it keeps reusing the last given variable This is the code that has gotten me close:

UNDEFINE PayeeNumber
select *
from policytran
where comm1payee = &&PayeeNumber or comm2payee = &&PayeeNumber

I am new to using SQL Developer and I have tried Googling but nothing seems to be working for me. I am trying to use a variable twice in a statement and only be prompted once for the variable. However when I try using && even if I add undefine. The code will run twice for me asking for the variable but after the second time it keeps reusing the last given variable This is the code that has gotten me close:

UNDEFINE PayeeNumber
select *
from policytran
where comm1payee = &&PayeeNumber or comm2payee = &&PayeeNumber

• sql
• variables
• oracle-sqldeveloper

Share Improve this question Follow asked Nov 21, 2024 at 21:38 Robert ZRobert Z 1 2

• Remove UNDEFINE PayeeNumber or replace it with ACCEPT PayeeNumber PROMPT 'PayeeNumber: ' – keithwalsh Commented Nov 21, 2024 at 22:18
• Are you running this as a script, or just running the select as a statement on its own? – Alex Poole Commented Nov 21, 2024 at 23:08

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Generally speaking, undefine should work. This is a SQL*Plus demo.

Sample table:

SQL> select * From policytran;

COMM1PAYEE COMM2PAYEE NAME
---------- ---------- -----
         1        100 Mike
         2        100 Scott
         3        300 Jason

Query which uses substitution variable (the same as yours):

SQL> select *
  2  from policytran
  3  where comm1payee = &&payeenumber or comm2payee = &&payeenumber;
Enter value for payeenumber: 1

COMM1PAYEE COMM2PAYEE NAME
---------- ---------- -----
         1        100 Mike

Re-running the same statement returns the same data because of the && usage:

SQL> /

COMM1PAYEE COMM2PAYEE NAME
---------- ---------- -----
         1        100 Mike

Undefining it; query works as expected (it asks for the value):

SQL> undefine payeenumber
SQL> /
Enter value for payeenumber: 100

COMM1PAYEE COMM2PAYEE NAME
---------- ---------- -----
         1        100 Mike
         2        100 Scott

SQL>

---

On the other hand, that query could be rewritten so that you don't use the variable twice.

Undefining it first:

SQL> undefine payeenumber

A new query:

SQL> select *
  2  from policytran
  3  where &payeenumber in (comm1payee, comm2payee);
Enter value for payeenumber: 100

COMM1PAYEE COMM2PAYEE NAME
---------- ---------- -----
         1        100 Mike
         2        100 Scott

Re-running it asks for the value:

SQL> /
Enter value for payeenumber: 3

COMM1PAYEE COMM2PAYEE NAME
---------- ---------- -----
         3        300 Jason

SQL>

本文标签： Reset Variable Value with prompt SQLStack Overflow