Oracle Common Function Summary

. Sysdate

Used to get 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 requirements given, if Fmt=mi represents a retention score, truncated seconds 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 aaaafkaacaaaaeqaaa SMITH

Aaaafkaacaaaaeqaab Aaaafkaacaaaaeqaab ALLEN

AAAAFKAACAAAAEQAAC AAAAFKAACAAAAEQAAC WARD

Aaaafkaacaaaaeqaad Aaaafkaacaaaaeqaad JONES

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 hexadecimal-formed string into a binary

45.RAWTOHEXT

Converts a binary-formed string to hexadecimal

46.ROWIDTOCHAR

Converting a ROWID data type to a 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 multibyte character

Sql> Select To_multi_byte (High) from dual;

To

--

High

50.to_number

Converts the given character to a number

Sql> Select To_number (1999) 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)

Converts the X field or the source of a variable to desc

sql> Select Sid,serial#,username,decode (Command, 2 0,none, 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# Usernam

E 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 GAO Select

GAO None

53.DUMP (S,fmt,start,length)

The DUMP function returns the value of a VARCHAR2 type in the internal number format specified by FMT

Sql> Col global_name for A30

Sql> Col dump_string for A50

Sql> 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 functions that are used to initialize large data type fields

55.GREATEST returns the maximum value in a set of expressions that compares the encoded size of a character. Sql> Select Greatest (AA,AB,AC) from dual;

Gr

--

AC

Sql> Select Greatest (Ah, Ann, Tian) from dual;

Gr

--

Days

56.LEAST

Returns the minimum value in a set of expressions

Sql> Select least (Ah, Ann, Tian) from dual;

LE

--

Ah

57.UID

Returns a unique integer that identifies the current user

Sql> Show User

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 return information about the current user environment, opt can be: entryid,sessionid,terminal,isdba,lable,language,client_info,lang,vsize

ISDBA to see if the current user is a DBA returns true if it is

Sql> Select Userenv (ISDBA) from dual;

Useren

------

FALSE

Sql> Select Userenv (ISDBA) from dual;

Useren

------

TRUE

Session

Return session Flag

Sql> Select Userenv (SessionID) from dual;

USERENV (SESSIONID)

--------------------

152

ENTRYID

Return to session population flag

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 for the language of the current environment

Sql> select Userenv (lang) from dual;

USERENV (LANG)

----------------------------------------------------

Zhs

TERMINAL

Returns the user's terminal or machine logo

Sql> Select Userenv (terminal) from dual;

USERENV (Termina

----------------

GAO

Vsize (X)

Returns the size of x (bytes)

Sql> Select Vsize (user), user from dual;

Vsize (user) User

----------- ------------------------------

6 SYSTEM

60.AVG (distinct| All) indicates an average value for all values, distinct only averages for different values sqlwks> CREATE TABLE Table3 (XM varchar (8), Sal number (7,2));

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