Sql Reference Jp

SQLステートメント

Table of Contents

ALTER CLUSTER

    ALTER CLUSTER [ schema. ]cluster
      { physical_attributes_clause
      | SIZE size_clause
      | allocate_extent_clause
      | deallocate_unused_clause
      | { CACHE | NOCACHE }
      }
        [ physical_attributes_clause
        | SIZE size_clause
        | allocate_extent_clause
        | deallocate_unused_clause
        | { CACHE | NOCACHE }
        ]...
      [ parallel_clause ]

ALTER DATABASE

    ALTER DATABASE [ database ]
      { startup_clauses
      | recovery_clauses
      | database_file_clauses
      | logfile_clauses
      | controlfile_clauses
      | standby_database_clauses
      | default_settings_clauses
      | instance_clauses
      | security_clause
      } 
 
startup_clauses:
   MOUNT
   MOUNT CLONE DATABASE
   MOUNT PARALLEL
   CONVERT
   OPEN [READ ONLY]
   OPEN [READ WRITE] RESETLOGS|NORESETLOGS [MIGRATE]
 
default_settings_clauses:
   [NATIONAL] CHARACTER SET char_set
 
standby_database_clauses:
   MOUNT STANDBY DATABASE
   ACTIVATE STANDBY DATABASE
 
archivelog options:
   ARCHIVELOG
   NOARCHIVELOG
 
recovery_clauses:
   BACKUP CONTROLFILE TO 'filename' [REUSE]
   BACKUP CONTROLFILE TO TRACE [RESETLOGS] [AS 'filename' [REUSE]]
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   RECOVER recover_clause
   RECOVER MANAGED STANDBY standby_recover_clause
   END BACKUP
 
database_file_clauses:
   CREATE DATAFILE 'filename' AS filespec
   DATAFILE 'filename' ONLINE
   DATAFILE 'filename' OFFLINE [DROP]
   DATAFILE 'filename' RESIZE int K | M
   DATAFILE 'filename' AUTOEXTEND OFF
   DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]
   DATAFILE 'filename' END BACKUP
   RENAME FILE 'data_file_name' TO 'data_file_name'
 
   TEMPFILE 'filename' ONLINE
   TEMPFILE 'filename' OFFLINE
   TEMPFILE 'filename' DROP [INCLUDING DATAFILES]
   TEMPFILE 'filename' RESIZE int K | M
   TEMPFILE 'filename' AUTOEXTEND OFF
   TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]
 
controlfile_clauses:
   BACKUP CONTROLFILE TO [TRACE| 'backup_controlfile_name']
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
 
redo log options:
   ADD LOGFILE [THREAD int] [GROUP int] filespec
   ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int
   ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename'
   DROP LOGFILE GROUP int
   DROP LOGFILE ('filename')
   DROP LOGFILE MEMBER 'filename'
   RENAME FILE 'redolog_file_name' TO 'redolog_file_name'
   CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE]
   CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE]
 
Parallel server options:
   SET DBLOW = 'text'
   SET DBHIGH = 'text'
   SET DBMAC = ON | OFF
   ENABLE [PUBLIC] THREAD int
   DISABLE THREAD int
 
Backwards compatibility options:
   RENAME GLOBAL_NAME TO database [domain]
   RESET COMPATIBILITY

