SQL References (C~G)

CLOSE cursor_name

Function

It closes a cursor.

Syntax

<close statement> ::=
    CLOSE cursor_name
    ;

Syntax Rules and Parameters

cursor_name

The cursor should be open.
The cursor should be declared with DECLARE cursor_name statement in the session.

Description

The cursor is an object which exists in the session and it does not affect the cursor in a different session.

Example

The following is an example of DECLARE, OPEN, FETCH, and CLOSE the cursor by using gsql (interactive SQL tool).

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur1;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.


gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic dynamic SQL

O

For More Information

Refer to the followings.

COMMENT ON name IS

Function

It stores the comments about the object in the dictionary.

Syntax

<comment statement> ::=
    COMMENT ON <comment object> IS 'comment string'
    ;

<comment object> ::=
      CLUSTER GROUP group_name
    | CLUSTER MEMBER member_name
    | DATABASE
    | PROFILE profile_name
    | AUTHORIZATION user_name
    | TABLESPACE tablespace_name
    | SCHEMA schema_name
    | TABLE [schema_name].table_name
    | COLUMN [schema_name].table_name.column_name
    | INDEX [schema_name].index_name
    | SEQUENCE [schema_name].sequence_name
    | CONSTRAINT [schema_name].constraint_name
    | PROCEDURE [schema_name].procedure_name

Invocation and Access Rules

The altering privileges on each object are required to perform <comment statement> as follows.

Syntax Rules and Parameters

<comment object>

It is an object in which the comments are to be stored. The comments for the following database objects are stored.

If schema_name for the schema object is not specified, the schema name is determined by Schema Path of the user performing the statement.
COMMENT ON TABLE test_table IS 'test comment'; 
→ COMMENT ON TABLE user_default_schema.test_table IS 'test comment';

'comment string'

It describes the comments to be stored.
Use the empty string ('') to delete the comments as follows.
COMMENT ON TABLE test_table IS '';
The length of the comment string can not exceed 1024 bytes.

Description

The information can be retrieved from the COMMENTS column of the following dictionary view per each object type.

For more information about the detailed description of each view, refer to DICTIONARY_SCHEMA.

Examples

The following is an example of creating the comment on the table.

gSQL> COMMENT ON TABLE t1 IS 'test comment on table t1';

Comment created.

The following is an example of creating the comment on the column.

gSQL> COMMENT ON COLUMN t1.id IS 'test comment on column t1.id';

Comment created.

The following is an example of creating the comment on the schema.

gSQL> COMMENT ON SCHEMA s1 IS 'test comment on schema s1';

Comment created.

Compatibility

<comment statement> does not exist in SQL standard.

COMMIT

Function

It terminates the current transaction and makes all changes permanent.

Syntax

<commit statement> ::=
    COMMIT [ WORK ] 
       [ [ <commit comment clause> ] [ <commit write clause> ] |
         [ <commit force clause> ] [ <commit comment clause> ] ]
    ;

<commit comment clause> ::=
      COMMENT 'comment_string'

<commit write clause> ::=
      WRITE [ WAIT | NOWAIT ]

<commit force clause> ::=
    FORCE 'xid_string'

Syntax Rules and Parameters

WORK

It is the reserved word which does not affect the operation.

<commit comment clause>

<commit write clause>

It determines whether to wait until the redo logs generated by the commit operation are written on the redo log file.

<commit force clause>

It is used to manually commit a distributed transaction.

Description

COMMIT statement completes the following statements which were executed in a transaction.

Exceptionally, the following DDL statements which manage the OS resources or change the DATA TYPE are automatically committed.

When performing COMMIT, the cursor opened by WITHOUT HOLD option is automatically closed. For more information about cursors, refer to the following cursor related statements.

If the transaction violates the DEFERRED constraint, the COMMIT statement fails and the transaction is rolled back. For more information about DEFERRED constraint, refer to SET CONSTRAINTS.

Example

The following is an example of performing COMMIT after executing INSERT statement.

gSQL> INSERT INTO t1 VALUES ( 1, 'anonymous' );

1 row created.

gSQL> COMMIT WORK COMMENT 'INSERT T1';

Commit complete.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T261

Chained transactions

X

For More Information

Refer to the followings.

CREATE AUDIT POLICY

Function

It creates an audit policy object.
AUDIT POLICY should be performed to activate the created audit policy object.

Syntax

<audit policy definition> ::= 
    CREATE AUDIT POLICY policy_name
    { <privilege_audit_clause> |  <action_audit_clause> | <privilege_audit_clause> <action_audit_clause> }
    ; 

<privilege_audit_clause> ::=
    PRIVILEGES <database_privilege> [, ...]

<action_audit_clause> ::=
    ACTIONS { <object_action_audit> | <system_action_audit> } [, ...]

<object_action_audit> ::=
      ALL ON [schema_name.]object_name
    | <object_action> ON [schema_name.]object_name

<system_action_audit> ::=
      ALL
    | DDL
    | <system_action>

Invocation and Access Rules

AUDIT SYSTEM ON DATABASE privilege is required to perform <audit policy definition>.

Syntax Rules and Parameters

policy_name

It is the name of the audit policy to be created.

<privilege_audit_clause>

The privilege audit is auditing when the SQL statement is successfully performed by using the database privilege.
It can audit a specific user performing SQL statement by using the database privilege, and it does not record the privilege audit for SYS user who is the owner of the database.

The following is an example of granting SELECT ANY TABLE privilege to user u1 and activating the audit policy.

CREATE AUDIT POLICY p1 
       PRIVILEGES SELECT ANY TABLE;

AUDIT POLICY p1;

If the user u1 performs the following SQL statement, the privilege audit differently operates.

<database_privilege> which can be described in the privilege audit can be viewed with the following query.

SELECT PRIVILEGE_NAME FROM V$AUDITABLE_DB_PRIVILEGES;

<action_audit_clause>

It audits an action for a specific object and an action for the entire database.

<object_action_audit>

ALL ON object_name

It means all actions which can list objects corresponding to object_name.

The following table describes audit actions of which each object type can audit.

Audit action per object type

Object type

Action

Table

ALTER, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE

View

ALTER, COMMENT, GRANT, SELECT

Sequence

ALTER, COMMENT, GRANT, SELECT

Stored Function/Procedure

ALTER, COMMENT, EXECUTE, GRANT

<object_action> ON object_name

Each separate action per a specific object should be listed by specifying ON clause as follows.

CREATE AUDIT POLICY p1
       ACTIONS INSERT ON u1.t1
             , DELETE ON u1.t1
             , UPDATE ON u1.t1
;

Caution of EXECUTE action

Auditing the success or the failure of stored function or stored procedure is determined only based on whether it is executable at the time of execution.

<system_action_audit>

It audits the system action which occurs in the database regardless of a specific object.

The valid system action can be retrieved by using the following query.

SELECT ACTION_NAME FROM V$AUDITABLE_SYSTEM_ACTIONS;

It means all system actions.

It means all Data Definition Language (DDL).

Description

An audit policy object is an object which defines auditing targets.  
Perform AUDIT POLICY statement to activate an audit policy.
Though it is possible to define and activate multiple audit policies, but it is recommended to maintain certain number of audit policies.  
It is also recommended to bind multiple small pieces of policies into a small number of groups.

The information about an option of the created audit policy object can be retrieved through AUDIT_POLICY_OPTIONS view as follows.

SELECT audit_option
     , audit_option_type
     , object_schema
     , object_name
  FROM audit_policy_options
 WHERE policy_name = 'P1'
;

AUDIT_OPTION AUDIT_OPTION_TYPE OBJECT_SCHEMA  OBJECT_NAME
------------ ----------------- -------------- ------------
DELETE         OBJECT ACTION     U1          T1
INSERT         OBJECT ACTION     U1          T1
UPDATE         OBJECT ACTION     U1          T1

Creating Audit Record

If an action corresponding to multiple audit policies occurs, then one or more audit records are created.

If similar audit options are listed as follows, then one audit record is created.

CREATE AUDIT POLICY p1
       PRIVILEGES SELECT ANY TABLE
       ACTIONS SELECT;

AUDIT POLICY p1;
SELECT * FROM other_user.t1;

If two different audit options are listed as follows, then two audit records are created.

CREATE AUDIT POLICY p1
       ACTIONS SELECT ON u1.t1
             , SELECT ON u2.t2;

AUDIT POLICY p1;
SELECT COUNT(*) FROM u1.t1 A, u2.t2 B WHERE A.id = B.id;

If multiple audit policies are activated for the same action as follows, then two audit records are created.

CREATE AUDIT POLICY p1
       PRIVILEGES SELECT ANY TABLE;
AUDIT POLICY p1;

CREATE AUDIT POLICY p2
       ACTIONS SELECT;
AUDIT POLICY p2;
SELECT * FROM other.t1;

Examples

The following is an example of defining an audit policy which audits an privilege.

CREATE AUDIT POLICY policy_table
       PRIVILEGES CREATE ANY TABLE
                , DROP ANY TABLE
;

The following is an example of defining an audit policy which audits an action for an object.

CREATE AUDIT POLICY policy_dml
       ACTIONS INSERT ON u1.t1
             , DELETE ON u1.t1
             , UPDATE ON u1.t1
             , ALL    ON u1.t2
;

The following is an example of defining an audit policy which audits the system action.

CREATE AUDIT POLICY policy_drop
       ACTIONS DROP TABLE, TRUNCATE TABLE
;

The following is an example of defining an audit policy which combines all examples above.

CREATE AUDIT POLICY policy_group
       PRIVILEGES CREATE ANY TABLE
                , DROP ANY TABLE
       ACTIONS INSERT ON u1.t1
             , DELETE ON u1.t1
             , UPDATE ON u1.t1
             , ALL    ON u1.t2
             , DROP TABLE
             , TRUNCATE TABLE
;

Compatibility

The audit policy does not exist in SQL standard.

For More Information

Refer to the followings.

CREATE CLUSTER GROUP

Function

It creates a cluster group which is to participate in a cluster system.

Syntax

<cluster group definition> ::=
    CREATE CLUSTER GROUP group_name 
        <cluster member definition> [, ...]
    ;

<cluster member definition> ::=
    CLUSTER MEMBER member_name <connection attribute> [<member position>]

<connection attribute> ::=
    HOST 'address' PORT port_no

<member position> ::=
    POSITION DEFAULT
  | POSITION MAX
  | POSITION number

Invocation and Access Rules

It can be performed in a cluster system.

ADMINISTRATION ON DATABASE privilege is required to perform <cluster group definition>.

Syntax Rules and Parameters

group_name

It is the name of a cluster group.
An identical cluster group name or a cluster member name should not exist.
The name length should be shorter than 128 bytes.

<cluster member definition>

It defines a cluster member to be included in a cluster group.
A cluster group can include maximum 32 cluster members.
A cluster group which is created first in a cluster system can define only one cluster member, and should include itself as a cluster member.

member_name

It is the name of a cluster member.
The name of a cluster member should be same as the name of the member which was defined when creating the database of that member.
An identical cluster group name or a cluster member name should not exist.
The name length should be shorter than 128 bytes.

The start-up phase of the cluster member should be the GLOBAL OPEN phase.

<connection attribute>

It defines the connection information for the communication between the cluster members.
<connection attribute> should be as same as the HOST and PORT which were defined when the database of that cluster member was created.
The combination of HOST and PORT should be unique in the cluster system.

<member position>

It assigns the position number of the cluster member.

The member_position information of the cluster member can be retrieved through DBA_CLUSTER view.

SELECT member_name, member_id, member_position FROM dba_cluster;

If the following position numbers are being used,

The following values are assigned according to each option.

Description

<cluster group definition> statement does not rebalance the shard of tables.

Perform the following statements to rebalance the shard to an added cluster group.

Examples

The following is an example of creating a cluster group which consists of two cluster members.

gSQL> 
CREATE CLUSTER GROUP g1
    CLUSTER MEMBER g1n1 HOST '192.168.0.11' PORT 10110
;

Cluster Group created.

gSQL>
ALTER CLUSTER GROUP g1
    ADD CLUSTER MEMBER g1n2 HOST '192.168.0.12' PORT 10120
;

Cluster Group altered.

gSQL> 
CREATE CLUSTER GROUP g2
    CLUSTER MEMBER g2n1 HOST '192.168.0.21' PORT 10210,
    CLUSTER MEMBER g2n2 HOST '192.168.0.22' PORT 10220
;

Cluster Group created.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to the followings.

CREATE CLUSTER LOCATION

Function

It creates the connection information of a cluster member.

Syntax

<cluster location definition> ::=
    CREATE CLUSTER LOCATION member_name 
    <cluster connection attribute>
    ;

<cluster connection attribute> ::
       HOST 'address' PORT port_no

Invocation and Access Rules

It can be performed in a cluster system.
ADMINISTRATION ON DATABASE privilege is required to perform <cluster location definition>.

Syntax Rules and Parameters

member_name

It is the name of a cluster member.
The same cluster member name should not exist in the registered cluster location information.
The length of the name should be shorter than 128 bytes.

<cluster connection attribute>

It defines the connection information for the communication between the cluster members.
The combination of HOST and PORT should be unique in the cluster system.

Description

Generally, the information of the cluster location is automatically created by using the connection information provided when creating the cluster group or adding the cluster member. The created information is deleted together when deleting the cluster member and the cluster group.

If the information of the cluster location is modified, then the connection information can be modified by using ALTER CLUSTER LOCATION without deleting or recreating the cluster member.

Examples

gSQL> 
CREATE CLUSTER LOCATION g1n2
    HOST '192.168.0.12' PORT 10120,
;

Created

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to DROP CLUSTER LOCATION .

CREATE DISK DATA TABLESPACE

Function

It defines the disk data tablespace.

Syntax

<disk data tablespace statement> ::=
    CREATE DISK [ DATA ] TABLESPACE tablespace_name
        DATAFILE <disk datafile clause> [, ...]
        [ <data tablespace management clause> [, ...] ]

<disk datafile clause> ::=
     'filename' 
        [ SIZE <size clause> | REUSE | SIZE <size clause> REUSE ]
        [ <autoextend clause> ]
        [ AT <domain_name> ]

<autoextend clause>
    AUTOEXTEND { ON [ <next size clause> ] [ <max size clause> ] | OFF }

<next size clause>
    NEXT <size clause>

<max size clause>
    MAXSIZE { <size clause> | UNLIMITED }

<size clause> ::=
    integer [ K | M | G | T ]

<data tablespace management clause> ::=
      { ONLINE | OFFLINE }
    | EXTSIZE <size clause>

Invocation and Access Rules

CREATE TABLESPACE ON DATABASE privilege is required to perform <disk data tablespace definition>.

The user who performed the statement has CREATE OBJECT ON TABLESPACE privilege for the created tablespace.

The following privileges are required to create an object on the created tablespace.

Syntax Rules and Parameters

tablespace_name

It is the name of the tablespace to be created.
The length of the tablespace name should be shorter than 128 bytes.

<disk datafile clause>

<autoextend clause>

It sets the automatic expand property to ON or OFF. If it is set to ON, then it can specify the automatic expanded size and the maximum size of the data file.

<next size clause>

It specifies the size to be extended when the data file in use does not have available space.

<max size clause>

It specifies the maximum expanded size of the data file.

<size clause>

It specifies the file size in byte. (If it is omitted, the default unit is bytes.)

<domain_name>

It is the name of member or the group to performs the statement.
If it is omitted, then it is performed for all groups.

ONLINE | OFFLINE

It determines whether to ONLINE/ OFFLINE the tablespace.

EXTSIZE <size clause>

It specifies the extent size of the tablespace.

Description

The data tablespace is an object which provides a physical storage to store SQL schema objects such as a table, and index (LOGGING).

Examples

The following is an example of creating the disk data tablespace.

gSQL> CREATE DISK TABLESPACE space1 DATAFILE 'test_file_1.dbf' SIZE 10M REUSE;

Tablespace created.

The following is an example of creating the tablespace which consists of multiple data files.

gSQL> CREATE DISK TABLESPACE space1 
             DATAFILE 'test_file_3_1.dbf' SIZE 10M REUSE,
                      'test_file_3_2.dbf' SIZE 10M REUSE;

Tablespace created.

Compatibility

The SQL standard does not define the concepts of the tablespace.

For More Information

Refer to the followings.

CREATE GLOBAL TEMPORARY TABLE

Function

It creates a new global temporary table.

Syntax

<global temporary table definition> ::=
    CREATE GLOBAL TEMPORARY TABLE table_name
        ( <table element> [, ...] )
        [ <table commit action clause> ]
        [ TABLESPACE tablespace_name ]
    ;

<global temporary table definition: AS query expression> ::=
    CREATE GLOBAL TEMPORARY TABLE table_name 
        [ TABLESPACE tablespace_name ]
        AS <query expression> [ WITH [ NO ] DATA ]
    ;

<table commit action clause> ::=
    ON COMMIT { PRESERVE | DELETE } ROWS

The definition of <table element> is as same as that of <table_definition>.

For more information, refer to CREATE TABLE.

Invocation and Access Rules

A user should satisfy the following conditions to perform <global temporary table definition> statement.

Syntax Rules and Parameters

table_name

It is the table name to be created.
For more information, refer to table_name.

other syntax

For more information about other syntaxes, refer to the syntax in CREATE TABLE and in CREATE TABLE AS SELECT statement.

Description

GLOBAL TEMPORARY TABLE is used to store the data which is maintained while a transaction or a session is performed. 
It is used for the purpose as same as that of the variable of which a developer temporarily stores the mid-data of the operation when developing an application.
The global temporary table has the following features.

Whether to specify tablespace

The tablespace in which the table is created

It specifies the tablespace.

It is created in the specified tablespace.

It does not specify the tablespace.

It is created in the default temporary tablespace of the current session user.

Table commit action

Description

ON COMMIT PRESERVE ROWS

It maintains the data remained in a table even after COMMIT or ROLLBACK.

ON COMMIT DELETE ROWS (default)

It deletes all data remained in a table at the time of COMMIT or ROLLBACK (TRUNCATE).

TEMP_UNDO_ENABLED value

Description

TRUE

The undo log is recorded in the default temporary tablespace of the database system.

FALSE

The undo log is recorded in the undo tablespace of the database system.

Examples

The following is an example of performing CREATE GLOBAL TEMPORARY TABLE statement.

gSQL> CREATE  GLOBAL TEMPORARY TABLE SESSION_TABLE1(
        COL1    CHAR(10)
       ,COL2    VARCHAR2(20)
       ,COL3    NUMBER(10)
)   ON  COMMIT  DELETE ROWS;

