본문 바로가기
정보처리기사/필기

[정보처리기사] Part02-02. 데이터조작 프로시저 작성

by 채연2 2023. 1. 8.

 

 

목차

 

  프로시저

  프로그램 디버깅

  단위테스트 도구

 

 

프로시저

 

정의

  • DB에서의 프로시저(Procedure) : 저장 프로시저 또는 스토어 프로시저를 의미
  • 저장 프로시저 : 일련의 쿼리를 마치 하나의 모듈처럼 실행하기 위한 쿼리의 집합
  • 함수와의 차이는 리턴 값 유무에 따른 차이
  • 서버 측에 탑재되어 수행됨
  • 서버 측에서 사용됨으로서 일관성이 보장되고, 관리가 쉬워지며 유지보수가 용이함

 

PL/SQL 작성 방법 (Procedural Language extension to SQL)

  • 절차형 데이터 조작 프로시저
  • 오라클(Oracle)에서 지원하는 프로그래밍 언어
  • PL/SQL Block 내에서 SQL의 DML(데이터 조작어)문과 Query문, 변수 정의, 절차형 언어[조건 처리(if), 반복 처리(loop, while, for)] 등을 지원하는 강력한 트랜잭션 언어
  • SQL 문장에서 변수 정의, 조건 처리, 반복 처리 등을 지원하며, 오라클 자체에 내장되어 있는 프로시저 언어

 

PL/SQL 구조

 선언부
(Declare)		 실행부에서 참조할 모든 변수, 상수, Cursor, Exception 선언
실행부
(Begin/End)		 Begin과 End 사이에 기술되는 영역
데이터를 처리할 SQL 문과 PL/SQL 블록을 명세
예외 처리부
(Exception)		 실행부에서 에러가 발생했을 경우 수행될 문장을 명세

 

PL/SQL 작성 방법

  • 블록 내에서는 한 문장이 종료할 때마다 세미콜론 (;) 사용
  • END 뒤에 세미콜론 (;) 사용하여 하나의 블록이 끝났다는 것을 명시
  • 주석 : 단일행 (--), 여러행 (/* */)
  • PL/SQL 블록은 행에 / 가 있으면 종결됨

 

PL/SQL 활용한 저장형 객체 활용

  • 익명 블록 : 이름이 없는 PL/SQL 블록
  • 이름이 있는 블록 : DB의 객체로 저장되는 블록
    • 함수 (function) : 리턴 값을 반드시 반환해야 하는 프로그램
    • 프로시저 (Procedure) : 리텅 값을 하나 이상 가질 수 있는 프로그램
    • 패키지 (Package) : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
    • 트리거 (Trigger) : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록

 

Function

  • 대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용할 수 있음
  • 반드시 반환될 값의 데이터 타입을 Return문에 선언해야 함
  • PL/SQL 블록 내에서 Return문을 통해서 반드시 값을 반환해야 함
CREATE OR REPLACE FUNCTION function_name [(argument ... IN type ...)]
RETURN datatype		- - 반환되는 값의 datatype 임
IS
	[변수 선언 부분]
BEGIN
	[PL/SQL Block]
                    - - PL/SQL 블록에는 적어도 한 개의 RETURN문이 있어야 함
                    - - PL/SQL 블록은 함수가 수행할 내용을 정의한 몸체 부분임
END;

 

예제

CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;

   CURSOR c1 IS
   SELECT course_number
     FROM courses_tbl
     WHERE course_name = name_in;

BEGIN
   OPEN c1;
   FETCH c1 INTO cnumber;

   IF c1%NOTFOUND THEN
      cnumber := 9999;
   END IF;

   CLOSE c1;

RETURN cnumber;