ALTER DIMENSION

    ALTER DIMENSION [ schema. ]dimension
      { ADD
        { level_clause
        | hierarchy_clause
        | attribute_clause
        | extended_attribute_clause
        }
        [ ADD
          { level_clause
          | hierarchy_clause
          | attribute_clause
          | extended_attribute_clause
          }
        ]...
      | DROP
        { LEVEL level
        [ RESTRICT | CASCADE ]
        | HIERARCHY hierarchy
        | ATTRIBUTE attribute
        [ LEVEL level [ COLUMN column
                [, COLUMN column ]... ]
        }
        [ DROP
          { LEVEL level
          [ RESTRICT | CASCADE ]
          | HIERARCHY hierarchy
          | ATTRIBUTE attribute
          [ LEVEL level [ COLUMN column
                  [, COLUMN column ]... ]
          }
        ]...
      | COMPILE
      }

ALTER DISKGROUP

    ALTER DISKGROUP
        { diskgroup_name
        { add_disk_clause | drop_disk_clause }
            [, { add_disk_clause | drop_disk_clause } ]...
        | resize_disk_clauses
        } [ rebalance_diskgroup_clause ]
        | {rebalance_diskgroup_clause                                    
          | check_diskgroup_clause
          | diskgroup_template_clauses
          | diskgroup_directory_clauses
          | diskgroup_alias_clauses
          | drop_diskgroup_file_clause
          } 
        | { diskgroup_name
          [, diskgroup_name ]...
          | ALL 
          }
          { undrop_disk_clause
          | diskgroup_availability
          }
        }
    ALTER FUNCTION
    ALTER FUNCTION [ schema. ]function
      COMPILE [ DEBUG ]
      [ compiler_parameters_clause
        [ compiler_parameters_clause ] ... ]
      [ REUSE SETTINGS ]

ALTER INDEX

    ALTER INDEX [ schema. ]index
      { { deallocate_unused_clause
        | allocate_extent_clause
        | shrink_clause
        | parallel_clause
        | physical_attributes_clause
        | logging_clause
        }
          [ deallocate_unused_clause
          | allocate_extent_clause
          | shrink_clause
          | parallel_clause
          | physical_attributes_clause
          | logging_clause
          ]...
      | rebuild_clause
      | PARAMETERS ('ODCI_parameters')
      | { ENABLE | DISABLE }
      | UNUSABLE
      | RENAME TO new_name
      | COALESCE
      | { MONITORING | NOMONITORING } USAGE
      | UPDATE BLOCK REFERENCES
      | alter_index_partitioning
      }

ALTER INDEXTYPE

    ALTER INDEXTYPE [ schema. ]indextype
      { { ADD | DROP }
        [ schema. ]operator (parameter_types)
          [, { ADD | DROP }
          [ schema. ]operator (parameter_types)
          ]...
        [ using_type_clause ]
      | COMPILE
      }

ALTER JAVA

    ALTER JAVA
      { SOURCE | CLASS } [ schema. ]object_name 
      [ RESOLVER 
          ( ( match_string [, ] { schema_name | - } ) 
        [ ( match_string [, ] { schema_name | - } )
        ]... 
          ) 
      ]
      { { COMPILE | RESOLVE }
      | invoker_rights_clause
      }

ALTER MATERIALIZED VIEW

    ALTER MATERIALIZED VIEW
      [ schema. ](materialized_view)
      [ physical_attributes_clause
      | table_compression
      | LOB_storage_clause
        [, LOB_storage_clause ]...
      | modify_LOB_storage_clause
        [, modify_LOB_storage_clause ]...
      | alter_table_partitioning
      | parallel_clause
      | logging_clause
      | allocate_extent_clause
      | shrink_clause
      | { CACHE | NOCACHE }
      ]
      [ alter_iot_clauses ]
      [ USING INDEX physical_attributes_clause ]
      [ MODIFY scoped_table_ref_constraint
      | alter_mv_refresh
      ]
      [ { ENABLE | DISABLE } QUERY REWRITE
      | COMPILE
      | CONSIDER FRESH
      ]

ALTER MATERIALIZED VIEW LOG

    ALTER MATERIALIZED VIEW LOG [ FORCE ]
      ON [ schema. ]table
      [ physical_attributes_clause
      | alter_table_partitioning
      | parallel_clause
      | logging_clause
      | allocate_extent_clause
      | shrink_clause
      | { CACHE | NOCACHE }
      ]
      [ ADD
          { { OBJECT ID
        | PRIMARY KEY
        | ROWID
        | SEQUENCE
        }
        [ (column [, column ]...) ]
          | (column [, column ]... )
          }
        [, { { OBJECT ID
             | PRIMARY KEY
             | ROWID
             | SEQUENCE
             }
             [ (column [, column ]...) ]
           | (column [, column ]...)
           }
        ]...
          [ new_values_clause ]
      ]

ALTER OPERATOR

    ALTER OPERATOR [ schema. ]operator
      { add_binding_clause
      | drop_binding_clause
      | COMPILE
      }

ALTER OUTLINE

    ALTER OUTLINE
      [ PUBLIC | PRIVATE ] outline
      { REBUILD
      | RENAME TO new_outline_name
      | CHANGE CATEGORY TO new_category_name
      | { ENABLE | DISABLE }
      }
        [ REBUILD
        | RENAME TO new_outline_name
        | CHANGE CATEGORY TO new_category_name
        | { ENABLE | DISABLE }
        ]...

ALTER PACKAGE

    ALTER PACKAGE [ schema. ]package
      COMPILE [ DEBUG ]
      [ PACKAGE | SPECIFICATION | BODY ]
      [ compiler_parameters_clause
        [ compiler_parameters_clause ] ... ]
      [ REUSE SETTINGS ]

ALTER PROCEDURE

    ALTER PROCEDURE [ schema. ]procedure
      COMPILE [ DEBUG ]
      [ compiler_parameters_clause
        [ compiler_parameters_clause ] ... ]
      [ REUSE SETTINGS ]

ALTER PROFILE

    ALTER PROFILE profile LIMIT
      { resource_parameters | password_parameters }
        [ resource_parameters | password_parameters
        ]...

ALTER RESOURCE COST

    ALTER RESOURCE COST
      { CPU_PER_SESSION
      | CONNECT_TIME
      | LOGICAL_READS_PER_SESSION
      | PRIVATE_SGA
      }
      integer
        [ { CPU_PER_SESSION
          | CONNECT_TIME
          | LOGICAL_READS_PER_SESSION
          | PRIVATE_SGA
          }
          integer
        ] ...

ALTER ROLE

    ALTER ROLE role
      { NOT IDENTIFIED
      | IDENTIFIED
          { BY password
          | USING [ schema. ]package
          | EXTERNALLY
          | GLOBALLY
          }
      }

ALTER ROLLBACK SEGMENT

    ALTER ROLLBACK SEGMENT rollback_segment
      { ONLINE
      | OFFLINE
      | storage_clause
      | SHRINK [ TO size_clause ]
      }

ALTER SEQUENCE

    ALTER SEQUENCE [ schema. ]sequence
      { INCREMENT BY integer
      | { MAXVALUE integer | NOMAXVALUE }
      | { MINVALUE integer | NOMINVALUE }
      | { CYCLE | NOCYCLE }
      | { CACHE integer | NOCACHE }
      | { ORDER | NOORDER }
      }
        [ INCREMENT BY integer
        | { MAXVALUE integer | NOMAXVALUE }
        | { MINVALUE integer | NOMINVALUE }
        | { CYCLE | NOCYCLE }
        | { CACHE integer | NOCACHE }
        | { ORDER | NOORDER }
        ]...

ALTER SESSION

    ALTER SESSION
      { ADVISE { COMMIT | ROLLBACK | NOTHING }
      | CLOSE DATABASE LINK dblink
      | { ENABLE | DISABLE } COMMIT IN PROCEDURE
      | { ENABLE | DISABLE } GUARD
      | { ENABLE | DISABLE | FORCE } PARALLEL
        { DML | DDL | QUERY } [ PARALLEL integer ]
      | { ENABLE RESUMABLE
          [ TIMEOUT integer ] [ NAME string ]
        | DISABLE RESUMABLE
        }
      | alter_session_set_clause
      }

ALTER SYSTEM

    ALTER SYSTEM
      { archive_log_clause
      | checkpoint_clause
      | check_datafiles_clause
      | distributed_recov_clauses
      | FLUSH { SHARED_POOL | BUFFER_CACHE }
      | end_session_clauses
      | SWITCH LOGFILE
      | { SUSPEND | RESUME }
      | quiesce_clauses
      | alter_system_security_clauses
      | shutdown_dispatcher_clause
      | REGISTER
      | SET alter_system_set_clause
        [ alter_system_set_clause ]...
      | RESET alter_system_reset_clause
          [ alter_system_reset_clause ]...
      }

ALTER TABLE

    ALTER TABLE [ schema. ]table
      [ alter_table_properties
      | column_clauses
      | constraint_clauses
      | alter_table_partitioning
      | alter_external_table_clauses
      | move_table_clause
      ]
      [ enable_disable_clause
      | { ENABLE | DISABLE }
        { TABLE LOCK | ALL TRIGGERS }
        [ enable_disable_clause
        | { ENABLE | DISABLE }
          { TABLE LOCK | ALL TRIGGERS }
        ]...
      ]
constraint_clauses::=
ADD | MODIFY | DROP
[inline_constraint|
out_of_line_constraint|
inline_ref_constraint|
out_of_line_ref_constraint]
inline_constraint::=
[CONSTRAINT constraint_name] 
(
[NOT] NULL 
| UNIQUE 
| PRIMARY KEY 
| references_close 
| CHECK(condition) 
) 
[constraint_state]
out_of_line_constraint::=
[CONSTRAINT constraint_name] 
(
UNIQUE(column[, column ...])
| PRIMARY KEY(column[, column ...])
| FOREIGN KEY(column[, column ...]) references_close 
| CHECK(condition) 
) 
[constraint_state]
inline_ref_constraint::=
(
SCOPE IS [schema.]scope_table
| WITH ROWID
| [CONSTRAINT constraint_name] reference_close [constraint_state]
)
out_of_line_ref_constraint::=
(
SCOPE FOR ( (ref_col | ref_attr) ) IS [schema.]scope_table
| REF ( (ref_col | ref_attr) ) WITH ROWID
| [CONSTRAINT constraint_name] FOREIGN KEY ( (ref_col | ref_attr) ) reference_close [constraint_state]
)
constraint_state::=
[NOT] DEFERRABLE
| INITIALLY (IMMEDIATE | DEFERRED)
| ENABLE | DISABLE
| VALIDATE | NOVALIDATE
| RELY | NORELY
| using_index_clause
| exceptions_clause
using_index_clause::=
USING INDEX 
[schema.] index 
| (create_index_statement)
| index_properties
index_attributes::=
[
physical_attributes_clause
| logging_clause
| ONLINE
| COMPUTE STATISTICS
| TABLESPACE ( tablespace | DEFAULT )
| key_compression
| SORT | NOSORT
| REVERSE
| parallel_clause
] ...
exceptions_clause::=
EXCEPTION INTO [schema.] table

Example:

-- check constraint
SQL> create table test (tid varchar2(10), constraint chk_test_tid check(to_number(tid) >0));
SQL> alter table test modify constraint chk_test_tid disable;
SQL> alter table test drop constraint chk_test_tid;

ALTER TABLESPACE

    ALTER TABLESPACE tablespace
      { DEFAULT
          [ table_compression ] storage_clause
      | MINIMUM EXTENT size_clause
      | RESIZE size_clause
      | COALESCE
      | RENAME TO new_tablespace_name
      | { BEGIN | END } BACKUP
      | datafile_tempfile_clauses
      | tablespace_logging_clauses
      | tablespace_group_clause
      | tablespace_state_clauses
      | autoextend_clause
      | flashback_mode_clause
      | tablespace_retention_clause
      }

ALTER TRIGGER

    ALTER TRIGGER [ schema. ]trigger
      { ENABLE
      | DISABLE
      | RENAME TO new_name
      | COMPILE [ DEBUG ]
          [ compiler_parameters_clause
        [ compiler_parameters_clause ] ... ]
          [ REUSE SETTINGS ]
      }

ALTER TYPE

    ALTER TYPE [ schema. ]type
      { compile_type_clause
      | replace_type_clause
      | { alter_method_spec
        | alter_attribute_definition
        | alter_collection_clauses
        | [ NOT ] { INSTANTIABLE | FINAL }
        }
        [ dependent_handling_clause ]
      }

ALTER USER

    ALTER USER
      { user
        { IDENTIFIED
          { BY password [ REPLACE old_password ]
          | EXTERNALLY [ AS 'certificate_DN' ]
          | GLOBALLY [ AS '[directory_DN]' ]
          }
        | DEFAULT TABLESPACE tablespace
        | TEMPORARY TABLESPACE
         { tablespace | tablespace_group_name }
        | QUOTA { size_clause
            | UNLIMITED
            } ON tablespace
          [ QUOTA { size_clause
              | UNLIMITED
              } ON tablespace
          ]...
        | PROFILE profile
        | DEFAULT ROLE { role [, role ]...
               | ALL [ EXCEPT
                    role [, role ]... ]
               | NONE
               }
        | PASSWORD EXPIRE
        | ACCOUNT { LOCK | UNLOCK }
        }
          [  { IDENTIFIED
           { BY password [ REPLACE old_password ]
           | EXTERNALLY [ AS 'certificate_DN' ]
           | GLOBALLY [ AS '[directory_DN]' ]
           }
         | DEFAULT TABLESPACE tablespace
         | TEMPORARY TABLESPACE
              { tablespace | tablespace_group_name }
         | QUOTA { size_clause
             | UNLIMITED
             } ON tablespace
           [ QUOTA { size_clause
               | UNLIMITED
               } ON tablespace
           ]...
         | PROFILE profile
         | DEFAULT ROLE { role [, role ]...
                | ALL [ EXCEPT
                     role [, role ]... ]
                | NONE
                }
         | PASSWORD EXPIRE
         | ACCOUNT { LOCK | UNLOCK }
         }
          ]...
      | user [, user ]...
        proxy_clause

ALTER VIEW

    ALTER VIEW [ schema. ]view
      { ADD out_of_line_constraint
      | MODIFY CONSTRAINT constraint
          { RELY | NORELY }
      | DROP { CONSTRAINT constraint
         | PRIMARY KEY
         | UNIQUE (column [, column ]...)
         }
      | COMPILE
      }

ANALYZE

    ANALYZE
      { TABLE [ schema. ]table
          [ PARTITION (partition)
          | SUBPARTITION (subpartition)
          ]
      | INDEX [ schema. ]index
          [ PARTITION (partition)
          | SUBPARTITION (subpartition)
          ]
      | CLUSTER [ schema. ]cluster
      }
      { validation_clauses
      | LIST CHAINED ROWS [ into_clause ]
      | DELETE [ SYSTEM ] STATISTICS
      | compute_statistics_clause
      | estimate_statistics_clause
      }

ASSOCIATE STATISTICS

    ASSOCIATE STATISTICS WITH
      { column_association | function_association }

AUDIT

    AUDIT
      { sql_statement_clause | schema_object_clause | NETWORK }
      [ BY { SESSION | ACCESS } ]
      [ WHENEVER [ NOT ] SUCCESSFUL ]

CALL

    CALL
      { routine_clause
      | object_access_expression
      }
      [ INTO :host_variable
        [ [ INDICATOR ] :indicator_variable ] ]

COMMENT

    COMMENT ON
      { TABLE [ schema. ]
        { table | view }
      | COLUMN [ schema. ]
        { table. | view. | materialized_view. } column
      | OPERATOR [ schema. ] operator
      | INDEXTYPE [ schema. ] indextype
      | MATERIALIZED VIEW materialized_view
      }
      IS string

COMMIT

    COMMIT [ WORK ]
      [
        [ COMMENT string ] 
        | [ WRITE 
        [ IMMEDIATE | BATCH ] [ WAIT | NOWAIT ]
        ]
      | FORCE string [, integer ]
      ]

CREATE CLUSTER

    CREATE CLUSTER [ schema. ]cluster
      (column datatype [ SORT ]
        [, column datatype [ SORT ] ]...
      )
      [ { physical_attributes_clause
        | SIZE size_clause
        | TABLESPACE tablespace
        | { INDEX
          | [ SINGLE TABLE ]
        HASHKEYS integer [ HASH IS expr ]
          }
        }
          [ physical_attributes_clause
          | SIZE size_clause
          | TABLESPACE tablespace
          | { INDEX
        | [ SINGLE TABLE ]
          HASHKEYS integer [ HASH IS expr ]
        }
          ]...
      ]
      [ parallel_clause ]
      [ NOROWDEPENDENCIES | ROWDEPENDENCIES ]
      [ CACHE | NOCACHE ]

CREATE CONTEXT

    CREATE [ OR REPLACE ] CONTEXT namespace
      USING [ schema. ] package
      [ INITIALIZED { EXTERNALLY | GLOBALLY }
      | ACCESSED GLOBALLY
      ]

CREATE CONTROLFILE

    CREATE CONTROLFILE
      [ REUSE ]
      [ SET ]
      DATABASE database
      [ logfile_clause ]
      { RESETLOGS | NORESETLOGS }
      [ DATAFILE file_specification
             [, file_specification ]... ]
      [ { MAXLOGFILES integer
        | MAXLOGMEMBERS integer
        | MAXLOGHISTORY integer
        | MAXDATAFILES integer
        | MAXINSTANCES integer
        | { ARCHIVELOG | NOARCHIVELOG }
        | FORCE LOGGING
        }
          [ MAXLOGFILES integer
          | MAXLOGMEMBERS integer
          | MAXLOGHISTORY integer
          | MAXDATAFILES integer
          | MAXINSTANCES integer
          | { ARCHIVELOG | NOARCHIVELOG }
          | FORCE LOGGING
          ]...
      ]
      [ character_set_clause ]

CREATE DATABASE

    CREATE DATABASE [ database ]
      { USER SYS IDENTIFIED BY password
      | USER SYSTEM IDENTIFIED BY password
      | CONTROLFILE REUSE
      | MAXDATAFILES integer
      | MAXINSTANCES integer
      | CHARACTER SET charset
      | NATIONAL CHARACTER SET charset
      | SET DEFAULT
          { BIGFILE | SMALLFILE } TABLESPACE
      | database_logging_clauses
      | tablespace_clauses
      | set_time_zone_clause
      }...

CREATE DATABASE LINK

    CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
      [ CONNECT TO
        { CURRENT_USER
        | user IDENTIFIED BY password [ dblink_authentication ]
        }
      | dblink_authentication
      ]
        [ CONNECT TO
          { CURRENT_USER
          | user IDENTIFIED BY password [ dblink_authentication ]
          }
        | dblink_authentication
        ]...
      [ USING connect_string ]

CREATE DIMENSION

    CREATE DIMENSION [ schema. ]dimension
      level_clause
      [ level_clause ]...
      { hierarchy_clause
      | attribute_clause
      | extended_attribute_clause
      }
        [ hierarchy_clause
        | attribute_clause
        | extended_attribute_clause
        ]...

CREATE DIRECTORY

    CREATE [ OR REPLACE ] DIRECTORY directory
      AS 'path_name'

CREATE DISKGROUP

    CREATE DISKGROUP diskgroup_name
      [ { HIGH | NORMAL | EXTERNAL } REDUNDANCY ]
      [ FAILGROUP failgroup_name ]
      DISK qualified_disk_clause
           [, qualified_disk_clause ]...
        [ [ FAILGROUP failgroup_name ]
          DISK qualified_disk_clause
           [, qualified_disk_clause ]...
        ]...

CREATE FUNCTION

    CREATE [ OR REPLACE ] FUNCTION [ schema. ]function
      [ (argument [ IN | OUT | IN OUT ]
         [ NOCOPY ] datatype
           [, argument [ IN | OUT | IN OUT ]
          [ NOCOPY ] datatype
           ]...
        )
      ]
      RETURN datatype
      [ { invoker_rights_clause
        | DETERMINISTIC
        | parallel_enable_clause
        }
          [ invoker_rights_clause
          | DETERMINISTIC
          | parallel_enable_clause
          ]...
      ]
      { { AGGREGATE | PIPELINED }
        USING [ schema. ]implementation_type
      | [ PIPELINED ]
        { IS | AS }
        { pl/sql_function_body | call_spec }
      }

CREATE INDEX

    CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ]index
      ON { cluster_index_clause
         | table_index_clause
         | bitmap_join_index_clause
         }

CREATE INDEXTYPE

    CREATE [ OR REPLACE ] INDEXTYPE
      [ schema. ]indextype FOR
      [ schema. ]operator (paramater_type
                   [, paramater_type ]...)
        [, [ schema. ]operator (paramater_type
                    [, paramater_type ]...)
        ]...
      using_type_clause

CREATE JAVA

    CREATE [ OR REPLACE ]
      [ AND { RESOLVE | COMPILE } ]
      [ NOFORCE ]
      JAVA { { SOURCE | RESOURCE }
         NAMED [ schema. ]primary_name
           | CLASS [ SCHEMA schema ]
           }
      [ invoker_rights_clause ]
      [ RESOLVER
        ((match_string [,] { schema_name | - })
          [ (match_string [,] { schema_name | - }) ]...
        )
      ]
      { USING { BFILE (directory_object_name ,
               server_file_name)
          | { CLOB | BLOB | BFILE }
            subquery
          | 'key_for_BLOB'
          }
      | AS source_char
      } 
 
    CREATE LIBRARY
    CREATE [ OR REPLACE ] LIBRARY [ schema. ]libname
      { IS | AS } 'filename' [ AGENT 'agent_dblink' ]

CREATE MATERIALIZED VIEW

    CREATE MATERIALIZED VIEW
      [ schema. ]materialized_view
      [ column_alias [, column_alias]... ]
      [ OF [ schema. ]object_type ]
      [ (scoped_table_ref_constraint) ]
      { ON PREBUILT TABLE
        [ { WITH | WITHOUT } REDUCED PRECISION ]
      | physical_properties materialized_view_props
      }
      [ USING INDEX
        [ physical_attributes_clause
        | TABLESPACE tablespace
        ]
          [ physical_attributes_clause
          | TABLESPACE tablespace
          ]...
      | USING NO INDEX
      ]
      [ create_mv_refresh ]
      [ FOR UPDATE ]
      [ { DISABLE | ENABLE }
        QUERY REWRITE
      ]
      AS subquery

CREATE MATERIALIZED VIEW LOG

    CREATE MATERIALIZED VIEW LOG
      ON [ schema. ] table
      [ physical_attributes_clause
      | TABLESPACE tablespace
      | logging_clause
      | { CACHE | NOCACHE }
        [ physical_attributes_clause
        | TABLESPACE tablespace
        | logging_clause
        | { CACHE | NOCACHE }
        ]...
      ]
      [ parallel_clause ]
      [ table_partitioning_clauses ]
      [ WITH { OBJECT ID
         | PRIMARY KEY
         | ROWID
         | SEQUENCE
         | (column [, column ]...)
         }
           [, { OBJECT ID
              | PRIMARY KEY
              | ROWID
              | SEQUENCE
              | (column [, column ]...)
              }
           ]...
        [ new_values_clause ]
      ]

CREATE OPERATOR

    CREATE [ OR REPLACE ] OPERATOR
       [ schema. ] operator binding_clause

CREATE OUTLINE

    CREATE [ OR REPLACE ]
       [ PUBLIC | PRIVATE ] OUTLINE [ outline ]
       [ FROM [ PUBLIC | PRIVATE ] source_outline ]
       [ FOR CATEGORY category ]
       [ ON statement ]

CREATE PACKAGE

    CREATE [ OR REPLACE ] PACKAGE [ schema. ]package
       [ invoker_rights_clause ]
       { IS | AS } pl/sql_package_spec

CREATE PACKAGE BODY

    CREATE [ OR REPLACE ] PACKAGE BODY
       [ schema. ]package
       { IS | AS } pl/sql_package_body

CREATE PFILE

    CREATE PFILE [= 'pfile_name' ]
       FROM SPFILE [= 'spfile_name']

CREATE PROCEDURE

    CREATE [ OR REPLACE ] PROCEDURE [ schema. ]procedure
       [ (argument [ { IN | OUT | IN OUT } ]
               [ NOCOPY ]
               datatype [ DEFAULT expr ]
           [, argument [ { IN | OUT | IN OUT } ]
               [ NOCOPY ]
               datatype [ DEFAULT expr ]
           ]...
         )
       ] 
       [ invoker_rights_clause ]
       { IS | AS }
       { pl/sql_subprogram_body | call_spec }

CREATE PROFILE

    CREATE PROFILE profile
       LIMIT { resource_parameters
         | password_parameters
         }
           [ resource_parameters
           | password_parameters
           ]...

CREATE RESTORE POINT

    CREATE RESTORE POINT restore_point
       [ GUARANTEE FLASHBACK DATABASE ]

CREATE ROLE

    CREATE ROLE role
       [ NOT IDENTIFIED
       | IDENTIFIED { BY password
            | USING [ schema. ] package
            | EXTERNALLY
            | GLOBALLY
            }
       ]

CREATE ROLLBACK SEGMENT

    CREATE [ PUBLIC ] ROLLBACK SEGMENT rollback_segment
      [ { TABLESPACE tablespace | storage_clause }
          [ TABLESPACE tablespace | storage_clause ]...
      ]

CREATE SCHEMA

    CREATE SCHEMA AUTHORIZATION schema
       { create_table_statement
       | create_view_statement
       | grant_statement
       }
         [ create_table_statement
         | create_view_statement
         | grant_statement
         ]...

CREATE SEQUENCE

    CREATE SEQUENCE [ schema. ]sequence
       [ { INCREMENT BY | START WITH } integer
       | { MAXVALUE integer | NOMAXVALUE }
       | { MINVALUE integer | NOMINVALUE }
       | { CYCLE | NOCYCLE }
       | { CACHE integer | NOCACHE }
       | { ORDER | NOORDER }
       ]
         [ { INCREMENT BY | START WITH } integer
         | { MAXVALUE integer | NOMAXVALUE }
         | { MINVALUE integer | NOMINVALUE }
         | { CYCLE | NOCYCLE }
         | { CACHE integer | NOCACHE }
         | { ORDER | NOORDER }
         ]...

CREATE SPFILE

    CREATE SPFILE [= 'spfile_name' ]
      FROM PFILE [= 'pfile_name' ]

CREATE SYNONYM

    CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
       [ schema. ]synonym 
       FOR [ schema. ]object [ @ dblink ]

CREATE TABLE

    { relational_table | object_table | XMLType_table }
    CREATE TABLESPACE
    CREATE
       [ BIGFILE | SMALLFILE ]
       { permanent_tablespace_clause
       | temporary_tablespace_clause
       | undo_tablespace_clause
       }

CREATE TRIGGER

    CREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger
       { BEFORE | AFTER | INSTEAD OF }
       { dml_event_clause
       | { ddl_event [ OR ddl_event ]...
         | database_event [ OR database_event ]...
         }
         ON { [ schema. ]SCHEMA
        | DATABASE
        }
       }
       [ WHEN (condition) ]
       { pl/sql_block | call_procedure_statement }

CREATE TYPE

    { create_incomplete_type
    | create_object_type
    | create_varray_type
    | create_nested_table_type
    }
    CREATE TYPE BODY
    CREATE [ OR REPLACE ] TYPE BODY [ schema. ]type_name
       { IS | AS }
       { subprogram_declaration
       | map_order_func_declaration
       }
         [, { subprogram_declaration
        | map_order_func_declaration
        }
         ]...
       END

CREATE USER

    CREATE USER user
       IDENTIFIED { BY password
              | EXTERNALLY [ AS 'certificate_DN' ]
              | GLOBALLY [ AS '[ directory_DN ]' ]
              }
       [ DEFAULT TABLESPACE tablespace
       | TEMPORARY TABLESPACE
        { tablespace | tablespace_group_name }
       | QUOTA size_clause
           | UNLIMITED
           }
           ON tablespace
         [ QUOTA size_clause
             | UNLIMITED
             }
             ON tablespace
         ]...
       | PROFILE profile
       | PASSWORD EXPIRE
       | ACCOUNT { LOCK | UNLOCK }
         [ DEFAULT TABLESPACE tablespace
         | TEMPORARY TABLESPACE
          { tablespace | tablespace_group_name }
         | QUOTA size_clause
             | UNLIMITED
             }
             ON tablespace
           [ QUOTA size_clause
               | UNLIMITED
               }
               ON tablespace
           ]...
         | PROFILE profile
         | PASSWORD EXPIRE
         | ACCOUNT { LOCK | UNLOCK }
         ]...
      ]

