ORACLE - Oracle [to] function DaquanORACLE,function | Programing Reference For .Net,Java,C

Oracle [to] function Daquan

SQL function in the records
1.ASCII
Back to the designated characters corresponding decimal number;
SQL> select ascii ( 'A') A, ascii ( 'a') a, ascii ('0 ') zero, ascii (' ') space from dual;

A A ZERO SPACE
——— ——— ——— ———
65 97 48 32

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

ZH C
— –
Zhao A

3.CONCAT
Connecting two strings;
SQL> select concat ('010-','88888888')||' to 23' senior cadres competing telephone from dual;

Senior cadres competing telephone
—————-
010-88888888 to 23

4.INITCAP
Back to the string and the string into the first letter of capital;
SQL> select initcap ( 'smith') upp from dual;

UPP
—–
Smith

5.INSTR (C1, C2, I, J)
In the search for a specified string of characters, returning to find the location of a specific character;
C1 in the search string
C2 hope that the search string
I search the start position, which defaults to 1
J emerging position as a default
SQL> select instr ( 'oracle traning', 'ra', 1,2) instring from dual;

INSTRING
———–
9

6.LENGTH
Back to 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))
—— ———— —————- ———— —- —– ——————–
Jing-3 senior cadres in Beijing ingot District 6 9999.99 7

7.LOWER
Back to the string, and all lowercase characters
SQL> select lower ( 'AaBbCcDd') AaBbCcDd from dual;

AABBCCDD
——–
Aabbccdd

8.UPPER
Back to the string, and all of the characters in capital
SQL> select upper ( 'AaBbCcDd') upper from dual;

UPPER
——–
AABBCCDD

9.RPAD and LPAD (paste characters)
RPAD out in the right paste characters
LPAD out in the left side of paste characters
SQL> select lpad (rpad ( 'gao', 10 ,'*'), 17 ,'*') from dual;

