ORACLE development manual

Here we have added a list of oracle functions that are helpful to ORACLE beginners. If you need them, you can check them out.

Single-record functions in SQL
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

2. CHR
Returns the corresponding characters;
SQL> select chr (54740) zhao, chr (65) chr65 from dual;

ZH C
---
Zhao

3. CONCAT
Connect two strings;
SQL> select concat ('010-', '000000') |' to 23' Gao Qian competing phone number from dual;

Gao Qian's phone number
----------------
010-88888888 to 23

4. INITCAP
Returns a string and converts the first letter of the string to uppercase;
SQL> select initcap ('Smith ') upp from dual;

UPP
-----
Smith

5. INSTR (C1, C2, I, J)
Searches for a specified character in a string and returns the location where the specified character is found;
String searched by C1
String to be searched by C2
The start position of the I search. The default value is 1.
Where J appears. The default value is 1.
SQL> select instr ('oracle traning', 'A', 1, 2) instring from dual;

INSTRING
---------
9

6. 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 (NAME) addr length (ADDR) sal length (TO_CHAR (SAL ))
---------------------------------------------------------------------------
Gao Qian Jing 3 Beijing haiding District 6 9999.99 7

7. LOWER
Returns a string and lowercase letters of all characters.
SQL> select lower ('abbccdd') AaBbCcDd from dual;

AABBCCDD
--------
Aabbccdd

8. UPPER
Returns a string and upper-case all characters.
SQL> select upper ('abbccdd') upper from dual;

UPPER
--------
AABBCCDD

9. RPAD and LPAD (paste characters)
Pad the character on the right of the column
LPAD paste characters on the left of the column
SQL> select lpad (rpad ('gao', 10, '*'), 17, '*') from dual;

