DECLARE
    TYPE my_array_type IS TABLE OF varchar2(20) INDEX BY pls_integer;   -- 1차원 배열 선언
    v_array my_array_type;
    
BEGIN
    v_array(0) := 'zoro';
    v_array(1) := 'one';
    v_array(2) := 'two';
    v_array(3) := 'three';
    v_array(4) := 'four';
    v_array(9) := 'nine';
    v_array(-10) := 'negative ten';
    
    DBMS_OUTPUT.PUT_LINE(v_array(-10));

    DBMS_OUTPUT.PUT_LINE(v_array.FIRST);
    DBMS_OUTPUT.PUT_LINE(v_array.LAST);
    
    /* 
    FOR i IN v_array.FIRST..v_array.LAST LOOP        -- 오류 : i 값이 연속적이지 않아 값을 못찾음
            DBMS_OUTPUT.PUT_LINE(v_array(i));        -- ORA-01403: no data found
    END LOOP;
    */
    
    FOR i IN v_array.FIRST..v_array.LAST LOOP
        IF v_array.EXISTS(i) THEN               -- i 값이 있다면 True
            DBMS_OUTPUT.PUT_LINE(v_array(i));
        END IF;
    END LOOP;   
END;
/
negative ten
-10
9
negative ten
zoro
one
two
three
four
nine

 

 

DECLARE
    TYPE my_array_type IS TABLE OF varchar2(20) INDEX BY pls_integer;   -- 1차원 배열 선언
    v_array my_array_type;
    idx pls_integer;
    
BEGIN
    v_array(0) := 'zoro';
    v_array(1) := 'one';
    v_array(2) := 'two';
    v_array(3) := 'three';
    v_array(4) := 'four';
    v_array(9) := 'nine';
    v_array(-10) := 'negative ten';
    
    idx := v_array.FIRST;
    
    WHILE idx IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(v_array(idx));
        idx := v_array.NEXT(idx);
    END LOOP;   
END;
/
negative ten
zoro
one
two
three
four
nine

 

DECLARE
    TYPE my_array_type IS TABLE OF varchar2(20) INDEX BY pls_integer;   -- 1차원 배열 선언
    v_array my_array_type;
    idx pls_integer;
    
BEGIN
    v_array(0) := 'zoro';
    v_array(1) := 'one';
    v_array(2) := 'two';
    v_array(3) := 'three';
    v_array(4) := 'four';
    v_array(9) := 'nine';
    v_array(-10) := 'negative ten';
    
    idx := v_array.LAST;                        -- 마지막부터
    
    WHILE idx IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(v_array(idx));
        idx := v_array.PRIOR(idx);              -- 뒤로 카운트
    END LOOP;   
END;
/
nine
four
three
two
one
zoro
negative ten

 

[문제] 배열 변수에 있는 100,101,102,103,104,200 사원들의 근무 연수를 출력하고
근무연수가 20년이상 되었으면 급여를 10% 인상한 급여로 수정하는 프로그램을 작성.

DECLARE
    TYPE id_arr_type IS TABLE OF number INDEX BY pls_integer;   
    v_id id_arr_type;

    v_years number;
    result varchar2(100);
    
BEGIN
    v_id(1) := 100;
    v_id(2) := 101;
    v_id(3) := 102;
    v_id(4) := 103;
    v_id(5) := 104;
    v_id(6) := 200;
    
    FOR i IN v_id.FIRST..v_id.LAST LOOP
        SELECT trunc(months_between(sysdate,hire_date)/12)  -- 결과가 단일행만 나와야 한다.
        INTO v_years
        FROM hr.employees
        WHERE employee_id = v_id(i);
        
        IF v_years >= 20 THEN
            UPDATE hr.employees
            SET salary = salary * 1.1
            WHERE employee_id = v_id(i);
            
            result := '10% 인상되었습니다.';
        ELSE
            result := '인상할 수 없습니다.';
        END IF;
        
    DBMS_OUTPUT.PUT_LINE(v_id(i)||'는 근무 연수가 '||v_years||'년 입니다. 급여는 '||result);
    
    END LOOP;
    
END;
/
100는 근무 연수가 20년 입니다. 급여는 10% 인상되었습니다.
101는 근무 연수가 18년 입니다. 급여는 인상할 수 없습니다.
102는 근무 연수가 22년 입니다. 급여는 10% 인상되었습니다.
103는 근무 연수가 17년 입니다. 급여는 인상할 수 없습니다.
104는 근무 연수가 16년 입니다. 급여는 인상할 수 없습니다.
200는 근무 연수가 20년 입니다. 급여는 10% 인상되었습니다.

 

[문제] 배열변수안에 있는 사원 번호 값을 기준으로 (100,110,200)
그 사원의 last_name, hire_date, department_name 정보를 레코드 변수에 담아놓은 후 화면에 출력

