Oracle SQL Tips [Database]


This article shows some tips for Oracle SQL.

๐Ÿฏ COALESCE

COALESCE returns the first non-null value in the expression list.

COALESCE (expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Example is as follows:

SELECT COALESCE(column1, 'A') FROM test1s;
column1
-----
AAA
(1 rows)

๐Ÿˆ No Type Boolean

Unfortunately, There is not Boolean type in Oracle Database(in 2017 Dec).

So, we usually use NUMBER(1) or CHAR(1) type for such kind use-case.

-- 0 for false and 1 for true in type of NUMBER
xxx NUMBER(1) DEFAULT 0 NOT NULL
-- 'Y' for true and 'N' for false in type of CHAR
xxx CHAR(1) DEFAULT 'N' NOT NULL

๐Ÿน CREATE/RENAME/DROP Synonym

โ€œSynonymโ€ is an alias name for a table or view in Oracle Database. As not to write a schema name or etc, we usually use it.

-- Create synonym
CREATE SYNONYM TEST01 FOR USER1.TEST_TBL;
-- Rename synonym
RENAME test_sym TO my_sym;
-- Remove synonym
DROP SYNONYM test_sym;

๐Ÿ˜ธ Special Thanks

๐Ÿ–ฅ Recommended VPS Service

VULTR provides high performance cloud compute environment for you. Vultr has 15 data-centers strategically placed around the globe, you can use a VPS with 512 MB memory for just $ 2.5 / month ($ 0.004 / hour). In addition, Vultr is up to 4 times faster than the competition, so please check it => Check Benchmark Results!!