Oracle DB13_VARRAY, 커서

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;