Common Oracle Functions

Common Oracle Functions

Some of the notes I took when I was learning Oracle some time ago. Below are some of the notes for common Oracle functions. I will share other notes later. Please criticize and correct me.

 

1. Use of the to_date () function in Oracle Database:
Insert a record to the emp table:

SQL> insert into emp values (1234, 'lizelu', 'boss', 1234, '2017-12-06 ', 1980, 10000.0); insert into emp values (1234, 'lizelu', 'bos', 1234, '2017-12-06 ', 1980, 10000.0,) ORA-01861: text does not match the format string -- date format does not use the to_date () function to get it done: format: to_date ('1970-02-05 ', 'yyyy-mm-dd ');

 

2. Character functions in Oracle:

Character functions are the most common functions in Oracle,
Lower (char); converts string to lowercase format;
Upper (char); converts string to uppercase format;
Length (char); returns the length of the string;
Substr (char, m, n); string;
Replace (char, search_char, replace_str );

1. output the names of all employees in lowercase.

select lower(emp.ename) from emp;

2. The name is exactly five characters long;

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

3. display the first three characters of the name. substr (char, 2, 3) indicates that the second character is used;

select substr(ename,1,3) from emp;

4. The first letter and the other lowercase letters are required to be displayed;
Divided into three parts:
(1) capital the first letter:

　　select upper(substr(emp.ename,1,1)) from emp;

(2) lowercase letters:

select lower(substr(ename,2,length(ename)-1)) from emp;

(3) connect two strings | (the pipe operator is used for connection)

select upper(substr(emp.ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

 

 

5. convert A from the name to;

select replace(ename,'A','a') from emp;

 

3. mathematical functions in Oracle:
1. round (n, [m]): rounding. m is the number of decimal places if m is omitted;

select round(sal,1) from emp where ename='MILLER';

2. trunc (n, [m]): retain decimal places. m indicates the number of decimal places.

select trunc(sal,1) from emp where ename='MILLER';

3. mod (n, m): 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 where ename = 'miller '; -- rounded up to CEIL (SAL) ---------- 1301

Other mathematical functions:
Abs (n): returns the absolute value of number n. Acos (n), asin (n), stan (n) returns the arccosine, arcsin, and arctangent of the number.
Exp (n): returns the n power of e; log (m, n); returns the logarithm value; power (m, n); returns the n power of m.

 

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

SQL> select sysdate from dual;SYSDATE-----------2014-4-13 9

(2) add_moths (d, n );

Displays employees who have been hired for more than 8 months;

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

 

(3) last_day (d); returns the last day of the month of the current date.

select last_day(emp.hiredate) from emp;

 

(4) display the number of days of employment

SQL> select ename, round (sysdate-emp.hiredate) "days of employment" from emp;

 

(5) Find the employees who have joined the company in the last 3rd days of the month.

SQL> select * from emp where (last_day(emp.hiredate)-emp.hiredate)=2;

 

 

 

 

5. Data type conversion in Oracle
To_char (): converts data to the 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. display information about employees who joined the company in April 1980

SQL> select * from emp where to_char(emp.hiredate,'yyyy')=1980;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------1234 LIZELU BOSS 1234 1980-12-6 10000.00 0.00 307369 SMITH CLERK 7902 1980-12-17 800.00 20

 

6. system functions in Oracle: sys_context ();
1) terminal identifier of the current session Client

SQL> select sys_context('USERENV','terminal') from dual;SYS_CONTEXT('USERENV','TERMINA--------------------------------------------------------------------------------WEB-A93B1E61669

2) language

SQL> select sys_context('USERENV','language') from dual;SYS_CONTEXT('USERENV','LANGUAG--------------------------------------------------------------------------------SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

3) db_name current database instance name

SQL> select sys_context('USERENV','db_name') from dual;SYS_CONTEXT('USERENV','DB_NAME--------------------------------------------------------------------------------orcl

 

4) The database corresponding to the current session of session_user

SQL> select sys_context('USERENV','session_user') from dual;SYS_CONTEXT('USERENV','SESSION--------------------------------------------------------------------------------SCOTT

 

5) current_schema: view the current scheme

SQL> select sys_context('USERENV','current_schema') from dual;SYS_CONTEXT('USERENV','CURRENT--------------------------------------------------------------------------------SCOTT

 

 

 

What are common Oracle functions?

Too many values include sum (), max (), min (), count (), decode (), case, avg (), to_date (), and Trunc, this can be learned only when used. These are basic things. When you use them, pay attention to accumulation and remember them.

What are common oracle functions?

It usually depends on your usage.

Commonly used date processing functions (month_between, add_months, next_day, extract ...)
Conversion Function (to_number, to_char, to_date)
Character Processing functions (substr, replace, trim, upper, lower, concat, instr ...)
Mathematical functions (I have never used them, so rondom and trunc cannot be provided)
Logic Functions (coalesce, nvl ..)
Aggregate functions (sum, avg, max, min)