13. PL/SQL - 변수의 의미와 사용법(RECORD, TABLE)

복합변수(조합변수라고도 합니다.)

1) RECORD TYPE 변수

TYPE 타입이름 IS RECORD (
    변수명 변수타입,
    변수명2 변수타입,
    ... );
타입변수이름 타입이름;

---

--사번, 성명, 이메일, 연락처를 필드로 하는 record타입을 선언하고 
--성명이 'Luis'인 직원의 정보를 Record에 담아 출력하시오(Record 명: rec_emp)
DECLARE
TYPE rec_emp IS RECORD(
	t_deptno employees.department_id%type,
	t_name employees.first_name%type,
	t_email employees.email%type,
	t_phone employees.phone_number%type );
v_rec1 rec_emp;
BEGIN
	SELECT employee_id, first_name, email, phone_number INTO v_rec1
	FROM EMPLOYEES
	WHERE FIRST_NAME = 'Luis';
	
	dbms_output.put_line
	('사번: '||v_rec1.t_deptno||', 이름: '||v_rec1.t_name||
	', 메일: '||v_rec1.t_email||', 연락처: '||v_rec1.t_phone);
end;

%ROWTYPE 키워드

: 테이블 내의 행 구조와 동일하게 레코드 변수를 선언하고자 할 경우에 쓸 수 있습니다.

--employees 테이블의 구조 전체를 가져다가 record를 만들 수 있다 
--%rowtype를 사용해서 'Luis'의 사번, 성명, 이메일, 연락처 조회 
DECLARE
	rec employees%rowtype;
BEGIN
	SELECT* INTO rec 
	FROM EMPLOYEES 
	WHERE FIRST_NAME = 'Luis';
	
	dbms_output.put_line
	('사번: '||rec.employee_id||', 이름: '||rec.first_name||
	', 메일: '||rec.email||', 연락처: '||rec.phone_number);
END;

출력문이 같기때문에 결과는 위의 실행결과와 동일하다. 과정이 다른 것에 유의하면 됨!

- - - - - - - 연습문제

--id, phone, address, kor, eng, mat, tot, avg를 갖는 Record를 선언하여 
--임의의 값을 대입한 후, tot와 avg를 계산하여 출력하시오. (*Record명은 rec_score)
DECLARE
TYPE rec_score IS RECORD(
	mid varchar(12),
	phone varchar(15),
	address varchar(40),
	kor number(3),
	eng number(3),
	mat number(3),
	tot number(3),
	avgs number(5,2)
	 );
v_rec1 rec_score;
BEGIN
	v_rec1.mid := 'redbeans';
	v_rec1.phone := '010-1111-2222';
	v_rec1.address := '대한민국 서울 어딘가';
	v_rec1.kor := 95;
	v_rec1.eng :=88;
	v_rec1.mat := 97;
	v_rec1.tot  := v_rec1.kor+v_rec1.eng+v_rec1.mat;
	v_rec1.avgs := v_rec1.tot/3;
	dbms_output.put_line
	('ID: '||v_rec1.mid||', 폰: '||v_rec1.phone||', 주소: '||v_rec1.address);
	dbms_output.put_line('국어 점수: '||v_rec1.kor);
	dbms_output.put_line('영어 점수: '||v_rec1.eng);
	dbms_output.put_line('수어 점수: '||v_rec1.mat);
	dbms_output.put_line('과목 총합: '||v_rec1.tot);
	dbms_output.put_line('총점 평균: '||v_rec1.avgs);
end;

--depatments 테이블의 구조를 사용하여 record를 선언하고
--부서 아이디가 100번인 부서의
--부서명, 매니저 아이디, 위치를 출력하시오. (*Record명: rec_dept)
DECLARE
	rec_dept departments%ROWTYPE;
BEGIN
	SELECT* INTO rec_dept
	FROM DEPARTMENTS 
	WHERE DEPARTMENT_ID = 100;
	
	dbms_output.put_line
	('부서명: '||rec_dept.department_name||', 매니저 ID: '||rec_dept.manager_id||
	', 위치: '||rec_dept.location_id);