Table created.

The following is an example of performing CREATE GLOBAL TEMPORARY TABLE ... AS SELECT statement.

gSQL> CREATE  GLOBAL TEMPORARY TABLE SESSION_TABLE2
    ON  COMMIT  PRESERVE ROWS
    AS  SELECT  *
          FROM  EMPLOYEES;

Table created.

Compatibility

CREATE GLOBAL TEMPORARY TABLE and CREATE GLOBAL TEMPORARY TABLE AS SELECT statements follow the definition of SQL standard <table definition>. However, the following is an extension of SQL standard.

SQL standard compatibility

Feature ID

Description

Compatibility

T171

LIKE clause in table definition

X

T172

AS subquery clause in table definition

O

F531

Temporary tables

X

S051

Create table of type

X

S043

Enhanced reference types

X

S081

Subtables

X

T173

Extended LIKE clause in table definition

X

T180

System-versioned tables

X

F692

Extended collation support

X

T174

Identity columns

O

T175

Generated columns

X

S071

SQL paths in function and type name resolution

X

F321

User authorization

O

T322

Extended roles

X

F762

CURRENT_CATALOG

O

F763

CURRENT_SCHEMA

O

For More Information

Refer to the followings.

CREATE IMMUTABLE TABLE

Function

It creates a new immutable table.

Syntax

