문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣ7FS02PS
문서번호ㅣTADTI095
개요
바인드 쿼리, 리터럴 쿼리에서 조건절 컬럼과 다른 타입의 값이 대입될 경우의 주의사항을, 묵시적으로 일어나는 형변환에 대한 예제를 통해 안내합니다.
방법
묵시적 형변환
묵시적 형변환은 아래와 같은 경우 발생할 수 있습니다.
1. 컬럼에 다른 타입의 데이터를 INSERT, UPDATE하거나 변수에 타입이 다른 Data를 대입하는 경우
2. 조건문에서 비교하는 양쪽 값이 다른 타입인 경우
<예시>
SQL> CREATE TABLE emp (id NUMBER, current_credits NUMBER(3));
SQL> INSERT INTO emp VALUES (1004, 2);
...
SQL> DECLARE
cur_cred VARCHAR2(5);
BEGIN
SELECT current_credits INTO cur_cred
FROM emp WHERE id = 1004;
END;
위의 예시는 emp 테이블에서 ID가 1004번인 직원의 현재 신용 번호를 조회하는 SELECT 문입니다.
컬럼인 current_credits 의 데이터 타입은 NUMBER(3)인 반면, cur_cred는 VARCHAR2(5) 일 경우
tbPSM은 NUMBER 타입의 데이터를 자동으로 VARCHAR2 타입으로 변환하여 INTO 절에 할당된 cur_cred에 저장하게 됩니다.
주의사항
타입이 다른 데이터를 대입하거나 비교할 때 아래와 같은 상황이 발생할 수 있으므로 대상에 맞춰 타입을 일치시켜서 처리할 것을 권장합니다.
묵시적 형변환 사용시 주의사항
VARCHAR2 타입이 DATE 타입으로 변환되는 경우, 실제로 VARCHAR2 타입의 변수가 DATE 형식으로 되어 있지 않으면 예외 상황이 발생할수 있습니다.
LONG 타입의 변수가 NUMBER 타입으로 변환되는 경우, 실제로 LONG 타입의 변수가 NUMBER 형식으로 되어 있지 않으면 예외 상황이 발생할 수 있습니다.
<예시>
SQL> DECLARE
a VARCHAR2(20) := '2025/04/30';
b VARCHAR2(20) := 'Tibero';
c VARCHAR2(20) := '1';
d DATE;
e NUMBER;
BEGIN
d := a; -- 변환에 성공
d := b; -- 예외 상황이 발생
e := b; -- 예외 상황이 발생
e := c; -- 변환에 성공
END;
d 의 경우 DATE 로 TYPE 을 받아
a 와 매칭시 a 의 데이터가 DATE 형식으로 되어 있어, 묵시적 변환이 되고,
b 와 매칭시 b 의 데이터가 DATE 형식으로 되어있지 않아, 묵시적 변환이 되지 않습니다.
e 의 경우 NUMBER 로 TYPE 을 받아
b 와 매칭시 b 의 데이터가 NUMBER 형식으로 되어있지 않아, 묵시적 변환이 되지 않고,
c 와 매칭시 c 의 데이터가 NUMBER 형식으로 되어 있어, 묵시적 변환이 됩니다.
<예시>
SQL> CREATE TABLE emp (id NUMBER);
SQL> INSERT INTO emp VALUES (1);
SQL> commit;
SQL> select * from emp where id = 1;
ID
----------
1
1 row selected.
SQL> select * from emp where id = 'a';
TBR-5074: Given string does not represent a number in proper format.
NUMBER 타입의 컬럼에 숫자(1)가 아닌 문자열('a') 를 대입시 오류가 발생함을 확인할 수 있습니다.
바인드 변수 동일 플랜 주의사항
바인드 변수에 대입되는 타입에 따라 묵시적 형변환으로 인해 결과값은 같을 수 있으나, 내부 플랜 및 읽히는 블락 수가 달라질 수 있습니다.
플랜/블락 수 변경될 경우 주의사항
<예시-플랜/블락 수 변경될 경우>
SQL> drop table t;
SQL> create table t(c1 varchar2(10));
SQL> insert into t
select '2025'||(lpad(trunc(dbms_random.value(1,12)), 2,'0'))||(lpad(trunc(dbms_random.value(1,30)), 2,'0')) c1
from dual
connect by level < 10000;
SQL> commit;
create index idx1_t on t(c1);
A. VARCHAR2 지정, 바인드 변수 쿼리일 경우
SQL> select /*+ varchar2 - bind */ *
from t
where c1 < :c_v1;
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:10, %%CPU:0, Rows:3164)
2 INDEX (RANGE SCAN): IDX1_T (Cost:10, %%CPU:0, Rows:3164)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T"."C1" < :C_V1) (0.249)
B. VARCHAR2 지정, 리터럴 쿼리일 경우
SQL> select /*+ varchar2 - literal */ *
from t
where c1 < '20250301';
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:10, %%CPU:0, Rows:3164)
2 INDEX (RANGE SCAN): IDX1_T (Cost:10, %%CPU:0, Rows:3164)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T"."C1" < '20250301') (0.249)
C. NUMBER 지정, 바인드 변수 쿼리일 경우
SQL> select /*+ number - bind */ *
from t
where c1 < :n_v1;
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:4, %%CPU:0, Rows:1270)
2 INDEX (RANGE SCAN): IDX1_T (Cost:4, %%CPU:0, Rows:1270)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T"."C1" < :N_V1) (0.100)
D. NUMBER 지정, 리터럴 쿼리일 경우
SQL> select /*+ number - literal */ *
from t
where c1 < 20250301;
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): T (Cost:13, %%CPU:0, Rows:1270)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
1 - filter: ("T"."C1" < 20250301) (0.100)
결과값 다를 경우 주의사항
바인드 변수에 대입되는 타입에 따라 묵시적 형변환으로 인해 결과값이 달라지는 경우가 발생할 수 있습니다.
<예시-결과값 이 다를 경우>
SQL> drop table t2;
SQL> create table t2(c1 varchar(10));
SQL> insert into t2 values ('9');
SQL> insert into t2 values ('99');
SQL> insert into t2 values ('100');
SQL> insert into t2 values ('101');
SQL> commit;
SQL> select * from t2 where c1 > 9;
C1
----------
99
100
101
3 rows selected.
SQL> select * from t2 where c1 > '9';
C1
----------
99
1 row selected.
조건절에 대입되는 타입에 따라 결과값이 달라질 수 있습니다.
참고
원본, 대상으로 구분한 묵시적 변환이 허용되는 타입을 아래 표에서 확인할 수 있습니다.
[묵시적 변환 허용되는 타입 정리표]
- 열 : 원본 타입 / 행 : 대상 타입
BIN_INT | PLS_INT | NUMBER | CHAR | VARCHAR2 | LONG | DATE | RAW | CLOB | BLOB | ROWID | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| BIN_INT | O | O | O | O | O | O | |||||
| PLS_INT | O | O | O | O | O | O | |||||
| NUMBER | O | O | O | O | O | O | |||||
| CHAR | O | O | O | O | O | O | O | O | O | O | |
| VARCHAR2 | O | O | O | O | O | O | O | O | O | O | |
| LONG | O | O | O | O | O | O | O | O | O | O | |
| DATE | O | O | O | ||||||||
| RAW | O | O | O | O | |||||||
| CLOB | O | O | O | ||||||||
| BLOB | O | ||||||||||
| ROWID | O | O | O |