Expressions

Title and Copyright Information
Preface
- Audience
- Documentation Accessibility
- Related Documents
- Conventions
Changes in This Release for Oracle Database SQL Language Reference
- Changes in Oracle Database Release 19c
1 Introduction to Oracle SQL
- History of SQL
- SQL Standards
  - How SQL Works
  - Common Language for All Relational Databases
- Using Enterprise Manager
- Lexical Conventions
- Tools Support
2 Basic Elements of Oracle SQL
- Data Types
- Data Type Comparison Rules
- Literals
- Format Models
- Nulls
- Comments
- Database Objects
  - Schema Objects
  - Nonschema Objects
- Database Object Names and Qualifiers
- Syntax for Schema Objects and Parts in SQL Statements
3 Pseudocolumns
- Hierarchical Query Pseudocolumns
- Sequence Pseudocolumns
  - Where to Use Sequence Values
  - How to Use Sequence Values
- Version Query Pseudocolumns
- COLUMN_VALUE Pseudocolumn
- OBJECT_ID Pseudocolumn
- OBJECT_VALUE Pseudocolumn
- ORA_ROWSCN Pseudocolumn
- ROWID Pseudocolumn
- ROWNUM Pseudocolumn
- XMLDATA Pseudocolumn
4 Operators
- About SQL Operators
  - Unary and Binary Operators
  - Operator Precedence
- Arithmetic Operators
- COLLATE Operator
- Concatenation Operator
- Hierarchical Query Operators
  - PRIOR
  - CONNECT_BY_ROOT
- Set Operators
- Multiset Operators
- User-Defined Operators
5 Expressions
- About SQL Expressions
- Simple Expressions
- Analytic View Expressions
  - Examples of Analytic View Expressions
- Compound Expressions
- CASE Expressions
- Column Expressions
- CURSOR Expressions
- Datetime Expressions
- Function Expressions
- Interval Expressions
- JSON Object Access Expressions
- Model Expressions
- Object Access Expressions
- Placeholder Expressions
- Scalar Subquery Expressions
- Type Constructor Expressions
- Expression Lists
6 Conditions
- About SQL Conditions
  - Condition Precedence
- Comparison Conditions
  - Simple Comparison Conditions
  - Group Comparison Conditions
- Floating-Point Conditions
- Logical Conditions
- Model Conditions
  - IS ANY Condition
  - IS PRESENT Condition
- Multiset Conditions
- Pattern-matching Conditions
  - LIKE Condition
  - REGEXP_LIKE Condition
- Null Conditions
- XML Conditions
  - EQUALS_PATH Condition
  - UNDER_PATH Condition
- SQL/JSON Conditions
- Compound Conditions
- BETWEEN Condition
- EXISTS Condition
- IN Condition
- IS OF type Condition
7 Functions
- About SQL Functions
- Single-Row Functions
  - Numeric Functions
  - Character Functions Returning Character Values
  - Character Functions Returning Number Values
  - Character Set Functions
  - Collation Functions
  - Datetime Functions
  - General Comparison Functions
  - Conversion Functions
  - Large Object Functions
  - Collection Functions
  - Hierarchical Functions
  - Data Mining Functions
  - XML Functions
  - JSON Functions
  - Encoding and Decoding Functions
  - NULL-Related Functions
  - Environment and Identifier Functions
- Aggregate Functions
- Analytic Functions
- Object Reference Functions
- Model Functions
- OLAP Functions
- Data Cartridge Functions
- ABS
- ACOS
- ADD_MONTHS
- APPROX_COUNT
- APPROX_COUNT_DISTINCT
- APPROX_COUNT_DISTINCT_AGG
- APPROX_COUNT_DISTINCT_DETAIL
- APPROX_MEDIAN
- APPROX_PERCENTILE
- APPROX_PERCENTILE_AGG
- APPROX_PERCENTILE_DETAIL
- APPROX_RANK
- APPROX_SUM
- ASCII
- ASCIISTR
- ASIN
- ATAN
- ATAN2
- AVG
- BFILENAME
- BIN_TO_NUM
- BITAND
- BITMAP_BIT_POSITION
- BITMAP_BUCKET_NUMBER
- BITMAP_CONSTRUCT_AGG
- BITMAP_COUNT
- BITMAP_OR_AGG
- CARDINALITY
- CAST
- CEIL
- CHARTOROWID
- CHR
- CLUSTER_DETAILS
- CLUSTER_DISTANCE
- CLUSTER_ID
- CLUSTER_PROBABILITY
- CLUSTER_SET
- COALESCE
- COLLATION
- COLLECT
- COMPOSE
- CON_DBID_TO_ID
- CON_GUID_TO_ID
- CON_NAME_TO_ID
- CON_UID_TO_ID
- CONCAT
- CONVERT
- CORR
- CORR_*
  - CORR_S
  - CORR_K