<immutable table definition> ::=
    CREATE IMMUTABLE TABLE table_name
        ( <table element> [, ...] )
        [ <table sharding strategy> ]
        [ <table attribute clause> [...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
    ;

<immutable table definition: AS query expression> ::=
    CREATE IMMUTABLE TABLE table_name
        [ ( column_name [, ...] ) ]
        [ <table sharding strategy> ]
        [ <table attribute clause> [, ...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
        AS <query expression> [ WITH [ NO ] DATA ]
    ;

The definition for <table element>, <table sharding strategy>, <table attribute clause> and <table global secondary index clause> are as same as those in <table_definition>. For more information, refer to CREATE TABLE.

Invocation and Access Rules

The user should satisfy the following conditions to perform <immutable table definition>.

Syntax Rules and Parameters

table_name

It is the table name to be created and it should be unique in the schema.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of the table name should be shorter than 128 bytes.

Other Syntax

For other syntaxes, refer to the syntaxes for CREATE TABLE and CREATE TABLE AS SELECT.

Description

An immutable table is used when it is required to prevent the record stored in the table from being altered or deleted and to prevent the table from being dropped.

An immutable table can be dropped when a user, a schema, a tablespace and a cluster group are dropped.

The following SQL statements are not allowed for an immutable table.


The following SQL statements are allowed for an immutable table.

Examples

The following is an example of executing CREATE IMMUTABLE TABLE statement.

gSQL> CREATE IMMUTABLE TABLE t1
(
    id INTEGER PRIMARY KEY,
    name VARCHAR(128),
    addr VARCHAR(128)
);

Table created.

The following is an example of executing CREATE IMMUTABLE TABLE ... AS SELECT statement.

gSQL> CREATE IMMUTABLE TABLE T2
       AS SELECT *
             FROM T1;

Table created.

Compatibility

The SQL standard does not cover CREATE IMMUTABLE TABLE and CREATE IMMUTABLE TABLE AS SELECT statements.

For More Information

Refer to the followings.

CREATE INDEX

Function

It creates an index.

Syntax

<index definition> ::=
    CREATE [ UNIQUE ] INDEX index_name
        ON table_name ( <index column element> [, ...] )
        [ <index attributes> [...] ]
        [ TABLESPACE tablespace_name ]
    ;

<index column element> ::=
    column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

<index attributes> ::=
      <physical attribute clause>
    | STORAGE ( <segment attr clause> [...] )
    | <parallel clause> 

<physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

Invocation and Access Rules

The user should satisfy the following conditions to perform <index definition>.

Unique indexes in a cluster system should include all sharding keys.

Syntax Rules and Parameters

UNIQUE

It does not allow duplicate values for the columns of the index.

index_name

It is the index name to be created and it should be a unique name within the schema.
If the schema name is omitted, the index is created in the schema to which the referring table belongs.
The length of the index name should be shorter than 128 bytes.

table_name

It is the table name which creates the index.
The schema to which a table belongs, such as schema_name.table_name, can be defined. If schema_name is omitted, the default schema name of the user performing the statement is used.

column_name

It is the column name to be used as an index key.
One or more columns should be defined, and maximum 32 columns can be used as an index key.
The following constraints can occur depending on the implementation.

ASC | DESC

It specifies the sort order of a column.

NULLS FIRST | NULLS LAST

It specifies the sort order of the NULL value.

<physical attribute clause>

It defines the physical attribute of the index.

<segment attr clause>

It specifies the information for the index storage space.

<size clause>

It specifies the file size in bytes. (If the unit is omitted, the default value is bytes.)

NOPARALLEL | PARALLEL [ integer ]

It specifies the number of threads to be used when building an index.

TABLESPACE tablespace_name

It specifies the name of the tablespace in which the index is to be stored.

Description

LOGGING index and NOLOGGING index have the following trade-offs.

Examples

The following is an example of creating the unique index.

gSQL> CREATE UNIQUE INDEX idx_t1_id ON t1( id );

Index created.

The following is an example of creating an index for multiple columns.

gSQL> CREATE INDEX idx_t1_id_name ON t1( id, name );

Index created.

The following is an example of specifying the sort order of the index column.

gSQL> CREATE INDEX idx_t1_dept_id ON t1( dept_id DESC );

Index created.

The following is an example of specifying the sort order of NULL value of the index column.

gSQL> CREATE INDEX idx_t1_name ON t1( name NULLS FIRST );

Index created.

The following is an example of setting information about the space in which index is stored.

gSQL> CREATE INDEX idx_t1_id ON t1( id )
             STORAGE ( INITIAL 10M NEXT 1M MINSIZE 10M MAXSIZE 100M );

Index created.

The following is an example of creating the redo logging for the index.

gSQL> CREATE INDEX idx_t1_id ON t1( id );

Index created.

The following is an example of creating the index with parallel option.

gSQL> CREATE INDEX idx_t1_name ON t1( name ) PARALLEL;

Index created.

The following is an example of specifying the tablespace when creating an index.

gSQL> CREATE INDEX idx_t1_name ON t1( name ) TABLESPACE mem_temp_tbs;

Index created.

Compatibility

The SQL standard does not cover the concepts of the index.

For more information

Refer to DROP INDEX.

CREATE MEMORY DATA TABLESPACE

Function

It defines a memory data tablespace.

Syntax

<memory data tablespace statement> ::=
    CREATE [ MEMORY ] [ DATA ] TABLESPACE tablespace_name
        DATAFILE <memory datafile clause> [, ...]
        [ <data tablespace management clause> [, ...] ]

<memory datafile clause> ::=
     'filename' 
        [ SIZE <size clause> | REUSE | SIZE <size clause> REUSE ]
        [ AT <domain_name> ]
<size clause> ::=
    integer [ K | M | G | T ]

<data tablespace management clause> ::=
      { ONLINE | OFFLINE }
    | EXTSIZE <size clause>

Invocation and Access Rules

CREATE TABLESPACE ON DATABASE privilege is required to perform <memory data tablespace definition>.

The user who performed the statement has CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

One of the following privileges is required to create the objects on the created tablespace.

Syntax Rules and Parameters

[ MEMORY ] [ DATA ]

It is a memory tablespace to store the permanent objects such as tables, indexes, etc.
The reserved words, MEMORY and DATA, can be omitted.

tablespace_name

It is the tablespace name to be created.
The length of the tablespace name should be shorter than 128 bytes.

<memory datafile clause>

<size clause>

It specifies the file size in bytes. (If it is omitted, the default unit is bytes.)

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

ONLINE | OFFLINE

It sets ONLINE or OFFLINE of the tablespace.

EXTSIZE <size clause>

It specifies extent size of the tablespace.

Description

The data tablespace is an object which provides the physical space to store the SQL schema object such as a table, an index (LOGGING).

Examples

The following is an example of creating a memory data tablespace.

gSQL> CREATE TABLESPACE space1 DATAFILE 'test_file_1.dbf' SIZE 10M REUSE;

Tablespace created.

The following is an example of creating a tablespace which consists of multiple data files.

gSQL> CREATE TABLESPACE space1 
             DATAFILE 'test_file_3_1.dbf' SIZE 10M REUSE,
                      'test_file_3_2.dbf' SIZE 10M REUSE;

Tablespace created.

Compatibility

The SQL standard does not cover the concepts of the tablespace.

For More Information

Refer to the followings.

CREATE MEMORY TEMPORARY TABLESPACE

Function

It defines a memory temporary tablespace.

Syntax

<memory temporary tablespace statement> ::=
    CREATE [ MEMORY ] TEMPORARY TABLESPACE tablespace_name
        MEMORY <memory clause> [, ...]
        <temporary tablespace management clause>

<memory clause> 
     'memory_name' { SIZE <size clause> } [ AT <domain_name> ]

<temporary tablespace management clause> ::=
    EXTSIZE <size clause>

Invocation and Access Rules

CREATE TABLESPACE ON DATABASE privilege is required to perform <memory temporary tablespace definition>.

The user who performed the statement has CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

One of the following privileges is required to create the objects on the created tablespace.

Syntax Rules and Parameters

[ MEMORY ] TEMPORARY

It is a memory temporary tablespace to store the no logging indexes or the temporary objects such as intermediate results which are generated during the query processing.
The reserved word, MEMORY, can be omitted.

tablespace_name

It is the tablespace name to be created.
The length of the tablespace name should be shorter than 128 bytes.

<memory clause>

<size clause>

It specifies the size of shared memory space in bytes.(If it is omitted, the default unit is bytes.)
The image is not managed as a file in case of the temporary memory data.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

EXTSIZE <size clause>

It specifies extent size of the tablespace.

Description

The temporary tablespace is an object which provides the physical space to store the SQL schema object such as an index (NOLOGGING), and to store the intermediate results for sorting, hashing during the query processing.

Examples

The following is an example of creating a temporary tablespace.

gSQL> CREATE TEMPORARY TABLESPACE temp_space1 MEMORY 'test_memory_1' SIZE 10M;

Tablespace created.

The following is an example of creating a temporary tablespace which includes multiple memory spaces.

gSQL> CREATE TEMPORARY TABLESPACE temp_space1 
             MEMORY 'test_memory_3_1' SIZE 10M,
                    'test_memory_3_2' SIZE 10M;

Tablespace created.

Compatibility

The SQL standard does not cover the concepts of the tablespace.

For More Information

Refer to the followings.

CREATE PROFILE

Function

It is the statement which creates the profile, and it sets the password management method. 
When a profile is allocated to a user, the user's password is managed in the way defined in the profile.

Syntax

<profile definition> ::=

    CREATE PROFILE profile_name LIMIT 
    { <password_parameters>, ...}
    ; 

<password parameters> ::= 
      FAILED_LOGIN_ATTEMPTS { integer | UNLIMITED | DEFAULT }
    | PASSWORD_LOCK_TIME  { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_LIFE_TIME  { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_GRACE_TIME { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_REUSE_MAX  { integer | UNLIMITED | DEFAULT }
    | PASSWORD_REUSE_TIME { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_VERIFY_FUNCTION { <verify_policy> | NULL | DEFAULT }

<verify_policy> ::= 
      KISA_VERIFY_FUNCTION
    | ORA12C_VERIFY_FUNCTION
    | ORA12C_STRONG_VERIFY_FUNCTION
    | VERIFY_FUNCTION_11G 
    | VERIFY_FUNCTION

<password_parameter_number_interval> ::=
   integer 
 | integer / integer

Invocation and Access Rules

CREATE PROFILE ON DATABASE privilege is required to perform <profile definition>.

Syntax Rules and Parameters

profile_name

It specifies the profile name to be created.

password_parameters

It sets the parameters for password management.

The omitted parameters follow the "DEFAULT" profile policy.

FAILED_LOGIN_ATTEMPTS

It sets the number of consecutive login attempts allowed to fail.
If the failed attempts exceed the specified number, the account is locked.

PASSWORD_LOCK_TIME

It sets the period (days) which the account is locked after consecutive login failure.

PASSWORD_LIFE_TIME

It sets the life time of the password (day).

PASSWORD_GRACE_TIME

It sets the grace period of password expiration when logging in after PASSWORD_LIFE_TIME.

PASSWORD_GRACE_TIME starts at first login trial after the password life time. If the password is not altered during the grace period, the password expires.

PASSWORD_REUSE_MAX

It sets the number of the recent passwords which can not be reused when the user wants to reuse the old password.

PASSWORD_REUSE_MAX should be used together with PASSWORD_REUSE_TIME.

PASSWORD_REUSE_TIME

It sets the duration which the password can not be reused when the user wants to reuse the old password.

PASSWORD_REUSE_TIME should be used together with PASSWORD_REUSE_MAX.

PASSWORD_VERIFY_FUNCTION

It sets the password complexity verification methods.

KISA_VERIFY_FUNCTION

It is the password verification method of KISA (Korea Internet & Security Agency).

ORA12C_VERIFY_FUNCTION

It is the password verification method of Oracle, ORA12C_VERIFY_FUNCTION.

ORA12C_STRONG_VERIFY_FUNCTION

It is the password verification method of Oracle, ORA12C_STRONG_VERIFY_FUNCTION.

VERIFY_FUNCTION_11G

It is the password verification method of Oracle, VERIFY_FUNCTION_11G.

VERIFY_FUNCTION

It is the password verification method of Oracle, VERIFY_FUNCTION.

Description

Account Lockout

The followings are the parameters affecting the account lockout.

For example, when a user and a profile are created as follows.

CREATE PROFILE prof LIMIT
    FAILED_LOGIN_ATTEMPTS 4
    PASSWORD_LOCK_TIME 30;

ALTER USER u1 PROFILE prof;
If the user u1 fails to log in more than four times, the account is locked for 30 days. Then the account lockout is released after 30 days.
If PASSWORD_LOCK_TIME is UNLIMITED, the account lockout should be explicitly released by using ALTER USER statement.
ALTER USER user1 ACCOUNT UNLOCK;

Password Expiration

The followings are the parameters affecting the password expiration.

The password is expired in the following order.

  1. The password is set.

    • The time of the password expiration is set to the time elapsed as much as PASSWORD_LIFE_TIME since when a password is altered.

    • The password expiration status is OPEN, and it allows a normal login.

  1. When a user logs in after the password expiration

    • The login succeeds but the password expiration status becomes EXPIRED (GRACE) and the following warnings occur.

      • ERR-28000(16310): The password will expire in n days

      • ERR-28000(16311): The password will expire soon

      • The SQL standard does not cover the concepts of password expiration.

      • 28000 is the authentication warning or SQL standard status code of an error. 16310, 16311 are the GOLDILOCKS error code.

    • The time of the password expiration is reset to the time elapsed as much as PASSWORD_GRACE_TIME since when a user logged in.

  1. When a user logs in after the grace time

    • The password expiration status becomes EXPIRED, the user can not log in, and the following error occurs.

      • ERR-28000(16312): The password has expired

      • The SQL standard does not cover the concepts of password expiration.

      • 28000 is the authentication warning or SQL standard status code of an error. 16312 is the GOLDILOCKS error code.

      • GOLDILOCKS internal error code of 16312 should be used to control password reentering using program.

Password expiration status transition

Step

Point of time

login

Status

1

The password is changed.

Success

OPEN

2

PASSWORD_LIFE_TIME is elapsed.

Success with warning

EXPIRED(GRACE)

3

PASSWORD_GRACE_TIME is elapsed.

Error

EXPIRED

The following is another example.

CREATE PROFILE prof LIMIT
   PASSWORD_LIFE_TIME 90
   PASSWORD_GRACE_TIME 3;

ALTER USER u1 PROFILE prof;
The example above describes that the user u1 succeeds in login after 90 days of password expiration. However, the user receives a warning that the password expires in three days.
If the password is not changed within three days, the password expires.
Once the password expires, it reminds that the new password should be entered when logging in, and the account access is denied.

Password Reusability

The followings are the parameters affecting the password reusability.

The password reusability of the two parameters above is determined according to the following table.

Conditions for the password reusability

PASSWORD_REUSE_MAX

PASSWORD_REUSE_TIME

Reusable condition

value

value

It can be reused when both conditions of PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are satisfied.

value

UNLIMITED

It can not be reused.

UNLIMITED

value

It can not be reused.

UNLIMITED

UNLIMITED

It can always be reused.

If a profile is created as follows,

CREATE PROFILE prof LIMIT
   PASSWORD_REUSE_MAX 5
   PASSWORD_REUSE_TIME 3;

the password can not be reused if it is the five recent passwords or the password changed within three days.

The following is an example of the user u1's password change history. If the current password is P # _000007 and the current date is 2015-08-08, the reusability of existing passwords is as follows.

Examples of password reusability

Password

Password_date

Password reusability

P#_000001

2015-08-01

It can be reused.

P#_000002

2015-08-02

It can be reused.

P#_000003

2015-08-03

It violates REUSE_MAX.

P#_000004

2015-08-04

It violates REUSE_MAX.

P#_000005

2015-08-05

It violates REUSE_MAX, REUSE_TIME.

P#_000006

2015-08-06

It violates REUSE_MAX, REUSE_TIME.

P#_000007

2015-08-07

It violates REUSE_MAX, REUSE_TIME.

The password change history accumulated for checking the password reusability can be deleted by using the following statement.

ALTER DATABASE CLEAR PASSWORD HISTORY;

DEFAULT profile

When creating the database, the following "DEFAULT" profile is automatically created. The password parameters of the "DEFAULT" profile are as follows.

Configuration of DEFAULT profile

Parameter

Value

FAILED_LOGIN_ATTEMPTS

10

PASSWORD_LOCK_TIME

1

PASSWORD_LIFE_TIME

180

PASSWORD_GRACE_TIME

7

PASSWORD_REUSE_MAX

UNLIMITED

PASSWORD_REUSE_TIME

UNLIMITED

PASSWORD_VERIFY_FUNCTION

NULL

The default values of "DEFAULT" profile have the following characteristics.

DEFAULT profile can not be dropped but it can be altered as follows.

ALTER PROFILE DEFAULT LIMIT ...

Examples

The following is an example of creating the profile to control the account lockout. The account is locked for three days after three consecutive login failures.

gSQL> CREATE PROFILE prof1 LIMIT
        FAILED_LOGIN_ATTEMPTS 3
        PASSWORD_LOCK_TIME 3;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating the profile to control the password expiration. The life time of the password is 90 days, and the grace time of the password is seven days.

gSQL> CREATE PROFILE prof1 LIMIT
        PASSWORD_LIFE_TIME 90 
        PASSWORD_GRACE_TIME 7;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating the profile to control whether the password is reusable. The following example does not verify the old password when changing the password.

gSQL> CREATE PROFILE prof1 LIMIT
        PASSWORD_REUSE_MAX  DEFAULT
        PASSWORD_REUSE_TIME DEFAULT;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating the profile to control the password complexity check.

gSQL> CREATE PROFILE prof1 LIMIT
        PASSWORD_VERIFY_FUNCTION KISA_VERIFY_FUNCTION;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating the profile by setting all parameter.

gSQL> CREATE PROFILE prof1 LIMIT
        FAILED_LOGIN_ATTEMPTS 3
        PASSWORD_LOCK_TIME 3
        PASSWORD_LIFE_TIME 90 
        PASSWORD_GRACE_TIME 7
        PASSWORD_REUSE_MAX  DEFAULT
        PASSWORD_REUSE_TIME DEFAULT
        PASSWORD_VERIFY_FUNCTION KISA_VERIFY_FUNCTION;

Profile created.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not cover the concepts of the profile.

For More Information

Refer to the followings.

CREATE SCHEMA

Function

It defines the schema.

Syntax

<schema definition> ::=
    CREATE SCHEMA <schema name clause>
        [ <schema element> [...] ]
    ;

<schema name clause> ::=
      schema_name
    | AUTHORIZATION user_identifier
    | schema_name AUTHORIZATION user_identifier

<schema element> ::=
      <table definition>
    | <view definition>
    | <index definition>
    | <sequence generator definition>
    | <grant privilege statement>
    | <comment statement>

Invocation and Access Rules

The user should satisfy the following conditions to perform <schema definition>.

Syntax Rules and Parameters

schema_name

It is the schema name to be created.
An identical schema name should not exist in the database.
The length of the schema name should be shorter than 128 bytes.

AUTHORIZATION user_identifier

If the schema name is omitted, a schema with the same name as the user_identifier is created.
If the AUTHORIZATION is not specified, the user_identifier of the user performing the statement is used.

schema_name AUTHORIZATION user_identifier

It specifies the schema name and schema owner to be created.
The owner can not be a role or PUBLIC.

<schema element>

It defines the objects to be created in the schema along with the schema creation.
The schema_element is executed in the listed order, and they are separated by a white space without a comma (,). 
The object can not be defined in a schema which has different name from the schema to be created.

Description

A schema is an object which logically classifies SQL schema objects such as table, view, index, sequence and constraint.
In GOLDILOCKS, the relationship between the user and the schema is 1 : N. In other words, the schema owned by a user may not exist, or the user may own multiple schemas.
The SQL standard does not explicitly define the relationship of the non-schema objects such as user, schema, database, but each DBMS defines the relationship between the non-schema objects as a different concept. Refer to the following note.

The relationship between user and schema in other DBMS





Examples

The following is an example of creating a schema.

gSQL> CREATE SCHEMA s1;

Schema created.

The following is an example of creating a schema and assigning the schema owner.

gSQL> CREATE SCHEMA s1 AUTHORIZATION test;

Schema created.

The following is an example of creating a schema together with the objects which belong to the schema.

gSQL> CREATE SCHEMA s1 
             CREATE TABLE t1 ( id INTEGER, name VARCHAR(128) )
             CREATE INDEX idx_t1_id ON t1 ( id )
             COMMENT ON TABLE t1 IS 'comment on s1.t1'
;

Schema created.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

S071

SQL paths in function and type name resolution

X

F461

Named character sets

X

F171

Multiple schemas per user

O

T332

Extended roles

X

For More Information

Refer to the followings.

CREATE SEQUENCE

Function

It creates a sequence.

Syntax

<sequence generator definition> ::=
    CREATE SEQUENCE [schema_name.] sequence_name 
        [ <sequence generator option> [, ...] ]
    ;

<sequence generator option> ::=
      <sequence generator start with option> 
    | <basic sequence generator option>

<sequence generator start with option> ::=
    START WITH integer

<basic sequence generator option> ::=
      <sequence generator increment by option>
    | <sequence generator maxvalue option>
    | <sequence generator minvalue option>
    | <sequence generator cycle option>
    | <sequence generator cache option>

<sequence generator increment by option> ::=
    INCREMENT BY integer

<sequence generator maxvalue option> ::=
      MAXVALUE integer
    | (NO MAXVALUE | NOMAXVALUE)

<sequence generator minvalue option> ::=
      MINVALUE integer
    | (NO MINVALUE | NOMINVALUE)

<sequence generator cycle option> ::=
      CYCLE 
    | (NO CYCLE | NOCYCLE)

<sequence generator cache option> ::=
      CACHE integer
    | (NO CACHE | NOCACHE)

Invocation and Access Rules

The user should have one of the following privileges to perform <sequence generator definition> statement.
• (CREATE SEQUENCE or CONTROL SCHEMA) ON SCHEMA for the schema to which the sequence 
   belongs
• CREATE ANY SEQUENCE ON DATABASE
The sequence owner is determined as follows.
• The owner of the schema to which the sequence belongs
• If the schema to which the sequence belongs is PUBLIC, then it is the user who executed the statement.

The sequence owner has USAGE ON SEQUENCE WITH GRANT OPTION privilege.

One of the following privileges is required to use the created sequence.
• USAGE ON SEQUENCE for the sequence
• (USAGE SEQUENCE or CONTROL SCHEMA) ON SCHEMA for the schema to which the sequence 
   belongs
• USAGE ANY SEQUENCE ON DATABASE

Syntax Rules and Parameters

sequence_name

It is the sequence name to be created, and it should be a unique name within the schema.
The schema to which the sequence belongs, such as schema_name.sequence_name, can be defined. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of the sequence name should be shorter than 128 bytes.

<sequence generator option>

If any of <sequence generator option> is not used, the following two statements have the same meaning.

<sequence generator start with option>

It defines the first sequence number to be generated.
Depending on the ascending or the descending order, it has the following features.

<sequence generator increment by option>

It defines the interval of sequence numbers.
The constraints and features are as follows.

<sequence generator maxvalue option>

It defines the maximum value of which the sequence can generate.

<sequence generator minvalue option>

It defines the minimum value of which the sequence can generate.

<sequence generator cycle option>

It specifies whether to continue generating a value when the sequence value becomes the maximum value or the minimum value.

<sequence generator cache option>

For quick access of a sequence, it defines the number of the sequence values to be preloaded in memory.
When restarting database, the sequence values loaded in memory are lost, and it starts from the value since being loaded.

Description

The sequence values of the created sequence objects are used by using NEXTVAL and CURRVAL functions.

The sequence value does not have a transaction property. The sequence value maintains the most recent value, even when an error occurs in the SQL statement in which the sequence function is used or when explicit ROLLBACK is performed.
CURRVAL function returns NEXTVAL value from the most recent call by a session. 
Therefore, using this feature, the sequence value obtained by NEXTVAL can still be usable in the other SQL statements. However, when the session does not call NEXTVAL, using CURRVAL function generates an error.

Examples

The object seq1 without the defined sequence options is an ascending sequence of the same meanings as the object seq2 in the following example.

gSQL> CREATE SEQUENCE seq1;

Sequence created.


gSQL> CREATE SEQUENCE seq2 START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 20;

Sequence created.

The following is an example of a sequence which generates an odd value.

gSQL> CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 2;

Sequence created.

The following is an example of generating sequence which repeatedly generates an even number starting from 0 to 1000.

gSQL> CREATE SEQUENCE seq1 START WITH 0 MINVALUE 0 MAXVALUE 1000 INCREMENT BY 2 CYCLE;

Sequence created.

The following is an example of generating a descending sequence starting from -1.

gSQL> CREATE SEQUENCE seq1 INCREMENT BY -1;

Sequence created.

Compatibility

The SQL standard does not define <sequence generator cache option> clause.

SQL standard compatibility

Feature ID

Description

Compatibility

T176

Sequence generator support

O

For More Information

Refer to the followings.

CREATE SYNONYM

Function

It creates a synonym. A synonym is an alternative name for a table, view, sequence, or another synonym, and it can be used in the following statements.

Syntax

<table definition> ::=    
    CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema_name.]synonym_name 
    FOR [schema_name.]object_name
    ;

Invocation and Access Rules

The user should satisfy the following conditions to perform <synonym definition> statement.

Syntax Rules and Parameters

[ OR REPLACE ]

It replaces the existing synonym if the synonym already exists.

[ PUBLIC ]

It is specified when creating public synonym.
If it is omitted, private synonym is created.

synonym_name

It is the synonym name to be created, and it should be a unique name within the schema.
The schema to which the synonym belongs, such as schema_name.synonym_name, can be defined. If schema_name is omitted, default schema name of the user performing the statement is used.
The length of the synonym name should be shorter than 128 bytes.
Public synonym is a non-schema object. Therefore, a schema name can not be specified when creating public synonym by explicitly specifying PUBLIC.

object_name

The schema to which the object belongs, such as schema_name.object_name, can be defined. If schema_name is omitted, default schema name of the user performing the statement is used.
The object types which can specify the object_name are as follows.

Existence of the target object, cycle check and privilege check are performed when executing the statement using the synonym.

Description

A synonym is an alternative name for a table, view, sequence, or another synonym.

If synonym is created, the applications do not need to be modified even when the base object is changed instead only the synonyms should be redefined. Therefore, it is convenient. 
Also, the database security is improved by hiding the objects' real names and their schemas, and the usability is enhanced by changing the object's long name to a shorter name.

The synonym is literally an alternative name so creating the synonym does not mean that the synonym can be used to access the object. The proper privilege is required to access the object.

When executing the statement using the synonym, the object access procedure is as follows.

  1. Find a table of the corresponding name.

  2. If the table does not exist, find the private synonym of the corresponding name.

  3. If the private synonym does not exist, find the public synonym of the corresponding name.

gSQL> CREATE PUBLIC SYNONYM syn1 FOR u1.t1;

Synonym created.

gSQL> CREATE PUBLIC SYNONYM syn2 FOR syn1;

Synonym created.

gSQL> SELECT * FROM syn2;
The example above is the object access procedure in SELECT statement.

  1. It searched for the table syn2, but it does not exist.

  2. It searched for the private synonym syn2, but it does not exist.

  3. It searched for the public synonym syn2, and it exists.

    1. It searched for the table syn1, but it does not exist.

    2. It searched for the private synonym syn1, but it does not exist.

    3. It searched for the public synonym syn2, and it exists.

      1. It searched for the table u1.t1, and it exists.

Examples

The following is an example of creating a private synonym.

gSQL> CREATE SYNONYM MyEmp FOR branch.Employee;

Synonym created.


gSQL> SELECT * FROM MyEmp;

The following is an example of creating a public synonym.

gSQL> CREATE PUBLIC SYNONYM MainEmp FOR main.Employee;

Synonym created.


gSQL> SELECT * FROM MainEmp;

Compatibility

The SQL standard does not define the CREATE SYNONYM statement.

For More Information

Refer to DROP SYNONYM.

CREATE TABLE

Function

It defines a table.

Syntax

<table definition> ::=
    CREATE TABLE table_name
        ( <table element> [, ...] )
        [ <table sharding strategy> ]
        [ <table attribute clause> [...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
    ;

<table element> ::=
      <column definition>
    | <table constraint definition>

<column definition> ::=
    column_name <data type> 
        [ <default clause> | <identity column specification> ]
        [ <column constraint definition> ]

<data type> ::=
      <character string type>
    | <binary string type>
    | <numeric type>
    | <boolean type>
    | <datetime type>
    | <interval type>

<character string type> ::=
      CHARACTER [ ( integer [ <character length units> ] ) ]
    | CHAR [ ( integer [ <character length units> ] ) ]
    | CHARACTER VARYING ( integer [ <character length units> ] )
    | CHAR VARYING ( integer [ <character length units> ] )
    | VARCHAR ( integer [ <character length units> ] )
    | CHARACTER LONG VARYING
    | LONG VARCHAR
  
<character length units> ::=
      CHARACTERS
    | CHAR
    | OCTETS
    | BYTE

<binary string type> ::=
      BINARY [ ( length ) ]
    | BINARY VARYING ( length )
    | VARBINARY ( length )
    | LONG BINARY VARYING
    | LONG VARBINARY

<numeric type> ::=
      <exact numeric type>
    | <approximate numeric type>
    | <native numeric type>

<exact numeric type> ::=
      NUMERIC [ ( precision [, scale ] ) ]
    | SMALLINT
    | INTEGER
    | INT
    | BIGINT

<approximate numeric type> ::=
    | FLOAT [ ( precision ) ]
    | REAL
    | DOUBLE PRECISION

<native numeric type> ::=
      NATIVE_SMALLINT
    | NATIVE_INTEGER
    | NATIVE_BIGINT
    | NATIVE_REAL
    | NATIVE_DOUBLE

<boolean type> ::=
    BOOLEAN

<datetime type> ::=
      DATE
    | TIME [ ( time_precision ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]
    | TIMESTAMP [ ( timestamp_precision ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]

<interval type> ::=
    INTERVAL <interval qualifier>

<interval qualifier> ::=
      <non-second primary datetime field> [ ( interval_leading_field_precision ) ]
          TO { <non-second primary datetime field> | SECOND [ ( interval_fractional_seconds_precision ) ] }
    | <non-second primary datetime field> [ ( interval_leading_field_precision ) ]
    | SECOND [ ( interval_leading_field_precision [, interval_fractional_seconds_precision ] ) ]

<non-second primary datetime field> ::=
      YEAR
    | MONTH
    | DAY
    | HOUR
    | MINUTE

<default clause> ::=
    DEFAULT <default option>

<default option> ::=
      constant
    | NULL
    | expression

<identity column specification> ::=
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 
    [ ( <common sequence generator option> [, ...] ) ]

<common sequence generator option> ::=
      START WITH integer_constant
    | <basic sequence generator option>

<basic sequence generator option> ::=
      INCREMENT BY integer_constant 
    | { MAXVALUE integer_constant | NO MAXVALUE }
    | { MINVALUE integer_constant | NO MINVALUE }
    | { CYCLE | NO CYCLE }
    | { CACHE integer_constant | NO CACHE }

<column constraint definition> ::=
    [ CONSTRAINT constraint_name ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::=
      NOT NULL
    | { UNIQUE | PRIMARY KEY } [ <index name clause> [ <index attributes> ] [ TABLESPACE index_tablespace_name ] ]

<index name clause> ::=
    INDEX index_name

<index attributes> ::=
      <index physical attribute clause>
    | STORAGE ( <segment attr clause> [...] )


<table constraint definition> ::=
    [ CONSTRAINT constraint_name ] <table constraint> [ <constraint characteristics> ]

<table constraint> ::=
      <unique constraint definition> [ <index name clause> [ <index attributes> ] [ TABLESPACE index_tablespace_name ] ]

<unique constraint definition> ::=
    { UNIQUE | PRIMARY KEY } ( <key column element> [, ...] )

<key column element> ::=
    column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]


<table sharding strategy> ::=
      <cloned strategy>
    | <hash sharding strategy>
    | <range sharding strategy>
    | <list sharding strategy>

<cloned strategy> ::=
    CLONED [ <clone placement> ]

<clone placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<hash sharding strategy> ::=
    SHARDING BY [HASH] ( column_list )
    [ <hash shard count> ]
    [ <hash shard placement> ]

<hash shard count> ::=
    SHARD COUNT integer

<hash shard placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<range sharding strategy> ::=
    SHARDING BY RANGE ( column_list )
    { <cluster-wide range shard placement> | <group-specific range shard placement> }

<cluster-wide range shard placement> ::=
    AT CLUSTER WIDE
    <range shard definition> [, ...]

<group-specific range shard placement> ::=
    <group-specific range shard definition> [, ...]

<group-specific range shard definition> ::=
    <range shard definition> AT CLUSTER GROUP group_name

<range shard definition> ::=
    SHARD range_name VALUES LESS THAN ( <range value clause> )

<range value clause> ::=
    <range value> [, ...]

<range value> ::=
      constant
    | MAXVALUE

<list sharding strategy> ::=
    SHARDING BY LIST ( column_name )
    { <cluster-wide list shard placement> | <group-specific list shard placement> }

<cluster-wide list shard placement> ::=
    AT CLUSTER WIDE
    <list shard definition> [, ...]

<group-specific list shard placement> ::=
    <group-specific list shard definition> [, ...]

<group-specific list shard definition> ::=
    <list shard definition> AT CLUSTER GROUP group_name

<list shard definition> ::=
      SHARD shard_name VALUES IN ( <list value clause> )

<list value clause> ::=
    <list value> [, ...]

<list value> ::=
      constant
    | NULL
    | DEFAULT


<table attribute clause> ::=
      [ <table physical attribute clause> ]
    | [ STORAGE ( <segment attr clause> [...] ) ]

<table physical attribute clause> ::=
      PCTFREE integer
    | PCTUSED integer
    | INITRANS integer
    | MAXTRANS integer

<index physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]


<constraint characteristics> ::=
      [ NOT ] DEFERRABLE [ <constraint check time> ]
    | <constraint check time> [ [ NOT ] DEFERRABLE ]

<constraint check time> ::=
      INITIALLY DEFERRED 
    | INITIALLY IMMEDIATE

<table global secondary index clause> ::=
      WITH GLOBAL SECONDARY INDEX [ <index attributes> [...] ] [ TABLESPACE tablespace_name ]
    |  WITHOUT GLOBAL SECONDARY INDEX

Invocation and Access Rules

The differences between the stand-alone database and the cluster database are as follows.

The user should satisfy the following conditions to perform <table definition> statement.

<table sharding strategy> statement can be used in a cluster system.

Syntax Rules and Parameters

table_name

It is the table name to be created and it should be a unique name within the schema.
The schema to which the table belongs, such as schema_name.table_name, can be defined. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of the table name should be shorter than 128 bytes.

<column definition>

It defines the columns which configure the table.
The table should include one or more column definitions. 
It can specify the column data type, default value, automatically generated value, and constraints.

column_name

It is name of the column which configures a table and each column should have a unique name within the table.
The length of the column name should be shorter than 128 bytes.

<data type>

It defines the data type of the column.
When defining the column including automatically generated values(<identity column specification>), its data type should be one of SMALLINT, INTEGER or BIGINT.
For more information about data types, refer to Data Type.

<character length units>

It specifies the length unit of a single character for the character type.

The SQL standard defines CHARACTERS as a default value.

The default value of the char length unit in other DBMS are as follows.

[ <default clause> | <identity column specification> ]

It specifies the default value of a column.
<default clause> and <identity column specification> can not be used together.
When both of them are omitted, the default value is NULL.

<default clause>

The DEFAULT clause defines the default value to be used when DEFAULT is specified in INSERT, UPDATE statements or the corresponding column name is omitted.

The data type of DEFAULT expression should be compatible with the data type of the column.
If the data type is not compatible or the expression is not valid, an error occurs.
--# result: error
CREATE TABLE t1 ( c1 INTEGER DEFAULT 1 / 0 );

ERR-22012(12122): divisor is equal to zero


--# result: success
CREATE TABLE t1 ( c1 INTEGER DEFAULT 1 / 1 );

Table created.
DEFAULT expression can use any built-in functions but it can not use the followings.

<identity column specification>

It defines a column which has automatically generated values.
The table can have only one identity column.
The identity column becomes not nullable column even though NOT NULL constraint is not specified.
<identity column specification> clause can not be specified together with DEFAULT clause.
<identity column specification> clause, like as DEFAULT clause, specifies DEFAULT in INSERT, UPDATE statements or it defines the default value to be used when the column name is omitted.

The generation method is defined as follows.

For more information about <common sequence generator option> and <basic sequence generator option>, which are options to create an identity column, refer to CREATE SEQUENCE.

<column constraint definition>

It defines the following constraints for a column.

constraint_name

It is the constraint name and it can be omitted.
If the constraint_name is omitted, it is automatically set as follows.
If the automatically generated name is duplicated, the constraint_name should be explicitly specified.

The length of the constraint name should be shorter than 128 bytes.

NOT NULL Constraint

NULL is not allowed for the column value.

UNIQUE Constraint

The identical value is not allowed for the column value, but NULL is allowed.

PRIMARY KEY Constraint

NULL or the identical value is not allowed as the column value. A single PRIMARY KEY constraint can be defined on a single table.

<index name clause>

It defines the index name to be created when defining UNIQUE constraint and PRIMARY KEY constraint.

When defining UNIQUE constraint and PRIMARY KEY constraint, if INDEX clause is omitted, an index which satisfies the constraints is automatically created.
"constraint_name" + "INDEX" is added to the name of index which is automatically generated.

<table constraint definition>

<unique constraint definition>

The table constraint definition has the following syntactic difference compared to the column constraint definition.

key column element

It specifies the column to be a target of the key.

<table sharding strategy>

It defines the sharding strategy of a table.
It can be defined as one of the four following strategies.

If it is omitted, it is determined by DEFAULT_SHARDING property value.

<cloned strategy>

It clones all data in a table.

<clone placement>

It defines the placement strategy of a clone.

<hash sharding strategy>

It shards the table data according to the hash value of the sharding key.

SHARDING BY [HASH] ( column_list )

It defines a sharding key for a hash sharding.

<hash shard count>

It defines the number of the hash shards to be sharded.
The number of shards can be defined from 1 to 512.
If it is omitted, the default value is 24.

<hash shard placement>

It defines the placement strategy of a hash shard.

<range sharding strategy>

It shards the table data according to the range value of the sharding key.

SHARDING BY RANGE ( column_list )

It defines a sharding key for the range sharding.

<cluster-wide range shard placement>

It automatically places range shards in all cluster groups of a cluster system.
AT CLUSTER WIDE statement is described before describing <range shard definition>.
Shards can be relocated by using ALTER TABLE name REBALANCE statement when adding a cluster group and a cluster member.
CREATE TABLE t1 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
    AT CLUSTER WIDE
    SHARD s1 VALUES LESS THAN ( 200000 ),
    SHARD s2 VALUES LESS THAN ( 400000 ),
    SHARD s3 VALUES LESS THAN ( 500000 ),
    SHARD s4 VALUES LESS THAN ( 600000 ),
    SHARD s5 VALUES LESS THAN ( 800000 ),
    SHARD s6 VALUES LESS THAN ( MAXVALUE )
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<group-specific range shard placement>

It places range shards in a specified cluster group.
It describes AT CLUSTER GROUP group_name statement which places that shard together with <range shard definition>.
Shards can be automatically relocated by using ALTER TABLE name REBALANCE statement when adding a cluster member to a specified cluster group.
Adding a cluster group does not affect the relocation of the range shard.
CREATE TABLE t1 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
    SHARD s1 VALUES LESS THAN ( 200000 )   AT CLUSTER GROUP g1,
    SHARD s2 VALUES LESS THAN ( 400000 )   AT CLUSTER GROUP g2,
    SHARD s3 VALUES LESS THAN ( 500000 )   AT CLUSTER GROUP g3,
    SHARD s4 VALUES LESS THAN ( 600000 )   AT CLUSTER GROUP g2,
    SHARD s5 VALUES LESS THAN ( 800000 )   AT CLUSTER GROUP g3,
    SHARD s6 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g1
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<range shard definition>

SHARD range_name should be unique in a table.
It can define maximum 512 of <range shard definition>.
The listed <range shard definition> is sorted in an order of <range value clause>, and it should use each different <range value clause>.
The <range shard definition> whose all values are define as MAXVALUE is a MAX shard.
MAX shard should exist, and it should be a single one.
gSQL>
CREATE TABLE t1 
(
   id INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
   AT CLUSTER WIDE
   SHARD s1 VALUES LESS THAN ( 100000 ),
   SHARD s2 VALUES LESS THAN ( 200000 ),
   SHARD s3 VALUES LESS THAN ( MAXVALUE )
;

Table created.
gSQL>
CREATE TABLE t1 
(
   id INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
   AT CLUSTER WIDE
   SHARD s1 VALUES LESS THAN ( 100000 ),
   SHARD s2 VALUES LESS THAN ( 200000 ),
   SHARD s3 VALUES LESS THAN ( 300000 )
;

ERR-42000(16377): MAX shard not defined : 
   SHARD s3 VALUES LESS THAN ( 300000 )
   *
ERROR at line 10:

<range value clause>

<range value> should be a constant or a MAXVALUE (a maximum value).

NULL can not be used as <range value>.

MAXVALUE is always bigger than any other value, and it includes null.

If there are multiple sharding keys, only a MAXVALUE can be specified after the MAXVALUE.

If a sharding key is defined by using multiple columns, a MAX shard which is listed with MAXVALUE for its all values as like the SHARD s3 below should exist.

CREATE TABLE t1 
(
   id INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id, name)
   AT CLUSTER WIDE
   SHARD s1 VALUES LESS THAN ( 100000, MAXVALUE ),
   SHARD s2 VALUES LESS THAN ( 200000, 20000 ),
   SHARD s3 VALUES LESS THAN ( MAXVALUE, MAXVALUE )
;

<list sharding strategy>

It shards the table data according to the listed value of the sharding key.

SHARDING BY LIST ( column_name )

It defines a sharding key for a list sharding.

<cluster-wide list shard placement>

It automatically places list shards in all cluster groups of a cluster system.
AT CLUSTER WIDE statement is described before describing <range shard definition>.
Shards can be relocated by using ALTER TABLE name REBALANCE statement when adding a cluster group and a cluster member.
CREATE TABLE city 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY LIST (name)
    AT CLUSTER WIDE
    SHARD s1 VALUES IN ( 'SEOUL' ),
    SHARD s2 VALUES IN ( 'PUSAN', 'ULSAN', 'DAEGU' ),
    SHARD s3 VALUES IN ( 'DAEJEON', 'GWANGJU' ),
    SHARD s4 VALUES IN ( 'ANSAN', 'GOYANG' ),
    SHARD s5 VALUES IN ( DEFAULT )
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<group-specific list shard placement>

It places list shards in a specified cluster group.
It describes AT CLUSTER GROUP group_name statement which places that shard together with <list shard definition>.
Shards can be automatically relocated by using ALTER TABLE name REBALANCE statement when adding a cluster member to a specified cluster group.
Adding a cluster group does not affect the relocation of the list shard.
CREATE TABLE city 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY LIST (name)
    SHARD s1 VALUES IN ( 'SEOUL' )                   AT CLUSTER GROUP g1,
    SHARD s2 VALUES IN ( 'PUSAN', 'ULSAN', 'DAEGU' ) AT CLUSTER GROUP g2,
    SHARD s3 VALUES IN ( 'DAEJEON', 'GWANGJU' )      AT CLUSTER GROUP g3,
    SHARD s4 VALUES IN ( 'ANSAN', 'GOYANG' )         AT CLUSTER GROUP g2,
    SHARD s5 VALUES IN ( DEFAULT )                   AT CLUSTER GROUP g1
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<list shard definition>

LIST list_name should be unique in a table.
It can define maximum 512 of <list shard definition>.
All <list value> of the listed <list shard definition> should be different each other.
DFFAULT are other values which is not the listed <list value>.
DFFAULT can not be defined together with another value.
A shard including DEFAULT is a DEFAULT shard.
MAX shard should exist, and it should be a single one.
gSQL>
CREATE TABLE t1 
(
   category INTEGER,
   name     VARCHAR(32)
)
SHARDING BY LIST (category)
   AT CLUSTER WIDE
   SHARD s1 VALUES IN ( 1, 3, 5, 7 ),
   SHARD s2 VALUES IN ( 2, 4, 6, 8 ),
   SHARD s3 VALUES IN ( DEFAULT )
;

Table created.
gSQL>

CREATE TABLE t1 
(
   category INTEGER,
   name     VARCHAR(32)
)
SHARDING BY LIST (category)
   AT CLUSTER WIDE
   SHARD s1 VALUES IN ( 1, 3, 5, 7 ),
   SHARD s2 VALUES IN ( 2, 4, 6, 8 ),
   SHARD s3 VALUES IN ( 9, 10 )
;

ERR-42000(16385): DEFAULT shard not defined : 
   SHARD s3 VALUES IN ( 9, 10 )
   *
ERROR at line 10:

<list value clause>

<list value> should be a constant.
NULL or DEFAULT can be used as <list value>.

<table physical attribute clause>

It defines the physical attribute information of the table.

<index physical attribute clause>

It defines the physical attributes of the index.

<segment attr clause>

It describes the information about the space in which the table is stored.

<size clause>

It specifies the file size in bytes. (If it is omitted, the default unit is bytes.)

TABLESPACE tablespace_name

It specifies the tablespace name in which a table is to be stored.
If TABLESPACE clause is omitted, the default tablespace_name of the user performing the statement is used.

TABLESPACE index_tablespace_name

It specifies the tablespace name in which an index is to be stored.
If TABLESPACE clause is omitted, then it uses the index tablespace of the user. 
If the index tablespace of the user is NULL, then the DISK table uses the user's data tablespace and the MEMORY table uses the user's default temporary tablespace.

<constraint characteristics>

It defines characteristics of the constraint.
When defining constraints, the following characteristics can be set.

If <constraint characteristics> is omitted, it is set to NOT DEFERRABLE INITIALLY IMMEDIATE.

DEFERRABLE | NOT DEFERRABLE

It sets whether the constraints checking is deferrable so that the constraints can be checked when executing COMMIT without checking when executing DML statements.

The checking time of the deferrable constraint is controlled by SET CONSTRAINTS.

<constraint check time>

If the constraints are DEFERRABLE, it sets an initial value for the checking time.

For more information about the deferrable constraints, refer to SET CONSTRAINTS.

<table global secondary index clause>

It defines a global secondary index of the table.

Description

Constraint Characteristics

GOLDILOCKS automatically creates an index to check the uniqueness when generating key constraints.

The following columns do not allow NULL value.

Cluster Table

A table manages data using one of the following sharding strategies in a cluster environment.

The sharding strategy is determined considering the followings when creating a table. Tables operated in a cluster system are specified as a code table and a fact table based on its features.

<cloned strategy> is appropriate for a code table, and an appropriate <table sharding strategy> should be determined according to the table access pattern in case of a fact table.

Examples

The following is an example of generating an ordinary table.

gSQL> CREATE TABLE region
(
    r_regionkey   INTEGER
  , r_name        CHAR(25)
  , r_comment     VARCHAR(152)
);

Table created.

The following is an example of specifying the constraints on the columns when creating a table.

gSQL> CREATE TABLE supplier
(
    s_suppkey     INTEGER PRIMARY KEY
  , s_name        CHAR(25) NOT NULL
  , s_address     VARCHAR(40)
  , s_nationkey   INTEGER
  , s_phone       CHAR(15)
  , s_acctbal     NUMERIC(12,2)
  , s_comment     VARCHAR(101)
);

Table created.

The following is an example of specifying the constraint which includes multiple columns when creating a table.

gSQL> CREATE TABLE partsupp
(
    ps_partkey    INTEGER
  , ps_suppkey    INTEGER
  , ps_availqty   INTEGER
  , ps_supplycost NUMERIC(12,2)    
  , ps_comment    VARCHAR(199)
  , CONSTRAINT ps_unique_key UNIQUE(ps_partkey, ps_suppkey)
);

Table created.

The following is an example of specifying whether constraints are deferrable when creating a table.

gSQL> CREATE TABLE t1 
( 
    id     NUMBER        PRIMARY KEY 
                         NOT DEFERRABLE INITIALLY IMMEDIATE
  , name   VARCHAR(128)  CONSTRAINT t1_nn NOT NULL 
                         DEFERRABLE INITIALLY IMMEDIATE
  , addr   VARCHAR(1024) 
  , CONSTRAINT t1_uk UNIQUE ( id, name ) 
                     DEFERRABLE INITIALLY DEFERRED
);

Table created.

gSQL> COMMIT;

Commit complete.

The following is an example of specifying a column including automatically generated values and the default value when creating a table.

CREATE TABLE customer
(
    c_custkey     INTEGER   GENERATED BY DEFAULT AS IDENTITY
  , c_name        VARCHAR(25)
  , c_address     VARCHAR(40) DEFAULT 'N/A'
  , c_nationkey   INTEGER
  , c_phone       CHAR(15)
  , c_acctbal     NUMERIC(12,2)
  , c_mktsegment  CHAR(10)
  , c_comment     VARCHAR(117)
);

Table created.

The following is an example of specifying the tablespace which is to be stored when creating a table.

gSQL> CREATE TABLE lineitem
(
    l_orderkey      INTEGER
  , l_partkey       INTEGER
  , l_suppkey       INTEGER
  , l_linenumber    INTEGER
  , l_quantity      NUMERIC(12,2)
  , l_extendedprice NUMERIC(12,2)
  , l_discount      NUMERIC(12,2)
  , l_tax           NUMERIC(12,2)
  , l_returnflag    CHAR(1)
  , l_linestatus    CHAR(1)
  , l_shipdate      DATE
  , l_commitdate    DATE
  , l_receiptdate   DATE
  , l_shipinstruct  CHAR(25)
  , l_shipmode      CHAR(10)
  , l_comment       VARCHAR(44)
  , PRIMARY KEY (l_orderkey, l_linenumber) INDEX lineitem_pk_idx TABLESPACE mem_temp_tbs
) TABLESPACE mem_data_tbs;

Table created.

The following is an example of defining a cluster-wide cloned table. The table data is copied and placed all over the cluster system.

gSQL>
CREATE TABLE region
(
    r_regionkey   INTEGER
  , r_name        CHAR(25)
  , r_comment     VARCHAR(152)
)
CLONED
AT CLUSTER WIDE
;

Table created.

The following is an example of defining a group-specific cloned table. The table data is copied and placed in a cluster group g1 and g2 specified by a user.

gSQL> 
CREATE TABLE region
(
    r_regionkey   INTEGER
  , r_name        CHAR(25)
  , r_comment     VARCHAR(152)
)
CLONED
AT CLUSTER GROUP g1, g2
;

Table created.

The following is an example of defining a cluster-wide hash sharded table. The table data is divided into 24 shards based on the hash value of a ps_partkey column, and each shard is automatically placed all over the cluster system.

gSQL>
CREATE TABLE partsupp
(
    ps_partkey    INTEGER
  , ps_suppkey    INTEGER
  , ps_availqty   INTEGER
  , ps_supplycost NUMERIC(12,2)    
  , ps_comment    VARCHAR(199)
)
SHARDING BY HASH ( ps_partkey )
SHARD COUNT 24
AT CLUSTER WIDE
;

Table created.

The following is an example of defining a group-specific hash sharded table. The table data is divided into 24 shards based on the hash value of a ps_partkey column, and each shard is automatically placed in the specified cluster groups g2 and g3.

gSQL>
CREATE TABLE partsupp
(
    ps_partkey    INTEGER
  , ps_suppkey    INTEGER
  , ps_availqty   INTEGER
  , ps_supplycost NUMERIC(12,2)    
  , ps_comment    VARCHAR(199)
)
SHARDING BY HASH ( ps_partkey )
SHARD COUNT 24
AT CLUSTER GROUP g2, g3
;

Table created.

The following is an example of defining a cluster-wide range sharded table. The table data is divided into 24 shards based on the range value of D_ID column, and each shard is automatically placed all over the cluster system.

gSQL>
CREATE TABLE DISTRICT (
    D_ID        INTEGER, 
    D_W_ID      INTEGER, 
    D_NAME      VARCHAR(10), 
    D_STREET_1  VARCHAR(20), 
    D_STREET_2  VARCHAR(20), 
    D_CITY      VARCHAR(20), 
    D_STATE     CHAR(2), 
    D_ZIP       CHAR(9), 
    D_TAX       NUMERIC(4,4), 
    D_YTD       NUMERIC(15,2), 
    D_NEXT_O_ID INTEGER,

    PRIMARY KEY (D_W_ID, D_ID) INDEX DISTRICT_PK_IDX
) 
    SHARDING BY RANGE (D_ID)
    AT CLUSTER WIDE
    SHARD s1 VALUES LESS THAN ( 100 ),
    SHARD s2 VALUES LESS THAN ( 200 ),
    SHARD s3 VALUES LESS THAN ( 300 ),
    SHARD s4 VALUES LESS THAN ( 400 ),
    SHARD s5 VALUES LESS THAN ( 500 ),
    SHARD s6 VALUES LESS THAN ( 600 ),
    SHARD s7 VALUES LESS THAN ( 700 ),
    SHARD s8 VALUES LESS THAN ( MAXVALUE );
;

Table created.
The following is an example of defining a group-specific range sharded table. The table data is divided into three range values based on the range value of a NO_D_ID column, and a shard s1 is placed in a cluster group g1, a shard s2 is placed in a cluster group g2, a shard s3 is placed in a cluster group g3.
gSQL>
CREATE TABLE NEW_ORDER
(
    NO_O_ID INTEGER,
    NO_D_ID INTEGER,
    NO_W_ID INTEGER,

    PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID) INDEX NEW_ORDER_PK_IDX
) 
    SHARDING BY RANGE (NO_D_ID)
    SHARD s1 VALUES LESS THAN ( 5 )        AT CLUSTER GROUP g1,
    SHARD s2 VALUES LESS THAN ( 8 )        AT CLUSTER GROUP g2,
    SHARD s3 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g3
;

Table created.

The following is an example of defining a cluster-wide list sharded table. A list shard is divided into five based on a city column, and each shard is automatically placed all over the cluster system.

gSQL>
CREATE TABLE t1 
(
    id   INTEGER
  , name VARCHAR(32)
  , city VARCHAR(128) 
) 
   SHARDING BY LIST (city)
      AT CLUSTER WIDE
      SHARD s1 VALUES IN ( 'seoul' ),
      SHARD s2 VALUES IN ( 'busan', 'ulsan' ),
      SHARD s3 VALUES IN ( 'suwon', 'ansan', 'osan' ),
      SHARD s4 VALUES IN ( 'goyang', 'paju', 'guri' ),
      SHARD s5 VALUES IN ( DEFAULT )            
;

Table created.

The following is an example of defining a group-specific list sharded table. A list shard is divided into five based on a city column, and each shard is placed in a specified cluster group.

gSQL>
CREATE TABLE t1 
(
    id   INTEGER
  , name VARCHAR(32)
  , city VARCHAR(128) 
) 
   SHARDING BY LIST (city)
      SHARD s1 VALUES IN ( 'seoul' )                  AT CLUSTER GROUP g1,
      SHARD s2 VALUES IN ( 'busan', 'ulsan' )         AT CLUSTER GROUP g2,
      SHARD s3 VALUES IN ( 'suwon', 'ansan', 'osan' ) AT CLUSTER GROUP g1,
      SHARD s4 VALUES IN ( 'goyang', 'paju', 'guri' ) AT CLUSTER GROUP g2,
      SHARD s5 VALUES IN ( DEFAULT )                  AT CLUSTER GROUP g3
;

Table created.

A table T1 is created without a global secondary index.

gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) )  WITHOUT GLOBAL SECONDARY INDEX;

Table created.

A global secondary index is created after creating a table T1.

gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) )  WITH GLOBAL SECONDARY INDEX;

Table created.

A global secondary index of table T1 is created in a tablespace USER_DATA_TBS as a logging index after creating a table T1.

gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) ) 
      WITH GLOBAL SECONDARY INDEX
      TABLESPACE USER_DATA_TBS;

Table created.
A global secondary index of table T1 is created in a tablespace USER_TEMP_TBS as a nologging  index after creating a table T1.
gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) ) 
      WITH GLOBAL SECONDARY INDEX
      TABLESPACE USER_TEMP_TBS;

Table created.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

T171

LIKE clause in table definition

X

F531

Temporary tables

X

S051

Create table of type

X

S043

Enhanced reference types

X

S081

Subtables

X

T173

Extended LIKE clause in table definition

X

T180

System-versioned tables

X

F692

Extended collation support

X

T174

Identity columns

O

T175

Generated columns

X

S071

SQL paths in function and type name resolution

X

F321

User authorization

O

T322

Extended roles

X

F762

CURRENT_CATALOG

O

F763

CURRENT_SCHEMA

O

For More Information

Refer to the followings.

CREATE TABLE AS SELECT

Function

It creates a new table from the query result.

Syntax

<table definition: AS query expression> ::=
    CREATE TABLE table_name 
        [ ( column_name [, ...] ) ]
        [ <table sharding strategy> ]
        [ <table attribute clause> [, ...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
        AS <query expression> [ WITH [ NO ] DATA ]
    ;

<table sharding strategy> ::=
      <cloned strategy>
    | <hash sharding strategy>
    | <range sharding strategy>
    | <list sharding strategy>

<cloned strategy> ::=
    CLONED [ <clone placement> ]

<clone placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<hash sharding strategy> ::=
    SHARDING BY [HASH] ( column_list )
    [ <hash shard count> ]
    [ <hash shard placement> ]

<hash shard count> ::=
    SHARD COUNT integer

<hash shard placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<range sharding strategy> ::=
    SHARDING BY RANGE ( column_list )
    { <cluster-wide range shard placement> | <group-specific range shard placement> }

<cluster-wide range shard placement> ::=
    AT CLUSTER WIDE
    <range shard definition> [, ...]

<group-specific range shard placement> ::=
    <group-specific range shard definition> [, ...]

<group-specific range shard definition> ::=
    <range shard definition> AT CLUSTER GROUP group_name

<range shard definition> ::=
    SHARD range_name VALUES LESS THAN ( <range value clause> )

<range value clause> ::=
    <range value> [, ...]

<range value> ::=
      constant
    | MAXVALUE

<list sharding strategy> ::=
    SHARDING BY LIST ( column_name )
    { <cluster-wide list shard placement> | <group-specific list shard placement> }

<cluster-wide list shard placement> ::=
    AT CLUSTER WIDE
    <list shard definition> [, ...]

<group-specific list shard placement> ::=
    <group-specific list shard definition> [, ...]

<group-specific list shard definition> ::=
    <list shard definition> AT CLUSTER GROUP group_name

<list shard definition> ::=
      SHARD shard_name VALUES IN ( <list value clause> )

<list value clause> ::=
    <list value> [, ...]

<list value> ::=
      constant
    | NULL
    | DEFAULT


<table attribute clause> ::=
      [ <table physical attribute clause> ]
    | [ STORAGE ( <segment attr clause> [...] ) ]

<table physical attribute clause> ::=
      PCTFREE integer
    | PCTUSED integer
    | INITRANS integer
    | MAXTRANS integer

<index physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

<table global secondary index clause> ::=
      WITH GLOBAL SECONDARY INDEX [ <index attributes> [...] ] [ TABLESPACE tablespace_name ]
    |  WITHOUT GLOBAL SECONDARY INDEX

Invocation and Access Rules

A user should satisfy the following conditions to perform <table definition:AS query expression>statement.

Syntax Rules and Parameters

table_name

It is the table name to be created.
For more information, refer to table_name.

column_name_list

These are the names of the columns that configure the table, and it should be unique names within the table.
The number of columns should be as same as the number of result columns in SELECT clause.
If not specified, the column names of SELECT clause in <query expression> are used.
However, if an expression (such as a function, operation, or subquery) is used instead of a column in SELECT clause, the alias or column name should be specified.
The length of the column name should be shorter than 128 bytes.

WITH [NO] DATA

If WITH DATA is specified, the result of SELECT clause is inserted to the table to be created.
If WITH NO DATA is specified, the result of SELECT clause is not inserted to the table to be created. 
If not specified, it is operated as same as when WITH DATA is specified.

Other Syntax

For more information about other syntaxes, refer to the syntax in CREATE TABLE statement.

Description

When executing CREATE TABLE AS SELECT, if a column including a  NOT NULL constraint is specified in SELECT list, the NOT NULL constraint is also created in the new table.
However, if the NOT NULL constraint is deferrable, then NOT NULL constraint is not created in the new table.
On the other hand, the NOT NULL constraint is not created in the new table if NOT NULL constraint was not explicitly created but there is NOT NULL property such as primary key column or identity column.

Examples

The following is an example of executing CREATE TABLE AS SELECT statement.

gSQL> CREATE TABLE recent_orders 
                AS SELECT order_id, order_item, order_date
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
Table created.

The following is an example of specifying the column name.

gSQL> CREATE TABLE recent_orders ( order_id, order_item, order_date )
                AS SELECT order_id, order_item, order_date
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
Table created.

The following is an example of a function in SELECT list.

gSQL> CREATE TABLE recent_orders ( order_date, order_count )
                AS SELECT order_date, COUNT(*) 
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
                   GROUP BY order_date;
Table created.

The following is an example of the statement including WITH DATA.

gSQL>CREATE TABLE orders 
( 
    order_id   NUMBER        
  , order_item VARCHAR(128)  
  , order_date DATE
);
gSQL> COMMIT;
gSQL> INSERT INTO orders VALUES ( 1, 'Pen', '2010-01-01' );
gSQL> INSERT INTO orders VALUES ( 2, 'Book', '2015-03-03' );
gSQL> COMMIT;
gSQL> CREATE TABLE recent_orders
                AS SELECT order_id, order_item, order_date 
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
      WITH DATA;
Table created.
gSQL> SELECT COUNT(*) FROM recent_orders;

COUNT(*)
--------
       1

1 row selected.

The following is an example of the statement including WITH NO DATA.

gSQL>CREATE TABLE orders 
( 
    order_id   NUMBER        
  , order_item VARCHAR(128)  
  , order_date DATE
);
gSQL> COMMIT;
gSQL> INSERT INTO orders VALUES ( 1, 'Pen', '2010-01-01' );
gSQL> INSERT INTO orders VALUES ( 2, 'Book', '2015-03-03' );
gSQL> COMMIT;
gSQL> CREATE TABLE recent_orders
                AS SELECT order_id, order_item, order_date 
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
      WITH NO DATA;
Table created.
gSQL> SELECT COUNT(*) FROM recent_orders;

COUNT(*)
--------
       0

1 row selected.

Compatibility

CREATE TABLE AS SELECT statement follows SQL standard. However, the following is an extension of SQL standard.

SQL standard compatibility

Feature ID

Description

Compatibility

T172

AS subquery clause in table definition

O

For More Information

Refer to the followings.

CREATE TABLESPACE

Function

It creates a tablespace.

Syntax

<create tablespace statement> ::=
      <memory data tablespace statement>
    | <memory temporary tablespace statement>
    ;

Invocation and Access Rules

CREATE TABLESPACE ON DATABASE privilege is required to perform <create tablespace statement>.

The user who performed the statement has CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

One of the following privileges is required to create the objects on the created tablespace.

Syntax Rules and Parameters

<memory data tablespace statement>

It is a memory temporary tablespace to store the no logging indexes or the temporary objects such as intermediate results which are generated during the query processing.
The reserved word MEMORY can be omitted.

<memory data tablespace clause>

It defines a memory data tablespace.
For more information, refer to CREATE MEMORY DATA TABLESPACE.

<memory temporary tablespace definition>

It defines a memory temporary tablespace.
For more information, refer to CREATE MEMORY TEMPORARY TABLESPACE.

Description

For more information, refer to the description of each detailed statement.

Example

For more information, refer to usage example of each detailed statement.

Compatibility

The SQL standard does not cover the concepts of the tablespace.

For More Information

Refer to the followings.

CREATE USER

Function

It defines a database user.

Syntax

<user definition> ::=
    CREATE USER user_identifier IDENTIFIED BY password
    [ PROFILE { profile_name | DEFAULT | NULL } ]
    [ PASSWORD EXPIRE ]
    [ ACCOUNT { LOCK | UNLOCK } ]
    [ DEFAULT TABLESPACE tablespace_name ]
    [ TEMPORARY TABLESPACE tablespace_name ]
    [ INDEX TABLESPACE { tablespace_name | NULL } ]
    [ <schema clause> ]
    ;

<schema clause> ::=
      WITH SCHEMA [schema_name]
    | WITHOUT SCHEMA

Invocation and Access Rules

CREATE USER ON DATABASE privilege is required to perform <user definition>.

The created user, user_identifier, has the privilege, which is the owner the schema created by using <schema clause>.

A separate privilege is not granted to the created user_identifier.

The appropriate privileges should be granted to user_identifier to access and perform SQL statements.

Syntax Rules and Parameters

user_identifier

It is the username to be created.
The identical username (user identifier) or role (role name) should not exist.
The length of user_identifier should be shorter than 128 bytes.

password

It is the user's password to be created. It is encrypted and stored.
The length of password should be shorter than 128 bytes.
The password is case sensitive.
The password should start with an alphabetic character, and it can include alphabetic characters, numbers, underscore (_), and $. 
The other special characters should be enclosed in double quotes (").

PROFILE { profile_name | DEFAULT | NULL }

The profile for password management policy is assigned.

If PROFILE clause is omitted, it is as same as PROFILE NULL, and the profile is not applied.
For more information about the password management policy, refer to CREATE PROFILE.

PASSWORD EXPIRE

It expires the user's password.
It is used when a user attempts to change the password by force before login.

ACCOUNT { LOCK | UNLOCK }

DEFAULT TABLESPACE tablespace_name

It specifies the default TABLESPACE to store objects such as the table created by the user, the indexes (with NOLOGGING option).
If DEFAULT TABLESPACE clause is omitted, default data tablespace(MEM_DATA_TBS) is specified, which was defined when creating DATABASE.

TEMPORARY TABLESPACE tablespace_name

It specifies the TABLESPACE which stores the temporary tables created by a user, indexes (NO LOGGING), and the intermediate results generated by the query processing.
If TEMPORARY TABLESPACE clause is omitted, default temporary tablespace (MEM_TEMP_TBS) is specified, which was defined when creating DATABASE.

INDEX TABLESPACE { tablespace_name | NULL }

It specifies the default TABLESPACE which stores the index objects created by a user.

If INDEX TABLESPACE clause is omitted, then it is INDEX TABLESPACE NULL.

<schema clause>

It creates a schema which a user uses by default.
The schema name should be unique in the database.
If <schema clause> is not specified, the default value is WITH SCHEMA and the schema is created whose name is as same as user_identifier.
The schema to be owned by the user can be additionally created by using CREATE SCHEMA statement.

Description

A user is an authorization object which consists of a set of privileges.
When <user definition> statement is executed for the first time, a user without any privilege is created, and the appropriate privileges should be granted as follows.
In GOLDILOCKS, the relationship between user and schema is 1 : N.
In other words, a user does not own any schema, or a user can own multiple schemas.
The SQL standard does not explicitly define the relationship of the non-schema objects such as a user, a schema, or a database. Each DBMS defines the relationship of non-schema objects in different concept as follows.

The relationship between user and schema in other DBMS.





Examples

At least the following privileges should be granted to create a user, and for the created user to create the objects, manipulate data.

The following is an example of creating a user and granting privileges.

• Create a user.

gSQL> CREATE USER u1 IDENTIFIED BY u1_password
             DEFAULT   TABLESPACE mem_data_tbs
             TEMPORARY TABLESPACE mem_temp_tbs
             INDEX TABLESPACE NULL;

User created.

gSQL> COMMIT;

Commit complete.

• Grant database privileges.

gSQL> GRANT CREATE SESSION ON DATABASE TO u1;

Grant succeeded.

COMMIT;

Commit complete.

• Grant schema privileges.

GRANT CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, ADD CONSTRAINT 
      ON SCHEMA u1 TO u1;

Grant succeeded.

COMMIT;

Commit complete.

• Grant tablespace privileges.

GRANT CREATE OBJECT ON TABLESPACE mem_data_tbs TO u1;

Grant succeeded.

GRANT CREATE OBJECT ON TABLESPACE mem_temp_tbs TO u1;

Grant succeeded.

COMMIT;

Commit complete.

The following is an example of creating objects by the user.

• It needs CREATE SESSION ON DATABASE.
gSQL> \connect u1 u1_password
• It needs CREATE TABLE ON SCHEMA u1. 
• It needs CREATE OBJECT ON TABLESPACE mem_data_tbs.
gSQL> CREATE TABLE u1.t1 ( c1 INTEGER, c2 INTEGER ) TABLESPACE mem_data_tbs;

Table created.

gSQL> COMMIT;
• It needs CREATE INDEX ON SCHEMA u1. 
• It needs CREATE OBJECT ON TABLESPACE mem_temp_tbs.
gSQL> CREATE INDEX u1.idx ON t1 (c2) TABLESPACE mem_temp_tbs;

Index created.

gSQL> COMMIT;
• It needs ADD CONSTRAINT ON SCHEMA u1.
gSQL> ALTER TABLE t1 ADD CONSTRAINT u1.t1_pk PRIMARY KEY (c1) ;

Table altered.

gSQL> COMMIT;
• It needs CREATE SEQUENCE ON SCHEMA u1.
gSQL> CREATE SEQUENCE u1.seq;

Sequence created.

gSQL> COMMIT;

gSQL> INSERT INTO u1.t1 VALUES ( u1.seq.NEXTVAL, u1.seq.NEXTVAL );

1 row created

gSQL> COMMIT;

Compatibility

The SQL standard covers the concepts of the user, but it does not define the SQL statements associated with the creation and deletion of user.

For More Information

Refer to the followings.

CREATE VIEW

Function

It defines a view.

Syntax

<view definition> ::=
    CREATE [ OR REPLACE ] [ FORCE | NO FORCE ] 
        VIEW view_name [ ( column_name [, ...] ) ]
        AS <query expression>
    ;

Invocation and Access Rules

The user should satisfy the following conditions to perform <view definition> statement.

Syntax Rules and Parameters

[ OR REPLACE ]

It replaces the existing view when a view already exists.

[ FORCE | NO FORCE ]

view_name

It is the view name to be created, and it should be a unique name within the schema.
The schema to which the view belongs, such as schema_name.view_name, can be defined. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of the view name must be shorter than 128 bytes.

[ ( column_name [, ...] ) ]

It defines a column name which will configure the view.
Each column name should be unique within the view.
The number of columns should be as same as the number of result columns in SELECT clause.
If the list of column names is omitted, the column names of SELECT clause in <query expression> are used.

AS <query expression>

It is the SELECT query which will create a view.

<query expression> can not include the following variables.

Description

A view is the object which gave a name to the query, and it is used in the similar way of a table.

When executing a query including the view, that view is interpreted as the query included in the view definition. If the table referenced by the view is altered, as the following example, the asterisk (*) included in the view definition is automatically interpreted based on the altered table information.
gSQL> CREATE VIEW v1 AS SELECT * FROM t1;
gSQL> COMMIT;
gSQL> SELECT * FROM v1;

ID NAME     
-- ---------
 1 leekmo   
 2 mkkim    
 3 egonspace

3 rows selected.

gSQL> ALTER TABLE t1 ADD COLUMN ( dept_id  INTEGER,  addr  VARCHAR(1024) );
gSQL> COMMIT;

gSQL> select * from v1;

ID NAME      DEPT_ID ADDR
-- --------- ------- ----
 1 leekmo       null null
 2 mkkim        null null
 3 egonspace    null null

3 rows selected.

The view is affected if the view is created on the query including errors by using FORCE option, the tables referred by the view, or views are altered or deleted.

This information can be retrieved from INFORMATION_SCHEMA.VIEWS information.

The maximum number of creating views and the maximum number of columns to be created within a view is not limited. Therefore, they can be created as many as the storage space is available.

Examples

The following is an example of creating a view.

gSQL> CREATE VIEW v1 AS SELECT * FROM t1 WHERE dept_id = 101;

View created.

The following is an example of defining the column names when defining a view.

gSQL> CREATE VIEW v1 ( v_id, v_name )
          AS SELECT id, name FROM t1 WHERE dept_id = 101;

View created.

The following is an example of removing the existing view and creating a new view by using REPLACE option.

gSQL> CREATE OR REPLACE VIEW v1(id, name) 
             AS SELECT id, name FROM t1;

View created.

The following is an example of forcing to create a view by using FORCE option even when the referenced object by the view does not exist.

gSQL> CREATE FORCE VIEW v1 
          AS SELECT * FROM t1 WHERE dept_id = 101;

ERR-01000(16243): Warning: View created with compilation errors
ERR-42000(16040): table or view does not exist : 
    AS SELECT * FROM t1 WHERE dept_id = 101
                     *
ERROR at line 2:

View created.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

T131

Recursive query

O

F751

View CHECK enhancements

X

S043

Enhanced reference types

X

T111

Updatable joins, unions, and columns

X

F852

Top-level <order by clause> in views

O

F864

Top-level <result offset clause> in views

O

F859

Top-level <fetch first clause> in views

O

S081

Subtables

X

For More Information

Refer to the followings.

DECLARE cursor_name

Function

It declares a cursor.

Syntax

<declare cursor> ::=
    DECLARE cursor_name <cursor properties> { FOR | IS } <cursor specification>
    ;

<cursor properties> ::=
      [ <cursor sensitivity> ] [ <cursor scrollability>] ] CURSOR [ <cursor holdability> ] 
    | [ <odbc cursor type] CURSOR [ <cursor holdability> ] 

<cursor sensitivity> ::=
      INSENSITIVE
    | SENSITIVE
    | ASENSITIVE

<cursor scrollability> ::=
      NO SCROLL
    | SCROLL

<cursor holdability> ::=
      WITH HOLD
    | WITHOUT HOLD

<odbc cursor type> ::=
      STATIC
    | KEYSET

<cursor specification> ::=
      statement_name
    | <cursor query>  [ <updatability clause> ]

<cursor query> ::=
      <select statement>
    | <insert returning query statement>
    | <update returning query statement>
    | <delete returning query statement>

<updatability clause> ::=
      FOR READ ONLY 
    | FOR UPDATE [ OF <column name list> ] [ <lock wait mode> ]

<lock wait mode> ::=
    | WAIT
    | WAIT second
    | NOWAIT

Invocation and Access Rules

The dynamic cursor which uses statement_name can be used in an embedded SQL.

An appropriate access privilege is required depending on <cursor query> types.
For more information about the access privileges, refer to the followings.

Syntax Rules and Parameters

cursor_name

It is the cursor name to be declared.
It should be a unique name within the session.
The length of cursor name should be shorter than 128 bytes.

{ FOR | IS }

Either FOR or IS is used as a syntax keyword in SQL standard.

<cursor properties>

It defines the cursor properties.

updatable query

To use a cursor property such as SENSITIVE or FOR UPDATE, a query of the cursor should identify changes in rows of the base table, or it should be the updatable query which can acquire a lock on the row.

The updatable query should satisfy all of the following conditions.

<cursor sensitivity>

It determines whether the following data changes that affect the query results can be queried when operating the cursor.

<cursor scrollability>

It specifies whether the result set of the cursor can be fetched sequentially or non-sequentially.

<cursor holdability>

It determines whether the cursor is maintained after the cursor is OPEN and the transaction is committed.

<odbc cursor type>

It is the cursor type in the ODBC standard, and it has the SCROLL property.

Sensitivity according to FOR [UPDATE / READ ONLY] statement and the query type

Updatability

Query type

Sensitivity

FOR UPDATE

Updatable query

SENSITIVE

FOR UPDATE

Non-updatable query

Query error

FOR READ ONLY

Any query

INSENSITIVE

N/A

Updatable query

SENSITIVE

N/A

Non-updatable query

INSENSITIVE

<cursor specification>

It defines a query which is a target of the cursor.
If statement_name is used, a dynamic cursor whose query has not been defined is declared.
If <cursor query> is used, a standing cursor whose query is defined is declared.

statement_name

It is a statement_name to be referenced by the cursor, and it can be used in an embedded SQL.
statement_name should exist before performing <declare cursor> statement, and the SQL statement referenced by statement_name should be the query prepared by PREPARE statement_name statement.
If it is not a query, an error occurs when executing OPEN cursor_name statement.

<cursor query>

For more information about available query types in the cursor, refer to the followings.

<updatability clause>

It specifies whether to change rows by using the cursor.

FOR UPDATE OF …

It lists the columns associated with the lock obtaining when OPENing the cursor.

<lock wait mode>

It is used together with FOR UPDATE clause, and it specifies the lock acquisition method.

Description

When controlling the query property, using DECLARE CURSOR, OPEN, FETCH, CLOSE statements have the performance burden compared to using the cursor with the ODBC or JDBC statements. It is because using DECLARE CURSOR, OPEN, FETCH, CLOSE statements control the cursor of the server.

Before executing the query, the cursor property can be controlled by ODBC statement and JDBC statement. The SQL cursor property control method by DECLARE CURSOR statement, and cursor property control method by the ODBC standard and the JDBC standard are as follows.
Controlling the cursor property of ODBC/ JDBC

Property

GOLDILOCKS

cursor property

ODBC standard cursor property

JDBC standard cursor property

Sensitivity

INSENSITIVE

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SENSITIVITY, SQL_INSENSITIVE, len)

It can not be set.

SENSITIVE

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SENSITIVITY, SQL_SENSITIVE, len)

It can not be set.

ASENSITIVE

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SENSITIVITY, SQL_UNSPECIFIED, len)

It can not be set.

Scrollability

NO SCROLL

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SCROLLABLE, SQL_NONSCROLLABLE, len)

It can not be set.

SCROLL

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SCROLLABLE, SQL_SCROLLABLE, len)

