Examples of common Oracle database functions

Examples of common Oracle database functions

Basic Introduction and practice of Oracle databases

1. Start (p1 ~ P2)
2. SQL statement compiling ideas (p3)
3. Introduction to common Oracle operators (p4 ~ P5)
4. DML statements (p6 ~ P11)
5. Introduction to common Oracle functions (p12 ~ P30)
6. SQL statement functions (p31 ~ P34)
7. Obtain SQL statements through logs (p35 ~ P49)
8. Use case System to submit the problem (p50 ~ P51)
9. Learning Resources and Q & A (p52)
10. End (p53)

 

1. SQL statement compiling ideas
1. First, determine the columns of the final output result, including:
1) these columns are from one or more tables, multi-table queries (equivalent, non-equivalent, left join, right join, external join, and self-join) may be used for multiple tables)
2) determine whether to obtain the column value through direct query or by performing (function, grouping, operation, and column subquery) operations. If the column needs an alias
2. Determine whether the values and query conditions of the output columns come from multiple tables. If there are multiple tables, use multi-table queries.
3. Determine whether the value of the output column can be obtained through direct query or through subquery. If you want to use subquery, add where
4. determine whether to use group by based on the results and conditions of the output column ), for example, words such as classification, statistics, grouping, maximum, minimum, average, and each require group
5. Whether to filter the grouped results. having is required.
6. Whether to sort the output results. order by is required.

2. Introduction to common Oracle Operators
1. Use | connectable characters
2. Use distinct to remove duplicate rows
3. operators:
1) Arithmetic (+ -*/)
2) comparison (>>=<=! = <>) Any (value 1, value 2 ...) all (value 1, value 2 ...) it cannot be used independently. It must be used together with >=,<,<=
3) logic (and or not)

4. SQL operators:
1) in (value 1, value n)
2) like 'pattern string': _ represents any character, and % Represents 0 to n characters
3) between value 1 and value 2: it can be used not only for values, but also for date and time.
4) is null, is not null: matches null values, non-null values. = Or! cannot be used for null values! =
5) union (union set, duplicate record rows show only one row), union all (union set), intersect (intersection), minus (difference set, returns the records after the difference set in the left table)
5. pseudo columns:
Rowid: each row in each table has a unique identifier in Oracle.
Rownum: Oracle has a row number for each row of records in each query result set. rownum can only be used <OR =

3. DML statements
1. Query (select)
Select distinct * | column as Alias | expression | function | column operation | subquery
From table 1 alias 1, table n alias n | subquery
Where condition 1 (>=<=<>) condition 2 | range (between condition 1 and condition 2)
In (subquery)
Like (condition _ %)
Combination (or | and | not)
Subquery (select statement)
Group by Column
Normal Value of the having grouping function (max, count, sum, etc.) operator | subquery
Order by COLUMN alias or number

2. insert)
Insert into Table Name (column name 1,...) values (value 1 ,..)
-- Add information
Insert into check
(PRIMARY_ID,
NAME,
DISTRICT,
UPDATED_DATE)
Values
(Sys_guid (), sysdate );

-- If only the table structure is copied and a condition 1 = 2 that is never possible, only the table structure is copied, but the table content is no longer displayed.
Create table username. Target table name as select * from username. source table name where 1 = 2;

-- Completely copy a table (including creating a table and copying records in the table)
Create table username. Target table name as select * from username. source table name;

-- Insert data from multiple tables into one table
Insert into username. Target table name (Field 1, field n) (select Field 1, field n)
From user name. source table name union all select Field 1, field n from table;

3. Modify (you need to add a commit; keyword to submit)
Update table name set column name = column change value (where condition expression );
Update check_store cs set cs. DISTRICT = '000000' where cs. NAME = 'packet ';
Commit;

4. Delete (you need to add commit; keyword to submit)
Delete from Table Name (where condition expression );
Delete from check_operate co where co. NAME = 'package ';
Commit;

4. Introduction to common Oracle Functions
1. Date character Functions
1) to_char
-- 2016-11-03
Select to_char (sysdate, 'yyyy-mm-dd') from dual;
2) to_date
Select to_date (to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), 'yyyy-mm-dd hh24: mi: ss') from dual;
3) to_number
To_number (varchar2 or char, 'format model ')
9: represents a number
0: Force 0 to display
$: Show dollar signs
L: force display of a local currency symbol
.: Displays a decimal point
,: Displays a thousands separator number

-- 1234.678
Select to_number ('$1234.678', '$9999.999') from dual;
-- 15
Select to_number ('F', 'x') from dual;
2. Aggregate functions
1) maximum function: max ([distinct | all] x), minimum function: min ([distinct | all] x)
It can act on data of numeric type, string type, and date type. max (null), min (null) returns null.
The maximum value of the string type data is from A ~ Z is ordered. The greater the value, the closer the result is.
For Chinese characters, they are arranged in full pinyin. If the first character is the same, the next character is compared, and so on.
For data of the date and time type, you can also obtain the Maximum/minimum values. The size arrangement is the morning and evening of the date and time. The earlier the data is considered to be smaller.

