오라클의 바인드(바인딩) 변수와 치환 기능을 설명하고 두 기능의 차이점과 Sql*Plus 및 Toad 에서의 사용시 차이점을 설명합니다.

치환

치환은 스크립트의 특정 문자열을 미리 지정한 변수로 교체하여 Oracle에 전송하는 기능이다. 이를 사용하는 방법은 Sql*Plus인지 Toad에서 실행인지에 따라 조금 다르다. Sql*Plus에서는 define 명령을 사용하여 변수를 설정한다. 스크립트에서 사용하려면 변수명 앞에 엠퍼센드(&)를 쓰고 변수명을 입력한다.

define tbname = 'employee'
select * from &tbname;
undefine tbname

치환에 사용되는 변수 선언은 define을 사용하며 사용시에는 &를 붙인 이름을 사용한다. 위 구문은 Sql*Plus나 Toad 모두에서 공통으로 실행 가능하다. 첫줄이 없다면 Sql*Plus는 명령줄에서 치환 문자를 물어본다. Toad에서는 스크립트에서 &가 붙은 이름을 만나면 define으로 정의되어 있지 않으면 프롬프트로 사용자에게 물어본다. 위 프로그램을 F5로 Run as script로 실행하면 toad는 선언된 변수를 인식하고 select * from employees를 oracle로 전송한다. F9나 Ctrl+Enter로 실행하면 Toad는 분해 가능한 프로그램 단위로 실행을 하기 때문에 define부분이 무시되고 select * … 문이 구문 분석되어 프롬프트로 변수를 어떤 문자로 치환할지 물어본다. toad에서는 실행할 명령을 구문분석 하면서 코멘트 내부에 들어있는 앰퍼센드를 치환 변수로 인식하여 치환 프롬프트를 출력하는 경우가 있을 수 있다. 이 경우는 치환 프롬프트를 끄는 옵션이 존재하므로 이를 사용하면 된다.

바인드 변수

바인드 변수는 이 변수를 Oracle 의 sql parser가 인지한다는 점에서 치환과 다르다. sal은 타입을 가지는 변수이며 그렇기 때문에 숫자가 들어갈 수 없는 구문에서 사용되면 구문 오류가 발생하고 쿼리 엔진은 실행을 멈출것이다.

var sal number
exec :sal := 10000
select * from employees where salary > :sal;

이 스크립트는 치환을 쓴 스크립트와 비슷해 보이지만 oracle 이 전체 문장에 대해 구문 분석을 수행하고 구문 분석된 결과가 컴파일 되어 oracle 에 캐싱이 된다는 점에서 다르다. oracle은 최근 실행한 sql에 대한 캐시를 유지하는데 아래 쿼리는 첫번째 쿼리보다 이론적으로 빠르게 실행된다.

exec :sal := 15000
select * from employees where salaray > :sal;

여기서 이론적이라고 말한것은 위와 같은 간단한 sql은 oracle이 처리하는데 많은 시간이 필요치 않기 때문이다. 쿼리가 복잡해서 파싱이 오래 걸리는 경우 바인드 변수를 사용한 두번째 sql은 미리 저장해 두었던 실행계획을 통해 즉시 수행되므로 파싱+실행계획생성+수행에서 앞 두 부분에 들어가는 시간이 0에 가까워 지므로 쿼리 수행이 빠르게 수행된다. 반면 치환을 사용하면 매번 파싱+실행계획생성이 수행된다. 아무리 비슷하게 보이는 명령이라도 오라클로써는 다른 문자열을 가진 sql일 뿐이다.

Sql*Plus에서는 var 로 변수를 지정하고 나면 프로그램의 나머지 부분에서 이 변수를 콜론이 붙은 변수명을 이용하여 참조할 수 있다.

반면 toad에서는 위 프로그램을 Run as script로 실행할 때는 오류없이 수행되지만 F9로 Execute Statement를 수행하면 :sal이 정의되지 않았다고 에러를 낸다. toad에서 위 프로그램을 수행하려면 변수의 선언과 값의 설정부분을 제거하거나 select 문과 분리하여 select 문이 온전히 혼자서 수행되도록 만들면 toad는 이 변수가 사용자가 설정한 변수가 아니라고 판단하고 변수 설정 프롬프트를 발생시키고 프로그램을 수행한다.

var sal number
exec :sal := 10000


select * from employees where salary > :sal;

select 를 F9로 실행할때는 1번 라인과 2번 라인은 존재 자체가 의미가 없다. F9로 실행할 때 toad가 변수 선언 및 할당을 대신 해 준다는 사실을 알고 있어야 한다. 왜냐하면 스크립트를 만들어서 실행할 때는 치환과는 다르게 Sql*Plus나 *toad가 사용자에게 값 할당을 물어보지 않을 것이므로 스스로 값을 선언하고 할당하는 방법을 알고 있어야 한다.