- COS
- COSH
- COUNT
- COVAR_POP
- COVAR_SAMP
- CUBE_TABLE
- CUME_DIST
- CURRENT_DATE
- CURRENT_TIMESTAMP
- CV
- DATAOBJ_TO_MAT_PARTITION
- DATAOBJ_TO_PARTITION
- DBTIMEZONE
- DECODE
- DECOMPOSE
- DENSE_RANK
- DEPTH
- DEREF
- DUMP
- EMPTY_BLOB, EMPTY_CLOB
- EXISTSNODE
- EXP
- EXTRACT (datetime)
- EXTRACT (XML)
- EXTRACTVALUE
- FEATURE_COMPARE
- FEATURE_DETAILS
- FEATURE_ID
- FEATURE_SET
- FEATURE_VALUE
- FIRST
- FIRST_VALUE
- FLOOR
- FROM_TZ
- GREATEST
- GROUP_ID
- GROUPING
- GROUPING_ID
- HEXTORAW
- INITCAP
- INSTR
- ITERATION_NUMBER
- JSON_ARRAY
- JSON_ARRAYAGG
- JSON_DATAGUIDE
- JSON_MERGEPATCH
- JSON_OBJECT
- JSON_OBJECTAGG
- JSON_QUERY
- JSON_SERIALIZE
- JSON_TABLE
- JSON_VALUE
- LAG
- LAST
- LAST_DAY
- LAST_VALUE
- LEAD
- LEAST
- LENGTH
- LISTAGG
- 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_COLLATION_ID
- NLS_COLLATION_NAME
- NLS_INITCAP
- NLS_LOWER
- NLS_UPPER
- NLSSORT
- NTH_VALUE
- NTILE
- NULLIF
- NUMTODSINTERVAL
- NUMTOYMINTERVAL
- NVL
- NVL2
- ORA_DM_PARTITION_NAME
- ORA_DST_AFFECTED
- ORA_DST_CONVERT
- ORA_DST_ERROR
- ORA_HASH
- ORA_INVOKING_USER
- ORA_INVOKING_USERID
- PATH
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
- POWER
- POWERMULTISET
- POWERMULTISET_BY_CARDINALITY
- PREDICTION
- PREDICTION_BOUNDS
- PREDICTION_COST
- PREDICTION_DETAILS
- PREDICTION_PROBABILITY
- PREDICTION_SET
- PRESENTNNV
- PRESENTV
- PREVIOUS
- RANK
- RATIO_TO_REPORT
- RAWTOHEX
- RAWTONHEX
- REF
- REFTOHEX
- REGEXP_COUNT
- REGEXP_INSTR
- REGEXP_REPLACE
- REGEXP_SUBSTR
- REGR_ (Linear Regression) Functions
- REMAINDER
- REPLACE
- ROUND (date)
- ROUND (number)
- ROUND_TIES_TO_EVEN (number)
- ROW_NUMBER
- ROWIDTOCHAR
- ROWIDTONCHAR
- RPAD
- RTRIM
- SCN_TO_TIMESTAMP
- SESSIONTIMEZONE
- SET
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STANDARD_HASH
- 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_TEST_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_EXTRACT_UTC
- SYS_GUID
- SYS_OP_ZONE_ID
- SYS_TYPEID
- SYS_XMLAGG
- SYS_XMLGEN
- SYSDATE
- SYSTIMESTAMP
- TAN
- TANH
- TIMESTAMP_TO_SCN
- TO_APPROX_COUNT_DISTINCT
- TO_APPROX_PERCENTILE
- TO_BINARY_DOUBLE
- TO_BINARY_FLOAT
- TO_BLOB (bfile)
- TO_BLOB (raw)
- TO_CHAR (bfile|blob)
- TO_CHAR (character)
- TO_CHAR (datetime)
- TO_CHAR (number)
- TO_CLOB (bfile|blob)
- TO_CLOB (character)
- TO_DATE
- TO_DSINTERVAL
- TO_LOB
- TO_MULTI_BYTE
- TO_NCHAR (character)
- TO_NCHAR (datetime)
- TO_NCHAR (number)
- TO_NCLOB
- TO_NUMBER
- TO_SINGLE_BYTE
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_UTC_TIMESTAMP_TZ
- TO_YMINTERVAL
- TRANSLATE
- TRANSLATE ... USING
- TREAT
- TRIM
- TRUNC (date)
- TRUNC (number)
- TZ_OFFSET
- UID
- UNISTR
- UPPER
- USER
- USERENV
- VALIDATE_CONVERSION
- VALUE
- VAR_POP
- VAR_SAMP
- VARIANCE
- VSIZE
- WIDTH_BUCKET
- XMLAGG
- XMLCAST
- XMLCDATA
- XMLCOLATTVAL
- XMLCOMMENT
- XMLCONCAT
- XMLDIFF
- XMLELEMENT
- XMLEXISTS
- XMLFOREST
- XMLISVALID
- XMLPARSE
- XMLPATCH
- XMLPI
- XMLQUERY
- XMLROOT
- XMLSEQUENCE
- XMLSERIALIZE
- XMLTABLE
- XMLTRANSFORM
- ROUND and TRUNC Date Functions
- About User-Defined Functions
  - Prerequisites
  - Name Precedence
    - Naming Conventions