DECLARE
    TYPE id_arr_type IS TABLE OF number INDEX BY pls_integer;    --배열 타입 선언
    v_id id_arr_type;
    
    TYPE rec_type IS RECORD        -- 레코드 타입 선언
        (v_name hr.employees.last_name%type,
         v_date hr.employees.hire_date%type,
         v_dept_name hr.departments.department_name%type);
    v_rec rec_type;

BEGIN
    v_id(1) := 100;
    v_id(2) := 110;
    v_id(3) := 200;

    FOR i IN v_id.FIRST..v_id.LAST LOOP
        SELECT last_name, hire_date, (SELECT department_name
                                        FROM hr.departments
                                        WHERE department_id = e.department_id) department_name
        INTO v_rec
        FROM hr.employees e
        WHERE employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||v_rec.v_name||', 입사한 날짜는 '||v_rec.v_date||
                                ', 근무 부서이름은 '||v_rec.v_dept_name||' 입니다.');
    END LOOP;
END;
/
100 사원의 이름은 King, 입사한 날짜는 03/06/17, 근무 부서이름은 Executive 입니다.
110 사원의 이름은 Chen, 입사한 날짜는 05/09/28, 근무 부서이름은 Finance 입니다.
200 사원의 이름은 Whalen, 입사한 날짜는 03/09/17, 근무 부서이름은 Administration 입니다.

 

# nested table(중첩테이블)
- index by table 과 달리 인덱스키(요소번호)는 자동으로 1번부터 시작해서 최대 2G 까지 생성할 수 있다.

DECLARE
    TYPE tab_char_type IS TABLE OF  varchar2(10);
    v_city tab_char_type := tab_char_type('서울','대전','부산','광주');
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_city.FIRST);
    DBMS_OUTPUT.PUT_LINE(v_city.LAST);
    DBMS_OUTPUT.PUT_LINE(v_city.NEXT(2));
    DBMS_OUTPUT.PUT_LINE(v_city.PRIOR(3));

    FOR i IN v_city.FIRST..v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/
1
4
3
2
서울
대전
부산
광주

 

DECLARE
    TYPE tab_char_type IS TABLE OF  varchar2(10);
    v_city tab_char_type := tab_char_type('서울','대전','부산','광주');     -- 선언 시 크기 고정.
                                                                         -->> ORA-06533: Subscript beyond count
BEGIN
    v_city.extend;      -- 한개의 새로운 요소를 추가한다.
    v_city.extend(5);  -- N개의 새로운 요소를 추가한다.
    v_city.extend(3,1); -- 1번의 인덱스 값을 가지고 3개 복제
    v_city(5) := '대구';
    
    DBMS_OUTPUT.PUT_LINE(v_city.COUNT);
    DBMS_OUTPUT.PUT_LINE(v_city.FIRST);
    DBMS_OUTPUT.PUT_LINE(v_city.LAST);
    
    FOR i IN v_city.FIRST..v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/
13
1
13
서울
대전
부산
광주
대구





서울
서울
서울

 

DECLARE
    TYPE id_arr_type IS TABLE OF number;
    v_id id_arr_type := id_arr_type(100,110,200);
    
    TYPE rec_type IS RECORD
        (v_name hr.employees.last_name%type,
         v_date hr.employees.hire_date%type,
         v_dept_name hr.departments.department_name%type);
    v_rec rec_type;

BEGIN

    FOR i IN v_id.FIRST..v_id.LAST LOOP
        SELECT last_name, hire_date, (SELECT department_name
                                        FROM hr.departments
                                        WHERE department_id = e.department_id) department_name
        INTO v_rec
        FROM hr.employees e
        WHERE employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||v_rec.v_name||', 입사한 날짜는 '||v_rec.v_date||
                                ', 근무 부서이름은 '||v_rec.v_dept_name||' 입니다.');
    END LOOP;
END;
/
100 사원의 이름은 King, 입사한 날짜는 03/06/17, 근무 부서이름은 Executive 입니다.
110 사원의 이름은 Chen, 입사한 날짜는 05/09/28, 근무 부서이름은 Finance 입니다.
200 사원의 이름은 Whalen, 입사한 날짜는 03/09/17, 근무 부서이름은 Administration 입니다.

 

# varray(VARRAY-SIZE ARRAY) 가변길이 배열방식
- VARRAY 고정된 상한값이 있다.
- 최대 2G 까지 사용한다.

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF  varchar2(10);       -- VARRAY(5): 최대 5개 까지 지정
    v_city tab_char_type := tab_char_type('서울','대전','부산','광주');
