Oracle DB12_레코드,연관배열

Goals

• Record - 정의, 테이블에 데이터 저장, 업데이트 ~ Row
• Collection - 연관 배열 ~ Column
• 변수이름.count, 변수이름.first, 변수이름.last

Notes

레코드(record) : 데이터 타입이 서로 다른 여러개의 값(데이터)들을 저장할 때 사용하는 변수 타입을 지칭하는 말.

     즉, 테이블의 열 데이터를 여러개 저장할 때 사용함.

      

행(row) = 콜렉션(Collection) 데이터 타입이 같은 값들을 저장.

-- 레코드 정의 방법 : 

1) Type 레코드이름 is record (

변수이름1 변수타입,

변수이름2 변수타입,

.....

);

-- 레코드에 값 저장 방법 :

레코드.필드 := 값

declare

    type rec_dept is record (

        deptno  number,

        dname   varchar2(20),

        loc     varchar2(20)        

    );

    

        v_dept_row rec_dept;

변수 이름   타입

begin

    -- 레코드 변수에 값을 저장

     

        v_dept_row.deptno := 99;

        v_dept_row.dname := 'ITWILL';

        v_dept_row.loc := '서울 강남';

    

    dbms_output.put_line('번호 : '|| v_dept_row.deptno);

    dbms_output.put_line('번호 : '|| v_dept_row.dname);

    dbms_output.put_line('번호 : '|| v_dept_row.loc);

end;

--dept2 테이블에 레코드를 insert !!

declare

    type rec_dept is record (

        deptno  dept2.deptno%type,

        dname   dept2.dname%type,

        loc     dept2.loc%type

    );

    

    v_row rec_dept;

begin

    v_row.deptno    := 11;

    v_row.dname     := '데이터베이스';

    v_row.loc       := '서울';

    

    --dept2 테이블에 v_row의 레코드를 저장insert!

    insert into dept2

    values v_row;

RECORD를 이용한 TABLE UPDATE

declare

    type rec_dept is record (

        deptno  dept2.deptno%type,

        dname   dept2.dname%type,

        loc         dept2.loc%type

    );

    

    v_row rec_dept;

begin

-- 레코드 타입의 변수를 사용한 dept2 테이블 업데이트

-- UPDATE dept2   SET deptno = ?, dname = '', loc = '' where deptno = 11;

 

   v_row.deptno     := 99;

    v_row.dname     := 'PL/SQL';

    v_row.loc            := '강남';

    update dept2 set row = v_row where deptno = 11;

end;

DECLARE

    -- row type 참조변수 == 테이블의 구조(컬럼 이름/타입)와 똑같은 레코드 타입

    v_row1 dept%rowtype;

BEGIN

    v_row1.deptno   := 22;

    v_row1.dname    := 'restaurant';

    v_row1.loc      := 'north korea';

    

    insert into dept2    values v_row1;

END;

/

   -- 레코드를 이용한 SELECT 구문

DECLARE

    -- 테이블의 모든 칼럼을 검색하는 경우에는

    -- 레코드를 직접 정의하는 것보다 %rowtype 참조를 사용하는 것이 편리함.

    v_emp_row1 emp%rowtype;

    

    -- 테이블의 일부 컬럼만 검색하거나 두개 이상의 테이블에서 조인하는 경우

    -- 사용자 정의 레코드를 선언하는 것이 편리함.

    type rec_emp is record (

        empno   emp.empno%type,

        ename   emp.ename%type,

        job     emp.job%type

    );

    v_emp_row2 rec_emp;

    

BEGIN

    -- 테이블에서 전체 컬럼 검색, 동시에 레코드에 값이 저장됨.

    select *    into    v_emp_row1

    from emp    where   empno = 7788;

    dbms_output.put_line(v_emp_row1.empno|| ', ' || v_emp_row1.ename);

    

    -- 테이블에서 일부 컬럼만 검색, 동시에 레코드에 값이 저장됨.

    select empno, ename, job    into v_emp_row2

    from emp            where empno = 7839;

    dbms_output.put_line(v_emp_row2.empno|| ', ' || v_emp_row2.ename);

END;

/

--1