It can not be set.

Holdability

WITHOUT HOLD

It can not be set.

java.sql.Connection::prepareStatement( query, type, conc, ResultSet.CLOSE_CURSORS_AT_COMMIT )

WITH HOLD

It can not be set.

java.sql.Connection::prepareStatement( query, type, conc, ResultSet.HOLD_CURSORS_OVER_COMMIT )

SQL cursor declaration corresponding to ODBC cursor type is as follows.

SQL cursor declaration corresponding to ODBC cursor type

ODBC cursor type

SQL cursor declaration

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY, len)

NO SCROLL CURSOR

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, len)

STATIC CURSOR

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN, len)

KEYSET CURSOR

SQL cursor declaration corresponding to JDBC cursor type is as follows.

SQL cursor declaration corresponding to JDBC cursor type

JDBC cursor type

SQL cursor declaration

java.sql.Connection::prepareStatement( query, ResultSet.TYPE_FORWARD_ONLY, conc, hold )

INSENSITIVE NO SCROLL CURSOR

java.sql.Connection::prepareStatement( query, ResultSet.TYPE_SCROLL_INSENSITIVE, conc, hold )

INSENSITIVE SCROLL CURSOR

java.sql.Connection::prepareStatement( query, ResultSet.TYPE_SCROLL_SENSITIVE, conc, hold )

