Oracle data types and operations

1. Oracle String manipulation

1.1 String Type

? Char and VARCHAR2 types

L Char holds the fixed long character, if the data is not satisfied with the specified length is filled with spaces, char type wasted space in exchange for the shortening of query time.

L VARCHAR2 store variable long characters, the actual data how much length, VARCHAR2 save space, query time is longer than char type.

? Storage encoding for Char and VARCHAR2

The default units that strings store in the database are bytes, or they can be explicitly specified as characters.

L char (10), equivalent to char (ten bytes)

L IF the specified unit is a character: char (ten char), 20 bytes

L VARCHAR2 (10), equivalent to VARCHAR2 (ten bytes)

L Specify the unit as a character: VARCHAR2 (20 CHAR)

Each English character occupies one byte, and each Chinese character is encoded differently, occupying 2-4 bytes.

L Zhs16gbk:2 bytes

L utf-8:2-4 bytes

? Maximum length of char and VARCHAR2

L Char type has a maximum length of 2000 bytes, i.e. char (2000), Save up to 2000 English characters, 1000 kanji (GBK)

L VARCHAR2 Maximum length of 4000 bytes, i.e.: VARCHAR2 (4000), save up to 4,000 English characters, 2000 kanji (GBK)

L Char If you do not specify a length, the default is 1 bytes, VARCHAR2 must specify the length

? Long and CLOB types

The long type can be considered an extended version of VARCHAR2 for storing variable-length strings up to 2GB of string data, but the long type has many limitations and is not recommended:

n each table can have only one long type column

n cannot be a primary key

n Cannot build an index

n cannot appear in query condition medium

L CLOB is used to store fixed-length or variable-length strings, up to 4GB of string data, and Oracle recommends using CLOB to replace the long type in development.

CREATE TABLE EMP ( ID Number (4), Name CHAR (20), Detail CLOB );

1.2 String functions

? Concat and "| |"

L Concat is a string join function with the following syntax:

CONCAT (CHAR1,CHAR2)

For the result of returning two strings concatenated, concat can only have two parameters, that is, only two strings, three strings concatenated, two concat are required.

Demo: Listed in "Name: Salary" form

SELECT CONCAT (CONCAT (ename, ': '), Sal) from EMP;

L | | Connection symbols, you can concatenate multiple strings

Demo: Listed in the form "Name: Performance: Salary"

SELECT ename| | ': ' | | comm| | ': ' | | Sal from EMP;

Demo: List The above information in an alias-empinfo manner

SELECT ename| | ': ' | | comm| | ': ' | | Sal as empinfo from EMP; --as can also be omitted SELECT ename| | ': ' | | comm| | ': ' | | Sal Empinfo from EMP;

l when connecting, if any one parameter is NULL, it is equivalent to connecting a space.

? LENGTH

Length (char) is used to return the lengths of the argument strings. If the character type is VARCHAR2, the actual length of the character is returned, and if it is char, the length also includes the padded space.

Demo: Query the length of an employee's name

SELECT Ename,length (ename) from EMP;

? UPPER, Lower and Initcap

L UPPER (char) is used to convert characters to uppercase

L LOWER (char) is used to convert a string to lowercase

L Initcap (char) is used to capitalize the first letter of each word in a string, other characters are lowercase, and words are separated by spaces and non-alphabetic characters

L if the input parameters of these three functions are null values, the null value is still returned

Demo: Convert Hello World to uppercase, lowercase, first letter uppercase

SELECT UPPER (' Hello World '), LOWER ("Hello World"), Initcap (' Hello World ') from dual;

? TRIM, LTRIM, RTRIM

L TRIM (C2 from C1) indicates the C2 from the front and back of the C1

L LTRIM (C1[,C2]) means to intercept from the left side of C1 C2

L RTRIM (C1[,C2]) means to intercept C2 from the right side of C1.

In the next two functions, if there is no parameter c2 the space is removed, the most commonly used is trim, which is often used to remove spaces before and after the string.

Demo: above three usage demos

SELECT TRIM (' e ' from ' elite ') as T1,ltrim (' elite ', ' e ') as T2,rtrim (' elite ', ' e ') as T3 from dual;

Demo: Demo without c2 parameter, remove space

SELECT TRIM (' e ' from ' elite ') as T1,ltrim (' elite ') as T2,rtrim (' elite ') as T3 from dual;

? Lpad, Rpad

Pad is a patch, Lpad and Rpad two functions are called the complement function.

L Lpad (CHAR1,N,CHAR2) left-complement function

