ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4)

[    ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4)    ]

쿼리의 일부분 예를 들어 WHERE 절의 내용만 다른 쿼리를 실행해야 하는 경우가 종종 생길 것이다. 이러한 경우에 거의 비슷한 두번의 쿼리를 실행하는 비효율성을 해소하는 방법이 바로 바인드 변수의 사용이다.
바인드 변수는 입력 내용을 넣고 SQL로부터 출력 내용을 받아내는 방법으로, " 이 부분에 들어갈 정확한 값은 이후에 알려줄테니, 일단 내가 값을 넣었을 때 어떻게 실행할 것인지에 대해서 계획만 세워둬라 "는 명령을 오라클에 내리는 것이다.

select * into temp from row_test where no = :bind_no; 이 부분이 있을 때 :가 붙은 변수가 바인드 변수인대, 실행한 뒤에 바인드 변수에 입력할 값을 넣게 된다.

그에 따라 no = 1, no = 2 등 실행할 때마다 다양한 결과를 낼 수가 있게 된다.

/* [ Table Type 변수(=컬렉션) ] 컬렉션 : 일반 프로그래밍 언어에서 사용하는 배열 타입을 pl/SQL에서는 "컬렉션"이라 한다. - 종류 1. 연관배열(associative array or index-by table) : (KEY, VALUE) 쌍으로 저장되는 배열 - 키값을 통해 값에 접근한다. . key의 데이터 유형 - 숫자 : binary_integer, pls_integer 위 두가지 데이터 타입은 number보다 작은 저장 영역이 필요, 산술연산의 경우 number보다 빠르다. - 문자 : varchar2 . 값(value) 유형 - 일반 데이터 타입, 레코드 데이터 타입도 값이 될 수 있다. -> 레코드 타입일 경우 여러개의 값을 가질 수 있다. 2. varry(variable array) : 고정 길이를 가진 배열(일반 프로그래밍에서 사용하는 배열과 동일) : 인덱스가 "0"부터 시작한다. ex) 3번째 v[3] 3. 중첩테이블(nested table) : varry와 흡사한 구조의 배열 : (배열의 크기를 명시하지 않음)배열의 최대값이 정해지지 않고 동적으로 자동으로 최대값이 증가됨 - [ Table Type(컬렉션)의 선언 형식 ] 1. 정의 TYPE 타입명 IS TABLE OF employees.first_name%type INDEX BY BINARY_INTEGER -- binary integer로 인덱스를 사용한단 의미 2. 선언(메모리 공간이 잡히는 부분) 식별자 타입명; */ set SERVEROUTPUT ON; declare tname varchar2(20); -- 정의 TYPE t_emp_name IS TABLE OF employees.last_name%type INDEX BY BINARY_INTEGER; -- 선언 v_name t_emp_name; begin select last_name into tname from employees where employee_id = 100; -- 인덱스를 통해 접근 v_name(0) := tname; DBMS_OUTPUT.PUT_LINE(v_name(0)); end; / declare TYPE tbl_type IS TABLE OF employees.last_name%type INDEX BY BINARY_INTEGER; vtbl_type tbl_type; a binary_integer := 0; -- 변수 a값을 0으로 초기화 begin for emp_name in(select last_name from employees) loop a := a+1; vtbl_type(a) := emp_name.last_name; end loop; for i in 1..a loop DBMS_OUTPUT.PUT_LINE(vtbl_type(i)); end loop; end; / /* [ 바인드 변수(비 PL/SQL 변수) ] : 호스트 환경에서 생성되어 데이터를 저장하기 때문에 호스트 변수라고 한다. 쿼리의 일부분 예를 들어 WHERE 절의 내용만 다른 쿼리를 실행해야 하는 경우가 종종 생길 것이다. 이러한 경우에 거의 비슷한 두번의 쿼리를 실행하는 비효율성을 해소하는 방법이 바로 바인드 변수의 사용이다. 바인드 변수는 입력 내용을 넣고 SQL로부터 출력 내용을 받아내는 방법으로, " 이 부분에 들어갈 정확한 값은 이후에 알려줄테니, 일단 내가 값을 넣었을 때 어떻게 실행할 것인지에 대해서 계획만 세워둬라 "는 명령을 오라클에 내리는 것이다. select * into temp from row_test where no = :bind_no; 이 부분이 있을 때 :가 붙은 변수가 바인드 변수인대, 실행한 뒤에 바인드 변수에 입력할 값을 넣게 된다. 그에 따라 no = 1, no = 2 등 실행할 때마다 다양한 결과를 낼 수가 있게 된다. - 키워드 VARIABLE를 이용하며, SQL문이나 PL/SQL블록에서도 사용가능 - PL/SQL블록이 실행된 후에도 액세스가 가능하다. - print명령을 이용하여 출력가능 - :을 붙여 이용한다. */ set autoprint on; -- print를 하지 않아도 자동으로 출력하도록 세팅하는 부분(default는 false로 되어있음) declare temp row_test%ROWTYPE; begin select * into temp from row_test where no = :bind_no; insert into row_test3 values temp; end; select * from row_test3; -- no에 1을 입력시 그에 해당되는 결과가 2를 입력하면 그에 해당하는 결과가 나오게 된다. begin select (salary*12+nvl(commission_pct,0)) into :vsal -- 결과값을 바인드 변수에 넣는다. from employees where employee_id = 100; end; / -- print를 이용해서 PL/SQL블럭 밖에서도 출력할 수 있다. print vsal; create table row_test3 as select * from row_test; truncate table row_test3; select * from row_test3; declare type arrRecType IS TABLE OF row_test%ROWTYPE INDEX BY binary_integer; TestValue arrRecType; a binary_integer := 0; begin for temp_rec in (select * from row_test) loop a := a+1; TestValue(a) := temp_rec; end loop; for i in 1..a loop insert into row_test3 values TestValue(i); end loop; end; / select * from row_test3;