Shulou(Shulou.com)06/01 Report--

Some time ago, I sorted out the study notes I made when I was learning Oracle. Here are some notes of the common functions of Oracle shared. I will share other notes later. Please criticize and correct them.

Use of the to_date () function in the 1.Oracle database:

Insert a record into the emp table:

SQL > insert into emp values (1234 LIZELUBG memo: 1800-12-06); insert into emp values (insert into emp values: text and format string do not match-date format is not correct, use to_date () function: format to_date ('1965-02-05)

Character functions in 2.Oracle:

Character function is the most commonly used function in Oracle.

Lower (char); converts a string to lowercase format

Upper (char); converts a string to uppercase format

Length (char); returns the length of the string

Substr (char,m,n); take the string of a string

Replace (char,search_char,replace_str)

1. Output all employee names in lowercase format

Select lower (emp.ename) from emp

two。 Displays a name that is exactly 5 characters

Select ename from emp where length (ename) = 5

3. Displays the first three characters of the name; substr (char,2,3); stands for taking three characters from the second

Select substr (ename,1,3) from emp

4. The first letter is required to display the name in uppercase and the rest in lowercase

Divided into three parts to go:

(1) capitalize the initials:

Select upper (substr (emp.ename,1,1)) from emp

(2) lowercase the following letters:

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

(3) concatenate two strings | | (pipe characters are concatenated)

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

5. Convert An in the name to a

Select replace (ename,'A','a') from emp

Mathematical functions in 3.Oracle:

1.round (n, [m]): rounded, omitting m rounds to integer places, m is the number of decimal places

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

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

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

3.mod (NMagol 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';-- rounding down FLOOR (SAL)-1300SQL > select ceil (sal) from emp where 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 value of the inverse cosine, arc sine, arc tangent of a number.

Exp (n): returns e to the nth power; log (m ~ n); returns the logarithm value; power (m ~ m ~ n); returns m to the n power

Date function in 4.Oracle:

The date function is used to process data of type date: it is in dd-mon-yy format by default.

(1) sysdate: this function returns the system time

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

(2) add_moths (dQuery n)

Show employees who have been employed 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 on the current date

Select last_day (emp.hiredate) from emp

(4) the number of days of entry of the display staff

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

(5) find out the employees on the penultimate 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 hh34:mi:ss') from dual;TO_CHAR (SYSDATE,'YYYY/MM/DDHH2--2014/04/13 10:13:52

two。 Displays information about employees who joined the workforce in 1980

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

System function in 6.Oracle: sys_context ()

1) Terminal identifier corresponding to terminal's current session customer

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) the current database instance name of db_name

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

4) the database corresponding to session_user 's current session

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

5) current_schema: view the current solution

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.