Oracle SQL built-in functions

Last Update:2018-12-05 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

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. hextorawConverts a hexadecimal string to a binary string.

45. rawtohextConverts a binary string to a hexadecimal string.

46. rowidtocharConvert 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_byteConverts 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