Skip Headers

< tr>

Oracle® Database SQL Referenc e 10g Release 1 (10.1) Part Number B10759-01

Home Book List Index Master Index Feedback

---

Next

View PDF

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Related Documentation

Conventions

< a href="preface.htm#sthref9">Documentation Accessibility

What's New in the SQL Reference?

Oracle Database 10g New Fe atures in the SQL Reference

1 Introduction to Oracle SQL

History of SQL

SQL Standards

How SQL Works

Common Language for All Relational Databases

Recent Enhanceme nts

Nonstandard SQL

Recursive SQL

Lexical Conventions

Tools Support

2 Basic Elements of Oracle S QL

Datatypes

Oracle Built-in Datatypes

CHAR Datatype

NCHAR Datatype

NVARCHAR2 Datatype

VARCHAR2 Datatype

VARCHAR Datatype

< a href="sql_elements001.htm#sthref84">NUMBER Datatype

Floating-Point Numbers

Numeric Precedence

DATE Dataty pe

TIMESTAMP Datatype

TIMES TAMP WITH TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE Datatype

INTERVAL YEAR TO MONTH Datatype

INTERV AL DAY TO SECOND Datatype

Datetime/Interval Arithmetic

Support for Daylight Saving Times

Datetime and Inter val Example

RAW and LONG RAW Datatypes

BFILE Datatype

BLOB Datatype

CLOB Datatype

NCLOB Datatype

Restricted Rowids

Extended Rowids

Compatibility and Migration

UROWID Datatype

ANSI, DB2, and SQL/DS Datatypes

User-Defined Types

Object Types

REFs

Varrays

Nested Tables

Oracle-Supplied Types

Any Types

SYS.ANYTYPE

SYS.ANYDATA

SYS.ANYDATASET

XML Types

XMLType

URI Datatypes

URIFactory Package

< /dl>

Spatial Types

SD O_GEOMETRY

SDO_GEORASTER

Media Types

ORDAudio

ORDImage

ORDImageSignature

ORDVideo

ORDDoc

SI_StillImage

SI_Color

SI_ AverageColor

SI_ColorHistogram

SI_PositionalColor

SI_Texture

SI_FeatureList

Expression Filter Type

Expression

3 Pseudocolumns

Hierarchical Query Pseudocolumns

CONNECT_BY_ISCYCLE

CONNECT_BY_ISLEAF

LEVEL

Sequence Pseudocolumns

Where to Use Sequence Values

How to Use Sequence Values

Version Query Pseudocolumns

OBJECT_ID

OBJECT_VALUE

ORA_ROWSCN

ROWID

ROWNUM

XMLDATA

4 Operators

About SQL Operators

Unary and Bina ry Operators

Operator Precedence

Arithmetic Operators

Concatenation Operator

Hierarchical Query Operators

PRIOR

CONNECT_BY_ROOT

Set Operators

< dd>Multiset Operators

MULTISET EXCEP T

MULTISET INTERSECT

MULTISET UNI ON

User-Defined Operators

5 Expressions

About SQL Expressions

Simple Expressions

Compound Expressions

CASE Expressions

< dd>CURSOR Expressions

Datetime Expressions

Function Expressions

Interva l Expressions

Object Access Expressions

Scalar Subquery Expressions

Model Expressions

Type Constructor Expressions

Variable Expressions

Expression Lists

6 Conditions

Ab out SQL Conditions

Condition Precedence

Comparison Conditions

Simple Comparison Con ditions

Group Comparison Conditions

Floating-Point Conditions

Logical Conditions

Range Conditions

Null Conditions

Compound Conditions

EQUALS_PATH

EXISTS

IN

IS A SET

IS ANY

IS E MPTY

IS OF type

IS PRE SENT

LIKE

MEMBER

< a href="conditions018.htm#sthref1001">REGEXP_LIKE

SUBMULTISET

UNDER_PATH

7 Functions

SQL Functions

Single-Row Functions

Nu meric Functions

Character Functions Returning Character Values

Character Functions Returning Number Values

Dateti me Functions

Conversion Functions

Collection Functions

Miscellaneous Single-Row Functions

Aggregate Functions

Analytic Functions

Object Reference Functions

Model Fun ctions

ABS

ACOS

ADD_MONTHS

ASCII

ASCIISTR

ASIN

ATAN

ATAN2

AVG

BFILENAME

BIN_TO_NUM

BITAND

CARDINALITY

CAST

CEIL

CHARTOROWID

CHR

COALESCE

COLLECT

COMPOSE

< a href="functions022.htm#sthref1156">CONCAT

CONVERT

CORR

CORR_*

CORR_S

CORR_K

