Oracle Common functions

Some time ago learning Oracle To do the study notes, organized a bit, the following is a share of the Oracle common functions of some notes, and later will share other parts of the notes, please criticize correct.

Use of the To_date () function in a 1.Oracle database:
Insert a record into the EMP table:

sql> INSERT INTO EMP values (1234, ' lizelu ', ' BOSS ', 1234, ' 1980-12-06 ', 10000.0,0,30);    INSERT into EMP values (1234, ' lizelu ', ' BOSS ', 1234, ' 1980-12-06 ', 10000.0,0,30)    ORA-01861: The text does not match the format string--the date format is incorrect    Use the To_date () function to fix: Format to_date (' 1965-02-05 ', ' yyyy-mm-dd ');    

Character functions in 2.Oracle:

Character functions are the most common function in Oracle,
Lower (char); Converts a string to lowercase format;
Upper (char); Convert string to uppercase format;
Length (char); Returns the lengths of the strings;
SUBSTR (char,m,n); string strings;
Replace (CHAR,SEARCH_CHAR,REPLACE_STR);

1. Export all employees ' names in lowercase format

Select Lower (Emp.ename) from EMP;

2. Display a name that is exactly 5 characters;

Select ename from emp where length (ename) = 5;

3. Display the first three characters of the name; substr (char,2,3); The delegate takes from the second, takes three characters;

Select substr (ename,1,3) from EMP;

4. Display the name of the first letter capital, the remaining lowercase;
Divided into three parts walk:
(1) Capitalize the first letter:

Select Upper (substr (emp.ename,1,1)) from EMP;

(2) Put the following letter in lowercase:

Select Lower (substr (Ename,2,length (ename)-1)) from EMP;

(3) Connect two strings together | | (The pipe character is connected)

Select Upper (substr (emp.ename,1,1)) | | Lower (substr (Ename,2,length (ename)-1)) from EMP;

5. Convert a in the name to A;

Select Replace (ename, ' a ', ' a ') from EMP;

Mathematical Functions in 3.Oracle:
1.round (N,[m]): Rounded, omitted m rounded to integer digits, M is the number of decimal places;

Select Round (sal,1) from emp where ename= ' MILLER ';

2.trunc (N,[m]): Preserve decimal digits, M is the number of decimal digits

Select Trunc (sal,1) from emp where ename= ' MILLER ';

3.mod (n,m): Go decimal;

4.floor (N): Returns the largest integer less than or equal to N; ceil (n): Returns the smallest integer greater than or equal to n

Sql> Select Floor (SAL) from EMP where ename= ' MILLER ';--take down the entire floor (SAL)----------1300sql> Select Ceil (SAL) from EMP W Here ename= ' MILLER ';--Rounding up Ceil (SAL)----------1301

Other mathematical functions:
ABS (N): Returns the absolute value of the number N. ACOs (n), ASIN (n), Stan (n) returns the inverse cosine of the number, the inverse of the sine, the value of the inverse tangent
EXP (n): Returns the n-th power of E; Log (m,n); Returns a pair of values; power (m,n); returns the n-th power of M

Date functions in 4.Oracle:
Date functions are used to process data of date type: Dd-mon-yy format by default.
(1) Sysdate: The function returns the system time

Sql> select Sysdate from dual; Sysdate-----------2014-4-13 9

(2) add_moths (d,n);

Showing employees in the 8 months of employment;

SELECT * from emp where sysdate>add_months (emp.hiredate,8);

(3) Last_day (d); Returns the current date the last day of the month

Select Last_day (emp.hiredate) from EMP;

(4) Number of days when the entry of a member is displayed

Sql> Select Ename,round (sysdate-emp.hiredate) "Days of Entry" from EMP;

(5) Identify the employee who entered the 3rd day of the Month

Sql> SELECT * from emp where (Last_day (emp.hiredate)-emp.hiredate) = 2;

Conversion of data types in 5.Oracle
To_char (): Converts data to String type: To_char (string, type);

1. Date Conversion

Sql> Select To_char (sysdate, ' Yyyy/mm/dd hh24:mi:ss ') from dual; To_char (sysdate, ' YYYY/MM/DDHH2------------------------------2014/04/13 10:13:52

2. Show employee information for 1980 entry

Sql> SELECT * from emp where TO_CHAR (emp.hiredate, ' yyyy ') = 1980; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------------------1234 LI Zelu BOSS 1234 1980-12-6 10000.00 0.00 307369 SMITH Clerk 7902 1980-12-17 800.00 20

System functions in 6.Oracle: Sys_context ();
1) terminal the terminal identifier corresponding to the current session client

Sql> Select Sys_context (' USERENV ', ' terminal ') from dual; Sys_context (' USERENV ', ' Termina--------------------------------------------------------------------------------web-a93b1e61669

2) Language language

Sql> Select Sys_context (' USERENV ', ' language ') from dual; Sys_context (' USERENV ', ' Languag--------------------------------------------------------------------------------Simplified Chinese_china . Zhs16gbk

3) Db_name The current DB instance name

Sql> Select Sys_context (' USERENV ', ' db_name ') from dual; Sys_context (' USERENV ', ' db_ NAME--------------------------------------------------------------------------------ORCL

4) Session_user the database that corresponds to the current session

Sql> Select Sys_context (' USERENV ', ' Session_user ') from dual; Sys_context (' USERENV ', ' SESSION--------------------------------------------------------------------------------SCOTT

5) Current_schema: View current Scenario

Sql> Select Sys_context (' USERENV ', ' Current_schema ') from dual; Sys_context (' USERENV ', ' Current--------------------------------------------------------------------------------SCOTT

Oracle Common functions