END;

--사번, 급여, 부서코드, 부서명을 갖는 레코드를 선언하고 Luis의 정보를 출력(rec_emp) *join
DECLARE
TYPE rec_emp IS RECORD(
	t_empid 	employees.employee_id%type,
	t_sal 		employees.salary%type,
	t_deptid 	employees.department_id%type,
	t_dname 	departments.department_name%type); 
	v_rec rec_emp;
BEGIN
	SELECT e.EMPLOYEE_ID, e.SALARY, e.DEPARTMENT_ID, d.department_name INTO v_rec
	FROM EMPLOYEES e JOIN DEPARTMENTS d
	ON e.department_id = d.department_id
	WHERE first_name = 'Luis';
	
	dbms_output.put_line('사번: '||v_rec.t_empid);
	dbms_output.put_line('급여: '||v_rec.t_sal);
	dbms_output.put_line('부서코드: '||v_rec.t_deptid);
	dbms_output.put_line('부서명: '||v_rec.t_dname);
END;

-- 사번, 급여, 부서코드, 매니저명을 갖는 레코드를 선언하고 Luis의 정보를 출력(rec_emp)
DECLARE
TYPE rec_emp IS RECORD(
	t_empid 	employees.employee_id%type,
	t_sal 		employees.salary%type,
	t_deptid 	employees.department_id%type,
	t_mname 	employees.first_name%type); 
	v_rec rec_emp;
BEGIN
	SELECT e.EMPLOYEE_ID, e.SALARY, e.DEPARTMENT_ID, ee.first_name INTO v_rec
	FROM EMPLOYEES e JOIN EMPLOYEES ee
	ON e.manager_id = ee.employee_id
	WHERE e.first_name = 'Luis';
	
	dbms_output.put_line('사번: '||v_rec.t_empid);
	dbms_output.put_line('급여: '||v_rec.t_sal);
	dbms_output.put_line('부서코드: '||v_rec.t_deptid);
	dbms_output.put_line('매니저명: '||v_rec.t_mname);
END;

---

2) TABLE TYPE 변수

Q. ID가 180번인 직원의 이름을 배열에 저장한 후 출력해보자.

DECLARE
	TYPE table_type IS TABLE OF
	employees.first_name%type INDEX BY BINARY_INTEGER;
	tbl 	table_type; --map<binary_integer, varchar2(20)> 이정도의 콜렉션으로 생각하자
BEGIN
	SELECT FIRST_NAME INTO tbl(0)
	FROM EMPLOYEES WHERE EMPLOYEE_ID = 180;
	
	dbms_output.put_line(tbl(0));
END;

Q. 부서코드가 100번인 직원들의 이름을 연관배열(table)에 저장한  후 출력하시오.

DECLARE
	TYPE table_emp IS TABLE OF
	varchar2(20) INDEX BY BINARY_INTEGER;
	tbl 	table_emp; --map<binary_integer, varchar2(20)> 이정도의 콜렉션으로 생각하자
	a 		BINARY_INTEGER := -1;
BEGIN
	FOR i IN (SELECT first_name FROM EMPLOYEES WHERE DEPARTMENT_ID = 100) 
	LOOP 
		a := a+1; 
		tbl(a) := i.first_name;		
	END LOOP;
		
	FOR j IN 0..a LOOP
		dbms_output.put_line(tbl(j)); 
	end loop;
END;

Q. 매니저 id가 114번인 직원들의 급여를 연관배열(table)에 담아 출력하시오. 

DECLARE
	type tbl_emp is table of
	employees.salary%type index by BINARY_INTEGER;
	te		tbl_emp;	
	a BINARY_INTEGER := -1;
BEGIN

	for i in (
	SELECT SALARY FROM EMPLOYEES	WHERE MANAGER_ID = 114)
	loop
		a := a+1;
		te(a) := i.salary;

	end loop;
	
	for j in 0..a loop
	dbms_output.put_line('매니저 ID가 114인 사원 '||(j+1)||'의 급여: '||te(j));
	end loop;