COS

COSH

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

CURRENT_DATE

CURRENT_TIMESTAMP

CV

DBTIMEZONE

DECODE

D ECOMPOSE

DENSE_RANK

DEPTH

DEREF

DUMP

EMPTY_BLOB, EMPTY_CLOB

EXISTSNODE

EXP

EXTRACT (datetime)

EXTRACT (XML)

EXTRACTVALUE

FIRST

FIRST_VALUE

FLOOR

FROM_TZ

GREATEST< /dd>

GROUP_ID

GROUPING

GROUPING_ID

HEXTORAW

INITCAP

INSTR

ITERATION_NUMBER

LAG

LAS T

LAST_DAY

LAST_VALUE

LEAD

LEAST

LENGTH

LN

LNNVL

LOCALTIMESTAMP

LOG

LOWER

LPAD

LTRIM

MAKE_REF

MAX

MEDIAN

MIN

MOD

MONTHS_BETWEEN

NANVL

NCHR

NEW_TIME

NEXT_DAY

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER< /a>

NTILE

NULLIF

NUMTODSINTERVAL

NUMTOYMINTERVAL

NVL

NVL2

ORA_HASH

PATH

PERCE NT_RANK

PERCENTILE_CONT

PERCENT ILE_DISC

POWER

POWERMULTISET

POWERMULTISET_BY_CARDINALITY

PRES ENTNNV

PRESENTV

PREVIOUS

RANK

RATIO_TO_REPORT

RAWTOHEX

RAWTONHEX

REF

REFTOHEX

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REGR_ (Linear Regression) Functions

REMAINDER

REPLACE

ROUND (number)

ROUND (date)

ROW_NUMBER

ROWIDTOCHAR

ROW IDTONCHAR

RPAD

RTRIM

< dd>SCN_TO_TIMESTAMP

SESSIONTIMEZONE

SET

SIGN

SIN

SINH

S OUNDEX

SQRT

STATS_BINOMIAL_TEST

STATS_CROSSTAB

STATS_F_TEST

STATS_KS_TEST

STATS_MODE

STATS_MW_TEST

STATS_ONE_WAY_ANOVA

STATS_T_TEST_*

STATS_T_TES T_ONE

STATS_T_TEST_PAIRED

STATS _T_TEST_INDEP and STATS_T_TEST_INDEPU

STATS_WSR_TEST

STDDEV

STDDEV_POP

STDDEV_SAMP

SUBSTR

SUM

SYS_CONNECT_BY_PATH

SYS_CONTEXT

SYS_DBURIGEN

SYS_EX TRACT_UTC

SYS_GUID

SYS_TYPEID

SYS_XMLAGG

SYS_XMLGEN

< dd>SYSDATE

SYSTIMESTAMP

TAN

TANH

TIMESTAMP_TO_SCN

TO_BINARY_DOUBLE

TO_BINARY_FLOAT

TO_CHAR (character)

TO_CHAR (datetime)

TO_CHAR (number)

TO_CLOB

TO_DATE

TO_DSINTERVAL

TO_LOB

TO_ MULTI_BYTE

TO_NCHAR (character)

TO_NCHAR (datetime)

TO_NCHAR (number)

TO_NCLOB

TO_NUMBER

TO_S INGLE_BYTE

TO_TIMESTAMP

TO_TIME STAMP_TZ

TO_YMINTERVAL

TRANSLAT E

TRANSLATE ... USING

TREAT

TRIM

TRUNC (number)

< a href="functions186.htm#sthref1961">TRUNC (date)

TZ_OFFSET

UID

UNISTR

UPDATEXML

UPPER

USER

USERENV

VALUE

VAR_POP

VAR_SAMP

VARIANCE

VSIZE

WIDTH_BUCKET

XMLAGG

XMLCOLAT TVAL

XMLCONCAT

XMLELEMENT

XMLFOREST

XMLSEQUENCE

< a href="functions206.htm#sthref2039">XMLTRANSFORM

ROUND and TRUNC Date Functions

User-Defined Functions

Prerequisites

Name Precedence

Naming Conventions

8 Common SQL DDL Clauses

allocate_e xtent_clause

constraint

< em>deallocate_unused_clause

file_specification

logging_clause

parallel_clause

physical_attributes_clause

storage_clau se

9 SQL Queries and Subqueries

About Queries and Subqueries

Creating Simple Queries

Hierarchical Queries

< dd>

Hierarchical Query Examples

The UNION [ALL], INTERSECT, MINUS Operators

Sorting Query Results

Joins

Join Conditions

Equijoins

Self Joins

Cartesian Products

Inner Joins

Outer Joins

Antijoins

Semijoins