BEGIN
    
    v_city(5) := '인천';  -- 오류
    -- ORA-06533: Subscript beyond count
    -- 5개로 지정했지만 위 선언에서 4개 사용으로 확정. 추가 입력이 안됨.

    v_city.EXTEND;
    v_city(5) := '인천';
    -- 확장 후 입력 가능

    v_city.EXTEND(2);   -- 오류
    -- ORA-06532: Subscript outside of limit
    -- 상한값 이상으로 EXTEND 할 수 없다.    


    v_city.DELETE(2);   -- 오류
    -- PLS-00306: wrong number or types of arguments in call to 'DELETE'
    -- VARRAY에서는 DELETE 메소드를 사용할 수 없다.

    v_city.TRIM;        -- 제일 뒤에 있는 요소 삭제
    v_city.TRIM(2);     -- 제일 뒤에 있는 n개 요소 삭제
    
    FOR i IN v_city.FIRST..v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;    
END;
/

 

DECLARE
    TYPE id_arr_type IS VARRAY(3) OF number;
    v_id id_arr_type := id_arr_type(100,110,200);
    
    TYPE rec_type IS RECORD
        (v_name hr.employees.last_name%type,
         v_date hr.employees.hire_date%type,
         v_dept_name hr.departments.department_name%type);
    v_rec rec_type;

BEGIN

    FOR i IN v_id.FIRST..v_id.LAST LOOP
        SELECT last_name, hire_date, (SELECT department_name
                                        FROM hr.departments
                                        WHERE department_id = e.department_id) department_name
        INTO v_rec
        FROM hr.employees e
        WHERE employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||v_rec.v_name||', 입사한 날짜는 '||v_rec.v_date||
                                ', 근무 부서이름은 '||v_rec.v_dept_name||' 입니다.');
    END LOOP;
END;
/

 

 

# 2차원배열
레코드 타입을 먼저 선언한 후 INDEX BY TABLE 생성 해야 한다.

FOR i IN 1..5 LOOP
    SELECT *
    INTO 2차원배열변수(i)
    FROM hr.departments
    WHERE department_id = i * 10;
END LOOP;
DECLARE
    TYPE dept_rec_type IS RECORD
        (id number, name varchar2(30), mgr number, loc number);     -- 레코드 타입 수동 선언
    v_rec dept_rec_type;  

BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_rec
        FROM hr.departments
        WHERE department_id = i * 10;

        DBMS_OUTPUT.PUT_LINE(v_rec.id||' '||v_rec.name||' '||v_rec.mgr||' '||v_rec.loc);
    END LOOP;
END;
/
DECLARE
    v_rec hr.departments%rowtype;       -- 레코드 선언, 칼럼명은 테이블 동일

BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_rec
        FROM hr.departments
        WHERE department_id = i * 10;

        DBMS_OUTPUT.PUT_LINE(v_rec.department_id||' '||v_rec.department_name||' '||v_rec.manager_id||' '||v_rec.location_id);
    END LOOP;
END;
/
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500

 

DECLARE
    TYPE dept_rec_type IS RECORD
        (id number, name varchar2(30), mgr number, loc number);
    v_rec dept_rec_type;
    
    TYPE dept_tab_type IS TABLE OF v_rec%type INDEX BY pls_integer;
    v_tab dept_tab_type;        -- 2차원 배열
    
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i)
        FROM hr.departments
        WHERE department_id = i * 10;
    END LOOP;
    
    FOR i IN v_tab.FIRST..v_tab.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(v_tab(i).id||' '||v_tab(i).name||' '||v_tab(i).mgr||' '||v_tab(i).loc);
    END LOOP;
END;
/
DECLARE
    TYPE dept_rec_type IS RECORD
        (id number, name varchar2(30), mgr number, loc number);         -- 레코드 선언
    
    TYPE dept_tab_type IS TABLE OF dept_rec_type INDEX BY pls_integer;  -- 레코드 타입을 그대로 사용한다.
    v_tab dept_tab_type;
    
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i)
        FROM hr.departments
        WHERE department_id = i * 10;
    END LOOP;
    
    FOR i IN v_tab.FIRST..v_tab.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(v_tab(i).id||' '||v_tab(i).name||' '||v_tab(i).mgr||' '||v_tab(i).loc);
    END LOOP;
END;
/
DECLARE
    TYPE dept_tab_type IS TABLE OF hr.departments%rowtype INDEX BY pls_integer; -- 테이블 그대로 사용 시 rowtype으로 선언
    v_tab dept_tab_type;
    
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i)
        FROM hr.departments
        WHERE department_id = i * 10;
    END LOOP;
    
    FOR i IN v_tab.FIRST..v_tab.LAST LOOP    -- 1~5 반복 출력
        DBMS_OUTPUT.PUT_LINE(v_tab(i).department_id||' '||v_tab(i).department_name||' '||v_tab(i).manager_id||' '||
                                v_tab(i).location_id);
    END LOOP;
