PL/SQL 실습

PL/SQL 실습

 

실습 1

 

 - 프로시저명 : printtemp

 

 - p_empno 변수를 파라미터로 가짐 ( emp테이블의 empno 데이터 타입을 가짐 )

 

 - v_dname 변수 선언 (dept 테이블의 dname컬럼의 데이터 타입)

 

 - v_eame 변수 선언 (emp 테이블의 ename 컬럼의 데이터 타입)

 

 - dept 테이블과 emp 테이블을 조인해서 ename, dname의 값을 선언한 변수에 저장

 

 - 조인 조건은 아래와 같음

 

create or replace procedure printtemp(p_empno IN emp.empno%type) is

    v_dname dept.dname%type;
    
    v_ename emp.ename%type;
    
begin

    select ename, dname into v_dname, v_ename
    
    from dept, emp
    
    where p_empno = emp.empno and dept.deptno = emp.deptno;  -> 조인 조건
    
    DBMS_OUTPUT.PUT_LINE(v_dname || ', ' || v_ename);
    
end;
/

exec prittemp(7566);

 

---

실습 2

 

 - dept_test(deptno, dname, loc) INSERT 실습

 

 - 프로시저명 : registdept_test

 

 - 파라미터로 3개의 변수를 선언하되 dept_test테이블에서 각각의 컬럼에 맞는 데이터 타입을 받아옴

 

 - 입력하는 인자값을 INSERT문을 통해 dept_test 테이블에 데이터 삽입

create or replace procedure registdept_test(p_deptno in dept_test.deptno%type, 
                            
                                            p_dname in dept_test.dname%type, 

                                            p_loc in dept_test.loc%type) is                                           
begin

    insert into dept_test (deptno, dname, loc) values (p_deptno, p_dname, p_loc);
    
end;
/

exec registdept_test(99, 'ddit','daejeon');

 

---

실습 3

 

 - UPDATE문 실습

 

create or replace procedure UPDATEdept_test (p_deptno in dept_test.deptno%type, 

                                             p_dname in dept_test.dname%type, 
                                             
                                             p_loc in dept_test.loc%type) is
begin

    update dept_test set dname = p_dname, loc = p_loc  where deptno = p_deptno;
    
    commit;
end;
/

exec UPDATEdept_test(99, 'ddit_m','daejeon');

 

---

복합 변수

 

1. %ROWTYPE

 

 - 특정 테이블의 행의 모든 컬럼을 지정할 수 있는 변수

 

     -> 즉, 테이블 내의 모든 컬럼의 데이터 타입을 참조

 

 - 사용 방법 : 변수명 테이블명%ROWTYPE

 

set SERVEROUTPUT ON;

declare

    v_dept_row dept%rowtype;
    
begin

    select * into v_dept_row
    
    from dept
    
    where deptno = 10;
    
    DBMS_OUTPUT.put_line(v_dept_row.deptno || ' ' || v_dept_row.dname || ' ' || v_dept_row.loc);

end;
/

 ------------------- 가서 결과 캡쳐 ----------------

 

---

2. RECORD

 

 - 개발자가 직접 여러개의 컬럼을 관리할 수 있는 타입을 생성하는 명령

 

 - JAVA에 비유하면 클래스를 선언하는 과정

 

 - 인스턴스를 만드는 과정은 변수 선언

 

  

   SYNTAXS

type 타입이름(개발자가 지정) is record(

      변수명1 변수타입,
      
      변수명2 변수타입,
);

 

    실습

declare

    type dept_row is record(
    
        deptno NUMBER(2),
        
        dname VARCHAR(14)
        
    );
    
    v_dept_row dept_row;   -> 이건 뭐지?
    
begin

    select deptno, dname into v_dept_row
    
    from dept
    
    where deptno = 10;
    
    DBMS_OUTPUT.put_line(v_dept_row.deptno || ' ' || v_dept_row.dname);
    
end;
/

 

---