L Rpad (CHAR1,N,CHAR2) right-complement function

The left or right side of the string parameter char1 is char2 to n bits, where the parameter char2 can be repeated multiple times.

Demo: In the SAL column, 6 bits with the complement bit

SELECT Ename,lpad (sal,6, ' $ ') as Lp,rpad (sal,6, ' $ ') as RP from EMP;

? SUBSTR

SUBSTR represents the truncation of substrings in a string, with the following syntax:

SUBSTR (Char,[m[,n])

Used to return a substring starting from the M bit to intercept n characters in Char, the first count of the string starts at 1.

L if m=0, start with the first letter and if M takes a negative number, start at the tail

L if n is not set, or the length of n exceeds the length of char, it is taken to the end of the string

Demo 1:

SELECT SUBSTR (' Hello World ', 4,5) from dual;

Demo 2:

SELECT SUBSTR (' Hello World ', 0,8) from dual;

Demo 3:

SELECT SUBSTR (' Hello World ', -4,2) from dual;

Demo 4:

SELECT SUBSTR (' Hello World ', 2) from dual;

Demo 5:

SELECT SUBSTR (' Hello World ', 0,100) from dual;

? INSTR

The position used to return a substring of a string, the syntax is:

INSTR (Char1,char2[,n[,m])

L returns the position of the substring char2 in the source string char1

L Start Search from n position, no n specified, search starting from 1th character

L m is used to specify the number of M occurrences of a substring, and if not specified, a value of 1

L If no substring char2 is found in Char1, return 0

Demo 1:

SELECT INSTR (' Hello World ', ' O ') from dual;

Demo 2:

SELECT INSTR (' Hello World ', ' O ', 6) from dual;

Demo 3:

SELECT INSTR (' Hello World ', ' O ', 9) from dual;

Demo 4:

SELECT INSTR (' Hello World ', ' o ', -1) from dual;

Demo 5:

SELECT INSTR (' Hello World ', ' o ', 6,1) from dual;

2. Oracle Numeric operations

2.1 Numeric types

? Integer

Number (P) represents an integer, p represents the total digits of the number, and the value is 1-38.

Demo: Student ID set to 4-bit integer

CREATE TABLE Stedunt (ID number (4));

? Floating point number

Number (P,s) represents a floating-point number, meaning the following:

L P:number can store the maximum number length (excluding the left and right sides of the 0)

L S: Indicates the maximum number of decimal digits (including 0 on the left)

L if s is specified but no p is specified, p defaults to 38

Example: ID number (*,s)

Demo: Set up an employee salary of 6 digits, a decimal place of 2 bits, and a maximum value of 9999.99

CREATE TABLE empx (sal number (6,2));

The Variant data type of number: The internal implementation is numbers, which can be interpreted as an alias for the name, and is intended to be compatible with multiple databases and programming languages.

L Number (P,s): Fully mapped to number (P,s)

L DECIMAL (p,s) or Dec (p,s): Fully mapped to number (P,s)

l integer or int: fully mapped to number (38) type

L SMALLINT: Fully mapped to number (38) type

L FLOAT (b): Map to Number type

L DOUBLE PRECISION: Map to Number type

L REAL: Map to Number type

2.2 Numeric functions

? ROUND

Round is used to round up the syntax as follows:

ROUND (N,[,m])

Used to round the parameter n according to the number requirements of M.

The n in the L parameter can be any number that refers to the number to be processed

L m must be an integer

L m take integer rounded to M bit after decimal point

L m takes 0 rounded to the whole digit

L m takes a negative number and rounds it to the M position before the decimal point

L m Default value is 0

Demonstrate:

SELECT ROUND (45.678,2), ROUND (45.678,0), ROUND (45.678,-1) from dual;

? TRUNC

Trunc means interception, with the following syntax:

TRUNC (N[,m])

The meaning of n,m is the same as round.

Demonstrate:

SELECT TRUNC (45.678,2), TRUNC (45.678,0), TRUNC (45.678,-1), TRUNC (45.678) from dual;

? MOD

Take the remainder, the syntax is as follows:

MOD (M,n)

Used to return the remainder after m divided by N, and if n is 0, returns m directly.

Demo: Pay the value of 1000 to take the remainder

SELECT Ename,mod (sal,1000) from EMP;

? Ceil and floor

L ceil (N): Represents the smallest integer value greater than or equal to n

Floor (N): Represents the maximum integer value that is less than or equal to n

Demonstrate:

SELECT ceil (45.678), Floor (45.678), Ceil (n), Floor (*) from dual;

3. Oracle Date operation

3.1 DATE

Date and timestamp are the most common types of dates in Oracle. Date is used to save dates and times, representing the range from January 1, 4712 BC to December 31, 9999.

The actual storage of the date type in the database is fixed at 7 bytes, in the following format:

L 1th byte: Century +100

L 2nd Byte: Year

L 3rd Byte: Month

L 4th Byte: Day

L 5th Byte: Hours +1

L 6th byte: minute +1

L 7th byte: sec +1

? TIMESTAMP

The timestamp represents a timestamp, and the difference from date is that not only can the date and time be saved, but also the fractional seconds can be saved, the 0-9-bit, the default 6-bit, and the highest precision to the NS (nanosecond) level.

The database is stored internally with 7 or 11 bytes, with a precision of 0 o'clock, with 7 bytes of storage, the same as the date function, and with a precision greater than 0 with 11 bytes of storage. The format is as follows:

L 第1-7 Bytes: Same as Date

L 第8-11 Bytes: nanosecond, 4 byte storage, internal operation type integer

Demonstrate:

CREATE TABLE Test ( C1 DATE, C2 TIMESTAMP (9) );

3.2 Date Keywords

? Sysdate

Sysdate essence is an internal function of Oracle that returns the current system time, accurate to the second, the default display format is DD-MON-RR, and the time is not displayed for only the year and month days.

Demonstrate:

SELECT sysdate from dual;

If the database is an English-language environment, the form "27-JUN-17" will be displayed, and if it is a Chinese environment, the form "2 July-June-17" will be displayed.

If you want to display the seconds and minutes, you need to convert the format, using the To_char conversion.

Demonstrate:

SELECT to_char (sysdate, ' Yyyy-mm-dd Day Hh24:mi:ss ') from dual;

? Systimestamp

Systimestamp is also Oracle's internal date function, which returns the current system date and time, accurate to milliseconds.

Demonstrate:

SELECT Systimestamp,to_char (Systimestamp, ' SSSS. FF ') from dual;

3.3 Date Conversion function

Date data sometimes needs to be converted to and from string data, using date conversion functions, including To_char and To_date.

? To_date

The function of to_date is to convert a string to a date type in a custom format, with the following syntax:

To_date (Char[,fmt[,nlsparams])

Where: char is the string to convert, FMT is the conversion format, Nlsparams is the specified date language. The usual formats are as follows:

Format	Meaning
Yy	2-digit year
YYYY	4-digit year
Mm	2-digit Month
MON	The month of simple spelling
MONTH	month of full spell
Dd	Day of the 2-digit number
Dy	Abbreviation of the Week
Day	A full spell of the week
HH24	24-Hour Hour
HH12	12-Hour Hour
MI	Show minutes
Ss	Show seconds

Demo: Find employees who have entered the workforce after 1982 years

SELECT ename,hiredate from emp WHERE hiredate>to_date (' 1982-01-01 ', ' yyyy-mm-dd ');

? To_char

The role of To_char is to convert data of other types (date, value) to character types, mainly on date types, with the following syntax:

To_char (Date[,fmt[,nlsparams])

Where FMT is the format that will date type data date in FMT format, Nlsparams is used to specify the date language.

Demo: Convert an employee's hiredate to a month and day format display

Select Ename,hiredate,to_char (hiredate, ' YYYY ' year "MM" month "DD" Day ") from EMP;

3.4 Date Common functions

? Last_day

Grammar:

Last_day (date)

Returns the date of the last day of the month in which some business logic is calculated according to the natural month, or when the month-end recurring activity is scheduled.

Demonstrate:

SELECT Last_day (sysdate) from dual; --Query the last day of the month SELECT last_day (' 20月-February -2000 ') from dual; --Query the last day of February 2000

? Add_months

Grammar:

Add_months (Date,i)

Returns a date plus the date value after I month.

L parameter I can be any number, most of the time take a positive integer

L If I is a decimal, the integer will be truncated after the join operation

L If I is negative, gets the date value minus I month

Demo: Calculate the 20-year anniversary of the onboarding staff

SELECT ename,add_months (hiredate,12*20) from EMP;

? Months_between

Grammar:

Months_between (DATE1,DATE2)

Used to calculate the number of months between Date1 and Date2 two dates, the actual operation is Date1-date2, and if the date2 time is later than date1, a negative value is obtained.

Unless the two date interval is an integer month, you get a result with a decimal place.

Demo 1:date1 than date2 large integer month

SELECT Months_between (' September-September-2008 ', ' September-October -2007 ') from dual;

Demo 2:date1 Smaller integer month than date2

SELECT Months_between (' September-September-2006 ', ' September-October -2007 ') from dual;

Demo 3:date1 than date2 small non-integer month

SELECT Months_between (' 2 January-September-2006 ', ' September-October -2007 ') from dual;

? Next_day

Grammar:

Next_day (Date,char)

Returns the next week of date data, and the number of weeks is determined by the parameter char. In the Chinese environment, the use of the "Wednesday" format directly, in English environment, the need to use the "Wednesday" format. To avoid trouble, you can use 1-7 directly to represent Sunday-Saturday.

Demo: What day is the next Wednesday of June 27, 17?

SELECT Next_day (' 2 July-June -17 ', 4) from dual;

? LEAST, Greatest

Grammar:

LEAST (EXPR1[,EXPR2[,EXPR3]] ...)--Returns the minimum value Greatest (EXPR1[,EXPR2[,EXPR2]] ...)--Returns the maximum value

Two functions can have more than one numeric value, but the parameter type must be the same, the return result is the largest or smallest value in the parameter list, before the comparison, the second parameter in the argument list is implicitly converted to the data type of the first parameter, so if you can convert, then continue to compare, if not the conversion will be an error.

Demonstrate:

SELECT LEAST (sysdate, ' October-October -09 '), Greatest (Sysdate, ' October-October -09 ') from dual;

? EXTRACT

Grammar:

EXTRACT (date from datetime)

Extracts the data specified by date from the parameter datetime, such as extracting the year, month, and day.

Demo: Take out the year

SELECT ename,extract (year from HireDate) from EMP;

4. Null value operation

Null is an important concept in the database, that is, the null value. When some field values in the table are unknown or temporarily absent, the value is null.

A simple data type in Java is not nullable, and any data type in the database can have null values.

? Insert Null value

In a data table, if you want to insert a null value, you can either display the specified null value or not insert a field value, which implicitly represents a null value.

Demonstrate:

CREATE TABLE Student ( ID Number (4), Name CHAR (20), Gender CHAR (1) ); INSERT into student VALUES (1000, ' Li Mo sorrow ', ' F '); INSERT into student VALUES (1001, ' 林平 ', NULL); --Show Insert null value INSERT into student (id,name) VALUES (1002, ' Oriental unbeaten '); --Implicitly inserting null values

? Update to NULL value

Updates a field of data to a null value, and is updated to the same as other data methods, and is used in conjunction with where, otherwise the entire table will be updated to a null value.

Demonstrate:

UPDATE student SET Name=null; --All tables are updated

Note This update can only be manipulated if the column does not have a non-null constraint. If the column has a non-null constraint, it cannot be updated to a null value.

? Null condition Query

In a conditional query, because NULL does not equal any value, "=" cannot be used and must be judged by "is null" or "is not NULL".

Demonstrate:

SELECT Name,gender from Student WHERE gender are not NULL; SELECT Name,gender from Student WHERE gender is NULL;

? Non-null constraint

A non-null (NOT NULL) constraint is a constraint that ensures that a field value in a data table is not empty.

Because the columns of any data allow null values by default, the system business logic may require that some columns cannot be empty, and you will need to specify that the column is not allowed to be empty when you build the table.

Once a field is set to a non-null conditional constraint, the field must have a valid value. That is, when you perform an Insert data operation, you must provide data for this column, and when you perform an update operation, you cannot set the value of the column to null.

Demonstrate:

ALTER TABLE student MODIFY (NAME not NULL);

4.1 Control functions

? NVL

Grammar:

NVL (EXPR1,EXPR2)

Convert NULL to non-null. If EXPR1 is null, the value EXPR2,EXPR2 is a non-null value.

where Expr1 and EXPR2 can be any data type, but the data type of the two parameter must be consistent.

Any number is computed with NULL, and the last is null. If the employee's monthly income is calculated, if comm is null, then the income is empty, not logical, so the comm of the null value is converted to 0 first, and then added.

Demo: Calculate employee Monthly income, salary + performance

SELECT ENAME,SAL,COMM,SAL+NVL (comm,0) monthly revenue from EMP;

? NVL2

Grammar:

NVL2 (EXPR1,EXPR2,EXPR3)

Like the NVL function, NULL is converted to a non-null value. The NVL2 is used to determine whether the EXPR1 is null, if not NULL, returns EXPR2, and if NULL, returns EXPR3.

Demo: Query Employee Monthly Income

SELECT Ename,sal,comm,nvl2 (comm,sal+comm,sal) as monthly revenue from EMP;

Oracle data types and operations