2) sum ([distinct | all] x), count (* | [distinct | all] x), and calculate the average function: avg ([distinct | all] x)
Count (*): returns the total number of all existing rows (including null rows) in the table)
Count (column name): returns the total number of all rows except null in the table.
Count (distinct + column name): Same as count (column name)
3. Special Functions
1) unique string function: sys_guid ()
Generate a 32-bit string consisting of uppercase letters and numbers
Select sys_guid () from dual;
Result: 4061D1372CEA31E4E05318E01F0A5902

If the first parameter is left blank, it will be filled with spaces.
2) left Fill function: lpad (string, padded_length, [pad_string])
Select lpad (1, 6, 'T') from dual; -- ttttt1
Select lpad (1234567, 6, 'T') from dual; -- 123456
3) Right filling function: lpad (string, padded_length, [pad_string])
Select rpad (1, 6, 'T') from dual; -- 1 ttttt
Select rpad ('abcdef', 6, 'T') from dual; -- abcdef
4) random packet Function

1) returns 0 ~ A 38-bit precision random number function (including 0.0, excluding 1.0) in an interval: dbms_random.value
-- 0.474199333601534
Select dbms_random.value from dual;
2) returns ~ Random Number function between B (including a = 1, excluding B = 10): dbms_random.value (1, 10 ):
-- 4.63158069726374
Select dbms_random.value (1, 10) from dual;
3) function for generating random numbers with a normal distribution: dbms_random.normal

/*
Note: The normal function returns a group of numbers from the normal distribution. The standard deviation of this normal distribution is 1, and the expected value is 0.
The value returned by this function is 68% between-1 and + 1, 95% between-2 and + 2, and 99% between-3 and + 3.
*/
--- 0.241547252937121
Select dbms_random.normal from dual;
4) function for returning a string of the specified length: dbms_random.string (opt char, len NUMBER)

/*
'U' or 'U'-> return uppercase letters
'L' or 'l'-> return lowercase letters
'A' or 'A'-> uppercase/lowercase letters
'X' or 'X'-> mix uppercase letters and numbers
'P' or 'P'-> Any character that can be displayed
*/
-- LSNB
Select dbms_random.string ('U', 4) from dual;
5) function for returning a random number: dbms_random.random

-- Range:-power () <= random <power)
-- 35725665
Select dbms_random.random from dual;
6) returns the absolute value function: abs
-- 1949
Select abs (-1, 1949) from dual;
7) return the smallest integer greater than or equal to the given number: ceil
-- 7
Select ceil (1, 6.5) from dual;
8) return the largest integer less than or equal to the given number: floor
-- 6
Select floor (6.5) from dual;

 

9) returns the number that retains the specified number of digits: round (number, digits)
/*
Number of digits to be rounded to. digits is the number of digits to be retained after the decimal point.
If digits is greater than 0 or not entered, it is rounded to the specified decimal place.
If digits is equal to 0, it is rounded to the nearest integer.
If digits is less than 0, it is rounded to the left of the decimal point.
*/
-- 3.687
Select round (3.6873, 3) from dual;

10) nvl (expr1, expr2)
If expr1 is null, expr2 is returned; otherwise, expr1 is returned.
The expr1 and expr2 parameters can be of any data type, but they should be the same.
If the two data types are inconsistent, the Oracle database implicitly converts one of the data types to make them consistent with the other. If the conversion fails, an error is returned.
-- Null
Select nvl (null, 'null') from dual;
-- 1
Select nvl ('1', 'null') from dual;

11) nvl2 (expr1, expr2, expr3)
If expr1 is not a null value, expr2 is returned. Otherwise, expr3 is returned.
The value of any data type can be returned, but expr2 and expr3 cannot be Data Types of the LONG type.
-- Null
Select nvl2 (null, 'non-null', 'null') from dual;
-- Not empty
Select nvl2 ('1', 'non-null', 'null') from dual;

12) nullif (expr1, expr2)
Expr1, expr2
Any combination of constants, column names, functions, subqueries, Arithmetic Operators, bitwise operators, and string operators cannot be null.
The return type is the same as the first expr1.
If the two expressions are equal, null is returned.
If the two expressions are not equal, the value of expre1 is returned.
--
Select nullif (1, 1) from dual;
-- 1
Select nullif ('1', '') from dual;
-- 1
Select nullif ('1', '') from dual;

13) function for returning the string position: instr (source, search, start_position, nth_appearance)
Source: source string
Search: string to be searched
Start_position: Specifies the position in the source string to start searching for the string. This parameter can be omitted. The default value is 1. It is a positive integer, which is left-to-right. The negative integer is right-to-left.
Nth_appearance: specifies the number of times the string is searched. Do not enter this parameter. The default value is 1 and cannot be negative.
You can also find a single character
-- 8
Select instr ('2017-11-03 16:24:20 ','-', 1, 2) from dual;

