Oracle decode Function Application Example

/* Decode Function */
-- The prototype of the decode function is decode (testvalue, if1, then1, if2, then2.... else ).
-- Test testvalue. If testvalue is set to if1, then1 is returned. If testvalue is set to if2, then2,... is returned. If no result is returned, else is returned.
-- Common usage is to implement the current convert rows to columns in Oracle ).

 

SQL script

Drop   Table Student;
/
-- 1. Create a table
Create   Table Student
(
Sid Varchar ( 10 ),
Sname Varchar ( 30 ),
Sex Varchar2 ( 2 ) Default   ' 1 ' ,
Age Integer ,
Address Varchar ( 100 ),
Primary   Key (SID)
);
/
-- 2. Add data
Declare
I Integer : = 1 ;
V_ SQL Varchar ( 1000 );
Begin
I: = 1 ;
While I < 10 Loop
V_ SQL: = ' Insert into student (SID, sname, sex, age, address) values (: 1,: 2,: 3,: 4,: 5) ' ;
If I < 5   Then
Execute Immediate v_ SQL using I, ' Students ' | I, to_char (mod (I, 2 )), 18 , ' Hunan ' ;
Else
Execute Immediate v_ SQL using I, ' Students ' | I, to_char (mod (I, 2 )), 19 , ' Sichuan ' ;
End   If ;
Commit ;
I: = I + 1 ;
End Loop;
End ;
/
Commit ;
/

--3. Call the Stored Procedure
--========================================================== ========================================================== ============
Begin
Inserttestdata (10);
End;
/

/* Decode Function */
-- The prototype of the decode function is decode (testvalue, if1, then1, if2, then2.... else ).
-- Test testvalue. If testvalue is set to if1, then1 is returned. If testvalue is set to if2, then2,... is returned. If no result is returned, else is returned.
-- Common usage is to implement the current convert rows to columns in Oracle ).

-- 4. Use decode to query the Gender Information of the students
Select Sname,
Decode (sex,
' 1 ' , ' Male ' ,
' 0 ' , ' Female '
) As Gender
From Student;
/

-- 5. Group by gender and count the number of students whose home addresses are Hunan and Sichuan.
Select Age, Sum (Decode (address, ' Hunan ' , 1 , 0 )) As "Hunan Province ",
Sum (Decode (address, ' Sichuan ' , 1 , 0 )) As "Sichuan Province"
From Student
Group   By Age;
/

 

 

Running result: