DB2 9.7에서 PL/SQL 익명 블록 사용하기

DB2 9.7에서 PL/SQL 익명 블록 사용하기 * 출처: http://www.kdug.kr 쿼리 | 2009-10-26 18:29:54

DB2 9.7에서 PL/SQL 익명 블록 사용하기 DB2 환경에서 PL/SQL 익명 블록을 사용하는 방법 난이도 : 초급 Maksym Petrenko, DB2 Open Database Technologies, IBM Maria Schwenger, DB2 Open Database Technologies, IBM 원문 게재일 : 2009 년 8 월 27 일 번역 게재일 : 2009 년 10 월 20 일 출처: developerWorks http://www.ibm.com/developerworks/kr/library/dm-0908anonymousblocks/index.html 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 솔루션을 빠르게 활용할 수 있다.