Oracle: Usage

Талбаруудын өгөгдлийн төрлийг харах.
Select the column datatype using case when, concat function.

SELECT column_name,
CASE
WHEN data_type = 'DATE' then data_type
WHEN data_type = 'NUMBER' then concat( concat( concat( data_type, '(' ), concat( data_precision, case when data_scale > 0 then concat( ',', data_scale) end ) ), ')' )
WHEN data_type = 'NVARCHAR2' then concat( concat( concat( data_type, '(' ), char_length), ')' )
WHEN data_type = 'VARCHAR2' then concat( concat( concat( data_type, '(' ), concat( char_length, case char_used when 'B' then ' BYTE' end ) ), ')' )
END data_type
FROM user_tab_columns
WHERE table_name='table_name' AND (
column_name = 'columnname1' OR
column_name = 'columnname2' OR
column_name = 'columnname3'
) order by column_name;

Output:
COLUMN_NAME DATA_TYPE
columnname1 NUMBER(4)
columnname2 VARCHAR2(15 BYTE)
columnname3 DATE

Oracle: Concat Function

In Oracle/PLSQL, the concat function allows you to concatenate two strings together.

Хоёр текстийг хооронд нь холбоно.

The syntax for the concat function is:
concat( string1, string2 )

string1 is the first string to concatenate.
string2 is the second string to concatenate.

Applies To:
• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:
concat('Tech on', ' the Net'); would return 'Tech on the Net'.
concat('a', 'b') would return 'ab'.

Thanks,
Info source: http://www.techonthenet.com/oracle/functions/concat.php

Oracle: Case when

case when x = y then a else b end

case when x < y then a when x = y then b else c end

case XYZ when 'foo' then 'moo' else 'bar' end


select a,
case
when b = '*' then 'star'
when b = '+' then 'plus'
when b = '-' then 'minus'
else '????'
end
from table_test_case_when;

Thanks,
Info source: http://www.adp-gmbh.ch/ora/sql/case_when.html