Substitution Variable in SQL


A substitution variable is special type of variable which is automatic declare and automatic destroyed in the system itself advantage of S.V is
        It makes a statement dynamic as because it is declare automatically it can able to store any type of data in run time S.V in 2 diff categories
1.   Statement type
2.   Session type
Statement type 
        It is type of variable which is automatically declares whenever a statement strat exaction and automatically destroyed once the statement is exacted this type of variable is fail by  (&)
Ex - &dno
Session type
        On other hand where type of variable is a variable once declare and declare with a value will return the value unless until exit of *plus and variable is explicitly destroyed this type of denoted by (&&)
Ex - &&dno
Sql> set verify off
Sql>select ename, job, depptno from emp
Where deptno=&deptno;

Note :
Set  verify off – it is a sql *plus comment respond able sures the verification message
Select ename, job, deptno from emp where job=’&job’;
The above example are the example of statement type
Select empno, deptno from emp 
Where dept no= ‘&&deptno’;
Above ex is session type

NOTE:
        Define and Undefined are two sql*plus commands reasonable to control session variable.
Sql>undefined dno;
Above command will destroy substation variable
Sql>define dno=0
Above command is used reassign a substation variable
Reporting through select statement:
        Reporting is a concept through which data will represent in customize manner through query statement. The command mains for reporting as basically sql*plus Command.


Sql>column ename heading name
Sql>column sal heading salary
Above ex are required to provide session type allow
Sql>column sal heading salary format ‘$99.999’;
Sql>column comm. format ‘$99.999’, nul ‘no comm’
Sql>column sal clear
It is used for clear the one column
Sql>clear column
Above sql command used for clear all column without exit sql command
Working with T-TITLE and B-TITLE
Sql>TTITLE ‘report for 2008/2009’;
Sql>BTITLE ’THANK U’;
One ttitle and btitle insert it aplyable for any table in oracle table;
Any table in oracle table to sub press
Sql > TTITLE OFF
SQL> BTITLE OFF


0 Comments