Oracle Database entry functions/types

Main Oracle Data Types

Overview: The data types supported by various databases are similar to those in standard SQL languages.

Type: varchar2: You can set the maximum length of a field when declaring it. After using it, you do not have to consider the existence of spaces.

If the length of the inserted string is lower than the upper limit, the system automatically reduces it to the true length of the string.

Number (m, n): Can be used to represent both integer and floating point types. However, m cannot exceed 38. If n is 0 or n is omitted, it indicates that it is an integer.

Date: used to store the date and time

Blob: it is usually used in applications, rather than directly using SQL commands in databases.

For example, you can use JDBC to access a database, read and write blob or clob fields, that is, read and write long string information.

Char: it is a fixed-length character type. Oracle databases do not distinguish between characters and strings. They are collectively referred to as character or text

If the inserted string does not reach the specified field length, the system will automatically fill in spaces at the end of the string.

Similarly, the length of the field to be read is always the length of the field to be declared. When comparing string content, we also need to consider space filtering.

Nchar: it is also a string type with a fixed length. It is defined in the SQL language standard and uses Unicode encoding to store characters in different countries or languages.

Varchar: the SQL standard does not guarantee forward and backward compatibility when defining varchar, that is, it may cause incompatibility problems as the language standard is modified.

Therefore, Oracle defines varchar2 similar to varchar. This is to ensure that varchar2 is always supported in Versions later than Oracle.

Oracle is doing this to ensure that this type is forward and backward compatible, so as to import and export data in Oracle series databases.

Long: the difference between it and varchar2 is that it does not support searching string content, that is, it cannot perform conditional queries on its content during query.

The varchar2, char, and nchar types can both be used to directly retrieve string content during query.

Supplement: select * from v $ nls_parameters; database configuration information exists in the form of a data table. It is usually called the data or data dictionary about the data.

In fact, it queries a view in the data dictionary. The NLS_CHARACTERSET corresponds to the current database character set.

The database character set is used by default. In this tutorial, the default ZHS16GBK character set is used during installation, that is, Chinese characters occupy 2 bytes, and English occupies 1 byte.

The NLS_NCHAR_CHARACTERSET corresponds to the secondary character set of the nchar or nvarchar2 type, that is, the AL16UTF16 character set.

In fact, AL16UTF16 is a 16-Bit fixed-length Unicode Character Set. The database character set and such national character set can be modified.

However, the modification of the database character set may cause serious consequences. Unless it is a database administrator, ordinary users do not have to delve into this issue.

When importing and exporting data in the database, if the character set used by the source database and the target database is different, problems may also occur.

Function

Overview: A function can be considered as a set of code that can complete relatively independent functions. Oracle functions are equivalent to methods or processes in other languages.

Oracle functions can be divided into single-row functions and multi-row functions. Oracle functions return values.

The single-row function works for each row in the query result and returns a result.

A multiline function, also called a grouping function, returns a result for a group of query records or multiple rows.

Single Row: operation data item. It accepts parameters and returns processing results. It takes effect for each returned row. The data type can be modified and can be nested.

Single-row functions include character functions, numeric functions, date functions, conversion functions, and general functions.

Multiple rows: Also known as grouping functions, that is, operations on a group of data. Only one result can be returned for a group of data (multiple rows of records ).

Multiline functions include avg (), count (), max (), min (), sum (), etc.

For example, select avg (sal), max (sal), min (sal), sum (sal), max (hiredate), min (hiredate) from emp;

Continued 1: multiple functions can be implemented using a single row function in Oracle system functions, such as data calculation and data output format control.

Set and change the date display format, convert data types, use functions such as NVL to process null values, and implement IF-THEN-ELSE multi-path branch logic.

Continued 2: the conversion function does not change the field type and value of the table data. It is equivalent to copying a copy of the data. The converted data is the copied data.

Implicit and explicit conversions are supported for data type conversion. Explicit conversions are recommended to ensure the reliability of SQL statements.

Continued 3: common functions are applicable to any type of data including null values. It is usually used to process null values, filter NULL values or set default values.

Common functions include nvl (), nvl2 (), nullif (), coalesce (), case expression, and decode ().

Nesting: A single-row function can be nested for unlimited nesting layers. Grouping functions can be nested at most two layers. The execution sequence of nested functions is from inner to outer.