Using Subqueries

Unnesting of Nested Subqueries

Selecting from the DUAL Table

Distributed Queries

10 SQL Statements: ALTER CLUSTER to ALTER JAVA

Types of SQL Statements

Data Definition Language ( DDL) Statements

Data Manipulation Language (DML) Statements

Transaction Control Statements

Session Contr ol Statements

System Control Statement

Embedded SQL Statements

How the SQL Statement Chapters are Organized

ALTER CLUSTER

ALTER DATABASE

ALTER DIMENSION

ALTER DISKGROUP

ALTER FUNCTION

ALTER INDEX

ALTER INDEXTYPE

ALTER JAVA

11 SQL Statements: ALTER MATERIALIZED VIEW to ALTER SYSTEM

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER OPERATOR

ALTER OUTLINE

ALTER PACKAGE

ALTER PROCEDURE

ALTER PROFILE

ALTER RESOURCE CO ST

ALTER ROLE

ALTER ROLLBAC K SEGMENT

ALTER SEQUENCE

ALTER SESSION

Initialization Parameters and ALTER SESSION

Session Parameters and ALTER SESSION

12 SQL Statements: ALTER TABLE to ALTER TABLESPACE

ALTER TABLE

ALTER TABLESPACE

13 SQL Stateme nts: ALTER TRIGGER to COMMIT

ALTER TRIGGER

ALTER TYPE

ALTER USER

ALTER VIEW

ANALYZE

ASSOCIATE STATISTICS

AUDIT

CALL

COMMENT

COMMIT

14 SQL Statements: CREATE CLUSTER to CREATE JAVA

CR EATE CLUSTER

CREATE CONTEXT

CREATE CONTROLFILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE DISKGROUP

CREATE FUNCTION

CREATE INDEX

CREATE INDEXTYPE

CREATE JAVA

15 SQL Statements: CRE ATE LIBRARY to CREATE SPFILE

CREATE LIBRARY

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIE W LOG

CREATE OPERATOR

CRE ATE OUTLINE

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PFILE

CREATE PROCEDURE

CREATE PROFILE

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SCHEMA

CREATE SEQUENCE

CREATE SPFILE

16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

17 SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT

CREATE TYPE

CREATE TYPE BODY

CREATE USER

CREATE VIEW

DELETE

DISASSOCIATE STATISTICS

DROP CLUSTER

DROP CONTEXT

DROP DATABASE

DROP DATABASE LINK

DROP DIMENSION< /dd>

DROP DIRECTORY

DROP DISKGROUP

DROP FUNCTION

DROP INDEX

DROP INDEXTYPE

DROP JAVA

DROP LIBRARY

DROP MATERI ALIZED VIEW

DROP MATERIALIZED VIEW LOG

DROP OPERATOR

DROP OUTLINE

DROP PACKAGE

DROP PROCEDURE

DROP PROFILE

DROP ROLE

DROP ROLLBACK SEGMENT

18 SQL Statements: DROP SEQUENCE to ROLLBACK

DROP SEQUENCE

DROP SYNONYM

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE

DROP TYPE BODY

DROP USER

DROP VIEW

EXPLAIN PLAN

FLASHBACK DATABASE

FLASHBACK TABLE

GRANT

INSERT

LOCK TABLE

MERGE

NOAUDIT

PURGE

RENAME< /dd>

REVOKE

ROLLBACK

19 SQL Statements: SA VEPOINT to UPDATE

SAVEPOINT

SELECT

SET CONSTRAINT[S]

SET ROLE

SET TRANSACTION

TRUNCATE

UPDATE

A How to Read Syntax Diagrams

Graphic Syntax Diagrams

Required Key words and Parameters

Optional Keywords and Parameters

Syntax Loops

Multipart Diagrams

Database Objects

Backus-Naur Form Syntax

B Oracle and Standar d SQL

ANSI Standards

ISO Standards

Oracle Compliance To Core SQL:2003

Oracle Support for Optional Features of SQL/Foundation:2003

Oracle Compliance with SQL/CLI:2003

Or acle Compliance with SQL/PSM:2003

Oracle Compliance with SQL/MED:2003

Oracle Compliance with SQL/XML:2003

Oracle Compliance with FIPS 127-2

Oracle Extensions to Standard S QL

Character Set Support

C Oracle Regular Expression Support

Multilingual Regular Expression Syntax

R egular Expression Operator Multilingual Enhancements

D Oracle Database Reserved Words

E Examples

Using Extensible Indexing

Using XML in SQL Statements

Index

---

Next

Copy right © 1996, 2003 Oracle Corporation All Rights Reserved.

Home Book List Index Master Index Feedback