CREATE VIEW

    CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW
       [ schema. ]view
       [ (alias [ inline_constraint
              [ inline_constraint ]... ]
         | out_of_line_constraint
           [, alias [ inline_constraint
              [ inline_constraint ]... ]
           | out_of_line_constraint
           ]...
         )
       | object_view_clause
       | XMLType_view_clause
       ]
       AS subquery [ subquery_restriction_clause ]

DELETE

    DELETE [ hint ]
       [ FROM ]
       { dml_table_expression_clause
       | ONLY (dml_table_expression_clause)
       }
       [ t_alias ]
       [ where_clause ]
       [ returning_clause ] [error_logging_clause]

DISASSOCIATE STATISTICS FROM

       { COLUMNS [ schema. ]table.column
               [, [ schema. ]table.column ]...
       | FUNCTIONS [ schema. ]function
             [, [ schema. ]function ]...
       | PACKAGES [ schema. ]package
            [, [ schema. ]package ]...
       | TYPES [ schema. ]type
             [, [ schema. ]type ]...
       | INDEXES [ schema. ]index
               [, [ schema. ]index ]...
       | INDEXTYPES [ schema. ]indextype
              [, [ schema. ]indextype ]...
       }
       [ FORCE ]

DROP CLUSTER

    DROP CLUSTER [ schema. ]cluster
       [ INCLUDING TABLES [ CASCADE CONSTRAINTS ] ]

