Step-by-step explanation of ORACLE (Lecture 3)

Step-by-step explanation of ORACLE (Lecture 3)
I. Common oracle system functions1. Date Functions(1) to_date (string, Format)

SQL> select to_date('2014-10-11 12:13:14','yyyy-mm-dd hh:mi:ss') from dual;TO_DATE('2014-10-1112:13:14','------------------------------2014-10-11 12:13:14

(2) obtain the first day of the current month.

SQL> select trunc(sysdate,'month') from dual;TRUNC(SYSDATE,'MONTH')----------------------2014-11-01

(3) obtain the last day of the current month

SQL> select trunc(last_day(sysdate)) from dual;TRUNC(LAST_DAY(SYSDATE))------------------------2014-11-30

(4) obtain the first day of the current year

SQL> select trunc(sysdate,'year') from dual;TRUNC(SYSDATE,'YEAR')---------------------2014-01-01

(5) obtain the last day of the current year

SQL> select add_months(trunc(sysdate,'year'),12)-1 from dual;ADD_MONTHS(TRUNC(SYSDATE,'YEAR------------------------------2014-12-31

(6) the number of days in the current month

SQL> select cast(to_char(last_day(sysdate),'dd') as int) from dual;         CAST(TO_CHAR(LAST_DAY(SYSDATE)---------------------------------------                                     30

(7) retrieve the remaining days of the current month

SQL> select last_day(sysdate)-sysdate from dual;LAST_DAY(SYSDATE)-SYSDATE-------------------------                        8

(8) Take the date of the next Sunday of the current date (return the date of the next week, day is 1-7 or Sunday-Saturday, 1 indicates Sunday)

SQL> select next_day(sysdate,1) from dual;NEXT_DAY(SYSDATE,1)-------------------2014-11-23 17:39:48

(9) Take the month with two different dates

<pre name="code" class="sql" style="font-size: 14px;">SQL> <span style="font-family: Arial, Helvetica, sans-serif;">select months_between(to_date('2014-11-01','yyyy-mm-dd'),to_date('2014-01-01','yyyy-mm-dd')) from dual;</span>

MONTHS_BETWEEN (TO_DATE ('2017-1 -------------------------------- 10

2. String Functions(1) to_char (string, Format)

SQL> select to_char(sysdate,'yyyy-mm') from dual;TO_CHAR(SYSDATE,'YYYY-MM')--------------------------2014-11

(2) upper and lower case-insensitive conversion, length, connector |

SQL> select upper('a') || lower('B') || length('aaa') from dual;UPPER('A')||LOWER('B')||LENGTH------------------------------Ab3

(3) Fill the LPAD and RPAD functions. Add a specified character on the left or right side to make the length reach the specified length.

SQL> select lpad('aaa',5,'*'),rpad('aaa',5,'*') from dual;LPAD('AAA',5,'*') RPAD('AAA',5,'*')----------------- -----------------**aaa             aaa**

(4) LTRIM, RTRIM, and TRIM are used to delete unnecessary characters from the left, right, and left and right sides of the string. spaces are deleted by default.

SQL> select ltrim('abbb','a'),rtrim('1234','4'),trim(' ABC ') from dual;LTRIM('ABBB','A') RTRIM('1234','4') TRIM('ABC')----------------- ----------------- -----------bbb               123               ABC

(5) SUBSTR (string, start [, count]) This function tells oracle to extract a subset of string, start position, and count extract length. If count is not specified, extracted to the end of the string by default.

SQL> select substr('abcde',1,2) from dual;SUBSTR('ABCDE',1,2)-------------------ab

(6) INSTR returns the position of the specified string
INSTR (string, set [, start [, occurrence]) If start is specified, oracle skips all the preceding strings to start searching, it forces the instr to skip the first few times to match the string and give the next matching position. If occurence specifies 3, it is the third matching position.

SQL> select instr('abcad','a',1),instr('abcad','a',2,1) from dual;INSTR('ABCAD','A',1) INSTR('ABCAD','A',2,1)-------------------- ----------------------                   1                      4

3. mathematical functions
(1) round (n, [m]) is used to perform rounding. If m is omitted, It is rounded to an integer. If m is positive, it is rounded to the decimal place after m, if m is negative, it is rounded to the first digit of the decimal point.

SQL> select round(12.12),round(12.12,1),round(12.12,-1) from dual;ROUND(12.12) ROUND(12.12,1) ROUND(12.12,-1)------------ -------------- ---------------          12           12.1              10

(2) trunc (n, [m]) is used to intercept digits. If m is saved, the fractional part is truncated. If m is positive, the decimal point is truncated to m, if m is negative, it is truncated to the m digit before the decimal point.

SQL> select trunc(12.12),trunc(12.12,1),trunc(12.12,-1) from dual;TRUNC(12.12) TRUNC(12.12,1) TRUNC(12.12,-1)------------ -------------- ---------------          12           12.1              10

(3) MOD (x, y) returns the remainder of x divided by y, CEIL (x) returns the smallest integer greater than or equal to x, FLOOR (x) returns the largest integer less than or equal to x.

SQL> select mod(1,3),ceil(12.56),floor(12.56) from dual;  MOD(1,3) CEIL(12.56) FLOOR(12.56)---------- ----------- ------------         1          13           12

4. System Function sys_context
(1) terminal identifier corresponding to the current session customer of terminal. (2) lanuage: language. (3) db_name: name of the current database. (4) nls_date_format: Date Format corresponding to the current session customer. (5) session_user: User Name of the database corresponding to the current session. (6) current_schema: the default solution name corresponding to the current session customer. (7) host: return the host Name of the database.

SQL> select sys_context('userenv','db_name') dbName,sys_context('userenv','nls_date_format') date_fromat from dual;DBNAME                                                                           DATE_FROMAT-------------------------------------------------------------------------------- --------------------------------------------------------------------------------orcl                                                                             DD-MON-RR