Single Row such as select empno, lpad (initcap (trim (ename), 10, '') Name, job, sal from emp;

Multiple rows, such as select max (avg (sal) from emp group by deptno; -- in fact, it makes no sense to use the grouping function here.

Note: functions provided at the database level are generally used for simple data processing, or can only implement extremely common functions.

So we should not, or do not expect to implement extremely complex business logic at the database query level.

If the logic of the application is mixed with the database, it will not facilitate code maintenance and update.

It is not conducive to database management, including data migration, Database Import and Export, etc.

Date type

Summary: In computer operating systems or in various advanced programming languages, dates are usually stored as long integers, usually recorded in milliseconds.

Oracle stores date and time information in digital format: century, year, month, day, hour, minute, second

The default date format is DD-MON-YY. You can use the sysdata function to obtain the current system date and time.

Calculation: The date type data can be directly added or subtracted by a number, and the result is that the date. The agreed value indicates the number of days of addition and subtraction.

The two date data types can be subtracted, and the result is how many days the two data types differ. No, because the addition of dates is meaningless.

NVL () function

Overview: it is used to replace null with the specified default value, applicable to character, number, date, and other types of data

Format: NVL (exp1, exp2) -- if the expression exp1 value is null, The exp2 value is returned; otherwise, the exp1 value is returned.

Example: select empno, ename, sal, comm, sal + nvl (comm, 0) from emp;

Select empno, ename, job, nvl (job, 'no job yun') from emp;

NVL2 () function

Overview: it is used to implement the conditional expression function.

Format: NVL2 (exp1, exp2, exp3) -- if the expression exp1 value is not null, The exp2 value is returned; otherwise, the exp3 value is returned.

Example: select empno, ename, sal, comm, nvl2 (comm, sal + comm, sal) Total income from emp;

NULLIF () function

Overview: it is used for data equivalence comparison and returns null or a value to be compared based on the comparison result. In actual development, there are not many applications

Format: nullif (exp1, exp2) -- if the expression exp1 is equal to the value of exp2, null is returned; otherwise, the value of exp1 is returned.

Example: select name, formerly known as nullif (pen_name, name), alias from author;

COALESCE () function

Overview: it is used to implement the "join" function of data.

Format: coalesec (exp1, exp2,...) -- evaluate each parameter expression in sequence. If a non-null value is returned, it is stopped.

If the expression is null, return null. Generally, the last expression ensures that the field is not null.

Example: select empno, ename, sal, comm, coalesec (sal + comm, sal, 0) total revenue from emp;

CASE expression

Overview: it is used to implement multi-branch Structures

Format: case exp when comparison_exp1 then return_exp1

[When comparison_exp2 then return_exp2

When comparison_expn then return_expn

Else else_exp]

End

Example: select empno, ename, sal,

Case deptno when 10 then 'finance author'

When 20 then 'r & D amount'

When 30 then 'sales directory'

Else 'unknown departments'

End Department

From emp;

Note: Each expression in CASE (such as deptno, 10, Finance Department, etc.) can be composite.

This alignment is written to increase readability. Of course, you can also write the code on the same line.

The entire range between case and end is equivalent to a field in a common query, and the "department" behind the end is the alias.

DEDODE () function

Overview: similar to the case expression, it is also used to implement multi-path branch structures.

Format: decode (col | expression, search1, result1

[, Search2, result2,...,]

[, Default])

Example: select empno, ename, sal,

Decode (deptno, 10, 'Finance author ',

20. 'r & D Center ',

30, 'sales directory ',

'Unknown departments ')

Department

From emp;

COUNT () function

Format: count (*) returns the total number of records in the group.

Count (exp) returns records with non-null exp values.

Count (distinct (exp) returns the number of records with non-repeated and non-empty expression exp values.

Example: select count (*) from emp; -- by default, the entire table is a group

Select count (comm) from emp; -- return the number of records (rows) with the comm field not null in the emp table.

Select count (distinct (deptno) from emp; -- query the number of records with the deptno value being non-empty and non-repeated

Grouping functions and null values

Overview: grouping functions omit null values in columns. You can use the NVL () function to force grouping functions to process null values.

Example: select avg (comm) from emp; -- equivalent to sum (comm)/count (comm)

Select sum (comm) from emp; -- calculates the sum of the comm values of non-null values in the table.

Select avg (nvl (comm, 0) from emp; -- equivalent to avg (nvl (comm, 0)/count (*)

Group by clause

Overview: it is used to divide data in a table into several groups.

Format: select column, group_function (column)

From table

[Where condition]

[Group by group_by_expression]

[Order by column];

Example: select deptno, avg (sal) from emp group by deptno;

Note: This field appears in the SELECT list. If it is not included in the GROUP function, it must appear in the group by clause at the same time.

Fields contained in the group BY clause do not have to appear in the SELECT list. The clause execution order is where → group by → order by default.

Supplement: select deptno, job, avg (sal) from emp group by deptno, job order by deptno desc; -- grouping Based on Multiple Fields

Select deptno, avg (sal) from emp; -- invalid

Note: without the group by clause, fields (single-row functions) and grouping functions cannot be mixed in the SELECT list.

Grouping functions are not allowed in WHERE, such as select deptno, avg (sal) from emp where avg (sal)> 2000 group by deptno; -- invalid

This is related to the order in which the sub-statement is executed. The where clause is executed first. The group by clause has not been executed before the where clause is executed.

So the program does not know that this is in the group, nor has it ever calculated the average salary of the avg (sal) group, so the group function is not allowed in the where clause.

Since the group by clause has not been executed, you are not sure how to group and how many groups are divided.

Therefore, the where clause can only be used for primary filtering. In this case, you can use the HAVING clause to filter the average wage.

HAVING clause

Overview: it is used to filter groups.

Format: select column, group_function (column)

From table

[Where condition]

[Group by group_by_expression]

[Having group_condition]

[Order by column];

Example: select deptno, job, avg (sal)

From emp

Where hiredate> = to_date ('2017-05-01 ', 'yyyy-mm-dd ')

Group by deptno, job

Having avg (sal) & gt; 1200

Order by deptno, job;