LPAD (RPAD ( 'GAO', 1
—————–
******* ******* Gao
* Character is not used to fill

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

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

11.SUBSTR (string, start, count)
Admission of string from the start, take a count
SQL> select substr ('13088888888 ', 3, from dual;

SUBSTR ( '
——–
08888888

12.REPLACE ( 'string', 's1', 's2')
Hope that the string of characters or be replaced variables
S1 be replaced string
S2 to replace the string
SQL> select replace ( 'he love you', 'he', 'i') from dual;

REPLACE ( 'H
———-
I love you

13.SOUNDEX
Return a string with the pronunciation given the same string
SQL> create table table1 (xm varchar (8));
SQL> insert into table1 values ( 'weather');
SQL> insert into table1 values ( 'wether');
SQL> insert into table1 values ( 'gao');

SQL> select xm from table1 where soundex (xm) = soundex ( 'weather');

XM
——–
Weather
Wether

14.TRIM ( 's' from the' string ')
LEADING cut in front of the characters
TRAILING cut behind the characters If you do not specify, the default for space at

15.ABS
Back to the designated value of the absolute value
SQL> select abs (100), abs (-100) from dual;

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

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

ACOS (-1)
———–
3.1415927

17.ASIN
Anyway, given the value of String
SQL> select asin (0.5) from dual;

ASIN (0.5)
———–
.52359878

18.ATAN
Return a numeric value of the cut anyway
SQL> select atan (1) from dual;

ATAN (1)
———–
.78539816

19.CEIL
Return is greater than or equal to the smallest integer numbers
SQL> select ceil (3.1415927) from dual;

CEIL (3.1415927)
—————
4

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

COS (-3.1415927)
—————
-1

21.COSH
Return a numeric value inverse cosine
SQL> select cosh (20) from dual;

COSH (20)
———–
242582598

22.EXP
Back to a number of the n-th e-kan
SQL> select exp (2), exp (1) from dual;

EXP (2) EXP (1)
——— ———
7.3890561 2.7182818

23.FLOOR
On the figures to be rounded
SQL> select floor (2345.67) from dual;

FLOOR (2345.67)
————–
2345

24.LN
Back to the figures on a numerical
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)
N1 for a return at the end of the logarithm of n2
SQL> select log (2,1), log (2,4) from dual;

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

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

MOD (10,3) MOD (3,3) MOD (2,3)
——— ——— ———
1 0 2

27.POWER
Back to the n2-th roots of n1
SQL> select power (2,10), power (3,3) from dual;

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

28.ROUND and TRUNC
On the prescribed accuracy rounding
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
N figures from the symbol, a return of more than 0 and less than the return -1, equivalent to zero return 0
SQL> select sign (123), sign (-100), sign (0) from dual;

SIGN (123) SIGN (-100) SIGN (0)
——— ———- ———
1 -1 0

30.SIN
Return a numeric value of the sinusoidal
SQL> select sin (1.57079) from dual;

SIN (1.57079)
————
1

31.SIGH
Back to the value of sinh
SQL> select sin (20), sinh (20) from dual;

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

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

SQRT (64) SQRT (10)
——— ———
8 3.1622777

33.TAN
Back to the tangent of figures
SQL> select tan (20), tan (10) from dual;

TAN (20) TAN (10)
——— ———
2.2371609 .64836083

34.TANH
Back to the number n of the hyperbolic
SQL> select tanh (20), tan (20) from dual;

TANH (20) TAN (20)
——— ———
1 2.2371609

35.TRUNC
According to the accuracy of interception of a specified number of
SQL> select trunc (124.1666, -2) trunc1, trunc (124.16666,2) from dual;

TRUNC1 TRUNC (124.16666,2)
——— ——————
100124.16

36.ADD_MONTHS
Increase or less in
SQL> select to_char (add_months (to_date ('199912 ',' yyyymm '), 2),' yyyymm ') from dual;

TO_CHA
——
200002
SQL> select to_char (add_months (to_date ('199912 ',' yyyymm '), -2),' yyyymm ') from dual;

TO_CHA
——
199910

37.LAST_DAY
Back to the date of the last day
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
———-
31-5, -04

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

MON_BETWEEN
———–
9
SQL> selectmonths_between (to_date ('2000 .05.20 ',' yyyy.mm.dd '), to_date (thoroughly earned .05.20', 'yyyy.mm.dd')) mon_betw from dual;

MON_BETW
———–
-60

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

NEXT_DAY
———-
25-5, -01

41.SYSDATE
The current system used to be the date
SQL> select to_char (sysdate, 'dd-mm-yyyy day') from dual;

TO_CHAR (SYSDATE, '
—————–
Sunday 09-05-2004
Trunc (date, fmt) is given in accordance with the cut-off date of the request, if fmt = 'mi' expressed reservations points, second cut
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
Character data types will be converted to ROWID type
SQL> select rowid, rowidtochar (rowid), ename from scott.emp;

ROWID ROWIDTOCHAR (ROWID) ENAME
—————— —————— ———-
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

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

Conver
——
Strutz

44.HEXTORAW
Will be a hexadecimal string into a binary

45.RAWTOHEXT
Consisting of a binary will be converted to hexadecimal string

46.ROWIDTOCHAR
ROWID type of data will be converted to type characters

47.TO_CHAR (date, the 'format')
SQL> select to_char (sysdate, 'yyyy / mm / dd hh24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'YY
——————-
Gong 21:14:41

48.TO_DATE (string, the 'format')
ORACLE string into a date in the

49.TO_MULTI_BYTE
String of single-byte characters into a multi-byte character
SQL> select to_multi_byte ( 'high') from dual;

TO
—
High

50.TO_NUMBER
The characters will be given to digital conversion
SQL> select to_number ('1999 ') year from dual;

YEAR
———–
1999

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

52.CONVERT ( 'x' and 'desc', 'source')
X will be the source field or variable source converted to desc
SQL> select sid, serial #, username, decode (command,
2 0, 'none',
2, 'insert'
4 3,
5 'select',
6 6, 'update',
7, the 'delete',
8 8, the '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
GAO select 9 20
GAO none 10 40

53.DUMP (s, fmt, start, length)
DUMP fmt designated function to the internal digital format returns a value type VARCHAR2
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> 200 set lin
SQL> select global_name, dump (global_name, 1017,8,5) dump_string from global_name;

GLOBAL_NAME DUMP_STRING
—————————— ——————– ——————————
ORACLE.WORLD Typ Len = 12 = 1 = CharacterSet ZHS16GBK: W, O, R, L, D

54.EMPTY_BLOB () and EMPTY_CLOB ()
These two functions are used for large data type field operations function to initialize

55.GREATEST
Back to a group of the maximum expression of that size compared character encoding.
SQL> select greatest ( 'AA', 'AB', 'AC') from dual;

GR
—
AC
SQL> select greatest ( 'ah', 'the', 'days') from dual;

GR
—
Days

56.LEAST
Back to a group of expression in the minimum
SQL> select least ( 'ah', 'the', 'days') from dual;

LE
—
Ah

57.UID
Logo to return to the only current users Integer
SQL> show user
USER "GAO"
SQL> select username, user_id from dba_users where user_id = uid;

USERNAME USER_ID
—————————— ———
GAO 25

58.USER
Back to the current user's name
SQL> select user from dual;

USER
——————————
GAO

59.USEREVN
Back to the current user of the information environment, opt can be:
ENTRYID, SESSIONID, TERMINAL, ISDBA, LABLE, LANGUAGE, CLIENT_INFO, LANG, VSIZE
ISDBA Show whether the current user is returned to the DBA If it is true,
SQL> select userenv ( 'isdba') from dual;

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

USEREN
——
TRUE
SESSION
Back to the session signs
SQL> select userenv ( 'sessionid') from dual;

USERENV ( 'SESSIONID')
——————–
152
ENTRYID
Returned to the conversation population signs
SQL> select userenv ( 'entryid') from dual;

USERENV ( 'ENTRYID')
——————
0
INSTANCE
Back to the current signs INSTANCE
SQL> select userenv ( 'instance') from dual;

USERENV ( 'INSTANCE')
——————-
1
LANGUAGE
Back to the current environment variables
SQL> select userenv ( 'language') from dual;

USERENV ( 'LANGUAGE')
————————————————– –
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
Back to the current environment of the language acronym
SQL> select userenv ( 'lang') from dual;

USERENV ( 'LANG')
————————————————– –
ZHS
TERMINAL
Back to the user terminal or machine signs
SQL> select userenv ( 'terminal') from dual;

USERENV ( 'TERMINA
—————-
GAO
VSIZE (X)
Back to X's size (bytes) of
SQL> select vsize (user), user from dual;

VSIZE (USER) USER
———– ——————————
6 SYSTEM

60.AVG (DISTINCT | ALL)
All said that the value of all for average, only distinct values for the different average
SQLWKS> create table table3 (xm varchar (8), sal number (7,2));
Statements have 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 the maximum, ALL said that the value of all for maximum DISTINCT said that the different values for the maximum, only from the same time
SQL> select max (distinct sal) from scott.emp;

MAX (DISTINCTSAL)
—————-
5000

62.MIN (DISTINCT | ALL)
For the minimum, ALL said that the value of all for the minimum, DISTINCT expressed different values for the minimum, only from the same time
SQL> select min (all sal) from gao.table3;

MIN (ALLSAL)
———–
1111.11

63.STDDEV (distinct | all)
For standard deviation, ALL said that the value for all standard deviation, DISTINCT said only different values for standard deviation
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)
For covariance

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

VARIANCE (SAL)
————-
1398313.9

65.GROUP BY
Used primarily to a group of a few statistics
SQL> select deptno, count (*), the sum (sal) from scott.emp group by deptno;

DEPTNO COUNT (*) SUM (SAL)
——— ——— ———
10 3 8750
20 5 10875
30 6 9400

66.HAVING
Statistics coupled with restrictions on the group conditions
SQL> select deptno, count (*), the 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 (*), the sum (sal) from scott.emp having count (*)>= group by deptno 5;

DEPTNO COUNT (*) SUM (SAL)
——— ——— ———
20 5 10875
30 6 9400

67.ORDER BY
For the results of the enquiry to sort 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 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950