More Commands In Oracle 9i

More Commands In Oracle 9i

Substitution Variables

Temporarily Store Values

- Single ampersand (&)

- Double ampersand (&&)

- DEFINE command

Using the & Substitution Variable

For Integer
select empno,ename from emp where empno = &empno ;

For Character or date
select empno,ename from emp where job = '&job' ;

Specifying Column Names, Expressions and Text
select empno,ename,job,&column_name from emp where &condition order by &Order_column ;

Using DEFINE Command

DEFINE empno=7902 ;
select empno,ename,sal,deptno from emp where empno= &empno ;

Using the && Substitution Variable

select empno,ename,job,&&column_name from emp order by &column_name ;

Using Verify Commands
set verify on ;
select empno,ename,sal,deptno from emp where empno=&empno ;

SQL Sample Report

set feedback off
TTITLE 'EMployee|Report'
BTITLE 'Confidential'
BREAK ON JOB
COLUMN JOB HEADING 'JOB|Cattegory'
COLUMN ename HEADING 'EMPLOYEE NAME'
COLUMN sal HEADING 'SALARY'
select job,ename,sal from emp where sal <>
set feedback on
COLUMN JOB clear
COLUMN ename clear
COLUMN sal clear
clear Break

Using Sub query in an INSERT Statements

insert into (select empno,ename,job,deptno from emp where deptno=20)
values (2222,'ddddd','MANAGER',30) ;

Using Sub query in an INSERT Statements with check option

insert into (select empno,ename,job,deptno from emp where deptno=20 with check option)
values (2222,'ddddd','MANAGER',30) ;

Using Explicit Default Values

insert into emp (empno,ename,sal,hirdate) values (222,'dddd',3333,default) ;

Performing TOP - N Analysis

select rownum as rank, ename,sal from (select ename,sal from emp order by sal desc) where rownum <= 3 ;


Unconditional INSERT ALL

INSERT ALL
INTO sal_history values (empno,hiredate,sal)
INTO mgr_history values (empno,mgr,sal)
select empno,hiredate,sal,mgr from emp where empno > 1000 ;

Conditional INSERT ALL

INSERT ALL
WHEN SAL > 10000 then
INTO sal_history values (empno,hiredate,sal)
WHEN MGR > 200 THEN
INTO mgr_history values (empno,mgr,sal)
select empno,hiredate,sal,mgr from emp where empno > 1000 ;

Pivoting Insert

INSERT ALL
INTO sales_info values (empno,week_id,sales_mon)
INTO sales_info values (empno,week_id,sales_TUE)
INTO sales_info values (empno,week_id,sales_WED)
INTO sales_info values (empno,week_id,sales_THU)
INTO sales_info values (empno,week_id,sales_FRI)

select empno,week_id,sales_mon,sales_tue,sales_wed,sales_thu,sales_fri from sales_source_data ;