PLSQL tips

is not null

null should use "is", not "="

(taught, but easy to forget, and important!!!)

 

group functions like AVG, SUM... will dismiss the NULL values

 

NVL (colA, -1) replace all NULL values by -1 when selection

NVL2 (colA, -1, 2) replace all NULL values by -1 when selection, and replace others by 2. 

NULLIF (A, B, C) returns NULL if A = B, else returns C

 

Oracle password is not case sensitive, and should not use characters like "&"

 

row_number() function

can solve the select numbers of lines with each restrict

 

LIKE 'Clear%' 

'%' can stand for many characters, and if '%' happens on the beginning, INDEX is no use.

'_' can stand for one characters

 

rownum

don't write:

select ... where rownum = 2;

because the first row is not selected, so the second row's rownum is still 1...

 

CONCAT is the same as '||'

 

LPAD, RPAD

add some characters to the left or right part of a string

 

CEIL, FLOOR

CEIL (4.1) = 5

FLOOR (4.9) = 4

TRUNC and used on number, time...

 

CASE in SQL

SELECT ... CASE

WHEN ... THEN ...

WHEN ... THEN ...

END

 

in OUT JOIN

LEFT OUT JOIN should mention the difference between:

LEFT OUT JOIN ... ON (... AND ...)

LEFT OUT JOIN ... ON ... WHERE ...

 

SELECT * INTO valueA WHERE ...

if there's nothing returns from SELECT, there will be ERROR, coz no value given to valueA .

but if

SELECT COUNT (*) INTO valueA WHERE

then it is ok, coz COUNT (*) returns zero.

 

update tableA

set colA = XXX

where exist

select ...

from tableA and tableB 

where tableA.x = tableB.x