Analysis of Oracle built-in functions

Oracle built-in function analysis SQL single record function 1. ASCII returns the decimal number corresponding to the specified character. SQL> select ascii ('A') A, ascii ('A') A, ascii ('0') zero, ascii ('') space from dual; a zero space --------- 65 97 48 32 www.2cto.com 2. the CHR gives an integer and returns the corresponding characters. SQL> select chr (54740) zhao, chr (65) chr65 from dual; ZH C ---zhao A3.CONCAT connects two strings; SQL> select concat ('010-', '000000') |' to 23' Gao Qian competing phone number from dual; Gao Qian competing phone number -------------- 010-88888888 to 23 4. INITCAP returns the string and converts the first letter of the string to uppercase. SQL> select initcap ('Smith ') upp from dual; UPP ----- smith www.2cto.com 5. INSTR (C1, C2, I, J) searches for a specified character in a string and returns the location where the specified character is found; the start position of the string C2 to be searched by C1. The default value is 1J. The default value is 1SQL> select instr ('oracle traning', 'A ', 1, 2) instring from dual; INSTRING --------- 96. LENGTH returns the length of the string. SQL> select name, length (name), addr, length (addr), sal, LENGTH (to_char (sal) from gao. nchar_tst; name length (NA ME) addr length (ADDR) sal length (TO_CHAR (SAL) ------ ------------ ------------------ ------------- -------------------- Gao Qian Jing 3 Beijing sea ingot Area 6 9999.99 7 www.2cto.com 7. LOWER returns a string and converts all characters to lowercase SQL> select lower ('aabbccdd') AaBbCcDd from dual; AaBbCcDd -------- AABBCCDD 8. UPPER returns a string and capital all characters SQL> select upper ('aabbccdd') upper from dual; UPPER--------AABBCCDD 9. RPAD and LPAD (paste character) RPAD paste character on the right of the column LPAD paste character on the left of the column SQL> s Elect lpad (rpad ('gao', 10, '*'), 17, '*') from dual; LPAD (RPAD ('gao ', 1 ----------------- ********* gao ******* if the character is not enough, use * to fill in www.2cto.com 10. LTRIM and RTRIMLTRIM Delete the string on the left RTRIM Delete the string on the right SQL> select ltrim (rtrim ('gao qian jing', ''),'') from dual; LTRIM (RTRIM ('----------- gao qian jing 11. SUBSTR (string, start, count) Take the sub-string, start from start, take count SQL> select substr ('123456', 13088888888) from dual; SUBSTR ('-------- 08888888 12.REP Substring ('string', 's1', 's2 ') string the character to be replaced or the string s2 variable s1 to be replaced SQL> select replace ('He love you', 'hes', 'I') from dual; REPLACE ('H ---------- I love you 13. SOUNDEX returns a string with the same pronunciation as the given string. SQL> create table table1 (xm varchar (8); SQL> insert into table1 values ('weate '); SQL> insert into table1 values ('wether '); SQL> insert into table1 values ('gao'); SQL> select xm from table1 where soundex (xm) = soundex ('weate' ); XM -------- weatherwether 14. TRIM ('S' from 'string') LEADING: TRIM the character TRAILING. If not specified, the default space character is 15. ABS returns the absolute value of the specified value. SQL> select abs (100), abs (-100) from dual; ABS (100) ABS (-100) --------- ------- 100 100 www.2cto.com 16. SQL> select ACOS (-1) from dual; acos (-1) --------- 3.1415927 17. ASIN returns the arc sine value SQL> select asin (0.5) from dual; ASIN (0.5 )---------. 52359878 18. ATAN returns the arc tangent of a number. SQL> select atan (1) from Dual; ATAN (1 )---------. 78539816 19. CEIL returns the smallest integer greater than or equal to the given number. SQL> select ceil (3.1415927) from dual; CEIL (3.1415927) ------------- 4 www.2cto.com 20. COS returns the cosine of a given number. SQL> select cos (-3.1415927) from dual; COS (-3.1415927) --------------121. COSH returns an SQL> select cosh (20) from dual; COSH (20) --------- 242582598 22. EXP returns the n root SQL of the number e> select exp (2), exp (1) from dual; EXP (2) EXP (1) --------- 7.3890561 2.7182818 23. FLOOR takes the integer SQL> select floor (2345.67) from dual; FLOOR (2345.67) ------------ 2345 www.2cto.com 24 for the given number. LN returns the logarithm of a number SQL> select ln (1), ln (2), ln (2.7182818) from dual; LN (1) LN (2) LN (2.7182818) --------- ------------- 0. 69314718. 99999999 25.LOG( n1, n2) returns a logarithm of n2 with n1 as the base SQL> select log (), log () from dual; LOG) --------- 0 2 26.MOD( n1, n2) returns a remainder of n1 divided by n2 SQL> se Lect mod (), mod (), mod () from dual; MOD () mod () ----------- --------- 1 0 2 www.2cto.com 27. POWER returns the n2 power of n1 to the root SQL> select power (1024), POWER () from dual; POWER () ----------- ---------- 27 28. ROUND and TRUNC round SQL> select round (55.5), ROUND (-55.4), trunc (55.5), trunc (-55.5) from dual; ROUND (55.5) ROUND (-55.4) TRUNC (55.5) TRUNC (-55.5 )------------------ ----- ----------- ------------ 56-55 55-55 29. SIGN: returns the number n. If it is greater than 0, 1 is returned. If it is less than 0,-1 is returned. If it is equal to 0, 0 SQL> select sign (123), sign (-100), sign (0) is returned) from dual; SIGN (123) SIGN (-100) SIGN (0) --------- ---------- --------- 1-1 0 30. SIN returns the sine of a number. SQL> select sin (1.57079) from dual; SIN (1.57079) www.2cto.com ------------ 131. SIGH returns the hyperbolic sine value SQL> select sin (20), sinh (20) from dual; SIN (20) SINH (20 )------------------. 91294525 242582598 3 2. SQRT returns the root SQL> select sqrt (64), sqrt (10) from dual; SQRT (64) SQRT (10) --------- 8 3.1622777 33. TAN returns the tangent of the number SQL> select tan (20), tan (10) from dual; TAN (20) TAN (10) --------- www.2cto.com 2.2371609. 64836083 34. TANH returns the hyperbolic tangent of number n SQL> select tanh (20), tan (20) from dual; TANH (20) TAN (20) --------- 1 2.2371609 35. TRUNC intercepts a number of SQL> select trunc (124.1666,-2) trunc1, trunc (124.16 666, 2) from dual; TRUNC1 TRUNC (124.16666, 2) --------- ------------------ 100 124.16 36. ADD_MONTHS add or subtract the month SQL> select to_char (add_months (to_date ('20170101', 'yyyymm'), 2), 'yyyymm') from dual; TO_CHA www.2cto.com ------ 200002SQL> select to_char (add_months (to_date ('201312', 'yyymmm'),-2), 'yyymmm') from dual; TO_CHA------199910 37. LAST_DAY returns the last day of the date SQL> select to_char (sysdate, 'yyyy. mm. dd'), to_char (sysdate) + 1 ,' Yyyy. mm. dd') from dual; TO_CHAR (SY TO_CHAR (S ---------- 2004.05.09 2004.05.10SQL> select last_day (sysdate) from dual; LAST_DAY (May-04 www.2cto.com 38. MONTHS_BETWEEN (date2, date1) returns the month SQL for the date2-date1> select months_between ('19-December-100', '19-March-100') mon_between from dual; MON_BETWEEN ----------- 9SQL> selectmonths_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yy Yy. mm. dd') mon_betw from dual; MON_BETW ----------60 39. NEW_TIME (date, 'this', 'that ') returns the date and time SQL> select to_char (sysdate, 'yyyy. mm. dd hh24: mi: ss') bj_time, to_char (new_time 2 (sysdate, 'pdt ', 'gmt'), 'yyyy. mm. dd hh24: mi: ss') los_angles from dual; BJ_TIME LOS_ANGLES ------------------- --------------------- 2004.05.09 11:05:32 2004.05.09 18:05:32 40. NEXT_DAY (date, 'day') calculates the date of the next week after the date and week x. SQL> select next_day ('18-May-2001 ', 'Friday') next_day from dual; next_day--------25- May-01 www.2cto.com 41. SYSDATE is used to obtain the current system date SQL> select to_char (sysdate, 'dd-mm-yyyy day ') from dual; TO_CHAR (SYSDATE,' --------------- 09-05-2004 Sunday trunc (date, fmt) trunc (sysdate, 'hh '), 'yyyy. mm. dd hh24: mi: ss') hh, 2 to_char (trunc (sysdate, 'mi'), 'yyyy. mm. dd hh24: mi: ss') hhm M from dual; hh hhmm ------------------- 2004.05.09 11:00:00 2004.05.09 11:17:00 42. CHARTOROWID converts the character data type to the ROWID type. SQL> select rowid, rowidtochar (rowid), ename from scott. emp; rowid rowidtochar (ROWID) ENAME ------------------ ---------- aaaafkaacaaeqaaa AAAAfKAACAAAAEqAAA aaaaaaaaaaaaafka ACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES www.2cto.com 43. CONVERT (c, dset, sset) converts the source string sset from one language character set to another dset Character Set SQL> select convert ('strutz', 'we8hp ', 'f7dec ') "conversion" from dual; conver ------ strutz 44. HEXTORAW converts a hex string to binary 45. RAWTOHEXT converts a binary string to hexadecimal 46. ROWIDTOCHAR converts the ROWID data type to the character type 47. TO_CHAR (date, 'format') SQL> select to_char (sysdate, 'yyyy/mm/dd hh24: mi: ss') from dual; TO_CHAR (SYSDATE, 'yy ---- --------------- 21:14:41 48. TO_DATE (string, 'format') converts a string to a date 49 in ORACLE. TO_MULTI_BYTE converts a single-byte character in a string TO a multi-byte character. SQL> select to_multi_byte ('high') from dual; TO www.2cto.com -- 50 high. TO_NUMBER converts the given character to a numeric SQL> select to_number ('000000') year from dual; YEAR --------- 1999 51. BFILENAME (dir, file) specifies an external binary file SQL> insert into file_tb1 values(bfilename('lob_dir1', 'image1.gif '); 52. CONVERT ('x', 'desc', 'source') will Convert the source of the x field or variable to descSQL> select sid, serial #, username, decode (command, 2 0, 'none', www.2cto.com 3 2, 'insert ', 4 3, 5 'select', 6 6, 'update', 7 7, 'delete', 8 8, 'drop', 9 'other ') cmd from v $ session where type! = 'Background '; sid serial # username cmd --------- ------------------------------ 1 1 none 2 1 none 3 1 none 4 1 none 5 1 none 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO none 53. DUMP (s, fmt, start, length) the DUMP function returns a VARCHAR2 value SQL> col global_name for a30 www.2cto.com SQL> col dump_string for a50SQL> set lin 200SQL> select global_name, dump (global_name, 1017,8, 5) dump_string from global_name; GLOBAL_NAME DUMP_STRING ------------------------------ ------------------------------------------------ ORACLE. WORLD Typ = 1 Len = 12 CharacterSet = ZHS16GBK: W, O, R, L, D 54. EMPTY_BLOB () and EMPTY_CLOB () are both functions used to initialize fields of the big data type. GREATEST returns the maximum value in a group of expressions, that is, compare the character encoding size. SQL> select greatest ('A', 'AB', 'ac') from dual; GR--ACSQL> select greatest ('Ah', 'an', 'day') from dual; GR www.2cto.com -- Day 56. LEAST returns the minimum value in a group of expressions. SQL> select least ('Ah', 'an', 'day') from dual; LE -- Ah 57. UID returns the unique integer that identifies the current user. SQL> show userUSER is "GAO" SQL> select username, user_id from dba_users where user_id = uid; USERNAME USER_ID -------------------------- --------- GAO 25 58. USER Returns the name of the current user SQL> select USER from dual; USER------------------------------GAO www.2cto.com 59. USEREVN returns the information of the current user environment. opt can be: ENTRYID, SESSIONID, TERMINAL, ISDBA, LAB LE, LANGUAGE, CLIENT_INFO, LANG, VSIZEISDBA check whether the current user is a DBA. If yes, trueSQL> select userenv ('isdba ') from dual; USEREN------FALSESQL> select userenv ('isdba') is returned ') from dual; The USEREN------TRUESESSION returns the session flag SQL> select userenv ('sessionid') from dual; USERENV ('sessionid ') -------------------- 152 ENTRYID www.2cto.com returns the session population flag SQL> select userenv ('entryid') from dual; USERENV ('entryid') ---------------- 0INSTANCE returns the current INSTANC E Mark SQL> select userenv ('instance') from dual; USERENV ('instance') ----------------- 1LANGUAGE returns the current environment variable SQL> select userenv ('language') from dual; USERENV ('language') returns the abbreviation SQL> select userenv ('lang ') from dual; USERENV ('lang ') ---------------------------------------------------- ZHSTERMINAL returns the user's terminal or machine flag SQL> se Lect userenv ('terminal') from dual; USERENV ('termina ---------------- GAOVSIZE (X) returns the size (bytes) of x SQL> select vsize (user), user from dual; VSIZE (USER) USER ----------- ------------------------------ 6 SYSTEM www.2cto.com 60.AVG( DISTINCT | ALL) all indicates the average value of ALL values, distinct calculates the average SQLWKS> create table table3 (xm varchar (8), sal number () for different values only. The statement has been processed. SQLWKS> insert into table3 values ('gao', 1111.11); SQLWKS> insert into table3 values ('gao', 1111.11); SQLWKS> insert into table3 values ('zhu ', 5555.55); SQLWKS> commit; SQL> select avg (distinct sal) from gao. table3; AVG (DISTINCTSAL) ---------------- 3333.33SQL> select avg (all sal) from gao. table3; AVG (ALLSAL) ----------- 2592.59 61.MAX( DISTINCT | ALL) returns the maximum value, ALL represents the maximum value for ALL values, and DISTINCT represents the maximum value for different values, select max (distinct sal) from scott. emp; MAX (DISTINCTSAL) ---------------- 5000 www.2cto.com 62.MIN( DISTINCT | ALL) for the minimum value, ALL for ALL values for the minimum value, DISTINCT for different values for the minimum value, take the same SQL statement only once> select min (all sal) from gao. table3; MIN (ALLSAL) ----------- 1111.11 63. STDDEV (distinct | all) Evaluate the standard deviation, ALL represents evaluate the standard deviation for all values, and DISTINCT represents evaluate the standard deviation for only different values SQL> select stddev (sal) from scott. emp; STDDEV (SAL) ----------- 1182.5032SQL> select stddev (distinct sal) from scott. emp; STDDEV (DISTINCTSAL) ------------------- 1229.951 www.2cto.com 64. VARIANCE (DISTINCT | ALL) returns the covariance SQL> select variance (sal) from scott. emp; VARIANCE (SAL) ------------- 1398313.9 65. group by is mainly used to calculate the number of a group. SQL> select deptno, count (*), sum (sal) from scott. emp group by deptno; deptno count (*) SUM (SAL) --------- 10 3 8750 20 5 10875 30 6 9400 66. HAVING grouping statistics plus restrictions SQL> select deptno, count (*), sum (sal) from scott. emp group by deptno having count (*)> = 5; deptno count (*) SUM (SAL) --------- 20 5 10875 30 6 9400SQL> select deptno, count (*), sum (sal) from scott. emp having count (*)> = 5 group by deptno; deptno count (*) SUM (SAL) --------- 20 5 10875 30 6 9400 www.2cto.com 67. order by is used to sort the query results and output SQL> select deptno, ename, sal from scott. emp order by deptno, sal desc; deptno ename sal --------- ---------- --------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 SMITH 800 30 blke 2850 30 ALLEN 1600 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250 30 JAMES 950