Oracle Walkthrough (Third lecture)

First, Oracle common system functions 1. Date class function(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) Gets the first day of the current month.

Sql> Select Trunc (sysdate, ' month ') from dual; TRUNC (sysdate, ' MONTH ')----------------------2014-11-01

(3) Get the last day of the current month

Sql> Select Trunc (Last_day (sysdate)) from dual; TRUNC (Last_day (sysdate))------------------------2014-11-30

(4) Get the first day of the current year

Sql> Select Trunc (sysdate, ' year ') from dual; TRUNC (Sysdate, ' year ')---------------------2014-01-01

(5) Get the last day of the current year

Sql> Select Add_months (trunc (Sysdate, ' Year '), 1 from dual; Add_months (TRUNC (Sysdate, ' year------------------------------2014-12-31

(6) Number of days to take the current month

Sql> Select cast (To_char (Last_day (sysdate), ' DD ') as int) from dual;         CAST (To_char (Last_day (sysdate)---------------------------------------                                     30

(7) Take the remaining days of the current month

Sql> Select Last_day (sysdate)-sysdate from dual; Last_day (sysdate)-sysdate-------------------------                        8

(8) Date of the next Sunday of the current date (return to next week's date, day 1-7 or Sunday-Saturday, 1 for Sunday)

Sql> Select Next_day (sysdate,1) from dual; Next_day (sysdate,1)-------------------2014-11-23 17:39:48

(9) Take two month of the date difference

<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 (' 2014-1------------------------------10

2. String class 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 capitalization conversions, length lengths, connectors | |

Sql> Select Upper (' a ') | | Lower (' B ') | | Length (' AAA ') from dual; UPPER (' A ') | | LOWER (' B ') | | LENGTH------------------------------Ab3

(3) Lpad and Rpad, fill function, add the specified character to the left or right 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, their role is to remove the unwanted characters from the left side of the string, the right side, and the left and far sides, by default deleting spaces

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, count fetch length, not specify count, the default extract to the end of the string.

Sql> Select substr (' ABCDE ') from dual; SUBSTR (' ABCDE ',-------------------AB)

(6) INSTR returns the location where the specified string is located
INSTR (String,set[,start [, occurrence]]) If you specify start,oracle to skip all previous strings to that position to start the search, occurence, is forcing INSTR to skip the previous couple of matches to the string, Given the next matching position, if occurence specifies 3, that is the position of the third time.

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 omitted m is rounded to an integer, if M is a positive number is rounded to the decimal point m, and if M is negative, it is rounded to the decimal point before the M bit.

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.1              10

(2) trunc (N,[m]) is used to intercept numbers, if M is omitted to intercept the fractional portion, if M is a positive number is truncated to the decimal point m bit, if M is negative, then intercept the decimal point of M bit.

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.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, and 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           12

4. system function Sys_context
(1) terminal the terminal identifier corresponding to the current session client. (2) Lanuage: Language. (3) db_name: The current database name. (4) Nls_date_format: the date format corresponding to the current session customer. (5) Session_user: The database user name corresponding to the current session client. (6) Current_schema: The default scheme name corresponding to the current session customer. (7) Host: Returns the host name where the database resides.

Oracle Walkthrough (Third lecture)