ORACLE Varaible

1) Bind Variable (is session specific and need to be reset if the session is lost)

 

(note : use F5 to run script, F9 run statement won't work)

VARIABLE v_id NUMBER

BEGIN

  :v_id := 1; (need to end with ;)

END; (need to end with ;)

/ (used in SQL*PLUS)

PRINT v_id

SELECT * FROM products WHERE product_id = :v_id;

--use F5 to invoke the value of bind varialbe directly

--use F9, it will prompt to enter value for bind variable

 

2) Substitution variables

two types : Temporary Variables and Defined variables

Temporary Variables :

SELECT * FROM products WHERE product_id = &v_id;

--use F5 or F9, it will prompt to enter value for substitution variable

 

Defined Variables :

DEFINE v_id=7

SELECT * FROM products WHERE product_id = &v_id;

--use F5 or F9, it will invoke the pre-defined value of variable

ACCEPT variable [TYPE] [FORMAT format] [PROMPT prompt] [HIDE]

ACCEPT v_id NUMBER FROMAT 99 PROMPT 'product id: '

--use F5 only, it will prompt to enter value for the variable

UNDEFINE v_id

 

This variable can be used as : &v_id, &&v_id, '&v_Id' or '&&v_id'

SQL>DEFINE

SQL>SET DEFINE '#'

SQL>SET VERIFY ON

SQL>SET VERIFY OFF