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  intoEmpValues(1234,'Lizelu','BOSS',1234,'1980-12-06',10000.0,0, -); Insert  intoEmpValues(1234,'Lizelu','BOSS',1234,'1980-12-06',10000.0,0, -) ORA-01861: The text does not match the format string--date format is incorrectUse 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  from EMP;

2. Display a name that is exactly 5 characters;

Select  from 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,1fromwhere ename='MILLER';

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

Select trunc (sal,1fromwhere 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) fromEmpwhereEname='MILLER';--Rounding down Floor(SAL)----------1300SQL> SelectCeil (SAL) fromEmpwhereEname='MILLER';--Rounding upceil (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 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 from dual; Sysdate-----------4-9 

(2) add_moths (d,n);

Showing employees in the 8 months of employment;

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

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

Select  from EMP;

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

SQL>Select ename,round(sysdate- from EMP;

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

SQL>Select*fromwhere (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 *  fromEmpwhereTo_char (Emp.hiredate,'yyyy')=1980; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------1234Lizelu BOSS1234 1980- A-6 10000.00 0.00  -7369SMITH Clerk7902 1980- A- - 800.00  -

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