DROP CONTEXT

    DROP CONTEXT namespace

DROP DATABASE

    DROP DATABASE

DROP DATABASE LINK

    DROP [ PUBLIC ] DATABASE LINK dblink

DROP DIMENSION

    DROP DIMENSION [ schema. ]dimension

DROP DIRECTORY

    DROP DIRECTORY directory_name

DROP DISKGROUP

    DROP DISKGROUP diskgroup_name
       [ { INCLUDING | EXCLUDING }
         CONTENTS
       ]

DROP FUNCTION

    DROP FUNCTION [ schema. ]function_name

DROP INDEX

    DROP INDEX [ schema. ]index [ FORCE ]

DROP INDEXTYPE

    DROP INDEXTYPE [ schema. ]indextype [ FORCE ]

DROP JAVA

    DROP JAVA
       { SOURCE | CLASS | RESOURCE }
       [ schema. ]object_name

DROP LIBRARY

    DROP LIBRARY library_name

DROP MATERIALIZED VIEW

    DROP MATERIALIZED VIEW
       [ schema. ]materialized_view
       [ PRESERVE TABLE ]

DROP MATERIALIZED VIEW LOG

    DROP MATERIALIZED VIEW LOG
       ON [ schema. ]table

DROP OPERATOR

    DROP OPERATOR [ schema. ]operator [ FORCE ]

DROP OUTLINE

    DROP OUTLINE outline

DROP PACKAGE

    DROP PACKAGE [ BODY ] [ schema. ]package

DROP PROCEDURE

    DROP PROCEDURE [ schema. ]procedure

DROP PROFILE

    DROP PROFILE profile [ CASCADE ]

DROP RESTORE POINT

    DROP RESTORE POINT restore_point

DROP ROLE

    DROP ROLE role

DROP ROLLBACK SEGMENT

    DROP ROLLBACK SEGMENT rollback_segment

DROP SEQUENCE

    DROP SEQUENCE [ schema. ]sequence_name

DROP SYNONYM

    DROP [ PUBLIC ] SYNONYM [ schema. ]synonym
       [ FORCE ]

DROP TABLE

    DROP TABLE [ schema. ]table
       [ CASCADE CONSTRAINTS ]
       [ PURGE ]

DROP TABLESPACE

    DROP TABLESPACE tablespace 
       [ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ]
         [ CASCADE CONSTRAINTS ]
       ]

DROP TRIGGER

    DROP TRIGGER [ schema. ]trigger

DROP TYPE

    DROP TYPE [ schema. ]type_name
       [ FORCE | VALIDATE ]

DROP TYPE BODY

    DROP TYPE BODY [ schema. ]type_name

DROP USER

    DROP USER user [ CASCADE ]

DROP VIEW

    DROP VIEW [ schema. ] view
       [ CASCADE CONSTRAINTS ]

EXPLAIN PLAN

    EXPLAIN PLAN
       [ SET STATEMENT_ID = string ]
       [ INTO [ schema. ]table [ @ dblink ] ]
       FOR statement

FLASHBACK DATABASE

    FLASHBACK [ STANDBY ] DATABASE [ database ]
       { TO { { SCN | TIMESTAMP } expr
        | RESTORE POINT restore_point
        }
       | TO BEFORE { SCN | TIMESTAMP} expr
               | RESETLOGS 
               }
       }

FLASHBACK TABLE

    FLASHBACK TABLE
       [ schema. ]table
         [, [ schema. ]table ]...
       TO { { SCN | TIMESTAMP } expr
          | RESTORE POINT restore_point
          }
        [ { ENABLE | DISABLE } TRIGGERS ]
          | BEFORE DROP [ RENAME TO table ]
          }

GRANT

    GRANT { grant_system_privileges
          | grant_object_privileges
          }

INSERT

    INSERT [ hint ]
       { single_table_insert | multi_table_insert }

LOCK TABLE

    LOCK TABLE
       [ schema. ] { table | view }
       [ { PARTITION (partition)
         | SUBPARTITION (subpartition)
         }
       | @ dblink
       ]
         [, [ schema. ] { table | view }
        [ { PARTITION (partition)
          | SUBPARTITION (subpartition)
          }
        | @ dblink
        ]
         ]...
       IN lockmode MODE
       [ NOWAIT ]

MERGE

    MERGE [ hint ]
       INTO [ schema. ] { table | view } [ t_alias ]
       USING [ schema. ] { table | view | subquery }
         [ t_alias ]
       ON ( condition )
       [ merge_update_clause ]
       [ merge_insert_clause ]
       [ error_logging_clause ]

NOAUDIT

    NOAUDIT 
       { sql_statement_clause
       | schema_object_clause
       | NETWORK
       }
       [ WHENEVER [ NOT ] SUCCESSFUL ]

PURGE

    PURGE
       { { TABLE table
         | INDEX index
         }
       | { RECYCLEBIN | DBA_RECYCLEBIN }
       | TABLESPACE tablespace
         [ USER user ]
       } ;
    DO NOT IMPORT

RENAME

    RENAME old_name
       TO new_name

REVOKE

    REVOKE { revoke_system_privileges
           | revoke_object_privileges
           }

ROLLBACK

    ROLLBACK [ WORK ]
       [ TO [ SAVEPOINT ] savepoint
       | FORCE string
       ]

SAVEPOINT

    SAVEPOINT savepoint

SELECT

    subquery [ for_update_clause ]

SET CONSTRAINT / SET CONSTRAINTS

    SET { CONSTRAINT | CONSTRAINTS }
        { constraint [, constraint ]...
        | ALL
        }
        { IMMEDIATE | DEFERRED }

SET ROLE

    SET ROLE
       { role [ IDENTIFIED BY password ]
         [, role [ IDENTIFIED BY password ] ]...
       | ALL [ EXCEPT role [, role ]... ]
       | NONE
       }

SET TRANSACTION

    SET TRANSACTION
       { { READ { ONLY | WRITE }
         | ISOLATION LEVEL
           { SERIALIZABLE | READ COMMITTED }
         | USE ROLLBACK SEGMENT rollback_segment
         }
         [ NAME string ]
       | NAME string
       }