14) return the truncated string: substr (strings | express, start, [length])
Strings | express: Truncated string or string expression
Start: Where to start intercepting, positive (starting from the specified position of the string), negative (starting from the specified position at the end of the string), 0 (starting from the first position in the string)
Length: Optional. specifies the length of the string to be truncated. If the length is short, all characters before the end of the character expression value are returned.
-- 01
Select substr ('2017-11-03 ', 2016) from dual;
-- 03
Select substr ('2017-11-03 ',-2016) from dual;
-- 16-11-03
Select substr ('2017-11-03 ', 3) from dual;

15) value conversion function: decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)
The condition can be a field or expression.
When the condition is equal to the value 1, the function translation value 1, and the condition is equal to the value 2, the function translation value 2, are inconsistent, return the default value
-- 9 is greater than 3
Select decode (sign (9-3), 1, '9 is greater than 3',-1, '9 is less than 3', 0, '9 is equal to 3', 'unknown ') from dual;
16) Positive and negative number judgment function: sign (variable 1-variable 2)
Returns 0, 1, and-1 based on the values of (variable 1-variable 2) being 0, positive, or negative.

17) translate (Str, fromStr, toStr)
1. Replace the string in Str and return the result. The fromStr and toStr values correspond one to one. If they do not match one to one, the return value is considered null.
2. If the fromStr string is longer than the toStr string, the extra characters in the fromStr string are deleted.
3. If any parameter in translate is null, the result is also null.
-- 1 9XXX999 converts numbers to 9, other uppercase letters to X, and then returns
Select translate ('2krw229 ',
'0123456789abcdefghijklmnopqrstuvwxy ',
'9999999999xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') result
From dual;

-- 2 2229 keep the number and remove other uppercase letters
Select translate ('2krw229 ',
'0123456789abcdefghijklmnopqrstuvwxy ',
'123') as result
From dual;
-- 3 I am a Ch user. I love to process Ch by character rather than byte. If toStr has more characters than fromStr, the extra characters will be deleted.
Select translate ('I am Chinese, I Love China', 'China') "result"
From dual;

-- 4 I m Chinese ese, I love China if the fromStr Character Count is greater than toStr, the extra characters will be deleted
Select translate ('I am Chinese, I love China', 'China', 'China') as "result"
From dual;
-- 5 if the parameter is null or a null string, the return value is null.
Select translate ('2krw229', '0123456789abcdefghijklmnopqrstuvwxyz ', null) "result"
From dual;
-- 6 ** users only display the last word of the account name, and the remaining words are replaced by asterisks.
Select translate ('China ',
Substr ('China', 1, length ('China')-1 ),
Rpad ('*', length ('China'), '*') as "result"
From dual;

18) sorting function:
1. row_number () over ([partition by colum] order by colum)
Returns a unique sorting value for each row in an ordered group. The sequence number is specified by the order by clause. It starts from 1 and is different even if it has equal values.
Partition by colum is distinguished by column values, and each group is sorted internally.
2. dense_rank () over ([partition by colum] order by colum)
Calculate the position of a value in a group, starting with 1, with equal value rows with the same qualifying position and consecutive ranking.
3. rank () over ([partition by colum] order by colum)
Calculate the position of a value in a group, starting with 1, with equal value rows with the same ranking. The ordinal number then jumps the corresponding value.

5. SQL statement Functions

Group sorting example
-- Count by date (role 1) the number of users
Select trunc (cr. created_date, 'dd '),
Count (case
When cr. role_id = 'rolea' then
1
Else
Null
End) as role 1
From check_role cr
Group by trunc (cr. created_date, 'dd ')
Order by trunc (cr. created_date, 'dd') desc;

Use a pseudo column as a string operation example
-- Returns the latest version number.
Select flowId
From (select row_number () over (partition by cr. key _ order by cr. version _ desc) rnum,
Substr (cr. key _,
Instr (cr. key _, '_', 1, 2) + 1,
Length (cr. key _) cityId,
Cr. ID _ flowId,
Cr. version _
From check_record cr
Where cr. key _ like 'check _ test % ')
Where rnum = 1;

Decode function example
-- My message table
Select decode (tn. type, 1, 'Type 1', 2, 'type 2', 3, 'Type 3', 4, 'Type 4') as "product type", tn. user_id as "User ID", count (*) as "Message count"
From test_news tn
Where tn. user_id is not null
And tn. created_date> =
To_date ('2017-11-01 09:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
Group by decode (tn. type, 1, 'Type 1', 2, 'type 2', 3, 'Type 3', 4, 'Type 4'), tn. user_id
Order by decode (tn. type, 1, 'Type 1', 2, 'type 2', 3, 'Type 3', 4, 'Type 4') desc, tn. user_id desc, count (*) desc