[20180515] PL SQL

기본설정

---

기본 문법

DECLARE -- 변수를 선언한다. 
  v_emprow emp%ROWTYPE; -- emp의 row타입을 가지고 있는 변수를 생성 >> java의 객체개념과 비슷 
BEGIN
  select *
    into v_emprow -- empno , ename , ,..... deptno >> select한 결과를 변수에 저장 
  from emp
  where empno=7788;
  
  DBMS_OUTPUT.PUT_LINE(v_emprow.empno || '-' || v_emprow.ename || '-' || v_emprow.deptno);
END; -- 끝

제어문

IF문

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vdeptno emp.deptno%TYPE;
  vname varchar2(20) := null; --기본값 설정
BEGIN
  select empno , ename , deptno
    into vempno , vename , vdeptno
  from emp
  where empno=7788;
  --제어문 if(조건문){실행문}
  IF(vdeptno = 10) THEN vname := 'ACC'; -- if(vdeptno==10) { vname = "ACC"}
    ELSIF(vdeptno=20) THEN vname := 'IT';
    ELSIF(vdeptno=30) THEN vname := 'SALES';
  END IF;
  DBMS_OUTPUT.PUT_LINE('당신의 직종은 : ' || vname);
END;

CASE문

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vdeptno emp.deptno%TYPE;
  v_name varchar2(20);
BEGIN
     select empno, ename , deptno
        into vempno, vename , vdeptno
     from emp
     where empno=7788;
     
--    v_name := CASE vdeptno >> 어떠한 조건이 딱 맞아 떨어질 때
--                WHEN 10  THEN 'AA'
--                WHEN 20  THEN 'BB'
--                WHEN 30  THEN 'CC'
--                WHEN 40  THEN 'DD'
--              END;
 
    v_name := CASE -- 조건이 유동적일 
                WHEN vdeptno=10  THEN 'AA'
                WHEN vdeptno in(20,30)  THEN 'BB'
                WHEN vdeptno=40  THEN 'CC'
                ELSE 'NOT'
              END;
    DBMS_OUTPUT.PUT_LINE('당신의 부서명:' || v_name);            
END;
 

LOOP

DECLARE
  n number :=0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('n value : ' || n);
    n := n + 1;
    EXIT WHEN n > 5;
  END LOOP;
END;
 

위와 같은 방법 이외에도 다음과 같이 활용할 수 있다 .

while을 활용한 LOOP문

DECLARE
  num number := 0;
BEGIN
  WHILE(num < 6) -- EXIT대신 써줄 수 있다.
  LOOP
    DBMS_OUTPUT.PUT_LINE('num 값 : ' || num);
    num := num +1;
  END LOOP;
END;

LOOP-FOR

BEGIN
  FOR i IN 0..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;

FOR문 활용해 1~100총 합 구하기

DECLARE
total number :=0;
BEGIN
  FOR i IN 1..100 LOOP
    total := total + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('1~100 총합 : ' || total);
END;

추가: ORACLE 11버젼 이후로는 CONTINUE라는 문법이 추가되었다.

 
DECLARE
  total number := 0;
BEGIN
  FOR i IN 1..100 LOOP
    DBMS_OUTPUT.PUT_LINE('변수 : ' || i);
    CONTINUE WHEN i > 5; --skip
    total := total + i; -- 1 , 2 , 3 , 4, 5
  END LOOP;
    DBMS_OUTPUT.PUT_LINE('합계 : ' || total);
END;
 

PL SQL활용

EXCEPTION의 활용

DECLARE
  v_empno emp.empno%TYPE;
  v_name  emp.ename%TYPE := UPPER('&name');
  v_sal   emp.sal%TYPE;
  v_job   emp.job%TYPE;
BEGIN
  select empno , job ,sal
    into v_empno, v_job , v_sal
  from emp
  where ename = v_name;
  
  IF v_job IN('MANAGER','ANALYST') THEN
     v_sal := v_sal * 1.5;
  ELSE
     v_sal := v_sal * 1.2;
  END IF;
  
  update emp
  set sal = v_sal
  where empno=v_empno;
  
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '개의 행이 갱신 되었습니다');
  
  --예외처리
  EXCEPTION
    WHEN NO_DATA_FOUND THEN --오라클이 제공하는 상수 변수 
       DBMS_OUTPUT.PUT_LINE(v_name || '는 자료가 없습니다');
    WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE(v_name || '는 동명 이인입니다');
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('기타 에러가 발생했습니다');
END;
/*
질의는 하나의 행만 RETURN 해야 합니다. PL/SQL 블록 내의 SELECT 문장은 다음 규칙을
적용하는 Embedded SQL 의 ANSI 범주에 속합니다. 질의의 결과는 하나의 행만을 RETURN 해
야  하고  하나의  행  이상  또는  행이  없는  것은  에러를  생성합니다.  PL/SQL 은
NO_DATA_FOUND 와 TOO_MANY_ROWS 를 예외로 블록의 예외 섹션에서 추적할 수 있는 표준 예
외를 조성하여 처리 합니다.
*/