TABLE TYPE 테이블 타입

 

- 점 : 스칼라 변수

 

- 선 : %ROWTYPE, RECORD TYPE

 

- 면 : TABLE TYPE

 

    고려사항) 1. 어떤 선(%ROWTYPE, RECORD TYPE)을 저장할 수 있는 지

    

                 2. 인덱스 타입은 무엇인지

 

 - 기존에 배운 스칼라 타입, ROWTYPE에서는 한 행의 정보를 담을 수 있었지만

 

   TABLE TYPE을 이용하면 여러 행의 정보를 담을 수 있다.

 

 - PL/SQL 에서는 JAVA와 다르게 배열에 대한 인덱스가 정수로 고정되어 있지 않고

 

   문자열도 가능하다

 

   그래서 TABLE 타입을 선언할 때에는 인덱스 타입도 명시를 해준다.

 

 - BINARY_INTEGER타입은 PL/SQL에서만 사용 가능한 타입으로 NUMBER 타입을 이용하여

 

   정수만 사용 가능하게끔 한 NUMBER 타입의 서브 타입이다.

 

---

 SYNTAXS

 

TYPE 테이블타입 명 IS TABLE OF 테이블.컬럼%TYPE

INDEX BY BINARY_INTEGER

변수명 테이블타입 명

 

---

실습

declare

    type dept_tab is table of dept%rowtype index by binary_integer;
    
    v_dept_tab dept_tab;
    
begin

    select * bulk collect into v_dept_tab
    
    from dept;
    
    기존 스칼라 변수, record 타입을 실습시에는
    
    한 행만 조회되도록 WHERE절을 통해 제한하였다.
    
    자바에서는 배열[인덱스 번호]
    
    table변수(인덱스 번호)로 접근
    
    for(int i = 0; i < 10; i++){
     }    
    
    for i in 1..v_dept_tab.count loop
    
    DBMS_OUTPUT.put_line(v_dept_tab(i).deptno || ' ' || v_dept_tab(i).dname);
    
    end loop;
    
end;
/

  * BULK COLLECT

 

  - 데이터 베이스에 암묵적, 명시적으로 한번만 질의를 하여도 다중행을 검색 가능

 

  - PL/SQL과 SQL문의 실행 엔진 사이에 문맥전환 수를 줄여주고, 데이터 검색 시 부담을 줄여준다.

 

  - 9i이후 부터 동적, 정적 SQL에서 이용 가능 (9i 이전에는 정적 SQL 에서만 사용 가능)

 

  - SELECT INTO, FETCH INTO, RETURNING INTO 절에서 사용 가능

 

  - 참조하는 컬렉션에는 스칼라 값만 저장할 수 있다. (문자열, 숫자, 날짜 등) 

  

    즉, 레코드 구조로 이루어진 컬렉션은 패치할 수 없다.

 

  - SQL엔진은 BULK COLLECT절에서 사용하는 컬렉션을 자동으로 초기화하고 확장한다.

 

    첫번째 인덱스부터 컬렉션을 채우기 시작하여 연속적으로 요소들을 삽입하고, 먼저 정의 되었던 요소들의 값은 

 

    덮어 씌운다.

 

단점

 

 - 성능은 향상되지만 더 많은 메모리를 소모함

 

 - 콜렉션의 메모리는 SGA(System Global Area)가 아닌 PGA(Program Global Area)에 저장되는데

 

   SGA 메모리는 Oracle Database에 연결된 모든 세션에서 공유되지만 PGA 메모리는 각 세션에 할당된다.

 

   따라서, 프로그램에 콜렉션을 채우기 위해 5MB의 메모리가 필요하고 100개의 동시 연결이 있는 경우 해당

 

   프로그램은 SGA에 할당된 메모리 이외에도 500MB의 PGA 메모리를 소비한다

 

   다행히도   LIMIT절을 사용해서 연산에 사용되는 메모리 양을 제어할 수 있다.