-- EMP 테이블과 DEPT 테이블에서

-- 사번, 이름, 급여, 부서 이름, 부서 위치

-- 변수들을 저장할 수 있는 레코드(emp_dept)를 정의

-- 조인(join) 문장을 작성해서 7788번 사원의 scott의 정보를 출력하는 pl/sql을 작성

DECLARE

    type emp_dept is record (

        empno   emp.empno%type,

        ename   emp.ename%type,

        sal     emp.sal%type,

        deptno  dept.deptno%type,

        loc     dept.loc%type

    );

    v_emp_dept emp_dept;

    

BEGIN

    select e.empno, e.ename, e.sal, d.deptno, d.loc    into    v_emp_dept

    from emp e, dept d    where   e.empno = 7788 and e.deptno = d.deptno ;

    dbms_output.put_line('empno : '     ||v_emp_dept.empno||

                        ', ename : '    || v_emp_dept.ename||

                        ', sal : '      || v_emp_dept.sal ||

                        ', deptno : '   ||v_emp_dept.deptno ||

                        ', loc : '      ||v_emp_dept.loc);

END;

/

--2

-- emp 테이블과 salgrade 테이블에서

-- 사번, 이름, 급여, 급여 등급

-- 4개 변수들을 저장할 수 있는 레코드(emp_sal)를 정의

-- 조인(join) 문장을 작성해서 scott의 정보를 출력하는 pl/sql

DECLARE

    type emp_sal is record (

        empno   emp.empno%type,

        ename   emp.ename%type,

        sal     emp.sal%type,

        grade   salgrade.grade%type        

    );

    v_emp_sal emp_sal;

    

BEGIN

    select e.empno, e.ename, e.sal, s.grade    into    v_emp_sal

    from emp e, salgrade s  

    where   e.empno = 7788 and

           (e.sal between s.losal and s.hisal) ;

            

    dbms_output.put_line

    ('empno : '     ||v_emp_sal.empno||

    ', ename : '    || v_emp_sal.ename||

    ', sal : '      ||v_emp_sal.sal ||

    ', grade : '    ||v_emp_sal.grade );

END;

/

• Collection

같은 타입의 데이터를 여러 개 저장할 때 사용 --> 다른 언어의 배열과 비슷한 성질. 

  칼럼 값을 가져다가 평균, 등을 계산할 때 사용함

1) 연관 배열( Associative Array, index by Table  ) : 

Key/Index와 Value의 쌍으로 구성되는 컬렉션

연관 배열에서 key/index는 binrary_integer(정수), pls_integer(양의 정수), varchar2(문자열) 타입이 가능.

>>연관 배열을 선언(정의)하는 방법

type 배열 이름 is table of 데이터타입 index by 인덱스타입; --> 인덱스를 숫자로 쓸지 문자로 쓸지 명시.

                                                         number, 배열이 가진 원소의 타입

declare

    -- 문자열을 원소로 갖고, 양의 정수를 인덱스로 사용하는 연관 배열을 정의.

type students is table of varchar2(20)

index by PLS_INTEGER;

    

    -- 정의한 연관 배열 타입의 변수 선언

v_stu students;

begin

    -- 연관 배열에 데이터 저장 : 배열이름(인덱스) := 값;

    v_stu(1)    := '강다혜';

    v_stu(2)    := '김수인';

    v_stu(3)    := '김영광';

    

-- 연관 배열에 저장된 원소(데이터)값을 읽을 때 : 배열이름(인덱스)

for i in 1..3 loop

dbms_output.put_line(v_stu(i));

end loop;

    

end;

/

declare

    -- 인덱스 : 양의 정수 , 원소 ( 데이터 타입 ) : 숫자

    -- 연관된 타입의 변수를 선언;

    type arr_numbers is table of number(3)

    index by pls_integer;

    v_scores arr_numbers;

    v_sum number := 0;

    v_avg number := 0;

    

