Tips for Oracle DECODE function operations

In fact, the actual operations and related functions of the Oracle DECODE function are similar to the CASE or IF-THEN-ELSE statement, which is easy to operate, the following articles mainly describe the actual application of the Oracle DECODE function by introducing the actual operations and functions of the CASE or IF-THEN-ELSE statement.

Oracle DECODE function:

The function is similar to the CASE or IF-THEN-ELSE statement, but it is easier. Syntax:

 
 

  
  
DECODE(col/expression,search1,result1  
  
  
[,search2,result2,......,]  
  
  
[,default] )  
  
  
select job,sal,  
  
  
DECODE(job,'ANALYST',SAL*1.1,  
  
  
'CLERK',SAL*1.15,  
  
  
'MANAGER',SAL*1.20,  
  
  
SAL)  
  
  
REVISER_SALARY  
  
  
* FROM scott.emp  
  
  
JOB SAL REVISER_SALARY  
  
  
CLERK 800 920  
  
  
SALESMAN 1600 1600  
  
  
SALESMAN 1250 1250  
  
  
MANAGER 2975 3570  
  
  
SALESMAN 1250 1250  
  
  
MANAGER 2850 3420  
  
  
MANAGER 2450 2940  
  
  
ANALYST 3000 3300  
  
  
PRESIDENT 5000 5000  
  
  
SALESMAN 1500 1500  
  
  
CLERK 1100 1265  
  
  
CLERK 950 1092.5  
  
  
ANALYST 3000 3300  
  
  
CLERK 1300 1495  
 
 

Oracle decode function command:

Save: save the commands in sqlplus in the hard disk. For example:

SQL> save 'd: \ selectEmp.txt'

Created file D: \ selectEmp.txt

Get: load the command from the hard disk to the sqlplus environment. Then type run or r or/to execute. For example:

 
 

  
  
SQL> get 'd: \ selectEmp.txt'
  
  
1 * select * from scott. emp
  
  
SQL> r
  
  
1 * select * from scott. emp
  
  
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  
  
7369 smith clerk 7902-12-80 800 20
  
  
7499 allen salesman 7698 20-2 month-81 1600 300 30
  
  
7521 ward salesman 7698 22-2 month-81 1250 500 30
  
  
7566 jones manager 7839 2975-81 20
  
  
7654 martin salesman 7698 28-9 month-81 1250 1400 30
 
 

@: Load the command from the hard disk to the sqlplus environment and execute it automatically. For example:

 
 

  
  
SQL> @ D: \ selectEmp.txt
  
  
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  
  
7369 smith clerk 7902-12-80 800 20
  
  
7499 allen salesman 7698 20-2 month-81 1600 300 30
  
  
7521 ward salesman 7698 22-2 month-81 1250 500 30
  
  
7566 jones manager 7839 2975-81 20
  
  
7654 martin salesman 7698 28-9 month-81 1250 1400 30
 
 

The above content is an introduction to the Oracle DECODE function and Oracle DECODE function commands. I hope you will find some gains.