TRUNCATE

    TRUNCATE 
       { TABLE [ schema. ]table
         [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
       | CLUSTER [ schema. ]cluster
       }
       [ { DROP | REUSE } STORAGE ]

UPDATE

    UPDATE [ hint ]
       { dml_table_expression_clause
       | ONLY (dml_table_expression_clause)
       }
       [ t_alias ]
       update_set_clause
       [ where_clause ]
       [ returning_clause ] [error_logging_clause]

Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14195/sqlqr01.htm#i87990
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/clauses.html#4240
http://www.ss64.com/ora/database_a.html

SQL関数

Table of Contents

分析ファンクション

分析ファンクション

分析ファンクションは、行のグループに基づいて集計値を計算します。各グループに対して複数の行を戻す点で、集計ファンクションと異なります。行のグループをウィンドウといい、analytic_clauseで定義されます。各行に対して、行のスライディング・ウィンドウが定義されます。このウィンドウによって、カレント行の計算に使用される行の範囲が決定されます。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。

分析ファンクションは、問合せで最後に実行される演算(最後のORDER BY句を除く)の集合です。すべての結合およびすべてのWHERE、GROUP BYおよびHAVING句は、分析ファンクションが処理される前に実行されます。そのため、分析ファンクションは、SELECT構文のリストまたはORDER BY句のみに指定できます。

通常、分析ファンクションは、累積集計、移動集計、センター集計およびレポート集計の実行に使用されます。

-- 総合文法
analytic_function::=
    analytic_function([ arguments ])
       --analytic_clause
       OVER (
        --query_partition_clause
        [PARTITION BY
          { value_expr[, value_expr ]...
          | ( value_expr[, value_expr ]... )
          } ]
        --order_by_clause
        [ ORDER [ SIBLINGS ] BY
            { expr | position | c_alias }
            [ ASC | DESC ]
            [ NULLS FIRST | NULLS LAST ]
              [, { expr | position | c_alias }
                 [ ASC | DESC ]
                 [ NULLS FIRST | NULLS LAST ]
              ]...
            --windowing_clause
            [     { ROWS | RANGE }
            { BETWEEN
              { UNBOUNDED PRECEDING
              | CURRENT ROW
              | value_expr { PRECEDING | FOLLOWING }
              }
              AND
              { UNBOUNDED FOLLOWING
              | CURRENT ROW
              | value_expr { PRECEDING | FOLLOWING }
              }
            | { UNBOUNDED PRECEDING
              | CURRENT ROW
              | value_expr PRECEDING
              }
            } ] 
        ])
 
--それぞれのクローズの文法
analytic_function::=
    analytic_function([ arguments ])
       OVER (analytic_clause)
 
analytic_clause::=
    [ query_partition_clause ]
    [ order_by_clause [ windowing_clause ] ]
 
query_partition_clause::=
    PARTITION BY
      { value_expr[, value_expr ]...
      | ( value_expr[, value_expr ]... )
      }
 
order_by_clause::=
    ORDER [ SIBLINGS ] BY
    { expr | position | c_alias }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
      [, { expr | position | c_alias }
         [ ASC | DESC ]
         [ NULLS FIRST | NULLS LAST ]
      ]...
 
windowing_clause::=
    { ROWS | RANGE }
    { BETWEEN
      { UNBOUNDED PRECEDING
      | CURRENT ROW
      | value_expr { PRECEDING | FOLLOWING }
      }
      AND
      { UNBOUNDED FOLLOWING
      | CURRENT ROW
      | value_expr { PRECEDING | FOLLOWING }
      }
    | { UNBOUNDED PRECEDING
      | CURRENT ROW
      | value_expr PRECEDING
      }
    }

analytic_function

分析ファンクションの名前を指定します(セマンティクスの説明の後に示す分析ファンクションのリストを参照)。

引数

分析ファンクションには引数を0~3個指定します。引数には、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を指定できます。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換します。個々のファンクションに特に指定がないかぎり、戻り型もその引数のデータ型となります。

参照:
数値の優先順位の詳細は、「数値の優先順位」を参照してください。暗黙的な変換の詳細は、表2-10「暗黙的な型変換のマトリックス」を参照してください。

analytic_clause

OVER analytic_clause句は、ファンクションが問合せ結果セットを操作することを示します。FROM、WHERE、GROUP BYおよびHAVING句の後に計算されます。SELECT構文のリストのこの句またはORDER BY句に分析ファンクションを指定できます。分析ファンクションに基づいて、問合せの結果をフィルタするには、これらのファンクションを親問合せ内でネストした後、ネストされた副問合せの結果をフィルタします。

analytic_clauseの注意事項:

analytic_clauseには、次の注意事項があります。

analytic_clauseのどの部分にも、分析ファンクションを指定できません。つまり、分析ファンクションはネストできません。ただし、副問合せで分析ファンクションを指定して、別の分析ファンクションを計算することはできます。
OVER analytic_clauseには、組込み分析ファンクションと同様に、ユーザー定義の分析ファンクションを指定できます。「CREATE FUNCTION」を参照してください。
query_partition_clause

PARTITION BY句を使用すると、1つ以上のvalue_exprに基づいて、問合せ結果セットをグループに分割できます。この句を省略すると、ファンクションは問合せ結果セットのすべての行を単一のグループとして扱います。

分析ファンクションでquery_partition_clauseを使用するには、構文の上位ブランチ(カッコなし)を使用します。この句をモデルの問合せ(model_column_clauses内)またはパーティション化された外部結合(outer_join_clause内)で使用するには、構文の下位ブランチ(カッコ付き)を使用します。

同じまたは異なるPARTITION BYキーで、同じ問合せに複数の分析ファンクションを指定できます。

問い合せているオブジェクトにパラレル属性があり、query_partition_clauseで分析ファンクションを指定する場合は、ファンクションの計算もパラレル化されます。

有効な値のvalue_exprは、定数、列、非分析ファンクション、ファンクション式、またはこれらのいずれかを含む式です。

order_by_clause

order_by_clauseを使用すると、パーティション内でのデータの順序付け方法を指定できます。PERCENTILE_CONTおよび(単一キーのみを適用する)PERCENTILE_DISC以外の分析ファンクションでは、各キーがvalue_exprで定義され、順序付けシーケンスで修飾された複数キーのパーティションの値を順序付けできます。

各ファンクションには、複数の順序式を指定できます。これは、2番目の式が最初の式にある同一値との間の関連性を変換できるため、値をランク付けするファンクションを使用する場合に特に有効です。

order_by_clauseの結果が複数行の個々の値である場合、ファンクションは各行の同じ値を戻します。この動作の詳細は、「SUM」の分析例を参照してください。

ORDER BY句の制限事項:

ORDER BY句には次の制限事項があります。

order_by_clauseを分析ファンクションで使用する場合、式(expr)が必要です。SIBLINGSキーワードは無効です(これは、階層問合せでのみ有効です)。位置(position)および列別名(c_alias)も無効です。それ以外で使用する場合、このorder_by_clauseは、問合せまたは副問合せ全体を順序付ける場合に使用するものと同じです。
RANGEキーワードを使用する分析ファンクションでは、次の2つのウィンドウのいずれかを指定する場合に、ORDER BY句で複数のソート・キーを使用できます。
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。この短縮形は、RANGE UNBOUNDED PRECEDINGです。
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING。この短縮形は、RANGE UNBOUNDED FOLLOWINGです。
この2つ以外のウィンドウ境界では、分析ファンクションのORDER BY句でソート・キーを1つしか持てません。この制限事項は、ROWキーワードで指定したウィンドウ境界には適用されません。

ASC | DESC

順序付けシーケンス(昇順または降順)を指定します。デフォルトはASCです。

NULLS FIRST | NULLS LAST

NULL値を含む戻された行が順序の最初にくるか、最後にくるかを指定します。

NULLS LASTは昇順のデフォルトで、NULLS FIRSTは降順のデフォルトです。

分析ファンクションは、常に、ファンクションのorder_by_clauseで指定された順序で行を操作します。ただし、ファンクションのorder_by_clauseは結果の順序を保証しません。最終結果の順序を保証するには、問合せのorder_by_clauseを使用してください。

参照:
この句の詳細は、「SELECT」の「order_by_clause」を参照してください。

windowing_clause

一部の分析ファンクションでは、windowing_clauseを使用できます。7-16ページに示す分析ファンクションのリストでは、windowing_clauseを使用できるファンクションにアスタリスク(*)が付いています。

ROWS | RANGE

これらのキーワードは、各行に対して、ファンクションの結果の計算に使用されるウィンドウ(行の物理集合または論理集合)を定義します。ファンクションは、ウィンドウのすべての行に適用されます。ウィンドウは、問合せ結果セット内またはパーティションの上から下まで移動します。

ROWSは、物理単位(行)でウィンドウを指定します。
RANGEは、論理オフセットとしてウィンドウを指定します。
order_by_clauseを指定しないと、この句を指定できません。RANGE句で定義したウィンドウ境界には、order_by_clauseで指定できる式が1つのみのものもあります。詳細は、「ORDER BY句の制限事項:」を参照してください。

分析ファンクションが論理オフセットで戻す値は、常に決定的なものです。ただし、分析ファンクションが物理オフセットで戻す値は、順序式の結果が一意の順序にならないかぎり、非決定的な結果を生成することがあります。order_by_clauseに複数の列を指定して、結果の順序を一意にする必要があります。

BETWEEN …AND

BETWEEN … AND句を使用すると、ウィンドウにスタート・ポイントおよびエンド・ポイントを指定できます。最初の式(ANDの前)はスタート・ポイントを定義し、2番目の式(ANDの後)はエンド・ポイントを定義します。

BETWEENを省略してエンド・ポイントを1つのみ指定すると、Oracleはそれをスタート・ポイントとみなし、デフォルトでカレント行をエンド・ポイントに指定します。

UNBOUNDED PRECEDING

UNBOUNDED PRECEDINGを指定すると、パーティションの最初の行で、ウィンドウが開始します。これはスタート・ポイントの指定で、エンド・ポイントの指定としては使用できません。

UNBOUNDED FOLLOWING

UNBOUNDED FOLLOWINGを指定すると、パーティションの最後の行で、ウィンドウが終了します。これはエンド・ポイントの指定で、スタート・ポイントの指定としては使用できません。

CURRENT ROW

スタート・ポイントとして、ウィンドウがカレント行または値(それぞれROWまたはRANGEを指定したかどうかに基づく)で開始することを指定します。この場合、value_expr PRECEDINGをエンド・ポイントにできません。

エンド・ポイントとして、ウィンドウがカレント行または値(それぞれROWまたはRANGEを明示的に指定したかどうかに基づく)で終了することを指定します。この場合、value_expr FOLLOWINGをスタート・ポイントにできません。

value_expr PRECEDINGまたはvalue_expr FOLLOWING

RANGEまたはROWに対して、次のことがいえます。

value_expr FOLLOWINGがスタート・ポイントの場合、エンド・ポイントはvalue_expr FOLLOWINGである必要があります。
value_expr PRECEDINGがエンド・ポイントの場合、スタート・ポイントはvalue_expr PRECEDINGである必要があります。
数値形式の時間間隔で定義されている論理ウィンドウを定義する場合、変換ファンクションを使用する必要があります。

参照:
数値時間から間隔への変換の詳細は、「NUMTOYMINTERVAL」および「NUMTODSINTERVAL」を参照してください。

ROWSを指定した場合、次のことがいえます。

value_exprは物理オフセットになります。これは定数または式であり、正数値に評価する必要があります。
value_exprがスタート・ポイントの一部の場合、エンド・ポイントの前にある行に評価する必要があります。
RANGEを指定した場合、次のことがいえます。

value_exprは論理オフセットになります。これは、正数値または期間リテラルに評価する定数または式である必要があります。期間リテラルの詳細は、「リテラル」を参照してください。
order_by_clauseには、式を1つのみ指定できます。
value_exprが数値に対して評価を行う場合、ORDER BY exprは数値データ型またはDATEデータ型である必要があります。
value_exprが間隔値に対して評価を行う場合、ORDER BY exprはDATEデータ型である必要があります。
windowing_clauseを完全に省略した場合、デフォルトでRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWになります。

分析ファンクションは、通常、データ・ウェアハウス環境で使用されます。次に示す分析ファンクションのリストでは、windowing_clauseを含む完全な構文を使用できるファンクションには、アスタリスク(*)が付いています。

AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_(線形回帰)ファンクション *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
参照:
これらのファンクションおよびその使用方法の詳細は、下記の『Oracle Databaseデータ・ウェアハウス・ガイド』の「分析計算およびレポート」を参照してください。

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19217-02/analysis.htm#i1007779
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/functions.html
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj04_3.htm

ABS

    ABS(n)

ACOS

    ACOS(n)

ADD_MONTHS

    ADD_MONTHS(date, integer)

analytic_function

    analytic_function([ arguments ])
       OVER (analytic_clause)

APPENDCHILDXML

    APPENDCHILDXML
      ( XMLType_instance, XPath_string, value_expr [, namespace_string ]
      )

ASCII

    ASCII(char)

ASCIISTR

    ASCIISTR(char)

ASIN

    ASIN(n)

ATAN

    ATAN(n)

ATAN2

    ATAN2(n1 { , | / } n2)

AVG

    AVG([ DISTINCT | ALL ] expr)
      [ OVER(analytic_clause) ]

BFILENAME

    BFILENAME('directory', 'filename')

BIN_TO_NUM

    BIN_TO_NUM(expr [, expr ]... )

BITAND

    BITAND(expr1, expr2)

CARDINALITY

    CARDINALITY(nested_table)

CAST

    CAST({ expr | MULTISET (subquery) } AS type_name)

CEIL

    CEIL(n)

CHARTOROWID

    CHARTOROWID(char)

CHR

    CHR(n [ USING NCHAR_CS ])

CLUSTER_ID

    CLUSTER_ID ( [ schema . ] model mining_attribute_clause )

CLUSTER_PROBABILITY

    CLUSTER_PROBABILITY ( [ schema . ] model
       [ , cluster_id ] mining_attribute_clause )

CLUSTER_SET

    CLUSTER_SET ( [ schema . ] model
       [ , topN [ , cutoff ]
       ]
       mining_attribute_clause )

COALESCE

    COALESCE(expr [, expr ]...)

COLLECT

    COLLECT (column)

COMPOSE

    COMPOSE(char)

CONCAT

    CONCAT(char1, char2)

CONVERT

    CONVERT(char, dest_char_set[, source_char_set ])

CORR

    CORR(expr1, expr2)
       [ OVER (analytic_clause) ]

CORR_K

    { CORR_K | CORR_S }
       (expr1, expr2
        [, { COEFFICIENT
           | ONE_SIDED_SIG
           | ONE_SIDED_SIG_POS
           | ONE_SIDED_SIG_NEG
           | TWO_SIDED_SIG
           }
        ]
       )

COS

    COS(n)

COSH

    COSH(n)

COUNT

    COUNT({ * | [ DISTINCT | ALL ] expr })
       [ OVER (analytic_clause) ]
    COVAR_POP
    COVAR_POP(expr1, expr2)
       [ OVER (analytic_clause) ]
    COVAR_SAMP
    COVAR_SAMP(expr1, expr2)
       [ OVER (analytic_clause) ]
    CUME_DIST(集計)
    CUME_DIST(expr[,expr ]...)
       WITHIN GROUP
       (ORDER BY expr [ DESC | ASC ]
              [ NULLS { FIRST | LAST } ]
             [, expr [ DESC | ASC ]
                 [ NULLS { FIRST | LAST } ]
             ]...
       )

CUME_DIST(分析)

    CUME_DIST( )
       OVER ([ query_partition_clause ] order_by_clause)

CURRENT_DATE

    CURRENT_DATE
    CURRENT_TIMESTAMP
    CURRENT_TIMESTAMP [ (precision) ]
    CV
    CV([ dimension_column ])

DBTIMEZONE

    DBTIMEZONE
    DECODE
    DECODE(expr, search, result
             [, search, result ]...
           [, default ]
          )

DECOMPOSE

    DECOMPOSE( string [ CANONICAL | COMPATIBILITY ] )

DELETXML

    DELETEXML
      ( XMLType_instance, XPath_string
        [, namespace_string ]
      )

DENSE_RANK(集計)

    DENSE_RANK(expr [, expr ]...) WITHIN GROUP
      (ORDER BY expr [ DESC | ASC ]
             [ NULLS { FIRST | LAST } ]
            [,expr [ DESC | ASC ]
               [ NULLS { FIRST | LAST } ]
            ]...
      )

DENSE_RANK(集計)

    DENSE_RANK( )
       OVER([ query_partition_clause ] order_by_clause)

DEPTH

    DEPTH(correlation_integer)

DEREF

    DEREF(expr)

DUMP

    DUMP(expr[, return_fmt
            [, start_position [, length ] ]
         ]
        )

EMPTY_BLOB

    { EMPTY_BLOB | EMPTY_CLOB }( )

EXISTSNODE

    EXISTSNODE
       (XMLType_instance, XPath_string
          [, namespace_string ]
       )

EXP

    EXP(n)

EXTRACT(日時)

    EXTRACT( { { YEAR
           | MONTH
           | DAY
           | HOUR
           | MINUTE
           | SECOND
           }
         | { TIMEZONE_HOUR
           | TIMEZONE_MINUTE
           }
         | { TIMEZONE_REGION
           | TIMEZONE_ABBR
           }
         }
         FROM { datetime_value_expression
              | interval_value_expression
              }
           )

EXTRACT(XML)

    EXTRACT(XMLType_instance, XPath_string
        [, namespace_string ]
           )

EXTRACTVALUE

    EXTRACTVALUE
       (XMLType_instance, XPath_string
         [, namespace_string ]
       )

FEATURE_ID

    FEATURE_ID ( [ schema . ] model mining_attribute_clause )

FEATURE_SET

    FEATURE_SET ( [ schema . ] model
       [ , topN [ , cutoff ]
       ]
       mining_attribute_clause )

FEATURE_VALUE

    FEATURE_VALUE ( [ schema . ] model
       [ , feature_id ] mining_attribute_clause )

FIRST

    aggregate_function
       KEEP
       (DENSE_RANK FIRST ORDER BY
        expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
        ]...
       )
       [ OVER query_partition_clause ]

