Oracle Database entry function/type

Oracle Primary Data types

Overview: The data types supported by various databases are similar, and may differ slightly from the data types in standard SQL languages

Type: VARCHAR2: You can set its length limit when declaring a field. And after using it, you don't have to think about the existence of spaces.

If you insert a string that is less than the length of the upper limit, the system automatically shrinks it to the true length of the string

Number (M,n): can be used to represent an integral type or a floating-point type. But M can not exceed 38. If n is 0 or omits n, it means that it is an integer

Date: For storing dates and times

BLOB: Typically used in an application, rather than using SQL instructions directly in the database

For example, access to the database through JDBC technology, read and write blob or clob type of field, that is, read-write long string information, etc.

Char: It is a fixed-length character type. The Oracle database does not differentiate between characters and strings, which are collectively referred to as character or literal

The so-called fixed-length character is the insertion of a string, if not reached the agreed field length, the system will automatically fill the tail of the string space

Similarly, the length of the field at the time of the read is always the length of the field at the time of declaration. And when you compare string content, you also need to take into account the filtering of spaces

NCHAR: It is also a fixed-length string type, which is defined in the SQL language standard and typically uses Unicode encoding to hold characters in different countries or languages

The VARCHAR:SQL standard does not guarantee forward and backward compatibility when defining varchar, that is, it is possible to create incompatibilities with changes in language standards

So Oracle defines a VARCHAR2 type similar to the varchar type. is to support the VARCHAR2 type forever in future versions of Oracle.

Oracle is doing this to ensure that this type is compatible for the purpose of importing and exporting data in an Oracle family database

Long: It differs from the VARCHAR2 in that it does not support retrieving the contents of a string, that is, the query cannot be conditionally queried for its contents

VARCHAR2 and char, nchar type, and so on, can retrieve the contents of a string directly in the query.

Add: SELECT * from v$nls_parameters; the configuration information of a database exists as a datasheet, usually referred to as a data or data dictionary of data

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

The default is to use the database character set. The tutorial uses the default ZHS16GBK character set when installed, that is, Chinese characters account for 2 bytes and English is 1 bytes

The nls_nchar_characterset corresponds to the auxiliary character set used by the NCHAR or nvarchar2 type, that is, the AL16UTF16 character set

In fact, AL16UTF16 is a 16-bit fixed-length Unicode-encoded character set. The database character set and the national character set can be modified

However, the database character set may face serious consequences, unless it is a database administrator, and ordinary users do not need to delve into this aspect

When the data in the database is imported and exported, it can be problematic if the source database and the target database use different character sets.

Function

Overview: Functions can be thought of as a collection of code that can accomplish relatively independent functions. Oracle functions are equivalent to methods or procedures in other languages

Oracle functions can be divided into single line functions and multi-line functions in two broad categories. All Oracle functions have return values.

The so-called Single-line function works for each row in the query result, and returns a result

A multiline function, which is called a grouping function, is a record of a set of queries, or multiple lines, that returns a result

Single line: Manipulate data items, accept parameters and return processing results, work on each returned row, modify the data type, can be nested using

Single-line functions are divided into character functions, numerical functions, date functions, conversion functions, general functions

Multiline: Also called a grouping function that operates on a set of data that can only return one result for a set of data (multiple-line records)

Multiline functions include AVG (), COUNT (), Max (), Min (), sum (), and so on.

For example, select AVG (SAL), Max (Sal), Min (sal), sum (SAL), Max (HireDate), Min (hiredate) from EMP;

Continuation: The use of one-line functions in Oracle's system functions can achieve many functions, such as the calculation of data, control the output format of the data

Set up and change the display format of the date, do data type conversion, use functions such as NVL to deal with null value, implement IF-THEN-ELSE branch logic, etc.

Second: The conversion function does not change the field type and value of the data in the table. It's the equivalent of copying the data and converting the data after it's replicated.

Data type conversions include both implicit and explicit conversions, and it is recommended that explicit data type conversions be used to ensure the reliability of SQL statements

Continuation III: Common functions apply to any type of data, including null values. Usually used to implement null value processing, empty value filter or set default value, etc.

Common functions include NVL (), Nvl2 (), Nullif (), coalesce (), Case expressions, decode (), etc.

Nesting: Single-line functions can be nested, with no limit on nesting levels. Grouping functions can be nested up to two levels. The order in which nested functions are executed is from inside to outside

