Oracle function learning notes

1. Oracle single-line function 1.1Oracle character function LOWER makes the string LOWER case; selectLOWER (#39; HeLp #39;) fromdual --- gt; helpUPPER makes the string large

1. Oracle single-line function 1.1 Oracle character function LOWER makes the string LOWER case; select LOWER (#39; HeLp #39;) from dual --- gt; helpUPPER makes the string large

1. Oracle single row Functions

Oracle character functions 1.1

LOWER: lowercase string;
Select LOWER ('help') from dual ---> HeLp

UPPER-case strings;
Select UPPER ('help') from dual ---> HeLp

INITCAP upper case of the first letter of the string, and lower case for the others
Select INITCAP ('help') from dual ---> hELp

LENGTH returns the string LENGTH in the expression.
Select LENGTH ('help') from dual ----> 4

CONCAT concatenates values (for this function, only two parameters can be used)
Select CONCAT ('hello', 'World') from dual ---> HelloWorld

Use SUBSTR to extract strings of determined length
Select SUBSTR ('helloworld', 1, 5) from dual ----> Hello

INSTR searches for the numeric position of a specified character
Select INSTR ('helloworld', 'w') from dual ---> 6

Fill the string with the right alignment of the LPAD
Select LPAD (salary, 10, '*') from dual ---> ***** 24000

Pad the left-aligned string
Select RPAD (salary, 10, '*') from dual ---> 24000 *****

TRIM (leading | trailing | both, trim_character FROM trim_source) removes the header or tail characters FROM the string (or both the header and tail are truncated) (if trim_character or trim_source is a literal value, it must be included in single quotes .)
Select TRIM ('H' FROM 'helloworld') from dual ---> elloWorld

REPLACE (text, search_string, replacement_string) searches for the text expression of a string. If it is found, REPLACE it with the specified replacement string.
Select REPLACE ('helloworld', 'Hello', 'Hi') from dual ---> hiWorld

1.2 numeric Functions

ROUND rounds the value to the specified number of digits.
ROUND (45.926, 2) ----> 45.93

TRUNC truncates the value to the specified decimal place)
TRUNC (45.926, 2) ----> 45.92

MOD returns the remainder of the division operation.
MOD (1600,300) ---> 100

1.3 date functions

In oracle, the date is stored as a number, so you can directly perform operations such as +-*/on the date data.

The number of months between two dates. The result is in the unit of month.
MONTHS_BETWEEN (date1, date2)

ADD_MONTHS add calendar month to date
ADD_MONTHS (date, n) n is the number of months

NEXT_DAY specifies the next month of the date
NEXT_DAY (date, 'Char ') char can be a week, number of months

Last day of month LAST_DAY
LAST_DAY (date)

ROUND rounding date
ROUND (date [, 'fmt'])

Set the date format: alter session set nls_date_format = 'yyyy-mm-dd: hh24: mi: ss'

TRUNC truncation date
TRUNC (date [, 'fmt'])

1.4 conversion functions

TO_CHAR (number | date, '[fmt]', [nlsparams]) converts numeric and date values to varchar2 strings in the format of fmt.
The nlsparams parameter specifies the returned month name, date name, and abbreviation language.

TO_NUMBER (char, '[fmt]', [nlsparams]) converts a string containing numbers to a number in the format specified by fmt.
The nlsparams parameter is the same as TO_CHAR () and is used for digital conversion.

TO_DATE (char, '[fmt]', [nlsparams]) converts a string representing a date to a date value based on the specified fmt. If fmt is omitted, the format is DD-MON-YY.
The nslparams parameter is used in this function for date conversion.

1.5 nested Functions

NVL (expr1, expr2) converts null values to actual values.

NVL2 (expr1, expr2, expr3) returns expr2 if expr1 is not empty. If expr1 is null, expr3 is returned. Expr1 can be any data type.

NULLIF (expr1, expr2) compares two values. If they are equal, a null value is returned. Otherwise, the first expression is returned.

COALESCE (expr1, expr2,..., exprn) returns the first non-null value in the expression.

1.6 conditional expressions

CASE expression (compatible with ANSISQL)
You can simplify conditional query by executing the IF-THEN-ELSE statement.
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

DECODE function (Oracle-specific syntax)
You can simplify conditional query by executing the IF-THEN-ELSE statement.
DECODE (col | expression, search1, result1
[, Search2, result2,...,]
[, Default])


2. Grouping Functions

Rules for using grouping functions:
DISTINCT enables the function to only consider non-repeated values; ALL considers ALL values including repeated values. The default value is ALL, so it does not need
It must be specified.
The data type of a function with the expr parameter can be CHAR, VARCHAR2, NUMBER, or DATE.
All grouping functions ignore null values. Replace the null value with a value. You can use the NVL, NVL2, or COALESCE functions.
When using the group by clause, the Oracle server implicitly sorts the structure set in ascending order. To rewrite the default order, you can
DESC is used in the order by clause.

2.1 AVG ([DISTINCT | ALL] n) n average, ignore null values

2.2 COUNT ({* | [DISTINCT | ALL] expr}) number of rows, in which expr is used to determine non-null values (use * to calculate ALL selected rows, including
Duplicate rows and rows with null values)

2.3 MAX ([DISTINCT | ALL] expr) expr maximum value, ignore Null Value

2.4 MIN ([DISTINCT | ALL] expr) expr minimum value, ignore Null Value
Note: MIN and MAX functions can be used for any data type.

2.5 STDDEV ([DISTINCT | ALL] x) n standard deviation, ignore null values

2.6 SUM ([DISTINCT | ALL] n) n total value, ignore Null Value

2.7 VARIANCE ([DISTINCT | ALL] x) VARIANCE of n, ignoring null values
Example 1:
Select avg (salary), MAX (salary ),
MIN (salary), SUM (salary)
FROM employees
WHERE job_id LIKE '% REP % ';
Example 2:
Select min (hire_date), MAX (hire_date)
FROM employees;
Example 3:
Select count (*)
FROM employees
WHERE department_id = 50;

2.8 group by statements
Syntax: group by group_by_expression ---> specifies columns. The values of these columns are the basis for grouping rows.
Purpose: divide rows in a table into smaller groups BY using the group by clause. You can also use the group by clause for multiple columns.
Example 4:
SELECT department_id dept_id, job_id, SUM (salary)
FROM employees
Group by department_id, job_id;
Note:
A. Any column or expression in the SELECT list that is not an aggregate function must be in the group by clause.
B. You cannot use the WHERE clause to restrict groups;
C. You can use the HAVING clause to restrict groups;
D. Grouping functions cannot be used in the WHERE clause.

2.9 nested grouping Functions
Grouping functions can be nested with two layers.
Example 5:
Select max (AVG (salary ))
FROM employees
Group by department_id;