FIRST_VALUE

    FIRST_VALUE (expr [ IGNORE NULLS ])
       OVER (analytic_clause)

FLOOR

    FLOOR(n)

FROM_TZ

    FROM_TZ (timestamp_value, time_zone_value)

GREATEST

    GREATEST(expr [, expr ]...)

GROUP_ID

    GROUP_ID( )

GROUPING

    GROUPING(expr)

GROUPING_ID

    GROUPING_ID(expr [, expr ]...)

HEXTORAW

    HEXTORAW(char)

INITCAP

    INITCAP(char)

INSERTCHILDXML

    INSERTCHILDXML
      ( XMLType_instance, XPath_string, child_expr,
          value_expr [, namespace_string ]
      )

INSERTXMLBEFORE

    INSERTXMLBEFORE
       ( XMLType_instance, XPath_string,
          value_expr [, namespace_string ]
       )

INSTR

    { INSTR
    | INSTRB
    | INSTRC
    | INSTR2
    | INSTR4
    }
    (string , substring [, position [, occurrence ] ])

ITERATION_NUMBER

    ITERATION_NUMBER

LAG

    LAG(value_expr [, offset ] [, default ])
       OVER ([ query_partition_clause ] order_by_clause)

LAST

    aggregate_function KEEP
       (DENSE_RANK LAST ORDER BY
        expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
        ]...
       )
       [ OVER query_partition_clause ]

LAST_DAY

    LAST_DAY(date)

LAST_VALUE

    LAST_VALUE(expr [ IGNORE NULLS ])
       OVER (analytic_clause)

LEAD

    LEAD(value_expr [, offset ] [, default ])
       OVER ([ query_partition_clause ] order_by_clause)

LEAST

    LEAST(expr [, expr ]...)

