Oracle series: (8) one-line function
One-line function: only one parameter input, only one result output
Multiline function or grouping function: can have multiple parameter inputs and only one result output
Test the lower/upper/initcap function, using the dual dumb table
Select lower ('www.BAIdu.COM') from dual;select upper (' www.BAIdu.COM') from dual;select initcap ('www.BAIdu.COM') from dual
Test the concat/substr function, starting with 1, to represent characters, both Chinese and English
Select concat ('hello',' Hello') from dual; is correct
Select concat ('hello',' Hello', 'World') from dual; error
Select 'hello' | |' Hello'| | 'World' from dual; is correct
Select concat ('hello',concat (' Hello', 'World') from dual; is correct
Select substr ('hello Hello', 5jol 3) from dual
5 means that it starts with the first character, and the first character is 1, which is processed in both Chinese and English.
3 means to take several characters in succession
Test the length/lengthb function. The encoding method is UTF8/GBK. One Chinese account for 2 bytes and one English one byte.
Select length ('hello Hello') from dual; select lengthb ('hello Hello') from dual
Test the instr/lpad/rpad function to find the first occurrence from left to right, starting with 1
Select instr ('helloworld','o') from dual
Note: no return is found, case sensitive
Select LPAD ('hello',10,'#') from dual;select RPAD (' hello',10,'#') from dual
Test trim/replace function
Select trim ('from' hell') from dual;select replace ('hello','l','L') from dual
Test the round/trunc/mod function on the numerical type
Select round (3.1415) from dual;select trunc (3.1415) from dual;select mod (10) from dual
Current date:
Select sysdate from dual
Test the effect of round on date (month)
Select round (sysdate,'month') from dual
Test the effect of round on date (year)
Select round (sysdate,'year') from dual
Test the effect of trunc on date (month)
Select trunc (sysdate,'month') from dual
Test the effect of trunc on date (year)
Select trunc (sysdate,'year') from dual
Show yesterday, today, tomorrow's date, date type +-value = date type
Select sysdate-1 "yesterday", sysdate "Today", sysdate+1 "tomorrow" from dual
The approximate length of service of the employee is displayed in the form of year and month, date-date = value, assuming: 365 days in a year and 30 days in January
Select ename "name", round (sysdate-hiredate,0) / 365seniority from emp
Use the months_between function to calculate exactly how many months there are until the end of the year
Select months_between ('31-December-16 September September Sysdate) from dual
Use the months_between function to display the length of service of employees in the form of precise months
Select ename "name", months_between (sysdate,hiredate) "precise monthly length of service" from emp
Test the add_months function, what's the date today next month?
Select add_months (sysdate,1) from dual
Test the add_months function, what's the date today last month?
Select add_months (sysdate,-1) from dual
Test the next_day function, starting from today, what's the date next Wednesday [Chinese platform]
Select next_day (sysdate,' Wednesday') from dual
Test the next_day function, starting from today, what is the number of the following Wednesday [Chinese platform]
Select next_day (next_day (sysdate,' Wednesday'), 'Wednesday') from dual
Test the next_day function, starting from today, what is the date of the next Wednesday and the next Sunday [Chinese platform]
Select next_day (next_day (sysdate,' Wednesday'), 'Sunday') from dual
Test the last_day function, what is the last day of this month?
Select last_day (sysdate) from dual
Test the last_day function, what is the number on the penultimate day of this month
Select last_day (sysdate)-1 from dual
Test the last_day function, what is the last day of the next month?
Select last_day (add_months (sysdate,1)) from dual
Test the last_day function, what is the last day of the last month?
Select last_day (add_months (sysdate,-1)) from dual
Note:
1) date-date = number of days
2) date +-days = date
