Oracle launches "Oracle Application Expresss Developer Certified Expert" certificate "1Z1-450"

New move from Oracle for "Oracle Application Express" developers to be more recognized and known by launching new certificate Oracle Application Expresss Developer Certified Expert
Oracle Application Express 3.2: Developing Web Applications
Till now the it is beta exam and recommended training is
Oracle Application Express: Developing Web Applications NEW.
 
Saad,

RETURNING INTO Clause in Oracle

, Nice new feature introduced in Oracle 10g:
Single-Set Aggregates in DML Returning Clause: -as per Oracle documentation- This feature enables the use of single-set aggregation functions in the RETURNING clause of DML statements. This capability can result in significant performance gains in transactions that process many rows of the same table, a behavior often observed in batch processes.
Simple syntax is:

INSERT INTO {table} (columnn) 

{values}
RETURNING {exp} INTO {variables};

where:
can be table, materialized view or single-table view.

The purpose of the RETURNING clause is to return the rows affected by the INSERT statement.

SET serveroutput on

DECLARE
   v_total_salary   NUMBER;
BEGIN
   INSERT INTO emp
      SELECT *
        FROM emp
     RETURNING SUM (sal)
          INTO v_total_salary;

   DBMS_OUTPUT.put_line ('Total Salary is: ' || v_total_salary);
END;
/

RETURNING clause can be used with UPDATE statement:

SET serveroutput on

DECLARE
   v_total_salary   NUMBER;
BEGIN
   UPDATE    emp
         SET sal = sal * 1.1
   RETURNING SUM (sal)
        INTO v_total_salary;

   DBMS_OUTPUT.put_line ('Total Salary is: ' || v_total_salary);
END;
/

RETURNING clause can be used with UPDATE statement also:

SET serveroutput on

DECLARE
   v_total_salary   NUMBER;
BEGIN
   DELETE FROM emp
         WHERE manager IS NOT NULL
     RETURNING SUM (al)
          INTO v_total_salary;

   DBMS_OUTPUT.put_line ('Total Salary is: ');
END;
/

Saad,

NULL value in Oracle

NULL is the absence of a value or information. NULL can be assigned (to a column or a variable ...) but it can not be equated with anything.
so treating NULL or column which may contain NULL values should be done carefully.
Any equation with NULL value will return NULL, same thing for Adding, subtracting, multiplying and dividing.

Oracle has number of built-in functions to treat NULL values, we will describe some of these functions:

NVL:
NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
The most frequently used function to replace a NULL value with another value (for example 0 in case of number datatype).

SELECT ename, NVL (comm, 0)
  FROM emp;

NVL2:
NVL2 ( expr1 , expr2 , expr3 )
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2.

SELECT ename, NVL2 (comm, 'has commission', 'has no commission')
  FROM emp;

COALESCE:
COALESCE(expr[,expr]…)
Returns the first non-null expr in the expression list.

SELECT COALESCE (NULL, 3, 1)
  FROM DUAL;

NULLIF:
NULLIF( expr1, expr2 )
NULLIF function returns NULL if expr1 and expr2 are equal. Otherwise, it returns expr1.

SELECT NULLIF (ename, ename)
  FROM emp;

SELECT NULLIF (ename, ename || ' ')
  FROM emp;

LNNVL: -New in Oracle 10g-
LNNVL(condition)
Return TRUE if the condition is false or unknown and FALSE if the condition is true.
LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query.

SELECT COUNT (*)
  FROM emp
 WHERE lnnvl (comm < .2);

Sorting NULL values:
NULL values come last in ascending order and first in descending order but this order can be overridden by using NULLS FIRST/LAST clause.

SELECT   *
    FROM emp
ORDER BY comm DESC NULLS FIRST;

Note:
It is important to remember that any equation with NULL will return NULL, if we did understand this logic we can explain the reason why any query returns no rows if it has NOT IN operator with list of values (or sub-query) contains NULL values.
The following query retuens no rows:

SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT mgr
                       FROM emp);

To solve this problem we use NVL function:

SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT NVL (mgr, 0)
                       FROM emp);

OR EXISTS

SELECT ename
  FROM emp e
 WHERE NOT EXISTS (SELECT mgr
                     FROM emp
                    WHERE mgr = e.empno);

Saad,