일상을 추억으로: ORACLE 바인딩 변수, 치환 기능

이 문서의 내용은 아래 링크에 새로 정리하였습니다. 토드와 SQL*PLUS에서의 바인드 변수 사용 방법을 함께 살펴 보려면 아래 링크를 참고하세요.

http://www.jongminjang.com/plsql/2018/08/14/bindvariable.html

오라클은 바인드 변수라는 개념의 변수 사용을 지원한다.

바인딩 변수는 SQL 문장을 실행할 때 SQL에 사용자 값을 전달할 수 있는 통로 역할을 한다. 또한 오라클 성능에 큰 영향을 미치는 SQL 공유와도 큰 연관이 있다.

바인드 변수는 다음 방법들로 선언될 수 있다.

var[riable] 을 사용해서 선언
Toad, SQL Developer등의 툴이 선언
declare 내부에서 선언
프로그램 파라미터에서 선언

일단 var를 사용하는 예를 보자.

var a number;
exec :a := 2;
select :a from dual;

var로 선언된 바인드 변수가 다른 방법들로 선언된 바인드 변수와 가장 다른점이 여기서 나왔다. a를 선언할 때에는 콜론을 사용하지 않지만 참조할 때는 콜론이 사용되었다. var변수를 자주 사용하지 않기 때문에 항상 헷갈리는 점이다.

var로 선언된 변수의 특징은 다음과 같다.

선언시에는 이름만 사용, 참조시에는 콜론(:) 함께 사용
세션에서 전역적으로 선언됨. 즉 블럭내부에서 var를 사용해서 선언할 수 없음
값 할당시 exec 를 사용. 즉 함수 호출처럼 다루어짐

1의 특징에 대해서는 처음의 예제에서 설명이 되었다.

2는 var변수가 한번 선언되고 나면 해당 세션에서 계속 선언되어 있음을 뜻한다.

var 명령어를 수행하면 var로 선언된 모든 변수의 목록이 출력된다.

VARIABLE a  DATATYPE VARCHAR2
...

3은 값의 할당에 관련된 내용이다. 함수 호출 문법을 사용해서 할당을 수행해야 한다.

Oracle PL/SQL Ed3은 SQL*Plus에서 variable 할당이 익명블럭에서만 가능하다고 설명하고 있지만 실제로 그런 제약은 존재하지 않았다(ORACLE 9i에서 테스트됨)

var 는 스크립트 수행등 사용자가 개입할 여지가 없는 환경에서 사용된다.

Var사용시 Toad는 SQL*Plus와 약간 다르게 동작하였다. SQL*Plus와 SQL Developer는 var를 사용하지 않고 변수를 참조하려고 할때 정상적인 에러를 생성하였는데 Toad은 이 경우에 에러를 생성하지 않고 빈(NULL아님) 변수가 존재하는것 처럼 동작하였다.

다음은 툴이 바인딩 변수를 선언해주는 경우이다. 이 방식은 콜론이 붙은 바인드 변수 명을 프로그램이 인식해서 SQL 실행시에 사용자에게 값을 요구하는 방식이다.

다음 프로그램을 보자

select :i from dual;

위 문장을 SQL*Plus에서 호출하면 :i 가 정의되어 있지 않아서 오류를 발생할 것이다. 같은 문장을 Toad, Sql Developer에서 수행하면 :i 바인딩 변수에 대한 값을 사용자에게 요구한다. 여기서 값을 입력하면 :i 는 사용자가 var로 선언한 것처럼 바인딩 변수로 다루어 진다. 그러나 var선언과는 다르게 이 변수는 문장의 실행이 끝나면 세션에서 찾을 수 없다.

declare절에서 선언되는 경우나 매개변수 파라미터로 선언되는 경우에는 변수는 해당 블럭 내부에서 자유롭게 사용될 수 있다.

다음 프로그램은 두 개의 변수를 사용한 간단한 프로그램이다.

declare
 a number;
 b number;
begin
 a := 1;
 select a into b from dual;
end;

var를 사용하거나 툴이 선언해주는 방식과는 다르게 이름앞에 콜론이 들어가지 않는다.

매개변수와는 다르지만 사용자로 부터 값을 입력받는 다른 방식인 치환을 살펴보자. 치환은 SQL문장 자체를 변환시켜서 오라클에 전송한다.

다음 예제를 보자.

select * from &al;

이 방식은 SQL*Plus, Toad, SQL developer등의 모든 툴에서 사용가능하다. 이 문장을 실행하면 a에 대한 변경 값을 요구한다.

&a에 dual을 입력하면 위 문장은

select * from dual;

로 변경되어 오라클에 전송되고 실행된다. 사실 바인드 변수로는 위와 같은 문법을 사용하지 못한다. 예를 들어

select * from :a;

는 명백한 오류이다. 왜냐하면 오라클은 위 문장을 파싱할 수 없기 때문이다.

이 문서의 내용은 아래 링크에 새로 정리하였습니다. 토드와 SQL*PLUS에서의 바인드 변수 사용 방법을 함께 살펴 보려면 아래 링크를 참고하세요.

http://www.jongminjang.com/plsql/2018/08/14/bindvariable.html

프로필

ORACLE 바인딩 변수, 치환 기능