8 Common SQL DDL Clauses
- allocate_extent_clause
- constraint
- deallocate_unused_clause
- file_specification
- logging_clause
- parallel_clause
- physical_attributes_clause
- size_clause
- storage_clause
9 SQL Queries and Subqueries
- About Queries and Subqueries
- Creating Simple Queries
- Hierarchical Queries
  - Hierarchical Query Examples
- The UNION [ALL], INTERSECT, MINUS Operators
- Sorting Query Results
- Joins
- Using Subqueries
- Unnesting of Nested Subqueries
- Selecting from the DUAL Table
- Distributed Queries
10 SQL Statements: ADMINISTER KEY MANAGEMENT to ALTER JAVA
- Types of SQL Statements
- How the SQL Statement Chapters are Organized
- ADMINISTER KEY MANAGEMENT
- ALTER ANALYTIC VIEW
- ALTER ATTRIBUTE DIMENSION
- ALTER AUDIT POLICY (Unified Auditing)
- ALTER CLUSTER
- ALTER DATABASE
- ALTER DATABASE DICTIONARY
- ALTER DATABASE LINK
- ALTER DIMENSION
- ALTER DISKGROUP
- ALTER FLASHBACK ARCHIVE
- ALTER FUNCTION
- ALTER HIERARCHY
- ALTER INDEX
- ALTER INDEXTYPE
- ALTER INMEMORY JOIN GROUP
- ALTER JAVA
11 SQL Statements: ALTER LIBRARY to ALTER SESSION
- ALTER LIBRARY
- ALTER LOCKDOWN PROFILE
- ALTER MATERIALIZED VIEW
- ALTER MATERIALIZED VIEW LOG
- ALTER MATERIALIZED ZONEMAP
- ALTER OPERATOR
- ALTER OUTLINE
- ALTER PACKAGE
- ALTER PLUGGABLE DATABASE
- ALTER PROCEDURE
- ALTER PROFILE
- ALTER RESOURCE COST
- ALTER ROLE
- ALTER ROLLBACK SEGMENT
- ALTER SEQUENCE
- ALTER SESSION
  - Initialization Parameters and ALTER SESSION
  - Session Parameters and ALTER SESSION
