변수 사용

개요

SQL*Plus에서 쿼리에 치환 변수를 사용하면 다수의 커서가 생성될 수 있습니다.

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER);

 

치환 변수

아래 내용으로 t1.sql 파일을 생성하겠습니다. 쿼리에 &1 치환 변수(substitution variable)를 사용합니다. &1 치환 변수는 SQL 파일에 사용한 첫 번째 매개변수로 치환됩니다.

-- 2
SELECT /* TEST1 */ * FROM t1 WHERE c1 = &1;

 

아래와 같이 t1.sql 파일을 실행하겠습니다.

-- 3
@t1 1
@t1 2

 

아래는 V$SQL 뷰를 조회한 결과입니다. 치환 변수가 매개변수로 치환되어 2개의 커서가 생성된 것을 확인할 수 있습니다. 쿼리에 치환 변수를 사용하면 커서가 치환 변수의 개수만큼 생성됩니다.

-- 4
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* TEST1 */%';

SQL_ID        SQL_TEXT
------------- -----------------------------------------
06v0w58gh5m5w SELECT /* TEST1 */ * FROM t1 WHERE c1 = 1
faf4ynhda2srr SELECT /* TEST1 */ * FROM t1 WHERE c1 = 2

2 행이 선택되었습니다.

 

바인드 변수

아래 내용으로 t2.sql 파일을 생성하겠습니다. 바인드 변수(bind variable)에 치환 변수를 할당하고 쿼리에 바인드 변수를 사용합니다.

-- 5
VAR b1 NUMBER
EXEC :b1 := &1

SELECT /* TEST2 */ * FROM t1 WHERE c1 = :b1;

 

아래와 같이 t2.sql 파일을 실행하겠습니다.

-- 6
@t2 1
@t2 2

 

아래는 V$SQL 뷰를 조회한 결과입니다. 쿼리의 커서는 1개만 생성되었지만, 바인드 변수에 매개변수를 할당하는 커서가 2개 생성되었습니다.

-- 7-1
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* TEST2 */%';

SQL_ID        SQL_TEXT
------------- -------------------------------------------
gpgugsnzmh3np SELECT /* TEST2 */ * FROM t1 WHERE c1 = :b1

1개의 행이 선택되었습니다.

-- 7-2
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE 'BEGIN :b1%';

SQL_ID        SQL_TEXT
------------- --------------------
5fuv127p9havj BEGIN :b1 := 1; END;
84wzpwta11dk4 BEGIN :b1 := 2; END;

2 행이 선택되었습니다.

 

CURSOR_SHARING 파라미터

아래와 같이 t2.sql 파일을 실행하겠습니다. CURSOR_SHARING 파라미터를 FORCE로 설정한 후 쿼리를 수행하고 파라미터를 다시 EXACT로 설정합니다.

-- 8
ALTER SESSION SET CURSOR_SHARING = FORCE;

SELECT /* TEST3 */ * FROM t1 WHERE c1 = &1;

ALTER SESSION SET CURSOR_SHARING = EXACT;

 

아래와 같이 t3.sql 파일을 실행하겠습니다.

-- 9
@t3 1
@t3 2

 

아래는 V$SQL 뷰를 조회한 결과입니다. 커서가 1개만 생성된 것을 확인할 수 있습니다.

-- 10
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* TEST3 */%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------
0g2sg8hj8nz80 SELECT /* TEST3 */ * FROM t1 WHERE c1 = :"SYS_B_0"

1개의 행이 선택되었습니다.