SQL Functions

1172 Pages·1996·7.23 MB·English

Oraclei
SQL Reference
Release 3 (8.1.7)
September 2000
Part No. A85397-01
SQL Reference, Release 3 (8.1.7)
Part No. A85397-01
Copyright © 1996, 2000, Oracle Corporation. All rights reserved.
Primary Author: Diana Lorentz
Contributors: Dave Alpern, Vikas Arora, Lance Ashdown, Hermann Baer, Vladimir Barriere, Lucy
Burgess, Souripriya Das, Carolyn Gray, John Haydu, Thuvan Hoang, Wei Hu, Namit Jain, Hakan
Jakobsson, Bob Jenkins, Mark Johnson, Jonathan Klein, Susan Kotsovolos, Vishu Krishnamurthy,
Muralidhar Krishnaprasad, Paul Lane, Geoff Lee, Nina Lewis, Bryn Llewellyn, Phil Locke, David
McElhoes, Jack Melnick, Ari Mozes, Subramanian Muralidhar, Ravi Murthy, Sujatha Muthulingam,
Bruce Olsen, Alla S Pfauntsch, Tom Portfolio, Kevin Quinn, Ananth Raghavan, Den Raphaely, John
Russell, Anant Singh, Rajesh Sivaramasubramaniom, Roger Snowden, Jags Srinivisan, Sankar
Subramanian, Murali Thiyagarajah, Michael Tobie, AhnTuan Tran, Randy Urbano, Andy Witkowski,
Daniel Wong, Aravind Yalamanchi, Qin Yu, Fred Zemke, Mohamed Ziauddin
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................. xiii
Preface........................................................................................................................................................... xv
1 Introduction
Lexical Conventions........................................................................................................................... 1-5
2 Basic Elements of Oracle SQL
Datatypes ............................................................................................................................................. 2-2
Literals................................................................................................................................................ 2-33
Format Models.................................................................................................................................. 2-41
Nulls ................................................................................................................................................... 2-57
Pseudocolumns ................................................................................................................................ 2-59
Comments.......................................................................................................................................... 2-66
Database Objects.............................................................................................................................. 2-79
Schema Object Names and Qualifiers.......................................................................................... 2-83
Syntax for Schema Objects and Parts in SQL Statements........................................................ 2-88
3 Operators
Unary and Binary Operators............................................................................................................ 3-2
Precedence ........................................................................................................................................... 3-2
Arithmetic Operators ........................................................................................................................ 3-3
Concatenation Operator.................................................................................................................... 3-4
Comparison Operators...................................................................................................................... 3-5
iii
Logical Operators: NOT, AND, OR............................................................................................... 3-11
Set Operators: UNION [ALL], INTERSECT, MINUS ............................................................... 3-12
Other Built-In Operators................................................................................................................. 3-16
User-Defined Operators .................................................................................................................. 3-16
4 Functions
SQL Functions .................................................................................................................................... 4-2
ABS ..................................................................................................................................................... 4-14
ACOS .................................................................................................................................................. 4-14
ADD_MONTHS............................................................................................................................... 4-15
ASCII .................................................................................................................................................. 4-16
ASIN ................................................................................................................................................... 4-16
ATAN .................................................................................................................................................. 4-17
ATAN2 ................................................................................................................................................ 4-17
AVG .................................................................................................................................................... 4-18
BFILENAME ..................................................................................................................................... 4-19
BITAND ............................................................................................................................................. 4-20
CEIL .................................................................................................................................................... 4-21
CHARTOROWID ............................................................................................................................ 4-21
CHR .................................................................................................................................................... 4-22
CONCAT............................................................................................................................................ 4-23
CONVERT ......................................................................................................................................... 4-24
CORR.................................................................................................................................................. 4-25
COS ..................................................................................................................................................... 4-26
COSH.................................................................................................................................................. 4-27
COUNT .............................................................................................................................................. 4-27
COVAR_POP..................................................................................................................................... 4-29
COVAR_SAMP.................................................................................................................................. 4-31
CUME_DIST ..................................................................................................................................... 4-33
DENSE_RANK ................................................................................................................................. 4-34
DEREF ................................................................................................................................................ 4-35
DUMP................................................................................................................................................. 4-36
EMPTY_[B | C]LOB ........................................................................................................................ 4-37
EXP ...................................................................................................................................................... 4-38
FIRST_VALUE .................................................................................................................................. 4-38
iv
FLOOR ............................................................................................................................................... 4-40
GREATEST........................................................................................................................................ 4-40
GROUPING ...................................................................................................................................... 4-41
HEXTORAW ..................................................................................................................................... 4-42
INITCAP ............................................................................................................................................ 4-43
INSTR ................................................................................................................................................ 4-43
INSTRB .............................................................................................................................................. 4-44
LAG .................................................................................................................................................... 4-45
LAST_DAY ........................................................................................................................................ 4-46
LAST_VALUE ................................................................................................................................... 4-47
LEAD .................................................................................................................................................. 4-49
LEAST ................................................................................................................................................ 4-50
LENGTH ............................................................................................................................................ 4-51
LENGTHB ......................................................................................................................................... 4-51
LN........................................................................................................................................................ 4-52
LOG .................................................................................................................................................... 4-52
LOWER .............................................................................................................................................. 4-53
LPAD................................................................................................................................................... 4-53
LTRIM ................................................................................................................................................ 4-54
MAKE_REF ....................................................................................................................................... 4-55
MAX.................................................................................................................................................... 4-56
MIN .................................................................................................................................................... 4-58
MOD................................................................................................................................................... 4-59
MONTHS_BETWEEN .................................................................................................................... 4-60
NEW_TIME ....................................................................................................................................... 4-61
NEXT_DAY........................................................................................................................................ 4-62
NLS_CHARSET_DECL_LEN ........................................................................................................ 4-62
NLS_CHARSET_ID ........................................................................................................................ 4-63
NLS_CHARSET_NAME ................................................................................................................ 4-64
NLS_INITCAP.................................................................................................................................. 4-64
NLS_LOWER .................................................................................................................................... 4-65
NLSSORT .......................................................................................................................................... 4-66
NLS_UPPER ...................................................................................................................................... 4-67
NTILE ................................................................................................................................................. 4-67
NUMTODSINTERVAL................................................................................................................... 4-69
v
NUMTOYMINTERVAL.................................................................................................................. 4-70
NVL..................................................................................................................................................... 4-71
NVL2................................................................................................................................................... 4-72
PERCENT_RANK ............................................................................................................................ 4-73
POWER .............................................................................................................................................. 4-74
RANK ................................................................................................................................................. 4-74
RATIO_TO_REPORT ...................................................................................................................... 4-75
RAWTOHEX ..................................................................................................................................... 4-76
REF ...................................................................................................................................................... 4-77
REFTOHEX........................................................................................................................................ 4-78
REGR_ (linear regression) functions ........................................................................................... 4-78
REPLACE ........................................................................................................................................... 4-85
ROUND (number function) ........................................................................................................... 4-86
ROUND (date function).................................................................................................................. 4-87
ROW_NUMBER ............................................................................................................................... 4-87
ROWIDTOCHAR ............................................................................................................................ 4-89
RPAD .................................................................................................................................................. 4-89
RTRIM ................................................................................................................................................ 4-90
SIGN ................................................................................................................................................... 4-90
SIN ...................................................................................................................................................... 4-91
SINH ................................................................................................................................................... 4-91
SOUNDEX ......................................................................................................................................... 4-92
SQRT .................................................................................................................................................. 4-93
STDDEV ............................................................................................................................................ 4-93
STDDEV_POP .................................................................................................................................. 4-95
STDDEV_SAMP .............................................................................................................................. 4-96
SUBSTR ............................................................................................................................................. 4-98
SUBSTRB........................................................................................................................................... 4-99
SUM .................................................................................................................................................... 4-99
SYS_CONTEXT .............................................................................................................................. 4-101
SYS_GUID....................................................................................................................................... 4-105
SYSDATE ......................................................................................................................................... 4-106
TAN ................................................................................................................................................... 4-107
TANH ............................................................................................................................................... 4-107
TO_CHAR (date conversion) ....................................................................................................... 4-108
vi
TO_CHAR (number conversion) ................................................................................................ 4-109
TO_DATE ........................................................................................................................................ 4-110
TO_LOB ........................................................................................................................................... 4-111
TO_MULTI_BYTE ......................................................................................................................... 4-112
TO_NUMBER ................................................................................................................................. 4-112
TO_SINGLE_BYTE ....................................................................................................................... 4-113
TRANSLATE................................................................................................................................... 4-113
TRANSLATE ... USING ................................................................................................................ 4-114
TRIM ................................................................................................................................................ 4-116
TRUNC (number function) .......................................................................................................... 4-117
TRUNC (date function) ................................................................................................................ 4-117
UID ................................................................................................................................................... 4-118
UPPER............................................................................................................................................... 4-118
USER ................................................................................................................................................ 4-119
USERENV........................................................................................................................................ 4-120
VALUE.............................................................................................................................................. 4-121
VAR_POP ........................................................................................................................................ 4-122
VAR_SAMP ..................................................................................................................................... 4-123
VARIANCE ..................................................................................................................................... 4-125
VSIZE ............................................................................................................................................... 4-126
ROUND and TRUNC Date Functions........................................................................................ 4-127
User-Defined Functions ................................................................................................................ 4-128
5 Expressions, Conditions, and Queries
Expressions.......................................................................................................................................... 5-2
Conditions ......................................................................................................................................... 5-15
Queries and Subqueries ................................................................................................................. 5-21
6 About SQL Statements
Summary of SQL Statements........................................................................................................... 6-2
Finding the SQL Statement for a Database Task.......................................................................... 6-5
vii
7 SQL Statements:
ALTER CLUSTER to ALTER SYSTEM
ALTER CLUSTER .............................................................................................................................. 7-3
ALTER DATABASE ........................................................................................................................... 7-9
ALTER DIMENSION ...................................................................................................................... 7-34
ALTER FUNCTION ......................................................................................................................... 7-38
ALTER INDEX .................................................................................................................................. 7-40
ALTER JAVA ..................................................................................................................................... 7-58
ALTER MATERIALIZED VIEW.................................................................................................... 7-61
ALTER MATERIALIZED VIEW LOG ......................................................................................... 7-76
ALTER OUTLINE ............................................................................................................................ 7-83
ALTER PACKAGE ........................................................................................................................... 7-85
ALTER PROCEDURE ..................................................................................................................... 7-88
ALTER PROFILE .............................................................................................................................. 7-91
ALTER RESOURCE COST ............................................................................................................ 7-95
ALTER ROLE .................................................................................................................................... 7-98
ALTER ROLLBACK SEGMENT................................................................................................. 7-100
ALTER SEQUENCE ....................................................................................................................... 7-103
ALTER SESSION ........................................................................................................................... 7-105
ALTER SYSTEM............................................................................................................................. 7-127
8 SQL Statements:
ALTER TABLE toconstraint_clause
ALTER TABLE .................................................................................................................................... 8-2
ALTER TABLESPACE ..................................................................................................................... 8-67
ALTER TRIGGER ............................................................................................................................ 8-76
ALTER TYPE ..................................................................................................................................... 8-79
ALTER USER .................................................................................................................................... 8-88
ALTER VIEW .................................................................................................................................... 8-94
ANALYZE .......................................................................................................................................... 8-96
ASSOCIATE STATISTICS ........................................................................................................... 8-110
AUDIT............................................................................................................................................... 8-114
CALL................................................................................................................................................. 8-128
COMMENT ..................................................................................................................................... 8-131
COMMIT ......................................................................................................................................... 8-133
viii
constraint_clause............................................................................................................................. 8-136
9 SQL Statements:
CREATE CLUSTER to CREATE SEQUENCE
CREATE CLUSTER ........................................................................................................................... 9-3
CREATE CONTEXT ........................................................................................................................ 9-13
CREATE CONTROLFILE............................................................................................................... 9-15
CREATE DATABASE ...................................................................................................................... 9-21
CREATE DATABASE LINK........................................................................................................... 9-28
CREATE DIMENSION ................................................................................................................... 9-34
CREATE DIRECTORY .................................................................................................................... 9-40
CREATE FUNCTION ...................................................................................................................... 9-43
CREATE INDEX ............................................................................................................................... 9-52
CREATE INDEXTYPE..................................................................................................................... 9-76
CREATE JAVA .................................................................................................................................. 9-79
CREATE LIBRARY .......................................................................................................................... 9-86
CREATE MATERIALIZED VIEW ................................................................................................ 9-88
CREATE MATERIALIZED VIEW LOG .................................................................................... 9-107
CREATE OPERATOR ................................................................................................................... 9-115
CREATE OUTLINE ....................................................................................................................... 9-119
CREATE PACKAGE ...................................................................................................................... 9-122
CREATE PACKAGE BODY ......................................................................................................... 9-127
CREATE PROCEDURE ................................................................................................................ 9-132
CREATE PROFILE ......................................................................................................................... 9-139
CREATE ROLE ............................................................................................................................... 9-146
CREATE ROLLBACK SEGMENT.............................................................................................. 9-149
CREATE SCHEMA ........................................................................................................................ 9-152
CREATE SEQUENCE .................................................................................................................... 9-155
10 SQL Statements:
CREATE SYNONYM to
DROP ROLLBACK SEGMENT
CREATE SYNONYM ...................................................................................................................... 10-3
CREATE TABLE ............................................................................................................................... 10-7
CREATE TABLESPACE ................................................................................................................ 10-56
ix
CREATE TEMPORARY TABLESPACE ..................................................................................... 10-63
CREATE TRIGGER ....................................................................................................................... 10-66
CREATE TYPE ................................................................................................................................ 10-80
CREATE TYPE BODY ................................................................................................................... 10-93
CREATE USER................................................................................................................................ 10-99
CREATE VIEW ............................................................................................................................ 10-105
DELETE ......................................................................................................................................... 10-115
DISASSOCIATE STATISTICS ................................................................................................. 10-123
DROP CLUSTER......................................................................................................................... 10-126
DROP CONTEXT........................................................................................................................ 10-128
DROP DATABASE LINK .......................................................................................................... 10-129
DROP DIMENSION .................................................................................................................. 10-131
DROP DIRECTORY ................................................................................................................... 10-133
DROP FUNCTION ..................................................................................................................... 10-134
DROP INDEX .............................................................................................................................. 10-136
DROP INDEXTYPE .................................................................................................................... 10-138
DROP JAVA.................................................................................................................................. 10-140
DROP LIBRARY.......................................................................................................................... 10-142
DROP MATERIALIZED VIEW................................................................................................. 10-143
DROP MATERIALIZED VIEW LOG ..................................................................................... 10-145
DROP OPERATOR ..................................................................................................................... 10-147
DROP OUTLINE......................................................................................................................... 10-149
DROP PACKAGE ....................................................................................................................... 10-150
DROP PROCEDURE .................................................................................................................. 10-152
DROP PROFILE .......................................................................................................................... 10-154
DROP ROLE ................................................................................................................................ 10-156
DROP ROLLBACK SEGMENT ............................................................................................... 10-157
11 SQL Statements:
DROP SEQUENCE to UPDATE
DROP SEQUENCE .......................................................................................................................... 11-3
DROP SYNONYM ........................................................................................................................... 11-5
DROP TABLE.................................................................................................................................... 11-7
DROP TABLESPACE..................................................................................................................... 11-10
DROP TRIGGER ........................................................................................................................... 11-13
x