 | |
|
난이도 : 초급 Maksym Petrenko, DB2 Open Database Technologies, IBM
Maria Schwenger, DB2 Open Database Technologies, IBM
원문 게재일 : 2009 년 8 월 27 일
번역 게재일 : 2009 년 10 월 20 일 IBM DB2® for Linux®, UNIX®, and Windows® 9.7에는
PL/SQL 익명 블록에 대한 지원이 새롭게 추가되었으며, 이 기능을 통해 PL/SQL 애플리케이션 개발자는
새 프로시저 코드를 테스트, 문제 해결 및 프로타이핑하고, 애플리케이션 실행을 시뮬레이션하고, 복합
임시 쿼리 및 보고서를 동적으로 빌드할 수 있습니다. 이 기사에서는 DB2 9.7에서의 익명 블록에 대한 개념을 설명한
후 일반적인 데이터베이스 시나리오를 사용하여 이 기능을 사용하는 방법에 대해 살펴봅니다.
소개
이 기사에서는 다음과 같은 시나리오를 통해 DB2 9.7에서 익명 블록을 사용하는 방법에 대해 설명한다.
- 새 PL/SQL 저장 프로시저 테스트, 문제 해결 및 개발하기
- PL/SQL을 사용하여 애플리케이션 실행 시뮬레이션하기
- PL/SQL을 사용하여 동적으로 복합 임시 쿼리 및 보고서 빌드하기
전제 조건 및 시스템 요구 사항
이 기사의 대상 독자는 Oracle에서 DB2로 이동하려는 PL/SQL 애플리케이션 개발자
및 데이터베이스 관리자이다. PL/SQL 프로시저 언어에 대한 개념을 이해하고 있어야 한다. SQL PL
개발자는 DB2 네이티브 복합 SQL 문에 해당하는 함수를 사용해야 한다.
이 기사의 예제를 사용하려면 Linux, UNIX, and Windows용 DB2 9.7 Workgroup
또는 Enterprise Edition이 설치되어 있어야 한다. 참고자료 섹션의
링크를 통해 DB2 9.7 for Linux, UNIX, and Windows의 무료 시험판을 다운로드할 수 있다.
예제 사용하기
DB2 CLP(command line processor) 및 명령 유틸리티(CLPPLUS)와 같은 다양한 도구나
Optim Development Studio와 같은 비주얼 도구를 사용하여 예제를 실행할 수 있다. CLP에서 명령을
실행하려면 SET SQLCOMPAT PLSQL 명령을 실행하여 새 행에서 슬래시 문자(/)를 PL/SQL 문
종료 문자로 인식하도록 설정해야 한다.
DB2에서 PL/SQL 및 Oracle 데이터 유형을 지원하려면 Listing 1과 같이 ORA로 설정된
DB2_COMPATIBILITY_VECTOR 레지스트리 변수를 사용하여 데이터베이스를 작성해야 한다.
Listing 1. DB2_COMPATIBILITY_VECTOR 레지스트리 설정하기
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 create db test
|
이 기사의 Listing
2에서는 데이터베이스를 작성한 후 온라인 주문을 관리하는 간단한 전자 상거래 PL/SQL
애플리케이션을 작성하는 코드를 제공한다. 또한 일부 샘플 데이터를 사용하여 테이블을
채우게 된다.
Listing 2. 예제 코드
익명 블록 이해하기
익명 블록은 코드를 시스템 카탈로그에 데이터베이스 오브젝트로 영구적으로 저장하지
않고서 프로시저 코드를 동적으로 작성하고 실행할 수 있는 기능을 제공하는 PL/SQL 구조체이다. 익명
블록이라는 개념은 수동으로 입력한 여러 명령을 그룹화하여 하나의 단계로 실행할 수 있는 UNIX 쉘
스크립트와 비슷하다. 이름으로 알 수 있듯이 익명 블록에는 이름이 없기 때문에 다른 오브젝트에서
참조할 수 없다. 동적으로 작성되기는 하지만 반복해서 실행하기 위해 익명 블록을 운영 체제의
스크립트로 쉽게 저장할 수 있다.
익명 블록은 표준 PL/SQL 블록으로 구문을 전달하며 변수의 선언과 범위, 실행,
예외 처리, SQL 및 PL/SQL 사용법을 포함한 모든 PL/SQL 블록에 적용되는 규칙을 따른다.
익명 블록의 컴파일과 실행은 하나의 단계로 통합되어 수행되지만 정의가 변경될
때마다 PL/SQL 저장 프로시저를 다시 정의한 후 사용해야 한다. 익명 블록을 사용하면 코드의
변경 사항을 구현하는 작업과 실제 실행 사이의 시간을 줄일 수 있다. 이는 저장 프로시저,
사용자 정의 함수 등과 같이 영구적으로 명명된 데이터베이스 오브젝트와 비교되는 익명 블록의
큰 장점이다. 익명 블록은 프로시저 코드를 문제 해결, 프로토타이핑 및 테스트할 때 매우 유용하다.
왜냐하면 이러한 작업에서는 변경하고 실행하는 과정을 일반적으로 여러 번 반복해야 하기 때문이다.
익명 블록의 또 다른 장점은 종속성을 작성하지 않는다는 것이다. 따라서 오브젝트
작성을 위한 특별한 권한이 필요하지 않기 때문에 프로덕션 환경에서 컴파일을 수행하지 않아도
된다. 익명 블록을 사용하면 단순한 특정 권한을 기반으로 일련의 프로시저 작업을 유연하게 실행할
수 있으며 기존 데이터베이스 오브젝트를 작성 또는 연결하지 않고 테스트를 수행할 수 있다.
다음과 도구를 통해 익명 블록을 실행할 수 있다.
- SQL(예: EXECUTE IMMEDIATE 문 내에서 사용)
- JDBC, ODBC 등의 DB2 API
- CLP, CLPPlus, Optim Database Administrator 및 Optim Development Studio를 포함한 다양한 DB2 도구
익명 블록으로 PL/SQL 코드 프로토타이핑하기
Listing 3에서 애플리케이션 개발자는 이메일을 통해 CUSTOMER 테이블에 정의된
고객과 통신하는 메커니즘이 필요하다는 비즈니스 요구 사항을 예상할 수 있다. 이 요구 사항을
충족하기 위해 개발자는 CUSTOMER 테이블의 고객에게 메시지가 포함된 이메일을 보내는 간단한
프로토타입 PL/SQL 익명 블록을 작성하기로 결정한다. 나중에 비즈니스 요구 사항이 충족된 후
프로토타이핑된 익명 블록을 수정하여 새 PL/SQL 저장 프로시저로 쉽게 변환할 수 있다. 이 익명
블록에서는 DB2 9.7의 일부인 UTL_SMTP(이메일 전송을 위한 패키지) 및 DBMS_OUTPUT(표준 출력에
메시지를 쓰기 위한 패키지)을 포함한 새로운 내장 패키지를 사용한다.
Listing 3. 메시지가 포함된 이메일을 CUSTOMER 테이블의
고객에게 보내는 간단한 프로토타입 PL/SQL 익명 블록
SET SERVEROUTPUT ON
/
DECLARE
conn UTL_SMTP.connection;
reply UTL_SMTP.reply;
msg VARCHAR2(1024);
sender VARCHAR2(255) DEFAULT 'demo\@ca.ibm.com';
recipients VARCHAR2(255);
subject VARCHAR2(255) DEFAULT 'Quick notification';
crlf VARCHAR2(2);
BEGIN
crlf := UTL_TCP.CRLF;
FOR row IN (SELECT first_name, email FROM customer) LOOP
DBMS_OUTPUT.PUT_LINE('Sending test email to customer ' || row.first_name || '...');
recipients := row.email;
msg := 'FROM: ' || sender || crlf ||
'TO: ' || recipients || crlf ||
'SUBJECT: ' || subject || crlf ||
crlf ||
'Hi ' || row.first_name || ', this is a test notification.';
UTL_SMTP.OPEN_CONNECTION('smtp_server.ibm.com', 25, conn, 10, reply );
UTL_SMTP.HELO(conn, 'localhost');
UTL_SMTP.MAIL(conn, sender);
UTL_SMTP.RCPT(conn, recipients);
UTL_SMTP.DATA(conn, msg);
UTL_SMTP.QUIT(conn);
END LOOP;
END;
/
Output:
Sending test email to customer Mike...
Sending test email to customer Joan...
Sending test email to customer Colin...
Sending test email to customer Graham...
Sending test email to customer Patsy...
|
 |
