PL/SQL

• Procedual Language SQL의 약자
• 절차적 언어의 특징인 변수, 분기문, 반목문, 예외처리 등의 기능이 지원된 SQL
• 모든 코드가 DB내부에 생성되고 컴파일되어 저장되기 때문에 실행 속도가 빠름
• 네트워크의 트래픽 감소
• 표준 문법이 없음
• Anonymous Block, Stored Procedure, User Defined Function, Package

1. 익명블록

• 기본적인 PL/SQL의 구조를 정의
• 이름이 없는 블록
  (기본구조)

  DECLARE
    선언부(변수,상수,커서)
  BEGIN
    실행부(sql 문으로 구성하여 비지니스 로직처리)
    [EXCEPTION 예외처리]
  END;

예시)정수형변수에 값을 배정하고 홀·짝을 판별하여 출력하시오.

ACCEPT NUM1 PROMPT '정수 입력 :'
DECLARE
  V_NUM NUMBER := 0; --변수를 선언하고 (':=')오른쪽에 있는 값을 변수에 저장하시오.
  /*초기화하지 않으면 NULL이 들어가서 오류가 발생된다.,
  NUMBER타입은 초기화해주자 BEGIN블록에서 오류가 발생할수 있다.*/
  V_MESSAGE VARCHAR2(100);
BEGIN
  V_NUM := TO_NUMBER('&NUM1'); --숫자타입의 값으로 변환되어 V_NUM에 저장
  IF MOD(V_NUM,2)=0 THEN 
     V_MESSAGE := V_NUM||'은 짝수';
  ELSE
     V_MESSAGE := V_NUM||'은 홀수';
  END IF;
  --
  DBMS_OUTPUT.PUT_LINE(V_MESSAGE); --줄단위로 출력
  --
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외발생'||
        SQLERRM); --SQLERRM SQL에러메시지
END;

1)변수

• 다른 응용프로그램 언어의 변수 개념과 동일
• 보통 일반 변수는 'V_'로,
• 매개변수는 'P_'로 부여

   --선언형식--
   변수명 변수타입 [:= 초기값];

• 변수의 타입
  • 표준 sql에서 사용하는 타입과 동일하며 추가적으로
    BOOLEAN, BINARY_INTEGER, PLS_INTEGER가 지원
  • BOOLEAN : 논리값(true, false,null)
  • BINARY_INTEGER,PLS_INTEGER :
    2147483647 ~ -2147483648 까지의 정수 처리
  • 참조타입
    • 테이블명.컬럼명%TYPE -- 해당 컬럼의 TYPE을 참조해서 동일한 타입으로 사용한다.
      ex)V_PD PROD.PROD_DELIVERY%TYPE;
    • 테이블명%ROWTYPE -- 테이블 전체의 TYPE을 가져온다.

예)회원테이블에서 회원의 직업을 입력 받아
회원번호, 회원명, 마일리지를 출력하는 익명 블록 작성

ACCEPT A_JOB PROMPT '직업 : '
DECLARE
  V_NAME MEMBER.MEM_NAME%TYPE;
  V_ID MEMBER.MEM_ID%TYPE;
  V_MILE MEMBER.MEM_MILEAGE%TYPE;
  CURSOR CUR_MEM(V_JOB MEMBER.MEM_JOB%TYPE) --커서 sql명령의 행에 영향을 받은 집합
  IS
    SELECT MEM_ID, MEM_NAME, MEM_MILEAGE -- 명시적 커서, CUR_MEM이라고 하겠다.
      FROM MEMBER
     WHERE MEM_JOB=V_JOB;
BEGIN
  OPEN CUR_MEM('&A_JOB');
  LOOP
     FETCH CUR_MEM INTO V_ID,V_NAME,V_MILE; 
     EXIT WHEN CUR_MEM%NOTFOUND; --커서에서 더이상 꺼내올 자료가 없을때 루프를 끝내겠다.
     DBMS_OUTPUT.PUT_LINE(V_ID||', '||V_NAME||', '||V_MILE);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(CUR_MEM%ROWCOUNT);
  CLOSE CUR_MEM;
END;
SELECT MEM_ID,MEM_NAME,MEM_MILEAGE FROM MEMBER WHERE MEM_JOB LIKE '주부';

2) 상수

• 다른 응용 프로그램 언어의 상수 개념과 동일]
  (상수 선언 형식)
  식별자(변수명) CONSTANT 타입 [:=초기값];

3) 커서

• SQL 명령의 결과 집합
• 쿼리 결과를 읽거나 수정, 별도의 작업을 수행하기 위해 사용
  EX)커서로 회원ID,이름,마일리지를 생성시키고
  해당 회원의 성별까지 출력하시오
• 커서 사용 단계
  • 생성(DECLARE) -> OPEN -> FETCH -> CLOSE(BEGIN)
• 커서 선언 형식

 CURSOR  커서명[(매개변수 타입)]
 IS
      SELECT 문;

• '매개변수'에 값을 배정하는 곳은 OPEN문에서 수행

• 실행영역에서 사용형식
  (1) OPEN 커서명(값 [,값, ...]);
  (2) FETCH : 커서에 존재하는 데이터를 행단위로 읽어 변수에 저장
  FETCH 커서명 INTO 변수명[,변수명,...]
  . '변수'는 선언부에서 선언된 변수
  . 커서문 내의 SELECT 문의 SELECT절에 기술된 컬럼의 순서와 갯수, 타입이 일치
  . FETCH문은 반복문 안에 기술
  (3) CLOSE 커서명;
  . 사용이 종료된 커서를 닫아준다.
  . 한번 CLOSE되면 더 이상 접근할 수 없다.
  (4) 커서 속성

  ---

  커서명%ISOPEN -> 커서의 OPEN여부 --(OPEN 이면 참, 아니면 거짓)
  커서명%FOUND -> 커서에 읽어올 자료가 있으면(결과에 한행이라도 존재하면)
  참, 없으면 거짓
  커서명%NOTFOUND -> 커서명%FOUND의 반대
  커서명%ROWCOUNT -> 커서 결과 집합에 존재하는 전체 행의 수

  ---

  묵시적 커서의 속성은 '커서명'대신 'SQL'이사용