-
PL/SQL 실습을 위한 테이블 구조 소개
PL/SQL 실습을 진행하기 전에 먼저 사용할 테이블 구조를 정확히 이해하고 넘어가는 것이 중요하다.
이번 실습에서는 Oracle 데이터베이스에 존재하는 BOOK 테이블과 ORDERS 테이블을 기반으로 다양한 프로시저, 함수, 트리거 등을 실습할 예정이다.
1. BOOK 테이블
BOOK 테이블은 도서 정보를 저장하는 테이블로, 각 책에 대한 고유한 ID와 책 이름, 출판사, 가격 정보를 포함한다. PL/SQL 실습에서는 이 테이블에 데이터를 삽입하거나, 가격을 수정하거나, 특정 조건으로 조회하는 등의 작업을 수행하게 된다.
테이블 구조
컬럼명데이터 타입제약 조건설명
BOOKID NUMBER(2) PRIMARY KEY 책 고유 번호 BOOKNAME VARCHAR2(40) 책 제목 PUBLISHER VARCHAR2(40) 출판사 이름 PRICE NUMBER(8) 책 가격 주요 사항
- BOOKID는 기본 키로 지정되어 있어 중복될 수 없고, 각 책을 고유하게 식별한다.
- 문자형 컬럼들은 COLLATE USING_NLS_COMP 설정이 되어 있어, 데이터 정렬이나 비교에 영향을 줄 수 있다.
- 가격은 정수형 숫자 데이터로 구성되어 있다.
2. ORDERS 테이블
ORDERS 테이블은 고객이 구매한 도서의 주문 정보를 저장하는 테이블로, BOOK 테이블 및 CUSTOMER 테이블과 외래 키 관계를 갖는다. 실습에서는 이 테이블을 기반으로 이익 계산, 사용자 정의 함수 활용, 커서 기반 반복 처리 등을 수행하게 된다.
테이블 구조
컬럼명데이터 타입제약 조건설명
ORDERID NUMBER(2) PRIMARY KEY 주문 번호 CUSTID NUMBER(2) FOREIGN KEY 고객 ID (CUSTOMER 참조) BOOKID NUMBER(2) FOREIGN KEY 도서 ID (BOOK 참조) SALEPRICE NUMBER(8) 실제 판매 가격 ORDERDATE DATE 주문 날짜 주요 사항
- ORDERID는 주문을 고유하게 식별하는 기본 키다.
- CUSTID, BOOKID는 각각 고객 및 도서와의 관계를 정의하는 외래 키로 설정되어 있다.
- SALEPRICE는 실제 거래된 가격이며, BOOK.PRICE와는 다를 수 있다.
- 주문 날짜는 DATE 형식으로 저장된다.
3. 테이블 생성 DDL
실습 환경에서 직접 테이블을 생성해보고 싶다면 다음과 같이 작성할 수 있다.
BOOK 테이블 생성 예시
CREATE TABLE BOOK ( BOOKID NUMBER(2), BOOKNAME VARCHAR2(40), PUBLISHER VARCHAR2(40), PRICE NUMBER(8), CONSTRAINT PK_BOOK PRIMARY KEY (BOOKID) );ORDERS 테이블 생성 예시
CREATE TABLE ORDERS ( ORDERID NUMBER(2), CUSTID NUMBER(2), BOOKID NUMBER(2), SALEPRICE NUMBER(8), ORDERDATE DATE, CONSTRAINT PK_ORDERS PRIMARY KEY (ORDERID), CONSTRAINT FK_ORDERS_BOOK FOREIGN KEY (BOOKID) REFERENCES BOOK (BOOKID), CONSTRAINT FK_ORDERS_CUSTOMER FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID) );참고: 실제 실습 환경에서는 ADMIN 계정에서 관리되며, TABLESPACE, STORAGE, COLLATION 등의 옵션은 생략할 수 있다.
PL/SQL 실습 예제 정리 (프로시저, 트리거, 사용자 정의 함수)
이번 글에서는 이전에 소개한 BOOK, ORDERS 테이블을 기반으로 한 PL/SQL 실습을 진행한다. 주요 개념인 프로시저, 제어문, 커서, 트리거, 사용자 정의 함수에 대해 예제를 중심으로 살펴보고 실행 결과를 확인한다.
1. 프로시저를 이용한 삽입 작업
예제 1: 단일 도서 삽입 프로시저
이 예제는 도서 정보를 BOOK 테이블에 한 건 삽입하는 단순한 삽입 프로시저를 작성한다. 입력받은 책 ID, 제목, 출판사, 가격 정보를 INSERT INTO 구문을 통해 저장한다.
CREATE OR REPLACE PROCEDURE InsertBook( myBookID IN NUMBER, myBookName IN VARCHAR2, myPublisher IN VARCHAR2, myPrice IN NUMBER) AS BEGIN INSERT INTO Book(bookid, bookname, publisher, price) VALUES(myBookID, myBookName, myPublisher, myPrice); END; /-- 실행 테스트 EXEC InsertBook(13,'스포츠과학', '과학서적', 25000); SELECT * FROM Book;결과
2. PL/SQL 제어문 요약
구문 의미 문법 BEGIN~END 블록 구조 시작과 종료 BEGIN ... END; IF~ELSE 조건 분기 IF ... THEN ... ELSE ... END IF; FOR 지정 범위 반복 FOR i IN 1..n LOOP ... END LOOP; WHILE 조건 기반 반복 WHILE 조건 LOOP ... END LOOP; RETURN 함수 또는 프로시저 종료 후 값 반환 RETURN 값;
3. 조건 분기를 포함한 삽입/수정 프로시저
예제 2: 기존 도서 존재 시 UPDATE, 없으면 INSERT
이 프로시저는 삽입 대상 도서명이 이미 존재하는지 먼저 검사하고, 존재하면 가격을 UPDATE, 존재하지 않으면 INSERT 하는 방식으로 동작한다. 조건 분기에는 IF ... THEN ... ELSE 문을 사용하며, COUNT(*)를 통해 존재 여부를 판단한다.
CREATE OR REPLACE PROCEDURE BookInsertOrUpdate( myBookId NUMBER, myBookName VARCHAR2, myPublisher VARCHAR2, myPrice NUMBER ) AS mycount NUMBER; BEGIN SELECT COUNT(*) INTO mycount FROM Book WHERE bookname = myBookName; IF mycount != 0 THEN UPDATE Book SET price = myPrice WHERE bookname = myBookName; ELSE INSERT INTO Book (bookid, bookname, publisher, price) VALUES (myBookId, myBookName, myPublisher, myPrice); END IF; COMMIT; END; /EXEC BookInsertOrUpdate(15, '스포츠즐거움', '과학서적', 25000); EXEC BookInsertOrUpdate(15, '스포츠즐거움', '과학서적', 20000); SELECT * FROM Book;결과
Procedure BOOKINSERTORUPDATE이(가) 컴파일되었습니다. PL/SQL 프로시저가 성공적으로 완료되었습니다. BOOKID BOOKNAME PUBLISHER PRICE ---------- ---------------------------------------- ---------------------------------------- ---------- 1 축구의 역사 굿스포츠 7000 2 축구아는 여자 나무수 13000 3 축구의 이해 대한출판사 22000 4 골프 바이블 대한출판사 35000 5 피겨 교본 굿스포츠 8000 6 역도 단계별기술 굿스포츠 6000 7 야구의 추억 이상미디어 20000 8 야구를 부탁해 이상미디어 13000 10 Olympic Champions Pearson 13000 11 스포츠 세계 대한출판사 10000 13 스포츠과학 과학서적 25000 BOOKID BOOKNAME PUBLISHER PRICE ---------- ---------------------------------------- ---------------------------------------- ---------- 15 스포츠즐거움 과학서적 25000 12개 행이 선택되었습니다. PL/SQL 프로시저가 성공적으로 완료되었습니다. BOOKID BOOKNAME PUBLISHER PRICE ---------- ---------------------------------------- ---------------------------------------- ---------- 1 축구의 역사 굿스포츠 7000 2 축구아는 여자 나무수 13000 3 축구의 이해 대한출판사 22000 4 골프 바이블 대한출판사 35000 5 피겨 교본 굿스포츠 8000 6 역도 단계별기술 굿스포츠 6000 7 야구의 추억 이상미디어 20000 8 야구를 부탁해 이상미디어 13000 10 Olympic Champions Pearson 13000 11 스포츠 세계 대한출판사 10000 13 스포츠과학 과학서적 25000 BOOKID BOOKNAME PUBLISHER PRICE ---------- ---------------------------------------- ---------------------------------------- ---------- 15 스포츠즐거움 과학서적 20000 12개 행이 선택되었습니다.
4. 결과를 반환하는 프로시저
예제 3: 평균 가격 반환
이 프로시저는 BOOK 테이블에 저장된 가격들을 대상으로 평균값을 구하여 OUT 파라미터를 통해 반환한다. AVG(price) 함수와 INTO 구문을 사용하여 단일 값을 프로시저 외부로 전달한다.
CREATE OR REPLACE PROCEDURE AveragePrice(AverageVal OUT NUMBER) AS BEGIN SELECT AVG(price) INTO AverageVal FROM Book WHERE price IS NOT NULL; END; /SET SERVEROUTPUT ON; DECLARE AverageVal NUMBER; BEGIN AveragePrice(AverageVal); DBMS_OUTPUT.PUT_LINE('책값 평균: ' || AverageVal); END;결과
Procedure AVERAGEPRICE이(가) 컴파일되었습니다. 책값 평균: 16000 PL/SQL 프로시저가 성공적으로 완료되었습니다.
5. 커서를 사용하는 프로시저
커서는 SELECT 결과가 여러 행일 때 한 행씩 순차적으로 처리하기 위한 도구이다. 명시적 커서를 선언하여 OPEN → FETCH → EXIT → CLOSE 순으로 처리하는 패턴을 따른다.
예제 4: 판매된 도서의 이익 계산
이 프로시저는 ORDERS 테이블에서 판매 가격을 하나씩 읽어들이고, 가격이 3만 원 이상이면 10%, 그렇지 않으면 5%의 이익으로 계산하여 누적 이익을 출력한다.
CREATE OR REPLACE PROCEDURE Interest AS myInterest NUMBER := 0; Price NUMBER; CURSOR InterestCursor IS SELECT saleprice FROM Orders; BEGIN OPEN InterestCursor; LOOP FETCH InterestCursor INTO Price; EXIT WHEN InterestCursor%NOTFOUND; IF Price >= 30000 THEN myInterest := myInterest + Price * 0.1; ELSE myInterest := myInterest + Price * 0.05; END IF; END LOOP; CLOSE InterestCursor; DBMS_OUTPUT.PUT_LINE('전체 이익 금액 = ' || myInterest); END; /SET SERVEROUTPUT ON; EXEC Interest;결과
Procedure INTEREST이(가) 컴파일되었습니다. 전체 이익 금액 = 5900 PL/SQL 프로시저가 성공적으로 완료되었습니다.
6. 트리거 사용 예제
트리거는 테이블에 변화가 생겼을 때 자동으로 실행되는 절차형 코드 블록이다. 이 예제에서는 BOOK 테이블에 데이터가 삽입될 때 BOOK_LOG 테이블에 해당 데이터를 자동으로 백업하도록 한다.
예제 5: 도서 삽입 시 로그 테이블에 자동 기록
DROP TABLE Book_log; CREATE TABLE Book_log ( bookid_l NUMBER, bookname_l VARCHAR2(100), publisher_l VARCHAR2(40), price_l NUMBER ); CREATE OR REPLACE TRIGGER AfterInsertBook AFTER INSERT ON Book FOR EACH ROW BEGIN INSERT INTO Book_log (bookid_l, bookname_l, publisher_l, price_l) VALUES (:NEW.bookid, :NEW.bookname, :NEW.publisher, :NEW.price); DBMS_OUTPUT.PUT_LINE('삽입된 데이터를 Book_log 테이블에 백업했습니다.'); END; /SET SERVEROUTPUT ON; INSERT INTO Book VALUES (21, '소프트웨어공학', '이상미디어', 15000); SELECT * FROM Book_log WHERE bookid_l = 21;결과
Table BOOK_LOG이(가) 삭제되었습니다. Table BOOK_LOG이(가) 생성되었습니다. Trigger AFTERINSERTBOOK이(가) 컴파일되었습니다. 삽입된 데이터를 Book_log 테이블에 백업했습니다. 1 행 이(가) 삽입되었습니다. BOOKID BOOKNAME PUBLISHER PRICE ---------- ---------------------------------------- ---------------------------------------- ---------- 21 소프트웨어공학 이상미디어 15000 BOOKID_L BOOKNAME_L PUBLISHER_L PRICE_L ---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------- ---------- 21 소프트웨어공학 이상미디어 15000
7. 사용자 정의 함수
사용자 정의 함수는 SELECT 문에서 사용할 수 있는 반환형 로직으로, 프로시저와 달리 값을 반환해야 하며 비교적 간단한 계산 로직에 적합하다.
예제 6: 판매가에 따른 이익 계산 함수
CREATE OR REPLACE FUNCTION fnc_Interest(p_price NUMBER) RETURN NUMBER IS v_interest NUMBER; BEGIN IF p_price >= 30000 THEN v_interest := p_price * 0.1; ELSE v_interest := p_price * 0.05; END IF; RETURN v_interest; END; /SELECT custid, orderid, saleprice, fnc_Interest(saleprice) AS interest FROM Orders;결과
Function FNC_INTEREST이(가) 컴파일되었습니다. CUSTID ORDERID SALEPRICE INTEREST ---------- ---------- ---------- ---------- 1 1 6000 300 1 2 21000 1050 2 3 8000 400 3 4 6000 300 4 5 20000 1000 1 6 12000 600 4 7 13000 650 3 8 12000 600 2 9 7000 350 3 10 13000 650 10개 행이 선택되었습니다.'Data' 카테고리의 다른 글
[JPA] Spring Data JPA의 핵심 개념 (1) 2026.01.24 [JPA] 기본적인 Spring Data Jpa 활용 (0) 2026.01.24 [Data] JPA - 나오게 된 배경, 사용 이유 (0) 2026.01.03 PL/SQL이란? 오라클의 절차형 SQL 언어 정리 (0) 2025.05.01 데이터베이스 종류 (3) 2025.03.10