지금까지는 기본문법이었고 이제 좀 더 고급 문법을 소개한다.

고급자원인 만큼 활용에 따른 리스크 또한 크기 때문에 주의해서 활용해야 한다 .

---

위의 실습은 한 건의 데이터 (하나의 row)만을 처리할 수 있는 방법이었다. 이제 다수의 row를 한번에 처리할 수 있는 CURSOR의 개념을 알아본다.

CURSOR

1. 행단위로  데이터를 처리하는 방법을 제공

2. 여러건의 데이터를 처리하는 방법을 제공

 

기본문법

DECLARE
          CURSOR 커서이름 IS 문자(커서가 실행할 쿼리)
   BEGIN
         OPEN 커서이름 (커서가 가지고 있는 쿼리를 실행)
             
         FETCH 커서이름 INTO 변수명들...
          --커서로 부터 데이터를 읽어서 원하는 변수에 저장
         CLOSE 커서이름 (커서닫기) 
   END
 

SQL CURSOR 의 속성을 사용하여 SQL 문장의 결과를 테스트할 수 있다.

[종 류 설 명]

• SQL%ROWCOUNT 가장 최근의 SQL 문장에 의해 영향을 받은 행의 수

• SQL%FOUND 가장 최근의 SQL 문장이 하나 또는 그 이상의 행에 영향을 미친다면 TRUE 로 평가한다.

• SQL%NOTFOUND 가장 최근의 SQL 문장이 어떤 행에도 영향을 미치지 않았다면 TRUE 로  평가한다.

• SQL%ISOPEN PL/SQL 이 실행된 후에 즉시 암시적 커서를 닫기 때문에 항상 FALSE 로 평가된다.

활용예시는 다음과 같다.

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vsal   emp.sal%TYPE;
  CURSOR c1  IS select empno,ename,sal from emp where deptno=30;
BEGIN
    OPEN c1; --커서가 가지고 있는 문장 실행
    LOOP -- 데이터 row 건수만큼 회전
      --Memory에 선택된 데이터를 올리낟. 
      /*
        7499 ALLEN 1600
        7521 WARD 1250
        7654 MARTIN 1250
        7698 BLAKE 2850
        7844 TURNER 1500
        7900 JAMES 950
      */
      FETCH c1 INTO vempno , vename, vsal; -- Memory에 올라간 데이터를 하나하나 접근한다.
      EXIT WHEN c1%NOTFOUND; -- 더이상 row 가 없으면 탈출
        DBMS_OUTPUT.PUT_LINE(vempno || '-' || vename || '-'|| vsal);
    END LOOP;
    CLOSE c1;
END;

위의 예시를 좀 더 간소화해 표현하면 다음과 같이 활용할 수 있다.

DECLARE
  CURSOR emp_curr IS  select empno ,ename from emp;
BEGIN
   
    FOR emp_record IN emp_curr  --row 단위로 emp_record변수 할당, for ~ in 구문은 내부적으로 open을 포함하고 있다
    LOOP
      EXIT WHEN  emp_curr%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_record.empno || '-' || emp_record.ename);
    END LOOP;
   
    CLOSE emp_curr;
END;

---

지금까지의 작업은 영속적으로 저장되지 않는다. 우리는 DDL명령어를 통해서 우리가 만든 커서를 영속적으로 저장할 수 있다. 즉, 영속적인 저장을 통해 언제든 별도의 코딩을 없이 객체의 형태로 호출해 활용 할 수 있다.

다음은 그 예시이다.

create or replace procedure usp_emplist
is
  BEGIN
    update emp
    set job = 'TTT'
    where deptno=30;
  END;

우리는 위의 코드에서 create or replace를 통해 생성과 수정이 동시에 가능함을 짐작할 수 있다. 위의 코드를 실행하면 

