SQL References (H~Z)

INSERT INTO

기능

테이블에 새로운 row들을 생성한다.

구문

<insert statement> ::=
    INSERT [ /*+ <append insert hint clause> */ ]
        INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
    ;

<append insert hint clause> ::=
    APPEND [ ( append insert option element [, ...] ) ]

<append insert option element> ::=
      PARALLEL [NOLOGGING]
    | STATEMENT_NOFORCE
    | <index maintenance options>

<insert maintenance options> ::=
      IMMEDIATE_INDEX_MAINTENANCE
    | DEFERRED_INDEX_MAINTENANCE
    | SKIP_INDEX_MAINTENANCE

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

사용 범위 및 접근 권한

<insert statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

<append insert hint clause>

APPEND INSERT 방식으로 데이터를 추가하도록 지정하는 힌트이다.

<append insert option element>

APPEND INSERT 방식으로 데이터를 추가할 때 사용할 수 있는 옵션이다. 만약 사용자가 기술한 옵션을 사용할 수 없는 경우 insert statement는 실패한다.

<index maintenance options>

APPEND INSERT 방식으로 데이터를 추가할 때 사용할 수 있는 인덱스 관리 옵션이다.

table_name

Row를 생성할 대상 테이블의 이름이다.
schema_name.table_name과 같이 테이블이 속한 스키마를 정의할 수 있는데 schema_name을 생략할 경우 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.

[ ( column_name [, ...] ) ]

테이블의 column 이름이다. 
Column 리스트는 생략할 수 있다. 
Column의 개수와 <insert source> 값의 개수는 동일해야 하며, 생략된 column에는 DEFAULT 값을 할당한다.

<values clause>

대응하는 column에 할당할 값의 리스트이다.

다음과 같이 다수의 row를 생성할 수 있다.

INSERT INTO table_name VALUES ( 1, 'A' ), ( 2, 'B' ), ( 3, 'C' )

<from subquery>

Row들을 생성할 질의이다.
자세한 내용은 SELECT 구문의 query expression 절을 참조한다.

DEFAULT VALUES

모든 column들을 기본값으로 채운다.

DEFAULT VALUES 절은 다음과 같은 의미이다.

VALUES ( DEFAULT, DEFAULT, ..., DEFAULT )

설명

INSERT 관련 구문들의 차이점

사용 예

다음은 INSERT 구문을 이용해 row 하나를 생성하는 예이다.

gSQL> INSERT INTO region VALUES ( 0, 'AFRICA' );

1 row created.

다음은 INSERT 구문에서 column의 DEFAULT 값 또는 identity 값을 사용하는 예이다.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.
gSQL> INSERT INTO region DEFAULT VALUES;

1 row created.
gSQL> INSERT INTO region VALUES (DEFAULT, DEFAULT);

1 row created.
gSQL> INSERT INTO region(r_regionkey) VALUES (-100);

1 row created.
gSQL> INSERT INTO region(r_name) VALUES ('ASIA');

1 row created.


gSQL> SELECT * FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          1 N/A                      
          2 N/A                      
       -100 N/A                      
          3 ASIA                     

4 rows selected.

다음은 VALUES 구문에 다수의 row를 기술하여 생성하는 예이다.

gSQL> INSERT INTO region
       VALUES ( 1, 'AFRICA' ),
              ( 2, 'ASIA'   ),
              ( 3, 'EUROPE' );

3 rows created.

다음은 subquery를 사용하여 다수의 row를 생성하는 예이다.

gSQL> INSERT INTO region SELECT r_regionkey, r_name FROM tmp_region WHERE r_regionkey < 3;

3 rows created.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F781

Self-referencing operations

X

F222

INSERT statement: DEFAULT VALUES clause

O

S204

Enhanced structured types

X

S043

Enhanced reference types

X

T111

Updatable joins, unions, and columns

X

참조

관련 내용은 다음을 참조한다.

INSERT INTO name RETURNING

기능

테이블에 새로운 row를 생성하고, 생성한 row들을 검색한다.

구문

<insert statement> ::=
    INSERT [ /*+ <append insert hint clause> */ ]
        INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <returning clause>
    ;

<append insert hint clause> ::=
    APPEND [ ( append insert option element [, ...] ) ]

<append insert option element> ::=
      PARALLEL [NOLOGGING]
    | STATEMENT_NOFORCE
    | <index maintenance options>

<insert maintenance options> ::=
      IMMEDIATE_INDEX_MAINTENANCE
    | DEFERRED_INDEX_MAINTENANCE
    | SKIP_INDEX_MAINTENANCE

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

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

사용 범위 및 접근 권한

<insert returning query statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

<append insert hint clause>

APPEND INSERT 방식으로 데이터를 추가하도록 지정하는 힌트이다.

<append insert option element>

APPEND INSERT 방식으로 데이터를 추가할 때 사용할 수 있는 옵션이다. 만약 사용자가 기술한 옵션을 사용할 수 없는 경우 insert statement는 실패한다.

<index maintenance options>

APPEND INSERT 방식으로 데이터를 추가할 때 사용할 수 있는 인덱스 관리 옵션이다.

table_name

Row를 생성할 대상 테이블의 이름이다.

[ ( column_name [, ...] ) ]

테이블의 column 이름이다.
자세한 내용은 INSERT INTO 구문을 참조한다.

<values clause>

대응하는 column에 할당할 값의 리스트이다.
자세한 내용은 INSERT INTO 구문을 참조한다.

<from subquery>

Row들을 생성할 질의이다.
자세한 내용은 INSERT INTO 구문을 참조한다.

DEFAULT VALUES

모든 column들을 기본값으로 채운다. 
자세한 내용은 INSERT INTO 구문을 참조한다.

<returning clause>

INSERT 된 row들을 반환한다.

RETURN과 RETURNING은 동일한 의미의 키워드이다.

설명

자세한 내용은 INSERT 관련 구문들의 차이점을 참조한다.

사용 예

다음은 INSERT 구문으로 생성된 column 값을 검색하는 예이다.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.
gSQL> INSERT INTO region VALUES ( DEFAULT, DEFAULT ) RETURNING r_regionkey, r_name;

R_REGIONKEY R_NAME                   
----------- -------------------------
          1 N/A                      

1 row created.
gSQL> INSERT INTO region(r_name) VALUES ('ASIA') RETURNING r_regionkey;

R_REGIONKEY
-----------
          2

1 row created.

다음은 subquery로부터 생성된 row들을 검색하는 예이다.

gSQL> INSERT INTO region 
      SELECT r_regionkey, r_name FROM tmp_region WHERE r_regionkey < 3 
      RETURNING r_regionkey, r_name;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     

3 rows created.

호환성

SQL 표준에서는 <insert returning query statement> 구문을 정의하지 않고 있다.

참조

관련 내용은 다음을 참조한다.

INSERT INTO name RETURNING .. INTO

기능

테이블에 row 하나를 생성하고, 생성한 row의 값을 호스트 변수로 얻어온다.

구문

<insert statement> ::=
    INSERT [ /*+ <append insert hint clause> */ ]
        INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <returning into clause>
    ;

<append insert hint clause> ::=
    APPEND [ ( append insert option element [, ...] ) ]

<append insert option element> ::=
      PARALLEL [NOLOGGING]
    | STATEMENT_NOFORCE
    | <index maintenance options>

<insert maintenance options> ::=
      IMMEDIATE_INDEX_MAINTENANCE
    | DEFERRED_INDEX_MAINTENANCE
    | SKIP_INDEX_MAINTENANCE

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

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

사용 범위 및 접근 권한

<insert returning into statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

<append insert hint clause>

APPEND INSERT 방식으로 데이터를 추가하도록 지정하는 힌트이다.

<append insert option element>

APPEND INSERT 방식으로 데이터를 추가할 때 사용할 수 있는 옵션이다. 만약 사용자가 기술한 옵션을 사용할 수 없는 경우 insert statement는 실패한다.

<index maintenance options>

APPEND INSERT 방식으로 데이터를 추가할 때 사용할 수 있는 인덱스 관리 옵션이다.

table_name

Row를 생성할 대상 테이블의 이름이다.

[ ( column_name [, ...] ) ]

테이블의 column 이름이다.
자세한 내용은 INSERT INTO 구문을 참조한다.

<values clause>

대응하는 column에 할당할 값의 리스트이다.
자세한 내용은 INSERT INTO 구문을 참조한다.

<from subquery>

Row들을 생성할 질의이다.
자세한 내용은 INSERT INTO 구문을 참조한다.

DEFAULT VALUES

모든 column들을 기본값으로 채운다.
자세한 내용은 INSERT INTO 구문을 참조한다.

<returning clause>

INSERT 된 row를 반환한다.
자세한 내용은 INSERT INTO name RETURNING 구문의 <returning clause> 절을 참조한다.

INTO variable_name [, ...]

INTO 절에 기술된 변수의 개수는 RETURNING 절에 기술된 expression의 개수와 동일해야 한다. 
생성할 row가 한 건 이하여야 한다. Row가 두 건 이상 생성될 경우, 에러가 발생한다.

설명

자세한 내용은 INSERT 관련 구문들의 차이점을 참조한다.

사용 예

다음은 생성된 row의 값을 호스트 변수에 얻어오는 예이다.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.
\VAR v_key  BIGINT
\VAR v_name VARCHAR(128)
gSQL> INSERT INTO region 
      VALUES ( DEFAULT, DEFAULT ) 
      RETURNING r_regionkey, r_name 
      INTO :v_key, :v_name;

V_KEY V_NAME                   
----- -------------------------
    1 N/A                      

1 row created.
gSQL> INSERT INTO region(r_name) 
      VALUES ('ASIA') 
      RETURNING r_regionkey 
      INTO :v_key;

V_KEY
-----
    2

1 row created.

호환성

SQL 표준에서는 <insert returning into statement> 구문을 정의하지 않고 있다.

참조

관련 내용은 다음을 참조한다.

INSERT INTO name ... UPDATE

기능

테이블에 새로운 row들을 생성한다. 만약 unique 제약 조건에 위배될 경우에는 기존 row들을 갱신한다.

구문

<upsert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

사용 범위 및 접근 권한

<upsert statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

table_name

Row를 생성할 대상 테이블의 이름이다. 
만약 unique 제약 조건에 위배되어 update가 수행될 경우에는 변경될 대상 테이블의 이름이다.
schema_name.table_name과 같이 테이블이 속한 스키마를 정의할 수 있는데 schema_name을 생략할 경우에는 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.

[ ( column_name [, ...] ) ]

테이블의 column 이름이다. 
자세한 내용은 INSERT INTO 구문의 [ ( column_name [, ...] ) ] 절을 참조한다.

<values clause>

대응하는 column에 할당할 값의 리스트이다.
자세한 내용은 INSERT INTO 구문의 <values clause>를 참조한다.

<from subquery>

Row들을 생성할 질의이다.
자세한 내용은 SELECT 구문의 query expression 절을 참조한다.

DEFAULT VALUES

모든 column들을 기본값으로 채운다.
자세한 내용은 INSERT INTO 구문의 DEFAULT VALUES 절을 참조한다.

<duplicate key clause>

Unique 제약 조건에 위배되었을 경우에 수행할 action을 정의한다.

DO NOTHING

Unique 제약 조건에 위배되는 경우에는 아무것도 하지 않는다.

<do update clause>

Unique 제약 조건에 위배되는 경우에는 <set clause>에 따라 column들의 값을 갱신한다.

<set value clause>

갱신할 column에 할당할 값을 정의한다.

다음과 같은 방법으로 정의할 수 있다.

DO UPDATE SET column1 = value1, column2 = value2, column3 = value3
DO UPDATE SET column1 = DEFAULT, column2 = DEFAULT, column3 = DEFAULT

<insert source>의 값을 갱신할 값으로 사용한다.

DO UPDATE SET column1 = VALUES(column1), column2 = VALUES(column2), column3 = VALUES(column2)

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.

다음과 같은 방법으로 정의할 수 있다.

ON DUPLICATE KEY
   DO UPDATE SET column1 = value1, column2 = value2, column3 = value3
ON DUPLICATE KEY
   DO UPDATE SET ( column1, column2, column3 ) = ( value1, value2, value3 )
ON DUPLICATE KEY
   DO UPDATE SET column1 = ( SELECT max(value1) FROM other_table_name )

<query expression>은 row 하나를 생성하는 질의여야 한다.

Column 값으로 DEFAULT를 사용할 경우, CREATE TABLE을 수행할 때 정의한 기본값 (<default clause> 참조)을 사용하며, 정의되지 않은 경우에는 NULL 값이 할당된다.

설명

INSERT INTO name ... UPDATE 관련 구문들의 차이점

<upsert statement>는 deterministic statement 이다.

다음과 같이 동치인 서로 다른 두 개의 UPSERT 구문은 동일한 결과를 만들어야 한다.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 );

3 rows created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

3 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4

3 rows selected.
gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 );

3 rows created.

gSQL> INSERT INTO t1 VALUES( 3 ),( 2 ),( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

3 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4

3 rows selected.

사용 예

다음은 unique 제약 조건에 위배되어 row 한 개가 갱신되는 예이다.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

1 row created.

gSQL> SELECT * FROM t1;

C1
--
 2

1 row selected.

다음은 unique 제약 조건에 위배되었을 때 row를 갱신하지 않는 예이다.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY DO NOTHING;

no rows created.

gSQL> SELECT * FROM t1;

C1
--
 1

1 row selected.

다음은 subquery를 사용하여 다수의 row를 삽입하거나 갱신하는 예이다.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ),( 4 );

4 rows created.

gSQL> INSERT INTO t1 ( SELECT c1 FROM t1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

4 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4
 5

4 rows selected.

호환성

SQL 표준은 <upsert statement> 구문을 정의하지 않고 있다.

참조

관련 내용은 다음을 참조한다.

INSERT INTO name ... UPDATE RETURNING

기능

테이블에 새로운 row들을 생성한다. 만약 unique 제약 조건에 위배되는 경우에는 기존 row들을 갱신한다. 이후 생성 또는 변경된 row들을 검색한다.

구문

<upsert returning statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        <returning clause>    
    ;
<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

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

사용 범위 및 접근 권한

<upsert returning statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

table_name

Row를 생성할 대상 테이블의 이름이다. 
만약 unique 제약 조건에 위배되어 update가 수행될 경우에는 변경될 대상 테이블의 이름이다.
자세한 내용은 INSERT INTO name ... UPDATE 구문의 table_name 절을 참조한다.

[ ( column_name [, ...] ) ]

테이블의 column 이름이다. 
자세한 내용은 INSERT INTO 구문의 [ ( column_name [, ...] ) ] 절을 참조한다.

<values clause>

대응하는 column에 할당할 값의 리스트이다.
자세한 내용은 INSERT INTO 구문의 <values clause>를 참조한다.

<from subquery>

Row들을 생성할 질의이다.
자세한 내용은 SELECT 구문의 query expression 절을 참조한다.

DEFAULT VALUES

모든 column들을 기본값으로 채운다.
자세한 내용은 INSERT INTO 구문의 DEFAULT VALUES 절을 참조한다.

<duplicate key clause>

Unique 제약 조건에 위배되었을 경우에 수행할 action을 정의한다.

DO NOTHING

Unique 제약 조건에 위배되는 경우에는 아무것도 하지 않는다.

<do update clause>

Unique 제약 조건에 위배되는 경우에는 <set clause>에 따라 column들의 값을 갱신한다.

<set value clause>

갱신할 column에 할당할 값을 정의한다.
자세한 내용은 INSERT INTO name ... UPDATE 구문의 <set value clause>를 참조한다.

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.
자세한 내용은 INSERT INTO name ... UPDATE 구문의 <set clause>를 참조한다.

<returning clause>

삽입 또는 변경된 row들을 반환한다.

설명

자세한 내용은 INSERT INTO name ... UPDATE 관련 구문들의 차이점을 참조한다.

다음은 네 개의 row들을 삽입한 후에 삽입된 결과를 반환하는 예이다.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ), ( 2 ), ( 3 ), ( 4 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1;

C1
--
 1
 2
 3
 4

4 rows created.

다음은 unique 제약 조건에 위배되어 row들을 갱신한 이후에 갱신된 결과를 반환하는 예이다.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ),( 4 );

4 rows created.