|
익명 블록으로 애플리케이션 실행 시뮬레이션하기
앞에서 언급한 대로 익명 블록의 가장 일반적인 용도 중 하나는 테스트를 위해
프로시저 언어 오브젝트를 호출하는 것이다. Listing 4에서는 PL/SQL 익명 블록을 사용하여 애플리케이션
실행을 시뮬레이션하는 방법을 보여 준다. 이 코드에서는 애플리케이션 실행을 시뮬레이션하면서
성능 지표를 캡처한다. 익명 블록에서는 기존 CUSTOMER 테이블에 있는 고객을 무작위로 선택하여
10개의 주문을 무작위로 생성하는 과정을 시뮬레이션하면서 각 실행에 대한 주문 세부 사항과 테스트
시작 및 종료 시간을 인쇄한다. 이제 주문 수를 10에서 20으로 변경한 후 이 익명 블록을 다시
실행할 수 있으며 이 경우 다시 컴파일하지 않아도 되기 때문에 매우 편리하다. 또한 성능 지표를
추가하여 추가 테스트를 수행할 수도 있다.
Listing 4. PL/SQL 익명 블록을 사용한 애플리케이션
실행
SET SERVEROUTPUT ON
/
DECLARE
v_customer_id customer.customer_id%TYPE;
product_id product.product_id%TYPE:=1;
o_order_id orders.order_id%TYPE;
v_test_start TIMESTAMP;
BEGIN
SELECT CURRENT TIMESTAMP INTO v_test_start FROM dual;
FOR k IN 1..10 LOOP
SELECT customer_id INTO v_customer_id FROM customer ORDER BY RAND() FETCH FIRST 1
ROW ONLY;
FOR i IN (
SELECT product_id, CAST(RAND()*50 as integer)+1 as quantity
FROM product
WHERE ROWNUM < CAST(RAND()*10 as integer))
LOOP
add_item_to_shopping_cart(i.product_id, i.quantity);
END LOOP;
create_order(v_customer_id, o_order_id);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('Test start: ' || v_test_start);
DBMS_OUTPUT.PUT_LINE('Test end : ' || CURRENT TIMESTAMP);
END;
/
Output:
Customer : Mike, Smith
Order creation : 07-06-2009
Estimated Delivery : 07-09-2009
Status : Shipped
Total price : $ 150,615.44
--------------------------------------------
Customer : Joan, Jett
Order creation : 07-06-2009
Estimated Delivery : 07-09-2009
Status : Shipped
Total price : $ 159,445.77
...
...
...
Customer : Colin, Taylor
Order creation : 07-06-2009
Estimated Delivery : 07-09-2009
Status : Shipped
Total price : $ 266,242.78
--------------------------------------------
Test start: 2009-07-06-11.10.11.500000
Test end : 2009-07-06-11.10.11.546000
|
|
|
익명 블록으로 임시 보고서 생성하기
일반적으로 보고서에는 둘 이상의 열에 있는 데이터를 단일 문자열로 연결하는
요구 사항이 있다. 복잡한 재귀 구문이 포함된 SQL 문을 작성하여 이 작업을 수행할 수도 있지만 익명
블록을 사용하면 동적 형식 지정 옵션과 단순한 논리 흐름을 통해 이 작업을 빠르게 수행할 수 있다.
Listing 5에서는 익명 블록을 활용하여 임시 보고서를 작성하는 방법을 보여 준다. 이
코드에서는 지난 달 매장에서 제품을 주문한 모든 고객의 목록과 모든 주문의 총액을 가져온다. 고객
이름은 한 행에서 쉼표로 구분되어 표시된다.
Listing 5. 익명 블록을 활용하여 임시 보고서 작성하기
SET SERVEROUTPUT ON
/
DECLARE
v_customer_names VARCHAR2(4000);
v_total_sales NUMBER(19,2);
BEGIN
DBMS_OUTPUT.PUT_LINE(' Last Month Sales Report ');
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
DBMS_OUTPUT.PUT('Customer List: ');
FOR row IN
(SELECT distinct(a.customer_id),first_name, last_name FROM customer a, orders b
WHERE a.customer_id=b.order_id AND b.creation_time>CURRENT DATE -1 month)
LOOP
v_customer_names := v_customer_names || '"' || row.first_name || ' ' ||
row.last_name || '", ';
END LOOP;
IF(LENGTH(v_customer_names) > 0) THEN
v_customer_names := SUBSTR(v_customer_names,1, LENGTH(v_customer_names)-2);
ELSE
v_customer_names := 'None';
END IF;
DBMS_OUTPUT.PUT_LINE(v_customer_names);
SELECT NVL(SUM(total_price),0) INTO v_total_sales FROM orders WHERE creation_time>
CURRENT DATE - 1 month;
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
DBMS_OUTPUT.PUT_LINE('Total Sales: ' || TO_CHAR(v_total_sales, '$99,999,999.99'));
END;
/
Output:
Last Month Sales Report ---------------------------------------
Customer List: "Mike Smith", "Joan Jett", "Colin Taylor", "Graham Norton", "Patsy Stone"
---------------------------------------
Total Sales: $ 49,772.56
|
|
|
결론
이 기사에서 살펴본 내용은 다음과 같다.
- DB2 9.7에 새롭게 추가된 PL/SQL 익명 블록 기능
- 익명 블록의 개념
- 익명 블록을 프로시저 코드의 테스트, 프로토타이핑 및 문제 해결에 활용하는 방법
- 익명 블록으로 애플리케이션 실행을 시뮬레이션하는 방법
- 익명 블록을 사용하여 강력한 임시 보고서를 작성하는 방법
이제 DB2 환경에서 PL/SQL 익명 블록이 지원되므로 기존 PL/SQL 스크립트를 사용하거나
다른 데이터베이스 관리 시스템에서 작동하는 개별 PL/SQL 및 SQL 문을 사용하여 PL/SQL 솔루션을 빠르게
활용할 수 있다.
참고자료 교육
제품 및 기술 얻기
토론
필자소개 | 
| | Maksym Petrenko는 IBM Toronto Lab의 DB2 Beta Enablement Team 소속으로 애플리케이션을
우수한 기능의 최신 DB2 코드베이스로 이동하려는 초기 채택자를 지원하는 업무를 맡고 있다. 2001년부터
개발자, 기술 지원 분석가 및 랩 서비스 컨설턴트로서 DB2 관련 업무를 수행하고 있다. 고객에게 Windows,
Linux 및 UNIX 플랫폼에서 DB2 데이터베이스와 관련된 설치, 구성, 애플리케이션 개발 및 성능 문제에 대한
지원을 제공하는 업무도 수행했다. 공인 DB2 Advanced Database Administrator 및 DB2 Application Developer이다. |
| 
| | 2005년에 IBM에 입사한 Maria Schwenger는 Entity Analytic Solutions 팀원이며 Oracle 및 MS SQL
서버의 성능 엔지니어링, 데이터베이스 아키텍처, 관리 및 데이터베이스 개발과 관련된 10년 이상의
경력을 보유하고 있으며 레거시 데이터베이스에서 관계형 데이터베이스로의 마이그레이션과 관련된 업무도
수행했었다. 현재는 초기 릴리스 참여자와 함께 하이터치 모델에 참여하여 DB2 Open Database Technology의
초기 정착을 위해 노력하고 있다. |
기사에 대한 평가
|
| 이 문서 북마킹 하기
|
| |