EXCEPTION
WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
  • varchar2 타입의 name_in이라는 매개변수 하나와 number 타입을 반환
  • CURSOR : Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터
    • 커서 선언 : CURSOR [커서이름] IS [SELECT 구문];
    • 커서 열기 : OPEN [커서이름];
    • 커서 패치 : FETCH [커서이름] INTO [로컬변수];
    • 커서 닫기 : CLOSE [커서이름];
  • %NOTFOUND: FETCH한 데이터가 행을 반환하지 않으면 TRUE
  • EXCEPTION : 예외처리
  • RAISE_APPLICATION_ERROR : 오류코드 -20000부터 -20999의 범위 내에서 사용자 정의 예외 생성 가능

Stored Procedure

  • 특정 작업을 수행할 수 있는 이름이 있는 PL/SQL 블록
  • 매개 변수를 받을 수 있고 반복적으로 사용할 수 있는 Object
  • 보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 DB에 저장하기 위해 생성
  • CREATE OR REPLACE 구문 사용하여 생성
  • IS로 PL/SQL 블록을 시작
  • LOCAL 변수는 IS와 BEGIN 사이에 선언
CREATE OR REPLACE PROCEDURE 프로시저명 ( 변수명
    IN 자료형             -- 실행 환경에서 프로그램으로 값을 전달
    OUT 자료형            -- 프로그램에서 실행 환경에서 값을 전달
    IN OUT 자료형         -- 실행 환경에서 프로그램으로 값을 전달하고,
)                        -- 다시 프로그램에서 실행환경으로 변경된 값을 전달
IS
    [변수의 선언]
BEGIN
    [PL/SQL Block]
                        -- SQL문장, PL/SQL 제어 문장
    [EXCEPTION]         --→ 선택
                        -- error가 발생할 때 수행되는 문장
END;
/

-- 실행 방법
-- 1. 매개변수가 있는 함수(OUT) 호출 시
EXEC[UTE] 프로시저명(인자);
    variable 변수명 자료형;
    PRINT 변수명;         -- 저장된 값 출력
    
-- 2. 매개변수가 없는 함수(IN) 호출 시
EXEC[UTE] 프로시저명;
EXEC[UTE] 프로시저명();

 

예제

CREATE OR REPLACE PROCEDURE del_dept(delNo number)
    IS
    BEGIN
    	DELETE FROM dept3
        WHERE deptno=delNo;
        DBMS_OUTPUT.PUT_LINE('#삭제 수행 완료');
    END;
/
EXECUTE del_dept(20);        -- 실행문

--

CREATE OR REPLACE PROCEDURE del_dept(delNo IN number, tomorrow OUT varchar2)
    IS
    BEGIN
        DELETE FROM dept3
        WHERE deptno=delNo;
        DBMS_OUTPUT.PUT_LINE('#삭제 수행 완료');
            COMMIT;
            SELECT TO_CHAR(sysdate+1, 'yy/mm/dd') INTO tomorrow FROM dual;
    END;
/
variable tomorrow varchar2(15).   -- 리턴 값을 받을 변수 선언
EXEC del_dept(40, :tomorrow);
print tomorrow;                   -- 리턴 값 확인

Package

  • Oracle DB에 저장되어 있는 서로 관련 있는 PL/SQL 프로시저와 함수들의 집합

 

패키지 선언부
  • 패키지에 포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을 선언
  • 패키지 선언부에서 선언한 모든 요소들은 패키지 전체(Public)에 적용됨
CREATE [OR REPLACE] PACKAGE 패키지명 IS:AS
    [변수 선언절] [커서 선언절] [예외 선언절]
    [PROCEDURE 선언절] [FUNCTION 선언절]
END 패키지명;

 

예제

CREATE OR REPLACE PACKAGE ex_pkg IS            -- 패키지 선언
    FUNCTION func_1(P_PRODUCT_ID IN number)    -- 패키지로 묶을 함수
    RETURN varchar2;
    
    PROCEDURE proc_1;                          -- 패키지로 묶을 프로시저1
    PROCEDURE proc_2(P_PRODUCT_ID IN number);  -- 패키지로 묶을 프로시저2
END ex_pkg;

 

패키지 본문
  • 패키지에서 선언된 부분의 실행을 정의
