PL/SQL - 기본 구조

 

 

 

오라클에서 SQL 언어를 확장하기위해 사용하는 언어로서  SQL만으로는 처리하기 힘든 자료저장,프로시저,트리거등을

작성할대 쓰이게된다.

 

1. PL/SQL 구조

 

 

선언           ----   데이터변수등의 크기,형등을 선언한다.

 

실행          ----    SELECT,INSERT,UPDATE같은 DML문이나 조건이나 제어문을 사용한다.

 

예외처리    ----    예외를 처리한다.

 

 

 

 

2. STORED PROCEDURE 사용에 대해 

 

일반적인 SQL 문의 경우 우리가 입력한 쿼리문들은 공유풀(SHARED POOL)이라는 장소에 저장된다. 그리고 이 쿼리문을

다시 사용하게 되면 공유풀에서 꺼내 사용하게함으로서 쿼리문 처리 속도가 빠르다. 처음에 입력했던 쿼리문의 실행을 할때와 달리

같은 쿼리문을 다시 실행할때 속도를 보면 다시 실행했을때는 거의 쿼리문 처리 시간이 없음을보면 알수 있다.

 

그러나 STORED PROCEDURE는  프로시저가 생성되면 DB에 저장된다. 이것은 이미 Comfile과정을 거첬으므로 이를

다시 불러쓰기만 실행 하면된다. 그래서 실행한다는 의미에서

 

EXEC  프로시저명

 

을 이용해서 실행하게 된다.

 

한줄의 짧은 실행문이므로 SQL문보다 간단하고 네트워크를 전송시킬때도 유리하다.

 

 

 

3. SQL gateE에서 PL/SQL 사용하기

 

 

 

 

 

SQL GATE에서 위에 빨간 네모박스안에 'SQL 쿼리편집기' 란 메뉴와 'PL/SQL'쿼리 편집기'라는 메뉴가 있는데 이를통해

SQL과 PL/SQL 창을 새로 만들어 낼수 있다.

 

참고로 PL/SQL 창에서는 Stored Procedure 를 만들어 comfile후 DB에 저장하고

          SQL 창에서 이 저장된 프로시저를 불러내 EXEC 명령으로 실행시킨다.

 

 

4. PL/SQL 실제 사용예

 

TBL_MEMBER 이라는 사원정보가 있는 테이블을 예로들어 보자

이테이블에는 다음과같은 내용이 들어가 있다고 해보자

 

------------------------------------------------------------------

사원번호   사원명        주민번호                 성별           직급         월급

-----------------------------------------------------------------

EID           NEME        JUBN                 GENDER      JOB_ID      SAL

 

 

우리에게 주어진 미션은 다음과같다고 해보자.

' 사원번호 1002번인 필드에 대한  '이름 성별   직급  연봉' 에 대한 정보를 알아내라'

 

 

 

1) PL/SQL창에서 프로시저를 만들자.

 

   프로시저는 다음과 같은 구문으로 만들어진다. 그리고 프로시저의 결과가 매개변수를 통해서 리턴되어

   나중에 사용되어진다.  

 

   CREATE OR REPLACE PROCEDURE 프로시저명(매개변수인자)

 

참고> 프로시저 또한 권한이 있어야 만들수 있으므로 SYS에서 권한을 주자

         GRANT CREATE PROCEDURE TO NEWSCOTT

 

 

 

   

CREATE OR REPLACE PROCEDURE PD_SAWON(V_SANO IN NUMBER)  
IS

V_NAME VARCHAR2(10);
V_GENDER CHAR(2);
V_JOB_ID    VARCHAR2(10);
V_YEARPAY  NUMBER(6);        

BEGIN 
     SELECT NAME, CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남'
                  ELSE '여' END,
                  JOB_ID, 
                  SAL+12
                  INTO V_NAME,V_GENDER, V_JOB_ID, V_YEARPAY
                  FROM TBL_MEMBER

                  WHERE EID=V_SANO;

   DBMS_OUTPUT.PUT_LINE ('------------------------');
   DBMS_OUTPUT.PUT_LINE ('사원명 성별 직급 연봉');
   DBMS_OUTPUT.PUT_LINE (LPAD('-',20,'-'));
   DBMS_OUTPUT.PUT_LINE (V_NAME || ' ' || V_GENDER || ' ' || V_JIK || ' ' || V_YEARPAY);
