Automomus transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. when an autonomous transaction is called, the origination transaction gets suspended. control is returned when the autonomous transaction does a commit or rollback. A procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. You may need to increase the transaction parameter to allow for the extra concurrent transactions.

Cartesian join

A Cartesian join is a join of every row of one table to every row of another table. this normally happens when no matching join columns are specified. a query must have at least n-1 joins to avoid a Cartesian product.

Coalesce

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

Syntax:

COALESCE (expression_1, expression_2, ...,expression_n).

Nvl

Nvl is an oralce sql function that will return a non-null value if a null value is passed to it.

Nvl2

Nvl2 is an oracle sql function that will return different values based on whether the input value is null or not. NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

Decode

Decode is a sql function that provides similar functionality to an if then else or case statement. SELECT decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') FROM employees;default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return NULL (no matches found).

Nullif

Nullif is an sql function that returns a null value if both parameters are equal in value. if not,the first value will be returned.

Eg:

SELECT NULLIF(1, 2) FROM dual;

RETURN 1.

SELECT NULLIF(1, 1) FROM dual;

RETURN NULL