END;
/
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500

 

■ CURSOR
- SQL문 실행 메모리 영역
- parse, bind, execute(active set), fetch(active set 결과를 화면에 출력할지, 프로그램이면 변수에 load하는 단계)
- 암시적 커서(Implicit Cursor)
    - 오라클이 자동으로 커서 관리를 한다.
    - SELECT...INTO 절
        - 반드시 1개 행 값만 FETCH해야 한다.
        - 0 : NO_DATE_FOUND
        - 2개이상 : TOO_MANY_ROWS
    - DML(INSERT, UPDATE, DELETE, MERGE), TCL(COMMIT, ROLLBACK, SAVEPOINT)
- 명시적 커서(Explicit Cursor)
    - 여러개의 행을 FETCH해야 한다면 명시적 커서를 이용해야 한다.
    - 프로그래머가 커서를 관리 해야한다.
    - 0건, 1건, 여러건의 행이든 상관없이 사용할 수 있다.

DECLARE
    /* 1. 커서 선언 */
    CURSOR emp_cur IS 
                SELECT last_name
                FROM hr.employees
                WHERE department_id = 20;
    
    -- v_name varchar2(30);
    v_name hr.employees.last_name%type;     -- 커서 내 칼럼 수 만큼 스칼라 변수 선언
    
BEGIN
    /* 2. 커서 open : 메모라 할당, SELECT문에 대해서 parse(문법체크,의미분석체크,실행계획), bind, execute */
    /* active set 결과*/
    OPEN emp_cur;
    
    /* 3. FETCH */
    /* active set 결과에 첫번째 데이터를 변수에 로드 작업한다. */
    /* 여러건의 데이터가 있으면 꼭 반복문안에서 fetch작업을 수행해야한다.*/
    LOOP
        FETCH emp_cur INTO v_name;
        EXIT WHEN emp_cur%NOTFOUND;     -- FETCH 행이 없으면 TRUE, 있으면 FALSE를 리턴하는 속성
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    
    /* 4. cursor close : cursor 해지 */    
    CLOSE emp_cur;
END;
/
Hartstein
Fay

 

DECLARE
    CURSOR emp_cur IS 
                SELECT e.last_name, e.salary, d.department_name
                FROM hr.employees e, hr.departments d
                WHERE e.department_id = 20
                AND d.department_id = 20;

    v_rec emp_cur%rowtype;      -- 레코드로 선언
    
BEGIN
    IF NOT emp_cur%ISOPEN THEN      -- 오픈 안되어있다면 커서오픈
        OPEN emp_cur;
    END IF;

    LOOP
        FETCH emp_cur INTO v_rec;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(emp_cur%ROWCOUNT);     -- FETCH 작업 행의 수 출력
    
    IF emp_cur%ISOPEN THEN      -- 커서가 오픈되어있다면 종료
        CLOSE emp_cur;
    END IF;
END;
/
DECLARE
    CURSOR emp_cur IS 
                SELECT e.last_name, e.salary, d.department_name
                FROM hr.employees e, hr.departments d
                WHERE e.department_id = 20
                AND d.department_id = 20;
BEGIN
    FOR v_rec IN emp_cur LOOP
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;
END;
/
BEGIN
    FOR v_rec IN (SELECT e.last_name, e.salary, d.department_name
                    FROM hr.employees e, hr.departments d
                    WHERE e.department_id = 20
                    AND d.department_id = 20) LOOP
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;
END;
/
Hartstein
13000
Marketing
Fay
6000
Marketing
2

 

■ 명시적 커서 속성
명시적커서이름%NOTFOUND : FETCH 한 행이 없으면 TRUE, 있으면 FALSE 를 리턴하는 속성
명시적커서이름%FOUND : FETCH 한 행이 있으면 TRUE, 없으면 FALSE를 리턴하는 속성
명시적커서이름%ROWCOUND : FETCH 한 행의 수를 리턴하는 속성
명시적커서이름%ISOPEN : 명시적 커서가 OPEN 되어 있으면 TRUE, 아니면 FALSE

 

'Oracle SQL' 카테고리의 다른 글

19 PL/SQL PROCEDURE  (0) 2023.11.01
18 PL/SQL EXCEPTION  (1) 2023.10.31
16 PL/SQL RECORD, Array  (0) 2023.10.27
15 PL/SQL WHILE LOOP, FOR, Implicit Cursor  (1) 2023.10.26
14 PL/SQL Variable, CASE, LOOP  (0) 2023.10.25
'Oracle SQL'의 다른글
  • 현재글17 PL/SQL Nested Table, VARRAY, Explicit Cursor

티스토리툴바