END;

 

 

위를 하나하나뜯어보자.

 

 <STEP1>

 

    우리가 최종적으로 얻어야할것은 '사원번호'이다. 사원번호를알게되면 우린 사원의 개인정보를 모두 알수있기 떄문이다.

  

    즉 SQL 창에서

 

    EXEC  PD_SAWON(1002) 이라고 입력하게되면 1002번 사원번호를 가진 사원의 정보를 얻을수 있다. 

 

 

즉 , 이를위해 프로시저 결과값이 사원번호가 되야하므로 리턴될 매개변수를 NUMBER 형태로지정하였다.

 

 

<STEP2> 사원정보를 얻기위한 SQL문을 생각해보자..

 

  위에 SELECT 가 있는 쿼리문은 간편하게 보면 다음과 같다

 

   SELECT A INTO B 

   FROM TBL_MEMBER

   WHERE EID = V.SANO

 

 이 의미는 TBL_MEMBER테이블에서 EID가 V_SANO인 값 (이 값은 위에서 설명했다 시피 EXEC 문구에서 인자로 넣어진다는 것을

 기억하자)들을 B에 넣는다는 것이다. B는 기존에 테이블에서  추출된 값(우리가 얻어내야할 이름,직급,연봉,성별 column 이름들)인데

 이것들은 프로시저안에서 만들어진 새로운 변수(V_NAME,V_GENDER, V_JOB_ID, V_YEARPAY) 들에 넣고 있다.

 

 이렇게 프로지서안에서 새롭게 정의된 변수들은 먼저 타입과 형에 맞게 선언되어야하므로 IS 이하 구문에서 우리가 SELECT구문

에서추출해낸 값과 일치하도록 알맞은 문자형또는 숫자형으로 지정해 주었다.

 

 

 <STEP3> DBMS_OUTPUT.PUT_LINE ();

     

  이 구문은 PL/SQL 에서 결과물을 출력해주기 위한 구문이다. 여기서는 새로운 변수들((V_NAME,V_GENDER, V_JOB_ID, V_YEARPAY) 을 이용해서그 결과값을 출력하도록 하고 있다.

    

예를들어

 

  DBMS_OUTPUT.PUT_LINE ('HELLO');    HELLO 라는 문구를 출력해준다. 문자열에는 양끝에 ' ' 표시를 해준다.

  DBMS_OUTPUT.PUT_LINE (V_VALUES);  V_VALUES의 참조값을 출력해준다. 변수의 경우 ' ' 필요가없다.

 

 

 주의해야할것은 PL/SQL에서 매명령문마다 세미콜론(;)을 찍어주어야함을 잊지말자.

 

2) 이제 프로시저를 저장했으므로 이를  SQL창에서 실행해보자.

 

  EXEC  PD_SAWON(1002)  

 

  우리는 사원번호 1002번의 정보를 얻기 원하므로 프로시저에 인자값으로 1002를 넣어주면 결과값이 나올것이다.

 

 

<참고> SQL창에서  기본적으로  SQL 쿼리과문을 보기위한 창이 하단에 나와있다. 그러나 우리는 프로시저를 통한

           EXEC 실행의 결과값을 원하므로 SQL_GATE의 하단에 탭을 DBMS Output 으로 선택하면

           결과값을 나타내는 창이 나올것이다. 

 

 

 

 

 

그 창에서 아래와 같이 좌측 천번째 버튼을눌러 DBMS OUTPUT 켜기로 설정해 주면된다.

 

 

 

 

 

 

 <참고>  VIEW라는것도 하나의 테이블과같으므로 테이블 대신 VIEW를 기초로 프로시저를 만들수도 있다.

  만일 VIEW_MEMBER 라는 VIEW를 정의해 주었다면 사용법을 일반 테이블을 기본으로 프로시저를 만들떄와 같다.