END;

---

- - - - - - - 연습문제(응용)

Q.   아이디, 성명, 부서코드, 부서명을 필드로 하는 record를 선언하고 

입사연도가 2007년인 직원들의 정보를 record 형식으로 저장한 후 record를 연관배열에 저장하고 출력하시오.

DECLARE
	type rec_emp is record(
	r_eid employees.employee_id%type,
	r_name employees.first_name%type,
	r_did employees.department_id%type,
	r_dname departments.department_name%type ); 
	rec 	rec_emp;	
	type tbl_emp is table of rec_emp index by binary_integer;
	tbl 	tbl_emp;	
	a binary_integer := -1;
BEGIN
	for i in (
	SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.DEPARTMENT_ID, d.department_name
	FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id WHERE TO_CHAR(HIRE_DATE, 'rrrr')='2007') LOOP
		a := a+1;
		tbl(a) := i;

	END LOOP;
	for i in 0..a loop
		rec := tbl(i);
		dbms_output.put_line(rec.r_eid);
		dbms_output.put_line(rec.r_name);
		dbms_output.put_line(rec.r_did);
		dbms_output.put_line(rec.r_dname);
	end loop;
END;

Q. 성명, 부서 코드, 부서 이름, 부서의 우편번호 조회(location_id로 locations 테이블의!)

1) ANSI 타입

SELECT e.first_name, e.DEPARTMENT_ID, d.department_id, l.postal_code
FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id
JOIN LOCATIONS l ON d.location_id = l.location_id;

2) ORACLE 타입

SELECT e.first_name, e.DEPARTMENT_ID, d.department_id, l.postal_code
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

--> pl/sql 문장으로 작성해보자. (ansi타입 사용)

DECLARE
	type rec_e is record (
	name 			employees.first_name%type,
	d_code 		employees.department_id%type,
	d_name 		departments.department_name%type,
	pcode 			locations.postal_code%type
	); r rec_e;
	
	type t_e is table of rec_e index by binary_integer;
	t t_e;
	
	cnt binary_integer := -1;
BEGIN
	for i in (
	SELECT e.first_name, e.DEPARTMENT_ID, d.department_name, l.postal_code
	FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id
	JOIN LOCATIONS l ON d.location_id = l.location_id) LOOP
		cnt := cnt+1;
		t(cnt) := i;
	end LOOP;
	
	for i in 0..cnt LOOP
	r := t(i);
	dbms_output.put_line('성명: '||r.name || '	부서 코드: '||r.d_code||
	'	부서 이름: '||r.d_name||'	우편번호: '||r.pcode); end LOOP;
END;

TABLE Type 중 VARRAY

--정수형 배열을만들어서 임의의 값을 저장한 후 총계와 평균을 계산하여 출력 
DECLARE
	type score is varray(5) of number;
	s score;
	mtot number := 0;
	mavg number(4,2) := 0;
BEGIN
	s := score(11, 27, 31, 43, 50);
	
	FOR i in 1..5 Loop
		mtot := mtot+s(i);
	end LOOP;
	
	mavg := mtot/5.0;
	dbms_output.put_line('tot: '||mtot);
	dbms_output.put_line('avg: '||mavg);
		
END;

- 배열의 값을 따로 따로 저장하기 위한 방법.

DECLARE
	type score is varray(5) of number;
	s score := score(); --생성자! 자바) score s = new score();
	mtot number := 0;
	mavg number := 0;
BEGIN
	s.extend; s(1) := 10;
	s.extend; s(2) := 20;
	s.extend; s(3) := 30;
	s.extend; s(4) := 40;
	s.extend; s(5) := 50;
	
	FOR i in 1..5 Loop
		mtot := mtot+s(i);
	end LOOP;	
	mavg := mtot/5.0;
	dbms_output.put_line('tot: '||mtot);
	dbms_output.put_line('avg: '||mavg);
END;