LENGTH

    { LENGTH
    | LENGTHB
    | LENGTHC
    | LENGTH2
    | LENGTH4
    }
    (char)

LN

    LN(n)

LNNVL

    LNNVL(condition)

LOCALTIMESTAMP

    LOCALTIMESTAMP [ (timestamp_precision) ]

LOG

    LOG(n2, n1)

LOWER

    LOWER(char)

LPAD

    LPAD(expr1, n [, expr2 ])

LTRIM

    LTRIM(char [, set ])

MAKE_REF

    MAKE_REF({ table | view } , key [, key ]...)

MAX

    MAX([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

MEDIAN

    MEDIAN(expr) [ OVER (query_partition_clause) ]

MIN

    MIN([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

MOD

    MOD(n2, n1)

MONTHS_BETWEEN

    MONTHS_BETWEEN(date1, date2)

NANVL

    NANVL(n2, n1)

NCHR

    NCHR(number)

NEW_TIME

    NEW_TIME(date, timezone1, timezone2)

NEXT_DAY

    NEXT_DAY(date, char)

NLS_CHARSET_DECL_LEN

    NLS_CHARSET_DECL_LEN(byte_count, 'char_set_id')

NLS_CHARSET_ID

    NLS_CHARSET_ID ( string )

NLS_CHARSET_NAME

    NLS_CHARSET_NAME(number)

NLS_INITCAP

    NLS_INITCAP(char [, 'nlsparam' ])

NLS_LOWER

    NLS_LOWER(char [, 'nlsparam' ])

NLS_UPPER

    NLS_UPPER(char [, 'nlsparam' ])

NLSSORT

    NLSSORT(char [, 'nlsparam' ])

NTILE

    NTILE(expr)
       OVER ([ query_partition_clause ] order_by_clause)

NULLIF

    NULLIF(expr1, expr2)

NUMTODSINTERVAL

    NUMTODSINTERVAL(n, 'interval_unit')

NUMTOYMINTERVAL

    NUMTOYMINTERVAL(n, 'interval_unit')

NVL

    NVL(expr1, expr2)

NVL2

    NVL2(expr1, expr2, expr3)

ORA_HASH

    ORA_HASH (expr [, max_bucket [, seed_value ] ])

PATH

    PATH (correlation_integer)

PERCENT_RANK(集計)

    PERCENT_RANK(expr [, expr ]...) WITHIN GROUP
       (ORDER BY
        expr [ DESC | ASC ]
         [NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [NULLS { FIRST | LAST } ]
        ]...
       )

PERCENT_RANK(分析)

    PERCENT_RANK( )
       OVER ([ query_partition_clause ] order_by_clause)

PERCENTILE_CONT

    PERCENTILE_CONT(expr) WITHIN GROUP
       (ORDER BY expr [ DESC | ASC ])
       [ OVER (query_partition_clause) ]

PERCENTILE_DISC

    PERCENTILE_DISC(expr) WITHIN GROUP
       (ORDER BY expr [ DESC | ASC ])
       [ OVER (query_partition_clause) ]

POWER

    POWER(n2, n1)

POWERMULTISET

    POWERMULTISET(expr)

POWERMULTISET_BY_CARDINALITY

    POWERMULTISET_BY_CARDINALITY(expr, cardinality)

PREDICTION

    PREDICTION ( [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )

PREDICTION_COST

    PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause
     mining_attribute_clause )

PREDICTION_DETAILS

    PREDICTION_DETAILS ( [ schema . ] model mining_attribute_clause )

PREDICTION_PROBABILITY

    PREDICTION_PROBABILITY ( [ schema . ] model [ , class ]
       mining_attribute_clause )

PREDICTION_SET

    PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ]
      [ cost_matrix_clause ] mining_attribute_clause )

PRESENTNNV

    PRESENTNNV(cell_reference, expr1, expr2)

PRESENTV

    PRESENTV(cell_reference, expr1, expr2)

PREVIOUS

    PREVIOUS(cell_reference)

RANK(集計)

    RANK(expr [, expr ]...) WITHIN GROUP
       (ORDER BY
        expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
        ]...
       )

RANK(分析)

    RANK( )
       OVER ([ query_partition_clause ] order_by_clause)

RATIO_TO_REPORT

    RATIO_TO_REPORT(expr)
       OVER ([ query_partition_clause ])

RAWTOHEX

    RAWTOHEX(raw)

RAWTONHEX

    RAWTONHEX(raw)

REF

    REF (correlation_variable)

REFTOHEX

    REFTOHEX (expr)

REGEXP_INSTR

    REGEXP_INSTR (source_char, pattern
              [, position
             [, occurrence
                [, return_option
                   [, match_parameter ]
                ]
             ]
              ]
             )

REGEXP_REPLACE

    REGEXP_REPLACE(source_char, pattern
               [, replace_string
              [, position
                 [, occurrence
                [, match_parameter ]
                 ]
              ]
               ]
              )

REGEXP_SUBSTR

    REGEXP_SUBSTR(source_char, pattern
              [, position
             [, occurrence
                [, match_parameter ]
             ]
              ]
             )

REGR_AVGX

    { REGR_SLOPE
    | REGR_INTERCEPT
    | REGR_COUNT
    | REGR_R2
    | REGR_AVGX
    | REGR_AVGY
    | REGR_SXX
    | REGR_SYY
    | REGR_SXY
    }
    (expr1 , expr2)
    [ OVER (analytic_clause) ]

REMAINDER

    REMAINDER(n2, n1)

REPLACE

    REPLACE(char, search_string
        [, replacement_string ]
           )

ROUND(日付)

    ROUND(date [, fmt ])

ROUND(数値)

    ROUND(n [, integer ])

ROW_NUMBER

    ROW_NUMBER( )
       OVER ([ query_partition_clause ] order_by_clause)

ROWIDTOCHAR

    ROWIDTOCHAR(rowid)

ROWIDTONCHAR

    ROWIDTONCHAR(rowid)

RPAD

    RPAD(expr1 , n [, expr2 ])

RTRIM

    RTRIM(char [, set ])

SCN_TO_TIMESTAMP

    SCN_TO_TIMESTAMP(number)

SESSIONTIMEZONE

    SESSIONTIMEZONE

SET

    SET (nested_table)

SIGN

    SIGN(n)

SIN

    SIN(n)

SINH

    SINH(n)

SOUNDEX

    SOUNDEX(char)

SQRT

    SQRT(n)

STATS_BINOMIAL_TEST

    STATS_BINOMIAL_TEST(expr1, expr2, p
                [, { TWO_SIDED_PROB
                   | EXACT_PROB
                   | ONE_SIDED_PROB_OR_MORE
                   | ONE_SIDED_PROB_OR_LESS
                   }
                ]
               )

STATS_CROSSTAB

    STATS_CROSSTAB(expr1, expr2
               [, { CHISQ_OBS
              | CHISQ_SIG
              | CHISQ_DF
              | PHI_COEFFICIENT
              | CRAMERS_V
              | CONT_COEFFICIENT
              | COHENS_K
              }
               ]
              )

STATS_F_TEST

    STATS_F_TEST(expr1, expr2
             [, { { STATISTIC
              | DF_NUM
              | DF_DEN
              | ONE_SIDED_SIG
              } expr3
            | TWO_SIDED_SIG
            }
             ]
            )

STATS_KS_TEST

    STATS_KS_TEST(expr1, expr2
              [, { STATISTIC | SIG } ]
             )

STATS_MODE

    STATS_MODE(expr)

STATS_MW_TEST

    STATS_MW_TEST(expr1, expr2
              [, { STATISTIC
             | U_STATISTIC
             | ONE_SIDED_SIG "expr3"
             | TWO_SIDED_SIG
             }
              ]
             )

STATS_ONE_WAY_ANOVA

    STATS_ONE_WAY_ANOVA(expr1, expr2
                [, { SUM_SQUARES_BETWEEN
                   | SUM_SQUARES_WITHIN
                   | DF_BETWEEN
                   | DF_WITHIN
                   | MEAN_SQUARES_BETWEEN
                   | MEAN_SQUARES_WITHIN
                   | F_RATIO
                   | SIG
                   }
                ]
               )

STATS_T_TEST_INDEP

    { STATS_T_TEST_INDEP
    | STATS_T_TEST_INDEPU
    | STATS_T_TEST_ONE
    | STATS_T_TEST_PAIRED
    }
    (expr1, expr2
      [, { { STATISTIC
           | ONE_SIDED_SIG
           } expr3
         | TWO_SIDED_SIG
         | DF
         }
      ]
    )

STATS_WSR_TEST

    STATS_WSR_TEST(expr1, expr2
               [, { STATISTIC
              | ONE_SIDED_SIG
              | TWO_SIDED_SIG
              }
               ]
              )

STDDEV

    STDDEV([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

STDDEV_POP

    STDDEV_POP(expr)
       [ OVER (analytic_clause) ]

STDDEV_SAMP

    STDDEV_SAMP(expr)
       [ OVER (analytic_clause) ]

SUBSTR

    { SUBSTR
    | SUBSTRB
    | SUBSTRC
    | SUBSTR2
    | SUBSTR4
    }
    (char, position [, substring_length ])

SUM

    SUM([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

SYS_CONNECT_BY_PATH

    SYS_CONNECT_BY_PATH(column, char)

SYS_CONTEXT

    SYS_CONTEXT('namespace', 'parameter' [, length ])

SYS_DBURIGEN

    SYS_DBURIGEN({ column | attribute }
             [ rowid ]
               [, { column | attribute }
              [ rowid ]
               ]...
             [, 'text ( )' ]
            )

SYS_EXTRACT_UTC

    SYS_EXTRACT_UTC(datetime_with_timezone)

SYS_GUID

    SYS_GUID( )

SYS_TYPEID

    SYS_TYPEID(object_type_value)

SYS_XMLAGG

    SYS_XMLAGG(expr [, fmt ])

SYS_XMLGEN

    SYS_XMLGEN(expr [, fmt ])

SYSDATE

    SYSDATE

SYSTIMESTAMP

    SYSTIMESTAMP

TAN

    TAN(n)

TANH

    TANH(n)

TIMESTAMP_TO_SCN

    TIMESTAMP_TO_SCN(timestamp)

TO_BINARY_DOUBLE

    TO_BINARY_DOUBLE(expr [, fmt [, 'nlsparam' ] ])

TO_BINARY_FLOAT

    TO_BINARY_FLOAT(expr [, fmt [, 'nlsparam' ] ])

TO_CHAR(文字)

    TO_CHAR(nchar | clob | nclob)

TO_CHAR(日時)

    TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

TO_CHAR(数値)

    TO_CHAR(n [, fmt [, 'nlsparam' ] ])

TO_CLOB

    TO_CLOB(lob_column | char)

TO_DATE

    TO_DATE(char [, fmt [, 'nlsparam' ] ])

TO_DSINTERVAL

    TO_DSINTERVAL(char [, 'nlsparam' ])

TO_LOB

    TO_LOB(long_column)

TO_MULTI_BYTE

    TO_MULTI_BYTE(char)

TO_NCHAR(文字)

    TO_NCHAR({char | clob | nclob})

TO_NCHAR(日時)

    TO_NCHAR({ datetime | interval }
         [, fmt [, 'nlsparam' ] ]
        )

TO_NCHAR(数値)

    TO_NCHAR(n [, fmt [, 'nlsparam' ] ])

TO_NCLOB

    TO_NCLOB(lob_column | char)

TO_NUMBER

    TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])

TO_SINGLE_BYTE

    TO_SINGLE_BYTE(char)

TO_TIMESTAMP

    TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])

TO_TIMESTAMP_TZ

    TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])