gSQL> INSERT INTO t1 ( SELECT c1 FROM t1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1;

C1
--
 2
 3
 4
 5

4 rows created.

호환성

SQL 표준에서는 <upsert returning statement> 구문을 정의하고 있지 않다.

참조

관련 내용은 다음을 참조한다.

INSERT INTO name ... UPDATE RETURNING ... INTO

기능

테이블에 row 하나를 생성한다. 만약 unique 제약 조건에 위배되는 경우에는 기존 row들을 갱신한다. 이후 생성 또는 변경된 row의 값을 호스트 변수로 얻어온다.

구문

<upsert returning into statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        <returning clause>    
        <into clause>    
    ;
<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

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

<into clause> ::= INTO variable_name [, ...]

사용 범위 및 접근 권한

<upsert returning into statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

table_name

Row를 생성할 대상 테이블의 이름이다. 
만약 unique 제약 조건에 위배되어 update가 수행될 경우에는 변경될 대상 테이블의 이름이다.
자세한 내용은 INSERT INTO name ... UPDATE 구문의 table_name 절을 참조한다.

[ ( column_name [, ...] ) ]

테이블의 column 이름이다. 
자세한 내용은 INSERT INTO 구문의 [ ( column_name [, ...] ) ] 절을 참조한다.

<values clause>

대응하는 column에 할당할 값의 리스트이다.
자세한 내용은 INSERT INTO 구문의 <values clause> 절을 참조한다.

<from subquery>

Row들을 생성할 질의이다.
자세한 내용은 SELECT 구문의 query expression 절을 참조한다.

DEFAULT VALUES

모든 column들을 기본값으로 채운다.
자세한 내용은 INSERT INTO 구문의 DEFAULT VALUES 절을 참조한다.

<duplicate key clause>

Unique 제약 조건에 위배되었을 경우에 수행할 action을 정의한다.

DO NOTHING

Unique 제약 조건에 위배되는 경우에는 아무것도 하지 않는다.

<do update clause>

Unique 제약 조건에 위배되는 경우에는 <set clause>에 따라 column들의 값을 갱신한다.

<set value clause>

갱신할 column에 할당할 값을 정의한다.
자세한 내용은 INSERT INTO name ... UPDATE 구문의 <set value clause> 절을 참조한다.

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.
자세한 내용은 INSERT INTO name ... UPDATE 구문의 <set clause> 절을 참조한다.

<returning clause>

삽입 또는 변경된 row를 반환한다.
자세한 내용은 INSERT INTO name ... UPDATE RETURNING 구문의 <returning clause> 절을 참조한다.

<into clause>

INTO 절에 기술된 변수의 개수는 RETURNING 절에 기술된 expression의 개수와 동일해야 한다. 
생성할 row가 한 건 이하여야 한다. Row가 두 건 이상 생성되면 에러가 발생한다.

설명

자세한 내용은 INSERT INTO name ... UPDATE 관련 구문들의 차이점을 참조한다.

다음은 row 하나를 삽입한 이후에 삽입된 결과를 호스트 변수로 얻어오는 예이다.

gSQL> \VAR v_c1 INTEGER;

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1 INTO :v_c1;

V_C1
----
   1

1 row created.

다음은 unique 제약 조건에 위배되어 row 하나를 갱신한 이후에 갱신된 결과를 호스트 변수로 얻어오는 예이다.

gSQL> \VAR v_c1 INTEGER;

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1 INTO :v_c1;

V_C1
----
   2

1 row created.

호환성

SQL 표준에서는 <upsert returning into statement> 구문을 정의하고 있지 않다.

참조

관련 내용은 다음을 참조한다.

LOCK TABLE

기능

하나 이상의 테이블에 lock을 설정한다.

구문

<lock table statement> ::=
    LOCK TABLE lock target [, ...] 
    IN <lock mode> MODE [<wait clause>]
    ;

<lock mode> ::=
    SHARE
    | EXCLUSIVE
    | ROW SHARE
    | ROW EXCLUSIVE
    | SHARE ROW EXCLUSIVE


<wait clause> ::=
    NOWAIT
    | WAIT time

사용 범위 및 접근 권한

<lock table statement> 구문을 수행하려면 사용자에게 다음 권한 중 하나가 있어야 한다.

구문 규칙 및 파라미터

<lock target>

LOCK 대상 테이블을 명시한다.

<lock mode>

LOCK mode를 명시한다.

<wait clause>

Lock을 획득하기 위한 대기 시간을 명시한다.

설명

Transaction을 COMMIT 하거나 ROLLBACK 할 경우 획득한 모든 lock은 자동으로 해제된다. ROLLBACK TO SAVEPOINT 구문을 사용할 경우 해당 savepoint 이후에 획득한 모든 lock이 해제된다.

사용 예

다음은 다른 transaction이 TABLE t1에 대해 어떠한 변경 연산도 수행할 수 없도록 하는 예이다.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE;

Table locked.

다음은 다수의 table에 LOCK 구문을 수행하는 예이다.

gSQL> LOCK TABLE t1, t2 IN EXCLUSIVE MODE;

Table locked.

다음은 TABLE t1에 SHARE ROW EXCLUSIVE lock을 획득하는 예이다.

gSQL> LOCK TABLE t1 IN SHARE ROW EXCLUSIVE MODE;

Table locked.

다음은 해당 TABLE에 즉시 lock을 획득할 수 있을 경우에만 수행할 수 있는 구문이다. Lock을 획득할 수 없을 경우에는 에러가 발생한다.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE NOWAIT;

Table locked.

다음은 lock을 획득하기 위해 10 초 동안 대기하도록 하는 예이다.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE WAIT 10;

Table locked.

호환성

SQL 표준은 lock table에 대한 개념을 다루지 않고 있다.

참조

관련 내용은 다음을 참조한다.

MERGE

기능

변경 대상 테이블에 조건에 맞는 레코드를 insert 또는 update 또는 delete 한다.

구문

<merge statement> ::=
    MERGE [ <hint clause> ] INTO <target table> [ [ AS ] <target alias> ]
    USING <source relation>
    ON <merge join condition>
    <merge operation specification>
    ;

<target table> ::=
    <table name>

<target alias> ::=
    <correlation name>
    
<source relation> ::=
    {
        <table name> [ [ AS ] <source alias> ]
      | <table subquery> [ [ AS ] <source alias> ]    
    }

<source alias> ::=
    <correlation name>

<merge join condition> ::=
    <search condition>
    
<merge operation specification> ::=
    <merge when clause> [...]

<merge when clause> ::=
      <merge when matched clause>
    | <merge when not matched clause>

<merge when matched clause> ::=
    WHEN MATCHED [ AND <search condition> ] 
        THEN { <merge update> | <merge delete> | <merge do nothing> }

<merge when not matched clause> ::=
    WHEN NOT MATCHED [ AND <search condition> ] 
        THEN { <merge insert> | <merge do nothing> }

<merge update> ::=
    UPDATE SET
    {
        <column name> = { <value expression> | DEFAULT }
      | <left paren> <column name> [, ...] <right paren>
        = <left paren> { <value expression> | DEFAULT } [, ...] <right paren>
    } [, ...]

<merge delete> ::=
    DELETE

<merge insert> ::=
    INSERT
    [ <left paren> <column name> [, ...] <right paren> ]
    {
        VALUES <left paren> <merge insert value element> [, ...] <right paren>
      | DEFAULT VALUES
    }

<merge do nothing> ::=
    DO NOTHING

<merge insert value element> ::=
      <value expression>
    | DEFAULT

사용 범위 및 접근 권한

MERGE 를 위한 별도의 권한은 없다.

<merge statement> 구문을 수행하기 위해 사용자는 다음과 같은 권한이 필요하다.

구문 규칙 및 파라미터

<hint clause>

<target table> 과 <source relation> 의 조인 결과를 얻기 위한 질의 수행에 필요한 힌트를 기술한다.
자세한 내용은  SQL Hint 를 참조한다.

<target table>

변경 대상 테이블을 지정한다.
테이블의 이름에는 schema_name.table_name과 같이 테이블이 소속한 스키마를 정의할 수 있으며, schema_name을 생략할 경우 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.
변경 대상 테이블에는 table 또는 temporary table 을 지정할 수 있다.

<target alias>

<target table> 의 대체 이름 (별칭)이다.

<source relation>

변경 대상 테이블인 <target table> 로 병합할 행을 제공하는 source relation 이다.

<source alias>

<source relation> 의 대체 이름 (별칭)이다.

<merge join condition>

<target table> 과 <source relation> 의 join 조건을 명시한다.
<search condition> 에 <target table>의 컬럼과 <source relation> 의 컬럼을 기술할 수 있다.

<merge operation specification>

하나 이상의 <merge when clause>를 기술한다.

<merge when clause>

<search condition> 이 없는 <merge when matched clause> 는 하나만 기술할 수 있다.
<search condition> 이 없는 <merge when matched clause> 가 기술된 경우, 더 이상의 <merge when matched clause> 를 기술할 수 없다.
gSQL> 
MERGE INTO t1
USING t2
ON t1.c1 = t2.c1
WHEN MATCHED THEN UPDATE SET ( c1, c2 ) = ( t2.c1, t2.c2 )
WHEN MATCHED AND t1.c1 = 100 THEN DO NOTHING;

ERR-42000(16614): unreachable WHEN clause specified after unconditional WHEN clause : 
WHEN MATCHED AND t1.c1 = 100 THEN DO NOTHING
*
ERROR at line 5:
<search condition> 이 없는 <merge when not matched clause> 는 하나만 기술할 수 있다.
<search condition> 이 없는 <merge when not matched clause> 가 기술된 경우, 더 이상의 <merge when not matched clause> 를 기술할 수 없다.
gSQL> 
MERGE INTO t1
USING t2
ON t1.c1 = t2.c1
WHEN NOT MATCHED THEN INSERT VALUES ( c1, c2 )
WHEN NOT MATCHED AND c1 = 4 THEN INSERT DEFAULT VALUES;

ERR-42000(16614): unreachable WHEN clause specified after unconditional WHEN clause : 
WHEN NOT MATCHED AND c1 = 4 THEN INSERT DEFAULT VALUES
*
ERROR at line 5:

<merge when matched clause>

<merge when matched clause> 는 <target table> 과 <source relation> 의 join 결과 레코드들을 대상으로 평가한다.
<target table> 의 레코드들 중 다음을 만족하는 경우 <merge when matched clause> 가 수행된다.
• <merge when matched clause> 의 <search condition> 이 true 로 평가 
• <merge when matched clause> 의 <search condition> 이 없는 경우
<merge when matched clause> 의 <search condition> 에는 <target table> 과 <source relation> 의 컬럼을 모두 참조할 수 있다.
<merge when matched clause> 는 다음 중 하나의 기능을 수행한다.
• <merge update> : 대상 후보 레코드를 갱신한다.
• <merge delete> : 대상 후보 레코드를 삭제한다.
• <merge do nothing> : 대상 후보 레코드에 대한 아무런 일도 하지 않는다.
대상 후보로 결정된 레코드는 이후 기술된 <merge when matched clause> 들의 대상 후보 레코드에서 제외된다.

<merge when not matched clause>

<merge when not matched clause> 는 <target table> 과 <source relation> 의 join 조건을 만족하지 않는 <source relation> 의 레코드들을 대상으로 평가한다.
<source relation> 의 레코드들 중 다음을 만족하는 경우 <merge when not matched clause> 가 수행된다. 
• <merge when not matched clause> 의 <search condition> 이 true 로 평가 
• <merge when not matched clause> 의 <search condition> 이 없는 경우

<merge when not matched clause> 의 <search condition> 에는 <source relation> 의 컬럼만 참조할 수 있다.

<merge when not matched clause> 는 다음 중 하나의 기능을 수행한다. 
• <merge insert> : <target table> 에 새로운 레코드를 삽입한다. 
• <merge do nothing> : 대상 후보 레코드에 대한 아무런 일도 하지 않는다.

대상 후보로 결정된 레코드는 이후 기술된 <merge when not matched clause> 들의 대상 후보 레코드에서 제외된다.

<merge update>

<merge when matched clause> 에서 선정된 대상 후보 레코드들에 대한 갱신을 수행한다.

UPDATE 구문의 <set clause> 만 기술 가능하다.

자세한 내용은 UPDATE 구문의 <set clause> 을 참조한다.

<set clause> 의 <column name> 에는 <target table> 의 컬럼만 기술하여야 하며, <value expression> 에는 <target table> 과 <source relation> 의 컬럼을 모두 참조할 수 있다.

<merge delete>

<merge when matched clause> 에서 선정된 대상 후보 레코드들에 대한 삭제를 수행한다.

<merge do nothing>

<merge when matched clause> 또는 <merge when not matched clause> 에서 선정된 대상 후보 레코드들에 대한 아무런 일도 하지 않는다.

<merge insert>

<merge when not matched clause> 에서 선정된 대상 후보 레코드들이 있을 경우, <target table> 에 삽입할 레코드를 지정한다.
<merge insert value element> 의 <value expression> 으로 <source relation> 의 컬럼을 참조할 수 있다.

설명

MERGE 구문은 조건부로 INSERT, UPDATE 또는 DELETE 를 수행하는 단일 SQL 문이다.
MERGE 구문 수행 결과는 일반 INSERT, UPDATE, DELETE 구문을 수행한 결과와 동일하다.
MERGE 구문의 INSERT, UPDATE, DELETE에는 대상 테이블을 지정하는 구문이 없고, WHERE 절과 OFFSET/LIMIT 절이 없다.
MERGE 구문은 <target table> 과 <source relation> 의 조인 결과를 이용해 조건에 맞게 레코드를 <target table> 에 INSERT 또는 UPDATE 또는 DELETE 하는 작업을 수행한다.
수행 절차는 다음과 같다.

  1. <target table> 과 <source relation> 의 조인 결과로 대상 후보 레코드를 결정한다.

  2. 각 대상 후보 레코드에 대해 MATCHED 또는 NOT MATCHED 상태가 결정된다.

    1. MATCHED

      1. <target table> 과 <source relation> 의 join 조건을 만족하는 join 결과 레코드

    2. NOT MATCHED

      1. <target table> 과 <source relation> 의 join 조건을 만족하지 않는 <source relation> 의 레코드

  3. MATCHED 또는 NOT MATCHED 상태가 결정된 레코드는 WHEN 절이 기술된 순서대로 평가된다.

    1. 각 대상 후보 레코드에 대한 WHEN 절 평가시 TRUE 로 평가되는 첫번째 WHEN 절이 수행된다.

      1. <search condition> 이 TRUE 로 평가

      2. <search condition> 이 없는 경우

  4. 대상 후보 레코드에 대해 하나 이상의 WHEN 절은 수행되지 않는다.

    1. 3 에서 수행된 대상 후보 레코드는 이후 기술된 WHEN 절 수행시 제외된다.

MERGE 수행 과정 예시

### table 정보

gSQL> 
SELECT * FROM t_target ORDER BY c1, c2;
C1 C2
-- --
 2  2
 4  4
 6  6
 8  8
4 rows selected.

gSQL>  
SELECT * FROM t_source ORDER BY c1, c2;
C1 C2
-- --
 2  1
 4  2
 6  3
 8  4
10  5
12  6
14  7
7 rows selected.
### MERGE 구문

MERGE INTO t_target
USING t_source
ON t_target.c1 = t_source.c1
WHEN MATCHED AND t_target.c1 = 4 THEN DELETE
WHEN MATCHED AND t_target.c1 = 2 THEN DO NOTHING
WHEN MATCHED THEN UPDATE SET c1 = t_target.c1 + 100
WHEN NOT MATCHED AND t_source.c1 = 14 THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES ( t_source.c1, t_source.c1 );
### <target table> 과 <source relation> 의 조인 결과로 대상 후보 레코드를 결정

t_target.c1 t_target.c2 t_source.c1 t_source.c2
----------- ----------- ----------- -----------
          2           2           2           1  <-- MATCHED
          4           4           4           2  <-- MATCHED
          6           6           6           3  <-- MATCHED
          8           8           8           4  <-- MATCHED
       null        null          10           5  <-- NOT MATCHED
       null        null          12           6  <-- NOT MATCHED
       null        null          14           7  <-- NOT MATCHED
### MATCHED 또는 NOT MATCHED 상태가 결정된 레코드는 WHEN 절이 기술된 순서대로 평가

WHEN MATCHED AND t_target.c1 = 4 THEN DELETE                      1
WHEN MATCHED AND t_target.c1 = 2 THEN DO NOTHING                  2
WHEN MATCHED THEN UPDATE SET c1 = t_target.c1 + 100               3
WHEN NOT MATCHED AND t_source.c1 = 14 THEN DO NOTHING             4
WHEN NOT MATCHED THEN INSERT VALUES ( t_source.c1, t_source.c1 ); 5

t_target.c1 t_target.c2 t_source.c1 t_source.c2
----------- ----------- ----------- -----------
          2           2           2           1  <-- MATCHED     2 DO NOTHING
          4           4           4           2  <-- MATCHED     1 DELETE
          6 (106)     6           6           3  <-- MATCHED     3 UPDATE 
          8 (108)     8           8           4  <-- MATCHED     3 UPDATE
       null (10)   null (10)     10           5  <-- NOT MATCHED 5 INSERT
       null (12)   null (12)     12           6  <-- NOT MATCHED 5 INSERT
       null        null          14           7  <-- NOT MATCHED 4 DO NOTHING
### MERGE 구문 수행 결과 

gSQL> 
MERGE INTO t_target
USING t_source
ON t_target.c1 = t_source.c1
WHEN MATCHED AND t_target.c1 = 4 THEN DELETE
WHEN MATCHED AND t_target.c1 = 2 THEN DO NOTHING
WHEN MATCHED THEN UPDATE SET c1 = t_target.c1 + 100
WHEN NOT MATCHED AND t_source.c1 = 14 THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES ( t_source.c1, t_source.c1 );
5 rows merged.

gSQL> 
SELECT * FROM t_target ORDER BY c2, c1;
 C1 C2
--- --
  2  2
106  6
108  8
 10 10
 12 12
5 rows selected.

사용 예

다음은 직원들의 부서 이동 등의 변동 사항을 employee 테이블에 반영하는 질의 예이다.

DROP TABLE employee;
CREATE TABLE employee ( id              INTEGER,
                        department_id   INTEGER,
                        name            VARCHAR( 10 ) );

INSERT INTO employee VALUES ( 1, 10, 'KIM' );
INSERT INTO employee VALUES ( 2, 10, 'LEE' );
INSERT INTO employee VALUES ( 3, 20, 'PARK' );
INSERT INTO employee VALUES ( 4, 20, 'JUNG' );
INSERT INTO employee VALUES ( 5, 30, 'SONG' );
COMMIT;

DROP TABLE dep_transfer;
CREATE TABLE dep_transfer( emp_id              INTEGER,
                           curr_department_id  INTEGER,
                           new_department_id   INTEGER,
                           name                VARCHAR( 10 ),
                           is_retire           BOOLEAN );

INSERT INTO dep_transfer VALUES ( 1,   10,   30,   'KIM', FALSE );
INSERT INTO dep_transfer VALUES ( 3,   20,   30,  'PARK', FALSE );
INSERT INTO dep_transfer VALUES ( 4,   20, NULL,  'JUNG', TRUE );
INSERT INTO dep_transfer VALUES ( 5,   30,   10,  'SONG', FALSE );
INSERT INTO dep_transfer VALUES ( 6, NULL,   10, 'HWANG', FALSE );
COMMIT;

gSQL> 
MERGE INTO employee
USING dep_transfer
ON employee.id = dep_transfer.emp_id
WHEN MATCHED AND is_retire = TRUE THEN DELETE
WHEN MATCHED THEN UPDATE SET department_id = new_department_id
WHEN NOT MATCHED THEN INSERT VALUES ( emp_id, new_department_id, name );
5 rows merged.

gSQL> 
SELECT * FROM employee;      
ID DEPARTMENT_ID NAME 
-- ------------- -----
 1            30 KIM  
 2            10 LEE  
 3            30 PARK 
 5            10 SONG 
 6            10 HWANG
5 rows selected.

호환성

SQL 표준은 MERGE 구문에서 DO NOTHING 절을 정의하지 않고 있다.

SQL 표준 호환성

Feature ID

설명

지원 여부

F781

Self-referencing operations

X

S024

Enhanced structured types

X

F312

MERGE statement

O

F313

Enhanced MERGE statement

O

F314

MERGE statement with DELETE branch

O

참조

관련 내용은 다음을 참조한다.

NOAUDIT POLICY

기능

Audit policy를 비활성화한다.

구문

<noaudit policy statement> ::= 
    NOAUDIT POLICY policy_name
    [ <specified_user_option> ]
    ;

<specified_user_option> ::=
      BY user_name [, ...]

사용 범위 및 접근 권한

<noaudit policy statement> 구문을 수행하려면 사용자에게 AUDIT SYSTEM ON DATABASE 권한이 있어야 한다.

구문 규칙 및 파라미터

policy_name

비활성화할 audit policy 객체의 이름이다. 
비활성화 된 audit policy는 기존 session에 영향을 미치지 않으며 새로 생성되는 session에만 영향을 준다.

<specified_user_option>

감사 대상에서 제외할 사용자를 명시한다.

AUDIT POLICY 구문과 달리 NOAUDIT POLICY 구문에는 EXCEPT 옵션이 없다.

AUDIT POLICY name BY 절을 사용한 경우 NOAUDIT POLICY name BY 구문으로 비활성화하며 
AUDIT POLICY name EXCEPT 절을 사용한 경우 BY 절 없이 NOAUDIT POLICY name 구문으로 비활성화해야 한다.

AUDIT POLICY 구문의 사용 방법에 따라 다음과 같이 NOAUDIT POLICY 구문을 사용하여 해당 옵션을 비활성화해야 한다.

Audit policy 활성화/ 비활성화

유형

AUDIT POLICY 구문

NOAUDIT POLICY 구문

전체 사용자

AUDIT POLICY p1

NOAUDIT POLICY p1

BY를 사용

AUDIT POLICY p1 BY u1

NOAUDIT POLICY p1 BY u1

EXCEPT를 사용

AUDIT POLICY p1 EXCEPT u1

NOAUDIT POLICY p1

활성화된 모든 user들을 비활성화한 경우, audit policy 객체가 완전히 비활성화된다.

설명

Audit policy 객체의 활성화 정보는 다음과 같이 조회한다.

SELECT policy_name
     , enabled_opt
     , user_name
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';
NOAUDIT POLICY 구문은 AUDIT POLICY 지정 방식에 따라 생성된 개별 활성화 정보를 삭제한다. 
위의 질의를 통해 활성화한 정보가 없을 경우, audit policy는 완전히 비활성화된다.

다음과 같이 모든 user를 활성화한 경우, NOAUDIT POLICY BY 절은 영향을 미치지 않는다.

AUDIT POLICY p1;
NOAUDIT POLICY p1 BY u1;
NOAUDIT POLICY p1;

하나 이상의 user들을 개별적으로 활성화한 경우 AUDIT POLICY 설정 방법에 따라 NOAUDIT POLICY 구문을 사용해야 한다.

BY를 이용해 활성화한 경우

다음과 같이 audit policy를 활성화한 경우,

AUDIT POLICY p1 WHENEVER NOT SUCCESSFUL;
AUDIT POLICY p1 BY u1;
AUDIT POLICY p1 BY u2;

활성화 정보를 조회하면 다음과 같다.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS  WHEN_FAILURE
-----------  -----------  ---------    ------------  ------------
P1           BY           ALL USERS    NO            YES
P1           BY           U1           YES           YES
P1           BY           U2           YES           YES

다음은 NOAUDIT POLICY 구문을 수행하고 활성화 정보를 조회하는 예이다.

NOAUDIT POLICY p1;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS    WHEN_FAILURE
-----------  -----------  ---------    ------------    ------------
P1           BY           U1           YES             YES
P1           BY           U2           YES             YES
ALL USERS의 failure에 대한 감사가 비활성화되었으며, u1, u2 사용자에 대한 감사는 여전히 활성화되어 있다.

다음과 같이 BY 옵션을 통해 NOAUDIT POLICY 구문을 추가적으로 사용하면 audit policy p1은 완전히 비활성화된다.

NOAUDIT POLICY p1 BY u1, u2;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

no rows selected.

EXCEPT를 이용해 활성화한 경우

다음과 같이 audit policy를 활성화한 경우,

AUDIT POLICY p1 EXCEPT u1, sys;

활성화 정보를 조회하면 다음과 같다.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS    WHEN_FAILURE
-----------  -----------  ---------    ------------    ------------
P1           EXCEPT       U1           YES             YES
P1           EXCEPT       SYS          YES             YES
AUDIT POLICY 구문과 달리 NOAUDIT POLICY 구문에는 EXCEPT option이 없으므로 다음과 같이 옵션 없이 구문을 수행한다.
NOAUDIT POLICY p1;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

no rows selected.
즉, EXCEPT 옵션을 이용해 audit policy를 활성화한 경우, NOAUDIT POLICY 구문으로 개별 사용자를 다시 비활성화할 수 없다.

사용 예

다음은 전체 사용자를 비활성화한 예이다.

NOAUDIT POLICY table_pol;

다음은 BY를 사용하여 활성화된 특정 사용자를 비활성화하는 예이다.

NOAUDIT POLICY table_pol BY u1;

호환성

SQL 표준에는 audit policy가 없다.

참조

관련 내용은 다음을 참조한다.

OPEN cursor_name

기능

커서를 연다.

구문

<open statement> ::=
    OPEN cursor_name [ <parameter using clause> ]
    ;

<parameter using clause> ::=
      <using parameter arguments>

<using parameter arguments> ::=
    USING variable_name [, ...]

사용 범위 및 접근 권한

cursor_name이 PREPARE statement_name 구문과 DECLARE cursor_name 구문을 사용해 선언한 동적 커서인 경우 embedded SQL에서 사용 가능하다.

cursor_name을 선언한 DECLARE cursor_name 구문에 포함된 <cursor query>의 권한과 동일하다.

구문 규칙 및 파라미터

cursor_name

세션 내에서 DECLARE cursor_name 구문으로 선언된 커서이어야 한다.

<parameter using clause>

Embedded SQL에서 사용할 수 있다.

<parameter using clause> 구문이 사용될 경우, cursor_name이 PREPARE statement_name 구문과 DECLARE cursor_name 구문을 이용해 선언한 동적 커서여야 한다.

<using parameter arguments>

<using parameter arguments> 구문이 사용될 경우, variable_name의 개수는 PREPARE statement_name 구문이 참조하는 query 문장에 포함된 parameter의 개수와 동일해야 한다.

variable_name은 나열된 순서에 따라 dynamic parameter에 순서대로 대응된다.

{
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT c1, c2 FROM t1 WHERE c1 IN ( ?, ?, ? )';
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
    EXEC SQL OPEN cur1 USING :sValue1, :sValue2, :sValue3;
    ...
    EXEC SQL WHENEVER NOT FOUND DO break;
    for(;;)
    {
        EXEC SQL FETCH cur1 INTO :sC1, :sC2;    
    }
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    ...
    EXEC SQL CLOSE cur1;    
    ... 
}

설명

Cursor는 session 내에서 구별되는 객체이며, 현재 session 내에서 사용되고 있는 cursor는 다른 session에서 사용되고 있는 cursor와 무관하다.

OPEN cursor_name 구문을 사용하려면 DECLARE cursor_name 구문으로 선언된 커서여야 하며, 커서는 닫혀 있는 상태여야 한다.

사용 예

다음은 interactive SQL (gsql)에서 cursor를 선언하고 OPEN cursor 구문을 사용하는 예이다.

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.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

B031

Basic dynamic SQL

O

참조

관련 내용은 다음을 참조한다.

PREPARE statement_name

기능

반복 수행을 위한 dynamic SQL 문장을 준비한다.

구문

<prepare statement> ::=
    PREPARE statement_name FROM <SQL statement variable>
    ;

<SQL statement variable> ::=
      variable_name
    | 'sql statement'
    | "sql statement"
    | sql statement

사용 범위 및 접근 권한

Embedded SQL에서 사용할 수 있다.
Dynamic SQL 구문의 종류에 부합하는 수행 권한이 있어야 한다.

구문 규칙 및 파라미터

statement_name

준비할 statement의 이름이다.
statement 이름의 길이는 128 바이트보다 작아야 한다.
이후에 수행될 EXECUTE statement_name 구문 또는 DECLARE cursor_name 구문은 statement_name을 참조한다.
동일한 statement_name이 존재할 경우, 이전에 준비된 dynamic SQL은 삭제된다.
{
    ...

    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1';
    ...
    EXEC SQL PREPARE stmt1 FROM 'UPDATE t1 SET c1 = c1 + 10';
    ...
}

<SQL statement variable>

<SQL statement variable>은 다음과 같이 네 가지 유형으로 사용된다.

Single-quoted string 내에 문자열 data를 표현하려면 다음과 같이 single quote (')를 두 번 기술한다.

{
    ...
    PREPARE stmt_name FROM 'INSERT INTO t1 VALUES ( ''literal data'' )'; 
    ...
}
<SQL statement variable>이 참조하는 dynamic SQL 문장은 host 변수 (:var)나 parameter marker (?)를 사용할 수 있다. 
단, quote 없는 SQL 문장을 사용할 경우 parameter marker (?)를 사용할 수 없다.
참조되는 dynamic SQL 문장의 특성에 따라 변수는 input 또는 output dynamic parameter가 된다.
Dynamic SQL 문장 내에 기술된 dynamic parameter는 변수의 이름이 아무 의미가 없으며 종류에 관계없이 구문에 기술된 순서에 따라 식별된다.
{
    ...
    int sValue1;
    int sValue2;
    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 BETWEEN ? AND ?';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2;   
    ...
}
{
    ...
    int sValue1;
    int sValue2;
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT SUM(c2) INTO :v1 FROM t1 WHERE c1 > :v2';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2;
    ...
}

variable_name

variable_name에 대응하는 type은 character string이어야 한다. 
variable_name에 정의된 dynamic SQL 문장은 유효한 문장이어야 한다.

sql statement

sql statement에 정의된 dynamic SQL 문장은 유효한 문장이어야 한다.

설명

PREPARE statement_name FROM sql_string 구문은 EXECUTE나 cursor를 사용하기 위해 SQL 문을 분석한다. statement_name은 embedded SQL 소스 코드에서 precompiler에게 statement를 알려주는 식별자로써 host variable이 아니기 때문에 별도의 type이나 선언이 필요하지 않다.

자세한 내용은 Embedded Dynamic SQL을 참조한다.

사용 예

다음은 embedded SQL 소스 코드 내에서 PREPARE statement_name를 사용하는 예이다.

{
    ...
    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;
    }
    ...
}

PREPARE statement_name이 사용된 전체 소스 코드는 Dynamic Embedded SQL Example Program에서 확인할 수 있다.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

B031

Basic Dynamic SQL

O

B034

Dynamic specification of cursor attributes

X

참조

관련 내용은 다음을 참조한다.

PURGE

기능

휴지통에 저장되어 있는 객체들을 영구적으로 제거한다.

구문

<purge statement> :==
    PURGE <purge action>
    ;

<purge action> :==
     TABLE table_name
   | INDEX index_name
   | CONSTRAINT constraint_name
   | TRIGGER trigger_name  
   | TABLESPACE tablespace_name [ USER user_name ]
   | RECYCLEBIN 
   | USER_RECYCLEBIN
   | DBA_RECYCLEBIN

사용 범위 및 접근 권한

<purge statement> 구문을 수행하려면 사용자에게 다음 권한 중 하나가 있어야 한다.

구문 규칙 및 파라미터

table_name

휴지통에 저장된 객체 이름 또는 제거된 테이블의 이름이다.
제거된 테이블의 이름에는 schema_name.table_name과 같이 테이블이 소속한 스키마를 정의할 수 있으며, schema_name을 생략할 경우 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.
테이블과 관련된 인덱스와 제약 조건들도 함께 제거된다.

index_name

휴지통에 저장된 객체 이름 또는 제거된 인덱스의 이름이다.
schema_name.index_name과 같이 인덱스가 속한 스키마를 정의할 수 있는데 schema_name을 생략할 경우, 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.
제약 조건으로 생성된 key 인덱스는 제약 조건으로 삭제해야 한다.

constraint_name

휴지통에 저장된 객체 이름 또는 삭제된 제약 조건의 이름이다.

trigger_name

휴지통에 저장된 객체 이름 또는 삭제된 trigger의 이름이다.

tablespace_name

테이블스페이스의 이름이다.
USER를 지정할 때는 DROP ANY TABLE ON DATABASE 권한이 필요하다.

user_name

사용자의 이름이다.

recyclebin

user_recyclebin의 alias 이다.

user_recyclebin

사용자가 소유한 휴지통을 모두 제거한다.

dba_recyclebin

데이터베이스의 모든 휴지통을 제거한다.
PURGE DBA_RECYCLEBIN ON DATABASE 권한이 필요하다.

설명

휴지통에 저장된 객체 이름이나 제거된 테이블의 이름을 사용하여 휴지통에 보관되어 있는 객체들을 영구적으로 제거한다. 만약 제거 대상 테이블과 동일한 이름의 테이블이 있는 경우, 가장 오래된 객체를 제거한다.
사용자가 소유한 휴지통 객체에서 테이블스페이스를 지정하여 테이블스페이스에 포함된 객체들을 제거할 수 있는데 이 때 사용자를 지정하면 해당 사용자의 명시된 테이블스페이스에 포함된 객체들만 제거할 수 있다.
PURGE TABLE, INDEX, CONSTRAINT 구문은 트랜잭션이 COMMIT 되기 전이라면 ROLLBACK 할 수 있다. 반면 PURGE TABLESPACE, RECYCLEBIN, DBA_RECYCLEBIN 구문은 ROLLBACK 할 수 없으며, 구문을 수행한 트랜잭션이 자동으로 COMMIT 된다.

사용 예

다음은 휴지통에 저장된 테이블을 제거하는 예이다.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$135B9926166111EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE TABLE t1;

Table purged.

다음은 휴지통에 저장된 인덱스를 제거하는 예이다.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$135B9926166111EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE INDEX t1_idx1;

Index purged.

다음은 휴지통에 저장된 제약 조건을 제거하는 예이다.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      

3 rows selected.

gSQL> PURGE CONSTRAINT t1_primary_key;

Constraints purged.

다음은 휴지통에 저장된 테이블스페이스에 포함된 객체들을 제거하는 예이다.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE, TABLESPACE_NAME FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE TABLESPACE_NAME
------------------------------------ ------------- ----------- ---------------
BIN$02C76B24166311EA9C5C835D3E4BBBF7 T1            TABLE       MEM_DATA_TBS   

1 row selected.

gSQL> PURGE TABLESPACE MEM_DATA_TBS;

Tablespace purged.

다음은 사용자가 소유한 휴지통을 모두 제거하는 예이다.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$64F6BFFC166311EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$64F6C042166311EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$64F6C010166311EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$64F6C024166311EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE USER_RECYCLEBIN;

Recyclebin purged.

다음은 시스템의 모든 휴지통을 제거하는 예이다.

gSQL> SELECT OWNER, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OWNER OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
----- ------------------------------------ -------------------- -----------
TEST  BIN$F0FB26F0166311EAA7C5D51B86D72AB6 T1                   TABLE      
TEST  BIN$F0FB272C166311EAA7C5D51B86D72AB6 T1_PRIMARY_KEY       CONSTRAINT 
TEST  BIN$F0FB2704166311EAA7C5D51B86D72AB6 T1_PRIMARY_KEY_INDEX INDEX      
TEST  BIN$F0FB2718166311EAA7C5D51B86D72AB6 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

호환성

SQL 표준에서는 <purge statement>를 다루지 않고 있다.

참조

관련 내용은 다음을 참조한다.

RELEASE SAVEPOINT savepoint_specifier

기능

저장점을 제거한다.

구문

<release savepoint statement> ::=
    RELEASE SAVEPOINT savepoint_name 
    ;

구문 규칙 및 파라미터

savepoint_name

저장점의 이름으로써 반드시 존재해야 한다. 
이름의 길이는 128 바이트보다 작아야 한다.

설명

다수의 savepoint가 정의되어 있을 경우, RELEASE SAVEPOINT savepoint_name 구문을 수행할 때savepoint_name 이후에 정의된 savepoint도 함께 제거된다.

사용 예

다음은 savepoint를 제거하는 예이다.

gSQL> RELEASE SAVEPOINT sp2;

Savepoint dropped.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T271

Savepoints

O

참조

관련 내용은 다음을 참조한다.

REVOKE privileges FROM

기능

사용자 또는 role에게 부여된 권한을 취소한다.

구문

<revoke privilege statement> ::=
    REVOKE [ <revoke option extention> ] <privilege>
      FROM <grantee> [, ...]
      [ <revoke behavior> ]
    ;

<revoke option extention> ::=
      GRANT OPTION FOR

<grantee> ::=
      PUBLIC
    | <user_identifier>
    | <role_name>

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

구문 규칙 및 파라미터

<privilege>

Revokee (권한을 취소당할 사용자 또는 role)로부터 취소할 권한이다.

Revoker (구문을 수행하는 사용자)는 다음 조건 중 하나를 만족해야 한다.

ALL [PRIVILEGES]를 사용하는 경우, 만족하는 <privilege>가 없더라도 성공한다.

<privilege> 종류에 대한 내용은 GRANT privileges TO 구문의 <privilege> 절을 참조한다.

<grantee>

권한을 취소당할 사용자 또는 role이다.

GRANT OPTION FOR

권한에 포함된 WITH GRANT OPTION을 삭제한다. 
Dependent privilege의 WITH GRANT OPTION도 함께 삭제한다.

권한은 그대로 유지된다.

<revoke behavior>

설명

REVOKE privilege와 같은 Data Definition Language (DDL) 구문도 트랜잭션이 COMMIT 되기 전이라면 ROLLBACK 할 수 있다.

다음과 같은 DROP 구문을 수행할 경우, 별도로 REVOKE 구문을 수행하지 않더라도 해당 객체와 관련된 모든 권한 정보가 삭제된다.

사용 예

다음은 table t1에 대한 다수의 권한을 REVOKE하는 예이다.

gSQL> REVOKE INSERT, UPDATE, DELETE, LOCK, ALTER, INDEX ON t1 FROM u1;

Revoke succeeded.

다음은 모든 authorization (사용자와 role)을 의미하는 PUBLIC 계정에 부여된 SELECT ON TABLE t1 권한을 REVOKE 하는 예이다. 단, PUBLIC 계정의 권한만 제거될 뿐, 특정 사용자 또는 role에게 명시적으로 부여된 SELECT ON TABLE t1 권한이 제거되는 것은 아니다.

gSQL> REVOKE SELECT ON t1 FROM PUBLIC;

Revoke succeeded.

다음은 user u1에게 부여된 SELECT ON TABLE t1 권한은 그대로 두고 다른 사용자에게 해당 권한을 부여할 수 있는 GRANT OPTION만 REVOKE하는 예이다.

gSQL> REVOKE GRANT OPTION FOR SELECT ON t1 FROM u1;

Revoke succeeded.

다음은 RESTRICT 옵션을 이용해 user u1에게 부여한 권한을 REVOKE 하면서 u1이 다른 사용자에게 해당 권한을 부여할 경우 에러가 발생하는 예이다. 이런 dependent privilege들도 함께 제거하려 할 경우 CASCADE 옵션을 사용한다.

gSQL> REVOKE SELECT ON t1 FROM u1 RESTRICT;

ERR-2B000(16235): dependent privilege descriptors still exist

gSQL> REVOKE SELECT ON t1 FROM u1 CASCADE;

Revoke succeeded.

다음은 role1에게서 table t1에 대한 다수의 권한을 REVOKE 하는 예이다.

gSQL> REVOKE INSERT, UPDATE, DELETE, LOCK, ALTER, INDEX ON t1 FROM role1;

Revoke succeeded.

호환성

SQL 표준에서는 다음 privilege들을 정의하지 않고 있다.

SQL 표준의 <revoke behavior>와는 다음과 같은 차이가 있다.

SQL 표준 호환성

Feature ID

설명

지원 여부

T331

Basic roles

O

T332

Extended roles

X

F034

Extended REVOKE statement

O

S081

Subtables

X

참조

관련 내용은 다음을 참조한다.

REVOKE role FROM

기능

다른 사용자나 role에게 부여된 role을 취소한다.

구문

<revoke role statement> ::=
     REVOKE [ ADMIN OPTION FOR ] <role revoked> [ , ...... ] 
            FROM <grantee> [ , ...... ]
     ;

<grantee> ::=
      PUBLIC
    | <user_identifier>
    | <role_name>

<role revoked> ::=
     <role_name>

사용 범위 및 접근 권한

<revoke role statement> 구문을 수행하기 위해서는 다음 조건 중 하나를 만족해야 한다.

구문 규칙 및 파라미터

<role revoked>

Role을 취소하고자 하는 role 이름이다.

<grantee>

Role을 취소당할 사용자 또는 role이다.

ADMIN OPTION FOR

Role에 대한 WITH ADMIN OPTION을 삭제한다.
부여된 role은 그대로 유지된다.

설명

다른 사용자나 role에게서 role을 회수한다.
REVOKE role과 같은 Data Definition Language (DDL) 구문도 트랜잭션이 COMMIT 되기 전이라면 ROLLBACK 할 수 있다.
DROP ROLE을 수행할 경우, 별도로 REVOKE role 구문을 수행하지 않더라도 부여된 role 정보는 모두 삭제된다.

사용 예

다음은 GRANT ROLE ON DATABASE 권한이 있는 사용자가 role을 취소하는 예이다.

gSQL> GRANT GRANT ROLE ON DATABASE TO u1;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 
U1      GRANT ROLE ON DATABASE     

2 rows selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE ADMIN_OPTION
------- ------------ ------------
ROLE2   ROLE1        NO          

1 row selected.

gSQL> \connect u1 u1

gSQL> REVOKE role1 FROM role2;

Revoke succeeded.

다음은 role에 대한 WITH ADMIN OPTION이 있는 사용자가 role을 취소하는 예이다.

gSQL> GRANT role1 TO u1 WITH ADMIN OPTION;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 

1 row selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE ADMIN_OPTION
------- ------------ ------------
ROLE2   ROLE1        NO          
U1      ROLE1        YES         

2 rows selected.


gSQL> \connect u1 u1

gSQL> REVOKE role1 FROM role2;

Revoke succeeded.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T331

Basic roles

O

T332

Extended roles

X

F034

Extended REVOKE statement

O

S081

Subtables

X

참조

관련 내용은 다음을 참조한다.

ROLLBACK

기능

트랜잭션을 취소하거나, 저장점 이후의 작업을 취소한다.

구문

<rollback statement> ::=
    ROLLBACK [ WORK ] [ <rollback force clause> | <savepoint clause> ]
    ;

<rollback force clause> ::=
    FORCE 'xid_string' [ COMMENT 'comment_string' ]

<savepoint clause> ::=
    TO SAVEPOINT savepoint_name

구문 규칙 및 파라미터

WORK

동작에 영향을 미치지 않는 예약어이다.

<rollback force clause>

분산 트랜잭션을 수동으로 rollback 할 때 사용한다.

<savepoint clause>

현재 트랜잭션의 ROLLBACK 범위를 명시한다.

설명

ROLLBACK 구문은 트랜잭션 내에서 수행된 다음 구문들을 rollback 한다.

예외적으로, DDL 중에 OS 자원을 다루거나 DATA TYPE을 변경하는 다음 구문들은 rollback 되지 않고 구문을 수행할 때 자동으로 COMMIT 된다.

사용 예

다음은 INSERT 구문을 ROLLBACK 하는 예이다.

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

1 row created.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

gSQL> ROLLBACK;

Rollback complete.

gSQL> SELECT * FROM t1;

no rows selected.

다음은 DROP TABLE 구문을 수행한 후에 이를 ROLLBACK 하는 예이다.

gSQL> DROP TABLE t1;

Table dropped.

gSQL> SELECT * FROM t1;

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

gSQL> ROLLBACK;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T271

Savepoints

O

T261

Chained transactions

X

참조

관련 내용은 다음을 참조한다.

SAVEPOINT savepoint_specifier

기능

저장점을 정의한다.

구문

<savepoint statement> ::=
    SAVEPOINT savepoint_name 
    ;

구문 규칙 및 파라미터

savepoint_name

저장점 이름이다. 
저장점 이름이 기존의 저장점 이름과 중복될 경우 기존의 저장점이 삭제된다. 
이름의 길이는 128 바이트보다 작아야 한다.

설명

정의한 savepoint는 ROLLBACK TO SAVEPOINT 구문 (ROLLBACK 구문 참조)에서 사용되며, 해당 savepoint까지 수행된 DML, DDL 구문이 철회되고 해당 구문이 획득한 lock도 해제된다.

정의한 savepoint는 transaction을 COMMIT 하거나 ROLLBACK 할 때 자동으로 제거되는데 RELEASE SAVEPOINT savepoint_specifier 구문을 사용하여 명시적으로 제거할 수도 있다.

사용 예

다음은 savepoint를 정의하고 ROLLBACK TO SAVEPOINT 구문을 사용하는 예이다.

gSQL> SAVEPOINT sp1;

Savepoint created.

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

1 row created.

gSQL> SAVEPOINT sp2;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 2, 'someone' );

1 row created.

gSQL> SAVEPOINT sp3;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 3, 'anyone' );

1 row created.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous
 2 someone  
 3 anyone   

3 rows selected.

gSQL> ROLLBACK TO SAVEPOINT sp3;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous
 2 someone  

2 rows selected.

gSQL> ROLLBACK TO SAVEPOINT sp2;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

gSQL> ROLLBACK TO SAVEPOINT sp1;

Rollback complete.

gSQL> SELECT * FROM t1;

no rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T271

Savepoints

O

참조

관련 내용은 다음을 참조한다.

SELECT

query expression

기능

하나 이상의 table 또는 view에서 원하는 row를 검색한다.

구문

<query expression> ::=
    [ <with clause> ] <query expression body> [ <order by clause> ] [ <offset limit clause> ]

<query expression body> ::=
      <query term>
    | <set operator>

<query term> ::=
      <query specification>
    | <left paren> <query expression body> [ <order by clause> ] [ <offset limit clause> ] <right paren>

사용 범위 및 접근 권한

<query expression> 구문을 수행하려면 구문에 사용된 모든 테이블에 대해 다음 권한 중 하나를 가져야 한다.

구문 규칙 및 파라미터

<with clause>

<with clause>는 임시 결과 집합을 정의하고, 그 결과 집합을 참조할 수 있다. 
자세한 내용은 with clause를 참조한다.

<set operator>

부질의 (subquery) 간의 집합 연산을 수행한다.
자세한 내용은 set operator 절을 참조한다.

<query specification>

하나의 부질의 (subquery)를 기술한다.
자세한 내용은 query specification 절을 참조한다.

<order by clause>

검색 결과에 대한 정렬 정보를 기술한다.
자세한 내용은 order by clause를 참조한다.

<offset limit clause>

검색 결과 집합에서 skip 할 row의 개수와 fetch 할 row의 개수를 기술한다.
자세한 내용은 offset limit clause를 참조한다.

설명

SELECT 구문으로 query를 기술한다.
<with clause>, <order by clause>, <offset limit clause>는 생략할 수 있다.
<set operator>를 사용하여 둘 이상의 부질의 (subquery)를 가질 수 있다.

사용 예

다음은 SELECT 구문의 예이다.

gSQL> SELECT s_name, s_nation FROM supplier;

S_NAME                    S_NATION     
------------------------- -------------
Supplier#1                FRANCE       
Supplier#2                KOREA        
Supplier#3                GERMANY      
Supplier#4                UNITED STATES
Supplier#5                CANADA       

5 rows selected.

다음은 <order by clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT s_name, s_nation FROM supplier ORDER BY s_name DESC;

S_NAME                    S_NATION
------------------------- -------------
Supplier#5                CANADA
Supplier#4                UNITED STATES
Supplier#3                GERMANY
Supplier#2                KOREA
Supplier#1                FRANCE

5 rows selected.

다음은 <offset limit clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT s_name, s_nation FROM supplier OFFSET 1;

S_NAME                    S_NATION
------------------------- -------------
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

4 rows selected.

gSQL> SELECT s_name, s_nation FROM supplier LIMIT 1; 

S_NAME                    S_NATION
------------------------- --------
Supplier#1                FRANCE  

1 row selected.

다음은 <order by clause>와 <offset limit clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT s_name, s_nation FROM supplier ORDER BY s_name DESC OFFSET 3 LIMIT 1; 

S_NAME                    S_NATION
------------------------- --------
Supplier#2                KOREA   

1 row selected.

다음은 <with clause>를 사용한 SELECT 구문의 예이다.

* Non Recursive CTE

gSQL>
WITH revenue ( supplier_no, total_revenue ) AS
      (
            SELECT
                   l_suppkey,
                   SUM(l_extendedprice * (1 - l_discount))
              FROM lineitem
             WHERE l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
             GROUP BY
                   l_suppkey
      )
select
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       ROUND( total_revenue, 2 ) as total_revenue
  from
       supplier,
       revenue
 where
       s_suppkey = supplier_no
   and total_revenue = (
                           select
                                  max(total_revenue)
                             from
                                  revenue
                       )
order by
      s_suppkey;

S_SUPPKEY S_NAME                    S_ADDRESS         S_PHONE         TOTAL_REVENUE
--------- ------------------------- ----------------- --------------- -------------
     8449 Supplier#000008449        Wp34zim9qYFbVctdW 20-469-856-8873    1772627.21

1 row selected.

* Recursive CTE

gSQL>
WITH GenerateRecord ( c1, c2 ) AS 
     (
          SELECT 1, 11
            FROM dual
          UNION ALL
          SELECT c1 + 1, c2 + 1
            FROM GenerateRecord
           WHERE c1 < 10
     )
SELECT c1, c2 FROM GenerateRecord;

C1 C2
-- --
 1 11
 2 12
 3 13
 4 14
 5 15
 6 16
 7 17
 8 18
 9 19
10 20

10 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T121

WITH (excluding RECURSIVE ) in query expression

O

T122

WITH (excluding RECURSIVE ) in subquery

O

T131

Recursive query

O

T132

Recursive query in subquery

O

F661

Simple tables

O

F302

INTERSECT table operator

O

F301

CORRESPONDING in query expressions

X

T551

Optional key words for default syntax

O

F304

EXCEPT ALL table operator

O

F850

Top-level <order by clause>in <query expression>

O

F851

<order by clause>in subqueries

O

F855

Nested <order by clause>in <query expression>

O

F856

Nested <fetch first clause>in <query expression>

O

F857

Top-level <fetch first clause>in <query expression>

O

F858

<fetch first clause>in subqueries

O

F860

dynamic <fetch first row count>in <fetch first clause>

X

F861

Top-level <result offset clause>in <query expression>

O

F862

<result offset clause>in subqueries

O

F863

Nested <result offset clause>in <query expression>

O

F865

dynamic <offset row count>in <result offset clause>

X

F866

FETCH FIRST clause: PERCENT option

X

F867

FETCH FIRST clause: WITH TIES option

X

with clause

기능

<with clause>는 임시 결과 집합을 정의하고, 그 결과 집합을 참조할 수 있다.
이는 SELECT 구문 내에서 정의되고 참조되며 이름이 부여된 임시 결과 집합으로서 Common Table Expression (CTE) 이라고 한다.

구문

<with clause> ::=
    WITH <with list>

<with list> ::=
    <with list element> [ { <comma> <with list element> }... ]

<with list element> ::=
    <query name> [ <left paren> <with column list> <right paren> ] 
        AS <table subquery> [ <search or cycle clause> ]

<with column list> ::=
    <column name list>   
 
<search or cycle clause> ::=
    <search clause>
  | <cycle clause>
  | <search clause> <cycle clause>

<search clause> ::=
    SEARCH <recursive search order> SET <sequence column>

<recursive search order> ::=
    DEPTH FIRST BY <ordering column list>
  | BREADTH FIRST BY <ordering column list>

<ordering column list> ::= 
    <ordering column> [ { <comma> <ordering column> }... ]

<ordering column> ::= 
    <column name> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

<sequence column> ::=
    <column name>

<cycle clause> ::=
    CYCLE <cycle column list> SET <cycle mark column> TO <cycle mark value>
        DEFAULT <non-cycle mark value>

<cycle column list> ::=
    <cycle column> [ { <comma> <cycle column> }... ]

<cycle column> ::=
    <column name>
    
<cycle mark column> ::=
    <column name>
 
<cycle mark value> ::=
    <value expression>

<non-cycle mark value> ::=
    <value expression>

사용 범위 및 접근 권한

<query expression> 구문에서 지원되며 이를 수행하려면 사용자가 <query expression>의 접근 권한을 만족해야 한다.
자세한 내용은 query expression 을 참조한다.

구문 규칙 및 파라미터

<with list>

여러 개의 <with list element>를 정의할 수 있다.

<with list element>

기술한 <query name>의 임시 결과 집합을 정의한다.
<with list element>를 Common Table Expression (CTE) 이라고 한다.
CTE는 recursive CTE와 non-recursive CTE로 구분된다.
자세한 내용은 설명 부분을 참조한다.
--# success : non recursive CTE
WITH CTE_1( c1 ) AS
    (
         SELECT i1
           FROM t1
    ),
    CTE_2( c2 ) AS
    (
         SELECT c1
           FROM CTE_1      1 선행 CTE 참조
    )
SELECT c2 FROM CTE_2;

--# error : non recursive CTE
WITH CTE_1( c1 ) AS
    (  
         SELECT c2
           FROM CTE_2      2 후행 CTE 참조
    ),
    CTE_2( c2 ) AS
    (  
         SELECT i1
           FROM t1
    )
SELECT c1 FROM CTE_1;
--# success : recursive CTE
WITH CTE_1( c1 ) AS
     (  
          SELECT i1
            FROM t1
     ),
     CTE_2( c2 ) AS
     (  
          SELECT c1
            FROM CTE_1         1 선행 CTE 참조
     ),
     CTE_3( c3 ) AS
     (  
          SELECT 1
            FROM CTE_1
          UNION ALL
          SELECT 1
           FROM CTE_2, CTE_3    2 선행 CTE 또는 self CTE 참조
     )
SELECT c3 FROM CTE_3;

--# error : recursive CTE
WITH CTE_RECURSIVE( c1 ) AS  
   (  
          SELECT i1
            FROM t1
          WHERE i1 IS NULL
          UNION ALL
          SELECT 1
            FROM CTE_RECURSIVE A, CTE_RECURSIVE B   3 self-reference CTE는 한 번만 허용
          WHERE 1 = 0
     )
SELECT c1 FROM CTE_RECURSIVE;

<query name>

<query name>은 WITH clause 내에서 중복되지 않아야 한다.

<with column list>

Recursive CTE는 <with column list>를 생략할 수 없다.

<search clause>

<cycle clause>

Cycle 발생 유무에 따라 <cycle mark column>에 <cycle mark value> 또는 <non-cycle mark value>를 저장한다.

설명

<with clause>는 임시 결과 집합을 정의하고, 그 결과 집합을 참조할 수 있다.
SELECT 구문 내에서 정의되고 참조되며 이름이 부여된 임시 결과 집합이다.
이를 Common Table Expression (CTE)라고 한다.
CTE는 recursive CTE와 non-recursive CTE로 구분된다.
WITH RECURSIVE_CTE ( c1 ) AS
     (
          SELECT 1 
            FROM dual
          UNION ALL
          SELECT c1 + 1 
            FROM RECURSIVE_CTE
           WHERE c1 < 10
     )
SELECT c1 FROM RECURSIVE_CTE;
WITH NON_RECURSIVE_CTE ( c1 ) AS 
     (
          SELECT i1
            FROM t1
          UNION ALL
          SELECT i1
            FROM t2 
     )
SELECT c1 FROM NON_RECURSIVE_CTE;
<with clause>는 SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, CREATE VIEW 구문에 기술할 수 있다.

<with list element>

기술한 <query name>의 임시 결과 집합을 정의한다.
<with list element>를 Common Table Expression (CTE)라고 한다.
CTE는 recursive CTE와 non-recursive CTE로 구분된다.
WITH CTE_RECURSIVE( c1, c2 ) AS
    (  
         SELECT i1, i2                          1 Anchor member query
           FROM t1
          WHERE i2 IS NULL
         UNION ALL
         SELECT i1, i2                          2 Recursive member query
           FROM CTE_RECURSIVE, t1      3 Self reference
          WHERE CTE_RECURSIVE.c1 = t1.i2
    )
SELECT c1, c2 FROM CTE_RECURSIVE;

<search clause>

CTE 결과 레코드의 정렬 순서를 기술한다. 
형제행들을 <ordering column list>로 정렬하고, 정렬된 레코드에 대해 형제행과 자식행의 반환 순서를 명시한다. 
<sequence column>에는 결과 레코드의 순서를 저장한다.
gSQL>
SELECT * FROM t1;

I1  I2 
--- ---
A   ---
AA  A  
AB  A  
AC  A  
AAX AA 
ABX AB 
ACX AC 

7 rows selected.

* SEARCH BREADTH FIRST BY

gSQL> 
WITH w1( w_i1, w_i2 ) AS
    ( 
         SELECT i1, i2
           FROM t1
          WHERE i1 = 'A'
         UNION ALL
         SELECT i1, i2
           FROM w1, t1
          WHERE w_i1 = i2
    ) SEARCH BREADTH FIRST BY w_i1, w_i2 SET w_seq
SELECT w_i1, w_i2, w_seq
 FROM w1;

W_I1 W_I2 W_SEQ
---- ---- -----
A    ---      1
AA   A        2
AB   A        3
AC   A        4
AAX  AA       5
ABX  AB       6
ACX  AC       7

7 rows selected.

* SEARCH DEPTH FIRST BY

gSQL> 
WITH w1( w_i1, w_i2 ) AS
    ( 
         SELECT i1, i2
           FROM t1
          WHERE i1 = 'A'
         UNION ALL
         SELECT i1, i2
           FROM w1, t1
          WHERE w_i1 = i2
    ) SEARCH DEPTH FIRST BY w_i1, w_i2 SET w_seq
SELECT w_i1, w_i2, w_seq
  FROM w1;

W_I1 W_I2 W_SEQ
---- ---- -----
A    ---      1
AA   A        2
AAX  AA       3
AB   A        4
ABX  AB       5
AC   A        6
ACX  AC       7

7 rows selected.

<cycle clause>

<cycle clause> 구문을 기술하지 않은 경우, cycle이 발생할 때 에러가 발생한다.
<cycle column list>는 cycle을 검사하는데 사용된다. 
Cycle 발생 유무에 따라 <cycle mark column>에 <cycle mark value> 또는 <non-cycle mark value>를 저장한다. 
<cycle mark value> 또는 <non-cycle mark value>에는 1 byte 문자만 기술할 수 있다. 
Cycle 발생 레코드의 <cycle mark column>에는 <cycle mark value>를 저장한다. 이 때, 더 이상의 recursion 없이 cycle이 발생한 레코드까지만 반환한다. 
Cycle이 발생하지 않은 형제행들에 대해서는 recursion이 계속 진행된다.
gSQL>
SELECT * FROM t1;

I1  I2 
--- ---
A   ---
AA  A  
AB  A  
AC  A  
AA  AA 
AAX AA 
ABX AB 
ACX AC 

8 rows selected.
gSQL> 
WITH w1( w_i1, w_i2 ) AS
     (     
          SELECT i1, i2
            FROM t1
           WHERE i1 = 'A'
          UNION ALL
          SELECT i1, i2
            FROM w1, t1
           WHERE w_i1 = i2
     )
SELECT w_i1, w_i2
  FROM w1;

ERR-42000(16511): cycle detected while executing recursive WITH query
gSQL> 
WITH w1( w_i1, w_i2 ) AS
     ( 
          SELECT i1, i2
            FROM t1
           WHERE i1 = 'A'
          UNION ALL
          SELECT i1, i2
            FROM w1, t1
           WHERE w_i1 = i2
     ) CYCLE w_i1, w_i2 SET c_cycle TO 'T' DEFAULT 'F'
SELECT w_i1, w_i2, c_cycle
  FROM w1;

W_I1 W_I2 C_CYCLE
---- ---- -------
A    ---  F      
AC   A    F      
AB   A    F      
AA   A    F      
ACX  AC   F      
ABX  AB   F      
AAX  AA   F      
AA   AA   F      
AAX  AA   F      
AA   AA   T      

10 rows selected.

사용 예

다음은 WITH 절을 사용한 SELECT 구문의 예이다.
gSQL>
WITH revenue ( supplier_no, total_revenue ) AS
      (
            SELECT
                   l_suppkey,
                   SUM(l_extendedprice * (1 - l_discount))
              FROM lineitem
             WHERE l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
             GROUP BY
                   l_suppkey
      )
select
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       ROUND( total_revenue, 2 ) as total_revenue
  from
       supplier,
       revenue
 where
       s_suppkey = supplier_no
   and total_revenue = (
                           select
                                  max(total_revenue)
                             from
                                  revenue
                       )
order by
      s_suppkey;

S_SUPPKEY S_NAME                    S_ADDRESS         S_PHONE         TOTAL_REVENUE
--------- ------------------------- ----------------- --------------- -------------
     8449 Supplier#000008449        Wp34zim9qYFbVctdW 20-469-856-8873    1772627.21

1 row selected.
gSQL> 
WITH GenerateRecord ( c1, c2 ) AS 
     (
          SELECT 1, 11
            FROM dual
          UNION ALL
          SELECT c1 + 1, c2 + 1
            FROM GenerateRecord
           WHERE c1 < 10
     )
SELECT c1, c2 FROM GenerateRecord;

C1 C2
-- --
 1 11
 2 12
 3 13
 4 14
 5 15
 6 16
 7 17
 8 18
 9 19
10 20

10 rows selected.
다음은 WITH 절 예제에 사용될 emp 테이블의 레코드 검색 결과이다.
gSQL>
SELECT * FROM emp;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Jackson Kelly  
Joe     Kelly  
Scott   Bill   
Larry   Bill   
Paul    Jackson
Bill    Bill   

