Release 3 (8.1.7)
Part Number A85397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 |
|
Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format allows them to operate on zero, one, two, or more arguments:
function(argument, argument, ...)
This chapter contains these sections:
SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL.
If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.
In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.
|
See Also:
|
The general syntax is as follows:
function::=
single_row_function::=
The sections that follow list the built-in SQL functions in each of the groups illustrated above except user-defined functions. All of the built-in SQL functions are then described in alphabetical order. User-defined functions are described at the end of this chapter.
The examples provided with the function descriptions use the emp and dept tables that are part of the scott schema in your sample Oracle database. Many examples also use a sales table, which has the following contents:
REGION PRODUCT S_DAY S_MONTH S_YEAR S_AMOUNT S_PROFIT ------ ------- ------ ---------- ---------- ---------- -------- 200 1 10 6 1998 77586 586 200 1 26 8 1998 62109 509 200 1 11 11 1998 46632 432 200 1 14 4 1999 15678 278 201 1 9 6 1998 77972 587 201 1 25 8 1998 62418 510 201 1 10 11 1998 46864 433 201 1 13 4 1999 15756 279 200 2 9 6 1998 39087 293.5 200 2 25 8 1998 31310 255 200 2 10 11 1998 23533 216.5 200 2 13 4 1999 7979 139.5 201 2 9 11 1998 23649.5 217 201 2 12 4 1999 8018.5 140 200 3 9 11 1998 15834 144.67 200 3 12 4 1999 5413.33 93.33 201 3 11 4 1999 5440 93.67 200 4 11 4 1999 4131 70.25 201 4 10 4 1999 4151.25 70.5 200 5 10 4 1999 3362 56.4 201 5 5 6 1998 16068 118.2 201 5 21 8 1998 12895.6 102.8 201 5 9 4 1999 3378.4 56.6
Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH clauses, and CONNECT BY clauses.
Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The number functions are:
Character functions that return character values, unless otherwise noted, return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message. The character functions that return character values are:
The character functions that return number values are:
Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number. The date functions are:
Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:
The following single-row functions do not fall into any of the other single-row function categories.
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
|
See Also: "GROUP BY Examples" and the HAVING clause for more information on the |
Many (but not all) aggregate functions that take a single argument accept these options:
DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither option, the default is ALL.
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the scott schema:
SELECT AVG(MAX(sal)) FROM emp GROUP BY deptno; AVG(MAX(SAL)) ------------- 3616.66667
This calculation evaluates the inner aggregate (MAX(sal)) for each group defined by the GROUP BY clause (deptno), and aggregates the results again.
The aggregate functions are:
Analytic functions compute an aggregate value based on a group of rows. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
analytic_function::=
analytic_clause::=
The keywords and parameters of this syntax are:
analytic_function
Specify the name of an analytic function (see the listings of different types of analytic functions following this table).
arguments
Analytic functions take 0 to 3 arguments.
analytic_clause
Use analytic_clause OVER clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
query_partition_clause
ORDER_BY_clause
Use the ORDER BY clause to specify how data is ordered within a partition. You can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Restriction: When used in an analytic function, the ORDER_BY_clause must take an expression (expr). Position (position) and column aliases (c_alias) are invalid. Otherwise this ORDER_BY_clause is the same as that used to order the overall query or subquery.
windowing_clause
|
|
These keywords define for each row a "window" (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top to bottom.
You cannot specify this clause unless you have specified the |
|
|
|
|
|
Use the |
|
|
If you omit |
|
|
Specify |
|
|
Specify |
|
|
As a start point,
As an end point, |
|
|
For |
|
|
If you are defining a logical window defined by an interval of time in numeric format, you may need to use conversion functions. See Also: NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into interval literals |
|
|
If you specified |
|
|
If you specified
|
|
|
|
|
If you omit the |
|
Analytic functions are commonly used in data warehousing environments. The analytic functions are:
|
See Also: Oracle8i Data Warehousing Guide for more information on these functions, and for scenarios illustrating their use |
Object functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:
|
See Also: Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals for more information about REFs |
ABS returns the absolute value of n.
SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15
ACOS returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.
SELECT ACOS(.3)"Arc_Cosine" FROM DUAL; Arc_Cosine ---------- 1.26610367
ADD_MONTHS returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.
SELECT TO_CHAR( ADD_MONTHS(hiredate,1), 'DD-MON-YYYY') "Next month" FROM emp WHERE ename = 'SMITH'; Next Month ----------- 17-JAN-1981
ASCII returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code, this function returns an EBCDIC value. There is no corresponding EBCDIC character function.
SELECT ASCII('Q') FROM DUAL; ASCII('Q') ---------- 81
ASIN returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of pi/2 to pi/2 and are expressed in radians.
SELECT ASIN(.3) "Arc_Sine" FROM DUAL; Arc_Sine ----------
.304692654
ATAN returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -pi/2 to pi/2 and are expressed in radians.
SELECT ATAN(.3) "Arc_Tangent" FROM DUAL; Arc_Tangent ---------- .291456794
ATAN2 returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of -pi to pi, depending on the signs of n and m, and are expressed in radians. ATAN2(n,m) is the same as ATAN2(n/m)
SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL; Arc_Tangent2 ------------ .982793723
AVG returns average value of expr. You can use it as an aggregate or analytic function.
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
The following example calculates the average salary of all employees in the emp table:
SELECT AVG(sal) "Average" FROM emp; Average ---------- 2077.21429
The following example calculates, for each employee in the emp table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:
SELECT mgr, ename, hiredate, sal, AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM emp; MGR ENAME HIREDATE SAL C_MAVG ---------- ---------- --------- ---------- ---------- 7566 FORD 03-DEC-81 3000 3000 7566 SCOTT 19-APR-87 3000 3000 7698 ALLEN 20-FEB-81 1600 1425 7698 WARD 22-FEB-81 1250 1450 7698 TURNER 08-SEP-81 1500 1333.33333 7698 MARTIN 28-SEP-81 1250 1233.33333 7698 JAMES 03-DEC-81 950 1100 7782 MILLER 23-JAN-82 1300 1300 7788 ADAMS 23-MAY-87 1100 1100 7839 JONES 02-APR-81 2975 2912.5 7839 BLAKE 01-MAY-81 2850 2758.33333 7839 CLARK 09-JUN-81 2450 2650 7902 SMITH 17-DEC-80 800 800 KING 17-NOV-81 5000 5000
BFILENAME returns a BFILE locator that is associated with a physical LOB binary file on the server's file system. A directory is an alias for a full pathname on the server's file system where the files are actually located, and 'filename' is the name of the file in the server's file system.
Neither 'directory' nor 'filename' needs to point to an existing object on the file system at the time you specify BFILENAME. However, you must associate a BFILE value with a physical file before performing subsequent SQL, PL/SQL, DBMS_LOB package, or OCI operations.
|
See Also:
|
INSERT INTO file_tbl VALUES (BFILENAME ('lob_dir1', 'image1.gif'));
BITAND computes an AND operation on the bits of argument1 and argument2, both of which must resolve to nonnegative integers, and returns an integer. This function is commonly used with the DECODE expression, as illustrated in the example that follows.
Consider the following table named cars:
MANUFACTURER MODEL OPTIONS --------------- ---------- ---------- TOYOTA CAMRY 3 TOYOTA COROLLA 5 NISSAN MAXIMA 6
The following example represents each option in each car by individual bits:
SELECT manufacturer, model, DECODE(BITAND(options, 1), 1, 'Automatic', 'Stick-shift'), DECODE(BITAND(options, 2), 2, 'CD', 'Radio'), DECODE(BITAND(options, 4), 4, 'ABS', 'No-ABS') FROM cars; MANUFACTURER MODEL DECODE(BITA DECOD DECODE --------------- ---------- ----------- ----- ------ TOYOTA CAMRY Automatic CD No-ABS TOYOTA COROLLA Automatic Radio ABS NISSAN MAXIMA Stick-shift CD ABS
CEIL returns smallest integer greater than or equal to n.
SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16
CHARTOROWID converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.
SELECT ename FROM emp WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO'); ENAME ---------- LEWIS
CHR returns the character having the binary equivalent to n in either the database character set or the national character set.
If USING NCHAR_CS is not specified, this function returns the character having the binary equivalent to n as a VARCHAR2 value in the database character set.
If USING NCHAR_CS is specified, this function returns the character having the binary equivalent to n as a NVARCHAR2 value in the national character set.
The following example is run on an ASCII-based machine with the database character set defined as WE8ISO8859P1:
SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL; Dog --- CAT SELECT CHR(16705 USING NCHAR_CS) FROM DUAL; C - A
To produce the same results on an EBCDIC-based machine with the WE8EBCDIC1047 character set, the first example above would have to be modified as follows:
SELECT CHR(195)||CHR(193)||CHR(227) "Dog" FROM DUAL; Dog --- CAT
CONCAT returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||).
This example uses nesting to concatenate three character strings:
SELECT CONCAT(CONCAT(ename, ' is a '), job) "Job" FROM emp WHERE empno = 7900; Job ----------------- JAMES is a CLERK
CONVERT converts a character string from one character set to another.
char argument is the value to be converted.
dest_char_set argument is the name of the character set to which char is converted.
source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.
Both the destination and source character set arguments can be either literals or columns containing the name of the character set.
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.
SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') "Conversion" FROM DUAL; Conversion ---------- Gross
Common character sets include:
CORR returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.
Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) after eliminating the pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.
The following example calculates the coefficient of correlation between the salaries and commissions of the employees whose manager is 7698 from the emp table:
SELECT mgr, CORR(sal, comm) FROM EMP GROUP BY mgr HAVING mgr = 7698; MGR CORR(SAL,COMM) ---------- -------------- 7698 -.69920974
The following example returns the cumulative coefficient of correlation of monthly sales and monthly profits from the sales table for year 1998:
SELECT s_month, CORR(SUM(s_amount), SUM(s_profit)) OVER (ORDER BY s_month) AS CUM_CORR FROM sales WHERE s_year=1998 GROUP BY s_month ORDER BY s_month; S_MONTH CUM_CORR ---------- ---------- 6 8 1 11 .860554259
Correlation functions require more than one row on which to operate, so the first row in the preceding example has no value calculated for it.
COS returns the cosine of n (an angle expressed in radians).
SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" FROM DUAL; Cosine of 180 degrees --------------------- -1
COSH returns the hyperbolic cosine of n.
SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL; Hyperbolic cosine of 0 ---------------------- 1
COUNT returns the number of rows in the query. You can use it as an aggregate or analytic function.
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
If you specify expr, COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.
If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.
SELECT COUNT(*) "Total" FROM emp; Total ---------- 14 SELECT COUNT(*) "Allstars" FROM emp WHERE comm > 0; Allstars -------- 3 SELECT COUNT(mgr) "Count" FROM emp; Count ---------- 13 SELECT COUNT(DISTINCT mgr) "Managers" FROM emp; Managers ---------- 6
The following example calculates, for each employee in the emp table, the moving count of employees earning salaries in the range $50 less than through $150 greater than the employee's salary.
SELECT ename, sal, COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count FROM emp; ENAME SAL MOV_COUNT ---------- ---------- ---------- SMITH 800 2 JAMES 950 2 ADAMS 1100 3 WARD 1250 3 MARTIN 1250 3 MILLER 1300 3 TURNER 1500 2 ALLEN 1600 1 CLARK 2450 1 BLAKE 2850 4 JONES 2975 3 SCOTT 3000 3 FORD 3000 3 KING 5000 1
COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.
Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n
where n is the number of (expr1 , expr2) pairs where neither expr1 nor expr2 is null.
The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.
The following example calculates the population covariance for the amount of sales and sale profits for each year from the table sales.
SELECT s_year, COVAR_POP(s_amount, s_profit) AS COVAR_POP, COVAR_SAMP(s_amount, s_profit) AS COVAR_SAMP FROM sales GROUP BY s_year; S_YEAR COVAR_POP COVAR_SAMP ---------- ---------- ---------- 1998 3747965.53 4060295.99 1999 360536.162 400595.736
The following example calculates cumulative sample covariance of the amount of sales and sale profits in 1998.
SELECT s_year, s_month, s_day, COVAR_POP(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_COVP, COVAR_SAMP(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_COVS FROM sales WHERE s_year=1998 ORDER BY s_year, s_month, s_day; S_YEAR S_MONTH S_DAY CUM_COVP CUM_COVS ---------- ---------- ---------- ---------- ---------- 1998 6 5 0 1998 6 9 4940952.6 7411428.9 1998 6 9 4940952.6 7411428.9 1998 6 10 5281752.33 7042336.44 1998 8 21 6092799.46 7615999.32 1998 8 25 4938283.61 5761330.88 1998 8 25 4938283.61 5761330.88 1998 8 26 4612074.09 5270941.82 1998 11 9 4556799.53 5063110.59 1998 11 9 4556799.53 5063110.59 1998 11 10 4014833.65 4379818.52 1998 11 10 4014833.65 4379818.52 1998 11 11 3747965.53 4060295.99
COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.
Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
where n is the number of (expr1 , expr2) pairs where neither expr1 nor expr2 is null.
The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.
The following example calculates the population covariance for the amount of sales and sale profits for each year from the table sales.
SELECT s_year, COVAR_POP(s_amount, s_profit) AS COVAR_POP, COVAR_SAMP(s_amount, s_profit) AS COVAR_SAMP FROM sales GROUP BY s_year; S_YEAR COVAR_POP COVAR_SAMP ---------- ---------- ---------- 1998 3747965.53 4060295.99 1999 360536.162 400595.736
The following example calculates cumulative sample covariance of the amount of sales and sale profits in 1998.
SELECT s_year, s_month, s_day, COVAR_POP(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_COVP, COVAR_SAMP(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_COVS FROM sales WHERE s_year=1998 ORDER BY s_year, s_month, s_day; S_YEAR S_MONTH S_DAY CUM_COVP CUM_COVS ---------- ---------- ---------- ---------- ---------- 1998 6 5 0 1998 6 9 4940952.6 7411428.9 1998 6 9 4940952.6 7411428.9 1998 6 10 5281752.33 7042336.44 1998 8 21 6092799.46 7615999.32 1998 8 25 4938283.61 5761330.88 1998 8 25 4938283.61 5761330.88 1998 8 26 4612074.09 5270941.82 1998 11 9 4556799.53 5063110.59 1998 11 9 4556799.53 5063110.59 1998 11 10 4014833.65 4379818.52 1998 11 10 4014833.65 4379818.52 1998 11 11 3747965.53 4060295.99
CUME_DIST (cumulative distribution) is an analytic function. It computes the relative position of a specified value in a group of values. For a row R, assuming ascending ordering, the CUME_DIST of R is the number of rows with values lower than or equal to the value of R, divided by the number of rows being evaluated (the entire query result set or a partition). The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.
The following example calculates the salary percentile for each employee within each job category excluding job categories PRESIDENT and MANAGER. For example, 50% of clerks have salaries less than or equal to James.
SELECT job, ename, sal, CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist FROM emp WHERE job NOT IN ('MANAGER', 'PRESIDENT'); JOB ENAME SAL CUME_DIST --------- ---------- ---------- ---------- ANALYST SCOTT 3000 1 ANALYST FORD 3000 1 CLERK SMITH 800 .25 CLERK JAMES 950 .5 CLERK ADAMS 1100 .75 CLERK MILLER 1300 1 SALESMAN WARD 1250 .5 SALESMAN MARTIN 1250 .5 SALESMAN TURNER 1500 .75 SALESMAN ALLEN 1600 1
DENSE_RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties.
The following statement selects the department name, employee name, and salary of all employees who work in the RESEARCH or SALES department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for RANK.
SELECT dname, ename, sal, DENSE_RANK() OVER (PARTITION BY dname ORDER BY sal) as drank FROM emp, dept WHERE emp.deptno = dept.deptno AND dname IN ('SALES', 'RESEARCH'); DNAME ENAME SAL DRANK -------------- ---------- ---------- ---------- RESEARCH SMITH 800 1 RESEARCH ADAMS 1100 2 RESEARCH JONES 2975 3 RESEARCH FORD 3000 4 RESEARCH SCOTT 3000 4 SALES JAMES 950 1 SALES MARTIN 1250 2 SALES WARD 1250 2 SALES TURNER 1500 3 SALES ALLEN 1600 4 SALES BLAKE 2850 5
DEREF returns the object reference of argument expr, where expr must return a REF to an object. If you do not use this function in a query, Oracle returns the object ID of the REF instead, as shown in the example that follows.
CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename VARCHAR2(20), salary NUMBER); CREATE TABLE emp_table OF emp_type (primary key (eno, ename)); CREATE TABLE dept_table (dno NUMBER, mgr REF emp_type SCOPE IS emp_table); INSERT INTO emp_table VALUES (10, 'jack', 50000); INSERT INTO dept_table SELECT 10, REF(e) FROM emp_table e; SELECT mgr FROM dept_table; MGR -------------------------------------------------------------------- 00002202085928CB5CDF7B61CAE03400400B40DCB15928C35861E761BCE03400400B40DCB1 SELECT DEREF(mgr) from dept_table; DEREF(MGR)(ENO, ENAME, SALARY) -------------------------------------------------------- EMP_TYPE(10, 'jack', 50000)
DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set. For the datatype corresponding to each code, see Table 2-1.
The argument return_fmt specifies the format of the return value and can have any of the following values:
By default, the return value contains no character set information. To retrieve the character set name of expr, specify any of the format values above, plus 1000. For example, a return_fmt of 1008 returns the result in octal, plus provides the character set name of expr.
The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.
If expr is null, this function returns a null.
SELECT DUMP('abc', 1016) FROM DUAL; DUMP('ABC',1016) ------------------------------------------
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63
SELECT DUMP(ename, 8, 3, 2) "OCTAL" FROM emp WHERE ename = 'SCOTT'; OCTAL ---------------------------- Type=1 Len=5: 117,124 SELECT DUMP(ename, 10, 3, 2) "ASCII" FROM emp WHERE ename = 'SCOTT'; ASCII ---------------------------- Type=1 Len=5: 79,84
EMPTY_BLOB and EMPTY_CLOB returns an empty LOB locator that can be used to initialize a LOB variable or in an INSERT or UPDATE statement to initialize a LOB column or attribute to EMPTY. EMPTY means that the LOB is initialized, but not populated with data.
You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.
INSERT INTO lob_tab1 VALUES (EMPTY_BLOB()); UPDATE lob_tab1 SET clob_col = EMPTY_BLOB();
EXP returns e raised to the nth power, where e = 2.71828183 ...
SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power ------------------ 54.59815
FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.
You cannot use FIRST_VALUE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
The following example selects, for each employee in Department 20, the name of the employee with the highest salary.
SELECT deptno, ename, sal, FIRST_VALUE(ename) OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS rich_emp FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno); DEPTNO ENAME SAL RICH_EMP ---------- ---------- ---------- ---------- 20 SCOTT 3000 SCOTT 20 FORD 3000 SCOTT 20 JONES 2975 SCOTT 20 ADAMS 1100 SCOTT 20 SMITH 800 SCOTT
The example illustrates the nondeterministic nature of the FIRST_VALUE function. Scott and Ford have the same salary, so are in adjacent rows. Scott appears first because the rows returned by the subquery are ordered by empno. However, if the rows returned by the subquery are ordered by empno in descending order, as in the next example, the function returns a different value:
SELECT deptno, ename, sal, FIRST_VALUE(ename) OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS fv FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc); DEPTNO ENAME SAL FV ---------- ---------- ---------- ---------- 20 FORD 3000 FORD 20 SCOTT 3000 FORD 20 JONES 2975 FORD 20 ADAMS 1100 FORD 20 SMITH 800 FORD
The following example shows how to make the FIRST_VALUE function deterministic by ordering on a unique key.
SELECT deptno, ename, sal, hiredate, FIRST_VALUE(ename) OVER (ORDER BY sal DESC, hiredate ROWS UNBOUNDED PRECEDING) AS fv FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc); DEPTNO ENAME SAL HIREDATE FV ---------- ---------- ---------- --------- ---------- 20 FORD 3000 03-DEC-81 FORD 20 SCOTT 3000 19-APR-87 FORD 20 JONES 2975 02-APR-81 FORD 20 ADAMS 1100 23-MAY-87 FORD 20 SMITH 800 17-DEC-80 FORD
FLOOR returns largest integer equal to or less than n.
SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15
GREATEST returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle compares the exprs using nonpadded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher character set value. If the value returned by this function is character data, its datatype is always VARCHAR2.
SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL; Greatest -------- HARRY
The GROUPING function is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE. These operations produce superaggregate rows that contain nulls representing the set of all values. You can use the GROUPING function to distinguish a null that represents the set of all values in a superaggregate row from an actual null.
The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the GROUPING function is Oracle NUMBER.
In the following example, if the GROUPING function returns 1 (indicating a superaggregate row rather than a data row from the table), the string "All Jobs" appears instead of the null that would otherwise appear:
SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname, DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal" FROM emp, dept WHERE dept.deptno = emp.deptno GROUP BY ROLLUP (dname, job); DNAME JOB Total Empl Average Sa --------------- --------- ---------- ---------- ACCOUNTING CLERK 1 15600 ACCOUNTING MANAGER 1 29400 ACCOUNTING PRESIDENT 1 60000 ACCOUNTING All Jobs 3 35000 RESEARCH ANALYST 2 36000 RESEARCH CLERK 2 11400 RESEARCH MANAGER 1 35700 RESEARCH All Jobs 5 26100 SALES CLERK 1 11400 SALES MANAGER 1 34200 SALES SALESMAN 4 16800 SALES All Jobs 6 18800 All Departments All Jobs 14 24878.5714
HEXTORAW converts char containing hexadecimal digits to a raw value.
INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL;
INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
SELECT INITCAP('the soap') "Capitals" FROM DUAL; Capitals --------- The Soap
INSTR searches string for substring.
position is an integer indicating the character of string where Oracle begins the search. If position is negative, Oracle counts and searches backward from the end of string.
occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.
The function returns an integer indicating the position of the character in string that is the first character of this occurrence. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string) the return value is 0.
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14 SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2
INSTRB is the same as INSTR, except that position and the return value are expressed in bytes, rather than in characters. For a single-byte database character set, INSTRB is equivalent to INSTR.
This example assumes a double-byte database character set.
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27
LAG is an analytic function. It provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.
If you do not specify offset, its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, its default value is null.
You cannot use LAG or any other analytic function for value_expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
The following example provides, for each salesperson in the emp table, the salary of the employee hired just before:
SELECT ename, hiredate, sal, LAG(sal, 1, 0) OVER (ORDER BY hiredate) as prev_sal FROM emp WHERE job = 'SALESMAN'; ENAME HIREDATE SAL PREV_SAL ---------- --------- ---------- ---------- ALLEN 20-FEB-81 1600 0 WARD 22-FEB-81 1250 1600 TURNER 08-SEP-81 1500 1250 MARTIN 28-SEP-81 1250 1500
LAST_DAY returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 23-OCT-97 31-OCT-97 8
The following example adds 5 months to the hiredate of each employee to give an evaluation date:
SELECT ename, hiredate, TO_CHAR( ADD_MONTHS(LAST_DAY(hiredate), 5)) "Eval Date" FROM emp; ENAME HIREDATE Eval Date ---------- --------- --------- SMITH 17-DEC-80 31-MAY-81 ALLEN 20-FEB-81 31-JUL-81 WARD 22-FEB-81 31-JUL-81 JONES 02-APR-81 30-SEP-81 MARTIN 28-SEP-81 28-FEB-82 BLAKE 01-MAY-81 31-OCT-81 CLARK 09-JUN-81 30-NOV-81 SCOTT 19-APR-87 30-SEP-87 KING 17-NOV-81 30-APR-82 TURNER 08-SEP-81 28-FEB-82 ADAMS 23-MAY-87 31-OCT-87 JAMES 03-DEC-81 31-MAY-82 FORD 03-DEC-81 31-MAY-82 MILLER 23-JAN-82 30-JUN-82
LAST_VALUE is an analytic function. It returns the last value in an ordered set of values.
You cannot use LAST_VALUE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
The following example returns the hiredate of the employee earning the highest salary.
SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER (ORDER BY sal ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate); ENAME SAL HIREDATE LV ---------- ---------- --------- --------- SMITH 800 17-DEC-80 19-APR-87 ADAMS 1100 23-MAY-87 19-APR-87 JONES 2975 02-APR-81 19-APR-87 FORD 3000 03-DEC-81 19-APR-87 SCOTT 3000 19-APR-87 19-APR-87
This example illustrates the nondeterministic nature of the LAST_VALUE function. Ford and Scott have the same salary, so they are in adjacent rows. Ford appears first because the rows in the subquery are ordered by hiredate. However, if the rows are ordered by hiredate in descending order, as in the next example, the function returns a different value:
SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER (ORDER BY sal ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC); ENAME SAL HIREDATE LV ---------- ---------- --------- --------- SMITH 800 17-DEC-80 03-DEC-81 ADAMS 1100 23-MAY-87 03-DEC-81 JONES 2975 02-APR-81 03-DEC-81 SCOTT 3000 19-APR-87 03-DEC-81 FORD 3000 03-DEC-81 03-DEC-81
The following two examples show how to make the LAST_VALUE function deterministic by ordering on a unique key. By ordering within the function by both salary and hiredate, you can ensure the same result regardless of the ordering in the subquery.
SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER (ORDER BY sal, hiredate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate); ENAME SAL HIREDATE LV ---------- ---------- --------- --------- SMITH 800 17-DEC-80 19-APR-87 ADAMS 1100 23-MAY-87 19-APR-87 JONES 2975 02-APR-81 19-APR-87 FORD 3000 03-DEC-81 19-APR-87 SCOTT 3000 19-APR-87 19-APR-87 SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER (ORDER BY sal, hiredate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC); ENAME SAL HIREDATE LV ---------- ---------- --------- --------- SMITH 800 17-DEC-80 19-APR-87 ADAMS 1100 23-MAY-87 19-APR-87 JONES 2975 02-APR-81 19-APR-87 FORD 3000 03-DEC-81 19-APR-87 SCOTT 3000 19-APR-87 19-APR-87
LEAD is an analytic function. It provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LEAD provides access to a row at a given physical offset beyond that position.
If you do not specify offset, its default is 1. The optional default value is returned if the offset goes beyond the scope of the table. If you do not specify default, its default value is null.
You cannot use LEAD or any other analytic function for value_expr. That is, you can use other built-in function expressions for value_expr, but you cannot nest analytic functions.
The following example provides, for each employee in the emp table, the hiredate of the employee hired just after:
SELECT ename, hiredate, LEAD(hiredate, 1) OVER (ORDER BY hiredate) AS "NextHired" FROM emp; ENAME HIREDATE NextHired ---------- --------- --------- SMITH 17-DEC-80 20-FEB-81 ALLEN 20-FEB-81 22-FEB-81 WARD 22-FEB-81 02-APR-81 JONES 02-APR-81 01-MAY-81 BLAKE 01-MAY-81 09-JUN-81 CLARK 09-JUN-81 08-SEP-81 TURNER 08-SEP-81 28-SEP-81 MARTIN 28-SEP-81 17-NOV-81 KING 17-NOV-81 03-DEC-81 JAMES 03-DEC-81 03-DEC-81 FORD 03-DEC-81 23-JAN-82 MILLER 23-JAN-82 19-APR-87 SCOTT 19-APR-87 23-MAY-87 ADAMS 23-MAY-87
LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle compares the exprs using nonpadded comparison semantics. If the value returned by this function is character data, its datatype is always VARCHAR2.
SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL; LEAST ------ HAROLD
LENGTH returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.
SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL; Length in characters -------------------- 7
LENGTHB returns the length of char in bytes. If char is null, this function returns null. For a single-byte database character set, LENGTHB is equivalent to LENGTH.
This example assumes a double-byte database character set.
SELECT LENGTHB ('CANDIDE') "Length in bytes" FROM DUAL; Length in bytes --------------- 14
LN returns the natural logarithm of n, where n is greater than 0.
SELECT LN(95) "Natural log of 95" FROM DUAL; Natural log of 95 -----------------
4.55387689
LOG returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.
SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL; Log base 10 of 100 ------------------ 2
LOWER returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL; Lowercase -------------------- mr. scott mcmillan
LPAD returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.
SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL; LPAD example --------------- *.*.*.*.*Page 1
LTRIM removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, you must enclose it in single quotes. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.
SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL; LTRIM example ------------ XxyLAST WORD
MAKE_REF creates a REF to a row of an object view or a row in an object table whose object identifier is primary key based.
|
See Also:
|
CREATE TABLE employee (eno NUMBER, ename VARCHAR2(20), salary NUMBER, PRIMARY KEY (eno, ename)); CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename CHAR(20), salary NUMBER); CREATE VIEW emp_view OF emp_type WITH OBJECT IDENTIFIER (eno, ename) AS SELECT * FROM emp; SELECT MAKE_REF(emp_view, 1, 'jack') FROM DUAL; MAKE_REF(EMP_VIEW,1,'JACK') ------------------------------------------------------ 000067030A0063420D06E06F3C00C1E03400400B40DCB10000001C26010001000200 2900000000000F0600810100140100002A0007000A8401FE0000001F02C102146A61 636B2020202020202020202020202020202000000000000000000000000000000000 00000000
MAX returns maximum value of expr. You can use it as an aggregate or analytic function.
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
SELECT MAX(sal) "Maximum" FROM emp; Maximum ---------- 5000
The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.
SELECT mgr, ename, sal, MAX(sal) OVER (PARTITION BY mgr) AS mgr_max FROM emp; MGR ENAME SAL MGR_MAX ---------- ---------- ---------- ---------- 7566 SCOTT 3000 3000 7566 FORD 3000 3000 7698 ALLEN 1600 1600 7698 WARD 1250 1600 7698 JAMES 950 1600 7698 TURNER 1500 1600 7698 MARTIN 1250 1600 7782 MILLER 1300 1300 7788 ADAMS 1100 1100 7839 JONES 2975 2975 7839 CLARK 2450 2975 7839 BLAKE 2850 2975 7902 SMITH 800 800 KING 5000 5000
If you enclose this query in the parent query with a predicate, you can determine the employee who makes the highest salary in each department:
SELECT mgr, ename, sal FROM (SELECT mgr, ename, sal, MAX(sal) OVER (PARTITION BY mgr) AS rmax_sal FROM emp) WHERE sal = rmax_sal; MGR ENAME SAL ---------- ---------- ---------- 7566 SCOTT 3000 7566 FORD 3000 7698 ALLEN 1600 7782 MILLER 1300 7788 ADAMS 1100 7839 JONES 2975 7902 SMITH 800 KING 5000
MIN returns minimum value of expr. You can use it as an aggregate or analytic function.
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
SELECT MIN(hiredate) "Earliest" FROM emp; Earliest --------- 17-DEC-80
The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.
SELECT mgr, ename, hiredate, sal, MIN(sal) OVER(PARTITION BY mgr ORDER BY hiredate RANGE UNBOUNDED PRECEDING) as p_cmin FROM emp; MGR ENAME HIREDATE SAL P_CMIN ---------- ---------- --------- ---------- ---------- 7566 FORD 03-DEC-81 3000 3000 7566 SCOTT 19-APR-87 3000 3000 7698 ALLEN 20-FEB-81 1600 1600 7698 WARD 22-FEB-81 1250 1250 7698 TURNER 08-SEP-81 1500 1250 7698 MARTIN 28-SEP-81 1250 1250 7698 JAMES 03-DEC-81 950 950 7782 MILLER 23-JAN-82 1300 1300 7788 ADAMS 23-MAY-87 1100 1100 7839 JONES 02-APR-81 2975 2975 7839 BLAKE 01-MAY-81 2850 2850 7839 CLARK 09-JUN-81 2450 2450 7902 SMITH 17-DEC-80 800 800 KING 17-NOV-81 5000 5000
MOD returns remainder of m divided by n. Returns m if n is 0.
SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3
This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
m - n * FLOOR(m/n)
The following statement illustrates the difference between the MOD function and the classical modulus:
SELECT m, n, MOD(m, n), m - n * FLOOR(m/n) "Classical Modulus" FROM test_mod_table; M N MOD(M,N) Classical Modulus ---------- ---------- ---------- ----------------- 11 4 3 3 11 -4 3 -1 -11 4 -3 1 -11 -4 -3 -3
MONTHS_BETWEEN returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer. Otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.
SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL; Months ---------- 1.03225806
NEW_TIME returns the date and time in time zone z2 when date and time in time zone z1 are d. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.
The arguments z1 and z2 can be any of these text strings:
The following example returns an Atlantic Standard time, given the Pacific Standard time equivalent:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT NEW_TIME(TO_DATE( '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM DUAL; New Date and Time -------------------- 09-NOV-1999 21:23:45
NEXT_DAY returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.
This example returns the date of the next Tuesday after March 15, 1998.
SELECT NEXT_DAY('15-MAR-98','TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY --------- 16-MAR-98
NLS_CHARSET_DECL_LEN returns the declaration width (in number of characters) of an NCHAR column. The bytecnt argument is the width of the column. The csid argument is the character set ID of the column.
SELECT NLS_CHARSET_DECL_LEN (200, nls_charset_id('ja16eucfixed')) FROM DUAL; NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED')) --------------------------------------------------------
100
NLS_CHARSET_ID returns the NLS character set ID number corresponding to NLS character set name, text. The text argument is a run-time VARCHAR2 value. The text value 'CHAR_CS' returns the database character set ID number of the server. The text value 'NCHAR_CS' returns the national character set ID number of the server.
Invalid character set names return null.
SELECT NLS_CHARSET_ID('ja16euc') FROM DUAL; NLS_CHARSET_ID('JA16EUC') ------------------------- 830
NLS_CHARSET_NAME returns the name of the NLS character set corresponding to ID number n. The character set name is returned as a VARCHAR2 value in the database character set.
If n is not recognized as a valid character set ID, this function returns null.
SELECT NLS_CHARSET_NAME(2) FROM DUAL; NLS_CH ------ WE8DEC
NLS_INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The value of 'nlsparam' can have this form:
'NLS_SORT = sort'
where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. These requirements can result in a return value of a different length than the char. If you omit 'nlsparam', this function uses the default sort sequence for your session.
The following examples show how the linguistic sort sequence results in a different return value from the function:
SELECT NLS_INITCAP
('ijsland') "InitCap" FROM DUAL;
InitCap
-------
Ijsland
SELECT NLS_INITCAP
('ijsland', 'NLS_SORT = XDutch') "InitCap"
FROM DUAL;
InitCap
-------
IJsland
NLS_LOWER returns char, with all letters lowercase. The 'nlsparam' can have the same form and serve the same purpose as in the NLS_INITCAP function.
SELECT NLS_LOWER ('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL; Lower ----- cittá
NLSSORT returns the string of bytes used to sort char. The value of 'nlsparams' can have the form
'NLS_SORT = sort'
where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char.
This function can be used to specify comparisons based on a linguistic sort sequence rather than on the binary value of a string:
SELECT ename FROM emp WHERE NLSSORT (ename, 'NLS_SORT = German') > NLSSORT ('S', 'NLS_SORT = German') ORDER BY ename; ENAME ---------- SCOTT SMITH TURNER WARD
NLS_UPPER returns char, with all letters uppercase. The 'nlsparam' can have the same form and serve the same purpose as in the NLS_INITCAP function.
SELECT NLS_UPPER ('große', 'NLS_SORT = XGerman') "Uppercase" FROM DUAL; Upper ----- GROSS
NTILE is an analytic function. It divides an ordered dataset into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expr, and expr must resolve to a positive constant for each partition.
The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed 1 per bucket, starting with bucket 1.
If expr is greater than the number of rows, a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.
You cannot use NTILE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
The following example divides the values in the SAL column into 4 buckets. The SAL column has 14 values, so the two extra values (the remainder of 14 / 4) are allocated to buckets 1 and 2, which therefore have one more value than buckets 3 or 4.
SELECT ename, sal, NTILE(4) OVER (ORDER BY sal DESC) AS quartile FROM emp; ENAME SAL QUARTILE ---------- ---------- ---------- KING 5000 1 SCOTT 3000 1 FORD 3000 1 JONES 2975 1 BLAKE 2850 2 CLARK 2450 2 ALLEN 1600 2 TURNER 1500 2 MILLER 1300 3 WARD 1250 3 MARTIN 1250 3 ADAMS 1100 4 JAMES 950 4 SMITH 800 4
|
Note: This function is restricted to use with analytic functions. It accepts only numbers as arguments, and returns interval literals. See "Analytic Functions" and "Interval". |
NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. n can be a number or an expression resolving to a number. The value for char_expr specifies the unit of n and must resolve to one of the following string values:
char_expr is case insensitive. Leading and trailing values within the parentheses are ignored. By default, precision of the return is 9.
The following example calculates for each employee, the number of employees hired, by the same manager, within the last 100 days from his/her hiredate:
SELECT mgr, ename, hiredate, COUNT(*) OVER (PARTITION BY mgr ORDER BY hiredate RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count FROM emp; MGR ENAME HIREDATE T_COUNT ---------- ---------- --------- ---------- 7566 FORD 03-DEC-81 1 7566 SCOTT 19-APR-87 1 7698 ALLEN 20-FEB-81 1 7698 WARD 22-FEB-81 2 7698 TURNER 08-SEP-81 1 7698 MARTIN 28-SEP-81 2 7698 JAMES 03-DEC-81 3 7782 MILLER 23-JAN-82 1 7788 ADAMS 23-MAY-87 1 7839 JONES 02-APR-81 1 7839 BLAKE 01-MAY-81 2 7839 CLARK 09-JUN-81 3 7902 SMITH 17-DEC-80 1 KING 17-NOV-81 1
|
Note: This function is restricted to use with analytic functions. It accepts only numbers as arguments, and returns interval literals. See "Analytic Functions" and "Interval". |
NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number. The value for char_expr specifies the unit of n, and must resolve to one of the following string values:
char_expr is case insensitive. Leading and trailing values within the parentheses are ignored. By default, precision of the return is 9.
The following example calculates, for each employee, the total salary of employees hired in the past one year from his/her hiredate.
SELECT ename, hiredate, sal, SUM(sal) OVER (ORDER BY hiredateRANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_salFROM emp;ENAME HIREDATE SAL T_SAL---------- --------- ---------- ----------SMITH 17-DEC-80 800 800ALLEN 20-FEB-81 1600 2400WARD 22-FEB-81 1250 3650JONES 02-APR-81 2975 6625BLAKE 01-MAY-81 2850 9475CLARK 09-JUN-81 2450 11925TURNER 08-SEP-81 1500 13425MARTIN 28-SEP-81 1250 14675KING 17-NOV-81 5000 19675JAMES 03-DEC-81 950 23625FORD 03-DEC-81 3000 23625MILLER 23-JAN-82 1300 24125SCOTT 19-APR-87 3000 3000ADAMS 23-MAY-87 1100 4100
If expr1 is null, NVL returns expr2; if expr1 is not null, NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2.
SELECT ename, NVL(TO_CHAR(COMM), 'NOT APPLICABLE') "COMMISSION" FROM emp WHERE deptno = 30; ENAME COMMISSION ---------- ------------------------------------- ALLEN 300 WARD 500 MARTIN 1400 BLAKE NOT APPLICABLE TURNER 0 JAMES NOT APPLICABLE
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.
If the datatypes of expr2 and expr3 are different, Oracle converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary.
The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value's datatype is VARCHAR2.
The following example shows whether the income of each employee in department 30 is made up of salary plus commission, or just salary, depending on whether the comm column of emp is null or not.
SELECT ename, NVL2(TO_CHAR(COMM), 'SAL & COMM', 'SAL') income FROM emp WHERE deptno = 30; ENAME INCOME ---------- ---------- ALLEN SAL & COMM WARD SAL & COMM MARTIN SAL & COMM BLAKE SAL TURNER SAL & COMM JAMES SAL
PERCENT_RANK is an analytic function, and is similar to the CUME_DIST (cumulative distribution) function. For a row R, PERCENT_RANK calculates the rank of R minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition). The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0.
The following example calculates, for each employee, the percent rank of the employee's salary within the department:
SELECT deptno, ename, sal, PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr FROM emp; DEPTNO ENAME SAL PR ---------- ---------- ---------- ---------- 10 KING 5000 0 10 CLARK 2450 .5 10 MILLER 1300 1 20 SCOTT 3000 0 20 FORD 3000 0 20 JONES 2975 .5 20 ADAMS 1100 .75 20 SMITH 800 1 30 BLAKE 2850 0 30 ALLEN 1600 .2 30 TURNER 1500 .4 30 WARD 1250 .6 30 MARTIN 1250 .6 30 JAMES 950 1
POWER returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.
SELECT POWER(3,2) "Raised" FROM DUAL; Raised ---------- 9
RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.
The following statement ranks the employees within each department based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks. Compare this example with the example for DENSE_RANK.
SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk FROM emp; DEPTNO ENAME SAL COMM RK ---------- ---------- ---------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 3 20 ADAMS 1100 4 20 SMITH 800 5 30 BLAKE 2850 1 30 ALLEN 1600 300 2 30 TURNER 1500 0 3 30 WARD 1250 500 4 30 MARTIN 1250 1400 5 30 JAMES 950 6
RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, the ratio-to-report value also evaluates to null.
The set of values is determined by the query_partition_clause. If you omit that clause, the ratio-to-report is computed over all rows returned by the query.
You cannot use RATIO_TO_REPORT or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
The following example calculates the ratio-to-report of each salesperson's salary to the total of all salespeople's salaries:
SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS rr FROM emp WHERE job = 'SALESMAN'; ENAME SAL RR ---------- ---------- ---------- ALLEN 1600 .285714286 WARD 1250 .223214286 MARTIN 1250 .223214286 TURNER 1500 .267857143
RAWTOHEX converts raw to a character value containing its hexadecimal equivalent.
SELECT RAWTOHEX(raw_column) "Graphics" FROM graphics; Graphics -------- 7D
In a SQL statement, REF takes as its argument a correlation variable (table alias) associated with a row of an object table or an object view. A REF value is returned for the object instance that is bound to the variable or row.
CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename VARCHAR2(20), salary NUMBER); CREATE TABLE emp_table OF emp_type (primary key (eno, ename)); INSERT INTO emp_table VALUES (10, 'jack', 50000); SELECT REF(e) FROM emp_table e; REF(E) ----------------------------------------------------- 0000280209420D2FEABD9400C3E03400400B40DCB1420D2FEABD9300C3E03400400B 40DCB1004049EE0000
REFTOHEX converts argument expr to a character value containing its hexadecimal equivalent. expr must return a REF.
CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename VARCHAR2(20), salary NUMBER); CREATE TABLE emp_table OF emp_type (primary key (eno, ename)); CREATE TABLE dept (dno NUMBER, mgr REF emp_type SCOPE IS emp); INSERT INTO emp_table VALUES (10, 'jack', 50000); INSERT INTO dept SELECT 10, REF(e) FROM emp_table e; SELECT REFTOHEX(mgr) FROM dept; REFTOHEX(MGR) ------------------------------------------------------ 0000220208420D2FEABD9400C3E03400400B40DCB1420D2FEABD9300C3E03400400B 40DCB1
The linear regression functions are:
The linear regression functions fit an ordinary-least-squares regression line to a set of number pairs. You can use them as both aggregate and analytic functions.
Oracle applies the function to the set of (expr1, expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Oracle computes all the regression functions simultaneously during a single pass through the data.
expr1 is interpreted as a value of the dependent variable (a "y value"), and expr2 is interpreted as a value of the independent variable (an "x value"). Both expressions must be numbers.
REGR_SLOPE returns the slope of the line. The return value is a number and can be null. After the elimination of null (expr1, expr2) pairs, it makes the following computation:
COVAR_POP(expr1, expr2) / VAR_POP(expr2)
REGR_INTERCEPT returns the y-intercept of the regression line. The return value is a number and can be null. After the elimination of null (expr1, expr2) pairs, it makes the following computation:
AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
REGR_COUNT returns an integer that is the number of non-null number pairs used to fit the regression line.
REGR_R2 returns the coefficient of determination (also called "R-squared" or "goodness of fit") for the regression. The return value is a number and can be null. VAR_POP(expr1) and VAR_POP(expr2) are evaluated after the elimination of null pairs. The return values are:
NULL if VAR_POP(expr2) = 0 1 if VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 POWER(CORR(expr1,expr),2) if VAR_POP(expr1) > 0 and VAR_POP(expr2 != 0
All of the remaining regression functions return a number and can be null:
REGR_AVGX evaluates the average of the independent variable (expr2) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs:
AVG(expr2)
REGR_AVGY evaluates the average of the dependent variable (expr1) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs:
AVG(expr1)
REGR_SXY, REGR_SXX, REGR_SYY are auxiliary functions that are used to compute various diagnostic statistics.
REGR_SXX makes the following computation after the elimination of null (expr1, expr2) pairs:
REGR_COUNT(expr1, expr2) * VAR_POP(expr2)
REGR_SYY makes the following computation after the elimination of null (expr1, expr2) pairs:
REGR_COUNT(expr1, expr2) * VAR_POP(expr1)
REGR_SXY makes the following computation after the elimination of null (expr1, expr2) pairs:
REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)
The following examples are based on the sales table, described in COVAR_POP.
REGR_SLOPE and REGR_INTERCEPT Examples
The following example determines the slope and intercept of the regression line for the amount of sales and sale profits for each year.
SELECT s_year, REGR_SLOPE(s_amount, s_profit), REGR_INTERCEPT(s_amount, s_profit) FROM sales GROUP BY s_year; S_YEAR REGR_SLOPE REGR_INTER ---------- ---------- ---------- 1998 128.401558 -2277.5684 1999 55.618655 226.855296
The following example determines the cumulative slope and cumulative intercept of the regression line for the amount of sales and sale profits for each day in 1998:
SELECT s_year, s_month, s_day, REGR_SLOPE(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_SLOPE, REGR_INTERCEPT(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_ICPT FROM sales WHERE s_year=1998 ORDER BY s_month, s_day; S_YEAR S_MONTH S_DAY CUM_SLOPE CUM_ICPT ---------- ---------- ---------- ---------- ---------- 1998 6 5 1998 6 9 132.093066 401.884833 1998 6 9 132.093066 401.884833 1998 6 10 131.829612 450.65349 1998 8 21 132.963737 -153.5413 1998 8 25 130.681718 -451.47349 1998 8 25 130.681718 -451.47349 1998 8 26 128.76502 -236.50096 1998 11 9 131.499934 -1806.7535 1998 11 9 131.499934 -1806.7535 1998 11 10 130.190972 -2323.3056 1998 11 10 130.190972 -2323.3056 1998 11 11 128.401558 -2277.5684
REGR_COUNT Examples
The following example returns the number of sales transactions in the sales table that resulted in a profit. (None of the rows for containing a sales amount have a null in the s_profit column, so the function returns the total number of rows in the sales table.)
SELECT REGR_COUNT(s_amount, s_profit) FROM sales; REGR_COUNT ---------- 23
The following example computes, for each day, the cumulative number of transactions within each month for the year 1998:
SELECT s_month, s_day, REGR_COUNT(s_amount,s_profit) OVER (PARTITION BY s_month ORDER BY s_day) FROM SALES WHERE S_YEAR=1998 ORDER BY S_MONTH; S_MONTH S_DAY REGR_COUNT ---------- ---------- ---------- 6 5 1 6 9 3 6 9 3 6 10 4 8 21 1 8 25 3 8 25 3 8 26 4 11 9 2 11 9 2 11 10 4 11 10 4 11 11 5
REGR_R2 Examples
The following example computes the coefficient of determination of the regression line for amount of sales and sale profits:
SELECT REGR_R2(s_amount, s_profit) FROM sales; REGR_R2(S_ ---------- .942435028
The following example computes the cumulative coefficient of determination of the regression line for monthly sales and monthly profits for each month in 1998:
SELECT s_month, REGR_R2(SUM(s_amount), SUM(s_profit)) OVER (ORDER BY s_month) FROM SALES WHERE s_year=1998 GROUP BY s_month ORDER BY s_month; S_MONTH REGR_R2(SU ---------- ---------- 6 8 1 11 .740553632
REGR_AVGY and REGR_AVGX Examples
The following example calculates the regression average for the amount of sales and sale profits for each year:
SELECT s_year, REGR_AVGY(s_amount, s_profit), REGR_AVGX(s_amount, s_profit) FROM sales GROUP BY s_year; S_YEAR REGR_AVGY( REGR_AVGX( ---------- ---------- ---------- 1998 41227.5462 338.820769 1999 7330.748 127.725
The following example calculates the cumulative averages for the amount of sales and sale profits in 1998:
SELECT s_year, s_month, s_day, REGR_AVGY(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_AMOUNT, REGR_AVGX(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_PROFIT FROM sales WHERE s_year=1998 ORDER BY s_month, s_day; S_YEAR S_MONTH S_DAY CUM_AMOUNT CUM_PROFIT ---------- ---------- ---------- ---------- ---------- 1998 6 5 16068 118.2 1998 6 9 44375.6667 332.9 1998 6 9 44375.6667 332.9 1998 6 10 52678.25 396.175 1998 8 21 44721.72 337.5 1998 8 25 45333.8 350.357143 1998 8 25 45333.8 350.357143 1998 8 26 47430.7 370.1875 1998 11 9 41892.91 332.317 1998 11 9 41892.91 332.317 1998 11 10 40777.175 331.055833 1998 11 10 40777.175 331.055833 1998 11 11 41227.5462 338.820769
REGR_SXY, REGR_SXX, and REGR_SYY Examples
The following example computes the REGR_SXY, REGR_SXX, and REGR_SYY values for the regression analysis of amount of sales and sale profits for each year:
SELECT s_year, REGR_SXY(s_amount, s_profit), REGR_SYY(s_amount, s_profit), REGR_SXX(s_amount, s_profit) FROM sales GROUP BY s_year; S_YEAR REGR_SXY(S REGR_SYY(S REGR_SXX(S ---------- ---------- ---------- ---------- 1998 48723551.8 6423698688 379462.311 1999 3605361.62 200525751 64822.8841
The following example computes the cumulative REGR_SXY, REGR_SXX, and REGR_SYY statistics for amount of sales and sale profits for each month-day value in 1998:
SELECT s_year, s_month, s_day, REGR_SXY(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_SXY, REGR_SYY(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_SXY, REGR_SXX(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_SXX FROM sales WHERE s_year=1998 ORDER BY s_month, s_day; S_YEAR S_MONTH S_DAY CUM_SXY CUM_SXY CUM_SXX ---------- ---------- ---------- ---------- ---------- ---------- 1998 6 5 0 0 0 1998 6 9 14822857.8 1958007601 112215.26 1998 6 9 14822857.8 1958007601 112215.26 1998 6 10 21127009.3 2785202281 160259.968 1998 8 21 30463997.3 4051329674 229115.08 1998 8 25 34567985.3 4541739739 264520.437 1998 8 25 34567985.3 4541739739 264520.437 1998 8 26 36896592.7 4787971157 286542.049 1998 11 9 45567995.3 6045196901 346524.854 1998 11 9 45567995.3 6045196901 346524.854 1998 11 10 48178003.8 6392056557 370056.411 1998 11 10 48178003.8 6392056557 370056.411 1998 11 11 48723551.8 6423698688 379462.311
REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes -------------- BLACK and BLUE
ROUND returns n rounded to m places right of the decimal point. If m is omitted, n is rounded to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.
SELECT ROUND(15.193,1) "Round" FROM DUAL; Round ---------- 15.2 SELECT ROUND(15.193,-1) "Round" FROM DUAL; Round ---------- 20
ROUND returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day.
SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR')
"New Year" FROM DUAL;
New Year
---------
01-JAN-93
ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the ORDER_BY_clause, beginning with 1.
You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
For each department in the emp table, the following example assigns numbers to each row in order of employee's hire date:
SELECT deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY hiredate) AS emp_id FROM emp; DEPTNO ENAME HIREDATE EMP_ID ---------- ---------- --------- ---------- 10 CLARK 09-JUN-81 1 10 KING 17-NOV-81 2 10 MILLER 23-JAN-82 3 20 SMITH 17-DEC-80 1 20 JONES 02-APR-81 2 20 FORD 03-DEC-81 3 20 SCOTT 19-APR-87 4 20 ADAMS 23-MAY-87 5 30 ALLEN 20-FEB-81 1 30 WARD 22-FEB-81 2 30 BLAKE 01-MAY-81 3 30 TURNER 08-SEP-81 4 30 MARTIN 28-SEP-81 5 30 JAMES 03-DEC-81 6
ROW_NUMBER is a nondeterministic function. However, hiredate is a unique key, so the results of this application of the function are deterministic.
ROWIDTOCHAR converts a rowid value to VARCHAR2 datatype. The result of this conversion is always 18 characters long.
SELECT ROWID FROM offices WHERE ROWIDTOCHAR(ROWID) LIKE '%Br1AAB%'; ROWID ------------------ AAAAZ6AABAAABr1AAB
RPAD returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.
SELECT RPAD('MORRISON',12,'ab') "RPAD example" FROM DUAL; RPAD example ----------------- MORRISONabab
RTRIM returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, you must enclose it in single quotes. RTRIM works similarly to LTRIM.
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g." FROM DUAL; RTRIM e.g ------------- BROWNINGyxX
If n<0, SIGN returns -1. If n=0, the function returns 0. If n>0, SIGN returns 1.
SELECT SIGN(-15) "Sign" FROM DUAL; Sign ---------- -1
SIN returns the sine of n (an angle expressed in radians).
SELECT SIN(30 * 3.14159265359/180) "Sine of 30 degrees" FROM DUAL; Sine of 30 degrees ------------------ .5
SINH returns the hyperbolic sine of n.
SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL; Hyperbolic sine of 1 -------------------- 1.17520119
SOUNDEX returns a character string containing the phonetic representation of char. This function allows you to compare words that are spelled differently, but sound alike in English.
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
b, f, p, v = 1 c, g, j, k, q, s, x, z = 2 d, t = 3 l = 4 m, n = 5 r = 6
SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE'); ENAME ---------- SMITH
SQRT returns square root of n. The value n cannot be negative. SQRT returns a "real" result.
SELECT SQRT(26) "Square root" FROM DUAL; Square root ----------- 5.09901951
STDDEV returns sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns a null.
Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function.
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
|
See Also:
|
SELECT STDDEV(sal) "Deviation" FROM emp; Deviation ---------- 1182.50322
The query in the following example returns the cumulative standard deviation of salary values in Department 30 ordered by hiredate:
SELECT ENAME, SAL, STDDEV(SAL) OVER (ORDER BY HIREDATE) FROM EMP WHERE DEPTNO=30; ENAME SAL STDDEV(SAL ---------- ---------- ---------- ALLEN 1600 0 WARD 1250 247.487373 BLAKE 2850 841.130192 TURNER 1500 715.308791 MARTIN 1250 666.520817 JAMES 950 668.331255
STDDEV_POP computes the population standard deviation and returns the square root of the population variance. You can use it as both an aggregate and analytic function.
The expr is a number expression, and the function returns a value of type NUMBER. This function is same as the square root of the VAR_POP function. When VAR_POP returns null, this function returns null.
The following example returns the population and sample standard deviations of profit from sales in the SALES table.
SELECT STDDEV_POP(s_profit), STDDEV_SAMP(s_profit) FROM sales; STDDEV_POP STDDEV_SAM ---------- ---------- 173.975774 177.885831
The following example returns the population standard deviations of salaries in the emp table by department:
SELECT deptno, ename, sal, STDDEV_POP(sal) OVER (PARTITION BY deptno) AS pop_std FROM emp; DEPTNO ENAME SAL POP_STD ---------- ---------- ---------- ---------- 10 CLARK 2450 1546.14215 10 KING 5000 1546.14215 10 MILLER 1300 1546.14215 20 SMITH 800 1004.73877 20 ADAMS 1100 1004.73877 20 FORD 3000 1004.73877 20 SCOTT 3000 1004.73877 20 JONES 2975 1004.73877 30 ALLEN 1600 610.100174 30 BLAKE 2850 610.100174 30 MARTIN 1250 610.100174 30 JAMES 950 610.100174 30 TURNER 1500 610.100174 30 WARD 1250 610.100174
STDDEV_SAMP computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.
The expr is a number expression, and the function returns a value of type NUMBER. This function is same as the square root of the VAR_SAMP function. When VAR_SAMP returns null, this function returns null.
The following example returns the population and sample standard deviations of profit from sales in the SALES table.
SELECT STDDEV_POP(s_profit), STDDEV_SAMP(s_profit) FROM sales; STDDEV_POP STDDEV_SAM ---------- ---------- 173.975774 177.885831
The following example returns the sample standard deviation of salaries in the EMP table by department:
SELECT deptno, ename, hiredate, sal, STDDEV_SAMP(sal) OVER (PARTITION BY deptno ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev FROM emp; DEPTNO ENAME HIREDATE SAL CUM_SDEV ---------- ---------- --------- ---------- ---------- 10 CLARK 09-JUN-81 2450 10 KING 17-NOV-81 5000 1803.12229 10 MILLER 23-JAN-82 1300 1893.62967 20 SMITH 17-DEC-80 800 20 JONES 02-APR-81 2975 1537.95725 20 FORD 03-DEC-81 3000 1263.01557 20 SCOTT 19-APR-87 3000 1095.8967 20 ADAMS 23-MAY-87 1100 1123.3321 30 ALLEN 20-FEB-81 1600 30 WARD 22-FEB-81 1250 247.487373 30 BLAKE 01-MAY-81 2850 841.130192 30 TURNER 08-SEP-81 1500 715.308791 30 MARTIN 28-SEP-81 1250 666.520817 30 JAMES 03-DEC-81 950 668.331255
SUBSTR returns a portion of char, beginning at character m, n characters long.
m is 0, it is treated as 1.
m is positive, Oracle counts from the beginning of char to find the first character.
m is negative, Oracle counts backwards from the end of char.
n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned.
Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL; Substring --------- CDEF
SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL; Substring --------- CDEF
SUBSTRB is the same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.
Floating-point numbers passed as arguments to SUBSTRB are automatically converted to integers.
Assume a double-byte database character set:
SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL; Substring with bytes -------------------- CD
SUM returns sum of values of expr. You can use it as an aggregate or analytic function.
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
The following example calculates the sum of all salaries in the emp table:
SELECT SUM(sal) "Total" FROM emp; Total ---------- 29025
The following example calculates, for each manager, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary:
SELECT mgr, ename, sal, SUM(sal) OVER (PARTITION BY mgr ORDER BY sal RANGE UNBOUNDED PRECEDING) l_csum FROM emp; MGR ENAME SAL L_CSUM ---------- ---------- ---------- ---------- 7566 SCOTT 3000 6000 7566 FORD 3000 6000 7698 JAMES 950 950 7698 WARD 1250 3450 7698 MARTIN 1250 3450 7698 TURNER 1500 4950 7698 ALLEN 1600 6550 7782 MILLER 1300 1300 7788 ADAMS 1100 1100 7839 CLARK 2450 2450 7839 BLAKE 2850 5300 7839 JONES 2975 8275 7902 SMITH 800 800 KING 5000 5000
SYS_CONTEXT returns the value of attribute associated with the context namespace. You can use this function in both SQL and PL/SQL statements. The context namespace must already have been created, and the associated attribute and its value must also have been set using the DBMS_SESSION.set_context procedure. The namespace must be a valid SQL identifier. The attribute name can be any string, and it is not case sensitive, but it cannot exceed 30 bytes in length.
The datatype of the return value is VARCHAR2. The default maximum size of the return value is 256 bytes. You can override this default by specifying the optional length parameter. The valid range of values is 1 to 4000 bytes. (If you specify an invalid value, Oracle ignores it and uses the default.)
Oracle8i provides a built-in namespace called USERENV, which describes the current session. The predefined attributes of namespace USERENV are listed Table 4-1, along with the lengths of their return strings.
|
See Also:
|
The following statement returns the name of the user who logged onto the database:
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL; SYS_CONTEXT ('USERENV', 'SESSION_USER') ------------------------------------------------------ SCOTT
The following example returns the group number that was set as the value for the attribute group_no in the PL/SQL package that was associated with the context hr_apps when hr_apps was created:
SELECT SYS_CONTEXT ('hr_apps', 'group_no') "User Group" FROM DUAL; User Group ---------- Sales
SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier and a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.
The following examples return the 32-character hexadecimal representation of the 16-byte raw value of the global unique identifier:
CREATE TABLE mytable (col1 VARCHAR2(10), col2 RAW(32)); INSERT INTO mytable VALUES ('BOB', SYS_GUID()); SELECT * FROM mytable; COL1 COL2 ---------- -------------------------------------------------- BOB 5901B85D996C570CE03400400B40DCB1 SELECT SYS_GUID() FROM DUAL; SYS_GUID() -------------------------------- 5901B85D996D570CE03400400B40DCB1
SYSDATE returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW" FROM DUAL; NOW ------------------- 10-29-1999 20:27:11
TAN returns the tangent of n (an angle expressed in radians).
SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL; Tangent of 135 degrees ---------------------- - 1
TANH returns the hyperbolic tangent of n.
SELECT TANH(.5) "Hyperbolic tangent of .5" FROM DUAL; Hyperbolic tangent of .5 ------------------------ .462117157
TO_CHAR converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format.
The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
If you omit nlsparams, this function uses the default date language for your session.
SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY') "New date format" FROM emp WHERE ename = 'BLAKE'; New date format ------------------ May 01, 1981
TO_CHAR converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
The 'nlsparams' specifies these characters that are returned by number format elements:
This argument can have this form:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.
In this example, the output is blank padded to the left of the currency symbol.
SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL; Amount -------------- $10,000.00- SELECT TO_CHAR(-10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ------------------- AusDollars10.000,00-
|
Note: In the optional number format |
TO_DATE converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer.
The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion.
Do not use the TO_DATE function with a DATE value for the char argument. The first 2 digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.
INSERT INTO bonus (bonus_date) SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL;
TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement.
Before using this function, you must create a LOB column to receive the converted LONG values. To convert LONGs, create a CLOB column. To convert LONG RAWs, create a BLOB column.
Given the following tables:
CREATE TABLE long_table (n NUMBER, long_col LONG); CREATE TABLE lob_table (n NUMBER, lob_col CLOB);
use this function to convert LONG to LOB values as follows:
INSERT INTO lob_table SELECT n, TO_LOB(long_col) FROM long_table;
TO_MULTI_BYTE returns char with all of its single-byte characters converted to their corresponding multibyte characters. Any single-byte characters in char that have no multibyte equivalents appear in the output string as single-byte characters. This function is useful only if your database character set contains both single-byte and multibyte characters.
TO_NUMBER converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.
UPDATE emp SET sal = sal + TO_NUMBER('100.00', '9G999D99') WHERE ename = 'BLAKE';
The 'nlsparams' string in this function has the same purpose as it does in the TO_CHAR function for number conversions.
SELECT TO_NUMBER('-AusDollars100','L9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ---------- -100
TO_SINGLE_BYTE returns char with all of its multibyte characters converted to their corresponding single-byte characters. Any multibyte characters in char that have no single-byte equivalents appear in the output as multibyte characters. This function is useful only if your database character set contains both single-byte and multibyte characters.
TRANSLATE returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License" FROM DUAL; License -------- 9XXX999
The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') "Translate example" FROM DUAL; Translate example ----------------- 2229
TRANSLATE ... USING converts text into the character set specified for conversions between the database character set and the national character set.
The text argument is the expression to be converted.
Specifying the USING CHAR_CS argument converts text into the database character set. The output datatype is VARCHAR2.
Specifying the USING NCHAR_CS argument converts text into the national character set. The output datatype is NVARCHAR2.
This function is similar to the Oracle CONVERT function, but must be used instead of CONVERT if either the input or the output datatype is being used as NCHAR or NVARCHAR2.
The examples below use the following table and table values:
CREATE TABLE t1 (char_col CHAR(20), nchar_col nchar(20)); INSERT INTO t1 VALUES ('Hi', N'Bye'); SELECT * FROM t1; CHAR_COL NCHAR_COL -------- --------- Hi Bye UPDATE t1 SET nchar_col = TRANSLATE(char_col USING NCHAR_CS); UPDATE t1 SET char_col = TRANSLATE(nchar_col USING CHAR_CS); SELECT * FROM t1; CHAR_COL NCHAR_COL -------- --------- Hi Hi UPDATE t1 SET nchar_col = TRANSLATE('deo' USING NCHAR_CS); UPDATE t1 SET char_col = TRANSLATE(N'deo' USING CHAR_CS); SELECT * FROM t1; CHAR_COL NCHAR_COL -------- --------- deo deo
TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, you must enclose it in single quotes.
LEADING, Oracle removes any leading characters equal to trim_character.
TRAILING, Oracle removes any trailing characters equal to trim_character.
BOTH or none of the three, Oracle removes leading and trailing characters equal to trim_character.
trim_character, the default value is a blank space.
trim_source, Oracle removes leading and trailing blank spaces.
VARCHAR2. The maximum length of the value is the length of trim_source.
trim_source or trim_character is a null value, then the TRIM function returns a null value.
This example trims leading and trailing zeroes from a number:
SELECT TRIM (0 FROM 0009872348900) "TRIM Example" FROM DUAL; TRIM example ------------ 98723489
TRUNC returns n truncated to m decimal places. If m is omitted, n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.
SELECT TRUNC(15.79,1) "Truncate" FROM DUAL; Truncate ---------- 15.7
SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL; Truncate ---------- 10
TRUNC returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day.
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-92
UID returns an integer that uniquely identifies the session user (the user who logged on).
SELECT UID FROM DUAL; UID ---------- 19
UPPER returns char, with all letters uppercase. The return value has the same datatype as the argument char.
SELECT UPPER('Large') "Uppercase" FROM DUAL; Upper ----- LARGE
USER returns the name of the session user (the user who logged on) with the datatype VARCHAR2. Oracle compares values of this function with blank-padded comparison semantics.
In a distributed SQL statement, the UID and USER functions identify the user on your local database. You cannot use these functions in the condition of a CHECK constraint.
SELECT USER, UID FROM DUAL; USER UID ------------------------------ ---------- SCOTT 19
USERENV returns information of VARCHAR2 datatype about the current session. This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session. You cannot use USERENV in the condition of a CHECK constraint. Table 4-2 describes the values for the option argument.
| Option | Return Value |
|---|---|
|
' |
|
|
|
|
|
|
Oracle recommends that you use the application context feature or the See Also: - Oracle8i Concepts for information on application context - CREATE CONTEXT and SYS_CONTEXT |
|
' |
|
|
' |
|
|
' |
ISDBA returns ' |
|
' |
|
|
' |
language_territory.characterset |
|
' |
|
|
' |
|
SELECT USERENV('LANGUAGE') "Language" FROM DUAL; Language ----------------------------------- AMERICAN_AMERICA.WE8DEC
In a SQL statement, VALUE takes as its argument a correlation variable (table alias) associated with a row of an object table and returns object instances stored in the object table. The type of the object instances is the same type as the object table.
CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename VARCHAR2(20), salary NUMBER); CREATE TABLE emp_table OF emp_type (primary key (eno, ename)); INSERT INTO emp_table VALUES (10, 'jack', 50000); SELECT VALUE(e) FROM emp_table e; VALUE(E)(ENO, ENAME, SALARY) ---------------------------------------------------- EMP_TYPE(10, 'jack', 50000)
VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
The expr is a number expression, and the function returns a value of type NUMBER. If the function is applied to an empty set, it returns null. The function makes the following calculation:
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
The following example returns the population variance of the salaries in the EMP table:
SELECT VAR_POP(sal) FROM emp; VAR_POP(SAL) ------------ 1298434.31
The following example calculates the cumulative population and sample variances of the monthly sales in 1998:
SELECT s_month, VAR_POP(SUM(s_amount)) OVER (ORDER BY s_month), VAR_SAMP(SUM(s_amount)) OVER (ORDER BY s_month) FROM sales WHERE s_year =1998 GROUP BY s_month; S_MONTH VAR_POP(SU VAR_SAMP(S ---------- ---------- ---------- 6 0 8 440588496 881176992 11 538819892 808229838
VAR_SAMP returns the sample variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
The expr is a number expression, and the function returns a value of type NUMBER. If the function is applied to an empty set, it returns null. The function makes the following calculation:
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)
This function is similar to VARIANCE, except that given an input set of one element, VARIANCE returns 0 and VAR_SAMP returns null.
The following example returns the sample variance of the salaries in the emp table.
SELECT VAR_SAMP(sal) FROM emp; VAR_SAMP(SAL) ------------- 1398313.87
The following example calculates the cumulative population and sample variances of the monthly sales in 1998:
SELECT s_month, VAR_POP(SUM(s_amount)) OVER (ORDER BY s_month), VAR_SAMP(SUM(s_amount)) OVER (ORDER BY s_month) FROM sales WHERE s_year =1998 GROUP BY s_month; S_MONTH VAR_POP(SU VAR_SAMP(S ---------- ---------- ---------- 6 0 8 440588496 881176992 11 538819892 808229838
VARIANCE returns variance of expr. You can use it as an aggregate or analytic function.
Oracle calculates the variance of expr as follows:
If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.
The following example calculates the variance of all salaries in the emp table:
SELECT VARIANCE(sal) "Variance" FROM emp; Variance ---------- 1389313.87
The query returns the cumulative variance of salary values in Department 30 ordered by hiredate.
SELECT ename, sal, VARIANCE(sal) OVER (ORDER BY hiredate) FROM emp WHERE deptno=30; ENAME SAL VARIANCE(S ---------- ---------- ---------- ALLEN 1600 0 WARD 1250 61250 BLAKE 2850 707500 TURNER 1500 511666.667 MARTIN 1250 444250 JAMES 950 446666.667
VSIZE returns the number of bytes in the internal representation of expr. If expr is null, this function returns null.
SELECT ename, VSIZE (ename) "BYTES" FROM emp WHERE deptno = 10; ENAME BYTES ---------- ---------- CLARK 5 KING 4 MILLER 6
Table 4-3 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.
The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.
|
See Also: Oracle8i Reference and Oracle8i National Language Support Guide for information on this parameter |
You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL functions. User functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.
For example, user functions can be used in the following:
SELECT statement
WHERE clause
CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
VALUES clause of an INSERT statement
SET clause of an UPDATE statement
See Also:
User functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement.
To use a user function in a SQL expression, you must own or have EXECUTE privilege on the user function. To query a view defined with a user function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.
|
See Also:
|
Within a SQL statement, the names of database columns take precedence over the names of functions with no parameters. For example, if user scott creates the following two objects in his own schema:
CREATE TABLE emp(new_sal NUMBER, ...); CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;
then in the following two statements, the reference to NEW_SAL refers to the column emp.new_sal:
SELECT new_sal FROM emp; SELECT emp.new_sal FROM emp;
To access the function new_sal, you would enter:
SELECT scott.new_sal FROM emp;
Here are some sample calls to user functions that are allowed in SQL expressions:
circle_area (radius) payroll.tax_rate (empno) scott.payroll.tax_rate (dependent, empno)@ny
To call the tax_rate user function from schema scott, execute it against the ss_no and sal columns in tax_table, and place the results in the variable income_tax, specify the following:
SELECT scott.tax_rate (ss_no, sal) INTO income_tax FROM tax_table WHERE ss_no = tax_id;
If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:
PAYROLL package in the current schema.
PAYROLL package is not found, look for a schema name PAYROLL that contains a top-level TAX_RATE function. If no such function is found, return an error.
PAYROLL package is found in the current schema, look for a TAX_RATE function in the PAYROLL package. If no such function is found, return an error.
You can also refer to a stored top-level function using any synonym that you have defined for it.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|