바인드 변수에 리스트 형태로 넘길 수 없을까?

바인드 변수를 사용하는 이유와 종류

바인드 변수는 오라클에서 데이터 값을 변수로 두어서 일일이 작성하지 않고 한 번 저장해 두면 저장한 데이터 값을 불러와서 쿼리를 실행하기 때문에 자주 사용하고 있다.

 

오라클에서 바인드 변수는 다음 방법들로 선언할 수 있다.

• var(iable) 을 사용해서 선언
• SQL Developer 툴 선언
• declare 내부에서 선언
• 프로그램 파라미터에서 선언

var(iable) 을 사용해서 선언

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

a를 선언할 때 콜론을 사용하지 않지만 참조할 때는 콜론이 사용된다. 이처럼 var로 선언된 변수는 다음과 같은 특징이 있다. 

1. 선언시에는 이름만 사용, 참조시에는 콜론(:) 함께 사용
2. 세션에서 전역적으로 선언되었기 때문에 블럭내부에서 var를 사용해서 선언할 수 없음
3. 함수 호출처럼 값 할당시 exec를 사용

SQL Developer 툴 선언

콜론이 붙은 변수명을 툴에서 인식하여 SQL 실행시 변수 값을 입력하는 방법이다. 이 방법은 var 선언과는 다르게 쿼리 실행이 끝나면 세션에서 값을 찾을 수 없다.

select :id from dual;

Declare 내부에서 선언

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

  팁 

select * from &al;

select * from dual;

//오라클은 아래 문장을 파싱할 수 없다. 
select * from :a;

PL/SQL(Procedure, Package) - 동적 SQL 

EXECUTE IMMEDIATE : Inset, Update, Delete 구문을 실행하거나 Select 구문을 실행 시 INTO를 사용하여 단일 값을 리턴 받을 때 사용

 

OPEN-FOR : Select 구문을 실행 시 Cursor를 리턴 받을 때 사용

 

  팁 

바인드 변수(:) 사용 시 쿼리 내부에서 변수명은 의미가 없고 변수 순서, 개수가 USING의 변수 순서, 개수와 일치해야 한다. 바인드 변수가 없다면 USING는 생략가능.

 

EXECYTE IMMEDIATE

CREATE OR REPLACE PROCEDURE
		PC_SET_HOLIDAY ( in_hldy_dte in date
			, in_hldy_nm in varchar2
			, in_use_yn in varchar2)
IS
	v_query varchar(1000);
	d_sysdate date;
BEGIN
	BEGIN
		-- 단일 값을 리턴받을때
		EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL' INTO d_sysdate;
	END;

	v_query := v_query || 'INSERT INTO HOLIDAY';
	v_query := v_query || 'VALUES(:1,:2,:3,:4)';

	BEGIN
	-- INSERT, UPDATE, DELETE 구문 실행
    EXECUTE IMMEDIATE v_query
		USING in_hldy_dte, in_hldy_nm, in_use_yn, d_sysdate;
	END;
END;

OPEN-FOR (CURSOR 리턴 받을 때)

CREATE OR REPLACE PROCEDURE
		PC_GET_HOLIDAY ( in_fromdate in varchar2
			, in_todate in varchar2
			, out_cursor out SYS_REFCURSOR)
IS
	v_query varchar(1000);
  BEGIN
	v_query := v_query || 'SELECT HLDY_DTE, HLDY_NM';
	v_query := v_query || ' FROM HOLIDAY';
	v_query := v_query || ' WHERE HLDY_DTE BETWEEN :in_fromdate';
	v_query := v_query || ' AND :in_todate';
 
	BEGIN
		-- CURSOR를 리턴 받을때
		OPEN out_cursor FOR v_query
			USING in_fromdate, in_todate;
	END;
END;

 

Reference

[Oracle|오라클] 동적쿼리(Dynamic SQL) 사용법 (텍스트 쿼리) : gent.tistory.com/39

ORACLE 바인딩 변수, 치환 기능: jangjongmin.blogspot.com/2012/12/oracle.html

[Oracle|오라클] 동적쿼리(Dynamic SQL) 사용법 (텍스트 쿼리)