CREATE [OR REPLACE] PACKAGE BODY 패키지명 IS:AS
    [변수 선언절] [커서 선언절] [예외 선언절]
    [PROCDURE 선언절] [FUNCTION 선언절]
END 패키지명;

 

예제

CREATE OR REPLACE PACKAGE BODY ex_pkg IS
    -------------- 패키지 선언부 함수 --------------
    FUNCTION func_1(P_PRODUCT_ID IN number)
    RETURN varchar2
    IS V_PRODUCT_NAME varchar(100);
    BEGIN
        SELECT PRODUCT_NAME INTO V_PRODUCT_NAME
        FROM PRODUCTS
        WHERE PRODUCT_ID = P_PRODUCT_ID;
        RETURN NVL(V_PRODUCT_NAME, '해당제품없음');
    END;
    
    -------------- 패키지 선언부 프로시저1 --------------
    PROCEDURE proc_1
    IS
    	CURSOR ex_cur IS
        SELECT PRODUCT_ID, PRODUCT_NAME
        FROM PRODUCTS;
    BEGIN
        FOR i IN ex_cur LOOP
            DBMS_OUTPUT.PUT_LINE(' 제품ID: ' || i.PRODUCT_ID);
            DBMS_OUTPUT.PUT_LINE(' 제품명: ' || i.PRODUCT_NAME);
        END LOOP;
        EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
    END proc_1;
    
    -------------- 패키지 선언부 프로시저2 --------------
    PROCEDURE proc_2(P_PRODUCT_ID IN number)
    IS
    	CURSOR ex_cur IS
        SELECT PRODUCT_ID, PRODUCT_NAME
        FROM PRODUCTS
        WHERE PRODUCT_ID=P_PRODUCT_ID;
    BEGIN
        FOR i IN ex_cur LOOP
            DBMS_OUTPUT.PUT_LINE(' 제품ID: ' || i.PRODUCT_ID);
            DBMS_OUTPUT.PUT_LINE(' 제품명: ' || i.PRODUCT_NAME);
        END LOOP;
        EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
    END proc_1;
END ex_pkg;

Trigger

  • 특정 Table에 INSERT, UPDATE, DELETE 문이 적용되면 자동으로 수행되는 PROCEDURE
  • rollback, commit, savepoint 문은 사용 불ㄹ가
  • TABLE과는 별도로 DB에 저장됨
  • VIEW가 아닌 TABLE에 대해서만 정의 가능
  • DBMS_OUTPUT.PUT_LINE을 출력하기 위해 'set serveroutput on' 사용
CREATE [OR REPLACE] TRIGGER 트리거명
    [시점] [이벤트] [OF] ON 테이블명
    [FOR EACH ROW]
    [WHEN]
DECLARE
    변수 선언 ...
BEGIN
    ...
END;

 

예제

CREATE OR REPLACE TRIGGER recv_trigger
    AFTER INSERT ON recv
    FOR EACH ROW
DECLARE
    v_cnt number;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM stock
    WHERE p_id=:NEW.p_id;
        
    IF(v_cnt=0) THEN
        INSERT INTO stock VALUES(:NEW.p_id, :NEW.p_cnt, :NEW.p_price * :NEW.p_cnt);
    ELSE
        UPDATE stock SET
        p_cnt = p_cnt + :NEW.p_cnt,
        p_price = p_price + (:NEW.p_cnt * :NEW.p_price)
        WHERE p_id = :NEW.p_id;
    END IF;
END;

 

프로그램 디버깅

 

정의

  • 개발자가 만든 프로그램이 정해진 입력 자료를 받아 정확한 출력을 하는지 검증하는 과정
  • 만들어진 DB 프로시저에 대해 디버깅 도구를 활용하여 객관적인 검증 과정을 수행해야 함

 

프로그램 디버깅 도구

  • SQL *Plus 도구 이용
    • SQL *Plus : 오라클에서 제공하는 도구로서, SQL을 DBMS 서버에 전송하여 처리 가능