LPAD (RPAD ('gao', 1
-----------------
* ******* Gao *******
If the character is not enough, use * to fill it up.

10. LTRIM and RTRIM
LTRIM deletes the string on the left
RTRIM deletes the string that appears on the right.
SQL> select ltrim (rtrim ('gao qian jing', ''),'') from dual;

LTRIM (RTRIM ('
-------------
Gao qian jing

11. SUBSTR (string, start, count)
Substring, starting from start, count
SQL> select substr ('123', 13088888888) from dual;

SUBSTR ('
--------
08888888

12. REPLACE ('string', 's1', 's2 ')
String: the character or variable to be replaced.
String to be replaced by s1
String to be replaced by s2
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 a 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
--------
Weather
Wether

14. TRIM ('s 'from 'string ')
LEADING
TRAILING
If this parameter is not specified, the space character is used by default.

15. ABS
Returns the absolute value of a specified value.
SQL> select abs (100), abs (-100) from dual;

ABS (100) ABS (-100)
------------------
100 100

16. ACOS
Returns the arc cosine value.
SQL> select acos (-1) from dual;

ACOS (-1)
---------
3.1415927

17. ASIN
Returns the arcsin value.
SQL & gt; 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

20. COS
Returns the cosine of a given number.
SQL> select cos (-3.1415927) from dual;

COS (-1, 3.1415927)
---------------
-1

21. COSH
Returns the arc cosine of a number.
SQL> select cosh (20) from dual;

COSH (20)
---------
242582598

22. EXP
Returns the n root of the number e.
SQL> select exp (2), exp (1) from dual;

EXP (2) EXP (1)
------------------
7.3890561 2.7182818

23. FLOOR
Returns an integer to a given number.
SQL> select floor (2345.67) from dual;

FLOOR (2345.67)
--------------
2345

24. LN
Returns the logarithm of a number.
SQL> select ln (1), ln (2), ln (2.7182818) from dual;

LN (1) LN (2) LN (1, 2.7182818)
-------------------------------
0. 69314718. 99999999

25. LOG (n1, n2)
Returns the base n2 logarithm of n1.
SQL> select log (2, 1), log (2, 4) from dual;

LOG (2, 1) LOG (2, 4)
------------------
0 2

26. MOD (n1, n2)
Returns the remainder of n1 divided by n2.
SQL> select mod (10, 3), mod (3, 3), mod (2, 3) from dual;

MOD)
---------------------------
1 0 2

27. POWER
Returns the n2 root of n1.
SQL> select power (2, 10), power (3, 3) from dual;

POWER (2, 10) POWER (3, 3)
---------------------
1024 27

28. ROUND and TRUNC
Round according to the specified precision
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 1 if the number n is greater than 0,-1 if the value is less than 0, and 0 if the value is 0.
SQL> select sign (123), sign (-100), sign (0) 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)
------------
1

31. SIGH
Returns the hyperbolic sine value.
SQL> select sin (20), sinh (20) from dual;

SIN (20) SINH (20)
------------------
.. 91294525 242582598

32. SQRT
Returns the root of number n.
SQL> select sqrt (64), sqrt (10) from dual;

SQRT (64) SQRT (10)
------------------
8 3.1622777

33. TAN
Returns the tangent of a number.
SQL> select tan (20), tan (10) from dual;

TAN (20) TAN (10)
------------------
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
Truncate a number based on the specified precision
SQL> select trunc (124.1666,-2) trunc1, trunc (124.16666, 2) from dual;

TRUNC1 TRUNC (124.16666, 2)
---------------------------
100 124.16

36. ADD_MONTHS
Add or subtract a month
SQL> select to_char (add_months (to_date ('000000', 'yyymmm'), 2), 'yyymmm') from dual;

TO_CHA
------
200002
SQL> select to_char (add_months (to_date ('000000', '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.10
SQL> select last_day (sysdate) from dual;

LAST_DAY (S
----------
September 31-04

38. MONTHS_BETWEEN (date2, date1)
Given the month of the date2-date1
SQL> select months_between ('19-December-1999 ', '19-March-1999') mon_between from dual;

MON_BETWEEN
-----------
9
SQL> selectmonths_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. mm. dd') mon_betw from dual;

MON_BETW
---------
-60

39. NEW_TIME (date, 'this', 'that ')
Returns the date and time in this time zone = other time zone.
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 ')
Returns the date of the week and the date of the next week after week x.
SQL> select next_day ('18-May-2001 ', 'Friday') next_day from dual;

NEXT_DAY
----------
25-5-01

41. SYSDATE
Used to obtain the current date of the system
SQL> select to_char (sysdate, 'dd-mm-yyyy Day') from dual;

TO_CHAR (SYSDATE ,'
-----------------
09-05-2004 Sunday
Trunc (date, fmt) truncates the date according to the given requirements. If fmt = 'mi ', it indicates that the minute is retained and the second is truncated.
SQL> select to_char (trunc (sysdate, 'hh'), 'yyyy. mm. dd hh24: mi: ss') hh,
2 to_char (trunc (sysdate, 'mi'), 'yyyy. mm. dd hh24: mi: ss') hhmm from dual;

HH HHMM
--------------------------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

42. CHARTOROWID
Convert character data type to ROWID type
SQL> select rowid, rowidtochar (rowid), ename from scott. emp;

Rowid rowidtochar (ROWID) ENAME
----------------------------------------------
AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD JONES

43. CONVERT (c, dset, sset)
Convert the source string sset from one language character set to another destination dset Character Set
SQL> select convert ('strutz', 'we8hp ', 'f7dec') "conversion" from dual;

Conver
------
Strutz

44. HEXTORAW
Converts a hexadecimal string to a binary string.

45. RAWTOHEXT
Converts a binary string to a hexadecimal string.

46. ROWIDTOCHAR
Convert 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
-------------------
2004/05/09 21:14:41

48. TO_DATE (string, 'format ')
Converts a string to a date in ORACLE.

49. 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
--
High

50. TO_NUMBER
Converts a given character to a number.
SQL> select to_number ('20140901') year from dual;

YEAR
---------
1999

51. BFILENAME (dir, file)
Specify an external binary file
SQL> insert into file_tb1 values(bfilename('lob_dir1', 'image1.gif '));

52. CONVERT ('x', 'desc', 'source ')
Convert the source of the x field or variable to desc
SQL> select sid, serial #, username, decode (command,
2 0, 'none ',
3 2, 'insert ',
4 3,
5 'select ',
6 6, 'update ',
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 in the internal numeric format specified by fmt.
SQL> col global_name for a30
SQL> col dump_string for a50
SQL & gt; set lin 200
SQL> 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 ()
Both functions are used to initialize fields of the big data type.

55. GREATEST
Returns the maximum value in a group of expressions, that is, compare the encoding size of characters.
SQL> select greatest ('A', 'AB', 'ac') from dual;

GR
--
AC
SQL> select greatest ('Ah', 'an', 'day') from dual;

GR
--
Days

56. LEAST
Returns the minimum value in a group of expressions.
SQL> select least ('Ah', 'an', 'day') from dual;

LE
--
Ah

57. UID
Returns a unique integer that identifies the current user.
SQL> show user
The USER 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

59. USEREVN
Returns information about the current user environment. The opt can be:
ENTRYID, SESSIONID, TERMINAL, ISDBA, LABLE, LANGUAGE, CLIENT_INFO, LANG, VSIZE
ISDBA checks whether the current user is a DBA. If yes, true is returned.
SQL> select userenv ('isdba ') from dual;

USEREN
------
FALSE
SQL> select userenv ('isdba ') from dual;

USEREN
------
TRUE
SESSION
Returned session flag
SQL> select userenv ('sessionid') from dual;

USERENV ('sessionid ')
--------------------
152
ENTRYID
Return session Population Sign
SQL> select userenv ('entryid') from dual;

USERENV ('entryid ')
------------------
0
INSTANCE
Returns the flag of the current INSTANCE.
SQL> select userenv ('instance') from dual;

USERENV ('instance ')
-------------------
1
LANGUAGE
Returns the current environment variable.
SQL> select userenv ('language') from dual;

USERENV ('language ')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
Returns the abbreviation of the current environment language.
SQL> select userenv ('lang ') from dual;

USERENV ('lang ')
----------------------------------------------------
ZHS
TERMINAL
Returns the identifier of the user's terminal or machine.
SQL> select userenv ('terminal') from dual;

USERENV ('termina
----------------
GAO
VSIZE (X)
Returns the size (in bytes) of X.
SQL> select vsize (user), user from dual;

VSIZE (USER) USER
-----------------------------------------
6 SYSTEM

60. AVG (DISTINCT | ALL)
All indicates the average value for all values, while distinct only calculates the average value for different values.
SQLWKS> create table table3 (xm varchar (8), sal number (7,2 ));
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.33

SQL> select avg (all sal) from gao. table3;

AVG (ALLSAL)
-----------
2592.59

61. MAX (DISTINCT | ALL)
For maximum value, "ALL" indicates the maximum value for ALL values, and "DISTINCT" indicates the maximum value for different values. For the same value, only one time is used.
SQL> select max (distinct sal) from scott. emp;

MAX (DISTINCTSAL)
----------------
5000

62. MIN (DISTINCT | ALL)
Minimum value. "ALL" indicates minimum value for ALL values, and "DISTINCT" indicates minimum value for different values. The same value is used only once.
SQL> select min (all sal) from gao. table3;

MIN (ALLSAL)
-----------
1111.11

63. STDDEV (distinct | all)
Evaluate standard deviation. "ALL" indicates evaluate standard deviation for ALL values, and "DISTINCT" indicates evaluate standard deviation for different values only.
SQL> select stddev (sal) from scott. emp;

STDDEV (SAL)
-----------
1182.5032

SQL> select stddev (distinct sal) from scott. emp;

STDDEV (DISTINCTSAL)
-------------------
1229.951

64. VARIANCE (DISTINCT | ALL)
Covariance

SQL> select variance (sal) from scott. emp;

VARIANCE (SAL)
-------------
1398313.9

65. GROUP
It 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 9400
SQL> select deptno, count (*), sum (sal) from scott. emp having count (*)> = 5 group by deptno;

Deptno count (*) SUM (SAL)
---------------------------
20 5 10875
30 6 9400

67. ORDER
Used to sort and output the queried results.
SQL> select deptno, ename, sal from scott. emp order by deptno, sal desc;

DEPTNO ENAME SAL