12 SQL Statements: ALTER SYNONYM to COMMENT
- ALTER SYNONYM
- ALTER SYSTEM
- ALTER TABLE
- ALTER TABLESPACE
- ALTER TABLESPACE SET
- ALTER TRIGGER
- ALTER TYPE
- ALTER USER
- ALTER VIEW
- ANALYZE
- ASSOCIATE STATISTICS
- AUDIT (Traditional Auditing)
- AUDIT (Unified Auditing)
- CALL
- COMMENT
13 SQL Statements: COMMIT to CREATE JAVA
- COMMIT
- CREATE ANALYTIC VIEW
- CREATE ATTRIBUTE DIMENSION
- CREATE AUDIT POLICY (Unified Auditing)
- CREATE CLUSTER
- CREATE CONTEXT
- CREATE CONTROLFILE
- CREATE DATABASE
- CREATE DATABASE LINK
- CREATE DIMENSION
- CREATE DIRECTORY
- CREATE DISKGROUP
- CREATE EDITION
- CREATE FLASHBACK ARCHIVE
- CREATE FUNCTION
- CREATE HIERARCHY
- CREATE INDEX
- CREATE INDEXTYPE
- CREATE INMEMORY JOIN GROUP
- CREATE JAVA
14 SQL Statements: CREATE LIBRARY to CREATE SCHEMA
- CREATE LIBRARY
- CREATE LOCKDOWN PROFILE
- CREATE MATERIALIZED VIEW
- CREATE MATERIALIZED VIEW LOG
- CREATE MATERIALIZED ZONEMAP
- CREATE OPERATOR
- CREATE OUTLINE
- CREATE PACKAGE
- CREATE PACKAGE BODY
- CREATE PFILE
- CREATE PLUGGABLE DATABASE
- CREATE PROCEDURE
- CREATE PROFILE
- CREATE RESTORE POINT
- CREATE ROLE
- CREATE ROLLBACK SEGMENT
- CREATE SCHEMA
15 SQL Statements: CREATE SEQUENCE to DROP CLUSTER
- CREATE SEQUENCE
- CREATE SPFILE
- CREATE SYNONYM
- CREATE TABLE
- CREATE TABLESPACE
- CREATE TABLESPACE SET
- CREATE TRIGGER
- CREATE TYPE
- CREATE TYPE BODY
- CREATE USER
- CREATE VIEW
- DELETE
- DISASSOCIATE STATISTICS
- DROP ANALYTIC VIEW
- DROP ATTRIBUTE DIMENSION
- DROP AUDIT POLICY (Unified Auditing)
- DROP CLUSTER
16 SQL Statements: DROP CONTEXT to DROP JAVA
- DROP CONTEXT
- DROP DATABASE
- DROP DATABASE LINK
- DROP DIMENSION
- DROP DIRECTORY
- DROP DISKGROUP
- DROP EDITION
- DROP FLASHBACK ARCHIVE
- DROP FUNCTION
- DROP HIERARCHY
- DROP INDEX
- DROP INDEXTYPE
- DROP INMEMORY JOIN GROUP
- DROP JAVA
17 SQL Statements: DROP LIBRARY to DROP SYNONYM
- DROP LIBRARY
- DROP LOCKDOWN PROFILE
- DROP MATERIALIZED VIEW
- DROP MATERIALIZED VIEW LOG
- DROP MATERIALIZED ZONEMAP
- DROP OPERATOR
- DROP OUTLINE
- DROP PACKAGE
- DROP PLUGGABLE DATABASE
- DROP PROCEDURE
- DROP PROFILE
- DROP RESTORE POINT
- DROP ROLE
- DROP ROLLBACK SEGMENT
- DROP SEQUENCE
- DROP SYNONYM
18 SQL Statements: DROP TABLE to LOCK TABLE
- DROP TABLE
- DROP TABLESPACE
- DROP TABLESPACE SET
- DROP TRIGGER
- DROP TYPE
- DROP TYPE BODY
- DROP USER
- DROP VIEW
- EXPLAIN PLAN
- FLASHBACK DATABASE
- FLASHBACK TABLE
- GRANT
- INSERT
- LOCK TABLE
19 SQL Statements: MERGE to UPDATE
- MERGE
- NOAUDIT (Traditional Auditing)
- NOAUDIT (Unified Auditing)
- PURGE
- RENAME
- REVOKE
- ROLLBACK
- SAVEPOINT
- SELECT
- SET CONSTRAINT[S]
- SET ROLE
- SET TRANSACTION
- TRUNCATE CLUSTER
- TRUNCATE TABLE
- UPDATE
A How to Read Syntax Diagrams
- Graphic Syntax Diagrams
- Backus-Naur Form Syntax
B Automatic and Manual Locking Mechanisms During SQL Operations
- Automatic Locks in DML Operations
- Automatic Locks in DDL Operations
- Manual Data Locking
- List of Nonblocking DDLs
C Oracle and Standard SQL
- ANSI Standards
- ISO Standards
- Oracle Compliance to Core SQL
- Oracle Support for Optional Features of SQL/Foundation
- Oracle Compliance with SQL/CLI
- Oracle Compliance with SQL/PSM
- Oracle Compliance with SQL/MED
- Oracle Compliance with SQL/OLB
- Oracle Compliance with SQL/JRT
- Oracle Compliance with SQL/XML
- Oracle Compliance with FIPS 127-2
- Oracle Extensions to Standard SQL
- Oracle Compliance with Older Standards
- Character Set Support
D Oracle Regular Expression Support
- Multilingual Regular Expression Syntax
- Regular Expression Operator Multilingual Enhancements
- Perl-influenced Extensions in Oracle Regular Expressions
E Oracle SQL Reserved Words and Keywords
- Oracle SQL Reserved Words
- Oracle SQL Keywords
F Extended Examples
- Using Extensible Indexing
- Using XML in SQL Statements
Index

Search

Print

Download

PDF for offline and print

5 Expressions

This chapter describes how to combine values, operators, and functions into expressions.

This chapter includes these sections: