DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2
9.7
Tìm hiểu cách sử dụng các khối ẩn danh PL/SQL trong một môi trường
DB2
Maksym Petrenko
Chuyên gia công nghệ cơ sở dữ liệu mở DB2
IBM 06 11 2009
Maria Schwenger
Tư vấn công nghệ cơ sở dữ liệu mở DB2
IBM
IBM DB2® cho Linux®, UNIX® và Windows® 9.7 giới thiệu sự hỗ trợ đối với các khối ẩn danh
(anonymous blocks) PL/SQL: khía cạnh cho phép các nhà phát triển ứng dụng PL/SQL thử
nghiệm, sửa chữa lỗi và chạy thử mã theo thủ tục mới, mô phỏng các hoạt động của ứng dụng
và xây dựng động các báo cáo và các truy vấn đặc biệt phức tạp. Bài viết này mô tả khái niệm
về các khối ẩn danh trong DB2 9.7 và minh hoạ việc sử dụng khía cạnh này khi sử dụng các kịch
bản cơ sở dữ liệu thông thường.
Xem thêm bài trong loạt bài này
Giới thiệu
Bài viết này đưa ra hướng dẫn để sử dụng khối ẩn danh trong DB2 9.7 trong kịch bản sau:
• Thử nghiệm, sửa lỗi và phát triển các thủ tục lưu sẵn PL/SQL mới.
• Mô phỏng các hoạt động của ứng dụng với PL/SQL.
• Xây dựng các báo cáo và các truy vấn đặc biệt phức tạp đang hoạt động với PL/SQL.
Xem xét lại những điều kiện cần trước và các yêu cầu hệ thống
Bài này được viết cho các nhà phát triển ứng dụng và người quản trị cơ sở dữ liệu PL/SQL, những
người đã chuyển từ Oracle sang DB2. Bạn nên hiểu khái niệm về ngôn ngữ thủ tục PL/SQL. Các
nhà phát triển SQL PL nên sử dụng hàm tương ứng được các câu lệnh SQL phức hợp riêng của
DB2 cung cấp.
© Copyright IBM Corporation 2009
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Nhẫn hiệu đăng ký
Trang 1 của 8
developerWorks®
ibm.com/developerWorks/vn/
Để sử dụng các ví dụ trong bài viết này, bạn phải cài đặt DB2 9.7 Workgroup hoặc Enterprise
Edition cho Linux, UNIX và Windows. Hãy tham khảo phần Tài nguyên để tải về một phiên bản
dùng thử miễn phí của DB2 9.7 cho Linux, UNIX và Windows.
Sử dụng các ví dụ
Bạn có thể chạy các ví dụ bằng cách sử dụng các công cụ khác nhau, bao gồm bộ xử lý dòng lệnh
DB2 (CLP) và các tiện ích lệnh (CLPPLUS) hoặc các công cụ trực quan như Optim Development
Studio. Nếu bạn định chạy các ví dụ từ CLP, bạn cần phải chạy lệnh SET SQLCOMPAT PLSQL để
cho phép nhận ra ký tự dấu gạch chéo thuận (/) trên một dòng mới như là một ký tự kết thúc câu
lệnh PL/SQL.
Để kích hoạt sự hỗ trợ trong DB2 cho các kiểu dữ liệu PL/SQL và Oracle, cơ sở dữ liệu của bạn
phải được tạo ra với biến đăng kí DB2_COMPATIBILITY_VECTOR được thiết lập giá trị ORA, như
hiển thị trong Liệt kê 1.
Liệt kê 1. Thiết lập biến đăng kí DB2_COMPATIBILITY_VECTOR
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 create db test
Đối với bài này, Liệt kê 2 cung cấp mã để tạo một ứng dụng PL/SQL thương mại điện tử đơn giản
quản lý các đơn đặt hàng trực tuyến sau khi bạn tạo ra một cơ sở dữ liệu. Bạn cũng sẽ đặt vào các
bảng với một số dữ liệu mẫu.
Liệt kê 2. Mã ví dụ
Truy
cập vào mã ví dậ
Hiểu biết về các khối ẩn danh
Các khối ẩn danh là các cấu trúc PL/SQL cung cấp khả năng tạo ra và thực thi mã thủ tục đang
hoạt động mà không cần liên tục lưu trữ mã như là các đối tượng cơ sở dữ liệu trong các danh mục
hệ thống. Khái niệm về các khối ẩn danh tương tự như các kịch bản lệnh shell UNIX, nó cho phép
một số lệnh nhập vào thủ công được nhóm lại và thực hiện như là một bước. Như tên của nó, các
khối ẩn danh không có tên và vì lý do này mà chúng không thể được tham khảo từ các đối tượng
khác. Mặc dù được xây dựng động, các khối ẩn danh có thể dễ dàng được lưu trữ như các kịch bản
lệnh trong các tệp hệ điều hành để thực hiện lặp lại nhiều lần.
Các khối ẩn danh là các khối PL/SQL tiêu chuẩn. Chúng có kèm cú pháp và phải tuân theo quy tắc
áp dụng cho tất cả các khối PL/SQL, gồm cả khai báo và quy mô của các biến, việc thực hiện, xử lý
ngoại lệ và cách sử dụng SQL và PL/SQL.
Việc biên dịch và thực hiện các khối ẩn danh được kết hợp trong một bước, trong khi một thủ tục
lưu sẵn PL/SQL cần phải được định nghĩa lại trước mỗi lần sử dụng các thay đổi định nghĩa của nó.
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 2 của 8
ibm.com/developerWorks/vn/
developerWorks®
Đây là một trong những lợi thế đáng kể của các khối ẩn danh so với các đối tượng cơ sở dữ liệu có
tên liên tục, như các thủ tục lưu sẵn và các hàm do người dùng định nghĩa, vì nó làm giảm thời gian
giữa các thay đổi thực hiện trong mã và thực hiện thực tế. Điều đó làm cho các khối ẩn danh rất có
ích khi sửa lỗi, chạy thử và thử nghiệm mã thủ tục, vì đây là những nhiệm vụ thường đòi hỏi nhiều
hoạt động thay đổi và thực thi.
Một lợi ích khác của các khối ẩn danh là chúng không tạo ra bất kỳ các phụ thuộc nào và chúng
không yêu cầu bất kỳ các quyền ưu tiên đặc biệt nào để tạo đối tượng, mà việc này có thể tránh
những tình thế khó khăn trong một môi trường sản xuất. Các khối ẩn danh cho phép chạy linh hoạt
bất kỳ chuỗi hành động có tính thủ tục, dựa trên các đặc quyền select đơn giản; và chúng cho phép
bạn thử nghiệm mà không cần tạo hay ngầm định các đối tượng cơ sở dữ liệu hiện có.
Bạn có thể thực thi các khối ẩn danh từ:
• SQL (ví dụ, bên trong các câu lệnh EXECUTE IMMEDIATE).
• Các DB2 API như JDBC và ODBC.
• Các công cụ DB2 khác nhau, bao gồm CLP, CLPPlus, Optim Database Administrator (Nhà
quản trị cơ sở dữ liệu Optim) và Optim Development Studio.
Chạy thử mã PL/SQL với các khối ẩn danh
Trong Liệt kê 3, một nhà phát triển ứng dụng dự kiến yêu cầu nghiệp vụ cho một cơ chế để truyền
thông (bằng email) với các khách hàng được xác định trong bảng KHÁCH HÀNG (CUSTOMER).
Để chủ động đáp ứng yêu cầu này, anh ta quyết định viết một khối ẩn danh PL/SQL thử nghiệm
đơn giản để gửi email có chứa một số thông báo cho các khách hàng trong bảng CUSTOMER.
Sau đó, một khi yêu cầu nghiệp vụ này được hoàn thành, khối ẩn danh đã chạy thử có thể dễ
dàng được nâng cao và dễ dàng được biến đổi thành một thủ tục lưu sẵn PL/SQL mới. Lưu ý rằng
khối ẩn danh này sử dụng các gói kèm theo mới, bao gồm UTL_SMTP (gói để gửi các email) và
DBMS_OUTPUT (gói để viết các thông báo tới đầu ra tiêu chuẩn), là một phần của DB2 9.7.
Liệt kê 3. Một khối ẩn danh PL/SQL thử nghiệm đơn giản sẽ gửi email có chứa một
số thông báo cho khách hàng trong bảng CUSTOMER
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 ||
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 3 của 8
developerWorks®
ibm.com/developerWorks/vn/
'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
Sending
Sending
Sending
Sending
test
test
test
test
test
email
email
email
email
email
to
to
to
to
to
customer
customer
customer
customer
customer
Mike...
Joan...
Colin...
Graham...
Patsy...
Mô phỏng các hoạt động của ứng dụng với các khối ẩn danh
Như đã đề cập, một trong những cách sử dụng các khối ẩn danh phổ biến nhất là để gọi các đối
tượng ngôn ngữ thủ tục, thường là cho mục đích thử nghiệm. Liệt kê 4 giải thích cách mô phỏng
một hoạt động của ứng dụng với sự giúp đỡ của một khối ẩn danh PL/SQL. Mã này mô phỏng một
hoạt động của ứng dụng trong khi thu giữ các thước đo hiệu năng. Khối ẩn danh mô phỏng việc tạo
ra 10 đơn đặt hàng ngẫu nhiên cho các khách hàng ngẫu nhiên từ bảng CUSTOMER. Nó cũng in
thời gian bắt đầu và kết thúc thử nghiệm, cùng với các chi tiết đơn hàng cho mỗi lần chạy. Dễ dàng
thay đổi số lượng đơn đặt hàng từ 10 đến 20 và sau đó chạy lại khối ẩn danh này mà không cần
biên dịch lại. Bạn cũng có thể dùng thước đo hiệu năng khác để thử nghiệm thêm.
Liệt kê 4. Một ứng dụng đang chạy với sự giúp đỡ của một khối ẩn danh 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);
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 4 của 8
ibm.com/developerWorks/vn/
developerWorks®
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
Tạo các báo cáo đặc biệt bằng các khối ẩn danh
Một yêu cầu lập báo cáo chung là liên kết dữ liệu từ nhiều hơn một cột thành một chuỗi đơn. Có
thể viết các câu lệnh thuần SQL với lệnh đệ qui phức tạp để làm điều này. Tuy nhiên, thay vào đó
bạn có thể sử dụng các khối ẩn danh để làm điều này nhanh chóng với các tùy chọn định dạng
động và luồng logic đơn giản.
Liệt kê 5 cho thấy cách tạo một báo cáo đặc biệt với sự giúp đỡ của các khối ẩn danh. Mã này lấy
ra danh sách của tất cả các khách hàng đã đặt hàng các sản phẩm từ các cửa hàng và tổng giá trị
của tất cả các đơn đặt hàng trong tháng trước. Các tên được hiển thị trong một hàng và được phân
cách bằng dấu phẩy.
Liệt kê 5. Tạo một báo cáo đặc biệt với sự giúp đỡ của các khối ẩn danh
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;
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 5 của 8
developerWorks®
ibm.com/developerWorks/vn/
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
Kết luận
Bài này được trình bày như sau
•
•
•
•
•
Tính năng của các khối ẩn danh PL/SQL được giới thiệu trong DB2 9.7.
Khái niệm về các khối ẩn danh.
Cách tạo thuận lợi cho quá trình thử nghiệm, tạo mã mẫu và sửa lỗi của mã thủ tục.
Cách khối ẩn danh mô phỏng hoạt động chạy của ứng dụng.
Cách dùng khối ẩn danh để viết báo cáo.
Với sự hỗ trợ cho các khối ẩn danh PL/SQL, bạn có thể nhanh chóng kích hoạt các giải pháp PL/
SQL trong môi trường DB2 bằng cách sử dụng các kịch bản lệnh PL/SQL hiện có hoặc sử dụng các
câu lệnh riêng PL/SQL và SQL để làm việc với các hệ quản trị cơ sở dữ liệu khác.
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 6 của 8
ibm.com/developerWorks/vn/
developerWorks®
Tài nguyên
Học tập
• Sử dụng một nguồn cung cấp RSS để yêu cầu thông báo cho những bài viết sắp tới trong loạt
bài này. (Tìm hiểu thêm về các nguồn cung cấp RSS của nội dung developerWorks.)
• Tìm hiểu về sự hỗ trợ sẵn cho Oracle SQL và các phương ngữ PL/SQL trong DB2 9.7 từ "DB2
9.7: Chạy ứng dụng Oracle trên DB2 9.7 với Linux, UNIX và Windows" (developerWorks, 07.
2009).
• Tìm hiểu thêm về SQL ghép từ Trung tâm thông tin DB2.
• Tìm hiểu thêm về Các công cụ phát triển ứng dụng và quản lý cơ sở dữ liệu từ Trung tâm
thông tin DB2.
• Tìm hiểu thêm về Cú pháp khóa ẩn danh từ Trung tâm thông tin DB2.
• Tìm hiểu thêm về Các mô đun có định nghĩa hệ thống từ Trung tâm thông tin DB2.
• Tìm hiểu thêm về Quản trị thông tin tại Vùng Quản lý thông tin của developerWorks. Tìm thấy
tài liệu hướng dẫn kỹ thuật, các bài báo hướng dẫn, giáo dục, tải, thông tin sản phẩm và nhiều
hơn nữa.
• Theo sát các sự kiện kỹ thuật và webcast của developerWorks.
Lấy sản phẩm và công nghệ
• Tải bản DB2 Express-C 9.7, một phiên bản miễn phí của máy chủ cơ sở dữ liệu Express DB2
cho cộng đồng có bao gồm pureXML.
• Tải bản một phiên bản miễn phí của DB2 9.7 for Linux, UNIX, and Windows..
• Xây dựng các dự án phát triển tiếp theo của bạn với phần mềm dùng thử IBM, có sẵn để tải về
trực tiếp từ developerWorks.
Thảo luận
• Tham gia diễn đàn để thảo luận nội dung này.
• Xem developerWorks blogs và dành tâm trí cho cộng đồng developerWorks.
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 7 của 8
developerWorks®
ibm.com/developerWorks/vn/
Đôi nét về các tác giả
Maksym Petrenko
Maksym Petrenko là một thành viên của nhóm Hỗ trợ Beta DB2 tại Phòng thí nghiệm
Toronto của IBM. Anh sớm giúp đỡ những người chấp nhận chuyển các ứng dụng của
họ sang cơ sở mã DB2 mới nhất và lớn nhất. Maksym đã làm việc với DB2 kể từ năm
2001 như là một nhà phát triển, nhà phân tích hỗ trợ kỹ thuật và tư vấn dịch vụ của
phòng thí nghiệm. Kinh nghiệm của anh gồm việc hỗ trợ khách hàng các cài đặt, cấu
hình, phát triển ứng dụng và các vấn đề hiệu năng liên quan đến Cơ sở dữ liệu DB2
trên các nền Windows, Linux và UNIX. Maksym là người quản trị cơ sở dữ liệu cao cấp
DB2 và nhà phát triển ứng dụng DB2 được công nhận.
Maria Schwenger
Maria Schwenger gia nhập IBM vào năm 2005 như một thành viên của nhóm các Giải
pháp phân tích thực thể, sau đó phát huy thêm 10 năm kinh nghiệm trong việc thực
hiện kỹ thuật, kiến trúc cơ sở dữ liệu, quản trị và phát triển cơ sở dữ liệu trên Oracle
và MS SQL Server, cũng như kinh nghiệm sâu rộng trong việc chuyển từ cơ sở dữ liệu
hợp lệ sang cơ sở dữ liệu quan hệ. Hiện nay, Maria làm việc với mô hình tiếp xúc cao
(high-touch) với những người tham gia khác, nhằm thúc đẩy việc áp dụng sớm Công
nghệ cơ sở dữ liệu mở của DB2
© Copyright IBM Corporation 2009
(www.ibm.com/legal/copytrade.shtml)
Nhẫn hiệu đăng ký
(www.ibm.com/developerworks/vn/ibm/trademarks/)
DB2 9.7: Sử dụng các khối ẩn danh PL/SQL trong DB2 9.7
Trang 8 của 8