I am extremely new to SQL, and manage to extract from some other queries we use the following syntax:
--VARIABLES
undefine PF_PROD --product;
undefine PF_PSG --shop;
--QUERY
SELECT *
FROM ET1250
WHERE PRODUCT=&&PF_PROD
AND PRICE_SHOP_GROUP=&&PF_PSG
ORDER BY PERIOD_YEAR desc,PERIOD_WEEK desc;
This works fine as long as I run the undefine statements first, is there a way to make the query always ask for these variables without me having to undefine them first?
Use a single &. This is covered briefly in the SQL Developer documentation:
For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.
... and in more detail in the SQL*Plus documentation, which is largely relevant for both clients.
Note that if you define or accept a variable then it won't be prompted for even with a single ampersand, but that doesn't seem to be relevant to you at the moment.
There are two types of variable in SQL-plus: substitution and bind.
Substitution variables can replace SQL*Plus command options or other hard-coded text:
define a = 1;
select &a from dual;
undefine a;
Bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result setsb:
var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from from dual;
exec print x;
SQL Developer support substitution variables, but when you execute query with bind :var syntax you are prompted for binding (in dialog box).
Reference:
