New SQL function in Oracle 9i
COALESCE
You can also use COALESCE as a variety of the CASE expression. For example,
COALESCE (expr1, expr2)
is equivalent to:
CASE WHEN IS expr1 NOT NULL THEN expr1 ELSE expr2 END
Similarly,
COALESCE (expr1, expr2, ..., exprn), for n>=3
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END
select comm,COALESCE(0.9*comm, comm,5) from emp ;
CURRENT_DATE
Purpose
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian
calendar of datatype DATE.
Examples
The following example illustrates that CURRENT_DATE is sensitive to the session time zone:
ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-05:00 29-MAY-2000 13:14:03
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-08:00 29-MAY-2000 10:14:33
CURRENT_TIMESTAMP
Purpose
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of
datatype TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of
the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.
In the optional argument, precision specifies the fractional second precision of the time value
returned.
Examples
The following example illustrates that CURRENT_TIMESTAMP is sensitive to the session time zone:
ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------------
-05:00 04-APR-00 01.17.56.917550 PM -05:00
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------------------------
-08:00 04-APR-00 10.18.21.366065 AM -08:00
If you use the CURRENT_TIMESTAMP with a format mask, take care that the format mask matches the
value returned by the function. For example, consider the following table:
CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
The following statement fails because the mask does not include the TIME ZONE portion of the
type returned by the function:
INSERT INTO current_test VALUES
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
The following statement uses the correct format mask to match the return type of
CURRENT_TIMESTAMP:
INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ
(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
EXTRACT (DATETIME)
Purpose
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;
EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
1998
The following example selects from the sample table hr.employees all employees who were hired
after 1998:
SELECT ename, empno, hiredate FROM emp WHERE EXTRACT(YEAR FROM TO_DATE(hiredate, 'DD-MON-RR')) <>
ORDER BY hiredate;
ENAME EMPNO HIREDATE
---------- ---------- ---------
suresh 2345 02-JAN-76
SMITH 7369 17-DEC-80
ALLEN 7499 20-FEB-81
WARD 7521 22-FEB-81
FIRST
Purpose
FIRST and LAST are very similar functions. Both are aggregate and analytic functions that
operate on a set of values from a set of rows that rank as the FIRST or LAST with respect
to a given sorting specification. If only one row ranks as FIRST or LAST, the aggregate
operates on the set with only one element.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can
be implicitly converted to a numeric datatype. The function returns the same datatype as the
numeric datatype of the argument.
When you need a value from the first or last row of a sorted group, but the needed value is
not the sort key, the FIRST and LAST functions eliminate the need for self joins or views
and enable better performance.
select deptno,
min(sal) KEEP (DENSE_RANK FIRST ORDER BY comm) "Worst",
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY comm) "Best"
from emp
group by deptno ;
DEPTNO Worst Best
---------- ---------- ----------
10 1300 5000
20 800 3000
30 1500 2850
40 3456 3456
60 2345 2345
90 5657 5657
NULLIF
Purpose
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they
are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
If both arguments are numeric datatypes, then Oracle Database determines the argument with
the higher numeric precedence, implicitly converts the other argument to that datatype,
and returns that datatype. If the arguments are not numeric, then they must be of the same
datatype, or Oracle returns an error.
The NULLIF function is logically equivalent to the following CASE expression:
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
Examples
The following example selects those employees from the sample schema hr who have changed jobs
since they were hired, as indicated by a job_id in the job_history table different from the
current job_id in the employees table:
SELECT e.ename, NULLIF(e.job, j.job) "Old Job"
FROM emp e, job_history j
WHERE e.empno = j.empno
ORDER BY ename;
ENAME Old Job I
---------- ---------
ADAMS
ALLEN
BLAKE
CLARK
FORD
ggggg
suresh Program
COALESCE
You can also use COALESCE as a variety of the CASE expression. For example,
COALESCE (expr1, expr2)
is equivalent to:
CASE WHEN IS expr1 NOT NULL THEN expr1 ELSE expr2 END
Similarly,
COALESCE (expr1, expr2, ..., exprn), for n>=3
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END
select comm,COALESCE(0.9*comm, comm,5) from emp ;
CURRENT_DATE
Purpose
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian
calendar of datatype DATE.
Examples
The following example illustrates that CURRENT_DATE is sensitive to the session time zone:
ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-05:00 29-MAY-2000 13:14:03
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-08:00 29-MAY-2000 10:14:33
CURRENT_TIMESTAMP
Purpose
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of
datatype TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of
the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.
In the optional argument, precision specifies the fractional second precision of the time value
returned.
Examples
The following example illustrates that CURRENT_TIMESTAMP is sensitive to the session time zone:
ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------------
-05:00 04-APR-00 01.17.56.917550 PM -05:00
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------------------------
-08:00 04-APR-00 10.18.21.366065 AM -08:00
If you use the CURRENT_TIMESTAMP with a format mask, take care that the format mask matches the
value returned by the function. For example, consider the following table:
CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
The following statement fails because the mask does not include the TIME ZONE portion of the
type returned by the function:
INSERT INTO current_test VALUES
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
The following statement uses the correct format mask to match the return type of
CURRENT_TIMESTAMP:
INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ
(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
EXTRACT (DATETIME)
Purpose
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;
EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
1998
The following example selects from the sample table hr.employees all employees who were hired
after 1998:
SELECT ename, empno, hiredate FROM emp WHERE EXTRACT(YEAR FROM TO_DATE(hiredate, 'DD-MON-RR')) <>
ORDER BY hiredate;
ENAME EMPNO HIREDATE
---------- ---------- ---------
suresh 2345 02-JAN-76
SMITH 7369 17-DEC-80
ALLEN 7499 20-FEB-81
WARD 7521 22-FEB-81
FIRST
Purpose
FIRST and LAST are very similar functions. Both are aggregate and analytic functions that
operate on a set of values from a set of rows that rank as the FIRST or LAST with respect
to a given sorting specification. If only one row ranks as FIRST or LAST, the aggregate
operates on the set with only one element.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can
be implicitly converted to a numeric datatype. The function returns the same datatype as the
numeric datatype of the argument.
When you need a value from the first or last row of a sorted group, but the needed value is
not the sort key, the FIRST and LAST functions eliminate the need for self joins or views
and enable better performance.
select deptno,
min(sal) KEEP (DENSE_RANK FIRST ORDER BY comm) "Worst",
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY comm) "Best"
from emp
group by deptno ;
DEPTNO Worst Best
---------- ---------- ----------
10 1300 5000
20 800 3000
30 1500 2850
40 3456 3456
60 2345 2345
90 5657 5657
NULLIF
Purpose
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they
are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
If both arguments are numeric datatypes, then Oracle Database determines the argument with
the higher numeric precedence, implicitly converts the other argument to that datatype,
and returns that datatype. If the arguments are not numeric, then they must be of the same
datatype, or Oracle returns an error.
The NULLIF function is logically equivalent to the following CASE expression:
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
Examples
The following example selects those employees from the sample schema hr who have changed jobs
since they were hired, as indicated by a job_id in the job_history table different from the
current job_id in the employees table:
SELECT e.ename, NULLIF(e.job, j.job) "Old Job"
FROM emp e, job_history j
WHERE e.empno = j.empno
ORDER BY ename;
ENAME Old Job I
---------- ---------
ADAMS
ALLEN
BLAKE
CLARK
FORD
ggggg
suresh Program