TO_YMINTERVAL

    TO_YMINTERVAL(char)

TRANSLATE

    TRANSLATE(expr, from_string, to_string)

TRANSLATE ... USING

    TRANSLATE ( char USING
          { CHAR_CS | NCHAR_CS }
          )

TREAT

    TREAT(expr AS [ REF ] [ schema. ]type)

TRIM

    TRIM([ { { LEADING | TRAILING | BOTH }
         [ trim_character ]
           | trim_character
           }
           FROM
         ]
         trim_source
        )

TRUNC(日付)

    TRUNC(date [, fmt ])

TRUNC(数値)

    TRUNC(n1 [, n2 ])

TZ_OFFSET

    TZ_OFFSET({ 'time_zone_name'
          | '{ + | - } hh : mi'
          | SESSIONTIMEZONE
          | DBTMEZONE
          }
         )

UID

    UID
    UNISTR
    UNISTR( string )

UPDATEXML

    UPDATEXML
          (XMLType_instance,
        XPath_string, value_expr
          [, XPath_string, value_expr ]...
        [, namespace_string ]
          )

UPPER

    UPPER(char)

USER

    USER
    ユーザー定義ファンクション
    [ schema. ]
    { [ package. ]function | user_defined_operator }
    [ @ dblink. ]
    [ ([ DISTINCT | ALL ] expr [, expr ]...) ]

USERENV

    USERENV('parameter')

VALUE

    VALUE(correlation_variable)

VAR_POP

    VAR_POP(expr) [ OVER (analytic_clause) ]
    VAR_SAMP
    VAR_SAMP(expr) [ OVER (analytic_clause) ]
    VARIANCE
    VARIANCE([ DISTINCT | ALL ] expr)
        [ OVER (analytic_clause) ]
    VSIZE
    VSIZE(expr)

WIDTH_BUCKET

    WIDTH_BUCKET
       (expr, min_value, max_value, num_buckets)

XMLAGG

    XMLAGG(XMLType_instance [ order_by_clause ])

XMLCOLATTVAL

    XMLCOLATTVAL
      (value_expr [ AS c_alias ]
        [, value_expr [ AS c_alias ]
          ]...
      )

XMLCOMMENT

    XMLCOMMENT ( value_expr )

XMLCDATA

     XMLCDATA ( value_expr )

XMLCONCAT

    XMLCONCAT(XMLType_instance [, XMLType_instance ]...)

XMLELEMENT

    XMLELEMENT
     ( [ NAME ] identifier
        [, XML_attributes_clause ]
        [, value_expr [ AS c_alias ]
          [, value_expr [ AS c_alias ]
        ]...
     )

XMLFOREST

    XMLFOREST
      ( value_expr [ AS c_alias ]
        [, value_expr [ AS c_alias ]
          ]...
      )

XMLPARSE

    XMLPARSE
      ({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ]
      )

XMLPI

    XMLPI
     (
      [ NAME ] identifier
      [, value_expr ]
     )

XMLQUERY

    XMLQUERY
     ( XQuery_string
       [ XML_passing_clause ]
       RETURNING CONTENT
     )

XMLROOT

    XMLROOT
      ( value_expr, VERSION
      { value_expr | NO VALUE }
      [, STANDALONE { YES | NO | NO VALUE } ]
      )

XMLSEQUENCE

    XMLSEQUENCE( XMLType_instance
           | sys_refcursor_instance [, fmt ]
           )

XMLSERIALIZE

    XMLSERIALIZE
      ( { DOCUMENT | CONTENT } value_expr
          [ AS datatype ]
      )

XMLTABLE

    XMLTABLE
     (
      [ XML_namespaces_clause , ] XQuery_string XMLTABLE_options
     )

XMLTABLE_options

    [ XML_passing_clause ] [ COLUMNS XML_table_column
                     [, XML_table_column
                     ]...
                   ]

XMLTRANSFORM

    XMLTRANSFORM(XMLType_instance, XMLType_instance)

SQLエクスプレッション

Table of Contents

CASE式

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

複合式

{ (expr)
| { + | - | PRIOR } expr
| expr { * | / | + | - | || } expr
}
注意: 二重の縦線はBNF表記法ではなく、
      構文の一部(連結を示す)です。

CURSOR式

CURSOR (subquery)

DATETIME式

datetime_value_expr AT
   { LOCAL
   | TIME ZONE { ' [ + | - ] hh:mm'
               | DBTIMEZONE
               | 'time_zone_name'
               | expr
               }
   }

ファンクション式

組込みSQLファンクションまたはユーザー定義ファンクションを式として使用できます。

期間式

interval_value_expr
   { DAY [ (leading_field_precision) ] TO
     SECOND [ (fractional_second_precision) ]
   | YEAR [ (leading_field_precision) ] TO
     MONTH
   }

モデル式

{ measure_column [ { condition | expr }[ , { condition | expr } ...] ]
| aggregate_function
     { [ { condition | expr }[ , { condition | expr } ...] ]
     | [ single_column_for_loop [, single_column_for_loop] ... ]
     | [ multi_column_for_loop ]
     }
| analytic_function
}
 
注意:
太字で示されている外側の大カッコは構文の一部です。オプションを表すものではありません。

オブジェクト・アクセス式

{ table_alias.column.
| object_table_alias.
| (expr).
}
{ attribute [.attribute ]...
  [.method ([ argument [, argument ]... ]) ]
| method ([ argument [, argument ]... ])
}
 
スカラー副問合せ式
 
1行から1列のみ戻す式として使用できます。

単純式

{ [ query_name.
  | [schema.]
    { table. | view. | materialized view. }
  ] { column | ROWID }
| ROWNUM
| string
| number
| sequence. { CURRVAL | NEXTVAL }
| NULL
}

型コンストラクタ式

[ NEW ] [ schema. ]type_name
   ([ expr [, expr ]... ])

変数式

:host_variable
   [ [ INDICATOR ]
     :indicator_variable
   ]

SQ'L条件式

Table of Contents

論理(ブール)演算子でTRUE、FALSEまたはUNKNOWNを計算する式

複合条件

{ (condition)
| NOT condition
| condition { AND | OR } condition
}

EQUALS_PATH条件

EQUALS_PATH
    (column, path_string [, correlation_integer ])

EXISTS条件

EXISTS (subquery)

浮動小数点条件

expr IS [ NOT ] { NAN | INFINITE }

グループ比較条件

{ expr
     { = | != | ^= | <> | > | < | >= | <= }
     { ANY | SOME | ALL }
     ({ expression_list | subquery })
| expr [, expr ]...
  { = | != | ^= | <> }
  { ANY | SOME | ALL }
  ({ expression_list [, expression_list ]...
   | subquery
   }
  )
}
 
!=、^=および<>は不等性テストを表します。

IN条件

{ expr [ NOT ] IN ({ expression_list | subquery })
| ( expr
    [, expr ]...
    [ NOT ] IN ({ expression_list
                  [, expression_list ]...
                | subquery
                }
               )
  )
}

IS A SET条件

nested_table IS [ NOT ] A SET

IS ANY条件

[ dimension_column IS ] ANY

IS EMPTY条件

nested_table IS [ NOT ] EMPTY

IS OF TYPE条件

expr IS [ NOT ] OF [ TYPE ]
   ([ ONLY ] [ schema. ] type
      [, [ ONLY ] [ schema. ] type ]...
   )

IS PRESENT条件

cell_reference IS PRESENT

LIKE条件

char1 [ NOT ] ( LIKE | LIKEC | LIKE2 | LIKE4 )
  char2 [ ESCAPE esc_char ]

論理条件

{ NOT | AND | OR }

MEMBER条件

expr [ NOT ] MEMBER [ OF ] nested_table

NULL条件

expr IS [ NOT ] NULL

範囲条件

expr [ NOT ] BETWEEN expr AND expr

REGEXP_LIKE条件

REGEXP_LIKE(source_char, pattern
            [, match_parameter ]
           )

単純比較条件

{ expr
  { = | != | ^= | <> | > | < | >= | <= }
  expr
| (expr [, expr ]...)
  { = | != | ^= | <> }
  (subquery)
}
 
!=、^=および<>は不等性テストを表します。

SUBMULTISET条件

nested_table1
[ NOT ] SUBMULTISET [ OF ]
nested_table2

UNDER_PATH条件

UNDER_PATH (column [, levels ], path_string
             [, correlation_integer ]
           )

Pseudocolumn jp