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,

Compare the performance between two functions in Oracle

Sometimes when we can do something in Oracle in more than one way, but at that point we have to use the best one which can serves us better.

PERFORMANCE is the most important criteria we have to put into consideration when have more than one solution. Nice tip in Oracle enables us to measure performance -time spent- between more than one function which is get_cpu_time from dbms_utility package which returns the current CPU time in 100th's of a second and we use it before and after each function with loop of relatively big number of iteration.

Example:

DECLARE
   v_c1     NUMBER;
   v_c2     NUMBER;
   v_date   DATE;
   v_char   VARCHAR2 (10);
BEGIN
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_date := TRUNC (SYSDATE);
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TRUNC');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_char := TO_CHAR (SYSDATE, 'DD-MM-YYYY');
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TO_CHAR');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
END;

The output is:

For loop using TRUNC
CPU seconds used: 25
For loop using TO_CHAR
CPU seconds used: 68
PL/SQL procedure successfully completed.

The numbers will change depending on CPU speed and tasks assigned, but anyway you can notice the difference.


Saad,

Oracle UNION, UNION ALL, INTERSECT, MINUS

If you have result sets from two queries or more, and you want to combine those result sets or you want to remove duplicate rows or get only the common rows or not common rows, it's time to learn about UNION, UNION ALL, INTERSECT and MINUS queries:

UNION Query:
Allows you to combine the result sets of two or more queries but it removes the duplicate rows between the queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION
SELECT *
  FROM emp
 WHERE deptno = 20;

You can notice that employee with empno = 7566 should come in both select statement but comes once since we used UNION.

UNION ALL Query:
Allows you to combine the result set of two or more queries and returns all rows.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION ALL
SELECT *
  FROM emp
 WHERE deptno = 20;

Since we used UNION ALL, rows from both queries are returned and duplicate rows are not removed.

INTERSECT Query:

It allow you to get only selected rows returned by all queries you are using in INTERSECT Query.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
INTERSECT
SELECT *
  FROM emp
 WHERE deptno = 20;

It returns only one row -returned by both queries-.

MINUS Query:

Retuns rows from first query that are not returned by other queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
MINUS
SELECT *
  FROM emp
 WHERE deptno = 20;

Note:
Each SQL statement within the UNION,UNION ALL,INTERSECT and MINUS query must have the same number of fields in the result sets with similar data types.

Saad,