Goals
- 각 컬렉션별 특징, 중첩 테이블, V-ARRAY
- 커서, 결과 행이 여러개가 될 수 있는 커서, 명시적 커서를 for loop에서 사용하기, 파라미터를 갖는 커서 선언
Notes
- 컬렉션(Collection)의 종류 : 연관배열, 중첩테이블, VARRAY
1) 연관 배열( Associative Array, Index-by table )
- 인덱스는 정수, 양의 정수, 문자열을 사용할 수 있음.
- 저장할 수 있는 값들의 갯수의 제한이 없음.
- 생성자를 사용하지 않음.
- type ... is table of ... index by ...;
2) 중첩테이블( Nested Table )
- 인덱스는 양의 정수만 가능-> 인덱스의 타입(index by)을 명시하지 않음.
- 저장할 수 있는 값들의 갯수의 제한이 없음.
- 생성자를 반드시 사용해야 함.
- type ... is table of ...;
3) V-Array ( Variable Array : 고정된 길이를 가진 배열을 의미 )
- 인덱스는 양의 정수만 가능 -> 인덱스의 타입(index by)을 명시하지 않음.
- V-Array가 선언될 때 저장할 수 있는 값의 갯수를 지정함.
- 생성자를 반드시 사용해야 함.
- type ... is varray( limit ) of ...;
declare
-- 중첩 테이블 선언
type NumberArray is table of number; -- index by를 사용하지 않음
-- 중첩 테이블 타입의 변수를 선언하자
v_numbers NumberArray;
begin
-- v_numbers(1) := 100;
-- 중첩 테이블은 반드시 생성자를 호출해서 초기화(initialize)를 해야 함.
v_numbers := NumberArray(100, 200, 300);
-- 여기서 NumberArray가 생성자이다. 배열을 만들어 주는 것이 생성자이다.
-- Type의 이름과 생성자의 이름(TypeArray)이 같다.
-- 생성자의 매개변수로 중첩테이블/ Varray에 저장할 값들을 전달한다.
-- 중첩 테이블에 값을 추가할 때는
-- extend(배열 확장 함수)를 호출해서 배열이 저장할 수 있는 원소의 갯수를 늘려준 후
-- 값을 추가해야 함. extend 가능한 갯수의 제한이 없음.
v_numbers.extend(3);
v_numbers(4) := 400;
v_numbers(5) := 500;
v_numbers(6) := 600;
-- 중첩 테이블에 저장된 값들을 출력
for i in 1 .. v_numbers.count loop
dbms_output.put_line(i||' : '|| v_numbers(i));
end loop;
end;
/
declare
-- VARRAY 타입 선언 (생성자와 함께)
type NumberArray is varray(5) of number;
v_numbers NumberArray;
begin
-- VARRAY 생성자 호출
v_numbers := NumberArray(11, 22, 33, 44);
dbms_output.put_line( 'count : ' || v_numbers.count); -- count : 4
DBMS_OUTPUT.PUT_LINE('limit : ' || v_numbers.limit); -- limit : 5
-- Varray에 원소를 추가하고 싶으면, extend() 호출 후 원소를 추가해야 함.
v_numbers.extend(1); -- extend(2) 이상은 불가능
v_numbers(5) := 55;
for i in 1.. v_numbers.count loop
dbms_output.put_line(i || ' : ' || v_numbers(i));
end loop;
end;
/
-- 문자열 5개를 저장할 수 있는 varray를 StringArray라는 이름으로 선언
-- StringArray 타입의 변수(v_names)를 선언
-- v_names를 원소가 없는 varray 로 초기화(생성자 호출)
-- v_names 저장하는 원소의 개수를 5개로 확장(extend)
-- v_names에 5개의 문자열을 저장
-- v_names에 저장된 문자열을 출력
declare
type StringArray is varray(5) of varchar2(10);
v_names StringArray;
begin
v_names := StringArray();
v_names.extend(5);
v_names(1) := '한일';
v_names(2) := '두이';
v_names(3) := '석삼';
v_names(4) := '너구리';
v_names(5) := '오징어';
for i in 1..v_names.count loop
dbms_output.put_line(i||' : ' || v_names(i));
end loop;
end;
/
- 커서 (Cursor) : PL/SQL 내부에서 SQL 문장을 처리하는 정보를 저장한 메모리 공간
- 커서의 종류
(1) 명시적 커서 (explicit cursor) : 개발자가 직접 이름을 선언하고 사용하는 커서
(2) 묵시적 커서 (implicit cursor) : 별다른 선언 없이 사용하는 커서
명시적 커서 : DECLARE 선언 -> BEGIN open -> fetch -> close END
- fetch 는 행을 가져오는 것
-- select into 구문은 1개 이상의 행(row)이 select되는 경우는 사용할 수 없다.
-- 명시적 커서는 select의 결과 행의 갯수에 상관없이 사용할 수 있다.
declare
-- 명시적 커서 선언
-- cursor 커서이름 is (SQL 문장);
cursor my_cursor is (
select * from dept where deptno = 10 -- 세미콜론(;) 없음
);
v_row dept%rowtype; -- 커서의 데이터를 저장(fetch) 할 때 사용할 변수
begin
-- 커서 open (열기, sql 문장 실행)
open my_cursor;
-- 커서 fetch(데이터 읽기)
fetch my_cursor into v_row;
dbms_output(v_row.deptno || v_row.dname|| v_row.loc);
-- 커서 close(닫기)
close my_cursor;
end;
/
declare
--(1) 커서 선언
cursor sel_emp is (
select * from emp where empno = 7788
);
v_row emp%rowtype;
begin
--(2) 커서 open
open sel_emp;
--(3) 커서 실행 결과 읽기(fetch)
fetch sel_emp into v_row;
dbms_output.put_line(v_row.empno||','||v_row.ename);
-- (4) 커서 close
close sel_emp;
end;
/
-- 결과 행이 여러개가 될 수 있는 명시적 커서
declare
-- 명시적 커서 선언
cursor sel_dept is (
select * from dept
);
-- 커서 실행 결과를 fetch 할 때 사용할 변수
v_row dept%rowtype;
begin
--(2) open
open sel_dept;
--(3) fetch -- 결과 행이 여러개일 경우 반복문 안에서 fetch해야 한다.
loop
fetch sel_dept into v_row;
-- 커서에서 더 이상 읽을 레코드가 없을 때 loop 종료
exit when sel_dept%notfound;
dbms_output.put_line(v_row.deptno || ', ' ||v_row.dname || ', '|| v_row.loc);
ㄴ 출력 항목을 여기서 출력 값으로 제어.
select문장에서 사번, 이름, 급여를 하려면 , 각각 대응하는 변수를 지정해주어야 함.
end loop;
--(4) close
close sel_dept;
end;
/
-- emp 테이블에서 부서번호가 30번인 직원들의 사번, 이름, 급여를 출력
declare
cursor emp_inf is (
select empno, ename, sal from emp
);
e_empno emp.empno%type;
e_ename emp.ename%type;
e_sal emp.sal%type;
begin
open emp_inf;
loop
fetch emp_inf into e_empno, e_ename, e_sal;
exit when emp_inf%notfound;
dbms_output.put_line(e_empno||', '||e_ename||', '||e_sal);
end loop;
close emp_inf;
end;
/
-- 변수를 레코드타입으로 선언한다.
declare
cursor my_cursor is (
select empno, ename, sal from emp where deptno = 10
);
type my_record is record (
empno emp.empno%type,
ename emp.ename%type,
sal emp.sal%type
);
v_row my_record;
begin
open my_cursor;
-- loop
-- fetch my_cursor into v_row;
-- exit when my_cursor%notfound;
-- end loop;
fetch my_cursor into v_row;
while my_cursor%found loop
dbms_output.put_line(v_row.empno || ', '
|| v_row.ename || ', '
|| v_row.sal);
fetch my_cursor into v_row;
end loop;
close my_cursor;
end;
/
- 명시적 커서를 for loop에서 사용할 경우
open, fetch, close가 자동으로 수행됨.
** for 변수 in 커서 loop ... end loop;
declare
cursor my_cursor is (
select * from dept
);
begin
for row in my_cursor loop
dbms_output.put_line(row.deptno || ', '|| row.dname ||', '|| row.loc);
end loop;
end;
/
-- 1. 전체 직원의 급여 평균보다 적은 급여를 받는 직원들의 이름을 출력하는 PL/SQL
declare
cursor my_cursor is (
select ename from emp where sal<(select avg(sal) from emp)
);
begin
for result in my_cursor loop
dbms_output.put_line(result.ename);
end loop;
end;
/
-- 파라미터(parameter, 매개변수)를 갖는 커서 선언
-- cursor 커서이름(매개변수1 변수타입1,매개변수2 변수타입2 ... ) is SQL문장;
declare
v_avg number;
cursor my_cursor(p_avg number) is (
select ename from emp where sal < p_avg);
begin
-- 전체 직원의 급여 평균을 출력. --> 커서에 삽입
select avg(sal) into v_avg from emp;
dbms_output.put_line(v_avg);
-- 파라미터를 갖는 커서를 open/fetch/close -- row는 변수선언 필요 x
for row in my_cursor(v_avg) loop
dbms_output.put_line(row.ename);
end loop;
end;
/
-- 2. 10번 부서에서, 10번 부서 직원들의 급여 평균보다 적은 급여를 받는 직원들의 이름을 출력하는 PL/SQL
declare
cursor my_cursor is (
select * from emp
where sal<=(select avg(sal) from emp where deptno = 10)
and deptno = 10
);
begin
for row in my_cursor loop
dbms_output.put_line(row.ename);
end loop;
end;
/
-- 3. 각 부서에서, 그 부서 직원들의 급여 평균보다 적은 급여를 받는 직원들의
-- 부서번호와 직원 이름을 출력하는 PL/SQL
declare
cursor my_cursor(p_dno emp.deptno%type) is (
select * from emp
where sal<=(select avg(sal) from emp where deptno = p_dno)
and deptno = p_dno
);
begin
for i in 1..3 loop
for row in my_cursor(10*i) loop
dbms_output.put_line(row.deptno||', '|| row.ename);
end loop;
end loop;
end;
/
-- 3. 각 부서에서, 그 부서 직원들의 급여 평균보다 적은 급여를 받는 직원들의
-- 부서번호와 직원 이름을 출력하는 PL/SQL
declare
cursor my_cursor(p_dno emp.deptno%type) is (
select * from emp
where sal<(select avg(sal) from emp where deptno = p_dno)
and deptno = p_dno
);
cursor dno_cursor is(
select deptno from dept
);
begin
for dnum in dno_cursor loop
for row in my_cursor(dnum.deptno) loop
dbms_output.put_line(row.deptno||', '|| row.ename);
end loop;
end loop;
end;
/
declare
cursor cur1 is(
select e.ename, e.deptno, a.average, e.sal
from emp e
join ( select deptno, avg(sal) average
from emp group by deptno) a
on e.deptno = a.deptno
where e.sal<a.average);
begin
for rslt in cur1 loop
dbms_output.put_line(rslt.ename||', '|| rslt.deptno||', '||rslt.sal);
end loop;
end;
-- 사원번호와 세율을 파라미터로 전달받아서 총급여(total = (sal+comm)*(1-tax)) 를 계산해서 리턴하는 함수
create or replace function tot_sal(
p_empno emp.empno%type,
p_taxrate number := 0.05
) return number
is
v_sal emp.sal%type;
v_comm emp.comm%type;
begin
select sal, comm into v_sal, v_comm from emp where empno = p_empno;
return ((v_sal+nvl(v_comm,0))*(1-p_taxrate));
end;
/
-- 호출1
declare
cursor every_empno is ( select empno from emp);
begin
for i in every_empno loop
DBMS_OUTPUT.PUT_LINE (i.empno||', '||tot_sal(i.empno));
end loop;
end;
/
실행결과
-- 호출2
select empno, ename, sal, comm, tot_sal(7788,0.05)
from emp
where empno = 7788;
실행결과
※ 파라미터를 지정해서 값을 삽입하는 방법 : " => "
select empno, ename, sal, comm, tot_sal ( p_empno => 7654 , p_taxrate => 0.08 )
from emp
where empno = 7788;
'DB > Oracle' 카테고리의 다른 글
| Oracle DB15_ 프로시저와 패키지 (0) | 2019.10.11 |
|---|---|
| Oracle DB14_예외처리, 함수, 프로시저 (0) | 2019.10.09 |
| Oracle DB12_레코드,연관배열 (0) | 2019.10.03 |
| Oracle DB11_ 반복문 연습문제2 (0) | 2019.10.01 |
| Oracle DB10_조건문/반복문 연습문제 (0) | 2019.09.30 |