begin

    -- 연관 배열 v_score에 5개의 정수를 입력하세요

    v_scores(1) := 100;

    v_scores(2) := 75;

    v_scores(3) := 50;

    v_scores(4) := 60;

    v_scores(5) := 84;

    

   -- 데이터가 테이블에 저장된 것이 아니므로 sum(), avg()함수를 사용하려면.

    for i in 1..v_scores.count loop

        v_sum := v_sum + v_scores(i);

    end loop;

    

    v_avg := v_sum/v_scores.count;

        

    dbms_output.put_line('합계 : '||v_sum);

    dbms_output.put_line('평균 : '||v_avg);

end;

/

--테이블을 만들어서 select into.. 구문을 사용한다.

변수이름.count

배열 원소의 갯수(예 : v_scores.count)

변수이름.first

배열 원소의 첫 번째 인덱스(예 : v_scores.first)

변수이름.last

배열 원소의 마지막 인덱스(예 : v_scores.last)

-- 인덱스는 양의 정수, 배열의 원소(데이터)는 숫자 타입인 연관 배열을 정의

-- 난수 (dbms_random.value(x,y))함수와 trunc를 이용해서,

-- for loop를 사용해서 연관 배열에 10개의 시험 성적(0 ~ 100)을 저장

-- 10개 시험 성적의 총합, 평균, 분산, 표준편차를 계산하는 식을 구현

-- power(x,y), sqrt(x)

declare

    type arr_numbers is table of number(3)

    index by pls_integer;

    v_scores arr_numbers;

    v_sum number := 0;

    v_avg number := 0;

    v_devsum number := 0;

    v_dev   number  := 0;

    v_max   number := 0;

    v_min   number := 100;

    ------ㄴㄴㄴ직접계산 변수

    t_sum number := 0 ;

    t_avg number := 0 ;

    t_dev number := 0 ;

    t_std number := 0 ;

    t_max number := 0 ;

    t_min number ;

    -- 셀랙트문 변수

    x_x number :=0 ;

    --배열번호

    

    type rec_score is record (

        sid     ex_scores.sid%type,

        score   ex_scores.score%type );

    

    v_sc rec_score;

    

    ---ㄴㄴㄴ>레코드 변수

begin

    -- 직접 계산

    for k in x_x..(x_x+10) loop

        v_scores(k) := trunc(dbms_random.value(1,101),0);

    end loop;

           t_min := v_scores(x_x);

    for i in x_x..(x_x+10)loop

        v_sum := v_sum + v_scores(i);

    end loop;

    

    v_avg := v_sum/10;

    

    for j in x_x..(x_x+10) loop

        v_devsum := v_devsum + power((v_scores(j)-v_avg),2);

    end loop;

    

    for m in x_x..x_x+10 loop

        if v_max<v_scores(m) then

            v_max := v_scores(m);

        end if;

    end loop;

    

    for n in 2..(x_x+10) loop

        if v_min>v_scores(n) then

            v_min := v_scores(n);

        end if;

    end loop;

    

    v_dev := v_devsum/(9);

    

--직접 계산값 출력

    dbms_output.put_line('계산합계 : '||v_sum);

    dbms_output.put_line('계산평균 : '||v_avg);

    dbms_output.put_line('계산분산 : '||v_dev);

    dbms_output.put_line('계산표준편차 : '||SQRT(v_dev));

    dbms_output.put_line('계산최대 : '||v_max);

    dbms_output.put_line('계산최소 : '||v_min);

    dbms_output.put_line(' ');

    

-- 레코드에 입력

    for l in x_x..(x_x+10) loop

        v_sc.sid := l;

        v_sc.score := v_scores(l);

        insert into ex_scores

        values v_sc;

    end loop;

    

--select문

    select sum(score), avg(score), variance(score), stddev(score), max(score), min(score)

    into t_sum, t_avg, t_dev, t_std, t_max, t_min

    from ex_scores;

    

    dbms_output.put_line('쿼리합계 : '||t_sum);

    dbms_output.put_line('쿼리평균 : '||t_avg);

    dbms_output.put_line('쿼리분산 : '||t_dev);

    dbms_output.put_line('쿼리표준편차 : '||t_std);

    dbms_output.put_line('쿼리최대 : '||t_max);

    dbms_output.put_line('쿼리최소 : '||t_min);

end;

/

--create table ex_scores(

--    sid number primary key,

--    score number not null

--);

delete from ex_scores;