KEYSET CURSOR

Examples

The following is an example of declaring the cursor by using interactive SQL (gsql), and using it.

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur1;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

The following is an example of declaring KEYSET cursor, sequentially searching, then completing the transaction of UPDATE, DELETE statements, and searching for it in the reverse direction.

gSQL> DECLARE cur_keyset KEYSET CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur_keyset;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.


gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

no rows fetched.

gSQL> UPDATE t1 SET data = 'new data_2' WHERE id = 2;

1 row updated.

gSQL> COMMIT;

Commit complete.

gSQL> DELETE FROM t1 WHERE id = 4;

1 row deleted.

gSQL> COMMIT;

Commit complete.

gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.


gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

no rows fetched.


gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA    
---- ----------
   2 new data_2

1 row fetched.

gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> CLOSE cur_keyset;

Cursor closed.

The following is an example of declaring SCROLL cursor, and using the cursor through the fetch orientation.

gSQL> DECLARE cur_scroll SCROLL CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur_scroll;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;
V_ID V_DATA
---- ------
   5 data_5

1 row fetched.


gSQL> FETCH PRIOR cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.


gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.


gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.


gSQL> FETCH RELATIVE -1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.


gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> CLOSE cur_scroll;

Cursor closed.

Compatibility

<declare cursor> statement has the following differences compared to the SQL standard.