Single line such as Select Empno,lpad (Initcap (Trim (ename)), 10, ' ") name, job, Sal from EMP;

Multiple lines such as SELECT MAX ("Avg") from the EMP group by deptno;--There's no point in using a group function here.

Description: Usually the function provided at the database level is simply processing the data, or it can only achieve very conventional functions

So it should not, or should not, be expected to implement particularly complex business logic at the level of database queries

If the logic of the application is mixed with the database, it will be detrimental to the maintenance and update of the code.

But also not conducive to database management, including data migration, database import and export, etc.

Date type

Overview: In a computer operating system or in a variety of advanced programming languages, dates are usually saved in a long integer, usually in milliseconds

Oracle internally 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 get the current system date and time

Operation: Date-type data can be directly added or minus a value, which results in the date. The agreed value represents the number of days to add and subtract

Two date data can be subtracted, the result is how many days difference between the two. Neither, because it doesn't make sense to add dates together.

NVL () function

Overview: It is used to replace null nulls with a specified default value, for type data such as characters, numbers, dates, and so on

Format: NVL (EXP1,EXP2)--Returns EXP2 value if the expression EXP1 value is null, otherwise EXP1 value is returned

For example: select Empno, ename, Sal, Comm, SAL+NVL (comm,0) from EMP;

Select Empno, ename, Job, NVL (Job, ' No job yet ') from EMP;

NVL2 () function

Overview: It is used to implement conditional expression functionality

Format: NVL2 (EXP1,EXP2,EXP3)--Returns the EXP2 value if the expression EXP1 value is not NULL, otherwise returns the EXP3 value

For example: select Empno, ename, Sal, Comm, NVL2 (comm, Sal+comm, Sal) gross income from EMP;

Nullif () function

Overview: It is used for data equivalence comparisons and returns null or one of the values being compared based on the comparison. Not many applications in actual development

Format: Nullif (EXP1,EXP2)--Returns null if the expression Exp1 is equal to EXP2 value, otherwise returns the value of EXP1

For example: Select name formerly known as Nullif (Pen_name, name) alias from author;

COALESCE () function

Overview: It is used to implement data "splice" function

Format: coalesec (EXP1,EXP2,...) --Examine each parameter expression in turn, and stop and return the value if it encounters a non-null value

Returns null if the expression is a null value. Usually the last expression is a field that ensures that it is not a null value

For example: select Empno, ename, Sal, Comm, Coalesec (sal+comm,sal,0) total income from EMP;

Case expressions

Overview: It is used to implement a multiple branch structure

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

For example: select Empno, ename, Sal,

Case Deptno Then ' finance Department '

When then ' research and Development Department '

When then ' sales Department '

Else ' Unknown Department '

End Department

from EMP;

Description: Every expression in case (such as deptno,10, finance Department, etc.) can be compounded.

This alignment is written to increase readability and, of course, to write code on the same line

The whole of the case to end is the equivalent of a field in a normal query, and the "department" after

Dedode () function

Overview: Similar to case expressions, it is also used to implement a multiple branch structure

Format: Decode (col|expression, Search1, RESULT1

[, SEARCH2, RESULT2,...,]

[, default])

For example: select Empno, ename, Sal,

Decode (Deptno, 10, ' finance Department ',

20, the ' Research and development Department ',

30, ' Sales department ',

' Unknown department ')

Department

from EMP;

COUNT () function

Format: COUNT (*) returns the total number of records in a group

COUNT (exp) returns a record that is not empty for an expression exp value

COUNT (DISTINCT (exp)) returns the number of non-empty records with an expression exp value that is not repeated

Example: SELECT COUNT (*) from emp;--by default, the entire table is a set of

Select COUNT (comm) from emp;--returns the number of records (rows) that are not empty Comm fields in the EMP table

Select COUNT (Distinct (DEPTNO)) from emp;--find the number of records that have deptno values that are non-null and not duplicates

grouping functions and Null values

Overview: Group functions omit null values in a column. You can use the NVL () function to force a grouping function to handle 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 the Non-empty 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];

For example: Select Deptno, Avg (SAL) from EMP Group by DEPTNO;

Description: A field that appears in the select list, if not included in a group function, that field must appear in the GROUP BY clause at the same time

Fields that are contained in the gropy by clause do not have to appear in the select list. The order in which clauses are executed is Where→group By→order by default in ascending order

Add: 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;--illegal

Note: If the GROUP BY clause is not present, the field (Single-line function) is not allowed to be mixed with the grouping function in the select list

Grouping functions are not allowed in a where, such as select Deptno, avg (SAL) from EMP where AVG (SAL) >2000 GROUP by deptno;--illegal

This is related to the order in which the clauses are executed. The WHERE clause executes first, and the GROUP BY clause is not executed when the WHERE clause is executed

The program does not know that this is a grouping, nor has it computed avg (SAL) 's average wage, so the group function is not allowed in the WHERE clause

Because the GROUP BY clause has not been executed, it is not sure how to group and how many groups are divided at this time

So there is only primary filtering in the WHERE clause. You can use the HAVING clause to filter the average wage at this time

HAVING clause

Overview: It is used to filter groupings

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 (' 1981-05-01 ', ' yyyy-mm-dd ')

Group by by Deptno, job

Having avg (SAL) > 1200

Order by deptno, job;