Procedure USP_EMPLIST이(가) 컴파일되었습니다.

라는 메시지가 표시되고, 이로써 우리는 usp_emplist 의 이름으로 프로시저를 생성해 저장하였다.  생성된 프로시저는 다음과 같이 확인할 수 있다.

이렇게 생성된 프로시저는 다음과 같은 코드로 실행시킬 수 있다. 

execute usp_emplist; -- 프로시저 실행
select * from emp where deptno=30; 
rollback; -- 프로시저는 롤백이 가능하다.

이러한 프로시저의 활용은 다음과 같은 장점을 지닌다.

1. 네트워크의 트래픽시간을 감소시킬 수 있다.

2. 네트워크상의 보안을 강화할 수 있다. 

이러한 프로시저도 파라미터를 받아 처리하도록 설정할 수 있다. 

-- procedure  는 parameter  종류 2가지
--1. input paramter : 사용시 반드시  입력          (IN : 생략하는 default)
--2. output parmater : 사용시 입력값을 받지 않아요 (OUT)
create or replace procedure app_get_emplist
(
  vempno IN emp.empno%TYPE,
  vename OUT emp.ename%TYPE,
  vsal   OUT emp.sal%TYPE
)
is
  BEGIN
    select ename, sal
      into vename , vsal
    from emp
    where empno=vempno;
  END;
 
--오라클 실행 테스트
DECLARE
  out_ename emp.ename%TYPE;
  out_sal   emp.sal%TYPE;
BEGIN
   app_get_emplist(7902,out_ename,out_sal);
   DBMS_OUTPUT.put_line('출력값 : ' || out_ename || '-' || out_sal);
END;

OUTPUT으로 처리한 값은 프로시저를 실행할 때 변수명을 작성해주면 return값으로 받을 수 있다(?)

이제 생성한 프로시저를 자바에서 활용하는 예제를 살펴보자 

생성할 프로시저는 다음과 같다

.

CREATE OR REPLACE PROCEDURE usp_EmpList
(
p_sal IN number,
 p_cursor OUT SYS_REFCURSOR --APP 사용하기 위한 타입(한 건 이상의 데이터를 select할 때 >> 내부적으로 cursor사용
)
IS
BEGIN
    OPEN p_cursor
       FOR
          SELECT empno, ename, sal FROM EMP WHERE sal > p_sal;
 END;

이 프로시저를 java에서 실행시키면 다음과 같다.

 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
 
import oracle.jdbc.OracleTypes;
 
/*
CREATE OR REPLACE PROCEDURE usp_EmpList
(
p_sal IN number,
 p_cursor OUT SYS_REFCURSOR --APP 사용하기 위한 타입
)
IS
BEGIN
    OPEN p_cursor
       FOR
          SELECT empno, ename, sal FROM EMP WHERE sal > p_sal;
 END;
*/
public class Ex08_Oracle_Procedure_Select {
 
    public static void main(String[] args) {
         Connection conn = null;
          //명령객체
          CallableStatement cstmt= null; //변경 (procedure를 처리하는 객체 
          ResultSet rs = null;
          try{
           Class.forName("oracle.jdbc.OracleDriver");
           conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "bituser", "1004"); 
              
           String sql = "{call usp_EmpList(?,?)}"; // 실제 Oracle에서는 execute라는 명령어를 활용해 실행한다는 점에서 헷갈릴 수 있다.
              cstmt = conn.prepareCall(sql);
              
              //usp_EmpList(?,?) input , output
              cstmt.setInt(1, 2000); // input처리를 위한 파라미터 등록 
              cstmt.registerOutParameter(2, OracleTypes.CURSOR); // output에 대한 지정 > output타입이  SYS_REFCURSOR로 지정되어 있다.
           boolean result = cstmt.execute();
              
           rs = (ResultSet)cstmt.getObject(2); // sql에서 2번 째 물음표(Output parameter)에 대한 처리 
          
           while(rs.next()){
            System.out.println(rs.getInt(1) +"/" + rs.getString(2) +"/" + rs.getInt(3));
           }
          
          }catch(Exception e){
          
          }finally{
           if(rs != null){try{rs.close();}catch(Exception e){}};
           if(cstmt != null){try{cstmt.close();}catch(Exception e){}};
           if(conn != null){try{conn.close();}catch(Exception e){}};
          }
 
    }
 
}