SQL standard compatibility

Feature ID

Description

Compatibility

F831

Full cursor update

O

T231

Sensitive cursors

O

F791

Insensitive cursors

O

F431

Read-only scrollable cursors

O

T471

Result sets return value

X

T551

Optional key words for default syntax

O

T111

Updatable joins, unions, and columns

X

B031

Basic dynamic SQL

O

For More Information

Refer to the followings.

DELETE FROM

Function

It deletes rows in a table.

Syntax

<delete statement: searched> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]

<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

Invocation and Access Rules

One of the following privileges is required to perform <delete statement: searched>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be deleted.
It defines the schema to which the table belongs such as schema_name.table_name. 
If schema_name is omitted, the default schema name of the user performing the statement is used.

[ AS alias_name ]

It is the alias of table_name.

WHERE <search condition>

It deletes the rows which satisfy WHERE condition.
If WHERE condition is omitted, it deletes all rows.
For more information about WHERE condition, refer to where clause of SELECT statement.

<result offset clause>

It specifies the number of rows to be skipped among the query result.
For more information, refer to offset limit clause of SELECT statement.

<fetch limit clause>

The following two ways are used to specify the number of rows to be fetched.

Description

Differences among DELETE-related Statements

Examples

The following is an example of DELETE statement.

gSQL> DELETE FROM t1 WHERE id > 3;

2 rows deleted.

The following is an example of skipping some rows (two rows) and deleting some rows (two rows) among the rows which satisfy the conditions by using <result offset clause> and <fetch first clause> clauses.

gSQL> DELETE FROM t1 OFFSET 2 FETCH 2;

2 rows deleted.


gSQL> SELECT * FROM t1 ORDER BY 1;

ID DATA  
-- ------
 1 data_1
 2 data_2
 5 data_5

3 rows selected.

Compatibility

The SQL standard does not define the following clauses of DELETE statement.

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

T111

Updatable joins, unions, and columns

X

For More Information

Refer to the followings.

DELETE FROM name RETURNING

Function

It deletes rows of the table, and retrieves the deleted rows.

Syntax

<delete returning query statement> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning clause>
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]

<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

<returning clause> ::=
    { RETURN | RETURNING } { * | { <value expression> [ [AS] alias_name] } [, ...] }

Invocation and Access Rules

The user should satisfy the following conditions to perform <delete returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be deleted.

[ AS alias_name ]

It is the alias of table_name.

WHERE <search condition>

It deletes the rows which satisfy WHERE condition.
For more information, refer to DELETE FROM statement.

<result offset clause>

It specifies the number of rows to be skipped among the query result.
For more information, refer to DELETE FROM statement.

<fetch first clause>

It specifies the number of rows to be fetched.
For more information, refer to DELETE FROM statement.

<limit clause>

It specifies the number of rows to be fetched, or it simultaneously specifies both the number of rows to be skipped and the number of rows to be fetched.
For more information, refer to DELETE FROM statement.

<returning clause>

It sets the deleted rows as a result set, and it specifies the columns to be searched from the set.

The keywords RETURNING and RETURN have the same meaning.

Description

For more information, refer to Differences among DELETE-related Statements.

Examples

The following is an example of deleting rows which satisfy the condition, and searching for the deleted rows.

gSQL> DELETE FROM t1 WHERE id > 3 RETURNING *;

ID DATA  
-- ------
 4 data_4
 5 data_5

2 rows deleted.

The following is an example of querying information of the deleted rows by using operation in RETURNING clause.

gSQL> DELETE FROM t1 
             WHERE id > 3 
             RETURNING 'ID: ' || id || ', DATA: ' || data AS id_data;

ID_DATA            
-------------------
ID: 4, DATA: data_4
ID: 5, DATA: data_5

2 rows deleted.

Compatibility

The SQL standard does not cover <delete returning query statement>.

For More Information

Refer to the followings.

DELETE FROM name RETURNING .. INTO

Function

It deletes a single row from the table, and the value of the deleted row is obtained into the host variable.

Syntax

<delete returning query statement> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning into clause>
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }


<returning into clause> ::=
    { RETURN | RETURNING } { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO variable_name [, ...]

Invocation and Access Rules

The user should satisfy the following conditions to perform <delete returning into statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be deleted.

[ AS alias_name ]

It is the alias of table_name.

WHERE <search condition>

It deletes the rows which satisfy WHERE condition.
For more information, refer to DELETE FROM statement.

<result offset clause>

It specifies the number of rows to be skipped among the query result.
For more information, refer to DELETE FROM statement.

<fetch first clause>

It specifies the number of rows to be fetched.
For more information, refer to DELETE FROM statement.

<limit clause>

It specifies the number of rows to be fetched, or it simultaneously specifies both the number of rows to be skipped and the number of rows to be fetched.
For more information, refer to DELETE FROM statement.

<returning into clause>

Description

The number of rows to be deleted should be equal to or less than one.
If two or more rows are deleted, then an error occurs.
For more information, refer to Differences among DELETE-related Statements.

Example

The following is an example of deleting rows and obtaining the value of deleted rows into the host variable in an interactive SQL (gsql).

gSQL> \var v_id    INTEGER
gSQL> \var v_data  VARCHAR(128)

gSQL> DELETE FROM t1 WHERE id = 3 RETURNING id, data INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row deleted.

Compatibility

The SQL standard does not cover <delete returning into statement>.

For More Information

Refer to the followings.

DELETE FROM name WHERE CURRENT OF cursor_name

Function

It deletes a single row which the cursor indicates.

Syntax

<delete statement: positioned> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        WHERE CURRENT OF cursor_name
    ;

Invocation and Access Rules

The privilege to perform DELETE FROM statement is required to perform <delete statement: positioned>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be deleted.

[ AS alias_name ]

It is the alias of table_name.

cursor_name

The cursor corresponding to cursor_name should satisfy the following conditions.

Description

For more information, refer to Differences among DELETE-related Statements.

Example

The following is an example of declaring the FOR UPDATE cursor, and deleting rows by using the cursor in interactive SQL (gsql).

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1 FOR UPDATE;

Cursor declared.


gSQL> OPEN cur1;

Cursor is open.


gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> DELETE FROM t1 WHERE CURRENT OF cur1;

1 row deleted.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> DELETE FROM t1 WHERE CURRENT OF cur1;

1 row deleted.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

gSQL> SELECT id, data FROM t1 ORDER BY 1;

ID DATA  
-- ------
 1 data_1
 3 data_3
 5 data_5

3 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

S111

ONLY in query expressions

X

B031

Basic dynamic SQL

O

For More Information

Refer to the followings.

DROP AUDIT POLICY

Function

It drops an audit policy.

Syntax

<drop audit policy statement> ::= 
    DROP AUDIT POLICY [ IF EXISTS ] policy_name
;

Invocation and Access Rules

AUDIT SYSTEM ON DATABASE privilege is required to perform <drop audit policy statement>.

Syntax Rules and Parameters

IF EXISTS

An error does not occur even when a policy_name does not exist.

policy_name

It is the name of an audit policy object to be dropped.

Description

The audit policy object which is already activated can not be dropped. In this case, the audit policy should be deactivated by using NOAUDIT POLICY statement.

Examples

The following is an example of dropping an audit policy.

DROP AUDIT POLICY policy_table;

Compatibility

In the SQL standard, an audit policy does not exist.

For More Information

Refer to the followings.

DROP CLUSTER GROUP

Function

It drops a cluster group from a cluster system.

Syntax

<drop cluster group statement> ::=
    DROP CLUSTER GROUP [IF EXISTS] group_name
    ;

Invocation and Access Rules

It can be performed in a cluster system.
ADMINISTRATION ON DATABASE privilege is required to perform <drop cluster group statement>.

Syntax Rules and Parameters

[IF EXISTS]

An error does not occur even when a cluster group does not exist.

group_name

It is the name of a cluster group.
A cluster group without any shard can be dropped.

Description

A cluster group can be dropped only when dropping the cluster group does not cause the data loss.
However, an error may occur when trying to drop a group including the global coordinator.

Examples

The following is an example of dropping a cluster group.

gSQL> DROP CLUSTER GROUP g3;

Cluster Group dropped.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to CREATE CLUSTER GROUP.

DROP CLUSTER LOCATION

Function

It drops the access information of a cluster member.

Syntax

<drop cluster location statement> ::=
    DROP CLUSTER LOCATION member_name 
    ;

Invocation and Access Rules

It can be performed in a cluster system.
ADMINISTRATION ON DATABASE privilege is required to perform <drop cluster location statement>.

Syntax Rules and Parameters

member_name

It is the name of a cluster member.
The same cluster member name should exist in a registered cluster location information.
The length of the name should be shorter than 128 bytes.

Description

Generally, the information of the cluster location is automatically created by using the connection information provided when creating the cluster group or adding the cluster member. The created information is deleted together when deleting the cluster member and the cluster group.

If the access information of the cluster location is modified, then the connection information can be modified by using ALTER CLUSTER LOCATION without deleting or recreating the cluster member.

Example

gSQL> 
DROP CLUSTER LOCATION g1n2
;

Created

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to the followings.

DROP INDEX

Function

It drops an index.

Syntax

<drop index statement> ::=
    DROP INDEX [ IF EXISTS ] index_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop index statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the index does not exist, an error does not occur.

index_name

It is the index name to be dropped.
It can define schema to which the index belongs such as schema_name.index_name and if schema_name is omitted, the default schema name of the user performing the statement is used.
The indexes created for UNIQUE constraint, PRIMARY KEY constraint can not be dropped.
To drop the indexes created for the constraints above, the constraints should be removed through ALTER TABLE name DROP CONSTRAINT statement.

Description

Data Definition Language (DDL) statement such as DROP INDEX can be rolled back if it is before when the transaction is committed.

Examples

The following is an example of dropping an index.

gSQL> DROP INDEX idx_t1_id;

Index dropped.

The following is an example of preventing an error even when the index does not exist by using IF EXISTS statement.

gSQL> DROP INDEX IF EXISTS not_exist_index;

Index dropped.

Compatibility

The SQL standard does not cover the concepts of the index.

For More Information

Refer to the followings.

DROP PROFILE

Function

It drops a profile.

Syntax

<drop profile statement> ::= 
    DROP PROFILE [ IF EXISTS ] profile_name [ CASCADE ] ;

Invocation and Access Rules

DROP PROFILE ON DATABASE privilege is required to perform <drop profile statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the profile does not exist, an error does not occur.

profile_name

It specifies the profile name to be dropped.
It can not drop the DEFAULT profile.

CASCADE

If the profile has already been assigned to users, CASCADE clause should be explicitly specified to drop the profile.
The profile which is assigned to users and to be dropped is changed to DEFAULT profile.

Example

The following is an example of dropping a profile by using CASCADE statement.

gSQL> DROP PROFILE prof CASCADE;

Profile dropped.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not cover the concepts of the profile.

For More Information

Refer to the followings.

DROP SCHEMA

Function

It drops a schema.

Syntax

<drop schema statement> ::=
    DROP SCHEMA [ IF EXISTS ] schema_name
        [ <drop behavior> ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE

Invocation and Access Rules

One of the following privileges is required to perform <drop schema statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the schema does not exist, an error does not occur.

schema_name

It is the schema name to be dropped.
However, it can not drop the built-in schema such as "DICTIONARY_SCHEMA", "INFORMATION_SCHEMA" and "PUBLIC" which are automatically created when creating the database.

<drop behavior>

Description

Data Definition Language (DDL) statement such as DROP SCHEMA can be rolled back if it is before when the transaction is committed. In this case, recycle bin objects which are included in the schema to be dropped are also dropped.

Examples

The following is an example of dropping a schema and all objects which exist within the schema.

gSQL> DROP SCHEMA s1 CASCADE;

Schema dropped.

The following is an example of preventing an error even when the schema does not exist by using IF EXISTS statement.

gSQL> DROP SCHEMA IF EXISTS not_exist_schema;

Schema dropped.

Compatibility

The SQL standard does not define IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

O

F381

Extended schema manipulation

O

For More Information

Refer to CREATE SCHEMA.

DROP SEQUENCE

Function

It drops a sequence.

Syntax

<drop sequence generator statement> ::=
    DROP SEQUENCE [ IF EXISTS ] [schema_name.] sequence_name 
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop sequence generator statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the sequence does not exist, an error does not occur.

sequence_name

It is the sequence name to be dropped.
It can define schema to which the sequence belongs such as schema_name.sequence_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

Description

Data Definition Language (DDL) statement such as DROP SEQUENCE can be rolled back if it is before when the transaction is committed.

Examples

The following is an example of dropping a sequence.

gSQL> DROP SEQUENCE seq1;

Sequence dropped.

The following is an example of preventing an error even when the sequence does not exist by using IF EXISTS statement.

gSQL> DROP SEQUENCE invalid_sequence;

ERR-42000(16044): sequence does not exist : 
DROP SEQUENCE invalid_sequence
              *
ERROR at line 1:


gSQL> DROP SEQUENCE IF EXISTS invalid_sequence;

Sequence dropped.

Compatibility

The SQL standard does not define IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

T176

Sequence generator support

O

For More Information

Refer to the followings.

DROP SYNONYM

Function

It drops a synonym.

Syntax

<drop synonym statement> ::=
    DROP [ PUBLIC ] SYNONYM [ IF EXISTS ] [schema_name.]synonym_name
    ;

Invocation and Access Rules

DROP PUBLIC SYNONYM ON DATABASE privilege is required to drop a public synonym by specifying PUBLIC.

One of the following privileges is required to drop a private synonym.

Syntax Rules and Parameters

[ PUBLIC ]

It is specified when dropping the public synonym.
If this clause is omitted, the private synonym is dropped.

IF EXISTS

Even when the synonym does not exist, an error does not occur.

synonym_name

It is the synonym name to be dropped.
It can define schema to which the synonym belongs such as schema_name.synonym_name and if schema_name is omitted, the default schema name of the user performing the statement is used.
If PUBLIC is explicitly specified, the schema name can not be specified.

Description

Data Definition Language (DDL) statement such as DROP SYNONYM can be rolled back if it is before when the transaction is committed.

Examples

The following is an example of dropping a private synonym.

gSQL> DROP SYNONYM MyEmp;

Synonym dropped.

The following is an example of dropping a public synonym.

gSQL> DROP PUBLIC SYNONYM MainEmp;

Synonym dropped.

Compatibility

The SQL standard does not define DROP SYNONYM statement.

For More Information

Refer to CREATE SYNONYM.

DROP TABLE

Function

It drops a table.

If the recycle bin feature is activated, the table is not completely dropped but it is stored in the recycle bin.

Syntax

<drop table statement> ::=
    DROP TABLE [ IF EXISTS ] table_name
    [ <drop behavior> ]
    [ PURGE ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE
    | CASCADE CONSTRAINTS

Invocation and Access Rules

One of the following privileges is required to perform <drop table statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the table does not exist, an error does not occur.

table_name

It is the table name to be dropped.
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

The following tables which are automatically created during creating the database, can not be dropped.

It also drops constraints and indexes created in the table.

drop behavior

Currently, both RESTRICT and CASCADE are operated same.
When it is omitted, the default value is RESTRICT.

purge

It immediately drops a table instead of storing it in the recycle bin even when the recycle bin feature is activated.

Description

Data Definition Language (DDL) statement such as DROP TABLE can be rolled back if it is before when the transaction is committed.

Examples

The following is an example of dropping an ordinary table.

gSQL> DROP TABLE region;

Table dropped.

The following is an example of preventing an error even when the table does not exist by using IF EXISTS statement.

gSQL> DROP TABLE IF EXISTS invalid_table;

Table dropped.

The following is an example of rolling back the dropped table.

gSQL> SELECT r_regionkey, r_name FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     
          3 EUROPE                   
          4 MIDDLE EAST              

5 rows selected.


gSQL> DROP TABLE region;

Table dropped.


gSQL> SELECT r_regionkey, r_name FROM region;

ERR-42000(16040): table or view does not exist : 
SELECT r_regionkey, r_name FROM region
                                *
ERROR at line 1:


gSQL> ROLLBACK;

Rollback complete.


gSQL> SELECT r_regionkey, r_name FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     
          3 EUROPE                   
          4 MIDDLE EAST              

5 rows selected.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

O

For More Information

Refer to CREATE TABLE.

DROP TABLESPACE

Function

It drops a tablespace.

Syntax

<drop tablespace statement> ::=
    DROP TABLESPACE [ IF EXISTS ] tablespace_name
        [ INCLUDING CONTENTS ]
        [ { AND | KEEP } DATAFILES ]
        [ <drop behavior> ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE
    | CASCADE CONSTRAINTS

Invocation and Access Rules

DROP TABLESPACE ON DATABASE privilege is required to perform <drop tablespace definition>.

Syntax Rules and Parameters

IF EXISTS

Even when the tablespace does not exist, an error does not occur.

tablespace_name

It is the tablespace name to be dropped.

The following system tablespaces which are automatically created during creating the database, can not be dropped.

If tablespace_name was used as a default tablespace of a user, the space for the objects can not be allocated after dropping the tablespace.

After dropping the tablespace, the default tablespace should be changed by using ALTER USER statement.

INCLUDING CONTENTS

It drops objects (table, index, key constraint) which belong to the tablespace. If the index or key constraint which refers to the table which belongs to the tablespace exists outside of the tablespace, then it is also dropped.
If INCLUDING CONTENTS clause is not used, then any object which belongs to the tablespace should not exist.

[ { AND | KEEP } DATAFILES ]

It specifies whether to drop the datafiles which configure the tablespace together.
The datafiles are not in the memory temporary tablespace, so the clause is ignored.

drop behavior

Currently, both RESTRICT and CASCADE are operated same.
When it is omitted, the default value is RESTRICT.

Description

Unlike other Data Definition Language (DDL), DROP TABLESPACE statement can not be rolled back and the executed transaction is automatically committed. In this case, recycle bin objects which are included in the tablespace to be dropped are also dropped.

Examples

The following is an example of dropping a tablespace together with all objects in the tablespace and datafiles which configure the tablespace.

gSQL> DROP TABLESPACE space1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

The following is an example of preventing an error even when the tablespace does not exist by using IF EXISTS statement.

gSQL> DROP TABLESPACE IF EXISTS not_exist_tablespace;

Tablespace dropped.

Compatibility

The SQL standard does not cover the concepts of the tablespace.

For More Information

Refer to the followings.

DROP USER

Function

It drops a database user.

Syntax

<drop user statement> ::=
    DROP USER [ IF EXISTS ] user_identifier [ <drop behavior> ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE

Invocation and Access Rules

DROP USER ON DATABASE privilege is required to perform <drop user statement>.

The schema owned by user_identifier should not exist.

For more information about dropping the schema, refer to DROP SCHEMA.

Syntax Rules and Parameters

IF EXISTS

Even when the user does not exist, an error does not occur.

user_identifier

It is the database username to be dropped.
However, the user which is automatically created during creating the database such as "SYS", can not be dropped.

It does not drop the object which is created by user_identifier but is not an owner as follows.

<drop behavior>

The relationship between user and schema in other DBMS





Description

In GOLDILOCKS, relationship between the user and the schema is 1 : N. A user does not own a schema, or the user can have multiple schemas.
To drop a user, all schema owned by the user should be dropped. In this case, recycle bin objects of the user to be dropped are also dropped.

Examples

The following is an example of dropping all schema owned by the user and then dropping the user.

gSQL> DROP SCHEMA u1 CASCADE;

Schema dropped.

gSQL> DROP USER u1 CASCADE;

User dropped.

The following is an example of preventing an error even when the user does not exist by using IF EXISTS statement.

gSQL> DROP USER IF EXISTS not_exist_user;

User dropped.

Compatibility

SQL standard cover the concepts of the user, but they do not define the SQL statements related to creating or dropping a user.

For More Information

Refer to the followings.

DROP VIEW

Function

It drops a view.

Syntax

<drop view statement> ::=
    DROP VIEW [ IF EXISTS ] view_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop view statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the view does not exist, an error does not occur.

view_name

It is the view name to be dropped.
The schema to which the table belongs, such as schema_name.view_name, can be defined. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

Data Definition Language (DDL) statement such as DROP VIEW can be rolled back if it is before when the transaction is committed.

Examples

The following is an example of dropping a view.

gSQL> DROP VIEW v1;

View dropped.

The following is an example of preventing an error even when the view does not exist by using IF EXISTS statement.

gSQL> DROP VIEW IF EXISTS not_exist_view;

View dropped.

Compatibility

The SQL standard does not define IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

X

For More Information

Refer to the followings.

EXECUTE IMMEDIATE 'sql_string'

Function

It executes a dynamic SQL statement which was not defined when writing a program.

Syntax

<execute immediate statement> ::=
    EXECUTE IMMEDIATE <SQL statement variable>
    ;

<SQL statement variable> ::=
      variable_name
    | 'sql statement'
    | "sql statement"
    | sql statement

Invocation and Access Rules

It can be used in an embedded SQL.
An appropriate privilege according to the type of a dynamic SQL statement is required.

Syntax Rules and Parameters

<SQL statement variable>

The dynamic SQL statement referenced by <SQL statement variable> can not use a host variable (:var) or parameter marker (?).
The following four types of <SQL statement variable> can be used.

The single quote (') is used twice as follows to represent string data within a single-quoted string.

{
    ...
    EXEC SQL EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES ( ''literal data'' )'; 
    ...
}

If the SQL statement is a query including a query result, it is successfully executed, but the result can not be obtained.

variable_name

The type corresponding to the variable_name should be a character string.
The dynamic SQL statement defined in the variable_name should be valid.

sql statement

The dynamic SQL statement defined in the sql statement should be valid.

Description

EXECUTE IMMEDIATE 'sql_string' statement can be used as the non-query SQL without a host variable in dynamic embedded SQL application. It is appropriate to execute DDL, DML as one-off because it does not require separate preparation procedure.
For more information, refer to  Embedded Dynamic SQL.

Example

The following is an example of using EXECUTE IMMEDIATE 'sql_string' in the embedded SQL source code.

{
    ...
    sprintf(sSqlStmt, "INSERT INTO EMP_RND\n"
            "SELECT *\n"
            "FROM   EMP\n"
            "WHERE  JOB = 'RND'\n" );
    EXEC SQL EXECUTE IMMEDIATE :sSqlStmt;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }
    ...
}

The full source code in which EXECUTE IMMEDIATE 'sql_string' was used can be viewed in Dynamic Embedded SQL Example Program.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

For More Information

Refer to the followings.

EXECUTE statement_name

Function

It executes the prepared statement.

Syntax

<execute statement> ::=
    EXECUTE statement_name [ <parameter using clause> ] [ <result into clause> ]
    ;

<parameter using clause> ::=
      <using parameter arguments>

<using parameter arguments> ::=
    USING variable_name [, ...]

<result into clause> ::=
      <into result arguments>

<into result arguments> ::=
    INTO variable_name [, ...]

Invocation and Access Rules

It can be used in an embedded SQL.
An appropriate privilege according to the type of a dynamic SQL statement is required.

Syntax Rules and Parameters

statement_name

It is the name of a prepared statement.
Statement_name should be prepared by using PREPARE statement_name.
If the dynamic SQL statement referenced by statement_name contains a dynamic parameter, <parameter using clause> should explicitly be specified.
{
    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 > ?';
    EXEC SQL EXECUTE stmt1 USING :sValue;
    ...
}
{

    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT COUNT(*) INTO :v1 FROM t1';
    EXEC SQL EXECUTE stmt1 USING :sValue;
    ...
}

If the dynamic SQL statement referenced by statement_name is a query or a stored function including the result, <result into clause> should explicitly be specified.

{
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT COUNT(*) FROM t1';
    EXEC SQL EXECUTE stmt1 INTO :sValue;
    ...
}
If there are multiple queries they are normally executed, but only the first query can get the result.
To get multiple results, the following statements related to the cursor should be used.

If there is not any query result, it is completed as NO DATA.

[ <parameter using clause> ] [ <result into clause> ]

<parameter using clause> and <result into clause> can be specified in any order, but they should not be repeated.

<parameter using clause>

If any parameter exists in a dynamic SQL statement referenced by statement_name, the parameter information is specified with <using parameter arguments> clause.

<using parameter arguments>

If <using parameter arguments> statement is used, the number of variable_name should be equal to the number of the parameter included in the dynamic SQL statement referenced by statement_name.

The listed variable_name corresponds to the dynamic parameter in an order of its description.

{

    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 IN ( ?, ?, ? )';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2, :sValue3;
    ... 
}

<result into clause>

If the dynamic SQL statement referenced by statement_name is a query, the information about the result columns is specified with <into result arguments> clause.
If the result is null and INDICATOR is not specified, [DATA EXCEPTION, NULL VALUE, NO INDICATOR PARAMETER] error occurs.

<into result arguments>

If <into result arguments> clause is used, the number of variable_name should be equal to the number of the result column in the dynamic SQL statement referenced by statement_name.
The listed variable_name corresponds to the dynamic parameter in an order of its description.
{

    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT MIN(salary), MAX(salary), AVG(salary) FROM employee';
    EXEC SQL EXECUTE stmt1 INTO :sMinValue, :sMaxValue, :sAvgValue;
    ... 
}

Description

Statement_name is an identifier which informs the precompiler the statement in an embedded SQL source code.
A separate type or declaration is not required because statement_name is not a host variable. EXECUTE statement_name should be written after PREPARE statement_name.
For more information, refer to Embedded Dynamic SQL.

Example

The following is an example of using EXECUTE statement_name in an embedded SQL source code.

{
    ...
    sprintf( sUpdateSql, "UPDATE EMP SET sal = sal * :v1 WHERE JOB = 'SALES'");
    EXEC SQL PREPARE UPDATE_STMT FROM :sUpdateSql;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }

    sRatio = 1.1;
    EXEC SQL EXECUTE UPDATE_STMT USING :sRatio;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }
    ...
}

The full source code in which EXECUTE statement_name was used can be viewed in Dynamic Embedded SQL Example Program.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

B032

Extended dynamic SQL

X

For More Information

Refer to the followings.

FETCH cursor_name

Function

It locates the cursor on a specific row of result set, and obtains the value of that row to a host variable.

Syntax

<fetch statement> ::=
    FETCH [ <fetch orientation> ] [ FROM ] cursor_name 
        <result into clause>
    ;

<fetch orientation> ::=
      NEXT
    | PRIOR
    | FIRST
    | LAST
    | CURRENT
    | ABSOLUTE position
    | RELATIVE position

<result into clause> ::=
      <into result arguments>

<into result arguments> ::=
    INTO variable_name [, ...]

Syntax Rules and Parameters

[ FROM ] cursor_name

It should be an open cursor in a session.
FROM can be omitted.

<fetch orientation>

To use <fetch orientation> other than FETCH NEXT, a scrollable cursor should be used.
If <fetch orientation> is omitted, the default value is NEXT.
The open cursor has the cursor position information for the result set as follows.

The position of cursor

The position of cursor

The position of cursor

Cursor position

Description

BEFORE THE FIRST ROW

The cursor is positioned before the first row of the result set. It is also the cursor position when opening it.

ON A CERTAIN ROW

The cursor is positioned on a certain row of the result set through FETCH.

AFTER THE LAST ROW

The cursor is positioned after the last row of the result set.

Each <fetch orientation> operates based on the cursor position as follows.

<result into clause>

The variable information to obtain the result column is specified by using <into result arguments>.
If the result is null and INDICATOR is not specified, [DATA EXCEPTION, NULL VALUE, NO INDICATOR PARAMETER] error occurs.

<into result arguments>

The number of variables in INTO clause should be as same as the number of columns in the result set of the cursor.

Description

If the cursor is BEFORE THE FIRST ROW or AFTER THE LAST ROW after performing FETCH, it is positioned at the same position regardless of the entered position in <fetch orientation>.

Example

The following is an example of declaring SCROLL cursor by using the interactive SQL (gsql), then operating the various <fetch orientation>.

gSQL> DECLARE cur_scroll SCROLL CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur_scroll;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH NEXT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH NEXT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH PRIOR cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH PRIOR cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH CURRENT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH CURRENT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH CURRENT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH ABSOLUTE 1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH ABSOLUTE -1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH ABSOLUTE 6 cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH ABSOLUTE -6 cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH ABSOLUTE -3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH RELATIVE 1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH RELATIVE -1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH RELATIVE 5 cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH RELATIVE -5 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> CLOSE cur_scroll;

Cursor closed.

Compatibility

The SQL standard does not define CURRENT among <fetch orientation>.

SQL standard compatibility

Feature ID

Description

Compatibility

F431

Read-only scrollable cursors

O

B031

Basic dynamic SQL

O

For More Information

Refer to the followings.

FLASHBACK TABLE

Function

It restores the table object which is stored in the recycle bin.

Syntax

<flashback table statement> ::=
    FLASHBACK TABLE table_name
    TO BEFORE DROP [ RENAME TO new_table_name ]
    ;

Invocation and Access Rules

One of the following privileges is required to perform <flashback table statement>.

Syntax Rules and Parameters

table_name

It is the name of the object stored or of the dropped table in the recycle bin.
It can define the schema to which the table belongs in the dropped table name, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

new_table_name

It is a new name of the table to be restored.
The duplicate table name should not exist within a single schema.

Description

It restores the table object which is stored in the recycle bin by using the object name or the dropped table name stored in the recycle bin. If the name which is as same as that of the dropped table exists, then the newest table object is restored.
If the name which is as same as that of the table object to be restored exists, then an error occurs, but it can be restored with the new name by using RENAME TO clause. The constraints and the indexes of the restored tables are restored in its name of when before they were dropped. However, if their names of when before the constraints and the indexes were dropped already exist, then the object is restored in the name of when it is stored in the recycle bin.
Unlike other Data Definition Language (DDL), FLASHBACK TABLE statement can not be rolled back and the executed transaction is automatically committed.

Example

The following is an example of restoring a table with the name of the object stored in the recycle bin.

gSQL> SELECT SCHEMA_NAME, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

SCHEMA_NAME OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE
----------- ------------------------------------ ------------- -----------
PUBLIC      BIN$106A4F90165D11EA9C5C835D3E4BBBF7 T1            TABLE      

1 row selected.

gSQL> FLASHBACK TABLE "BIN$106A4F90165D11EA9C5C835D3E4BBBF7" TO BEFORE DROP;

Flashback complete.

The following is an example of restoring the table from the recycle bin in the name of when before it is dropped.

gSQL> SELECT SCHEMA_NAME, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

SCHEMA_NAME OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE
----------- ------------------------------------ ------------- -----------
PUBLIC      BIN$106A4F90165D11EA9C5C835D3E4BBBF7 T1            TABLE      

gSQL> FLASHBACK TABLE T1 TO BEFORE DROP;

Flashback complete.

Compatibility

The SQL standard does not define <flashback table statement>.

For More Information

Refer to the followings.

GRANT privileges TO

Function

It grants privileges to a user.

Syntax

<grant privilege statement> ::=
    GRANT <privilege> TO <grantee> [, ...]
        [ WITH GRANT OPTION ]
    ;

<grantee> ::=
      PUBLIC
    | user_identifier
    ;
    
<privilege> ::=
      <database privilege>
    | <tablespace privilege>
    | <schema privilege>
    | <table privilege>
    | <sequence privilege>
    | <procedure privilege>

<database privilege> ::=
      ALL [ PRIVILEGES ] [ON DATABASE]
    | <database action> [, ...] [ON DATABASE]

<database action> ::=
      ADMINISTRATION
    | ANALYZE ANY 
    | ALTER DATABASE
    | ALTER SYSTEM
    | AUDIT SYSTEM
    | ACCESS CONTROL
    | CREATE SESSION
    | CREATE PROFILE
    | ALTER PROFILE
    | DROP PROFILE 
    | CREATE USER
    | ALTER USER
    | DROP USER 
    | CREATE ROLE
    | ALTER ROLE
    | DROP ROLE
    | CREATE TABLESPACE
    | ALTER TABLESPACE
    | DROP TABLESPACE
    | USAGE TABLESPACE
    | CREATE SCHEMA
    | ALTER SCHEMA
    | DROP SCHEMA
    | CREATE PUBLIC SYNONYM
    | DROP PUBLIC SYNONYM
    | CREATE ANY TABLE
    | ALTER ANY TABLE
    | DROP ANY TABLE
    | SELECT ANY TABLE
    | INSERT ANY TABLE
    | DELETE ANY TABLE
    | UPDATE ANY TABLE
    | LOCK ANY TABLE
    | CREATE ANY VIEW
    | DROP ANY VIEW
    | CREATE ANY SEQUENCE
    | ALTER ANY SEQUENCE
    | DROP ANY SEQUENCE
    | USAGE ANY SEQUENCE
    | CREATE ANY INDEX
    | ALTER ANY INDEX
    | DROP ANY INDEX
    | CREATE ANY SYNONYM
    | DROP ANY SYNONYM
    | CREATE ANY PROCEDURE
    | ALTER ANY PROCEDURE
    | DROP ANY PROCEDURE
    | EXECUTE ANY PROCEDURE
    | CREATE ANY PACKAGE
    | ALTER ANY PACKAGE
    | DROP ANY PACKAGE
    | EXECUTE ANY PACKAGE
    | PURGE DBA_RECYCLEBIN

<tablespace privilege> ::=
      ALL [ PRIVILEGES ] ON TABLESPACE tablespace_name
    | <tablespace action> [, ...] ON TABLESPACE tablespace_name

<tablespace action> ::=
    CREATE OBJECT

<schema privilege> ::=
      ALL [ PRIVILEGES ] ON SCHEMA schema_name
    | <schema action> [, ...] [ON SCHEMA schema_name]

<schema action> ::=
      CONTROL SCHEMA
    | CREATE TABLE
    | ALTER TABLE 
    | DROP TABLE
    | SELECT TABLE
    | INSERT TABLE
    | DELETE TABLE
    | UPDATE TABLE
    | LOCK TABLE
    | CREATE VIEW
    | DROP VIEW
    | CREATE SEQUENCE
    | ALTER SEQUENCE
    | DROP SEQUENCE
    | USAGE SEQUENCE
    | CREATE INDEX
    | ALTER INDEX
    | DROP INDEX
    | ADD CONSTRAINT
    | CREATE SYNONYM
    | DROP SYNONYM
    | CREATE PROCEDURE
    | ALTER PROCEDURE
    | DROP PROCEDURE
    | EXECUTE PROCEDURE
    | CREATE PACKAGE
    | ALTER PACKAGE
    | DROP PACKAGE
    | EXECUTE PACKAGE

<table privilege> ::=
      ALL [ PRIVILEGES ] ON [TABLE] table_name
    | { <table action> | <column action> } [, ...] ON [TABLE] table_name

<table action> ::=
      CONTROL TABLE
    | SELECT
    | INSERT
    | UPDATE
    | DELETE
    | REFERENCES
    | LOCK
    | INDEX
    | ALTER

<column action> ::=
      SELECT ( column_name [, ...] )
    | INSERT ( column_name [, ...] )
    | UPDATE ( column_name [, ...] )
    | REFERENCES ( column_name [, ...] )

<sequence privilege> ::=
      ALL [ PRIVILEGES ] ON SEQUENCE sequence_name
    | <sequence action> ON SEQUENCE sequence_name

<sequence action> ::=
    USAGE

<procedure privilege> ::=
      ALL [ PRIVILEGES ] ON PROCEDURE procedure_name
    | <procedure action> ON PROCEDURE procedure_name

<procedure action> ::=
    EXECUTE

<package privilege> ::=
      ALL [ PRIVILEGES ] ON PACKAGE package_name
    | <package action> ON PACKAGE package_name

<package action> ::=
    EXECUTE

Syntax Rules and Parameters

<grantee>

It is the user to be granted the privileges.

WITH GRANT OPTION

It allows the grantee to grant the privilege to other users.
When the same <privilege> is granted as follows, WITH GRANT OPTION is maintained.

<privilege>

It is a privilege which is to be granted to a grantee (the user to be granted the privilege).
The grantor (the user to perform the statement) should satisfy one of the following conditions.

<database privilege>

It is the privilege for the database objects.
[ON DATABASE] statement can be omitted.
The database action which can be defined with the database privilege is as follows.
Database privilege

<database action>

Description

ADMINISTRATION

Privilege for starting or terminating the server

ALTER DATABASE

Privilege for executing ALTER DATABASE

ALTER SYSTEM

Privilege for executing ALTER SYSTEM

AUDIT SYSTEM

Privilege for controlling the audit policy

ACCESS CONTROL

Privilege for controlling all the privileges

CREATE SESSION

Privilege for connecting to the database

CREATE PROFILE

Privilege for creating profiles in the database

ALTER PROFILE

Privilege for altering any profile in the database

DROP PROFILE

Privilege for dropping any profile in the database

CREATE USER

Privilege for creating users in the database

ALTER USER

Privilege for altering any user in the database

DROP USER

Privilege for dropping any user in the database

CREATE ROLE

Privilege for creating roles in the database

ALTER ROLE

Privilege for altering any role in the database

DROP ROLE

Privilege for dropping any role in the database

CREATE TABLESPACE

Privilege for creating tablespaces in the database

ALTER TABLESPACE

Privilege for altering any tablespace in the database

DROP TABLESPACE

Privilege for dropping any tablespace in the database

USAGE TABLESPACE

Privilege for using any tablespace in the database

CREATE SCHEMA

Privilege for creating schemas in the database

ALTER SCHEMA

Privilege for altering any schema in the database

DROP SCHEMA

Privilege for dropping any schema in the database

CREATE PUBLIC SYNONYM

Privilege for creating public synonyms in the database

DROP PUBLIC SYNONYM

Privilege for dropping any public synonym in the database

CREATE ANY TABLE

Privilege for creating tables in any schema of the database

ALTER ANY TABLE

Privilege for altering any table in the database

DROP ANY TABLE

Privilege for dropping any table in the database

SELECT ANY TABLE

Privilege for querying rows of any table in the database

INSERT ANY TABLE

Privilege for creating rows of any table in the database

DELETE ANY TABLE

Privilege for deleting rows of any table in the database

UPDATE ANY TABLE

Privilege for updating rows of any table in the database

LOCK ANY TABLE

Privilege for locking any table in the database

CREATE ANY VIEW

Privilege for creating views in any schema of the database

DROP ANY VIEW

Privilege for dropping any view in the database

CREATE ANY SEQUENCE

Privilege for creating sequences in any schema of the database

ALTER ANY SEQUENCE

Privilege for altering any sequence of the database

DROP ANY SEQUENCE

Privilege for dropping any sequence of the database

USAGE ANY SEQUENCE

Privilege for using any sequence of the database

CREATE ANY INDEX

Privilege for creating indexes in any schema of the database

ALTER ANY INDEX

Privilege for altering any index in the database

DROP ANY INDEX

Privilege for dropping any index in the database

CREATE ANY SYNONYM

Privilege for creating synonyms in the database

DROP ANY SYNONYM

Privilege for dropping any synonym in the database

CREATE ANY PROCEDURE

Privilege for creating any procedure/function in any schema of the database

ALTER ANY PROCEDURE

Privilege for altering any procedure/function in the database

DROP ANY PROCEDURE

Privilege for dropping any procedure/function in the database

EXECUTE ANY PROCEDURE

Privilege for executing any procedure/function in the database

CREATE ANY PACKAGE

Privilege for creating any package in any schema of the database

ALTER ANY PACKAGE

Privilege for altering any package in the database

DROP ANY PACKAGE

Privilege for dropping any package in the database

EXECUTE ANY PACKAGE

Privilege for executing any package in the database

PURGE DBA_RECYCLEBIN

Privilege for dropping any package in the database

<tablespace privilege>

It is the privilege for the tablespace objects.
The tablespace action which can be defined with the tablespace privilege is as follows.
Tablespace privilege

<tablespace action>

Description

CREATE OBJECT

Privilege for creating objects in the tablespace

<schema privilege>

It is the privilege for the schema objects.

The schema action which can be defined with the schema privilege is as follows.

Schema privilege

<schema action>

Description

CONTROL SCHEMA

All privileges for that schema

CREATE TABLE

Privilege for creating tables in the schema

ALTER TABLE

Privilege for altering any table in the schema

DROP TABLE

Privilege for dropping any table in the schema

SELECT TABLE

Privilege for querying rows of any table in the schema

INSERT TABLE

Privilege for creating rows of any table in the schema

DELETE TABLE

Privilege for deleting rows of any table in the schema

UPDATE TABLE

Privilege for updating rows of any table in the schema

LOCK TABLE

Privilege for locking any table of the schema

CREATE VIEW

Privilege for creating views in the schema

DROP VIEW

Privilege for dropping any view of the schema

CREATE SEQUENCE

Privilege for creating sequences in the schema

ALTER SEQUENCE

Privilege for altering any sequence of the schema

DROP SEQUENCE

Privilege for dropping any sequence of the schema

USAGE SEQUENCE

Privilege for using any sequence of the schema

CREATE INDEX

Privilege for creating indexes in the schema

ALTER INDEX

Privilege for altering any index in the schema

DROP INDEX

Privilege for dropping any index in the schema

ADD CONSTRAINT

Privilege for creating constraints in the schema

CREATE SYNONYM

Privilege for creating synonyms in the schema

DROP SYNONYM

Privilege for dropping any synonym of the schema

CREATE PROCEDURE

Privilege for creating any procedure/function in the schema

ALTER PROCEDURE

Privilege for altering any procedure/function in the schema

DROP PROCEDURE

Privilege for dropping any procedure/function in the schema

EXECUTE PROCEDURE

Privilege for executing any procedure/function in the schema

CREATE PACKAGE

Privilege for creating any package in the schema

ALTER PACKAGE

Privilege for altering any package in the schema

DROP PACKAGE

Privilege for dropping any package in the schema

EXECUTE PACKAGE

Privilege for executing any package in the schema

<table privilege>

It is the privilege for the table object or the view object.
[TABLE] statement can be omitted.
The table action which can be defined with the table privilege is as follows.
Table privilege

<table action>

Description

CONTROL TABLE

All privileges for that table

SELECT

Privilege for querying rows of the table

INSERT

Privilege for creating rows into the table

UPDATE

Privilege for updating rows in the table

DELETE

Privilege for deleting rows from the table

REFERENCES

Privilege for creating referential constraints which refers to the table

LOCK

Privilege for locking the table

INDEX

Privilege for creating indexes in the table

ALTER

Privilege for altering the table

For SELECT, INSERT, UPDATE, REFERENCES, additional privileges are granted to all columns of the table.
The column action which can be defined with the table privilege is as follows. However, the column action is applicable only to the base table.
Column privilege

<column action>

Description

SELECT (columns)

Privilege for querying that columns

INSERT (columns)

Privilege for creating rows including that columns

UPDATE (columns)

Privilege for updating that columns

REFERENCES (columns)

Privilege for creating referential constraints which refers to that columns

<sequence privilege>

It is the privilege for the sequence object.
The sequence action which can be defined with the sequence privilege is as follows.
Sequence privilege

<sequence action>

Description

USAGE

Privilege for using the sequence

<procedure privilege>

It is the privilege for the procedure/ function object.
The action which can be defined with the procedure privilege is as follows.
Procedure privilege

<procedure action>

Description

EXECUTE

Privilege for executing the procedure/function

<package privilege>

It is the privilege for the package object.
The action which can be defined with the package privilege is as follows.
Package privilege

<package action>

Description

EXECUTE

Privilege for executing the package

Description

Data Definition Language (DDL) such as GRANT privilege can be rolled back if it is before when the transaction is committed.
The owner who created SQL schema object, such as table, sequence, has certain privileges without being granted any separate privilege for the object.  
For more information, refer to the following CREATE statements.
The owner who created non-schema object such as schema, tablespace, does not automatically have any privilege for the object. Therefore, the privilege should be separately granted. 
For more information, refer to the following CREATE statements.

Examples

The following is an example of granting SELECT ON TABLE t1 privilege to the user u1.

gSQL> GRANT SELECT ON t1 TO u1;

Grant succeeded.

The following is an example of granting SELECT ON TABLE t1 privilege to the PUBLIC account (all users).

gSQL> GRANT SELECT ON t1 TO PUBLIC;

Grant succeeded.

The following is an example that the user u1 grants the privilege to the other user by using WITH GRANT OPTION.

gSQL> GRANT SELECT ON t1 TO u1 WITH GRANT OPTION;

Grant succeeded.

The following is the example that the user executing the statement grants all privileges on the TABLE t1 to user u1 by using WITH GRANT OPTION.

gSQL> GRANT ALL PRIVILEGES ON TABLE t1 TO u1;

Grant succeeded.

The following is an example of granting CREATE SESSION ON DATABASE privilege which is a privilege for connecting to the database.

gSQL> GRANT CREATE SESSION ON DATABASE TO u1;

Grant succeeded.

The following is an example of granting multiple privileges for creating objects such as the table, view, index, sequence, constraint in the SCHEMA s1 to the user u1.

gSQL> GRANT CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, ADD CONSTRAINT ON SCHEMA s1 TO u1;

Grant succeeded.

The following is an example of granting the privileges for creating objects in TABLESPACE mem_data_tbs to the user u1.

gSQL> GRANT CREATE OBJECT ON TABLESPACE mem_data_tbs TO u1;

Grant succeeded.

The following is an example of granting the privilege for querying some columns in the TABLE t1 to the user u1.

gSQL> GRANT SELECT( id, name ) ON TABLE t1 TO u1;

Grant succeeded.

The following is an example of granting the privilege to the user u1 for using NEXTVAL(), CURRVAL() functions in the SEQUENCE seq1.

gSQL> GRANT USAGE ON SEQUENCE seq1 TO u1;
Grant succeeded.

Compatibility

The SQL standard does not define the following privileges.

SQL standard compatibility

Feature ID

Description

Compatibility

S023

Basic structured types

X

S024

Enhanced structured types

X

S081

Subtables

X

T211

Basic trigger capability

X

T281

SELECT privilege with column granularity

O

T332

Extended Roles

X

F731

INSERT column privileges

O

For More Information

Refer to the followings.