Sunday, December 2, 2012

0 PL/SQL: decode function

decode is the PL/SQL function which allows us quickly to covert/update the column in the defined conditions.

decode ( COLUMN_NAME,
              'OLD VALUE1','NEW VALUE1',
              'OLD VALUE2','NEW VALUE2', ....
              'OTHER VALUE' )

Here is the example:

Lets create the test table from dba_objects.

create table po.newtable as select * from dba_objects;
select distinct owner from po.newtable;

image

This sql statement is simply convert HR to NEW_HR, DEMO to NEW_DEMO, PUBLIC to NEW_PUBLIC and for the rest of the value covert to OTHERS.

update po.newtable set OWNER= DECODE(
    OWNER, 
    'HR','NEW_HR',
    'DEMO','NEW_DEMO',
    'PUBLIC','NEW_PUBLIC',
    'OTHER');

image

It is very efficient to complete the update.

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates