1. 변수 제어 문장
변수를 제어하는 문장은 데이터 유형을 선언하는 문장, 변수를 선언하는 문장, 변수에 값을 할당하는 문장 등으로 구분된다.
1️⃣ 변수의 데이터 유형 선언
변수의 데이터 유형을 선언하는 문장에는 DECLARE TYPE 문이 있다.
DECLARE TYPE 문은 complied 복합 SQL 문 블록 안에서만 유효한 사용자 정의 데이터 유형을 선언한다.
이때 선언 가능한 사용자 정의 데이터 유형은 row 유형과 array 유형이 있다.
2️⃣ 변수의 선언
변수를 선언하는 문장에는 DECLARE 문이 있다.
DECLARE 문은 complied 또는 inlined 복합 SQL 문 블록 안에서만 유효한 지역변수를 선언할 때에 사용한다.
이때 선언 가능한 변수 유형은 scalar 변수, boolean 변수, row 변수, array 변수, cursor 변수 등이 있다.
3️⃣ 변수의 값 할당
변수에 값을 할당하는 문장에는 SET 문이 있다.
SET 문은 지역변수뿐만 아니라 전역변수, 매개변수, 전이변수에도 값을 할당할 수 있다.
complied 또는 inlined 복합 SQL 문 블록 안에서 사용 가능하고,
complied 블럭 안에서는 SELECT INTO 문과 VALUES INTO 문을 추가적으로 사용할 수 있다.
2. DECLARE TYPE 문
DECLARE TYPE 문은 complied 복합 SQL 문 블록 안에서만 유효한 distinct 유형, row 유형, array 유형을 선언한다.
해당 문으로 선언된 데이터 유형은 지역변수의 데이터 유형으로 사용될 수 있다.
DECLARE TYPE 유형명 AS { distinct 유형 | row 유형 | array 유형 }
형식은 CREATE TYPE 문과 유사하다.
1️⃣ distinct 유형 선언
DECLARE TYPE 유형명 AS { built-in 유형 | anchor-scalar 유형 }
WITH WEAK TYPE RULES [ CHECK (제약 조건) ]
distinct 유형의 이름을 지정하고, AS 다음에 built-in 유형 또는 scalar 유형의 anchor 유형을 지정하게 된다.
DECLARE TYPE VAR_X AS INT WITH WEAK TYPE RULES CHECK(VALUE <= 1000) @
위의 예문에서는 distinct 유형으로 선언하는 VAR_X 가 INT 유형으로 정의되었고,
제약조건으로써 1000 이하 값을 갖게 된다.
2️⃣ row 유형 선언
DECLARE TYPE 유형명 AS ROW (필드1 유형1, 필드2 유형1, ...)
DECLARE TYPE 유형명 AS ROW ANCHOR ROW 테이블
DECLARE TYPE 유형명 AS ROW ANCHOR row 변수
DECLARE TYPE 유형명 AS ROW ANCHOR ROW cursor 변수
유형1 { built-in 유형 | anchor 유형 | distinct 유형 }
row 유형은 각 필드를 별도로 작성하거나 anchor 유형을 활용하여 참조하는 방법으로 선언할 수 있다.
각 필드를 별도로 작성하는 경우에는 필드명, 데이터 유형, 데이터 유형 길이와 크기 등을 함께 지정해야 한다.
DECLARE TYPE VAR_X AS ROW (XNO INT, XNAME VARCHAR(10)) @
이때의 VAR_X 는 두 개의 필드로 구성된 row 유형이 된다.
다음은 테이블을 참조하는 방법으로 row 유형을 선언해보록 하자.
DECLARE TYPE VAR_X AS ROW ANCHOR TABLE_X
row 유형으로 VAR_X 을 선언할 때에 TABLE_X 를 참조하여 선언하도록 하였다.
다음은 전역변수를 이용하여 row 유형을 선언하는 방법이다.
CREATE VARIABLE VAR_X ANCHOR ROW OF TABLE_X
DECLARE TYPE VAR_Y AS ROW ANCHOR VAR_X
테이블 TABLE_X 를 참조하여 선언된 전역변수 VAR_X 를 이용하여,
VAR_Y 를 row 유형으로 선언하였다.
마지막으로 cursor 유형을 이용하여 row 유형을 선언해보자.
CREATE TYPE VAR_X AS ANCHOR ROW OF TABLE_X CURSOR @
DECLARE TYPE VAR_Y AS ROW ANCHOR ROW VAR_X
먼저 사용자 정의로 VAR_X 를 TABLE_X 참조한 cursor 유형으로 정의하여,
VAR_Y 이 VAR_X 를 참조하여 row 유형으로 선언되었다.
3️⃣ array 유형 선언하기
DECLARE TYPE 유형명 AS 유형1 ARRAY [크기]
DECLARE TYPE 유형명 AS 유형1 ARRAY [유형2]
유형1 { built-in | anchor | row }
유형2 { INT | VARCHAR(N) | anchor }
[ ] 에 정수를 작성하게 되면 ordinary array 유형으로, INT 또는 VARCHAR 등을 지정하면 associative array 유형으로 선언된다.
DECLARE TYPE VAR_X AS VARCHAR(10) ARRAY[3]
위의 예시는 VAR_X 이름의 array 유형을 ordinary array 유형으로 선언하였고, 배열의 원소는 VARCHAR(10) 유형으로 되어 있고 최대 3개의 원소를 가지게 된다.
DECLARE TYPE VAR_X AS VARCHAR(10) ARRAY[VARCHAR(20)]
만일 [ ] 에 3이 아닌 VARCHAR(20) 을 넣게 되면, 배열의 유형이 associative array 유형으로 선언되어 인덱스 자리를 VARCHAR(20) 유형이 차지하게 된다.
테이블에 대한 anchor 유형을 이용하여 배열 유형을 선언하는 경우에는 다음과 같이 작성할 수 있다.
DECLARE TYPE VAR_X AS ANCHOR ROW TABLE_X ARRAY [ ]
배열 유형은 VAR_X 이름으로 선언되어, 테이블 TABLE_X 를 참조하는 유형이 되었다.
이때 [ ] 에는 최대 원소 개수를 지정하지 않았다.
때문에 최대 원소 개수는 약 20 억개의 원소를 저장할 수 있게 된다.
다음은 row 유형을 이용하여 array 유형을 선언할 수 있다.
DECLARE TYPE VAR_X AS ROW (XNO INT, XNAME VARCHAR(10));
DECLARE TYPE VAR_Y AS VAR_X ARRAY[5];
먼저 row 유형의 VAR_X 를 선언하여 이를 활용하여 VAR_Y 를 선언한다.
이때의 VAR_Y 는 최대 5개의 원소를 저장할 수 있고, VAR_X 를 활용하였기 때문에 2개의 컬럼으로 구성되어 있다.
3. DECLARE 문
DECLARE 문은 complied, inlined 복합 SQL 문 블록 안에서만 유효한 지역변수(Local Variable) 를 선언한다.
DECLARE 지역변수1 [, 지역변수2, ...] 유형1
[ { DEFAULT | CONSTANT } { NULL | 상수 } ]
유형1 { built-in | anchor | distinct | row | array | cursor }
DECLARE 의 지역변수는 한 개의 복합 SQL 블록에서 식별자로 작용하기 떄문에 고유해야 한다.
또, SQL 프로시저와 SQL 함수 등의 SQL 루틴에서는 매개변수와 동일한 이름의 변수는 선언할 수 없다.
컬럼명과 동일한 이름의 변수를 선언할 수는 있지만, SQL 문에서 동일한 이름의 컬럼명과 변수명이 함께 지정되면 변수명은 컬럼명으로 해석되어 혼동이 발생할 수 있기 때문에 가급적 피하는 것이 좋다.
DEFAULT 키워드를 지정하지 않으면 초기값으로 NULL 이 할당되고, 기본값으로 NULL 또는 데이터 유형과 호환되는 상수를 지정할 수 있다.
다만 row 유형과 array 유형에서는 NULL 만 지정할 수 있다.
CONSTANT 키워드는 complied에서만 사용 가능하고 NULL 또는 해당 변수의 데이터 유형과 호환되는 상수를 지정할 수 있다.
이 키워드를 사용하게 되면 해당 변수는 읽기 전용 변수가 되고, row , array 유형에서는 사용할 수 없다는 특징이 있다.
4. 지역변수의 유형과 데이터 유형
| 구분 | scalar 지역변수 | boolean 지역변수 | row 지역변수 | array 지역변수 | cursor 지역변수 |
| built-in 유형 | 기본 유형 | BOOLEAN | - | - | CURSOR |
| 사용자 정의 데이터 유형 |
distinct | - | row 유형 | array 유형 | cursor 유형 |
| 선언된 데이터 유형 |
- | - | row 유형 | array 유형 | - |
| anchor 유형의 참조 대상 |
scalar 변수 테이블.컬럼 |
boolean | row 변수 ROW 테이블 ROW cursor 변수 |
array 변수 | cursor 변수 |
built-in 유형은 시스템에서 기본적으로 제공하는 데이터 유형으로 숫자, 문자, 날짜, CURSOR, BOOLEAN 유형 등이 있다.
사용자 정의 데이터 유형은 CREATE TYPE 문으로 정의한 데이터 유형으로 schema data type 이라고도 한다.
해당 유형에는 distinct, row, array, cursor 유형 등이 있다.
선언된 데이터 유형은 DECLARE TYPE 문에 의하여 선언된 데이터 유형으로 locally declared data type 이라고도 한다.
complied 에서만 지원되고 distinct, row, array 유형 등이 있다.
anchor 유형의 참조 대상으로는 CREATE TABLE 문으로 정의된 테이블과 컬럼, CREATE VARIABLE 문으로 정의된 전역변수, DECLARE 문으로 선언된 지역변수, SQL 루틴의 매개변수 등이 모두 지원된다.
1️⃣ scalar 지역 변수 선언
DECLARE scalar지역변수 built-in유형
DECLARE scalar지역변수 정의된distinct유형
DECLARE scalar지역변수 ANCHOR 테이블.컬럼
DECLARE scalar지역변수 ANCHOR scalar변수
scalar 지역변수는 총 네 가지 방법을 통해 선언할 수 있다.
먼저 built-in 유형을 이용하여 지역변수를 선언하는 예문을 만들어보자.
DECLARE VAL_A INT DEFAULT 10;
DECLARE VAL_B DOUBLE CONSTANT 3.14;
DECLARE VAL_C VARCHAR(10) DEFAULT 'TEST';
DECLARE VAL_D DATE DAFAULT '2022-12-07';
built-in 유형을 활용할 때에는 DEFAULT 값을 지정할 수도 있고, CONSTANT 키워드를 통해 읽기 전용 변수로 선언할 수도 있다.
정의된 distinct 유형을 활용하기 위해서는 distinct 유형으로 사용자 정의를 해야 한다.
CREATE TYPE SC_NAME.VAL_X AS INT WITH WEAK TYPE RULES CHECK(VALUE <= 1000) @
CREATE TYPE 문을 통해 먼저 사용자 정의 유형을 제약 조건과 함께 VAL_X 라는 이름으로 정의하였다.
DECLARE VAL_Y SC_NAME.VAL_X
앞서 정의된 distinct 유형인 VAL_X 을 스키마명.distinct유형명 형식으로 지정한다.
스키마명을 생략하게 되면 CURRENT_PATH 에 지정된 스키마명이 순차적으로 적용되면서 지정한 이름의 distinct 유형이 존재하는 확인하게 된다.
즉 SC_NAME 을 사용하지 않고 사용하게 되면 CURRENT_PATH 에 지정된 스키마명이 적용되고 해당 스키마명에 존재하는 VAL_X 의 존재를 확인하게 된다.
다음은 특정 테이블의 컬럼을 참조하여 scalar 변수를 선언하려는 경우이다.
DECLARE VAL_X ANCHOR TABLE_X.VAL_Y
anchor 유형은 ANCHOR 스키마명.테이블명.컬럼명 형식으로 지정한다.
스키마명은 위의 예시처럼 생략 가능하고, 생략 시에는 기본 스키마명이 지정된다.
위의 예시는 VAL_X 에 대해 테이블 TABLE_X 의 컬럼 VAL_Y 를 참조하여 해당 데이터 유형으로 scalar 유형을 정의한 것이다.
마지막으로 scalar 유형의 변수를 참조하여 scalar 변수를 선언하는 경우이다.
이때 참조 가능한 scalar 유형의 변수는 전역 변수, 지역 변수, 매개 변수 세 가지가 올 수 있다.
CREATE VARIABLE VAL_X VARCHAR(10) @
DECLARE VAL_Y ANCHOR VAL_X
위의 예문에서는 VAL_X 이라는 이름으로 scalar 유형의 전역 변수를 먼저 정의하였고,
VAL_X 를 참조하는 형태의 VAL_Y 라는 이름의 scalar 유형의 지역 변수를 선언하였다.
2️⃣boolean 변수 선언
DECLARE boolean변수 BOOLEAN
boolean 변수를 선언할 때에는 built-in 유형인 BOOLEAN 을 지정하는 것이 기본적인 형태이다.
DECLARE boolean변수 ANCHOR boolean변수
물론 BOOLEAN 대신 boolean 유형의 전역 변수, 지역 변수, 매개 변수에 대한 anchor 유형도 활용 가능하다.
DECLARE VAL_X BOOLEAN DEFALUT TURE;
DECLARE VAL_Y boolean constant false;
또한 boolean 변수는 DEFAULT 키워드를 통해 기본값을 지정할 수 있고,
CONSTANT 키워드를 통해 읽기 전용 변수로 선언할 수 있다.
3️⃣ row 변수 선언
DECLARE row변수 정의된 row유형
DECLARE row변수 선언된 row유형
DECLARE row변수 ANCHOR row변수
DECLARE row변수 ANCHOR ROW테이블
DECLARE row변수 ANCHOR ROW cursor변수
row 변수에 대한 기본값은 NULL 만 허용된다는 특징이 있다.
때문에 DEFAULT 키워드로 기본값을 지정하지 않아도 row 변수의 필드(row변수.필드명)는 NULL 로 초기화 된다.
DECLARE VAL_X ANCHOR ROW TABLE_X
row 유형의 지역 변수를 선언하는 가장 간단한 방법은 테이블에 대한 anchor 유형을 이용하는 것이다.
위의 예시는 VAL_X 라는 이름의 row 유형의 지역 변수를 테이블 TABLE_X 를 참조하여 선언한 것이다.
CREATE VARIABLE VAL_X ANCHOR ROW OF TABLE_X
DECLARE VAL_Y ANCHOR VAL_X
위의 예시는 VAL_X 라는 이름의 row 유형을 정의하고, VAL_Y 이름으로 참조하게 하였다.
DECLARE TYPE VAL_X AS ROW ANCHOR ROW TABLE_X
DECLARE VAL_Y VAL_X;
이번 예시는 compiled 에서 사용할 수 있는 선언 방식으로 DECLARE TYPE 문으로 선언된 VAL_X row 유형을 이용하여,
VAL_Y 지역 변수를 선언하였다.
DECLARE TYPE VAL_X AS ROW (XNO INT, XNAME VARCHAR(10))
DECLARE TYPE 문으로 선언하는 방식은 이외에도 위의 예시처럼 row 유형의 필드를 직접 정의하는 방식도 있다.
CREATE TYPE VAL_X AS ROW (XNO INT, XNAME VARCHAR(10)) @
DECLARE VAL_Y VAL_X
또는 CREATE TYPE 문으로 정의한 VAL_X row 유형을 이용하여 지역 변수 VAL_Y 를 선언할 수도 있다.
4️⃣ array 변수 선언
DECLARE array변수 정의된 array유형
DECLARE array변수 선언된 array유형
DECLARE array변수 ANCHOR array변수
array 변수에 대한 기본값은 row 변수와 마찬가지로 NULL 만 허용된다.
array 변수의 원소는 array변수명[n] 형식으로 사용할 수 있으며, 원소가 row 유형이면 array변수명[n].필드명 형식으로 참조할 수 있다.
CREATE TYPE VAL_X AS VARCHAR(10) ARRAY [3] @
DECLARE VAL_Y VAL_X
CREATE TYPE 문을 통해 사용자 정의한 VAL_X 을 만들고 이를 통해 VAL_Y 를 선언하였다.
CREATE TYPE VAL_X AS ANCHOR ROW TABLE_X ARRAY [ ] @
DELACRE VAL_Y VAL_X
또는 테이블 TABLE_X 에 대한 anchor 유형 을 이용하여 선언할 수도 있다.
위의 예시의 VAL_Y 는 앞서 정의한 배열의 괄호에 아무것도 쓰지 않았기 때문에 약 2억개의 원소를 저장할 수 있는 특징이 그대로 동일한 구조를 갖게 된다.
DECLARE TYPE VAL_X AS ROW (XNO INT, XNAME VARCHAR(10));
DECLARE TYPE VAL_Y AS VAL_X ARRAY[3];
DECLARE VAL_Z VAL_Y;
compiled 에서는 DECLARE TYPE 문으로 선언된 array 유형을 이용하여 array 변수를 선언할 수 있다.
위의 예문에서는 먼저 DECLARE TYPE 문을 통해 row 유형을 선언하였고,
그렇게 선언된 VAL_X의 필드를 유형으로 갖는 array 유형을 선언하였다.
이를 활용하여 VAL_Z 변수를 선언할 때에 VAL_Y 를 사용하였다.
이때의 VAL_Z 변수는 VAL_X 필드와 동일한 구조를 가지면서 VAL_Y 의 내용처럼 최대 3개의 원소를 갖게 된다.
다음은 anchor 유형의 전역변수를 이용하여 array 변수를 선언해보자.
CREATE TYPE VAL_X AS ANCHOR ROW TABLE_X ARRAY [3] @
CREATE VARIABLE VAL_Y VAL_X @
DECLARE VAL_Z ANCHOR VAL_Y
먼저 테이블을 참조하여 VAL_X 이름의 array 유형을 사용자 정의하였고,
VAL_X 를 이용하여 VAL_Y 를 전역 변수로 정의하였다.
그 후에 지역 변수 VAL_Z 가 VAL_Y 를 참조하여 정의되도록 하였다.이때의 VAL_Z 는 테이블 TABLE_X 의 레코드와 동일한 구조를 가지는 array 유형이 된다.
지금까지 살펴본 array 유형은 모두 ordinary array 유형의 지역 변수였다.
이번에는 associative array 유형의 지역 변수를 선언해보자.
CREATE TYPE VAL_X AS VARCHAR(30) ARRAY [VARCHAR(10)] @
DECLARE VAL_Y VAL_X
먼저 associative array 유형으로 VAL_X 를 사용자 정의하고,
지역 변수 VAL_Y 를 지정할 때에 VAL_X 를 사용하였다.
이때의 VAL_Y 는 associative array 유형을 활용하였기에 원소 개수의 제한이 없는 상태가 되고 index 는 VARCHAR(10) 인 상태가 된다.
5️⃣ cursor 변수 선언하기
DECLARE cursor변수 CURSOR [ 기본값옵션 ]
DECLARE cursor변수 정의된 cursor유형 [ 기본값옵션]
DECLARE cursor변수 ANCHOR cursor변수
cursor 변수로 선언 가능한 데이터 유형은 built-in 유형인 CURSOR, CREATE TYPE 문으로 정의된 cursor 유형, cursor 유형을 참조하는 anchor 유형 등을 지정할 수 있다.
{DEFAULT | CONSTANT }
(
CURSOR (커서매개변수1 유형1, 커서매개변수2 유형2, ...)
FOR { SELECT 문 | statement 변수}
)
유형1, 유형2, ...
{scalar 유형 | 정의된 distinct 유형 | ANCHOR 테이블, 컬럼 | ANCHOR scalar 변수}
CURSOR 에는 DEFAULT 키워드를 통하여 기본값으로 cursor value constructor 를 지정할 수 있다.
cursor value constructor 로 지정하게 되면 cursor 변수가 실행될 때에 지정된 입력값을 통하여 SELECT 문을 실행하게 된다.
만약 cursor 변수가 변경되면 cursor value constructor 는 무시된다.
CONSTANT 키워드를 통해서는 읽기 전용 변수로 선언할 수 있게 된다.
또한 cursor 변수가 strongly typed cursor 유형이거나, cursor value constructor 가 있으면 FOR 문 다음에는 SELECT 문만 허용된다.
📌 이 때의 SELECT 문에는 전역변수는 포함 가능하지만, 지역변수와 SQL 루틴 매개변수는 포함할 수 없다.
먼저 built-in 유형의 CURSOR 유형을 통하여 cursor 변수를 선언해보자.
DECLARE VAL_X CURSOR ;
built-in 유형의 CURSOR 유형은 weakly typed cursor 유형이기 때문에 임의의 SELECT 문에 사용 가능하다.
다음으로 사용자 정의된 cursor 유형을 이용하여 cursor 변수를 선언해보자.
CREATE TYPE VAL_X AS ANCHOR ROW OF TABLE_X CURSOR @
DECLARE VAL_Y VAL_X
VAL_X 를 정의할 때에 테이블 TABLE_X 를 참조하여 정의하였기 때문에 이때의 cursor 유형은 결과 집합 구조가 결정된 형태인 strongly typed cursor 유형으로 정의된 상태이다.
지역 변수 VAL_Y 는 이러한 VAL_X 특성을 갖게 된다.
다음에는 사용자 정의된 row 유형과 cursor 유형을 활용하여 cursor 변수를 선언해보자.
CREATE TYPE VAL_X AS ROW (XNO INT, XNAME VARCHAR(10)) @
CREATE TYPE VAL_Y AS VAL_X CURSOR @
DECLARE VAL_Z VAL_Y;
예문에서는 먼저 row 유형의 VAL_X 를 사용자 정의하였고,
이를 활용하여 cursor 유형의 VAL_Y 를 사용자 정의하였다.
이때의 VAL_Y 는 결과 집합이 row 유형을 가지고 있기 때문에 strongly typed cursor 유형이 된다.
다시 이를 지역 변수 VAL_Z 에 사용하여 동일한 형태를 갖추도록 하였다.
마지막으로 anchor 유형을 이용한 cursor 변수를 선언하는 방식을 살펴보자.
CREATE TYPE VAL_X AS ANCHOR ROW OF TABLE_X CURSOR @
CREATE VARIABLE VAL_Y VAL_X @
DECLARE VAL_Z ANCHOR VAL_Y
먼저 테이블 TABLE_X 을 이용하여 VAL_X 이라는 cursor 유형을 사용자 정의하였고,
이를 활용하여 전역변수 VAL_Y 를 정의하였다.
지역변수 VAL_Z 는 전역변수 VAL_Y 를 참조하도록 하였다.
이때의 지역변수는 TABLE_X 와 동일한 구조의 결과 집합을 갖게 되기 때문에 strongly typed cursor 유형이 된다.
5. SET 문
SET 문은 변수의 값을 변경하기 위하여 변수에 할당할 값을 지정할 때 사용한다.
SET 문으로 값을 할당하는 변수는 지역변수, 전역변수, 매개변수, 전이변수 등이 있다.
SET 변수 = 값
SET 레이블명.변수 = 값
| scalar 변수 | boolean 변수 | row 변수 | array 변수 | cursor 변수 |
| NULL 상수 특별레지스터 DEFAULT 변수 함수변환값 수식 SELECT 문 VALUES 문 |
NULL TRUE FALSE 조건식 변수 함수반환값 |
NULL 변수 함수반환값 SELECT 문 VALUES 문 |
NULL Array Construnctor ARRAY_AGG함수 변수 함수반환 |
Cursor Constructor 변수 함수반환값 |
변수와 함수반환값은 해당 변수의 유형과 동일해야 한다.
array 변수의 원소는 scalar 변수 또는 row 변수와 동일한 방법으로 값을 할당할 수 있다.
row 변수의 필드는 scalar 변수와 동일한 방법으로 값을 할당할 수 있다.
scalar 변수는 한 개의 SET 문으로 여러 개의 변수를 한꺼번에 값을 할당할 수 있다.
1️⃣ scalar 변수에 값 할당
SET scalar 변수 = 값1
SET scalar 변수1 = 값1, scalar 변수2 = 값2, ..
SET (scalar 변수1, 변수2, ...) = (값1, 값2, ...)
SET (scalar 변수1, 변수2, ...) = ({SELECT 문 | VALUES 문})
한 개의 SET 문으로 한 개의 scalar 변수에 값을 할당할 수 있고, 여러 개의 scalar 변수에 한꺼번에 값을 할당할 수도 있다.
sET 문에서 사용되는 scalar 변수에는 scalar 유형의 지역변수, 전역변수, 매개변수, 전이변수가 모두 포함된다.
또한 scalar 유형의 row 변수의 구성 필드와 scalar 유형의 array 변수의 원소 또는 원소의 구성 필드도 포함된다.
CREATE VARIABLE VAL_X INT DEFAULT 100 @
DECLARE VAL_Y INT DEFAULT 10;
scalar 변수를 선언할 때에 DEFAULT 키워드로 기본값을 지정한 경우에는 SET 문을 사용하지 않고도 변수에 값을 기본값을 할당할 수 있다.
CONSTANT 키워드를 활용하여 읽기 전용 변수로 선언할 수도 있는데 CONSTANT 를 사용한 변수에 SET 문을 사용하여 다른 값을 할당하려고 하면 오류가 발생한다.
scalar 변수에 숫자를 할당하는 방법을 살펴보자.
DECALRE VAL_X INT;
DECALRE VAL_Y DOUBLE;
DECALRE VAL_Z DEC(5,2);
SET VAL_X = 123.4567;
SET VAL_Y = 123.4567;
SET VAL_Z = 123.4567;
VAL_X 는 INT 유형으로 선언된 변수로 정수형으로 표현하기 위하여 소수점 이하가 제거된 123 이 해당 변수에 할당된다.
VAL_Y 는 DOUBLE 유형으로 선언된 변수로 부동소수점 실수형이기 때문에 소수점의 위치가 일정하게 저장되지 않고 1.234567E2 이런 식으로 할당된다.
VAL_Z 는 고정소수점 실수형으로 총 5개의 자리 중 소수점 2자리까지만 나타나도록 정의하였기 때문에 123.45 가 변수에 할당된다.
scalar 변수에 문자열를 할당하는 방법을 살펴보자.
DECLARE VAL_X CHAR(10);
DECLARE VAL_Y VARCHAR(10);
SET VAL_X = '가나다';
SET VAL_Y = '가나다';
문자 상수를 할당하는 경우에는 데이터의 유형에 따라 변수에 할당되는 내용이 달라질 수 있다.
CHAR 유형은 고정 길이 문자열이기 때문에 10 으로 정의한 내용을 따라 이보다 짧은 문자열을 할당하려는 경우 나머지 영역을 공백으로 채워 함께 저장하려고 한다.
즉 VAL_X 에는 '가나다' 가 저장된 것이 아니라 '가나다 ' 이 할당된다.
반면 VARCHAR 유형은 가변 길이 문자열이기 때문에 앞서 10 으로 정의하였더라고 하여도 공백을 함께 저장하지 않고, 할당한 문자열만큼만 저장한다.
다만 문자열을 비교할 때에는 뒤의 공백을 무시하기 때문에 VAL_X 와 VAL_Y 는 동일한 데이터를 가지고 있다고 판단된다.
만일 지정한 데이터 길이보다 더 긴 문자열을 할당하고자 할 때에는 지정한 길이만큼으로 잘려 저장되고 오류는 발생하지 않는다는 특징이 있다.
DECALRE VAL_X DATE;
DECALRE VAL_Y TIME;
DECALRE VAL_Z TIMESTAMP;
SET VAL_X = TO_DATE('20220101', 'yyyymmdd');
SET VAL_Y = TO_DATE('12:12:12', 'hh24:mi:ss');
SET VAL_Z = TO_DATE('20220101 12:12:12', 'yyyymmdd hh24:mi:ss');
scalar 변수의 날짜 상수를 할당하는 경우에는 유효한 값으로 해석될 수 있도록 TO_DATE 함수를 활용한다.
다만 유효범위가 아닌 날짜를 문자열로 지정할 경우에는 해석에 실패하여 오류가 발생할 수 있다.
SET CURRENT_SCHEMA = KES @
SET CURRENT_PATH = SYSTEM PATH, KES @
DECLARE VAL_X DATE;
DECLARE VAL_Y TIME;
DECLARE VAL_Z TIMESTAMP;
DECLARE VAL_A, VAL_B, VAL_C = VARCHAR(128);
SET VAL_X = CURRENT_DATE;
SET VAL_Y = CURRENT_TIME;
SET VAL_Z = CURRENT_TIMESTAMP;
SET VAL_A = CURRENT_USER;
SET VAL_B = CURRENT_SCHEMA;
SET VAL_C = CURRENT_PATH;
특별 레지스터는 시스템 환경과 연관된 정보를 자동으로 저장한 변수이다.
대부분의 특별 레지스터는 읽기 전용으로 제공되지만, CURRENT_SCHEMA, CURRENT_PATH 등 일부는 변경이 가능하다.
DECLARE VAL_X INT DEFAULT 123;
DECLARE VAL_Y VARCHAR(10);
SET VAL_X = NULL;
SET VAL_Y = NULL;
VAL_X 변수는 123 으로 초기화 되지만 이후 SET 문을 통하여 NULL 로 재할당 된다.
SET 문에서 변경하려는 scalar 변수가 전역변수라면 DEFAULT 키워드를 통하여 CREATE VARIABLE 문에서 지정한 기본값을 할당할 수 있다.
CREATE VARIABLE VAL_X INTEGER DEFAULT 100 @
SET VAL_X = 200;
SET VAL_X = DEFAULT;
첫번째 VAL_X 는 기본값 100 을 200 으로 재할당한 것이고,
두번째 VAL_X 는 재할당한 200 을 다시 기본값 100 으로 재할당한 것이다.
scalar 변수에는 다른 scalar 변수의 값을 할당할 수도 있다.
다음 예문을 살펴보자.
CREATE VARIABLE VAL_X INT DEFAULT 20 @
CREATE VARIABLE VAL_Y INT @
CREATE OR REPLACE PROCEDURE SP_X (IN P1 INT, OUT P2 INT)
BEGIN ATOMIC
DECLARE VAL_A INT;
DECLARE VAL_B INT DEFAULT 10;
SET VAL_A = VAL_B;
SET VAL_A = VAL_X;
SET VAL_Z = P1;
SET VAL_Y = VAL_X;
SET VAL_Y = P1;
SET VAL_Y = VAL_B;
SET P2 = P1;
SET P2 = VAL_B;
SET P2 = VAL_X;
CALL SP_X(30, ?) @
위의 예문을 실행하게 되면
VAL_X 는 재할당을 시도하지 않았기 때문에 정의할 때의 기본값 20 이 저장되어 있고,
VAL_Y 는 지역변수 VAL_B 를 재할당하였기 때문에 VAL_B 의 기본값 10 이 저장되어 있고,
VAL_A 는 매개변수 P1 을 재할당하였기 때문에 30 이 저장되어 있다.
row 변수는 여러 개의 scalar 변수를 묶어서 한 개의 변수로 관리하는 복합 변수이다.
때문에 row 변수를 scalar 변수에 직접 할당할 수는 없지만, 구성 필드에 저장되어 있는 값은 개별적으로 scalar 변수에 할당할 수 있다.
DECLARE VAL_A ANCHOR ROW TABLE_A;
DECLARE VAL_B CHAR(3);
DECLARE VAL_C VARCHAR(10);
DECLARE VAL_D INT;
SET VAL_A = (SELECT * FROM TABLE_A WHERE ANO = 1);
SET VAL_B = VAL_A.ANO;
SET VAL_C = VAL_A.ANAME;
SET VAL_D = VAL_A.APHONE;
( TABLE_A )
ANO 1
ANAME 에이
APHONE 123-1234-1234
먼저 지역변수 VAL_A 를 테이블 TABLE_A 를 참조하여 row 유형으로 정의하고,
SET 문을 통하여 SELECT 문의 결과를 할당하였다.
이에 따라 지역변수 VAL_A 에는 SELECT 문의 결과가 담겨있고 테이블의 컬럼명을 통하여 필드에 접근이 가능해진다.
array 변수는 scalar 변수 또는 row 변수 형태의 원소로 구성된 집합 변수이다.
row 변수와 마찬가지로 직접 할당을 할 수 없지만 원소 또는 원소의 구성 필드에 저장할 수 있다.
DECLARE TYPE VAL_X AS VARCHAR(10) ARRAY [3];
DECLARE VAL_Y VAL_X;
DECLARE VAL_A, VAL_B VARCHAR(10);
SET VAL_Y[1] = '가나다';
SET VAL_Y[2] = '라마바';
SET VAL_A = VAL_Y[1];
SET VAL_B = VAL_Y[2];
먼저 array 유형의 VAL_X 을 사용자 정의하였고,
이를 활용하여 지역변수 VAL_Y 를 선언하였다.
지역변수 VAL_Y 의 첫번째 원소에 '가나다' 문자열을, 두번째 원소에 '라마바' 문자열을 저장하였다.
이를 다시 지역변수 VAL_A, VAL_B 에 각각 할당하였다.
scalar 변수의 값으로 수식도 할당이 가능하다.
CREATE VARIABLE VAL_X INT DEFAULT 10 @
DECLARE VAL_Y INT;
SET VAL_Y = VAL_Y + 10;
SET VAL_Y = VAL_X + 10;
이때 지역변수 VAL_Y 는 정의할 때에 기본값을 지정하지 않았기 때문에 NULL 로 초기화되고,
이 상태로 연산을 시도하게 되면 NULL 로 출력된다.
NULL 로 출력하고 싶지 않을 때에는 NVL 등의 함수를 적절히 활용할 수 있다.
CREATE TYPE 문으로 정의된 distinct 유형은 할당할 수 있는 값에 대한 제한 조건을 지정할 수 있다.
CREATE TYPE VAL_X AS INT WITH WEAK TYPE RULES CHECK(VALUE <= 1000) @
DECLARE VAL_Y VAL_X;
SET VAL_Y = 10;
SET VAL_Y = NULL;
SET VAL_Y = 1200;
VAL_X 은 distinct 유형으로 선언되어 1000 이하라는 제한 조건을 가지고 있다.
이를 활용하여 지역변수 VAL_Y 은 데이터 유형으로 VAL_X 으로 지정하였고,
각각의 값을 SET 문을 통하여 할당할 때에 제한 조건에 따라
조건을 만족하는 경우에는 할당에 성공하게 되지만, 조건을 만족하지 않는 세번째 SET 문의 경우에는 오류를 일으키게 된다.
한 개의 SET 문으로 여러 개의 scalar 변수에 한꺼번에 값을 할당할 수 있다.
DECLARE VAL_X INT DEFAULT 0;
DECLARE VAL_Y CHAR(3) DEFAULT '';
DECLARE VAL_Z DATE;
SET (VAL_X, VAL_Y, VAL_Z) = (123, '가나다', CURRENT_DATE);
📌변수의 현재값
변수의 현재값이란 SET 문을 실행하기 직전의 변수에 저장된 값이다.
SET 문을 통해 여러 개의 변수를 할당하는 경우에는 변수의 현재값에 주의해야 한다.
DECLARE V1, V2 INT;
SET (V1, V2) = (10, 20);
SET V1 = V1 + 1;
SET V2 = V2 + V1;
SET (V1, V2) = (V1 + 1, V2 + V1);
개별적으로 각각의 변수에 값을 할당하는 경우에는 코드 작성 순서대로 차례로 실행이 되고 값이 적용되지만,
한번에 할당한 경우에는 괄호 안의 모든 실행문이 실행되고 난 후 적용되기 때문에 기대한 값과 다르게 적용될 수 있다.
즉 첫번째 경우에는 V1 은 11, V2 는 31 이 되고, 두번째 경우에는 V1 은 11, V2 는 30 이 되는 것이다.
scalar 변수에는 SELECT 문의 결과 집합이 한 건인 경우를 할당할 수도 있다.
DECLARE VAL_A INT;
DECLARE VAL_B, VAL_C VARCHAR(10);
SET (VAL_A, VAL_B, VAL_C) = (SELECT XNO, XNAME, XPHONE FROM TABLE_X WHERE XNO = 1);
만일 SELECT 문의 결과가 없는 경우에는 모든 변수에는 NULL 이 할당된다.
하지만 2건 이상이 검색되는 경우에는 오류가 발생하기 때문에 주의해야 한다.
2️⃣ boolean 변수에 값 할당
SET boolean 변수 = 값1
SET boolean 변수1 = 값1, boolean 변수2 = 값2, ...
SET (boolean 변수1, 변수2, ...) = (값1, 값2, ...)
DECLARE VAL_X BOOLEAN DEFAULT TRUE;
DECLARE VAL_Y BOOLEAN;
DECLARE VAL_Z BOOLEAN CONSTANT FALSE;
boolean 변수에는 TRUE, FALSE, NULL 중에 한 가지의 값이 할당된다.
SET 문에서는 boolean 변수에 할당하는 값으로 TRUE 키워드, FALSE 키워드, NULL 키워드, 조건식, boolean 변수 등을 지정할 수 있다.
전역변수를 선언할 때에도 DEFAULT 또는 CONSTANT 키워드를 지정할 수 있다.
하지만, 전역변수에 대한 anchor 유형으로 선언된 변수는 데이터 유형만 참조한다.
CREATE VARIABLE VAL_X BOOLEAN DEFAULT TRUE @
DECLARE VAL_Y ANCHOR VAL_X;
SET VAL_Y = FALSE;
위의 예시에서는 먼저 VAL_X 이름의 전역변수를 기본값 TRUE 로 선언하였고,
이를 지역변수 VAL_Y 에 anchor 유형으로 참조하게 하였다.
이때 VAL_Y 는 기본값은 TRUE 가 아닌 NULL 이다.
이는 boolean 유형이 anchor 유형으로 선언된 변수에 대해서는 데이터 유형만 참조하기 때문에
VAL_Y 는 VAL_X 의 데이터 유형인 boolean 유형만을 참조하게 된 것이다.
boolean 유형 또한 scalar 유형처럼 한꺼번에 값을 할당할 수 있다.
DECLARE V1, V2, V3 BOOLEAN;
SET (V1, V2) = (TRUE, FALSE);
SET V3 = (V1 = TRUE AND V2 = FALSE);
boolean 유형에는 boolean 유형의 값을 반환하는 조건식(search-condition) 에 대한 판별 결과값을 할당할 수 있다.
DECLARE V1 BOOLEAN;
DECLARE V2, V2 INT DEFAULT 10;
SET V1 = ((V2 > V3) OR (V3 IS NULL));
조건식은 SELECT 문의 WHERE 절의 조건식과 유사한 형식으로 표현하며 조건식이 성립하면 TRUE, 성립하지 않으면 FALSE 를 반환한다.
만약 조건식 성립 여부를 알 수 없는 경우에는 NULL 을 반환한다.
boolean 변수에는 boolean 유형의 지역변수, 전역변수, 매개변수를 지정하거나 boolean 유형을 반환하는 함수를 지정할 수도 있다.
CREATE OR REPLACE VARIABLE VAL_X BOOLEAN DEFAULT TRUE @
CREATE PROCEDURE SP(IN P1 BOOLEAN, OUT P2 BOOLEAN)
BEGIN ATOMIC
DECLARE V1 BOOLEAN;
DECLARE V2 BOOLEAN DEFAULT TRUE;
SET V1 = V2;
SET V2 = P1;
SET VAL_X = V2;
SET P2 = VAL_X;
END @
CALL SP(TRUE, ?) @
예문을 살펴보면 먼저 매개변수 P1 은 TRUE 값을 가지고 V2 에 할당한다.
따라서 V2 의 값은 TRUE 가 되고 다시 이 값을 VAL_X 에 할당하고 있기 때문에 VAL_X 또한 TRUE 가 된다.
VAL_X 값을 다시 P2 에 할당하였기 때문에 프로시저가 종료되면 TRUE, 즉 1이 반환된다.
📌 BOOLEAN 변수의 값 표시
TRUE 는 1, FALSE 는 0, NULL 은 - 로 표시된다.
3️⃣ row 변수에 값 할당
row 변수에 값 할당하는 방법은 크게 row 단위로 지정하는 방법과 row 변수를 구성하는 필드 단위로 지정하는 방법으로 구분할 수 있다.
먼저 row 단위로 지정할 때를 살펴보자.
SET row 변수 = (값1, 값2, ...)
SET row 변수 = {SELECT문 | VALUSE문}
SET row 변수 = {NULL | 변수1}
값1 {NULL | 상수 | 특별레지스터 | ROW변수(전역, 지역, 매개, 전이) | 함수 SCALAR 반환값 | 수식}
변수1 {ROW변수(전역, 지역, 매개) | 함수 ROW 반환값}
row 변수를 구성하는 필드 단위로 지정할 때에는 다음과 같다.
SET row 변수.필드 = 값1
SET (row 변수.필드1, row 변수.필드2,...) = (값1, 값2, ...)
row 변수에 필드별로 값을 나열하여 지정하는 방법을 살펴보자.
DECLARE TYPE VAL_X AS ROW (XNO INT, XNAME VARCHAR(10));
DECLARE VAL_Y VAL_X;
DECLARE V_NO INT DEFAULT 20;
DECLARE V_NAME VACHAR(10) DEFAULT '가나다';
SET VAL_Y = (V_NO, V_NAME);
먼저 2개의 필드로 구성된 사용자 정의 row 유형 VAL_X 을 정의하였고, 이를 데이터 유형으로 하는 VAL_Y 을 선언하였다.
VAL_Y 에는 2개의 필드에 대하여 해당 데이터 유형에 맞는 데이터를 SET 문을 통하여 할당할 수 있다.
row 변수에 결과 집합을 지정할 수도 있다.
DECLARE V1 ANCHOR ROW TABLE_X;
SET V1 = (SELECT XNO, XNAME FROM TABLE_X WHERE XNO = 1);
결과 집합을 이용할 때의 주의점은 SELECT 문의 결과가 1건 이하여야 한다는 것이다.
결과가 0건인 경우에는 각 필드에는 NULL 이 할당되고, 결과가 1건인 경우에는 각 필드에 SELECT 결과 데이터가 할당된다.
하지만 결과가 2건 이상인 경우에는 오류가 발생하니 조심해야 한다.
row 변수에 row 유형의 변수 또는 row 유형을 반환하는 함수를 지정할 수도 있다.
DECLARE V1 ANCHOR ROW TABLE_X;
DECLARE V2 ANCHOR ROW TABLE_X;
SET V2 = (SELECT XNO, XNAME FROM TABLE_X WHERE XNO = 1);
SET V1 = V2;
위의 예시에서 지역변수 모두 테이블 TABLE_X 를 참조하여 row 변수를 정의하였다.
그 다음 v2 에는 SELECT 문의 결과 집합을 할당하였고, V1 는 V2 를 할당하도록 하였다.
row 변수에는 동일한 유형의 row 변수만 할당할 수 있다.
여기서 말하는 동일한 유형이란 동일한 테이블에 대한 anchor row 유형 또는 동일한 사용자 정의 row 유형임을 의미한다.
row 변수의 구성 필드명과 필드별 데이터 유형이 동일한 것은 동일한 유형이라고 하지 않는다.
DECLARE TYPE ROW_1 AS ROW (NO INT, NAME VARCHAR(10));
DECLARE TYPE ROW_2 AS ROW (NO INT, NAME VARCHAR(10));
DECLARE VAL_X ROW_1;
DECLARE VAL_Y ROW_2;
DECLARE VAL_Z ROW_2;
SET V1 = (1, '가');
SET V2 = V1;
위의 코드를 실행시키면 오류가 발생하게 될 것이다.
그 이유는 ROW_1 과 ROW_2 가 동일한 유형이 아니기 때문이다.
row 유형의 동일한 유형이란 구조적 동일함이 아닌 기인하는 테이블, row 유형이 동일한지의 여부를 따지는 것이다.
즉 v1 과 v2 는 각각 ROW_1 과 ROW_2 를 데이터 유형으로 활용하고 있기 때문에 동일한 유형이라고 다르다고 판단되어,
SET V2 = V1;에서 실패하게 되는 것이다.
반면 SET V2 = (1, '가'); SET V3 = V2; 으로 바꿔 작성하게 되면 이들은 ROW_2 라는 데이터 유형을 공통으로 사용하고 있기에
동일한 유형으로 판단되어 정상적으로 작동하게 된다.
4️⃣ array 변수에 값 할당
SET 문으로 array 변수에 값을 할당하는 방법은 첫번째 개별 원소별 값을 할당하는 형식,
SET array 변수[인덱스] = 값
DECLARE TYPE ARR AS INT ARRAY[10];
DECLARE VAL_X ARR;
SET VAL_X[1] = 100;
DECLARE TYPE ARR AS ANCHOR ROW TABLE_X ARRAY [5];
DECLARE VAL_X ARR;
SET VAL_X[1].XNO = 1;
DECLARE TYPE ROW_X AS ROW (NO INT, CITY VARCHAR(10));
DECLARE TYPE ARR AS ROW_X ARRAY [VARCHAR(10)];
DECLARE VAL_X ARR;
SET VAL_X['한국'] = (82, '서울');
두번째 array contructor 또는 ARRAY_AGG 함수를 이용한 SELECT 문을 지정하는 형식,
SET array 변수 = ARRAY [값1, 값2,..]
SET array 변수 = ARRAY [{SELECT 문 | VALUES 문}]
SET array 변수 = SELECCT ARRAY_AGG(컬럼1, 컬럼2,...) FROM 테이블 ...
DECLARE TYPE ARR AS VARCHAR(20) ARRAY [5];
DECLARE VAL_X ARR;
SET VAL_X = ARRAY['가', '나', '다', '라'];
DECLARE TYPE ARR AS VARCHAR(10) ARRAY [5];
DECLARE VAL_X ARR;
SET VAL_X = ARRAY[VALUES('가'), (NULL), ('다')];
SET VAL_X = ARRAY[SELECT XNO, XNAME FROM TABLE_X WHERE XNO < 3];
DECLARE TYPE ARR AS VARCHAR(10) ARRAY[5];
DECLARE VAL_X ARR;
SET VAL_X = (SELECT ARRAY_AGG((XNAME) ORDER BY XNAME) FROM TABLE_X WHERE XNO < 3);
세번째 array 유형의 변수를 지정하거나 array 유형을 반환하는 함수를 지정하는 형식이 있다.
SET array 변수 = {array 변수(전역, 지역, 매개) | 함수 array 반환값}
CREATE TYPE VAL_X AS ANCHOR ROW TABLE_X ARRAY [5];
DECLARE VAL_A VAL_X;
DECLARE VAL_B VAL_X;
SET VAL_A (SELECT ARRAY_AGG((XNO, XNAME) ORDER BY XNO) FROM TABLE_X WHERE XNO <3);
SET VAL_B = VAL_A;
마지막으로 함수가 반환하는 array 원소를 array 변수에 할당하는 경우를 살펴보자.
CREATE FUNCTION FN(P1 INT)
RETURNS ARR
BEGIN
DECLARE P2 ARR;
SET P2 = (SELECT ARRAY_AGG((XNO, XNAME) ORDER BY XNO) FROM TABLE_X WHERE XNO < P1); RETURN P2;
END @
SET VAL_X = FN(1200);
예문의 SET을 실행하게 되면 지역변수 p2 에는 SELECT 문의 결과 집합이 할당된다.
할당된 데이터 타입은 ARRAY_AGG 에 의하여 ARRAY 유형이 된다.
이를 그대로 RETURN 하고 있기 때문에 FUNCTION FN 을 실행하게 되면 반환값으로 ARRAY 유형의 데이터를 받게 되는 것이다.
5️⃣ cursor 변수에 값 할당
cursor 변수가 weakly typed cursor 유형이라면 임의의 결과 집합을 할당하는 것이 가능하지만,
결과 집합의 구조가 정해져 있는 strongly typed cursor 유형이라면 정의된 구조와 정확히 일치하는 결과 집합만 할당할 수 있다.
첫번째 형식은 cursor value constructor 를 지정하는 방식이다.
SET cursor 변수 =
CURSOR [ (커서매개변수1 유형1, 커서매개변수2 유형2, ...)]
FOR {SELECT 문 | VALUES 문 | statement 변수}
유형
{built-in 유형 | 정의된 distinc 유형 | ANCHOR 테이블, 컬럼 | ANCHOR scalar 변수(전역, 지역, 매개)}
이 형식의 cursor 변수가 strongly typed cursor 인 경우에는 statement 변수가 허용되지 않고,
커서매개변수가 있는 경우에는 statement 변수를 비롯한 지역변수, 매개변수는 허용되지 않는다.
두번째 형식은 cursor 유형의 변수를 지정하거나 cursor 유형을 반환하는 함수를 지정한다.
SET cursor 변수 = {cursor 변수(전역, 지역, 매개) | 함수 cursor 반환값}
먼저 cursor 변수에 커서매개변수가 없는 cursor value constructor 를 지정해보자.
DECLARE V1 INT;
DECLARE V2 VARCHAR(20);
DECLARE V3 INT;
DECLARE C1 CURSOR;
SET C1 = CURSOR FOR VALUES(1, '가', 100), (2, '나', 98);
OPEN C1;
FETCH C1 INTO V1, V2, V3;
FETCH C1 INTO V1, V2, V3;
CLOSE C1;
위의 예문에서는 VALUES 문을 활용하여 결과 집합의 구조가 정해져 있지 않은 weakly typed cursor 유형으로 할당하였다.
VALUES 문이 아닌 SELECT 문을 활용하면 다음과 같이 작성할 수 있다.
DECLARE V1 ANCHOR ROW TABLE_X;
DECLARE V2 ANCHOR ROW TABLE_Y;
DECLARE C1 CURSOR;
SET C1 = CURSOR FOR SELECT * FROM TABLE_X WHERE XNO < 3;
OPEN C1; FETCH C1 INTO V1; CLOSE C1;
SET C1 = CURSOR FOR SELECT * FROM TABLE_Y WHERE YNO < 4;
OPEN C1; FETCH C1 INTO V2; CLOSE C1;
위의 예문은 SELECT 문을 활용하여 weakly typed cursor 유형을 변수에 할당하였다.
지역변수 v1와 v2 는 row 유형으로 선언했기 때문에 참조한 테이블의 컬럼명으로 각 요소에 접근이 가능하다.
이번에는 weakly typed cursor 유형이 아닌 strongly typed cursor 유형을 알아보자.
strongly typed cursor 유형은 CREATE TYPE 문을 통해 결과 집합의 구조가 정해 놓으면 된다.
CREATE TYPE CUR AS ANCHOR ROW TABLE_X CURSOR @
DECLARE V1 ANCHOR ROW TABLE_X;
DECLARE C1 CUR;
SET C1 = CURSOR FOR SELECT * FROM TABLE_X WHERE XNO < 3;
OPEN C1; FETCH C1 INTO V1; CLOSE C1;
먼저 CREATE TYPE 문을 통해 테이블 TABLE_X 을 참조하는 CURSOR 유형을 만들었다.
지역변수 C1 을 이 CURSOR 유형으로 정의하였고, 이를 활용하여 C1 에 값을 할당하였다.
이때의 C1 과 TABLE_X 와의 구조가 동일하기 떄문에 정상적으로 실행이 된다.
결과 집합의 구조가 일치하지 않는 경우에는 오류가 발생하기 때문에 주의해야 한다.
SET 문에서 지정된 cursor value constructor 에 커서매개변수가 없는 경우에는 결과 집합을 생성하는 sql 문에서 지역변수, 전역변수, 매개변수 등을 사용할 수 있다.
CREATE TYPE CUR ANCHOR ROW TABLE_X CURSOR @
DECLARE V1 ANCHOR ROW TABLE_X;
DECLARE V2 VARCHAR(10);
DECLARE C1 CURSOR;
DECLARE C2 CUR;
SET V2 = 'A';
SET C1 = CURSOR FOR SELECT * FROM TABLE_X WHERE XNAME = V2;
SET C2 = CURSOR FOR SELECT * FROM TABLE_X WHERE XNO > 3;
지역변수 C1 은 결과 집합이 정해져 있지 않은 경우의 CURSOR 유형으로 정의되었기 때문에 weakly typed cursor 유형이며,
지역변수 V2 를 활용하여 레코드를 할당받았다.
이번에는 CURSOR 변수에 커서매개변수가 있는 경우를 살펴보자.
DECLARE V1 ANCHOR ROW TABLE_X;
DECLARE C1 CURSOR;
SET C1 = CURSOR (CP1 VARCHAR(2), CP2 INT) FOR SELECT * FROM TABLE_X WHERE XNO < CP2 AND XNAME = CP1 ORDER BY XNO;
OPEN C1 USING 'A', 100; FETCH C1 INTO V1; CLOSE C1;
OPEN 문에 두 개의 커서매개변수를 넘겨 SET 문에서 활용 가능하도록 하였다.
각각의 데이터가 CP1 과 CP2 가 되어 SELECT 문이 실행되는 것이다.
이렇게 cursor value constructor 에 커서매개변수가 있으면, 결과 집합을 생성하는 SQL 문에는 지역변수 또는 매개변수를 지정할 수 없다는 특징이 있다.
대신 전역변수는 사용 가능하다.
CREATE VARIABLE S1 INT DEFAULT 10 @;
DECLARE V1 ANCHOR ROW TABLE_X;
DECLARE V2 INT;
DECLARE C1 CURSOR;
SET C1 = CURSOR (CP1 VARCHAR(2), CP2 INT) FOR SELECT * FROM TABLE_X WHERE XNO > CP2 AND XNAME = CP1 AND XPHONE = S1;
SET V2; OPEN C1 USING 'A', V2; FETCH C1 INTO V1; CLOSE C1;
다음으로는 cursor 변수에 cursor 유형의 변수 또는 cursor 유형을 반환하는 함수를 지정해보자.
CREATE TYPE CUR ANCHOR ROW TABLE_X CURSOR @;
DECLARE C1 CURSOR;
DECLARE C2 CUR;
DECLARE V1, V2 ANCHOR ROW TABLE_X;
SET C2 = CURSOR FOR SELECT * FROM TABLE_X WHERE XNO < 3;
SET C1 = C2;
이때 weakly typed cursor 유형에는 동일한 유형 또는 strongly typed cursor 유형 또한 할당 가능하지만,
strongly typed cursor 유형에는 동일한 구조를 가지는 strongly typed cursor 유형이 아니면 오류를 일으키기 때문에 주의해야 한다.
'SQL > DB2' 카테고리의 다른 글
| DB2 흐름 제어 : 비교 제어 IF , CASE 문 (0) | 2022.12.19 |
|---|---|
| DB2 VALUES INTO 문 (0) | 2022.12.19 |
| DB2 SELECT INTO 문 (0) | 2022.12.08 |
| DB2 CREATE VARIABLE : 전역변수 선언 (0) | 2022.12.07 |
| DB2 CREATE TYPE : 사용자 정의 데이터 유형 정의 (0) | 2022.12.07 |