SQL SQL *Plus
DB와 통신하는 언어 SQL 명령어를 서버에 전송하는 도구
ANSI 표준에 기초 Oracle 사 제공 도구
데이터와 테이블에 대한 정의 가능 데이터에 대한 어떤 정의도 불가능
SQL buffer 사용 SQL buffer 사용하지 않음
여러 행 입력 가능 여러 행 입력 불가
명령어 실행 시 종료 문자 (;) 사용 명령어 실행 시 종료 문자 (;) 사용 안함
키워드 축약 불가 키워드 축약 가능

 

유형 SQL *Plus 명령어 내용
파일 명령어 EDIT {파일명} 버퍼의 내용을 편집기로 불러옴
SAVE {파일명} 버퍼의 내용을 파일에 저장
STA[RT] {파일명} 저장된 SQL script 실행
GET {파일명} 파일의 내용을 버퍼로 읽어옴
SPOOL {파일명} 조회 결과를 파일로 저장
SPOOL OFF 조회 결과를 파일로 저장하지 않음
HOST (!=와 동일한 효과) 운영체제(shell)로 빠져 나감
EXIT 운영체제(O/S) prompt로 빠져 나감
CONNECT {uid/pwd} 다른 사용자로 접속 시 사용
COL col FOR '999,999' [A15] Col 내용을 일정 Format으로 변경
편집 명령어 A {문자스트링} 현재 버퍼 끝에 새로운 문자 스트링 추가
C 현재 행의 문자열 치환
L 버퍼의 전체 리스트 출력
I 버퍼에 새로운 행을 추가
DEL n 현재 행을 삭제
N(숫자) 현재 행을 출력
CLEAR BUFFER 버퍼의 전체 내용을 삭제
실행 명령어 START {파일명} SQL script 실행 시
@ {파일명} SQL script 실행 시 : START와 동일
RUN {파일명} 버퍼의 내용 실행 시
/ 버퍼의 내용 실행 시
환경 명령어 SET ECHO {off | on} SQL script 실행 시 명령어 출력 여부
SET FEED[BACK] {6 | n | off | on} 조회 결과 메시지 출력 여부
SET HEAD[ING] {on | off} 컬럼 Head 출력 여부
LINE[SIZE] {80 | n} 출력될 한 라인의 길이
PAGE[SIZE] {24 | n} 출력 page당 라인 수
PAU[SE] {off | on} 화면 이동제어
SQLPREFIX {# | c} SQL 명령어 사이에 SQL *Plus 명령어 사용 시
NULL {text} NULL 값을 대체할 text 정보 설정 시
SERVEROUTPUT {on | off} PL/SQL 처리 결과를 화면에 출력하고자 할 때
SPACE {1 | n} 출력된 컬럼 간 여유 공간 설정 시
UNDERLINE {기호 | on | off} 컬럼 heading 밑에 사용될 underline 설정
WRAP {on | off} 컬럼들이 지정된 Linesize 초과 시 출력 여부
형식 명령어 COLUMN 컬럼의 FORMAT 변경 시
TTITLE 보고서 제목 설정 시
BTITLE 보고서 꼬릿말 설정 시
BREAK 컬럼 또는 행의 값이 바뀔 때마다 새로운 보고서 Format 설정 시
대화 명령어 DEFINE CHAR 데이터 형의 사용자 변수 생성
UNDEFINE 정의한 사용자 변수 해제
PROMPT PROMPT 지정
ACCEPT 변수 생성하여 특정 컬럼에 가변 값을 입력

 

단위테스트 도구

 

정의

  • Oracle DBMS는 모든 데이터 조작 프로시저에 대한 테스트 환경으로 SQL *Plus 라는 도구를 제공
  • 데이터 조작 프로시저 테스트를 위해 PL/SQL 기반 명령어들을 SQL *Plus 도구를 이용하여 단위 테스트 수행

 

PL/SQL 테스트

DBMS_OUTPUT 패키지 활용

  • 메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스를 제공하는 패키지인 DBMS_OUTPUT을 코드에 포함
CREATE OR REPLACE PROCEDURE type_test (p_empNo IN emp.empNo%TYPE)
IS
    v_empNo emp.empNo%TYPE;        -- %TYPE 데이터형 변수 선언
    v_eName emp.eNAme%TYPE;
    v_sal emp.sal%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT empNo, eName, sal       -- %TYPE 데이터형 변수 사용
    INTO v_empNo, v_eName, v_sal
    FROM emp
    WHERE empNo = p_empNo;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 :' || v_empNo);    -- 결과 값 출력
    DBMS_OUTPUT.PUT_LINE('사원이름 :' || v_eName);
    DBMS_OUTPUT.PUT_LINE('사원급여 :' || v_sal);
END;
구분 설명
DBMS_OUTPUT.DISABLE 메시지 버퍼 내용 삭제
DBMS_OUTPUT.ENABLE 메시지 버퍼 내용 할당
DBMS_OUTPUT.PUT 메시지 버퍼에 내용 저장 시 메시지의 마지막 라인 끝에 새로운 라인 문자(EOL, End Of Line)가 추가되지 않음
DBMS_OUTPUT.PUT_LINE 메시지 버퍼에 저장 시 PUT과 달리 메시지 끝에 새로운 라인 문자(EOL)가 추가됨
DBMS_OUTPUT.GET_LINE 한 번 호출될 때마다 하나의 라인 만을 읽어옴
DBMS_OUTPUT.GET_LINES 지정된 라인을 모두 읽어옴

 

DBMS_OUTPUT 패키지 실행 방법

  • PL/SQL 처리 결과를 화면에 출력하기 위한 'SERVEROUTPUT'을 ON 시키고, 실행하고자 하는 PL/SQL 블록 또는 저장 객체명을 호출함
  • PL/SQL 실행 시 오류가 발생하면 'SHOW ERRORS' 명령어를 통해 오류 내용 확인하여 조치함
SQL > SET SERVEROUTPUT ON
SQL > EXECUTE type_test(7369);
    처리결과
      사원번호 : 7369
      사원이름 : SMITH
      사원급여 : 800
SQL > show errors;

 

저장 객체 테스트

Function

  • 함수의 반환 값을 저장할 변수 선언
    • SQL > VAR salary NUMBER;
  • EXECUTE 문을 이용해 함수 실행
    • SQL > EXECUTE : salary := FC_update_sal(7369);
  • PRINT 문을 이용하여 결과 값 확인
    • SQL > PRINT salary;

Stored Procedure

  • 프로시저 실행에 따라 변경되는 데이터의 전/후 값을 확인할 수 있는 방법이나 환경을 사전에 확인, 조치함
    • 실행 결과를 보기 위한 설정을 함
      • SQL > SET SERVEROUTPUT ON;
    • 프로시저를 호출하여 실행함
      • execute update_sal(7369);

Package

  • 패키지의 실행은 [패키지명.프로시저(함수)명] 으로 호출하여 수행함
    • 실행 결과를 보기 위한 설정을 함
      • SQL > SET SERVEROUTPUT ON;
    • 패키지를 호출하여 실행함
      • exec emp_info.all_emp_info;

Trigger

  • 실행 결과를 보기 위한 설정을 함
    • SQL > SET SERVEROUTPUT ON;
  • Trigger가 처리될 조건에 부합되는 SQL을 실행하여 데이터 처리 결과를 확인함

 

 

 

 

 

320x100
저작자표시

'정보처리기사 > 필기' 카테고리의 다른 글

[정보처리기사] Part02-04-01. 애플리케이션 테스트케이스 설계  (33) 2023.01.11
[정보처리기사] Part02-03. 데이터조작 프로시저 최적화  (34) 2023.01.08
[정보처리기사] Part02-01-1. 논리 데이터 저장소 확인  (7) 2023.01.05
[정보처리기사] Part02-03-2. 제품 소프트웨어 메뉴얼 작성  (0) 2022.07.15
[정보처리기사] Part02-03-1. 제품 소프트웨어 패키징  (1) 2022.07.15

관련글

댓글

티스토리툴바