Oracle 2-single-line function theory and case analysis

Oracle 2-single-line function theory and case analysis
SQL has two types of functions: single-row and multi-row functions.
1. Single Row Functions
Single-row functions: operate on Data Objects, accept a parameter to return a result, transform only one row, return a result for each row, convert data type, can be nested, the parameter can be a column or a value

DUAL is a 'pseudo table' that can be used to test functions and expressions.
2. Character Functions

Case-sensitive control functions: these types of functions change the case sensitivity of characters.

Example: [SQL] select lower (ename) from emp; LOWER (ENAM
----------
Smith
Allen
Ward select upper (ename) from emp; UPPER (ENAM
----------
SMITH
ALLEN
WARD select initcap (ename) from emp; INITCAP (EN
----------
Smith
Allen
Ward
Character control functions:

Select concat ('hello', 'word') from dual; CONCAT ('H --------- helloword select substr ('helloword', 1, 3) from dual; SUB --- El select length ('helloword') from dual; LENGTH ('helloword') --------------- 9 select instr ('helloworld', 'w') from dual; INSTR ('helloworld', 'w') --------------------- 6 select lpad ('hello', 10, '*') from dual; LPAD ('hell -------- ***** hello select rpad ('hello', 10, '#') from dual; RPAD ('hell ---------- hello ##### select trim ('hello') from dual; TRIM (----- hello

3. Numeric Functions
ROUND: Rounding
TRUNC: Truncation
MOD: Remainder
Round Function: The syntax is ROUND (number, num_digits). Number indicates the number to be rounded up. Num_digits indicates the specified Number. If num_digits is greater than 0, the number is rounded to the specified decimal place, and the value of Num_digits is rounded to the corresponding decimal place. If num_digits is equal to 0, the number is rounded to the nearest integer. If num_digits is less than 0, the number of digits is rounded to the left of the decimal point. The value of Num_digits is rounded to the corresponding position of the integer on the left of the decimal point.
For example:
ROUND (2.149, 0) rounds 2.149 to an integer of 2.
ROUND (2.15, 1) rounds 2.15 To a decimal place and returns 2.2.
ROUND (2.149, 1) rounds 2.149 to a decimal place and returns 2.1.
ROUND (-1.475, 2) rounds-1.475 to two decimal places and returns-1.48 ).
ROUND (21.5,-1) rounds 21.5 to the left of the decimal point and returns 20.
Example:
SQL> select round (45.926, 2) from dual; ROUND (45.926, 2) ------------- 45.93 SQL> select round (45.926,-2) from dual; ROUND (45.926,-2) ---------------- 0 SQL> select round (55.926,-2) from dual; ROUND (55.926,-2) -------------- 100 SQL> select round (50.926,-2) from dual; ROUND (50.926,-2) -------------- 100 SQL> select round (150.926,-2) from dual; ROUND (150.926,-2) --------------- 200 SQL> select round (50.326, -2) from dual; ROUND (50.326,-2) -------------- 100 SQL> select round (550.326,-2) from dual; ROUND (550.326,-2) ----------------- 600 SQL> select trunc (45.926, 2) from dual; TRUNC (45.926, 2) --------------- 45.92 SQL> select trunc (45.926,-2) from dual; TRUNC (45.926, -2) ---------------- 0 SQL> select trunc (55.926,-2) from dual; TRUNC (55.926,-2) -------------- 0 SQL> select trunc (155.926,-2) from dual; TRUNC (155.926,-2) ----------------- 100 SQL> select mod (1600,300) from dual; MOD (1600,300) ------------- 100 SQL> select mod () from dual; MOD) ---------- 1 SQL> select round (45.926,-1) from dual; ROUND (45.926,-1) -------------- 50

4. Date
The date data in Oracle actually contains two values: Date and time.
The default date format is DD-MON-RR. Function SYSDATE returns: date, time
Add or subtract a number on the date and the result is still the date. Returns the number of days between two dates. You can add or subtract hours to the day period by dividing the number by 24.
Date Functions

Note: The date conversion format does not support converting dates in Chinese format.
Example:
SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual; TO_CHAR (SY ---------- 2013-04-04 SQL> select to_char (sysdate, 'yyyy/mm/dd ') from dual; TO_CHAR (SY ---------- 2013/04/04 select to_char (sysdate, 'Year-MONTH-day') from dual; TO_CHAR (SYSDATE, 'Year-MONTH-day ') --------------------------------------------------------- TWENTY thirteen-select to_date ('2017-12-12 ', 'yyyy/mm/dd') from dual; TO_DATE ('2017--------------- 12-12-12 SQL> select to_date ('2017-12-12 ', 'yyyy-mm-dd') from dual; TO_DATE ('1970--------------- 12-12-12 SQL> select to_char (sysdate, 'dd month year') from dual; TO_CHAR (SYSDATE, 'ddmonthyear ') -------------------------------------------------- April twenty thirteen SQL> select to_char (sysdate, 'dd month yyyy ') from dual; TO_CHAR (SYSDAT -------------- 04 August 13, April 20

5. conversion functions

Implicit data type conversion: Oracle automatically performs the following conversions:



TO_CHAR function for date conversion
Format: Must be included in single quotes and case sensitive. It can contain any valid date format. Dates are separated by commas.





Example:
Select ename, to_char (sal, '$999,999.00') from emp; ENAME TO_CHAR (SAL, ---------- SMITH $800.00 ALLEN $1,600.00 WARD $1,250.00 select ename, to_char (sal, 'l999, 100') from emp; ENAME TO_CHAR (SAL, 'l999, 999.00 ---------- --------------------- SMITH ￥999 ALLEN ￥800.00 WARD ￥1,600.00
6. General Functions
These functions apply to any data type and null values:
NVL (expr1, expr2): converts a null value to a known value. Available data types include date, character, and number.
The general form of the function:
NVL (commission_pct, 0)
NVL (hire_date, '01-JAN-97 ')
NVL (job_id, 'no Job yun ')
NVL2 (expr1, expr2, expr3): If expr1 is not NULL, expr2 is returned. If it is NULL, expr3 is returned. Equivalent to the three-object operator in java
NULLIF (expr1, expr2): returns NULL for equality, returns expr1 for Equality
COALESCE (expr1, expr2,..., exprn): Compared with NVL, COALESCE can process multiple alternate values at the same time. If the first expression is null, return the next expression and perform COALESCE on other parameters.
Example: SQL> select ename, nvl (comm, 0) from emp; ENAME NVL (COMM, 0)
---------------------
SMITH 0 ALLEN 300 WARD 500 Error syntax, condition comm and 0 location chaos SQL> select ename, nvl2 (comm, 0, comm) from emp; ENAME NVL2 (COMM, 0, COMM)
---------------------------
Smith allen 0 WARD 0 JONES: SQL> select ename, nvl2 (comm, comm, 0) from emp; ENAME NVL2 (COMM, COMM, 0) ---------- ----------------- SMITH 0 ALLEN 300 WARD 500 SQL> select nullif () from dual; NULLIF)
-----------
SQL> select nullif (2, 1) from dual; NULLIF (2, 1) ----------- 2
SQL> select nullif (1, 2) from dual; NULLIF (1, 2) ----------- 1 SQL> select ename, 2 coalesce (sal, comm) from emp;
 
Ename coalesce (SAL, COMM) ---------- ---------------- SMITH 800 ALLEN 1600 WARD 1250
 
COALESCE (COMM, SAL) ------------------ 800 300 500 2975 7 conditional expressions
Use the IF-THEN-ELSE logic in an SQL statement. use either of the following methods:
CASE expression
DECODE Function
Example: SQL> select ename, job, sal, case 2 job when 'salesman' then sal * 1.2 3 when 'manager' then sal * 1.8 4 else sal end "revised_sal" from emp; ename job sal revised_sal ---------- --------- ----------- smith clerk 800 800 allen salesman 1600 1920 ward salesman 1250 1500 2975 jones manager 5355 SQL> select ename, job, sal, decode (2 job, 'salesman', 1.2 * sal, 3 'manager', 1.8 * sal, sal) 4 revised_sal from emp; ename job sal REVISED_SAL
-----------------------------------
Smith clerk 800 800 allen salesman 1600 1920 ward salesman 1250 1500
8. nested Functions
Single-row functions can be nested. The execution sequence of nested functions is from inner to outer.
Example:
SQL> select ename, job, nvl2 (to_char (job), 'manager', 'no manager') from emp; ename job NVL2 (TO_CH ---------- --------- ---------- smith clerk manager allen salesman manager ward salesman manager