| General |
Note: Demos referencing the Oracle sample schemas such as oe and
sh have been copied from the documentation at http://tahiti.oracle.com
for our convenience. Examples too complex for this page are referenced
back to the original source where links may or may not be maintained.
Credit for their development belongs to Francisco Abedrabbo and his team. |
| |
| ABS |
| Returns the absolute value of a number |
ABS(<value>) |
| SELECT ABS(-100) FROM DUAL; |
| |
| ACOS |
| Returns the arc cosine of a number |
ACOS(<value>) |
| SELECT ACOS(0.5) ARC_COSINE
FROM DUAL; |
| |
| ASIN |
| Returns the arc sin of a number |
ASIN(<value>) |
| SELECT ASIN(0.5) ARC_SINE
FROM DUAL; |
| |
| ATAN |
| Returns the arc tanget of a number |
ATAN(<value>) |
| SELECT ATAN(0.5) ARC_TANGENT
FROM DUAL; |
| |
| ATAN2 |
|
Arc tangent of the first value divided by the arc tangent of the second |
ATAN2(<value>, <value>) |
| SELECT ATAN2(0.5, 0.4) ARC_TANGET_DIV
FROM DUAL; |
| |
| AVG |
| Returns the average of a column of numbers |
AVG(<value>) |
SELECT AVG(initial_extent) FROM user_tables;
SELECT AVG( DISTINCT initial_extent) FROM user_tables; |
| |
| BITAND |
Computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative
integers
Overload 1 |
BITAND(LEFT IN PLS_INTEGER, RIGHT IN
PLS_INTEGER) RETURN
PLS_INTEGER |
| TBD |
Overload 2 |
BITAND(LEFT IN INTEGER, RIGHT IN
INTEGER) RETURN INTEGER |
conn oe/oe
SELECT order_id, customer_id,
DECODE(BITAND(order_status,1),1, 'Warehouse','PostOffice')
Location,
DECODE(BITAND(order_status,2),2, 'Ground', 'Air') Method,
DECODE(BITAND(order_status,4),4,'Insured','Certified') Receipt
FROM orders
WHERE order_status < 8; |
| |
| CEIL |
| Smallest integer greater than or equal to a decimal value |
CEIL(<value>) |
| SELECT CEIL(12345.67)
FROM DUAL; |
| |
| COALESCE |
Returns the first non-null value |
COALESCE(<value>, <value>, ....) |
CREATE TABLE test (
col1 NUMBER(3),
col2 NUMBER(3),
col3 NUMBER(3));
INSERT INTO test VALUES (1, NULL, NULL);
INSERT INTO test VALUES (NULL, 2, NULL);
INSERT INTO test VALUES (NULL, NULL, 3);
INSERT INTO test VALUES (1, NULL, 3);
INSERT INTO test VALUES (NULL, 2, 3);
SELECT * FROM test;
SELECT COALESCE(col1, col2, col3) FROM test; |
| |
| CORR |
| Returns the coefficient of correlation of a set of
number pairs |
For information see the CORR page or tahiti.oracle.com
|
| |
| CORR_K |
| The CORR_K function calculates the Pearson's correlation coefficient |
Pearson's correlation reflects the degree of linear relationship between two variables. It ranges from +1 to -1. A correlation of +1 means that there is a perfect positive linear relationship between variables.
For information see the CORR_K page or tahiti.oracle.com |
| |
| CORR_S |
| The CORR_S function calculates the Spearman's rho correlation coefficient |
For information see CORR_S or tahiti.oracle.com |
| |
| COS |
|
Returns the cosine of a number (an angle expressed in radians) |
COS(<value>) |
| SELECT COS(180*3.1415926/180) COSINE
FROM DUAL; |
| |
| COSH |
| The hyperbolic cosine of a number |
COSH(<value>) |
|
SELECT COSH(0) HYPERBOLIC_COS FROM DUAL; |
| |
| COUNT |
| The number of rows returned by a query |
COUNT(<value>) |
-- To create the all_objs table: Click
Here
SELECT COUNT(*) FROM all_objs; |
| |
| COVAR_POP |
The population covariance of a set of number pairs |
COVAR_POP(<expression1>, <expression2>) |
conn sh/sh
SELECT t.calendar_month_number,
COVAR_POP(s.amount_sold, s.amount_sold) AS CP,
COVAR_SAMP(s.amount_sold, s.amount_sold) AS CS
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.calendar_year = 1998
GROUP BY t.calendar_month_number; |
| |
| COVAR_SAMP |
| The sample covariance of a set of number pairs |
COVAR_SAMP(<expression1>, <expression2>) |
| See COVAR_POP demo. |
| |
| CUME_DIST |
Returns the cumulative distribution of a value in a group of values |
CUME_DIST(<value>) |
conn oe/oe
SELECT CUME_DIST(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) CUME_DIST_OF_15500
FROM employees; |
| |
| DENSE_RANK |
| Computes the rank of a row in an ordered group of rows |
DENSE_RANK(<value>) |
conn oe/oe
SELECT DENSE_RANK(15500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) DENSE_RANK_OF_15500
FROM employees; |
| |
| DUMP |
Returns a VARCHAR2
value containing the datatype code, length in bytes, and internal
representation of a value |
DUMP(<value> [,<return_format>[,<start_position>[,<length>]]]
| 8 |
Octal |
| 10 |
Decimal |
| 16 |
Hexidecimal |
| 17 |
Single Characters |
| 1008 |
octal notation with the character set name |
| 1010 |
decimal notation with the character set name |
| 1016 |
hexadecimal notation with the character set name |
| 1017 |
single characters with the character set name |
|
col drows format a30
SELECT table_name, num_rows, DUMP(num_rows) DROWS
FROM user_tables;
SELECT table_name, num_rows, DUMP(num_rows, 8) DROWS
FROM user_tables; |
| |
| EXP |
Returns e raised to to an exponential power |
EXP(<value>) |
SELECT 2.71828183 * 2.71828183 FROM DUAL;
SELECT EXP(2) FROM DUAL;
SELECT 2.71828183 * 2.71828183 * 2.71828183 FROM DUAL;
SELECT EXP(3) FROM DUAL; |
| |
| FIRST |
Returns the row ranked first using DENSE_RANK |
SELECT <aggregate_function(column_name)> KEEP
(DENSE_RANK FIRST ORDER BY <column_name>)
FROM <table_name>
GROUP BY <column_name>; |
conn oe/oe
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) WORST,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) BEST
FROM employees
GROUP BY department_id; |
| |
| FLOOR |
| Returns the largest integer less than or equal to a decimal value |
FLOOR(<string_or_column>) |
| SELECT FLOOR(12345.67) FROM DUAL; |
| |
| GREATEST |
| Returns the largest of multiple values |
GREATEST(<value>, <value>, .... ) |
| SELECT GREATEST(9, 67.6, 10) FROM DUAL; |
| |
| LAST |
| Returns the row ranked last using DENSE_RANK |
See LAST or the FIRST demo above.
|
| |
| LEAST |
| Returns the smallest of multiple values |
LEAST(<value>, <value>, ....) |
| SELECT LEAST(9, 67.6, 10) FROM DUAL; |
| |
| LENGTH |
| Returns length in
characters |
LENGTH(<value>) |
|
SELECT bytes, LENGTH(bytes) FROM user_segments; |
| |
| LENGTHB |
| Returns length in bytes |
LENGTHB(<value>) |
|
SELECT bytes, LENGTHB(bytes) FROM user_segments; |
| Note: Additional forms of
LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available. |
| |
| LN |
| Returns the natural log of a number |
LN(<value>) |
| SELECT LN(2) NATURAL_LOG FROM DUAL; |
| |
| LOG |
| Returns the logarithm, base m of n |
LOG(<m_value>,<n_value>) |
SELECT LOG(10,100) FROM DUAL;
SELECT LOG(100,10) FROM DUAL; |
| |
| MAX |
| Returns the maximum value returned by a query |
MAX(<column_name>) |
|
SELECT MAX(initial_extent) FROM all_tables; |
| |
| MEDIAN |
| Returns the middle value of a set |
MEDIAN(<column_name>) |
|
SELECT MEDIAN(initial_extent) FROM all_tables; |
| |
| MIN |
| Returns the minimum value returned by a query |
MIN(<column_name>) |
|
SELECT MIN(initial_extent) FROM all_tables; |
| |
| MOD |
|
Returns the modulus of a number. Same as remainder except uses FLOOR |
MOD(<m_value>, <n_value>) |
SELECT MOD(3, 2) FROM DUAL;
SELECT MOD(6, 2) FROM DUAL; |
| |
| NANVL |
Returns Alternate Number If The Value Is Not A Number |
NANVL(<value_evaluated>, <value_returned>) |
CREATE TABLE fpd (
dec_num NUMBER(10,2),
bin_double BINARY_DOUBLE,
bin_float BINARY_FLOAT);
INSERT INTO fpd VALUES (0, 'NaN', 'NaN');
COMMIT;
SELECT * FROM fpd;
SELECT bin_double, NANVL(bin_double, 0)
FROM fpd;
SELECT bin_float, NANVL(bin_float, 0)
FROM fpd;
INSERT INTO fpd VALUES ('NaN', 'NaN', 'NaN');
COMMIT;
SELECT bin_float, NANVL(dec_number, 0)
FROM fpd; |
| |
| NVL |
Returns a Value if the Expression IS NULL |
NVL(<expression>, <return_value>) |
set serveroutput on
DECLARE
i PLS_INTEGER;
BEGIN
SELECT NVL(i, 93)
INTO i
FROM DUAL;
dbms_output.put_line('i1: ' || i);
SELECT NVL(i, 39)
INTO i
FROM DUAL;
dbms_output.put_line('i2: ' || i);
END;
/ |
| |
| NVL2 |
Returns First Value if NULL, Second Value if NOT NULL |
NVL2(<expression>, <return_if_value>, <return_if_not_null>) |
CREATE TABLE t (
category VARCHAR2(25),
outval NUMBER(3),
inval NUMBER(3));
INSERT INTO t VALUES ('Groceries', 10, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 100);
INSERT INTO t VALUES ('Groceries', 20, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 200);
INSERT INTO t VALUES ('Groceries', 30, NULL);
SELECT * FROM t;
SELECT category, SUM(NVL2(outval, -outval, inval)) NET
FROM t
GROUP BY category; |
| |
| PERCENT_RANK |
| Calculates for a row r and a sort specification, the rank of row r
minus 1 divided by the number of rows in the aggregate group |
PERCENT_RANK(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>) |
conn oe/oe
SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Percent-Rank"
FROM employees; |
| |
| PERCENTILE_CONT |
| Takes a percentile value and a sort specification, and returns an
interpolated value that would fall into that percentile value with respect to the sort specification |
PERCENTILE_CONT(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
OVER (<query_partition_clause>); |
conn oe/oe
SELECT department_id, PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY salary DESC) MEDIAN_CONT
FROM employees
GROUP BY department_id; |
| |
| PERCENTILE_DISC |
Takes a percentile value and a sort specification and returns an element from the set |
PERCENTILE_DISC(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
OVER (<query_partition_clause>); |
conn oe/oe
SELECT department_id, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY salary DESC) MEDIAN_DISC
FROM employees
GROUP BY department_id; |
| |
| POWER |
| Returns m_value raised to the n_value power |
POWER(<m_value>, <n_value>) |
SELECT 2*2*2 FROM DUAL;
SELECT POWER(2,3) FROM DUAL; |
| |
| RANK |
| Calculates the rank of a value in a group of values |
RANK(<column_name>) WITHIN GROUP |
SELECT RANK(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) SAL_RANK
FROM employees; |
| |
| REGR_ (Linear Regression) Functions |
| REGR_AVGX |
For more information go to http://tahiti.oracle.com
SELECT s.channel_id,
REGR_AVGX(s.quantity_sold, p.prod_list_price) AVGLISTP,
REGR_AVGY(s.quantity_sold, p.prod_list_price) AVGQSOLD,
REGR_COUNT(s.quantity_sold, p.prod_list_price) COUNT,
REGR_INTERCEPT(s.quantity_sold, p.prod_list_price)
INTCPT,
REGR_R2(s.quantity_sold, p.prod_list_price) RSQR,
REGR_SLOPE(s.quantity_sold, p.prod_list_price) SLOPE
FROM sales s, products p
WHERE s.prod_id=p.prod_id
AND p.prod_category='Women'
AND s.time_id=to_DATE('10-OCT-2000')
GROUP BY s.channel_id;
|
| REGR_AVGY |
| REGR_COUNT |
| REGR_INTERCEPT |
| REGR_R2 |
| REGR_SLOPE |
| REGR_SXX |
| REGR_SXY |
| REGR_SYY |
| |
| REMAINDER |
| Returns the modulus of a number (the
remainder from dividing m by n. Same as mod except uses ROUND and doesn't use floor. |
REMAINDER(<m_value>, <n_value>) |
| SELECT REMAINDER(2,3) FROM DUAL; |
| |
| ROUND |
| Returns a value rounded to integer places |
ROUND(<value>, <integer>) |
| SELECT ROUND(3.1415926, 4) FROM DUAL; |
| |
| SIGN |
| Returns the sign of a number |
SIGN(<value>) |
SELECT SIGN(15) FROM DUAL;
SELECT SIGN(0) FROM DUAL;
SELECT SIGN(-5) FROM DUAL; |
| |
| SIN |
| Returns the sine of a number |
SIN(<value>) |
| SELECT SIN(2) SINE FROM DUAL; |
| |
| SINH |
| Returns the hyperbolic sine of a number |
SINH(<value>) |
| SELECT SINH(2) HYPERBOLIC_SINE FROM DUAL; |
| |
| SQRT |
| Returns the square root of a number |
SQRT(<value>) |
| SELECT SQRT(2) FROM DUAL; |
| |
| STATS_ (Statistical) Functions |
|
|
| |
| STATS_MODE |
Returns the value that occurs with the greatest frequency |
STATS_MODE(<expression>) |
conn oe/oe
SELECT department_id, STATS_MODE(salary)
FROM employees
GROUP BY department_id;
SELECT salary, COUNT(*)
FROM employees
WHERE department_id = 50
GROUP BY salary; |
| |
| STDDEV |
Sample standard deviation of an expression |
STDDEV(<expression>) |
conn oe/oe
SELECT STDDEV(salary) AS DEVIATION
FROM employees; |
| |
| SUM |
| Computes the sum of an expression |
SUM(<column_name>) |
SELECT SUM(initial_extent) FROM all_tables;
SELECT SUM( DISTINCT initial_extent) FROM all_tables; |
| |
| TAN |
| Tangent in radians |
TAN(<value>) |
| SELECT TAN(135 * 3.14159265359/180) FROM DUAL; |
| |
| TANH |
| Hyperbolic tangent |
TANH(<value>) RETURN NUMBER; |
|
SELECT TANH(135 * 3.14159265359/180) FROM DUAL; |
| |
| TRUNC |
Truncates a Number or a Datetime to the Specified Number of Decimal Places.
Do not confuse this with the TRUNCATE function. |
TRUNC(<value>,
<decimal_places>) RETURN NUMBER; |
SELECT TRUNC(15.79, 1) FROM DUAL;
SELECT TRUNC(15.79, -1) FROM DUAL; |
| |
| VAR_POP |
| Population Variance of a Set of Numbers |
VAR_POP(<column_name>) RETURN
NUMBER; |
| SELECT VAR_POP(data_length) FROM
all_tab_cols; |
| |
| VAR_SAMP |
| Sample Variance of a Set of Numbers |
VAR_SAMP(<column_name>) RETURN
NUMBER; |
| SELECT VAR_SAMP(data_length) FROM
all_tab_cols; |
| |
| VARIANCE |
| Variance of an Expression |
VARIANCE(<value>) |
| SELECT VARIANCE(initial_extent) FROM user_tables; |
| |
| VSIZE |
| Byte Size |
VSIZE(e IN NUMBER) RETURN NUMBER |
| SELECT VSIZE(initial_extent) FROM all_tables; |
| |
| WIDTH_BUCKET |
Construct Equi-width Histograms
n+1 bucket is for overflow |
WIDTH_BUCKET(<value>, <min_value>, <max_value>, <number_of_buckets>); |
conn oe/oe
SELECT customer_id, cust_last_name, credit_limit,
WIDTH_BUCKET(credit_limit, 100, 4000, 10) CREDIT_GRP
FROM customers
WHERE nls_territory = 'SWITZERLAND'
ORDER BY credit_grp; |
