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!!