8 rows selected.
다음은 SEARCH BREADTH FIRST BY를 사용한 예이다.
gSQL> 
WITH w_emp( w_name, w_mgr ) AS
     (
         SELECT name, mgr
           FROM emp
          WHERE mgr IS NULL
         UNION ALL
         SELECT name, mgr
           FROM emp, w_emp
          WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
SELECT w_name, w_mgr, w_seq
  FROM w_emp;

ERR-42000(16511): cycle detected while executing recursive WITH query
gSQL> 
WITH w_emp( w_name, w_mgr ) AS
     (
         SELECT name, mgr
           FROM emp
          WHERE mgr IS NULL
         UNION ALL
         SELECT name, mgr
           FROM emp, w_emp
          WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

W_NAME  W_MGR   W_SEQ W_CYCLE
------- ------- ----- -------
Kelly   null        1 F      
Bill    Kelly       2 F      
Jackson Kelly       3 F      
Joe     Kelly       4 F      
Bill    Bill        5 T      
Larry   Bill        6 F      
Paul    Jackson     7 F      
Scott   Bill        8 F      

8 rows selected.
다음은 SEARCH DEPTH FIRST BY를 사용한 예이다.
gSQL>
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH DEPTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

W_NAME  W_MGR   W_SEQ W_CYCLE
------- ------- ----- -------
Kelly   null        1 F      
Bill    Kelly       2 F      
Bill    Bill        3 T      
Larry   Bill        4 F      
Scott   Bill        5 F      
Jackson Kelly       6 F      
Paul    Jackson     7 F      
Joe     Kelly       8 F      

8 rows selected.
다음은 CREATE TABLE AS SELECT 구문에 with clause를 사용한 예이다.
gSQL> 
CREATE TABLE new_emp AS
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

Table created.
다음은 INSERT 구문에 with clause를 사용한 예이다.
gSQL>
INSERT INTO new_emp
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr = 'Bill'
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

6 rows created.
다음은 UPDATE 구문에 with clause를 사용한 예이다.
gSQL>
UPDATE new_emp SET w_name = NULL
 WHERE ( w_name, w_mgr ) 
       IN ( WITH w_emp( w_name, w_mgr ) AS
                (
                    SELECT name, mgr
                      FROM emp
                     WHERE mgr = 'Bill'
                    UNION ALL
                    SELECT name, mgr
                      FROM emp, w_emp
                     WHERE mgr = w_emp.w_name
                ) SEARCH BREADTH FIRST BY w_name SET w_seq
                  CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
            SELECT w_name, w_mgr
              FROM w_emp );

9 rows updated.
다음은 DELETE 구문에 with clause를 사용한 예이다.
gSQL>
DELETE FROM new_emp
WHERE ( w_mgr ) 
      IN ( WITH w_emp( w_name, w_mgr ) AS
               (
                   SELECT name, mgr
                     FROM emp
                    WHERE mgr = 'Bill'
                   UNION ALL
                   SELECT name, mgr
                     FROM emp, w_emp
                    WHERE mgr = w_emp.w_name
               ) SEARCH BREADTH FIRST BY w_name SET w_seq
                 CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
           SELECT w_mgr
             FROM w_emp );

9 rows deleted.
다음은 CREATE VIEW 구문에 with clause를 사용한 예이다.
gSQL>
CREATE VIEW v_emp AS
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

View created.

query specification

기능

<table expression> 결과로부터 파생된 table을 기술한다.

구문

<query specification> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list> <table expression>

<set quantifier> ::=
      ALL
    | DISTINCT

<table expression> ::=
      <from clause> [ <where clause> ] [ <hierarchical query clause> ] [ <group by clause> ] [ <having clause> ] [ <window clause> ]

사용 범위 및 접근 권한

<query specification> 구문을 수행하려면 다음 조건 중 하나를 만족해야 한다.

구문 규칙 및 파라미터

<hint clause>

질의 수행에 필요한 힌트를 기술한다.
자세한 내용은 SQL Hint를 참조한다.

<set quantifier>

질의 결과의 중복 제거 여부를 기술한다.
생략할 경우, ALL과 동일하게 동작한다.

<select list>

질의 결과로부터 검색할 column을 기술한다.
자세한 내용은 select list를 참조한다.

<from clause>

검색할 table들을 기술한다.
자세한 내용은 from clause를 참조한다.

<where clause>

검색 조건을 기술한다.
자세한 내용은 where clause를 참조한다.

<hierarchical query clause>

계층 모델 데이터를 계층 구조로 검색하도록 기술한다. 
자세한 내용은 hierarchical query clause를 참조한다.

<group by clause>

검색 결과에 대한 grouping을 기술한다.
자세한 내용은 group by clause를 참조한다.

<having clause>

Grouping 된 결과에 대한 조건을 기술한다.
자세한 내용은 having clause를 참조한다.

<window clause>

Window function의 수행 범위를 기술한다.
자세한 내용은 window clause를 참조한다.

설명

<hint clause>

<hint clause>는 사용자가 optimizer에게 SQL 구문 수행 방법을 직접 지시하기 위해 사용하는 comment이다.

GOLDILOCKS의 optimizer는 사용자가 기술한 <hint clause>를 우선 적용한다.
만약 적용할 수 없을 경우에는 cost 계산을 통해 최적의 실행 계획을 선택한다.
GOLDILOCKS는 기본적으로 <hint clause>에 구문상 에러가 발생하더라도 이를 무시하도록 설정되어 있다. <hint clause>에 구문상 에러가 있는지 확인하려면 HINT_ERROR property를 on으로 설정하고 질의를 수행하도록 한다

<set quantifier>

<set quantifier>는 <select list> expression들로 구성된 결과 집합에서 중복을 제거할지 여부를 설정한다.

<select list>

질의 결과로부터 검색할 column을 기술한다.
이 목록은 콤마 (,) 리스트로 구분하여 기술한다.
<from clause>에 기술한 모든 column들을 기술하고 싶은 경우에는 별표 (*)를 사용한다.

<from clause>

<from clause>는 검색할 table 또는 view들을 기술한다.

<where clause>

<where clause>는 <from clause>로부터 얻은 결과 집합 중에 원하는 결과만 가져오도록 검색 조건을 기술한다.

<hierarchical query clause>

계층 모델 데이터를 계층 구조로 검색하도록 기술한다. 
시작 조건과 하위 연결 조건을 이용하여 테이블의 레코드들을 depth-first 순서의 계층 구조로 반환한다.

<group by clause>

<group by clause>는 <where clause>를 적용한 결과 집합의 grouping 방법을 기술한다.

<group by clause>가 기술된 경우, <select list>에 올 수 있는 expression은 다음과 같다.

<having clause>

<having clause>는 grouping된 결과 집합에 대한 검색 조건을 기술한다.
일반적으로 <group by clause>와 함께 사용된다.

<window clause>

select list와 order by clause에 기술되는 window function의 수행 범위를 기술한다.

사용 예

다음은 <hint clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT /*+ INDEX_DESC(supplier, supplier_pk_index) */ s_name, s_nation FROM supplier;

S_NAME                    S_NATION
------------------------- -------------
Supplier#5                CANADA
Supplier#4                UNITED STATES
Supplier#3                GERMANY
Supplier#2                KOREA
Supplier#1                FRANCE

5 rows selected.

다음은 <set quantifier>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT ALL p_type FROM part;

P_TYPE
------
COPPER
NICKEL
STEEL
NICKEL
STEEL

5 rows selected.

gSQL> SELECT DISTINCT p_type FROM part;

P_TYPE
------
COPPER
STEEL
NICKEL

3 rows selected.

다음은 <where clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT p_name, p_brand, p_type, p_size FROM part where p_size < 10;

P_NAME P_BRAND    P_TYPE P_SIZE
------ ---------- ------ ------
Part#1 Brand#1    COPPER      7
Part#2 Brand#1    NICKEL      1

2 rows selected.

다음은 <hierarchical query clause>를 사용하여 SELECT 구문의 계층 구조 데이터를 조회하는 예이다.

gSQL>
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Larry   Bill   
Scott   Bill   
Jackson Kelly  
Paul    Jackson
Joe     Kelly  

7 rows selected.

다음은 <group by clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT ps_partkey, SUM(ps_availqty) FROM partsupp GROUP BY ps_partkey;

PS_PARTKEY SUM(PS_AVAILQTY)
---------- ----------------
         1            11401
         2             8025
         3            13864
         4            11564
         5             8744

5 rows selected.

다음은 <having clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT ps_partkey, SUM(ps_availqty) FROM partsupp GROUP BY ps_partkey having SUM(ps_availqty) > 10000;

PS_PARTKEY SUM(PS_AVAILQTY)
---------- ----------------
         1            11401
         3            13864
         4            11564

3 rows selected.

다음은 <window clause>를 사용한 SELECT 구문의 예이다.

gSQL> 
SELECT item_no,
       sales_date,
       sales,
       SUM( sales ) OVER W1 cumulative_sales, 
       AVG( sales ) OVER w1 avg_sales
  FROM store
WINDOW w1 AS ( PARTITION BY item_no
               ORDER BY sales_date
               ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );

ITEM_NO SALES_DATE SALES CUMULATIVE_SALES AVG_SALES
------- ---------- ----- ---------------- ---------
    100 2001-01-01   150              150       150
    100 2001-01-02   100              250       125
    100 2001-01-03   170              420       140
    100 2001-01-04    90              510     127.5
    100 2001-01-05   200              710       142
    235 2001-01-01    70               70        70
    235 2001-01-02   130              200       100
    235 2001-01-03   190              390       130
    235 2001-01-04   150              540       135
    235 2001-01-05    50              590       118

10 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F801

Full set function

X

T051

Row types

X

T301

Functional dependencies

X

T325

Qualified SQL parameter references

X

T053

Explicit aliases for all-fields reference

O

T285

Enhanced derived column names

O

참조

관련 내용은 query expression을 참조한다.

select list

기능

질의 결과로부터 검색할 column을 기술한다.

구문

<select list> ::=
      <asterisk>
    | <select sublist> [ { <comma> <select sublist> } ... ]

<select sublist> ::=
      <derived column>
    | <qualified asterisk>

<qualified asterisk> ::=
      <asterisked identifier chain> <period> <asterisk>

<asterisked identifier chain> ::=
    <asterisked identifier> [ { <period> <asterisked identifier> } ... ]

<derived column> ::=
    <value expression> [ <as clause> ]

<as clause> ::=
    [ AS ] <column name>

사용 범위 및 접근 권한

<select list> 구문에 column이나 subquery가 존재할 때 다음을 만족해야 한다.

구문 규칙 및 파라미터

<select list>

<asterisk>나 <select sublist>를 갖는다.

<asterisk>

<select sublist>

설명

<select list>

<select list>는 결과 집합에 포함될 column들을 기술한다.

<asterisk>

<asterisk>는 <from clause>에 있는 모든 column들을 select list로 설정한다.

<select sublist>

<select sublist>는 <derived column> 또는 <qualified asterisk>를 갖는다.

<select sublist>를 둘 이상 기술할 경우에는 반드시 콤마 (,)로 구분하여야 한다.

select list에 설정되는 이름

사용 예

다음은 <asterisk>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT * FROM supplier;

S_SUPPKEY S_NAME                    S_NATION      S_PHONE
--------- ------------------------- ------------- ---------------
        1 Supplier#1                FRANCE        27-918-335-1736
        2 Supplier#2                KOREA         15-679-861-2259
        3 Supplier#3                GERMANY       11-383-516-1199
        4 Supplier#4                UNITED STATES 25-843-787-7479
        5 Supplier#5                CANADA        21-151-690-3663

5 rows selected.

다음은 <select sublist>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT revenue.* FROM revenue;

SUPPLIER_NO TOTAL_REVENUE
----------- -------------
          1      11978.64
          2       20321.5
          3      41844.68

3 rows selected.

gSQL> SELECT supplier_no suppno, total_revenue AS TOTAL FROM revenue;

SUPPNO    TOTAL
------ --------
     1 11978.64
     2  20321.5
     3 41844.68

3 rows selected.

gSQL> SELECT 1, revenue.*, CAST( total_revenue AS NATIVE_INTEGER ) TOTAL FROM revenue;

1 SUPPLIER_NO TOTAL_REVENUE TOTAL
- ----------- ------------- -----
1           1      11978.64 11979
1           2       20321.5 20322
1           3      41844.68 41845

3 rows selected.

참조

관련 내용은 query specification 을 참조한다.

from clause

기능

하나 이상의 table들로부터 파생된 table을 기술한다.

구문

<from clause> ::=
    FROM <table reference list>

<table reference list> ::=
    <table reference> [ { , <table reference> } ... ]

<table reference> ::=
      <table factor>
    | <joined table>
    | <table reference> <pivot clause>
    | <table reference> <unpivot clause>

<table factor> ::=
      <table primary> [ <sample clause> ]

<table primary> ::=
      <table name> [ <cluster domain> ] [ [ AS ] <correlation name> ]
    | <derived table> [ <cluster domain> ] [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
    | <lateral derived table> [ <cluster domain> ] [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
    | <table function derived table> [ [ AS ] <correlation name> ]
    | <parenthesized joined table>

<derived table> ::=
    <table subquery>

<lateral derived table> ::=
    LATERAL <table subquery>

<parenthesized joined table> ::=
      <left paren> <parenthesized joined table> <right paren>
    | <left paren> <joined table> <right paren>

<derived column list> ::=
    <column name list>

<cluster domain> ::=
    @ <cluster domain name>

<cluster domain name> ::=
      GLOBAL
    | LOCAL
    | LOCAL_OFFLINE
    | <identifier>

<table function derived table> ::=
      TABLE <left paren> <table function expression> <right paren>

<table function expression> ::=
      <table function name> <left paren> [ <table function argument list> ] <right paren>

<table function argument list> ::=
      <value expression> [ <comma> ... ]

사용 범위 및 접근 권한

<table reference list>에 기술한 table 또는 view에 대한 접근 권한이 있어야 한다.

구문 규칙 및 파라미터

<table reference list>

<table reference>

<table factor>

<table primary>

SELECT col1, col2 
FROM ( SELECT i1, i2 FROM t1 ) AS a( col1, col2 ) 
WHERE col1 = 1 AND col2 = 1;
SELECT t1.col1, ft.rf2 
FROM t1, TABLE( tablefunc( t1.col1 ) );

<correlation name>

<derived column list>

<derived column list>에는 동일한 <column name>이 두 개 이상 존재할 수 없다.

<cluster domain>

<cluster domain name>

<cluster domain name>의 <identifier>에는 cluster group name이나 cluster member name이 올 수 있다.

설명

<table reference list>

<table reference list>에는 콤마 (,)를 사용하여 두 개 이상의 테이블들을 기술할 수 있다.

<table reference>

단일 table이나 view, table subquery, joined table 등이 <table reference>가 될 수 있다. Joined table을 제외한 나머지는 correlation name을 가질 수 있다.

Joined table에 대한 자세한 내용은 joined table 절을 참고한다.

<table primary>

Table이나 view, table subquery, <parenthesized joined table>이 <table primary>가 될 수 있다.

Table이나 view, table subquery는 correlation name을 가질 수 있는데, 이 때 AS는 생략할 수 있다. Correlation name이 기술된 경우, <select list>나 <where clause>와 같이 해당 table이나 view, table subquery를 참조하는 모든 경우에 correlation name을 사용해야 한다.

Table subquery는 <derived column list>를 기술할 수 있으며, correlation name과 마찬가지로 해당 table subquery의 column을 참조하는 모든 경우에 <derived column list>에 기술한 이름을 사용하여야 한다. Table subquery에 <derived column list>를 사용하려면 correlation name을 반드시 기술해야 한다.

<parenthesized joined table>은 join 연산에 참여하는 table들의 논리적 join 순서를 기술한다. 이 때 괄호로 묶은 모든 table들에 대한 join이 모두 cross join과 inner join일 경우, optimizer가 join 순서를 변경할 수 있다.

<cluster domain>

<cluster domain>이 생략된 경우 <cluster domain name>으로 GLOBAL을 사용한 것과 동일한 의미를 갖는다.
자세한 내용은 Cluster Domain을 참조한다.

<cluster domain name>

<cluster domain name>에 정의된 예약어는 다음과 같은 의미를 가진다.

<cluster domain name>에 <identifier>를 기술한 경우 해당 이름의 cluster group 또는 cluster member를 Cluster Domain으로 선정한다.

사용 예

다음은 <table name>을 이용하여 단일 table을 검색하는 SELECT 구문의 예이다.

gSQL> SELECT c_name, c_nation FROM customer;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.

다음은 <derived table>을 이용하는 SELECT 구문의 예이다.

gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.


gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer) AS CUST ("CUSTOMER_NAME", "CUSTOMER_NATION");

CUSTOMER_NAME CUSTOMER_NATION
------------- ---------------
Customer#1    KOREA          
Customer#2    CANADA         
Customer#3    KOREA          
Customer#4    GERMANY        
Customer#5    UNITED STATES  

5 rows selected.

다음은 <lateral derived table>을 이용하는 SELECT 구문의 예이다.

gSQL> SELECT r_name, n_name
  FROM region, (  SELECT n_name
                    FROM nation
                   WHERE n_regionkey = r_regionkey
               ) v_nation
 WHERE r_name = 'ASIA';

ERR-42000(16036): 'R_REGIONKEY': invalid identifier :
                   WHERE n_regionkey = r_regionkey
                                       *

gSQL> SELECT r_name, n_name
  FROM region, LATERAL (  SELECT n_name 
                            FROM nation
                           WHERE n_regionkey = r_regionkey
                       ) v_nation
 WHERE r_name = 'ASIA';
R_NAME                    N_NAME
------------------------- -------------------------
ASIA                      INDIA
ASIA                      INDONESIA
ASIA                      JAPAN
ASIA                      CHINA
ASIA                      VIETNAM

5 rows selected.

다음은 괄호를 이용한 joined table에 대한 SELECT 구문의 예이다.

gSQL> SELECT customer.c_name, o_totalprice FROM (customer INNER JOIN orders ON customer.c_custkey = orders.o_custkey);

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

다음은 콤마 (,)로 구분한 두 개의 table들을 사용하는 SELECT 구문의 예이다.

gSQL> SELECT c_name, o_totalprice FROM customer, orders;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#1     46929.18
Customer#1    193846.25
Customer#1     32151.78
Customer#1     144659.2
Customer#2    173665.47
Customer#2     46929.18
Customer#2    193846.25
Customer#2     32151.78
Customer#2     144659.2
Customer#3    173665.47
Customer#3     46929.18
Customer#3    193846.25
Customer#3     32151.78
Customer#3     144659.2
Customer#4    173665.47
Customer#4     46929.18
Customer#4    193846.25
Customer#4     32151.78
Customer#4     144659.2

C_NAME     O_TOTALPRICE
---------- ------------
Customer#5    173665.47
Customer#5     46929.18
Customer#5    193846.25
Customer#5     32151.78
Customer#5     144659.2

25 rows selected.

다음은 <cluster domain>을 이용하는 SELECT 구문의 예이다.

gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@GLOBAL);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer)@LOCAL;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@G1);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@G2N1);

C_NAME     C_NATION
---------- -------------
Customer#3 KOREA
Customer#4 GERMANY

2 rows selected.

참조

관련 내용은 subquery를 참조한다.

joined table

기능

Cartesian product, inner join, outer join 등에서 파생되는 table을 기술한다.

구문

<joined table> ::=
      <cross join>
    | <qualified join>
    | <natural join>

<cross join> ::=
    <table reference> CROSS JOIN <table factor>

<qualified join> ::=
    <table reference> [ <join type> ] JOIN <table reference> <join specification>

<natural join> ::=
    <table reference> NATURAL [ <join type> ] JOIN <table factor>

<join specification> ::=
      <join condition>
    | <named columns join>

<join condition> ::=
    ON <search condition>

<named columns join> ::=
    USING ( <join column list> )

<join type> ::=
      INNER
    | { LEFT | RIGHT | FULL } [ OUTER ]

<join column list> ::=
    <column name list>

사용 범위 및 접근 권한

joined table에 기술된 모든 table 및 view에 대한 접근 권한이 있어야 한다.

구문 규칙 및 파라미터

<cross join>

Join 조건을 명시하는 <join specification>은 <cross join> 위치에 오지 않는다.
<cross join>의 오른쪽에는 단일 테이블이나 <table subquery>, <parenthesized joined table>이 올 수 있다.

<qualified join>

<natural join>

<join specification>

설명

<cross join>

<cross join>은 왼쪽의 각 row를 오른쪽의 모든 row들과 결합한 결과를 반환한다.

T1 ( 1, 1 ), ( 2, 2 )
T2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 CROSS JOIN t2;
C1 C2 C1 C2
-- -- -- --
 1  1  2  2
 1  1  3  3
 2  2  2  2
 2  2  3  3
4 rows selected.
<cross join>에는 join 조건을 명시적으로 기술할 수 없지만, <where clause>를 통해 두 table에 대한 join 조건을 기술할 수 있으며, 이 경우 inner join과 동일하게 동작한다.
• SELECT * FROM t1 CROSS JOIN t2 WHERE t1.c1 = t2.c1;
• <=> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
T1 ( 1, 1 ), ( 2, 2 )
T2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 CROSS JOIN t2 WHERE t1.c1 = t2.c1;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
1 row selected.

<qualified join>

<qualified join>은 왼쪽의 각 row들을 오른쪽의 모든 row들과 결합한 후 join 조건을 만족하는 row들만 결과로 반환한다.

<table expression>에 <where clause>가 존재할 경우, <qualified join>의 결과 집합에 <where clause> 조건들을 적용한다.

Inner join은 <where clause>에 존재하는 조건들을 join 조건처럼 처리해도 결과가 동일하지만, outer join은 <where clause>에 존재하는 조건들을 join 조건처럼 처리하면 결과가 달라진다.

INNER JOIN
t1 ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )
t2 ( 2, 2 ), ( 3, 3 )
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
( 2,  2,    2,    2 )                        ( 2,  2,    2,    2 )
  ( 3,  3,    3,    3 )                   →   ( 3,  3,    3,    3 )
OUTER JOIN
t1 ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )
t2 ( 2, 2 ), ( 3, 3 )
gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
C1 C2   C1   C2
-- -- ---- ----
 1  1 null null
 2  2    2    2
 3  3    3    3
 4  4 null null
 5  5 null null
5 rows selected.
gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
( 1,  1, null, null )
  ( 2,  2,    2,    2 )                        ( 2,  2,    2,    2 )
  ( 3,  3,    3,    3 )                   →   ( 3,  3,    3,    3 ) 
  ( 4,  4, null, null )
  ( 5,  5, null, null )

Left outer join은 왼쪽 row에 대한 join 조건을 만족하는 오른쪽 row가 있을 경우, 해당 row들을 결합한 row를 결과로 반환한다. Join 조건을 만족하는 오른쪽 row가 존재하지 않을 경우, 왼쪽 row의 값은 그대로 유지하고 오른쪽 row의 값은 모두 NULL로 채운 row를 결과로 반환한다.

LEFT OUTER JOIN
t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1;
C1 C2   C1   C2
-- -- ---- ----
 1  1 null null
 2  2    2    2
2 rows selected.

Right outer join은 left outer join과 정확히 반대로 동작한다.

RIGHT OUTER JOIN

t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1;
  C1   C2 C1 C2
---- ---- -- --
   2    2  2  2
null null  3  3
2 rows selected.

Full outer join은 left outer join의 결과와 함께 join 조건을 만족하지 않는 모든 오른쪽 row에 대해 왼쪽 row의 값을 NULL로 채운 row들을 결과로 반환한다.

FULL OUTER JOIN

t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.c1;
  C1   C2   C1   C2
---- ---- ---- ----
   1    1 null null
   2    2    2    2
null null    3    3
3 rows selected.

<natural join>

<natural join>은 join에 참여하는 두 table에서 동일한 이름을 갖는 모든 column들을 각각 equal 조건으로 join 한다. 즉, join에 참여하는 두 table에서 동일한 이름을 갖는 모든 column들을 inner join에서 USING 구문에 기술한 것과 동일하다.

t1 ( C1 INTEGER, C2 INTEGER )
t2 ( C1 INTEGER, C3 INTEGER )

t1 ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
t2 ( 1, 100 ), ( 2, 200 ), ( 3, 300 )

gSQL> SELECT * FROM t1 NATURAL JOIN t2; 
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

gSQL> SELECT * FROM t1 INNER JOIN t2 USING ( c1 );
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

<join specification>

조인 조건을 기술한다.
<join condition>은 join 구문의 왼쪽 row와 오른쪽 row를 조인할 조건을 기술한다.
<named columns join>은 왼쪽 row와 오른쪽 row에 대해 동일한 <column name>이 존재하는 경우 이를 나열하여 조인 조건을 기술한다.
t1 ( C1 INTEGER, C2 INTEGER )
t2 ( C1 INTEGER, C3 INTEGER )

t1 ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
t2 ( 1, 100 ), ( 2, 200 ), ( 3, 300 )

• <join condition>
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
C1 C2 C1  C3
-- -- -- ---
 1 10  1 100
 2 20  2 200
 3 30  3 300
3 rows selected.

• <named columns join>
gSQL> SELECT * FROM t1 INNER JOIN t2 USING ( c1 );
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

사용 예

다음은 <cross join>을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, o_totalprice FROM customer CROSS JOIN orders;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#1     46929.18
Customer#1    193846.25
Customer#1     32151.78
Customer#1     144659.2
Customer#2    173665.47
Customer#2     46929.18
Customer#2    193846.25
Customer#2     32151.78
Customer#2     144659.2
Customer#3    173665.47
Customer#3     46929.18
Customer#3    193846.25
Customer#3     32151.78
Customer#3     144659.2
Customer#4    173665.47
Customer#4     46929.18
Customer#4    193846.25
Customer#4     32151.78
Customer#4     144659.2

C_NAME     O_TOTALPRICE
---------- ------------
Customer#5    173665.47
Customer#5     46929.18
Customer#5    193846.25
Customer#5     32151.78
Customer#5     144659.2

25 rows selected.

다음은 inner join을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, o_totalprice FROM customer INNER JOIN orders ON c_custkey = o_custkey;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

다음은 outer join을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, o_totalprice FROM customer LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_orderdate < '1996-01-01';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1         null
Customer#2         null
Customer#3     32151.78
Customer#4    193846.25
Customer#5     144659.2

5 rows selected.

gSQL> SELECT c_name, o_totalprice FROM customer RIGHT OUTER JOIN orders ON c_custkey = o_custkey AND c_nation = 'KOREA';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
null           46929.18
null          193846.25
Customer#3     32151.78
null           144659.2

5 rows selected.

gSQL> SELECT c_name, o_totalprice FROM customer FULL OUTER JOIN orders ON c_custkey = o_custkey AND c_nation = 'KOREA' AND o_orderdate < '1996-01-01';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1         null
Customer#2         null
Customer#3     32151.78
Customer#4         null
Customer#5         null
null          173665.47
null           46929.18
null          193846.25
null           144659.2

9 rows selected.

다음은 natural join을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, o_totalprice FROM (SELECT c_custkey custkey, c_name FROM customer) NATURAL JOIN (SELECT o_custkey custkey, o_totalprice FROM orders);

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F401

Extended joined table

O

F402

Named column joins for LOBs, arrays, and multisets

X

F403

Partitioned join tables

X

참조

관련 내용은 from clause를 참조한다.

pivot clause

기능

Row (value)를 column으로 변환하는 cross table을 기술한다.

구문

<pivot clause> ::=
    PIVOT
    <left paren>
       <aggregation function> [[AS] alias]
       [, <aggregation function> [[AS] alias]] ...
       <pivot for clause>
       <pivot in clause>
    <right paren>

<pivot for clause> ::=
      FOR column
    | FOR <left paren> column [, column] ... <right paren>

<pivot in clause> ::=
    IN
    <left paren>
    { <pivot value list> [[AS] alias] [, <pivot value list> [[AS] alias]] ... }
    <right paren>

<pivot value list> ::=
      expr
    | <left paren> expr [, expr] ... <right paren>

구문 규칙 및 파라미터

<pivot clause>

<aggregation function>에서 중첩된 집계 함수를 사용할 수 없다.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( SUM( c2 ) ) 
                       FOR c1
                       IN (
                             1
                           , 2
                          )
                        );

ERR-42000(16160): group function is nested too deeply : 
                 SUM( SUM( c2 ) ) 
                      *
ERROR at line 3:

<pivot for clause>에 기술된 column의 개수와 <pivot value list>의 expr의 개수는 일치하여야 한다.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR ( c1, c2 )
                       IN (
                             ( 1, 2 )
                           , ( 3 )
                          )
                        );

ERR-42000(16606): the number of elements in pivot values mismatch the pivot columns : 
                           , ( 3 )
                             *
ERROR at line 7:

<pivot for clause>

<pivot for clause> 내 column은 column_name만 명시할 수 있다.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR 1
                       IN (
                             1
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(40000): syntax error: 
                       FOR 1
                           ^
Error at line 4


gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1 + 1
                       IN (
                             1
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(40000): syntax error: 
                       FOR c1 + 1
                              ^
Error at line 4

<pivot in clause>

<pivot in clause> 내 expr은 상수만 지원한다.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1
                       IN (
                             c1
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(16608): non-constant expression is not allowed for pivot|unpivot values : 
                             c1
                             *
ERROR at line 6:


gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1
                       IN (
                             CLOCK_DATE()
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(16608): non-constant expression is not allowed for pivot|unpivot values : 
                             CLOCK_DATE()
                             *
ERROR at line 6:

각 레코드마다 값이 달라지는 expression이나 매번 평가값이 달라지는 expression은 지원하지 않는다.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1
                       IN (
                             RANDOM( 1, 2 )
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(16608): non-constant expression is not allowed for pivot|unpivot values : 
                             RANDOM( 1, 2 )
                             *
ERROR at line 6:

설명

<pivot clause>

<pivot clause> 구문은 source relation을 이용하여 새로운 cross table을 정의한다.

gSQL> SELECT T_PIVOT.*
        FROM t1
                PIVOT(                  -- new cross table
                       SUM( c2 ) 
                       FOR c1
                       IN (
                             1
                           , 2
                          )
                        ) AS T_PIVOT;

1 2
- -
1 3

1 row selected.

<pivot clause> 구문 앞에 기술된 relation은 cross table의 source relation이다.

gSQL> SELECT *
        FROM t1                  -- source relation
                PIVOT(
                       SUM( c2 ) 
                       FOR c1
                       IN (
                             1
                           , 2
                          )
                        );


1 2
- -
1 3

1 row selected.

<pivot for clause>

<pivot for clause> 구문은 pivot 대상 column을 정의한다.

gSQL> SELECT c1 FROM t1;

C1
--
 1
 2
 2

3 rows selected.



gSQL> SELECT *
        FROM t1 
                PIVOT(
                       SUM( c2 ) 
                       FOR c1      -- t1.c1
                       IN (
                             1     -- t1.c1 = 1 인 경우
                           , 2     -- t1.c1 = 2 인 경우
                          )
                        );


1 2
- -
1 3

1 row selected.

Pivot Column

Cross table의 새로운 pivot column은 <pivot in clause>에서 나열한 값과 <aggregation function>의 조합 개수만큼 구성한다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )    -- aggregation #1
                       FOR c1
                       IN (
                             1      -- row #1
                           , 2      -- row #2
                          )
                        );

1 2
- -
1 3

1 row selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )    -- aggregation #1
                     , COUNT(*)     -- aggregation #2
                       FOR c1
                       IN (
                             1      -- row #1
                           , 2      -- row #2
                          )
                        );
1 1 2 2
- - - -
1 1 3 2

1 row selected.

Source relation의 column 중에 <pivot clause> 구문 내에서 참조되지 않은 모든 column은 cross table의 column으로 구성한다.

gSQL> \DESC t1

COLUMN_NAME TYPE         IS_NULLABLE
----------- ------------ -----------
C1          NUMBER(10,0) TRUE       
C2          NUMBER(10,0) TRUE  


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( 1 ) 
                       FOR c1      -- t1.c1 참조
                       IN (
                             1
                           , 2
                          )
                        );

C2    1 2
-- ---- -
 1    1 1
 2 null 1

2 rows selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )   -- t1.c2 참조
                       FOR c1      -- t1.c1 참조
                       IN (
                             1
                           , 2
                          )
                        );

1 2
- -
1 3

1 row selected.

Pivot Column Name

pivot column name은 pivot column name prefix와 pivot column name suffix 사이에 '_'를 붙여 구성한다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix
                       FOR c1
                       IN (
                             1   AS ONE       -- prefix #1
                           , 2   AS TWO       -- prefix #2
                          )
                        );

ONE_TOTAL TWO_TOTAL
--------- ---------
        1         3

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix #1
                     , COUNT(*)  AS CNT       -- suffix #2
                       FOR c1
                       IN (
                             1   AS ONE       -- prefix #1
                           , 2   AS TWO       -- prefix #2
                          )
                        );

ONE_TOTAL ONE_CNT TWO_TOTAL TWO_CNT
--------- ------- --------- -------
        1       1         3       2

1 row selected.
Pivot Column Name Prefix

<pivot value list> 내에서 나열된 expr에 대한 display name은 pivot column name의 prefix로 사용된다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix
                       FOR c1
                       IN (
                             1                -- prefix #1
                           , '2'              -- prefix #2
                          )
                        );


1_TOTAL '2'_TOTAL
------- ---------
      1         3

1 row selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix #1
                     , COUNT(*)  AS CNT       -- suffix #2
                       FOR c1
                       IN (
                             1                -- prefix #1
                           , '2'              -- prefix #2
                          )
                        );

1_TOTAL 1_CNT '2'_TOTAL '2'_CNT
------- ----- --------- -------
      1     1         3       2

1 row selected.

<pivot value list>에서 둘 이상의 expr이 사용된 경우 각 expr의 display name을 '_'로 연결한다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       COUNT(*)
                       FOR ( c1, c2 )
                       IN (
                             ( 1, 2 )      -- prefix #1
                           , ( '3', '4' )  -- prefix #2
                          )
                        );

1_2 '3'_'4'
--- -------
  0       0

1 row selected.



gSQL> SELECT *
        FROM t1
                PIVOT(
                       COUNT(*) AS CNT     -- suffix
                       FOR ( c1, c2 )
                       IN (
                             ( 1, 2 )      -- prefix #1
                           , ( '3', '4' )  -- prefix #2
                          )
                        );

1_2_CNT '3'_'4'_CNT
------- -----------
      0           0

1 row selected.

<pivot value list>에 alias를 명시한 경우 pivot column name의 prefix는 alias로 대체한다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix #1
                           , '2'  AS TWO     -- prefix #2
                          )
                        );

ONE TWO
--- ---
  1   3

1 row selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL    -- suffix
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix #1
                           , '2'  AS TWO     -- prefix #2
                          )
                        );

ONE_TOTAL TWO_TOTAL
--------- ---------
        1         3

1 row selected.
Pivot Column Name Suffix

<aggregation function>에 집계값에 대한 alias를 부여할 수 있는데, 이 때 alias는 pivot column name의 suffix로 적용된다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL    -- suffix #1
                     , COUNT(*)  AS CNT      -- suffix #2
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix
                          )
                        );

ONE_TOTAL ONE_CNT
--------- -------
        1       1

1 row selected.

<aggregation function>에 alias를 부여하지 않은 경우 pivot column name의 suffix는 존재하지 않는다.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )             -- suffix #1 (empty)
                     , COUNT(*)  AS CNT      -- suffix #2
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix
                          )
                        );

ONE ONE_CNT
--- -------
  1       1

1 row selected.

사용 예

다음은 <pivot clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT * FROM sales;

ITEM   REGION PRICE AMOUNT
------ ------ ----- ------
apple  seoul  30000     10
apple  seoul  30000     30
kiwi   seoul  20000     15
mango  seoul  40000     20
orange seoul  25000      5
apple  busan  25000      5
mango  busan  35000     20
mango  busan  45000     10
orange busan  30000     15
apple  daegu  25000     30
kiwi   daegu  25000     10
kiwi   daegu  15000     20
apple  jeju   25000     30
apple  jeju   35000      5
kiwi   jeju   15000     10
kiwi   jeju   15000     10
mango  jeju   45000     10

17 rows selected.


--# 지역별로 판매한 과일 종류별 매출 집계는?
gSQL> SELECT *
        FROM sales PIVOT(
                          SUM( price * amount ) FOR item IN (  'apple'  PC_APPLE
                                                             , 'kiwi'   PC_KIWI
                                                             , 'mango'  PC_MANGO
                                                             , 'orange' PC_ORANGE )
                        );

REGION PC_APPLE PC_KIWI PC_MANGO PC_ORANGE
------ -------- ------- -------- ---------
seoul   1200000  300000   800000    125000
daegu    750000  550000     null      null
busan    125000    null  1150000    450000
jeju     925000  300000   450000      null

4 rows selected.

호환성

SQL 표준에서는 pivot에 대한 개념을 정의하지 않고 있다.

참조

관련 내용은 from clause를 참조한다.

unpivot clause

기능

Column을 row (value)로 변환하는 cross table을 기술한다.

구문

<unpivot clause> ::=
    UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ]
    <left paren>
       <unpivot value column list>
       <unpivot for clause>
       <unpivot in clause>
    <right paren>

<unpivot value column list> ::=
       name
     | <left paren> name [, name] ... <right paren>
       
<unpivot for clause> ::=
      FOR name
    | FOR <left paren> name [, name] ... <right paren>

<unpivot in clause> ::=
    IN
    <left paren>
        <columns of unpivot in clause> [, <columns of unpivot in clause>] ...
    <right paren>

<columns of unpivot in clause> ::=
    {
        column
      | <left paren> column [, column] ... <right paren>
    }
    [ AS
         {
             expr
         }
    ]

구문 규칙 및 파라미터

<unpivot clause>

<unpivot clause> 구문에 [ INCLUDE NULLS | EXCLUDE NULLS ]을 기술하지 않은 경우 EXCLUDE NULLS와 동일하게 동작한다.

gSQL> SELECT * FROM result;

STUDENT ENGLISH MATH SCIENCE HISTORY
------- ------- ---- ------- -------
David        70   70      80      90
Linda        90   60      80      70
Tom          90 null    null      70

3 rows selected.


gSQL> SELECT *
        FROM result
              UNPIVOT INCLUDE NULLS      -- INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

12 rows selected.


gSQL> SELECT *
        FROM result
              UNPIVOT EXCLUDE NULLS      -- EXCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.


gSQL> SELECT *
        FROM result
              UNPIVOT                     -- NULLS Treatment 생략
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.

<unpivot in clause>

<unpivot in clause> 내의 column은 column_name만 명시할 수 있다.

gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             'aaa'
                          )
                     );

ERR-42000(40000): syntax error: 
                             'aaa'
                             ^   ^
Error at line 8


gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             xxx
                          )
                     );

ERR-42000(16036): 'XXX': invalid identifier : 
                             xxx
                             *
ERROR at line 8:

<unpivot value column list> 내의 name 개수와 <columns of unpivot in clause> 내의 column 개수는 일치하여야 한다.

gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score                    -- <unpivot value column list>
                       FOR uc_subject
                       IN (
                             english               -- <columns of unpivot in clause> #1
                           , ( math, science )     -- <columns of unpivot in clause> #2
                          )
                     );

ERR-42000(16607): the number of elements in unpivot values mismatch the unpivot columns : 
                           , ( math, science )     -- <columns of unpivot in clause> #2
                                     *
ERROR at line 9:


gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       ( uc_score_1, uc_score_2 )  -- <unpivot value column list>
                       FOR uc_subject
                       IN (
                             english               -- <columns of unpivot in clause> #1
                           , ( math, science )     -- <columns of unpivot in clause> #2
                          )
                     );

ERR-42000(16607): the number of elements in unpivot values mismatch the unpivot columns : 
                             english               -- <columns of unpivot in clause> #1
                             *
ERROR at line 8:

<columns of unpivot in clause>의 AS 키워드 다음에 기술되는 expr은 unpivot 대상 relation의 column을 참조할 수 없다.

gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english AS english    -- result.column
                          )
                     );

ERR-42000(16036): 'ENGLISH': invalid identifier : 
                             english AS english
                                        *
ERROR at line 8:


gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english AS student    -- result.column
                          )
                     );

ERR-42000(16036): 'STUDENT': invalid identifier : 
                             english AS student
                                        *
ERROR at line 8:


gSQL> SELECT *
        FROM dual
       WHERE EXISTS(
                     SELECT *
                       FROM result
                             UNPIVOT
                                    (
                                      uc_score
                                      FOR uc_subject
                                      IN (
                                            english AS dummy  -- dual.dummy (outer query's column)
                                         )
                                    )
                    );

DUMMY
-----
X    

1 row selected.

설명

<unpivot clause>

<unpivot clause> 구문은 source relation을 이용하여 새로운 cross table을 정의한다.

gSQL> SELECT T_UNPIVOT.*
        FROM result
              UNPIVOT                       -- new cross table
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     ) T_UNPIVOT;

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.

<unpivot clause> 구문 앞에 기술된 relation은 cross table의 source relation이다.

gSQL> SELECT * 
        FROM result                  -- source relation
              UNPIVOT 
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.

<unpivot clause>에 INCLUDE NULLS을 명시한 경우 unpivot에 의해 생성된 모든 레코드를 결과로 반환한다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

<unpivot clause>에 EXCLUDE NULLS을 명시한 경우 unpivot에 의해 생성된 레코드 중 unpivot column이 모두 null 인 레코드를 제외한 나머지 레코드들을 결과로 반환한다.

--# unpivot column 이 모두 null 인 경우

gSQL> SELECT * 
        FROM result
              UNPIVOT EXCLUDE NULLS   -- uc_score column값이 null인 row는 제거
                     (
                       uc_score        
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.
--# unpivot column 이 일부만 null 인 경우

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, math )
                           , ( science, history )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_MATH            70         70
Linda   ENGLISH_MATH            90         60
Tom     ENGLISH_MATH            90       null
David   SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY       null         70

6 rows selected.


gSQL> SELECT * 
        FROM result
              UNPIVOT EXCLUDE NULLS   -- uc_score_1, uc_score_2 모두 null값인 row는 제거
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, math )
                           , ( science, history )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_MATH            70         70
Linda   ENGLISH_MATH            90         60
Tom     ENGLISH_MATH            90       null
David   SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY       null         70

6 rows selected.
--# unpivot column 이 모두 null 인 경우

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, history )
                           , ( math, science )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_HISTORY         70         90
Linda   ENGLISH_HISTORY         90         70
Tom     ENGLISH_HISTORY         90         70
David   MATH_SCIENCE            70         80
Linda   MATH_SCIENCE            60         80
Tom     MATH_SCIENCE          null       null

6 rows selected.


gSQL> SELECT * 
        FROM result
              UNPIVOT EXCLUDE NULLS   -- uc_score_1, uc_score_2 모두 null값인 row는 제거

                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, history )
                           , ( math, science )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_HISTORY         70         90
Linda   ENGLISH_HISTORY         90         70
Tom     ENGLISH_HISTORY         90         70
David   MATH_SCIENCE            70         80
Linda   MATH_SCIENCE            60         80

5 rows selected.

Source Relation의 Column 정보로 구성된 Unpivot Column

<unpivot for clause> 구문은 unpivot 대상 column에 대한 정보로 구성된 새로운 unpivot column을 구성한다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject    -- value is column's name in <columns of unpivot in clause>
                       IN (
                             english     -- <columns of unpivot in clause> #1
                           , math        -- <columns of unpivot in clause> #2
                           , science     -- <columns of unpivot in clause> #3
                           , history     -- <columns of unpivot in clause> #4
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

<unpivot for clause> 내의 name은 새로운 unpivot column의 column name이다.

gSQL> SELECT T_UNPIVOT.* 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR english    -- T_UNPIVOT's column
                       IN (
                             english
                          )
                     ) T_UNPIVOT;

STUDENT MATH SCIENCE HISTORY ENGLISH UC_SCORE
------- ---- ------- ------- ------- --------
David     70      80      90 ENGLISH       70
Linda     60      80      70 ENGLISH       90
Tom     null    null      70 ENGLISH       90

3 rows selected.

<unpivot for clause> 내에 기술된 name 개수만큼 새로운 unpivot column이 구성한다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR (
                              uc_subject_1  -- <unpivot for clause> unpivot column #1
                            , uc_subject_2  -- <unpivot for clause> unpivot column #2
                            )
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT_1 UC_SUBJECT_2 UC_SCORE
------- ------------ ------------ --------
David   ENGLISH      ENGLISH            70
Linda   ENGLISH      ENGLISH            90
Tom     ENGLISH      ENGLISH            90
David   MATH         MATH               70
Linda   MATH         MATH               60
Tom     MATH         MATH             null
David   SCIENCE      SCIENCE            80
Linda   SCIENCE      SCIENCE            80
Tom     SCIENCE      SCIENCE          null
David   HISTORY      HISTORY            90
Linda   HISTORY      HISTORY            70
Tom     HISTORY      HISTORY            70

12 rows selected.

<columns of unpivot in clause> 내에 기술된 column들의 display name를 '_'로 연결하여 새로운 string value를 구성한다.

구성된 string value는 <unpivot for clause>에 의해 생성된 unpivot column의 값이다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject               -- value is column's name in <columns of unpivot in clause>
                       IN (
                             ( english, math )      -- <columns of unpivot in clause> #1
                           , ( science, history )   -- <columns of unpivot in clause> #2
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_MATH            70         70
Linda   ENGLISH_MATH            90         60
Tom     ENGLISH_MATH            90       null
David   SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY       null         70

6 rows selected.

Unpivot table의 각 레코드 내 <unpivot for clause>에 의해 구성된 unpivot column들은 모두 같은 값을 가진다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR (
                             uc_subject_1               -- value is column's name in <columns of unpivot in clause>
                           , uc_subject_2               -- value is column's name in <columns of unpivot in clause>
                           )
                       IN (
                             ( english, math )      -- <columns of unpivot in clause> #1
                           , ( science, history )   -- <columns of unpivot in clause> #2
                          )
                     );

STUDENT UC_SUBJECT_1    UC_SUBJECT_2    UC_SCORE_1 UC_SCORE_2
------- --------------- --------------- ---------- ----------
David   ENGLISH_MATH    ENGLISH_MATH            70         70
Linda   ENGLISH_MATH    ENGLISH_MATH            90         60
Tom     ENGLISH_MATH    ENGLISH_MATH            90       null
David   SCIENCE_HISTORY SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY SCIENCE_HISTORY       null         70

6 rows selected.

Source Relation의 Column Value로 구성된 Unpivot Column

<unpivot value column list> 구문은 unpivot 대상 column의 값을 가지는 새로운 unpivot column을 정의한다.

<columns of unpivot in clause>의 column들의 값은 <unpivot value column list>에 의해 생성된 unpivot column의 값으로 설정된다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score        -- value is column's value in <columns of unpivot in clause>
                       FOR uc_subject
                       IN (
                             english   -- <columns of unpivot in clause> #1
                           , math      -- <columns of unpivot in clause> #2
                           , science   -- <columns of unpivot in clause> #3
                           , history   -- <columns of unpivot in clause> #4
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

<unpivot in clause> 구문은 unpivot 대상 column을 정의한다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english   -- english is result.english
                           , math      -- math is result.math
                           , science   -- science is result.science
                           , history   -- history is result.history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

사용 예

다음은 <unpivot clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT * FROM result;

STUDENT ENGLISH MATH SCIENCE HISTORY
------- ------- ---- ------- -------
David        70   70      80      90
James        80   90      60      60
Mary         70   90      50      80
Linda        90   60      80      70
Tom          90 null    null      70
null       null null    null    null

6 rows selected.


--# 전체 학생에 대한 과목별 점수 조회
gSQL> SELECT *
  FROM result
             UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
James   ENGLISH          80
Mary    ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
James   MATH             90
Mary    MATH             90
Linda   MATH             60
David   SCIENCE          80
James   SCIENCE          60
Mary    SCIENCE          50
Linda   SCIENCE          80
David   HISTORY          90
James   HISTORY          60
Mary    HISTORY          80
Linda   HISTORY          70
Tom     HISTORY          70

18 rows selected.

호환성

SQL 표준에서는 unpivot에 대한 개념을 정의하지 않고 있다.

참조

관련 내용은 from clause를 참조한다.

sample clause

기능

<table primary>에 무작위 sampling을 적용한다.

구문

<sample clause> ::=
      TABLESAMPLE <left paren> percent_value [ PERCENT ROWS | PERCENT PAGES ] <right paren> [ <repeatable clause> ]

<repeatable clause> ::=
    REPEATABLE <left paren> seed_value <right paren>

구문 규칙 및 파라미터

<sample clause>

percent_value는 0보다 크고 100 이하인 실수만 허용한다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 0 PERCENT ROWS );

ERR-42000(16664): table sampling rate must be greater than 0 and less than or equal to 100 : 
SELECT COUNT(*) FROM t1 TABLESAMPLE( 0 PERCENT ROWS )
                                     *
ERROR at line 1:


gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 200 PERCENT ROWS );

ERR-42000(16664): table sampling rate must be greater than 0 and less than or equal to 100 : 
SELECT COUNT(*) FROM t1 TABLESAMPLE( 200 PERCENT ROWS )
                                     *
ERROR at line 1:


gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 100 PERCENT ROWS );

COUNT(*)
--------
 1000000

1 row selected.


gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
   99854

1 row selected.

PERCENT ROWS나 PERCENT PAGES를 명시하지 않은 경우, PERCENT ROWS를 적용한다.

gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 );

COUNT(*)
--------
   99791

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

percent_value는 소수점 이하 두 자리까지의 값을 table sampling rate로 사용한다.

gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 12.345678 );

COUNT(*)
--------
  123804

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 12.34 % )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

<repeatable clause>

seed_value는 native integer type이다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 10000000000 );

ERR-22003(12075): data is outside the range of the data type to which the number is being converted


gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 100000000 );

COUNT(*)
--------
   99967

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % ) REPEATABLE( 100000000 )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan


gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( -100000000 );

COUNT(*)
--------
  100109

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % ) REPEATABLE( -100000000 )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

설명

<sample clause>은 SQL에서 base table 이나 global temporary table의 일부 샘플 데이터만 추출하여 질의를 수행할 수 있게 해주는 기능이다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100415

1 row selected.


gSQL> SELECT COUNT(*) FROM ( SELECT * FROM t1 ) TABLESAMPLE( 10 PERCENT ROWS );

ERR-42000(16663): table sampling can only be performed on a single base table or temporary table : 
SELECT COUNT(*) FROM ( SELECT * FROM t1 ) TABLESAMPLE( 10 PERCENT ROWS )
                       *
ERROR at line 1:


gSQL> SELECT COUNT(*) FROM v1 TABLESAMPLE( 10 PERCENT ROWS );

ERR-42000(16663): table sampling can only be performed on a single base table or temporary table : 
SELECT COUNT(*) FROM v1 TABLESAMPLE( 10 PERCENT ROWS )
                     *
ERROR at line 1:

PERCENT ROWS를 지정한 경우, percent_value의 확률로 row 단위 샘플링이 수행되며, PERCENT PAGES를 지정한 경우에는 percent_value의 확률로 page 단위 샘플링이 수행된다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100415

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100173

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 20 PERCENT ROWS );

COUNT(*)
--------
  200285

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT PAGES );

COUNT(*)
--------
   96768

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 20 PERCENT PAGES );

COUNT(*)
--------
  202368

1 row selected.

<sample clause>은 정확한 결과 row 수를 보장을 하지 않으며, 인덱스를 무시하고 테이블에 직접 접근하여 평가를 수행한다.

gSQL> \EXPLAIN PLAN SELECT /*+ INDEX( t1 ) */ COUNT(*) FROM t1;

COUNT(*)
--------
 1000000

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      INDEX ACCESS ("T1", "IDX_T1")                           | (   1000000)          1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  READ INDEX COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan


gSQL> \EXPLAIN PLAN SELECT /*+ INDEX( t1 ) */ COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100081

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

<sample clause>는 무작위 샘플링을 수행하므로, REPEATABLE 구문 없이 실행할 경우 매번 다른 결과가 나올 수 있다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100167

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
   99802

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
   99732

1 row selected.

<repeatable clause>의 seed_value가 다를 경우, 서로 다른 결과가 나올 수 있다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 1 );

COUNT(*)
--------
   99756

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 1 );

COUNT(*)
--------
   99756

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 2 );

COUNT(*)
--------
  100361

1 row selected.

사용 예

다음은 <sample clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) WHERE c1 >= c2;

COUNT(*)
--------
  100290

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 A TABLESAMPLE( 1 PERCENT ROWS ), t1 B TABLESAMPLE( 2 PERCENT ROWS ) WHERE A.c1 = B.c1;

COUNT(*)
--------
 2012379

1 row selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T613

Sampling

O

참조

관련 내용은 query specification 을 참조한다.

where clause

기능

<from clause> 결과에 <search condition>을 적용한다.

구문

<where clause> ::=
    WHERE <search condition>

구문 규칙 및 파라미터

<where clause>

WHERE 키워드 뒤에는 boolean type을 반환하는 <search condition>이 와야 한다.

설명

<where clause>에 대한 자세한 내용은 Conditions을 참고한다.

사용 예

다음은 <where clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation = 'KOREA';

S_NAME                    S_NATION
------------------------- --------
Supplier#2                KOREA

1 row selected.

gSQL> SELECT s_name, ps_availqty, ps_supplycost FROM supplier, partsupp WHERE s_nation = 'KOREA' AND s_suppkey = ps_suppkey;

S_NAME                    PS_AVAILQTY PS_SUPPLYCOST
------------------------- ----------- -------------
Supplier#2                       8076        993.49
Supplier#2                       4069        357.84

2 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F441

Extended set function support

O

참조

관련 내용은 query specification 을 참조한다.

hierarchical query clause

기능

계층 모델 데이터를 계층 구조로 검색하도록 기술한다. 
시작 조건과 하위 연결 조건을 이용하여 테이블의 레코드들을 depth-first 순서의 계층 구조로 반환한다.

구문

<hierarchical query clause> ::= 
    <start with connect by clause> [ <order siblings by clause> ]

<start with connect by clause> ::= 
    <start with clause> <connect by clause>
    | <connect by clause> <start with clause>
    | <connect by clause>

<start with clause> ::=
    START WITH <start_with_condition>

<connect by clause> ::=
    CONNECT BY [NOCYCLE] <connect_by_condition>

<order siblings by clause> ::=
    ORDER SIBLINGS BY <ordering element> [ { <comma> <ordering element> }... ]

<ordering element> ::=
    <value expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]

<hierarchy expression> ::=
    LEVEL
    | CONNECT_BY_ISCYCLE
    | CONNECT_BY_ISLEAF
    | PRIOR <value expression>
    | CONNECT_BY_ROOT <value expression>
    | SYS_CONNECT_BY_PATH <left paren> <value expression> <comma> <character string literal> <right paren>

사용 범위 및 접근 권한

<query specification> 구문에서 지원되며, 이를 수행하기 위해 사용자는 <query specification>의 접근 권한을 만족해야 한다.
자세한 내용은 query specification 을 참조한다.

구문 규칙 및 파라미터

<hierarchical query clause>

<connect by clause>는 반드시 기술되어야  한다. 
<start with clause>나 <order siblings by clause>는 필요한 경우 기술한다.

<start with clause>

데이터 계층에서 root (최상위) 레코드의 조건을 기술한다.
기술되지 않았을 경우는 from 절의 모든 레코드가 root 레코드 대상이 된다.
SELECT 구문 내에서 한 번만 기술할 수 있다.

<connect by clause>

상위 (parent) 레코드와 하위 (child) 레코드의 관계를 기술한다.
상위 레코드의 column 값을 나타내는 PRIOR operator를 이용하여 상위 레코드와 하위 레코드의 관계를 표현한다. 
PRIOR operator로 상위 레코드와 하위 레코드의 연결 조건을 기술하지 않을 경우, 무한루프가 발생할 수 있다.
SELECT 구문 내에서 한 번만 기술할 수 있다.

<order siblings by clause>

같은 상위 (parent) 레코드를 가지는 형제 (sibling) 레코드들 간의 fetch 순서를 지정한다.

<hierarchy expression>

<hierarchy expression>의 결과 타입

Expression

Result DataType

LEVEL

NATIVE_BIGINT

CONNECT_BY_ISCYCLE

NATIVE_BIGINT

CONNECT_BY_ISLEAF

NATIVE_BIGINT

PRIOR expr

expr의 DataType

CONNECT_BY_ROOT expr

expr의 DataType

SYS_CONNECT_BY_PATH( expr, literal )

VARCHAR(4000 characters)

<hierarchy expression>을 기술할 수 있는 구문은 다음과 같다.

Expression\clause

FROM

START WITH

CONNECT BY

ORDER SIBLINGS BY

WHERE/

GROUP BY/

HAVING

ORDER BY/

SELECT TARGET

LEVEL

X

O

O

X

O

O

CONNECT_BY_ISCYCLE

X

X

X

X

O

O

CONNECT_BY_ISLEAF

X

X

X

X

O

O

PRIOR

X

X

O

X

O

O

CONNECT_BY_ROOT

X

X

X

X

O

O

SYS_CONNECT_BY_PATH

X

X

X

X

O

O

<hierarchy expression>의 인자로 <hierarchy expression>을 사용할 수 있는지 여부는 다음과 같다.

Expression\Argument(expr)

LEVEL

CONNECT_BY_ISCYCLE

CONNECT_BY_ISLEAF

PRIOR

CONNECT_BY_ROOT

SYS_CONNECT_BY_PARTH

PRIOR expr

X

X

X

X

X

X

CONNECT_BY_ROOT expr

X

X

X

X

X

X

SYS_CONNECT_BY_PARTH(expr,literal)

O

O

O

O

O

O

설명

<hierarchical query clause>는 계층 모델 데이터를 계층 구조로 검색하는 구문이다. 
시작 조건과 하위 연결 조건을 이용하여 테이블의 레코드들을 depth-first 순서의 계층 구조로 반환한다.
SELECT에 <hierarchical query clause>를 기술한 경우 다음과 같은 순서로 처리된다.

  1. FROM 절의 ON 조건

  2. START WITH

  3. CONNECT BY

  4. WHERE

SELECT *
 FROM r_region
WHERE r_population > 10000000             3 WHERE 절의 조건
START WITH r_name = 'EARTH'               1 START WITH
CONNECT BY r_domain = PRIOR r_name        2 CONNECT BY
SELECT *
  FROM r_region INNER JOIN s_region 
       ON r_id = s_id                      1 ON 절의 join 조건
 WHERE r_population > 10000000             4 WHERE 절의 조건
START WITH r_name = 'EARTH'                2 START WITH
CONNECT BY r_domain = PRIOR r_name         3 CONNECT BY
SELECT *
  FROM r_region, s_region
 WHERE r_population > 10000000             3 WHERE 절의 조건
   AND r_id = s_id                         3 WHERE 절의 join 조건
START WITH r_name = 'EARTH'                1 START WITH
CONNECT BY r_domain = PRIOR r_name         2 CONNECT BY
SELECT *
  FROM r_region INNER JOIN s_region
       ON r_name = s_name                   1 ON 절의 join 조건
 WHERE r_population > 10000000              4 WHERE 절의 조건
   AND r_id = s_id                          4 WHERE 절의 join 조건
START WITH r_name = 'EARTH'                 2 START WITH
CONNECT BY r_domain = PRIOR r_name          3 CONNECT BY

<order siblings by clause>

<hierarchical query clause> 내에서 같은 상위 (parent) 레코드를 가지는 형제 (sibling) 레코드들간의 fetch 순서를 지정한다.
<order siblings by clause>는 <order by clause>와는 별개의 구문이다.
gSQL> 
SELECT * FROM t1;

I1  I2
--- ----
A   null
AA  A   
AB  A   
fAA AA  
eAA AA  
bAA AA  
dAB AB  
cAB AB  
aAB AB  

9 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'A' 
CONNECT BY i2 = PRIOR i1
ORDER SIBLINGS BY i1;

LEVEL I1  I2
----- --- ----
    1 A   null
    2 AA  A   
    3 bAA AA  
    3 eAA AA  
    3 fAA AA  
    2 AB  A   
    3 aAB AB  
    3 cAB AB  
    3 dAB AB  

9 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'A'
CONNECT BY i2 = PRIOR i1
ORDER SIBLINGS BY i1
ORDER BY LEVEL;

LEVEL I1  I2  
----- --- ----
    1 A   null
    2 AA  A   
    2 AB  A   
    3 bAA AA  
    3 eAA AA  
    3 fAA AA  
    3 aAB AB  
    3 cAB AB  
    3 dAB AB  

9 rows selected.

<hierarchy expression>

hierarchy expression의 기능은 다음과 같다.

gSQL>
SELECT i1, i2
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = PRIOR i1;

I1    I2  
----- ----
X     null
XA    X   
XXA   XA  
XXXA  XXA 
XXXXA XXXA

5 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

LEVEL I1    I2  
----- ----- ----
   1 X     null
   2 XA    X   
   3 XXA   XA  
   4 XXXA  XXA 
   5 XXXXA XXXA

5 rows selected.
gSQL> 
SELECT * FROM t1;

I1 I2  
-- ----
A  null
AA A   
AB A   
AC A   
AA AA  
AB AA  

6 rows selected.

gSQL> 
SELECT i1, i2, CONNECT_BY_ISCYCLE 
  FROM t1
START WITH i1 = 'A'
CONNECT BY NOCYCLE i2 = prior i1;

I1 I2   CONNECT_BY_ISCYCLE
-- ---- ------------------
A  null                  0
AA A                     1
AB AA                    0
AB A                     0
AC A                     0

5 rows selected.
gSQL> 
SELECT i1, i2, CONNECT_BY_ISLEAF
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1; 

I1    I2   CONNECT_BY_ISLEAF
----- ---- -----------------
X     null                 0
XA    X                    0
XXA   XA                   0
XXXA  XXA                  0
XXXXA XXXA                 1

5 rows selected.
gSQL> 
SELECT i1, i2, CONNECT_BY_ROOT i1
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

I1    I2   CONNECT_BY_ROOT I1
----- ---- ------------------
X     null X                 
XA    X    X                 
XXA   XA   X                 
XXXA  XXA  X                 
XXXXA XXXA X                 

5 rows selected.
gSQL> 
SELECT i1, i2, SYS_CONNECT_BY_PATH( i1, '/' )
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

I1    I2   SYS_CONNECT_BY_PATH( I1, '/' )
----- ---- ------------------------------
X     null /X                            
XA    X    /X/XA                         
XXA   XA   /X/XA/XXA                     
XXXA  XXA  /X/XA/XXA/XXXA                
XXXXA XXXA /X/XA/XXA/XXXA/XXXXA          

5 rows selected.

사용 예

다음은 hierarchical query clause 예제에 사용될 emp 테이블의 레코드 검색 결과이다.

gSQL> 
SELECT * FROM emp;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Jackson Kelly  
Joe     Kelly  
Scott   Bill   
Larry   Bill   
Paul    Jackson
Bill    Bill   

8 rows selected.

다음은 cycle이 발생하는 예이다.

gSQL> 
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR name
ORDER SIBLINGS BY name;

ERR-42000(16511): cycle detected while executing recursive WITH query

다음은 CONNECT BY NOCYCLE 구문으로 질의를 수행하는 예이다.

gSQL> 
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Larry   Bill   
Scott   Bill   
Jackson Kelly  
Paul    Jackson
Joe     Kelly  

7 rows selected.

다음은 hierarchy expression을 이용해 계층 구조 데이터의 정보를 조회하는 예이다.

gSQL> 
SELECT name, 
       mgr, 
       PRIOR name AS prior_mgr,
       LEVEL,
       CONNECT_BY_ISCYCLE AS iscycle,
       CONNECT_BY_ISLEAF AS isleaf,
       CONNECT_BY_ROOT mgr AS root_mgr,
       SYS_CONNECT_BY_PATH( mgr, '/' ) AS path
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR     PRIOR_MGR LEVEL ISCYCLE ISLEAF ROOT_MGR PATH           
------- ------- --------- ----- ------- ------ -------- ---------------
Kelly   null    null          1       0      0 null     /              
Bill    Kelly   Kelly         2       1      0 null     //Kelly        
Larry   Bill    Bill          3       0      1 null     //Kelly/Bill   
Scott   Bill    Bill          3       0      1 null     //Kelly/Bill   
Jackson Kelly   Kelly         2       0      0 null     //Kelly        
Paul    Jackson Jackson       3       0      1 null     //Kelly/Jackson
Joe     Kelly   Kelly         2       0      1 null     //Kelly        

7 rows selected.

group by clause

기능

이전 구문들이 처리한 결과에 <group by clause>를 적용한 grouped table을 기술한다.

구문

<group by clause> ::=
    GROUP BY [<set quantifier>] <grouping element list>

<set quantifier> ::=
    ALL
    | DISTINCT

<grouping element list> ::=
    <grouping element> [ { , <grouping element> } ... ]

<grouping element> ::=
      <ordinary grouping set>
    | <rollup list>
    | <cube list>
    | <grouping sets specification>
    | <empty grouping set>

<ordinary grouping set> ::=
      <grouping column reference>
    | <left paren> <grouping column reference list> <right paren>

<grouping column reference> ::=
    <column reference>
    | <select list alias>
    | <value expression>

<grouping column reference list> ::=
    <grouping column reference> [ { , <grouping column reference> }... ]

<empty grouping set> ::=
    <left paren> <right paren>

<rollup list> ::=
    ROLLUP <left paren> <ordinary grouping set list> <right paren>

<ordinary grouping set list> ::=
    <ordinary grouping set> [ { , <ordinary grouping set> }... ]

<cube list> ::=
    CUBE <left paren> <ordinary grouping set list> <right paren>

<grouping sets specification> ::=
    GROUPING SETS <left paren> <grouping set list> <right paren>

<grouping set list> ::=
    <grouping set> [ { , <grouping set> }... ]

<grouping set> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grouping sets specification>
  | <empty grouping set>

사용 범위 및 접근 권한

<group by clause>를 수행하기 위해 별도의 접근 권한이 필요한 것은 아니다.

구문 규칙 및 파라미터

<ordinary grouping set>

하나 이상의 <grouping column reference>로 구성한다.
LONG type (LONG VARCHAR, LONG VARBINARY)은 지원하지 않는다.

• SELECT c1, sum(c2) FROM t1 GROUP BY c1;
• SELECT sum(c1) FROM t1 GROUP BY NULL;

<empty grouping set>

괄호만 사용하여 기술할 수 있다.

• SELECT sum(c1) FROM t1 GROUP BY ();

설명

<set quantifier>

ALL 또는 DISTINCT 이며, <set quantifier>가 지정되지 않으면 ALL을 의미한다. 
DISTINCT가 기술된 경우, 중복 정의된 그룹을 제거한다.

<grouping element list>

<group by clause>에 기술된 <grouping element list>를 하나의 GROUPING SET으로 만드는 grouping을 수행한다. GROUPING SET에 존재하는 모든 <grouping element>들과 값이 일치하면 동일한 group으로 처리한다.

<ordinary grouping set>

<ordinary grouping set>에는 <grouping column reference> 또는 <left paren><grouping column reference list><right paren>이 올 수 있다.

<group column reference list>는 <grouping column reference>의 list 이며, <grouping column reference>에는 <column reference> 또는 <value expression>이 올 수 있다.

<rollup list>

ROLLUP 구문은 <ordinary grouping set list>와 함께 사용된다. <ordinary grouping set list>에 나열된   <ordinary grouping set> 개수가 n개일 경우, <ordinary grouping set>를 n개로 grouping 한 후, <ordinary grouping set>를 n-1개로 grouping 하고 그 다음은  n-2개로 grouping 하는 식으로 계속 grouping 하다가 마지막에는 <empty grouping sets>로 grouping 한 결과를 반환한다. 
따라서 총 ( n + 1 )개의 그룹이 생성된다. 
SUM과 함께 사용하는 경우, ROLLUP은 가장 세부적인 수준의 부분 합부터 총합까지 구할 수 있게 된다.

<cube list>

CUBE 구문은 <ordinary grouping set list>와 함께 사용된다. <ordinary grouping set>을 모든 조합으로 grouping 한다. <ordinary grouping set> 개수가 n개 일 경우 총 2n 개의 그룹이 생성된다.

<grouping sets specification>

GROUPING SETS는 필요한 그룹의 조합을 모두 명시할 수 있다. ROLLUP 이나 CUBE는 각 구문에 맞게 그룹의 조합을 구한다. 그러나 GROUPING SETS는 필요한 그룹의 조합만 선택할 수 있다.

<empty grouping set>

<empty grouping set>의 모든 레코드는 단일 group으로 구성된다.
• SELECT sum(c1), sum(c2) FROM t1 GROUP BY ();

사용 예

다음은 GROUP BY를 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_nation, COUNT(c_name) FROM customer GROUP BY c_nation;

C_NATION      COUNT(C_NAME)
------------- -------------
UNITED STATES             1
CANADA                    1
KOREA                     2
GERMANY                   1

4 rows selected.

gSQL> SELECT COUNT(c_name) FROM customer GROUP BY NULL;

COUNT(C_NAME)
-------------
            5

1 row selected.

gSQL> SELECT COUNT(c_name) FROM customer GROUP BY ();

COUNT(C_NAME)
-------------
            5

1 row selected.

다음은 grouping key로 <select list alias>를 사용한 예이다.

gSQL> SELECT o_orderdate || ' : ' || o_custkey AS date_cust, COUNT(*) 
        FROM orders 
       GROUP BY date_cust 
      HAVING COUNT(*) > 2;


DATE_CUST           COUNT(*)
------------------- --------
1995-06-22 : 114637        3
1994-09-22 : 61855         3
1994-10-26 : 90070         3
1992-09-10 : 108091        3
1992-12-24 : 131530        3
1995-05-12 : 64672         3
1997-09-20 : 8098          3
1997-04-29 : 22942         3
1992-05-31 : 130456        3
1993-04-15 : 10405         3
1992-02-21 : 11939         3
1996-03-31 : 98120         3

12 rows selected.

다음은 GROUP BY ROLLUP을 사용한 SELECT 구문의 예이다.

SELECT 
       calendar_year as year 
     , calendar_quarter_desc as quarter
     , calendar_month_desc as month
     , SUM(amount_sold) as sum 
  FROM sales, times
 WHERE sales.time_id=times.time_id 
   AND times.calendar_year = 2001
   AND sales.cust_id < 1000 AND sales.prod_id > 142 AND sales.channel_id > 2
 GROUP BY ROLLUP(calendar_year, calendar_quarter_desc, calendar_month_desc)
 ORDER BY 1, 2, 3;

YEAR QUARTER MONTH        SUM
---- ------- ------- --------
2001 2001-01 2001-01  1631.26
2001 2001-01 2001-02   922.03
2001 2001-01 2001-03  1625.59
2001 2001-01 null     4178.88
2001 2001-02 2001-04  2087.83
2001 2001-02 2001-05  1168.99
2001 2001-02 2001-06  1778.76
2001 2001-02 null     5035.58
2001 2001-03 2001-07  1604.74
2001 2001-03 2001-08  1841.42
2001 2001-03 2001-09  1953.56
2001 2001-03 null     5399.72
2001 2001-04 2001-10  2117.61
2001 2001-04 2001-11  1862.95
2001 2001-04 2001-12  1880.53
2001 2001-04 null     5861.09
2001 null    null    20475.27
null null    null    20475.27

다음은 GROUP BY CUBE를 사용한 SELECT 구문의 예이다.

\EXPLAIN PLAN
SELECT 
       channels.channel_desc as channel 
     , countries.country_iso_code as country
     , SUM(amount_sold) as sold_sum
  FROM sales, customers, times, channels, countries
 WHERE sales.time_id = times.time_id 
   AND sales.cust_id = customers.cust_id 
   AND sales.channel_id = channels.channel_id 
   AND customers.country_id = countries.country_id
   AND channels.channel_desc IN ('Direct Sales', 'Internet')
   AND times.calendar_month_desc ='2001-09'
   AND countries.country_iso_code IN ('US','FR')
   AND sales.cust_id < 1000 AND sales.prod_id > 142 AND sales.channel_id > 2
   AND customers.cust_id < 1000
 GROUP BY CUBE(channels.channel_desc, countries.country_iso_code)
 ORDER BY 1,2;

CHANNEL      COUNTRY SOLD_SUM
------------ ------- --------
Direct Sales FR         59.91
Direct Sales US        662.47
Direct Sales null      722.38
Internet     FR         29.62
Internet     US        382.56
Internet     null      412.18
null         FR         89.53
null         US       1045.03
null         null     1134.56

9 rows selected.

다음은 GROUP BY GROUPING SETS를 사용한 SELECT 구문의 예이다.

\EXPLAIN PLAN
SELECT 
       calendar_year as year 
     , calendar_quarter_desc as quarter
     , calendar_month_desc as month
     , SUM(amount_sold) as sum 
  FROM sales, times
 WHERE sales.time_id=times.time_id 
   AND times.calendar_year = 2001
   AND sales.cust_id < 1000 AND sales.prod_id > 142 AND sales.channel_id > 2
 GROUP BY GROUPING SETS( (calendar_year, calendar_quarter_desc, calendar_month_desc),
                         (calendar_year),
                                ()
                              )
 ORDER BY 1, 2, 3;  

YEAR QUARTER MONTH        SUM
---- ------- ------- --------
2001 2001-01 2001-01  1631.26
2001 2001-01 2001-02   922.03
2001 2001-01 2001-03  1625.59
2001 2001-02 2001-04  2087.83
2001 2001-02 2001-05  1168.99
2001 2001-02 2001-06  1778.76
2001 2001-03 2001-07  1604.74
2001 2001-03 2001-08  1841.42
2001 2001-03 2001-09  1953.56
2001 2001-04 2001-10  2117.61
2001 2001-04 2001-11  1862.95
2001 2001-04 2001-12  1880.53
2001 null    null    20475.27
null null    null    20475.27

14 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T431

Extended grouping capabilities

O

T432

Nested and concatenated GROUPING SETS

O

T434

GROUP BY DISTINCT

O

참조

관련 내용은 다음을 참조한다.

having clause

기능

<search condition>을 만족하지 않는 group을 제거한 grouped table을 기술한다.

구문

<having clause> ::=
    HAVING <search condition>

사용 범위 및 접근 권한

<having clause>를 수행하기 위해 별도의 접근 권한이 필요한 것은 아니다.

구문 규칙 및 파라미터

<having clause>

설명

<having clause>

<having clause>는 grouping 된 데이터들에 대한 검색 조건을 기술한다.

일반적으로 <group by clause>와 함께 사용되며, <group by clause> 없이 <having clause>를 사용할 경우에는 <empty grouping set>이 있는 것으로 간주한다.

<having clause>에는 <group by clause>에 기술된 <column reference>를 기술할 수 있다.
<group by clause>에 기술되지 않은 column은 집계 함수를 사용하여 기술할 수 있다.

사용 예

다음은 <having clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_nation, COUNT(c_name) FROM customer GROUP BY c_nation HAVING COUNT(c_name) > 1;

C_NATION COUNT(C_NAME)
-------- -------------
KOREA                2

1 row selected.

gSQL> SELECT COUNT(c_name) FROM customer HAVING COUNT(c_name) > 1;

COUNT(C_NAME)
-------------
            5

1 row selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T301

Functional dependencies

O

참조

관련 내용은 다음을 참조한다.

window clause

기능

select list와 order by clause에 기술된 window function의 수행 범위를 정의한다.

구문

<window clause> ::=
     WINDOW <window definition list>

<window definition list> ::=
     <window definition> [ { <comma> <window definition> }... ]

<window definition> ::=
     <new window name> AS <window specification>

<new window name> ::=
     <window name>

<window specification> ::=
     <left paren> <window specification details> <right paren>

<window specification details> ::=
     [ <existing window name> ]
          [ <window partition clause> ]
          [ <window order clause> ]
          [ <window frame clause> ]

<existing window name> ::=
     <window name>

<window partition clause> ::=
     PARTITION BY <window partition column reference list>

<window partition column reference list> ::=
     <window partition column reference>
          [ { <comma> <window partition column reference> }... ]

<window partition column reference> ::=
     <column reference>

<window order clause> ::=
     ORDER BY <sort specification list>

<sort specification list> ::=
     <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
     <sort key> [ <ordering specification> ] [ <null ordering> ]

<sort key> ::=
     <value expression>

<ordering specification> ::=
       ASC
     | DESC

<null ordering> ::=
      NULLS FIRST
    | NULLS LAST

<window frame clause> ::=
     <window frame units> <window frame extent>
          [ <window frame exclusion> ]

<window frame units> ::=
       ROWS
     | RANGE
     | GROUPS

<window frame extent> ::=
       <window frame start>
     | <window frame between>

<window frame start> ::=
       UNBOUNDED PRECEDING
     | <window frame preceding>
     | CURRENT ROW

<window frame preceding> ::=
     <unsigned value specification> PRECEDING

<window frame between> ::=
     BETWEEN <window frame bound 1> AND <window frame bound 2>

<window frame bound 1> ::=
     <window frame bound>

<window frame bound 2> ::=
     <window frame bound>

<window frame bound> ::=
       <window frame start>
     | UNBOUNDED FOLLOWING
     | <window frame following>

<window frame following> ::=
     <unsigned value specification> FOLLOWING

<window frame exclusion> ::=
       EXCLUDE CURRENT ROW
     | EXCLUDE GROUP
     | EXCLUDE TIES
     | EXCLUDE NO OTHERS

사용 범위 및 접근 권한

window clause에 column이 존재하는 경우 column에 대한 접근 권한이 있어야 한다.

구문 규칙 및 파라미터

<window clause>

window clause에는 window function을 기술할 수 없다.

<window definition list>

여러 개의 <window definition>을 정의할 수 있다.

<window definition>

<new window name>으로 window function의 수행 범위를 기술한다.
<new window name>은 <window clause> 내에서 중복되지 않아야 한다. 
<new window name>은 window function의 over 절에서 참조될 수 있다.
SELECT SUM(i2) OVER w1
  FROM t1
WINDOW w1 AS ( PARTITION BY i1 ORDER BY i2 );

<window specification>

Window function의 수행 범위를 정의한다.

<existing window name>을 참조하여, 기존에 정의된 정보에 더하여 <window specification>을 재정의 할 수 있다.
<existing window name>은 <window definition list>에 이미 정의된 <new window name>만 참조할 수 있다.
• window 절에서 참조

SELECT SUM(i2) OVER w2
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 ),
       w2 AS ( w1 ROWS BETWEEN UNBOUNDED PRECEDING  <---
                           AND CURRENT ROW );

• window function의 over 절에서 참조

SELECT SUM(i2) OVER ( w1 ROWS BETWEEN UNBOUNDED PRECEDING  <---
                                  AND CURRENT ROW )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 );

<existing window name>을 참조하여 <window specification>을 재정의 할 경우

• 재정의 되는 부분에 <window partition clause>를 기술할 수 없다.

SELECT SUM(i2) OVER ( w1 PARTITION BY i1 )  <--- ( X )
  FROM t1
WINDOW w1 AS ( );

• <existing window name> 에 order by clause가 기술된 경우, 
  재정의 되는 부분에 order by clause를 기술할 수 없다.

SELECT SUM(i2) OVER ( w1 ORDER BY i3 ) <--- ( X )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 );

• <existing window name>에 window frame clause를 기술할 수 없다.

SELECT SUM(i2) OVER ( w1 )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2
               ROWS BETWEEN UNBOUNDED PRECEDING  <--- ( X )
                        AND CURRENT ROW );

<window frame start>

frame end가 생략된 경우 <window frame start>는 <window frame start> AND CURRENT ROW와 동일하다.

<window frame between>

<window frame following> / <window frame preceding>

offset PRECEDING / offset FOLLOWING

설명

WINDOW clause는 select list와 order by clause에 기술되는 window function의 수행 범위를 기술한다.
<window partition clause>로 그룹을 나누고
<window order clause>로 그룹 내 레코드를 정렬하며
<window frame clause>로 그룹 내에 정렬된 레코드에 대해 window function의 대상이 되는 레코드 범위를 정의한다.
WINDOW clause는 FROM, WHERE, GROUP BY, HAVING 절이 수행된 후의 결과 집합에 대해 수행된다.
쿼리에 aggregate, GROUP BY, HAVING 절을 사용할 경우, WINDOW 절에는 원래 테이블의 column 대신 그룹 column을 기술해야 한다.

<window specification>

각 레코드에 대한 window function의 수행 범위를 기술한다.
<existing window name>을 참조하여, 기존에 정의된 정보에 더하여 <window specification>을 재정의 할 수 있다.
SELECT SUM(i2) OVER ( w1 
                      ORDER BY i2
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1 ),
       w2 AS ( w1 ORDER BY i3 );

   → 동일한 구문이다.

SELECT SUM(i2) OVER ( PARTITION BY i1 
                      ORDER BY i2
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1 ),
       w2 AS ( PARTITION BY i1
               ORDER BY i3 );
Window function OVER 절에서 <window name> wname을 참조할 경우, OVER wname과 OVER ( wname )은 동일하지 않다.
* wname으로 정의된 <window specification> 정보 참조

예: w1 참조
SELECT SUM(i2) OVER w1 
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW );
* 기존에 정의된 정보에 더하여 <window specification>을 재정의하며,
  기존 정보에 <window frame clause>를 정의할 수 없다.

예: w1 참조 
SELECT SUM(i2) OVER ( w1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 );

예: w1 참조 ( 오류 상황 : 기존 정보에 <window frame clause>를 정의할 수 없다. )
SELECT SUM(i2) OVER ( w1 ) 
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW );  <---

<window partition clause>

PARTITION BY를 사용하여 <window partition column reference list>을 기반으로 쿼리 결과 집합을 그룹으로 분할한다.
이 절을 생략하면 함수는 쿼리 결과 집합의 모든 row를 단일 그룹으로 처리한다.
gSQL> 
SELECT orderdate,
       orderkey,
       totalprice, 
       SUM( totalprice ) OVER( PARTITION BY orderdate ) AS SUM_OVER_RESULT 
FROM orders;

ORDERDATE  ORDERKEY TOTALPRICE SUM_OVER_RESULT
---------- -------- ---------- ---------------
1998-07-24     1730     204656          520630  
1998-07-24    17056     289620          520630  
1998-07-24    19937      26354          520630      partition 1
----------------------------------------------------------------------
1998-07-25     2400     150304          368523  
1998-07-25    11204      27165          368523  
1998-07-25    11938     191054          368523      partition 2 
----------------------------------------------------------------------
1998-07-26    35655      13698          362691  
1998-07-26    53377     185930          362691  
1998-07-26    55010     163063          362691      partition 3 
----------------------------------------------------------------------

9 rows selected.
gSQL> 
SELECT orderdate,
       orderkey,
       totalprice,
       SUM( totalprice ) OVER() AS SUM_OVER_RESULT 
  FROM orders;

ORDERDATE  ORDERKEY TOTALPRICE SUM_OVER_RESULT
---------- -------- ---------- ---------------
1998-07-24     1730     204656         1251844  
1998-07-24    17056     289620         1251844  
1998-07-24    19937      26354         1251844  
1998-07-25     2400     150304         1251844  
1998-07-25    11204      27165         1251844  
1998-07-25    11938     191054         1251844  
1998-07-26    35655      13698         1251844  
1998-07-26    53377     185930         1251844  
1998-07-26    55010     163063         1251844      partition 1 
----------------------------------------------------------------------

9 rows selected.

<window order clause>

ORDER BY를 사용하여 <sort specification list>를 기반으로 파티션 내에서 데이터가 정렬되는 방식을 지정한다.

<window frame clause>

Window function의 대상이 되는 레코드 범위인 window frame을 지정한다.
window frame은 쿼리의 각 row (current row)와 관련된 레코드 범위이다.
window frame의 대상은 현재 파티션 내에 정렬된 레코드이다. 
window frame은 적용할 단위 (ROWS/ RANGE/ GROUPS), 시작 지점과 끝 지점, 제외할 레코드를 정의할 수 있다.
생략할 경우, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 적용된다.
gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          180000
2000-01-01   102 3088163      42000          269000   <- peer ( custkey 값 동일 )
2000-01-01   103 3088163      47000          269000      peer
2000-01-01   104 3088165     217000          486000
2000-01-01   105 3088167     108000          734000   <- peer ( custkey 값 동일 )
2000-01-01   106 3088167      60000          734000      peer
2000-01-01   107 3088167      80000          734000      peer
...
15 rows selected.
gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     269000 1+2+3 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     734000 1+2+3+4+5+6+7 
2000-01-01   106 3088167      60000 6     734000 1+2+3+4+5+6+7 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     816000 1+2+3+4+5+6+7+8+9+10 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     594000 1+2+3+4+5
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.

<window frame units>

ROWS/ RANGE/ GROUPS는 window frame을 적용하는 단위이다.

<window frame extent>

window frame start (시작 지점)와 window frame end (끝지점)을 정의한다.

<window frame exclusion>

window frame에서 제외할 레코드를 정의한다.

<window frame extent> 사용 예

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5
2000-01-01   106 3088167      60000 6     654000 1+2+3+4+5+6 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     486000 1+2+3+4
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.
# RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     269000 1+2+3 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     734000 1+2+3+4+5+6+7 
2000-01-01   106 3088167      60000 6     734000 1+2+3+4+5+6+7 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     816000 1+2+3+4+5+6+7+8+9+10 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     594000 1+2+3+4+5
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.
# GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     269000 1+2+3 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     734000 1+2+3+4+5+6+7 
2000-01-01   106 3088167      60000 6     734000 1+2+3+4+5+6+7 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     816000 1+2+3+4+5+6+7+8+9+10 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     594000 1+2+3+4+5
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.
# ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN 1 PRECEDING 
                                        AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     269000 1+2+3 
2000-01-01   102 3088163      42000 2     486000 1+2+3+4 
2000-01-01   103 3088163      47000 3     414000 2+3+4+5 
2000-01-01   104 3088165     217000 4     432000 3+4+5+6 
2000-01-01   105 3088167     108000 5     465000 4+5+6+7 
2000-01-01   106 3088167      60000 6     280000 5+6+7+8 
2000-01-01   107 3088167      80000 7     202000 6+7+8+9 
2000-01-01   108 3088169      32000 8     162000 7+8+9+10 
2000-01-01   109 3088170      30000 9      82000 8+9+10
2000-01-01   110 3088170      20000 10      50000 9+10
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     269000 1+2+3 
2000-03-03   302 3088161      42000 2     486000 1+2+3+4 
2000-03-03   303 3088165      47000 3     414000 2+3+4+5 
2000-03-03   304 3088167     217000 4     372000 3+4+5 
2000-03-03   305 3088167     108000 5     325000 4+5 

15 rows selected.
# RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING

#####################################################
# ORDER BY column을 ASC 방식으로 정렬할 경우 
#####################################################

 • 1 PRECEDING 
   -->   ( current row의 sortkey value - 1 ) 이상인 value
       = ( custkey - 1 ) 이상인 value

 • 2 FOLLOWING
   -->   ( current row의 sortkey value + 2 ) 이하인 value
       = ( custkey + 2 ) 이하인 value

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN 1 PRECEDING 
                                         AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     269000 1+2+3 
2000-01-01   102 3088163      42000 2     306000 2+3+4 
2000-01-01   103 3088163      47000 3     306000 2+3+4 
2000-01-01   104 3088165     217000 4     465000 4+5+6+7 
2000-01-01   105 3088167     108000 5     280000 5+6+7+8 
2000-01-01   106 3088167      60000 6     280000 5+6+7+8 
2000-01-01   107 3088167      80000 7     280000 5+6+7+8 
2000-01-01   108 3088169      32000 8      82000 8+9+10 
2000-01-01   109 3088170      30000 9      82000 8+9+10 
2000-01-01   110 3088170      20000 10      82000 8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2 
2000-03-03   302 3088161      42000 2     222000 1+2 
2000-03-03   303 3088165      47000 3     372000 3+4+5 
2000-03-03   304 3088167     217000 4     325000 4+5 
2000-03-03   305 3088167     108000 5     325000 4+5 

15 rows selected.


#####################################################
# ORDER BY column을 DESC 방식으로 정렬할 경우 
#####################################################

 • 1 PRECEDING 
   -->   ( current row의 sortkey value + 1 ) 이하인 value
       = ( custkey + 1 ) 이하인 value

 • 2 FOLLOWING
   -->   ( current row의 sortkey value - 2 ) 이상인 value
       = ( custkey - 2 ) 이상인 value

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey DESC
                               RANGE BETWEEN 1 PRECEDING 
                                         AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   109 3088170      30000 1      82000 1+2+3 
2000-01-01   110 3088170      20000 2      82000 1+2+3 
2000-01-01   108 3088169      32000 3     330000 1+2+3+4+5+6 
2000-01-01   105 3088167     108000 4     465000 4+5+6+7 
2000-01-01   106 3088167      60000 5     465000 4+5+6+7 
2000-01-01   107 3088167      80000 6     465000 4+5+6+7 
2000-01-01   104 3088165     217000 7     306000 7+8+9 
2000-01-01   102 3088163      42000 8     269000 8+9+10 
2000-01-01   103 3088163      47000 9     269000 8+9+10 
2000-01-01   101 3088161     180000 10     180000 10 
-------------------------------------------------------------------------
2000-03-03   304 3088167     217000 1     372000 1+2+3 
2000-03-03   305 3088167     108000 2     372000 1+2+3 
2000-03-03   303 3088165      47000 3      47000 3 
2000-03-03   301 3088161     180000 4     222000 4+5 
2000-03-03   302 3088161      42000 5     222000 4+5 

15 rows selected.
# GROUPS BETWEEN 1 PRECEDING AND 2 FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN 1 PRECEDING 
                                          AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     486000 1+2+3+4 
2000-01-01   102 3088163      42000 2     734000 1+2+3+4+5+6+7 
2000-01-01   103 3088163      47000 3     734000 1+2+3+4+5+6+7 
2000-01-01   104 3088165     217000 4     586000 2+3+4+5+6+7+8 
2000-01-01   105 3088167     108000 5     547000 4+5+6+7+8+9+10 
2000-01-01   106 3088167      60000 6     547000 4+5+6+7+8+9+10 
2000-01-01   107 3088167      80000 7     547000 4+5+6+7+8+9+10 
2000-01-01   108 3088169      32000 8     330000 5+6+7+8+9+10 
2000-01-01   109 3088170      30000 9      82000 8+9+10 
2000-01-01   110 3088170      20000 10      82000 8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     594000 1+2+3+4+5 
2000-03-03   302 3088161      42000 2     594000 1+2+3+4+5 
2000-03-03   303 3088165      47000 3     594000 1+2+3+4+5 
2000-03-03   304 3088167     217000 4     372000 3+4+5 
2000-03-03   305 3088167     108000 5     372000 3+4+5 

15 rows selected.
# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN CURRENT ROW 
                                        AND UNBOUNDED FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          816000
2000-01-01   102 3088163      42000          636000
2000-01-01   103 3088163      47000          594000
2000-01-01   104 3088165     217000          547000
2000-01-01   105 3088167     108000          330000
2000-01-01   106 3088167      60000          222000
2000-01-01   107 3088167      80000          162000
2000-01-01   108 3088169      32000           82000
2000-01-01   109 3088170      30000           50000
2000-01-01   110 3088170      20000           20000
2000-03-03   301 3088161     180000          594000
2000-03-03   302 3088161      42000          414000
2000-03-03   303 3088165      47000          372000
2000-03-03   304 3088167     217000          325000
2000-03-03   305 3088167     108000          108000

15 rows selected.
# RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN CURRENT ROW 
                                         AND UNBOUNDED FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          816000
