Oracle Database (quad)-Single-line function

Single-line function: For each row of data to be calculated after a row of output results,

SQL single-line functions are divided into character functions, numeric functions, date functions, conversion functions, and other general functions based on data types. such as the NVL function

• Manipulating Data Objects
• Accept parameter Returns a result
• Transform only one row
• Each row returns a result
• Data types can be converted
• can be nested
• The parameter can be a column or a value

1, character function casing control function: Lower,upper,initcap

Character control functions: Concat,substr,length,instr,lpad | Rpad,trim,replace

Concat: Connection strings, substr: delimited strings, Length: string lengths

InStr: The position of the first occurrence of a letter in a string

Lpad: Left

Rpad: Right End

Trim: Removes the corresponding character at the end of the string, preserving the middle

Replace: replacing all

2. Numeric functions

Round: Rounding

TRUNC: Truncate

MoD: Seek redundancy

3. Date function

• Add or subtract a number on a date the result is that the date
• Two date subtraction returns the number of days between dates that do not allow addition operations, meaningless
• You can add or subtract a number of days to a date with a number except 24来

Add and subtract numbers on a date

Date Subtraction

Months_between: Two days difference between dates

Add_months: Add several months to the specified day

Next_day: The next week of the specified date * corresponds to the date

Last_day: Last day of the month

Hire_date is the company's employees, which is the next day of the month?

ROUND: Date Rounding

Tranc: Date Truncation

4. Conversion function

Data type conversions: implicit transformations and explicit transformations

Implicit conversions

Dominant transformations

Date converted to string

string conversion to date

To use double quotes when inserting characters into the conversion

Numeric to string (currency symbol: $ USD, L local currency symbol)

String to Number

5. General functions

NVL (EXP1,EXP2): If EXP1 is not empty, use EXP1 itself, if EXP1 is empty, use EXP2

1. Data types that can be used are date, character, number

2. General form of function:

• NVL (commission_pct,0)
• NVL (hire_date, ' 01-jan-97 ')
• NVL (job_id, ' No job yet ')

----------Salary rate of employees (including commission_pct)

--Output last_name,department_id, when department_id is null, displays ' no department '.

NVL2 (EXP1,EXP2,EXP3): If Exp1 is empty, return EXP2, otherwise, return EXP3

--Query the employee's bonus rate, if empty, return 0.01, if not empty, return the actual bonus rate +0.015

Nullif (EXP1,EXP2): EXP1 equals EXP2 returns NULL, unequal return EXP1

Compares the length of the employee's last name and name, returns the same as NULL, and returns the length of a different name

COALESCE (not used)

• The advantage of coalesce compared to NVL is that coalesce can handle alternating multiple values at the same time.
• If the first expression is empty, the next expression is returned, and the other arguments are coalesce.

6. Conditional expressions

Using if-then-else logic in SQL statements

Use two methods

• Case expression
• Decode function

--Query The department Number 10, 20, 30 employee information, if the department number is 10, print their wages 1.1 times times, 20th department, print their wages 1.2 times times, 30th department printing their wages 1.3 times times the number of

When you need to use if-then-else logic:

1. Case expression

Case expr when Comparison_expr1 then return_expr1

[ when Comparison_expr2 then RETURN_EXPR2

　　 when comparison_exprn then RETURN_EXPRN

　　 ELSE else_expr]

END

2. Decode function

DECODE (Col|expression, Search1, RESULT1,

[, SEARCH2, RESULT2,...,]

[, default])

7. Summary

• Using functions to calculate data
• modifying Data using Functions
• Using functions to control the output format of a set of data
• Use functions to change the display format of dates
• Using functions to change data types
• Using the NVL function
• Using If-then-else logic

Oracle Database (quad)-Single-line function