[데이터베이스] PL/SQL을 활용한 프로시저

PL/SQL

SQL 만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그램 언어

구성 키워드	필수 / 선택	설명
DECLARE(선언부)	선택	실행에 사용될 변수, 상수, 커서 등을 선언
BEGIN(실행부)	필수	조건문,반복문,SELECT,DML 함수 등을 정의
EXCEPTION(예외처리부)	선택	PL/SQL 실행 도중 발생하는 오류(예외사항)을 해결하는 문장 기술

 

PL/SQL 블록의 기본 방식

DECLARE
       [실행에 필요한 여러 요소 선언];
BEGIN
      [작업을 위해실제 실행하는 명령어];
EXCEPTION 
      [PL/SQL 수행 도중 발생하는 오류 처리];
END; 
/

작성시 주의할 점

PL/SQL 블록을 구성하는 DECLARE, BEGIN 등에는 세미콜론(;)을 사용하지 않음. 각 부분에서 실행할 문장 끝에는 세미콜론 붙임. 내부에서 한 줄 주석과 여러 줄 주석 사용 가능 ((--),(/* ~*/) 

PL/SQL 작성을 마치고 실행하기 위해 슬래시(/)를 사용

 

변수 선언과 사용

선언부에 작성하고 실행부에서 활용함

기본형식: 변수이름 변수형 : = 겂 또는 값이 도출되는 여러 표현식; 또는 pEmpno EMP.EMPNO%TYPE 으로 적용 가능  

 

상수 정의

변수이름 CONSTANT 자료형 := 값 또는 값을 도출하는 여러 표현식;

변수 기본값 지정

변수이름  자료형 DEFAULT 값 또는 값 도출되는 여러 표현식;  

변수 NULL 값 저장 막기

변수이름 자료형 NOT NULL := 또느느 DEFAULT 값 또는 값이 도출되는 표현식

 

변수 이름 (식별자) 규칙

같은 블록 안에서 식별자는 고유해야 하며 중복될 수 없음

대소문자를 구분하지 않음

테이블 이름 붙이는 규칙과 동일

1. 문자로 시작 2.30BYTE 이하(영어 30글자 한글 13글자 그러나 한글은 되도록쓰지말것. ) 3. 숫자(0-9), 특수문자 ($,#,_) TKDYD RKSMD

4. SQL 키워드가 아닐 것 (EX. SELECT)  

 

여기서의 BOOLEAN 은 NULL 값도 포함해 총 3개 (TRUE, FALSE, NULL)

--PL/SQL
--출력 함수인 DBMS_OUTPUT.PUT.LINE 사용시 출력용
SET SERVEROUTPUT ON;

--PL/SQL의 기본 문법
DECLARE
    DATA EMP%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('PL/SQL 시작.......');
    
    SELECT *
      INTO DATA
      FROM EMP
     WHERE EMPNO='2001';
     
     DBMS_OUTPUT.PUT_LINE(DATA.EMPNO||','||DATA.ENAME || ',' || DATA.JOB);
END;
/

 

 

IF 조건문

IF 조건식 THEN 수행할 명령어 ; 

END IF;

 

IF - THEN

IF - THEN - ELSE

IF - THEN - ELSEIF

 

LOOP

WHILE LOOP

FOR LOOP

Cusor FOR LOOP

 

EXIT

EXIT-WHEN

...

사진참고

 

프로시저는 등록을 아예 해두고 쓸 수 있는 것, (배치에서 사용 가능)

--프로시저
CREATE OR REPLACE PROCEDURE InsertEMP
--CREATE와 AS 사이는 매게변수의 역할을 한다. 지금 여기! 
(
    P_ENAME IN VARCHAR2
)
AS
--BEGIN에서 사용할 실질적인 변수 정의는 여기
    P_EMPNO CHAR(4);    --P_EMPNO EMP.EMPNO%TYPE; //EMPNO와 타입을 맞추라는 뜻
BEGIN
    DBMS_OUTPUT.PUT_LINE('InsertEMP 시작...');
    INSERT INTO EMP (EMPNO, ENAME, HIREDATE)
    VALUES (EX_EMPNO_SEQ.NEXTVAL, P_ENAME, SYSDATE);
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE ('이름은 '||P_ENAME);
END;
/

--프로시저 실행

EXEC InsertEMP('홍길동');

SELECT MAX(EMPNO) FROM EMP;

SELECT * FROM EMP ORDER BY EMPNO DESC;

프로시저 검색해서찾기

 

 

프로시저 안에서 LOOP 사용하기

 

 

프로시저 - 커서

커서
/*
    반환되는 수행결과가 여러 행일 때 이를 처리하기 위해 사용. 
    1. 커서 선언
    CURSOR 커서이름 IS SELECT문장;
    2. 커서 오픈
    OPEN 커서이름;
    3. 커서로부터 데이터 읽어오기(LOOP END 반복문 사용) 
    FETCH 커서이름 INTO 저장할 로컬 변수(프로시저에서 사용할...)
    4. 커서 닫기
    CLOSE 커서이름;
    
--커서 속성
    %FOUND : 마지막으로 얻은 커서 결과 SET에 레코드가 있으면 TRUE
    %NOTFOUND : 마지막으로 얻은 커서 결과 SET 에 레코드가 없으면 TRUE 
    %ROWCOUNT : 커서에서 얻은 레코드 수 반환. 
    %ISOPEN : 커서가 열렸고 아직 닫히지 않은 상태일 때에 TRUE

 

 

CREATE OR REPLACE PROCEDURE EX_SAMPLE(E_JOB IN EMP.JOB%TYPE)
IS
    NAME EMP.ENAME%TYPE;
    CURSOR c_name IS SELECT ENAME FROM EMP WHERE JOB LIKE '%'||E_JOB||'%'; --커서 선언
BEGIN
    OPEN c_name; --커서 오픈
    DBMS_OUTPUT.PUT_LINE('++++++++++++++++++++++++++++++++++++++++++++++++++');
    LOOP 
        FETCH c_name INTO NAME;     --커서로부터 데이터 읽기
        EXIT WHEN c_name%NOTFOUND;  --커서에 데이터가 없을 때에 반복문 종료. 
        
        DBMS_OUTPUT.PUT_LINE(NAME||'님의 직급은 ' || E_JOB ||'입니다.');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('커서 처리건수 " '|| c_name%ROWCOUNT);
    
    IF c_name%ISOPEN THEN 
         DBMS_OUTPUT.PUT_LINE('커서가 열려 있습니다.');
        CLOSE c_name; --커서 닫기
    ELSE 
             DBMS_OUTPUT.PUT_LINE('커서가 닫혀 있습니다.');
    END IF;

END;
/

EXEC EX_SAMPLE ('팀장');