2000-01-01   102 3088163      42000          636000
2000-01-01   103 3088163      47000          636000
2000-01-01   104 3088165     217000          547000
2000-01-01   105 3088167     108000          330000
2000-01-01   106 3088167      60000          330000
2000-01-01   107 3088167      80000          330000
2000-01-01   108 3088169      32000           82000
2000-01-01   109 3088170      30000           50000
2000-01-01   110 3088170      20000           50000
2000-03-03   301 3088161     180000          594000
2000-03-03   302 3088161      42000          594000
2000-03-03   303 3088165      47000          372000
2000-03-03   304 3088167     217000          325000
2000-03-03   305 3088167     108000          325000

15 rows selected.
# GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN CURRENT ROW 
                                          AND UNBOUNDED FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          816000
2000-01-01   102 3088163      42000          636000
2000-01-01   103 3088163      47000          636000
2000-01-01   104 3088165     217000          547000
2000-01-01   105 3088167     108000          330000
2000-01-01   106 3088167      60000          330000
2000-01-01   107 3088167      80000          330000
2000-01-01   108 3088169      32000           82000
2000-01-01   109 3088170      30000           50000
2000-01-01   110 3088170      20000           50000
2000-03-03   301 3088161     180000          594000
2000-03-03   302 3088161      42000          594000
2000-03-03   303 3088165      47000          372000
2000-03-03   304 3088167     217000          325000
2000-03-03   305 3088167     108000          325000

15 rows selected.

<window frame exclusion> 사용 예

# ROWS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW
                               EXCLUDE CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null 
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     222000 1+2 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     594000 1+2+3+4+5 
2000-01-01   107 3088167      80000 7     654000 1+2+3+4+5+6 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     796000 1+2+3+4+5+6+7+8+9 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2     180000 1 
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     486000 1+2+3+4 

15 rows selected.
# RANGE

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW
                               EXCLUDE CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     227000 1+3 
2000-01-01   103 3088163      47000 3     222000 1+2 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     626000 1+2+3+4+6+7 
2000-01-01   106 3088167      60000 6     674000 1+2+3+4+5+7 
2000-01-01   107 3088167      80000 7     654000 1+2+3+4+5+6 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     786000 1+2+3+4+5+6+7+8+10 
2000-01-01   110 3088170      20000 10     796000 1+2+3+4+5+6+7+8+9 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1      42000 2 
2000-03-03   302 3088161      42000 2     180000 1 
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     377000 1+2+3+5 
2000-03-03   305 3088167     108000 5     486000 1+2+3+4 

15 rows selected.
# GROUPS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW
                               EXCLUDE CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     227000 1+3 
2000-01-01   103 3088163      47000 3     222000 1+2 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     626000 1+2+3+4+6+7 
2000-01-01   106 3088167      60000 6     674000 1+2+3+4+5+7 
2000-01-01   107 3088167      80000 7     654000 1+2+3+4+5+6 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     786000 1+2+3+4+5+6+7+8+10 
2000-01-01   110 3088170      20000 10     796000 1+2+3+4+5+6+7+8+9 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1      42000 2 
2000-03-03   302 3088161      42000 2     180000 1 
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     377000 1+2+3+5 
2000-03-03   305 3088167     108000 5     486000 1+2+3+4 

15 rows selected.
# ROWS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW
                               EXCLUDE GROUP ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     180000 1 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     486000 1+2+3+4 
2000-01-01   107 3088167      80000 7     486000 1+2+3+4 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     766000 1+2+3+4+5+6+7+8 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2       null
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     269000 1+2+3 

15 rows selected.
# RANGE

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW
                               EXCLUDE GROUP ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     180000 1 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     486000 1+2+3+4 
2000-01-01   107 3088167      80000 7     486000 1+2+3+4 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     766000 1+2+3+4+5+6+7+8 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2       null
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     269000 1+2+3 

15 rows selected.
# GROUPS

gSQL> SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW
                               EXCLUDE GROUP ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     180000 1 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     486000 1+2+3+4 
2000-01-01   107 3088167      80000 7     486000 1+2+3+4 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     766000 1+2+3+4+5+6+7+8 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2       null
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     269000 1+2+3 

15 rows selected.
# ROWS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW
                               EXCLUDE TIES ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     227000 1+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5 
2000-01-01   106 3088167      60000 6     546000 1+2+3+4+6 
2000-01-01   107 3088167      80000 7     566000 1+2+3+4+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     786000 1+2+3+4+5+6+7+8+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1 
2000-03-03   302 3088161      42000 2      42000 2 
2000-03-03   303 3088165      47000 3     269000 1+2+3 
2000-03-03   304 3088167     217000 4     486000 1+2+3+4 
2000-03-03   305 3088167     108000 5     377000 1+2+3+5 

15 rows selected.
# RANGE

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW
                               EXCLUDE TIES ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     227000 1+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5 
2000-01-01   106 3088167      60000 6     546000 1+2+3+4+6 
2000-01-01   107 3088167      80000 7     566000 1+2+3+4+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     786000 1+2+3+4+5+6+7+8+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1 
2000-03-03   302 3088161      42000 2      42000 2 
2000-03-03   303 3088165      47000 3     269000 1+2+3 
2000-03-03   304 3088167     217000 4     486000 1+2+3+4 
2000-03-03   305 3088167     108000 5     377000 1+2+3+5 

15 rows selected.
# GROUPS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW
                               EXCLUDE TIES ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     227000 1+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5 
2000-01-01   106 3088167      60000 6     546000 1+2+3+4+6 
2000-01-01   107 3088167      80000 7     566000 1+2+3+4+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     786000 1+2+3+4+5+6+7+8+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1 
2000-03-03   302 3088161      42000 2      42000 2 
2000-03-03   303 3088165      47000 3     269000 1+2+3 
2000-03-03   304 3088167     217000 4     486000 1+2+3+4 
2000-03-03   305 3088167     108000 5     377000 1+2+3+5 

15 rows selected.

사용 예

gSQL> 
SELECT item_no,
       sales_date,
       sales,
       SUM( sales ) OVER W1 cumulative_sales, 
       AVG( sales ) OVER w1 avg_sales
  FROM store
WINDOW w1 AS ( PARTITION BY item_no
               ORDER BY sales_date
               ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );

ITEM_NO SALES_DATE SALES CUMULATIVE_SALES AVG_SALES
------- ---------- ----- ---------------- ---------
    100 2001-01-01   150              150       150
    100 2001-01-02   100              250       125
    100 2001-01-03   170              420       140
    100 2001-01-04    90              510     127.5
    100 2001-01-05   200              710       142
    235 2001-01-01    70               70        70
    235 2001-01-02   130              200       100
    235 2001-01-03   190              390       130
    235 2001-01-04   150              540       135
    235 2001-01-05    50              590       118

10 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T611

Elementary OLAP operations

X

T612

Advanced OLAP operations

X

T301

Functional dependencies

X

T620

WINDOW clause: GROUPS option

O

참조

관련 내용은 다음을 참조한다.

order by clause

기능

검색 결과의 정렬 순서를 기술한다.

구문

<order by clause> ::=
    ORDER BY <sort specification list>

<sort specification list> ::=
    <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
    <sort key> [ <ordering specification> ] [ <null ordering> ]

<sort key> ::=
    <value expression>

<ordering specification> ::=
      ASC
    | DESC

<null ordering> ::=
      NULLS FIRST
    | NULLS LAST

사용 범위 및 접근 권한

정렬하기 위해 기술한 <sort key>에 column이 존재하는 경우 column에 대한 접근 권한이 있어야 한다.

구문 규칙 및 파라미터

<order by clause>

<sort specification list>

<sort key>

설명

<order by clause>

<order by clause>는 검색 결과를 정렬하는 방법을 기술한다.

<order by clause>에는 <sort key>들을 콤마 (,) 리스트로 나열할 수 있으며, 나열한 순서대로 각 레코드들의 <sort key>를 비교하여 순서대로 정렬한다.
SELECT c1, c2 FROM t1 ORDER BY c1, c2;

<sort key>에는 오름차순 정렬 또는 내림차순 정렬을 지정할 수 있는 <ordering specification>을 기술할 수 있는데 생략할 경우에는 오름차순으로 정렬된다.

gSQL> SELECT c1 FROM t1;
C1
--
 2
 3
 1
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 ORDER BY c1 ASC;
C1
--
 1
 2
 3
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1 DESC;
C1
--
 3
 2
 1
3 rows selected.

<sort key>에는 NULL 값과 NULL이 아닌 값의 순서를 <null ordering>을 사용하여 지정할 수 있는데 생략할 경우에는 NULLS LAST로 정렬된다.

gSQL> SELECT c1 FROM t1;
  C1
----
   2
null
   1
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1;    
  C1
----
   1
   2
null
3 rows selected.

gSQL> SELECT c1 FROM t1 ORDER BY c1 NULLS LAST;
  C1
----
   1
   2
null
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1 NULLS FIRST;
  C1
----
null
   1
   2
3 rows selected.

<sort key>에 상수값을 기술할 경우 <select list>에서 해당 값의 순번에 위치한 expression을 <sort key>로 간주한다. 그리고 이 때 기술하는 상수값은 0보다 큰 정수이며, <select list>에 기술한 expression의 전체 개수와 같거나 작아야 한다.

gSQL> SELECT c1 FROM t1 ORDER BY 1;
  C1
----
   1
   2
null
3 rows selected.

<sort key>에는 LONG type ( LONG VARCHAR, LONG VARBINARY )을 기술할 수 없다.

null value와의 비교

동일한 sort key 값을 가지는 row들의 정렬

Sort key로 구분할 수 없는 row들을 peer라고 하며, peer들은 탐색 순서에 따라 정렬된다.

<sort key>로 사용되는 <aggregation function>

<query specification>에서 <aggregation function>이 사용되거나 <group by clause>가 기술된 경우, <aggregation function>을 <sort key>로 사용할 수 있다.
단, <group by clause>가 기술된 경우에만 중첩된 <aggregation function>을 <sort key>로 사용할 수 있다.
gSQL> SELECT c1, c2 FROM t1;
C1 C2
-- --
 2  1
 3  5
 1  2
 2 10
 3 10
5 rows selected.

gSQL> SELECT sum(c1) FROM t1 ORDER BY sum(c1);
SUM(C1)
-------
     11
1 row selected.

gSQL> SELECT c1, sum(c2) FROM t1 GROUP BY c1 ORDER BY sum(c2);
C1 SUM(C2)
-- -------
 1       2
 2      11
 3      15
3 rows selected.

gSQL> SELECT sum(c1) FROM t1 GROUP BY c1 ORDER BY sum(sum(c1));
SUM(C1)
-------
      6
1 row selected.

사용 예

다음은 ORDER BY를 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY c_nation;

C_NAME     C_NATION
---------- -------------
Customer#2 CANADA
Customer#4 GERMANY
Customer#1 KOREA
Customer#3 KOREA
Customer#5 UNITED STATES

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY c_nation DESC;

C_NAME     C_NATION
---------- -------------
Customer#5 UNITED STATES
Customer#1 KOREA
Customer#3 KOREA
Customer#4 GERMANY
Customer#2 CANADA

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY 2 DESC;

C_NAME     C_NATION     
---------- -------------
Customer#5 UNITED STATES
Customer#1 KOREA        
Customer#3 KOREA        
Customer#4 GERMANY      
Customer#2 CANADA       

5 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F850

Top-level <order by clause> in <query expression>

O

F851

<order by clause> in subqueries

O

F852

Top-level <order by clause> in views

O

F855

Nested <order by clause> in <query expression>

O

참조

관련 내용은 query expression을 참조한다.

offset limit clause

기능

검색 결과에 대하여 skip 할 row의 개수와 fetch 할 row의 개수를 기술한다.

구문

<offset limit clause> ::=
      <result offset clause>
    | <fetch limit clause>
    | <result offset clause> <fetch limit clause>

<result offset clause> ::=
    OFFSET <offset row count> [ { ROW | ROWS } ]

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

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

<limit clause> ::=
    LIMIT { <fetch row count> | <offset row count> , <fetch row count> | ALL }

사용 범위 및 접근 권한

<offset limit clause>는 접근 권한을 필요로 하지 않는다.

구문 규칙 및 파라미터

<result offset clause>

<fetch limit clause>

<fetch first clause>

<limit clause>

설명

<result offset clause>

검색한 결과 중에 <offset row count> 번 째 row부터 fetch 한다. 만일 <offset row count>가 검색한 결과가 row의 개수와 같거나 크면 fetch row 개수는 0이다.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 OFFSET 1;
C1
--
 2
 3
2 rows selected.

gSQL> SELECT c1 FROM t1 OFFSET 3;
no rows selected.

<fetch first clause>

검색한 결과 중에 <fetch row count> 개수만큼만 fetch한다.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 FETCH FIRST 2 ROWS ONLY;
C1
--
 1
 2
2 rows selected.

<limit clause>

LIMIT <fetch_row_count>를 사용한 경우, 검색한 결과 중에 <fetch row count> 개수만큼만 fetch한다.

LIMIT <offset row count>, <fetch row count>를 사용한 경우, 검색한 결과 중에 <offset row count>번째 row부터 <fetch row count> 개수만큼만 fetch한다.

LIMIT ALL을 사용한 경우 개수 제한없이 검색한 결과를 fetch한다.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

• LIMIT <fetch_row_count>
gSQL> SELECT c1 FROM t1 LIMIT 2;
C1
--
 1
 2
2 rows selected.

• LIMIT <offset row count>, <fetch_row_count>
gSQL> SELECT c1 FROM t1 LIMIT 1, 1;
C1
--
 2
1 row selected.

• LIMIT ALL
gSQL> SELECT c1 FROM t1 LIMIT ALL;
C1
--
 1
 2
 3
3 rows selected.

사용 예

다음은 <result offset clause>을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1;

C_NAME     C_NATION
---------- -------------
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

4 rows selected.

다음은 <fetch first clause>을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, c_nation FROM customer FETCH FIRST ROW ONLY;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA

1 row selected.

gSQL> SELECT c_name, c_nation FROM customer FETCH FIRST 2 ROW ONLY;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.

다음은 <limit clause>을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, c_nation FROM customer LIMIT 1;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA

1 row selected.

gSQL> SELECT c_name, c_nation FROM customer LIMIT 1, 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

gSQL> SELECT c_name, c_nation FROM customer LIMIT ALL;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.

다음은 <result offset clause>과 <fetch limit clause>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1 FETCH 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1 LIMIT 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F861

Top-level <result offset clause> in <query expression>

O

F862

<result offset clause> in subqueries

O

F863

Nested <result offset clause> in <query expression>

O

F864

Top-level <result offset clause> in views

O

F865

dynamic <offset row count> in <result offset clause>

X

set operator

기능

부질의 (subquery) 결과들에 대한 집합 (set) 연산을 수행한다.

구문

<set operator> ::=
      <set operator term>
    | <query expression body> UNION [ ALL | DISTINCT ] <set operator term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ] <set operator term>
    | <query expression body> MINUS [ ALL | DISTINCT ] <set operator term>

<set operator term> ::=
      <query term>
    | <set operator term> INTERSECT [ ALL | DISTINCT ] <set operator term>

사용 범위 및 접근 권한

<set operator> 구문을 사용하려면 각 <set operator term>에 나타나는 <query expression>에 대한 접근 권한이 있어야 한다.

구문 규칙 및 파라미터

<set operator>

<query term>

하나의 부질의 (subquery)를 기술한다.
자세한 내용은 query expression 절을 참조한다.

설명

<set operator>의 ALL과 DISTINCT의 차이

예를 들어 R1과 R2 table의 데이터가 다음과 같을 경우, 각 <set operator>의 결과는 다음과 같다.

SET 연산 결과

SET 연산 결과

연산자 우선 순위

<set operator>의 연산자 우선순위는 다음과 같다.

<set operator>의 결과 타입

<set operator> 모든 부질의의 i 번째 column은 동일한 계열의 데이터 타입이어야 하며, 결과 타입 조합 규칙에 따라 결과 타입이 결정된다.
단, LONG VARCHAR와 LONG VARBINARY 타입은 UNION ALL만 사용할 수 있다.

ORDER BY 구문

<set operator>를 ORDER BY와 함께 사용할 때 부질의 간에 column 이름이 다를 경우, 다음과 같이 사용할 수 있다.

사용 예

다음은 UNION 연산을 사용한 SELECT 구문의 예이다.

gSQL> SELECT s_nation nation FROM supplier UNION ALL SELECT c_nation FROM customer;

NATION
-------------
FRANCE
KOREA
GERMANY
UNITED STATES
CANADA
KOREA
CANADA
KOREA
GERMANY
UNITED STATES

10 rows selected.

gSQL> SELECT s_nation nation FROM supplier UNION DISTINCT SELECT c_nation FROM customer;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY
FRANCE

5 rows selected.

다음은 EXCEPT 연산을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_nation nation FROM customer EXCEPT ALL SELECT s_nation FROM supplier;

NATION
------
KOREA

1 row selected.

gSQL> SELECT c_nation nation FROM customer EXCEPT DISTINCT SELECT s_nation FROM supplier;

no rows selected.

다음은 INTERSECT 연산을 사용한 SELECT 구문의 예이다.

gSQL> SELECT c_nation nation FROM customer INTERSECT ALL SELECT s_nation FROM supplier;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY

4 rows selected.

gSQL> SELECT c_nation nation FROM customer INTERSECT DISTINCT SELECT s_nation FROM supplier;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY

4 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F302

INTERSECT table operator

O

F301

CORRESPONDING

X

T551

Optional key words for default syntax

O

F304

EXCEPT ALL table operator

O

참조

관련 내용은 query expression을 참조한다.

subquery

기능

<query expression>에서 파생되는 scalar value, row, table 등을 기술한다.

구문

<scalar subquery> ::=
    <subquery>

<row subquery> ::=
    <subquery>

<table subquery> ::=
    <subquery>

<subquery> ::=
    ( <query expression> )

사용 범위 및 접근 권한

<subquery>에 존재하는 <query expression>에 대한 접근 권한이 있어야 한다.

구문 규칙 및 파라미터

<scalar subquery>

<row subquery>

<table subquery>

설명

<scalar subquery>

<scalar subquery>는 결과값으로 한 개의 column을 갖는 한 개의 row를 반환하는 subquery이다. <scalar subquery>의 target은 하나만 존재해야 하며, 결과의 data type은 target의 data type을 따른다.

<scalar subquery>는 <select list>의 target에 단독으로 쓰일 수 있으며, 단일 column만 갖는 연산자에 쓰일 수 있다.

<row subquery>

<row subquery>는 결과값으로 두 개 이상의 column을 갖는 한 개의 row를 반환하는 subquery이다. <row subquery>의 target은 두 개 이상 존재해야 하며, 결과의 data type은 target들 각각의 data type을 따른다.

<row subquery>는 <select list>의 target에 단독으로 쓰일 수 없으며, 둘 이상의 column을 갖는 row 연산자에만 쓰일 수 있다.

<table subquery>

<table subquery>는 결과값으로 한 개 이상의 column을 갖는 한 개 이상의 row를 반환하는 subquery이다. <table subquery>의 target은 한 개 이상 존재해야 하며, 결과의 data type은 target들 각각의 data type을 따른다.

<table subquery>는 <select list>의 target에 단독으로 쓰일 수 없으며, IN, NOT IN, EXISTS, NOT EXISTS, quantify operator 등의 연산자에 쓰일 수 있다.

사용 예

다음은 <scalar subquery>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT (SELECT c_name FROM dual)  FROM customer;

(SELECT C_NAME FROM DUAL)
-------------------------
Customer#1
Customer#2
Customer#3
Customer#4
Customer#5

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer WHERE c_nation = (SELECT 'CANADA' FROM dual);

C_NAME     C_NATION
---------- --------
Customer#2 CANADA

1 row selected.

다음은 <row subquery>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT p_name, p_brand, p_type FROM part WHERE (p_brand, p_type) = (SELECT 'Brand#1', 'NICKEL' FROM dual);

P_NAME P_BRAND    P_TYPE
------ ---------- ------
Part#2 Brand#1    NICKEL

1 row selected.

다음은 <table subquery>를 사용한 SELECT 구문의 예이다.

gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation IN (SELECT c_nation FROM customer);

S_NAME                    S_NATION
------------------------- -------------
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

4 rows selected.

gSQL> SELECT * FROM (SELECT s_name, s_nation FROM supplier);

S_NAME                    S_NATION
------------------------- -------------
Supplier#1                FRANCE
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

5 rows selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F471

Scalar subquery values

O

F641

Row and table constructors

X

T501

Enhanced EXISTS predicate

O

E061-11

Subqueries in IN predicate

O

E061-12

Subqueries in quantified comparison predicate

O

E061-12

Correlated subqueries

O

참조

관련 내용은 다음을 참조한다.

hint clause

Query를 수행할 때 사용할 hint를 기술한다.
자세한 내용은 SQL Hint를 참조한다.

SELECT .. FOR UPDATE

기능

SELECT 구문의 결과 집합을 갱신할지 여부를 설정한다.

구문

<select for update statement> ::=
    <query expression>  <updatability clause>
    ;

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

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

사용 범위 및 접근 권한

<select for update statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

<query expression>

SELECT 구문에 INTO 절이 없어야 한다.

FOR UPDATE를 사용하려면 query가 base table의 row 변화를 식별하거나 row에 lock을 획득할 수 있는 updatable query여야 한다.

Updatable query는 다음 조건을 모두 만족해야 한다.

SELECT 구문에 대한 자세한 내용은 query expression을 참조한다.

<updatability clause>

결과 집합에 대한 row를 변경할지 여부를 지정한다.

FOR UPDATE OF …

질의를 수행할 때 lock 획득과 관련된 column들을 나열한다.

<lock wait mode>

FOR UPDATE 구문과 함께 사용하며, lock 획득 방법을 지정한다.

설명

SELECT 구문은 transaction의 종료 여부와 관계없이 row에 대한 fetch를 지속할 수 있는 반면에, SELECT .. FOR UPDATE 구문은 row들에 대한 lock을 획득하기 때문에 transaction이 종료되면 fetch 할 수 없다.

Cursor holdability



사용 예

다음은 FOR UPDATE 구문을 사용하여 row에 대한 lock을 획득하는 예이다.

gSQL> SELECT id, data FROM t1 WHERE id = 3 FOR UPDATE;

ID DATA  
-- ------
 3 data_3

1 row selected.

다음과 같이 join과 ORDER BY 구문을 사용하더라도 updatable query이면 FOR UPDATE 구문을 사용할 수 있다.

gSQL> SELECT t1.id, t1.name, t2.addr 
        FROM t1, t2
       WHERE t1.id = t2.id
       ORDER BY 1
         FOR UPDATE;

ID NAME    ADDR         
-- ------- -------------
 1 someone somewhere    
 2 anyone  anywhere     
 3 unknown N/A          
 4 leekmo  leekmo's home
 5 mkkim   seoul        

5 rows selected.

다음과 같이 updatable query가 아닌 경우에는 FOR UPDATE 구문을 사용할 수 없다.

gSQL> SELECT id, COUNT(*)
        FROM t1
       GROUP BY id
         FOR UPDATE;

ERR-42000(16112): query expression is not updatable

호환성

SQL 표준에서는 <select for update statement>를 정의하지 않고 있는데, 이는 DECLARE cursor_name 구문을 사용하여 정의할 수 있다.

SELECT .. INTO

기능

질의를 통해 row 하나를 검색하고, 검색한 row의 값을 호스트 변수로 얻어온다.

구문

<select statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>
    ;

<select target list> ::=
    variable_name [, ...]

사용 범위 및 접근 권한

<select statement: single row> 구문을 수행하려면 사용자에게 구문에 사용된 모든 테이블에 대한 다음 권한 중 하나가 있어야 한다.

구문 규칙 및 파라미터

<hint clause>

질의를 수행하기 위한 힌트를 기술한다.
자세한 내용은 SELECT 구문의 hint clause 절을 참조한다.

<set quantifier>

질의 결과에서 중복을 제거할지 여부를 기술한다.
자세한 내용은 query specification 절을 참조한다.

<select list>

질의 결과로부터 검색할 column을 기술한다.
자세한 내용은 select list 절을 참조한다.

INTO <select target list>

INTO 절에 기술된 변수의 개수는 <select list>에 기술된 expression의 개수와 동일해야 한다.

<table expression>

검색 조건 등 질의 내용을 기술한다.
자세한 내용은 query specification  절을 참조한다.

설명

검색할 row가 한 건 이하여야 한다. 
두 건 이상의 row가 검색될 경우, 에러가 발생한다.

SELECT 구문들의 차이점

사용 예

다음은 interactive SQL (gsql)을 사용하여 host 변수에 값을 얻어오는 예이다.

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

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

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

1 row selected.

SELECT .. INTO .. FOR UPDATE

기능

질의를 통해 row 하나를 검색하여 갱신을 수행할지 여부를 설정한 후, 검색한 row의 값을 호스트 변수에 얻어온다.

구문

<select for update statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>  <updatability clause>
    ;

<select target list> ::=
    variable_name [, ...]

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

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

사용 범위 및 접근 권한

<select statement: single row> 구문을 수행하려면 사용자에게 구문에 사용된 모든 테이블에 대한 다음 권한 중 하나가 있어야 한다.

FOR UPDATE 구문을 사용할 경우, lock 대상이 되는 테이블에 대해 다음 권한 중 하나가 있어야 한다.

구문 규칙 및 파라미터

<select for update statement: single row>

FOR UPDATE를 사용하려면 query가 base table의 row 변화를 식별하거나 row에 lock을 획득할 수 있는 updatable query여야 한다.

Updatable query는 다음 조건을 모두 만족해야 한다.

<updatability clause>

결과 집합에 대해 row를 변경할지 여부를 지정한다.

FOR UPDATE OF …

질의를 수행할 때 lock 획득과 관련된 column을 나열한다.

<lock wait mode>

FOR UPDATE 구문과 함께 사용하며, lock 획득 방법을 지정한다.

<hint clause>

질의를 수행하기 위한 힌트를 기술한다.
자세한 내용은 SELECT 구문의 hint clause 절을 참조한다.

<set quantifier>

질의 결과에서 중복을 제거할지 여부를 기술한다.
자세한 내용은 query specification 절을 참조한다.

<select list>

질의 결과로부터 검색할 column을 기술한다.

자세한 내용은 select list 절을 참조한다.

INTO <select target list>

INTO 절에 기술된 변수의 개수는 <select list> 에 기술된 expression의 개수와 동일해야 한다.

<table expression>

검색 조건 등의 질의 내용을 기술한다.
자세한 내용은 query specification 절을 참조한다.

설명

검색할 row가 한 건 이하여야 한다. 
두 건 이상의 row가 검색될 경우, 에러가 발생한다.

SELECT 구문은 transaction의 종료 여부와 관계없이 row에 대한 fetch를 지속할 수 있는 반면에, SELECT .. FOR UPDATE 구문은 row들에 대한 lock을 획득하기 때문에 transaction이 종료되면 fetch 할 수 없다.

Cursor holdability



SELECT 구문들의 차이점

사용 예

다음은 FOR UPDATE 구문을 사용하여 row에 lock을 획득하고, interactive SQL (gsql)을 사용하여 host 변수에 값을 얻어오는 예이다.

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

gSQL> SELECT id, data INTO :v_id, :v_data FROM t1 WHERE id = 3 FOR UPDATE;

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

1 row selected.

다음과 같이 join과 ORDER BY 구문을 사용하더라도 updatable query이면 FOR UPDATE 구문을 사용할 수 있다.

gSQL> \var v_id   INTEGER
gSQL> \var v_name VARCHAR(128)
gSQL> \var v_addr VARCHAR(128)


gSQL> SELECT t1.id, t1.name, t2.addr 
        INTO :v_id, :v_name, :v_addr
        FROM t1, t2
       WHERE t1.id = t2.id
       ORDER BY 1
       LIMIT 1
         FOR UPDATE;

ID NAME    ADDR         
-- ------- -------------
 1 someone somewhere    

1 row selected.

다음과 같이 updatable query가 아닌 경우에는 FOR UPDATE 구문을 사용할 수 없다.

gSQL> \var v_id    INTEGER
gSQL> \var v_count INTEGER

gSQL> SELECT id, COUNT(*)
        INTO :v_id, :v_count
        FROM t1
       GROUP BY id
         FOR UPDATE;

ERR-42000(16112): query expression is not updatable

참조

관련 내용은 다음을 참조한다.

SET CONSTRAINTS

기능

트랜잭션 내에서 지연가능한 제약 조건들의 검사 시점을 IMMEDIATE 또는 DEFERRED로 설정한다.

구문

<set constraints mode statement> ::=
    SET { CONSTRAINT | CONSTRAINTS } <constraint name list> { DEFERRED | IMMEDIATE }
    ;

<constraint name list> ::=
      ALL
    | <constraint name> [, ...]

사용 범위 및 접근 권한

SET CONSTRAINTS를 수행하기 위해 별도의 접근 권한이 필요한 것은 아니다.

Cluster system에서 지원하지 않는다.

구문 규칙 및 파라미터

CONSTRAINT | CONSTRAINTS

CONSTRAINT와 CONSTRAINTS는 동일한 의미의 키워드인데 SQL 표준은 CONSTRAINTS 이다.

<constraint name list>

제약 조건 이름 목록을 기술하거나, ALL 키워드를 사용하여 지연 가능한 제약 조건을 모두 명시할 수 있다. 
<constraint name>을 기술할 경우, 제약 조건은 지연 가능해야 한다. 
ALL은 지연 가능한 모든 제약 조건을 의미한다.

DEFERRED | IMMEDIATE

명시한 지연 가능한 제약 조건들의 검사 시점을 설정한다.

트랜잭션이 진행 중이면, 검사 시점은 현재 트랜잭션에 설정되며, 트랜잭션이 진행 중이 아닌 경우에는 다음 트랜잭션에 설정된다. 
트랜잭션이 종료되면 다음 트랜잭션에 영향을 미치지 않는다.

설명

지연 가능한 제약 조건

지연 가능한 (DEFERRABLE) 제약 조건은 검사 시점을 변경할 수 있다. 
다음은 지연 가능한 제약 조건을 가진 테이블을 생성하고, 데이터를 추가하는 예이다.
gSQL> CREATE TABLE t1 
( 
    id   INTEGER, 
    name VARCHAR(128) CONSTRAINT t1_uk UNIQUE 
                      DEFERRABLE INITIALLY IMMEDIATE
);

Table created.

gSQL> COMMIT;

Commit complete.

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

1 row created.

gSQL> INSERT INTO t1 VALUES ( 2, 'mkkim' );

1 row created.

gSQL> COMMIT;

Commit complete.

위의 예에서 name column에 지연 가능한 UNIQUE 제약 조건을 생성하였으며, 초기 검사 시점이 INITIALLY IMMEDIATE로 설정되어 DML을 수행할 때마다 제약 조건을 검사한다.

이 때, 다음과 같이 두 row의 name 값을 서로 교체하려고 하면 검사 시점이 IMMEDIATE라서 모두 제약 조건을 위반하게 된다.

gSQL> UPDATE t1 SET name = 'mkkim' WHERE id = 1;

ERR-23000(16057): unique constraint (PUBLIC.T1_UK) violated

gSQL> UPDATE t1 SET name = 'leekmo' WHERE id = 2;

ERR-23000(16057): unique constraint (PUBLIC.T1_UK) violated

다음과 같이 검사 시점을 DEFERRED로 변경하면 COMMIT 시점에 제약 조건을 검사하므로 위의 예와 동일한 변경 작업이 모두 성공한다.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> UPDATE t1 SET name = 'mkkim' WHERE id = 1;

1 row updated.

gSQL> UPDATE t1 SET name = 'leekmo' WHERE id = 2;

1 row updated.

gSQL> COMMIT;

Commit complete.

검사 시점을 DEFERRED로 설정하면 COMMIT 시점에 제약 조건을 검사하므로 제약 조건을 위반한 상태에서 트랜잭션을 COMMIT 할 경우 다음과 같이 트랜잭션은 실패하고 ROLLBACK 된다.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> INSERT INTO t1 VALUES ( 3, 'leekmo' );

1 row created.

gSQL> COMMIT;

ERR-40002(16291): transaction rollback: integrity constraint violation : PUBLIC.T1_UK(1)

지연 제약 조건을 위반한 트랜잭션

트랜잭션이 DEFFERED로 설정된 제약 조건을 위반한 상태에서 다음 구문들을 수행할 경우 다음과 같은 에러가 발생한다.

COMMIT 할 경우 원치 않는 ROLLBACK이 발생할 수 있으므로, SET CONSTRAINTS ALL IMMEDIATE 구문을 수행하여 트랜잭션이 제약 조건을 위반한 상태인지 확인해야 한다.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> INSERT INTO t1 VALUES ( 3, 'leekmo' );

1 row created.

gSQL> SET CONSTRAINTS ALL IMMEDIATE;

ERR-23000(16038): integrity constraint violation : PUBLIC.T1_UK(1)

gSQL> SELECT * FROM t1 ORDER BY id;

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

3 rows selected.

gSQL> UPDATE t1 SET name = 'xcom73' WHERE id = 3;

1 row updated.

gSQL> SET CONSTRAINTS ALL IMMEDIATE;

Constraints set.

gSQL> COMMIT;

Commit complete.

트랜잭션 제어 언어

SET CONSTRAINTS 구문은 SAVEPOINT savepoint_specifier 구문과 같이 트랜잭션 진행 중에 사용하는 트랜잭션 제어 언어이다.
SET CONSTRAINTS 구문에는 COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT 구문 등의 트랜잭션 제어가 적용된다.

다음은 여러 개의 지연 가능한 제약 조건을 가진 테이블의 예이다.

CREATE TABLE t1
(
   id1 INTEGER CONSTRAINT t1_uk1 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
   id2 INTEGER CONSTRAINT t1_uk2 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
   id3 INTEGER CONSTRAINT t1_uk3 UNIQUE DEFERRABLE INITIALLY IMMEDIATE
);

다음과 같이 트랜잭션이 진행되는 중에 <set constraints mode statement> 구문을 수행할 경우, 각 시점에 따라 지연 가능한 제약 조건들의 검사 시점이 변경된다.

INSERT INTO t1 VALUES ( 1, 1, 1 );

1 row created.

COMMIT;

Commit complete.
SAVEPOINT sp1;

Savepoint created.
SET CONSTRAINTS t1_uk1 DEFERRED;

Constraints set.
SAVEPOINT sp2;

Savepoint created.
SET CONSTRAINTS t1_uk2 DEFERRED;

Constraints set.
SAVEPOINT sp3;

Savepoint created.
SET CONSTRAINTS ALL DEFERRED;

Constraints set.
SAVEPOINT sp4;

Savepoint created.
SET CONSTRAINTS ALL IMMEDIATE;

Constraints set.

다음과 같이 ROLLBACK TO SAVEPOINT 구문을 사용하여 트랜잭션을 부분 철회할 경우 SET CONSTRAINTS 구문도 함께 부분 철회되어 검사 시점이 변경된다.

INSERT INTO t1 VALUES ( 1, 2, 2 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK1) violated
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp4;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

1 row created.
INSERT INTO t1 VALUES ( 4, 4, 1 );

1 row created.
ROLLBACK TO SAVEPOINT sp3;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

1 row created.
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp2;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp1;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK1) violated
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
SELECT * FROM t1;

ID1 ID2 ID3
--- --- ---
  1   1   1

1 row selected.

트랜잭션을 COMMIT 하거나 ROLLBACK 할 경우, SET CONSTRAINTS 구문의 영향은 종료되며, 모든 지연 가능한 제약 조건들은 제약 조건의 특성으로 설정한 INITIALLY IMMEDIATE 또는 INITIALLY DEFERRED 값을 따른다.

사용 예

다음은 제약 조건 이름을 기술하여 검사 시점을 변경하는 예이다.

gSQL> SET CONSTRAINTS t1_uk1 DEFERRED;

Constraints set.

다음은 모든 지연 가능한 제약 조건의 검사 시점을 변경하는 예이다.

gSQL> SET CONSTRAINTS ALL DEFERRED;

Constraints set.

호환성

SQL 표준에서는 CONSTRAINT 키워드 절을 정의하지 않고 있다.

SQL 표준 호환성

Feature ID

설명

지원 여부

F721

Deferrable constraints

O

참조

관련 내용은 다음을 참조한다.

SET ROLE role_name

기능

Session role과 current role을 변경한다.

구문

<set role statement> ::=
    SET ROLE <role specification> ;

<role specification> ::=
     <role_name>
   | NONE

사용 범위 및 접근 권한

<set role statement> 구문을 수행하려면 다음 조건 중 하나를 만족해야 한다.

구문 규칙 및 파라미터

<role specification>

설명

트랜잭션이 활성화된 상태에서는 <set role statement> 구문을 변경할 수 없다.

Session에 처음 접속했을 때의 current role은 NULL이다.

<set role statement>를 수행하여 current role을 변경한다.
또는 session에 처음 접속했을 때처럼 current role을 설정하지 않는다.

<set role statement> 구문을 수행한 후에는 설정된 current role을 기준으로 모든 구문을 수행한다.

사용 예

다음은 role을 부여받은 사용자가 current role을 설정하고 해제하는 예이다.

gSQL> \connect u1 u1

gSQL> SELECT CURRENT_USER , CURRENT_ROLE FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
U1           null        

1 row selected.

gSQL> SET ROLE role1;

Session set.

gSQL> SELECT CURRENT_USER , CURRENT_ROLE FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
U1           ROLE1       

1 row selected.

gSQL> SET ROLE NONE;

Session set.

gSQL> SELECT CURRENT_USER , CURRENT_ROLE FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
U1           null        

1 row selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T331

Basic roles

O

T332

Extended roles

X

SET SCHEMA schema_name

기능

현재 session에서 사용할 기본 schema 이름을 설정한다.

구문

<set schema statement> ::=
    SET SCHEMA schema_name
    ;

사용 범위 및 접근 권한

없음

구문 규칙 및 파라미터

schema_name

현재 session에 설정할 기본 schema 이름이다.

설명

현재 session에서 사용할 기본 schema 이름을 설정한다.
객체의 schema 이름을 명시하지 않으면 현재 session에서 사용할 기본 schema 이름이 된다.
% gsql u1 u1
gsql> SELECT * FROM r;
gsql> SET SCHEMA new_schema;

gsql> SELECT * FROM r;

사용 예

다음은 u1 사용자가 s1, s2 스키마를 소유한 예이다.

CREATE USER u1 IDENTIFIED BY u1 WITHOUT SCHEMA;
CREATE SCHEMA s1 AUTHORIZATION u1;
CREATE SCHEMA s2 AUTHORIZATION u1;
COMMIT;

ALTER USER u1 SCHEMA PATH ( s1, s2 );
GRANT ALL PRIVILEGES TO u1;
COMMIT;

CREATE TABLE s1.t1 ( c1 VARCHAR(32) );
INSERT INTO s1.t1 VALUES ( 'S1.T1' );
COMMIT;

CREATE TABLE s2.t1 ( c1 VARCHAR(32) );
INSERT INTO s2.t1 VALUES ( 'S2.T1' );
COMMIT;

최초로 접속할 때 사용자 u1의 schema path를 이용하여 t1 테이블을 해석하여 S1.T1 테이블을 조회한다.

% gsql u1 u1

gSQL> SELECT current_schema FROM dual;

CURRENT_SCHEMA
--------------
S1            

1 row selected.


gSQL> SELECT * FROM t1;

C1   
-----
S1.T1

1 row selected.

SET SCHEMA 구문을 사용한 후에 session의 schema 이름을 이용하여 t1 테이블을 해석하여 S2.T1 테이블을 조회한다.

gSQL> SET SCHEMA s2;

Session set.


gSQL> SELECT current_schema FROM dual;

CURRENT_SCHEMA
--------------
S2            

1 row selected.


gSQL> SELECT * FROM t1;

C1   
-----
S2.T1

1 row selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F761

Session management

O

SET SESSION AUTHORIZATION user_identifier

기능

Session user와 current user를 변경한다.

구문

<set session user identifier statement> ::=
    SET SESSION AUTHORIZATION user_identifier
    ;

사용 범위 및 접근 권한

<set session user identifier statement> 구문을 수행하려면 logon 사용자에게 ACCESS CONTROL ON DATABASE 권한이 있어야 한다.

사용자 정보는 다음과 같은 세가지 형태로 관리된다.

구문 규칙 및 파라미터

user_identifier

변경할 사용자의 이름이다.

설명

SET SESSION AUTHORIZATION 구문을 수행한 이후의 모든 구문은 session user를 기준으로 수행되므로, session user에 대한 권한을 검사하고 객체를 생성할 때의 소유자 역시 session user가 된다.

사용 예

다음은 ACCESS CONTROL ON DATABASE 권한을 가진 test 사용자가 session user를 u1 사용자로 변경한 예이다.

gSQL> SET SESSION AUTHORIZATION u1;

Session set.

gSQL> SELECT LOGON_USER(), SESSION_USER(), CURRENT_USER FROM dual;

LOGON_USER() SESSION_USER() CURRENT_USER
------------ -------------- ------------
TEST         U1             U1          

1 row selected.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F321

User authorization

O

SET SESSION CHARACTERISTICS AS transaction_mode

기능

세션의 트랜잭션 속성을 설정한다.

구문

<set session characteristics statement> ::=
    SET SESSION CHARACTERISTICS AS TRANSACTION <transaction_mode>
    ;

<transaction_mode> ::=
    { <transaction_access_mode> | ISOLATION LEVEL < isolation_level > }

<transaction_access_mode> ::=
    READ { ONLY | WRITE }

< isolation_level > ::=
    { READ COMMITTED | SERIALIZABLE }

구문 규칙 및 파라미터

<transaction_access_mode>

다음 트랜잭션의 ACCESS MODE 이다.

<isolation_level>

다음 트랜잭션의 ISOLATION LEVEL 이다.

SERIALIZABLE 은 cluster 환경에서는 지원되지 않는다.

설명

SET SESSION CHARACTERISTICS은 세션의 트랜잭션 속성을 설정한다. 즉, session 내에서 생성되는 모든 transaction의 속성이 이를 따른다.

참고로 SET TRANSACTION transaction_mode 구문의 경우, 이후에 수행되는 하나의 transaction 속성만 변경한다.

사용 예

다음은 session 내에서 생성될 모든 transaction을 READ ONLY로 설정하는 예이다.

gSQL> SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

Session set.

다음은 session 내에서 생성될 모든 transaction의 isolation level을 READ COMMITTED로 설정하는 예이다.

gSQL> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

Session set.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F761

Session management

O

참조

관련 내용은 SET TRANSACTION transaction_mode를 참조한다.

SET TIME ZONE

기능

세션의 TIMEZONE을 설정한다.

구문

<set local time zone statement> ::=
    SET TIME ZONE <set time zone value>
    ;

<set time zone value> ::= 
    { '[+|-]hh:mm' | LOCAL }

구문 규칙 및 파라미터

<set time zone value>

설정할 TIMEZONE 값이다.

설명

Session의 time zone을 변경하면 함수 CURRENT_TIME, CURRENT_TIMESTAMP 등의 결과값에 영향을 미친다.

사용 예

다음은 session의 time zone을 '+09:00' 으로 변경하는 예이다.

gSQL> SET TIME ZONE '+09:00';

Session set.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F411

Time zone specification

O

SET TRANSACTION transaction_mode

기능

다음 트랜잭션의 속성을 설정한다.

구문

<set transaction statement> ::=
    SET TRANSACTION <transaction_mode>
    ;

<transaction_mode> ::=
    { <transaction_access_mode> | ISOLATION LEVEL < isolation_level > }

<transaction_access_mode> ::=
    READ { ONLY | WRITE }

< isolation_level > ::=
    { READ COMMITTED | SERIALIZABLE }

구문 규칙 및 파라미터

<transaction_access_mode>

다음 트랜잭션의 ACCESS MODE 이다.

<isolation_level>

다음 트랜잭션의 ISOLATION LEVEL 이다.

SERIALIZABLE 은 cluster 환경에서는 지원되지 않는다.

설명

SET TRANSACTION은 다음 트랜잭션의 속성을 설정하며, 다음 트랜잭션이 종료되면 트랜잭션 속성은 기본값으로 복원된다.

사용 예

다음에 수행될 transaction을 읽기 전용으로 설정한 예이다.

gSQL> SET TRANSACTION READ ONLY;

Transaction set.

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

T251

SET TRANSACTION statement: LOCAL option

X

참조

관련 내용은 SET SESSION CHARACTERISTICS AS transaction_mode를 참조한다.

TRUNCATE TABLE

기능

테이블의 모든 row들을 제거한다.

구문

<truncate table statement> ::= 
    TRUNCATE TABLE table_name 
        [ RESTART IDENTITY | CONTINUE IDENTITY ] 
        [ DROP STORAGE | DROP ALL STORAGE ] 
    ;

사용 범위 및 접근 권한

<truncate table statement> 구문을 수행하려면 사용자에게 다음 권한 중 하나가 있어야 한다.

구문 규칙 및 파라미터

table_name

Row들을 제거할 대상 테이블의 이름이다.
schema_name.table_name과 같이 테이블이 속한 스키마를 정의할 수 있는데 schema_name을 생략할 경우 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.

[ RESTART IDENTITY | CONTINUE IDENTITY ]

[ DROP STORAGE | DROP ALL STORAGE ]

설명

TRUNCATE TABLE과 같은 Data Definition Language (DDL) 구문도 트랜잭션이 COMMIT 되기 전이라면 ROLLBACK 할 수 있다.

TRUNCATE TABLE 은 DELETE TRIGGER 를 실행하지 않는다.

Foreign key 가 참조하는 parent table 은 TRUNCATE 할 수 없다.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER CONSTRAINT child_fk REFERENCES parent(pk) );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> TRUNCATE TABLE parent;
ERR-42000(16042): unique/primary keys in table referenced by foreign keys

다음과 같이 child table 을 먼저 TRUNCATE 하거나, foreign key 를 제거하거나 NOT ENFORCED 로 변경해야 한다.

gSQL> TRUNCATE TABLE child;
Table truncated.

gSQL> TRUNCATE TABLE parent;
Table truncated.
gSQL> ALTER TABLE child ALTER CONSTRAINT child_fk NOT ENFORCED;
Table altered.

gSQL> TRUNCATE TABLE parent;
Table truncated.

사용 예

다음은 TRUNCATE TABLE 구문을 수행하는 예이다.

gSQL> TRUNCATE TABLE t1;

Table truncated.

다음은 TRUNCATE TABLE을 수행할 때 identity column 값을 재시작하는 예이다.

TRUNCATE TABLE t1 RESTART IDENTITY;

Table truncated.

호환성

SQL 표준에서는 [ DROP STORAGE | DROP ALL STORAGE ] 절을 정의하지 않고 있다.

SQL 표준 호환성

Feature ID

설명

지원 여부

F200

TRUNCATE TABLE statement

O

F202

TRUNCATE TABLE: identity column restart option

O

UPDATE

기능

테이블의 row들을 갱신한다.

구문

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
    ;

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<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 }

사용 범위 및 접근 권한

<update statement: searched> 구문을 수행하려면 사용자에게 다음 권한 중 하나가 있어야 한다.

구문 규칙 및 파라미터

table_name

Row를 갱신할 대상 테이블의 이름이다.
schema_name.table_name과 같이 테이블이 속한 스키마를 정의할 수 있는데 schema_name을 생략할 경우 구문을 수행하는 사용자의 기본 스키마 이름이 사용된다.

[ AS alias_name ]

table_name의 alias 이다.

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.

다음과 같은 방법으로 정의할 수 있다.

UPDATE table_name 
   SET column1 = value1, column2 = value2, column3 = value3
UPDATE table_name 
   SET ( column1, column2, column3 ) = ( value1, value2, value3 )
UPDATE table_name 
   SET column1 = ( SELECT max(value1) FROM other_table_name )

<query expression>은 row 하나를 생성하는 질의여야 한다.

Column 값으로 DEFAULT를 사용할 경우, CREATE TABLE을 수행할 때 정의한 기본값 (<default clause> 참조)을 사용하며, 정의되지 않은 경우에는 NULL 값이 할당된다.

WHERE <search condition>

WHERE 조건을 만족하는 row를 갱신한다.
WHERE 조건을 명시하지 않을 경우, 모든 row들을 갱신한다.
WHERE 조건에 대한 자세한 내용은 SELECT 구문의 where clause를 참조한다.

<result offset clause>

질의 결과 중 건너뛸 row의 개수를 명시한다.
자세한 내용은 SELECT 구문의 <result offset clause>를 참조한다.

<fetch limit clause>

Fetch 할 row의 개수를 명시하는 구문으로써 다음 두 가지 방법을 사용할 수 있다.

설명

UPDATE 관련 구문들의 차이점

사용 예

다음은 조건에 부합하는 다수의 row를 갱신하는 예이다.

gSQL> UPDATE lineitem
         SET l_shipdate = CURRENT_DATE
       WHERE l_returnflag = 'R';

5 rows updated.

다음은 여러 column의 값을 갱신하는 예이다.

gSQL> UPDATE lineitem
         SET l_shipdate   = CURRENT_DATE
           , l_returnflag = 'A'
       WHERE l_returnflag = 'R';

5 rows updated.

다음은 여러 column을 괄호로 묶어 갱신하는 예이다.

gSQL> UPDATE lineitem
         SET ( l_shipdate  , l_returnflag )
           = ( CURRENT_DATE, 'A' )
       WHERE l_returnflag = 'R';

5 rows updated.

다음은 subquery를 사용하여 column의 값을 갱신하는 예이다.

gSQL> UPDATE lineitem
         SET l_discount = ( SELECT MAX(l_discount) + 0.01 FROM lineitem )
       WHERE l_returnflag = 'R';

5 rows updated.

다음은 OFFSET과 FETCH 절을 사용하여 조건에 부합하는 row들 중 일부만 갱신하는 예이다.

gSQL> UPDATE lineitem
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
      OFFSET 3
      FETCH 2;

2 rows updated.

호환성

SQL 표준은 UPDATE 구문에서 다음 절을 정의하지 않고 있다.

SQL 표준 호환성

Feature ID

설명

지원 여부

F781

Self-referencing operations

X

T111

Updatable joins, unions, and columns

X

UPDATE name RETURNING

기능

테이블의 row들을 갱신하고, 갱신 전의 row들이나 갱신 후의 row들을 검색한다.

구문

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning clause>

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<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 } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] }

사용 범위 및 접근 권한

<update returning query statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

table_name

Row를 갱신할 대상 테이블의 이름이다.

[ AS alias_name ]

table_name의 alias 이다.

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.
자세한 내용은 UPDATE 구문을 참조한다.

WHERE <search condition>

WHERE 조건을 만족하는 row를 갱신한다.
WHERE 조건을 명시하지 않을 경우, 모든 row들을 갱신한다.
WHERE 조건에 대한 자세한 내용은 SELECT 구문의 where clause를 참조한다.

<result offset clause>

질의 결과 중 건너뛸 row의 개수를 명시한다.
자세한 내용은 SELECT 구문의 <result offset clause>를 참조한다.

<fetch limit clause>

Fetch 할 row의 개수를 명시하는 구문으로써 다음 두 가지 방법을 사용할 수 있다.

<returning clause>

갱신된 row들을 결과 집합으로 하고, 이들 중에서 검색할 column을 기술한다.

설명

자세한 내용은 UPDATE 관련 구문들의 차이점을 참조한다.

사용 예

다음은 RETURNING 절을 사용하여 갱신된 row들의 값을 얻는 예이다.

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
   RETURNING l_orderkey, l_linenumber, l_discount;

L_ORDERKEY L_LINENUMBER L_DISCOUNT
---------- ------------ ----------
         8            1        .07
         9            2        .11
        12            5        .05
        15            1        .03
        16            2        .08

5 rows updated.

다음은 RETURNING OLD 절을 사용하여 갱신된 row들의 갱신 전 값을 얻는 예이다.

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
   RETURNING OLD l_orderkey, l_linenumber, l_discount;

L_ORDERKEY L_LINENUMBER L_DISCOUNT
---------- ------------ ----------
         8            1        .06
         9            2         .1
        12            5        .04
        15            1        .02
        16            2        .07

5 rows updated.

호환성

SQL 표준에는 <update returning query statement> 구문이 존재하지 않는다.

UPDATE name RETURNING .. INTO

기능

테이블 row 한 개를 갱신하고, 갱신한 row의 값을 호스트 변수에 얻어온다.

구문

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning into clause>
    ;


<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<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 } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO variable_name [, ...]

사용 범위 및 접근 권한

<update returning query statement> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

table_name

Row를 갱신할 대상 테이블의 이름이다.

[ AS alias_name ]

table_name의 alias 이다.

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.
자세한 내용은 UPDATE 구문을 참조한다.

WHERE <search condition>

WHERE 조건을 만족하는 row를 갱신한다.
WHERE 조건을 명시하지 않을 경우, 모든 row들을 갱신한다.
WHERE 조건에 대한 자세한 내용은 SELECT 구문의 where clause를 참조한다.

<result offset clause>

질의 결과 중 건너뛸 row의 개수를 명시한다.
자세한 내용은 SELECT 구문의 <result offset clause>를 참조한다.

<fetch limit clause>

Fetch 할 row의 개수를 명시하는 구문으로써 다음 두 가지 방법을 사용할 수 있다.

RETURNING .. AS ..

갱신된 row들을 결과 집합으로 하고, 이들 중에서 검색할 column을 기술한다.
자세한 내용은 UPDATE name RETURNING 구문의 <returning clause>를 참조한다.

INTO variable_name [, ...]

INTO 절에 기술된 변수의 개수는 RETURNING 절에 기술된 expression의 개수와 동일해야 한다. 
갱신할 row가 한 건 이하여야 한다. 
Row가 두 건 이상 갱신될 경우, 에러가 발생한다.

설명

자세한 내용은 UPDATE 관련 구문들의 차이점을 참조한다.

사용 예

다음은 갱신한 row의 column 값을 host 변수에 얻어오는 예이다.

gSQL> \VAR v_discount NUMBER

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_orderkey = 12 AND l_linenumber = 5
   RETURNING l_discount INTO :v_discount;

V_DISCOUNT
----------
       .05

1 row updated.

호환성

SQL 표준에는 <update returning into statement> 구문이 존재하지 않는다.

UPDATE name WHERE CURRENT OF cursor_name

기능

커서가 가리키는 row 하나를 갱신한다.

구문

<update statement: positioned> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        WHERE CURRENT OF cursor_name
    ;

사용 범위 및 접근 권한

<update statement: positioned> 구문을 수행하려면 사용자가 다음 조건들을 만족해야 한다.

구문 규칙 및 파라미터

table_name

Row를 갱신할 대상 테이블의 이름이다.

[ AS alias_name ]

table_name의 alias 이다.

<set clause>

갱신할 column과 할당할 값을 정의하며, <set clause>의 column 개수와 값의 개수는 동일해야 한다.
자세한 내용은 UPDATE 구문을 참조한다.

cursor_name

cursor_name에 해당하는 커서는 다음 조건들을 만족해야 한다.

설명

자세한 내용은 UPDATE 관련 구문들의 차이점을 참조한다.

사용 예

다음은 interactive SQL (gsql)에서 cursor를 사용하여 <update statement: positioned> 구문을 수행하는 예이다.

gSQL> \VAR v_discount NUMBER
gSQL> DECLARE update_cursor CURSOR FOR 
        SELECT l_discount
          FROM lineitem
         WHERE l_orderkey = 8 AND l_linenumber = 1
           FOR UPDATE;

Cursor declared.
gSQL> OPEN update_cursor;

Cursor is open.
gSQL> FETCH update_cursor INTO :v_discount;

V_DISCOUNT
----------
       .06

1 row fetched.
gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01 
       WHERE CURRENT OF update_cursor;

1 row updated.
gSQL> CLOSE update_cursor;

Cursor closed.

gSQL> COMMIT;

Commit complete.

다음은 embedded SQL 프로그램에서 cursor를 사용하여 <update statement: positioned> 구문을 수행하는 예이다.

{
    ...
    EXEC SQL BEGIN DECLARE SECTION;
        ...    
        double v_discount;  
        ...   
    EXEC SQL END DECLARE SECTION;
    ...
    EXEC SQL DECLARE update_cursor CURSOR FOR
              SELECT l_discount
                FROM lineitem
               WHERE l_orderkey = 8 AND l_linenumber = 1
                 FOR UPDATE;
    ...
    EXEC SQL OPEN update_cursor;
    ...
    EXEC SQL FETCH NEXT update_cursor INTO :v_discount;
    ...
    EXEC SQL UPDATE lineitem 
                SET l_discount = l_discount + 0.01 
              WHERE CURRENT OF update_cursor;
    ...
    EXEC SQL CLOSE update_cursor;
    ...
    EXEC SQL COMMIT WORK;
    ...
}

호환성

SQL 표준 호환성

Feature ID

설명

지원 여부

F831

Full cursor update

O

B031

Basic dynamic SQL

O

참조

관련 내용은 CLOSE cursor_name을 참조한다.