Các khái niệm cơ bản của DB2: Giới thiệu về các kiểu dữ liệu có cấu trúc và các bảng được định kiểu

Các khái niệm cơ bản của DB2: Giới thiệu về các kiểu dữ liệu có cấu trúc và các bảng được định kiểu Cơ sở dữ liệu vạn năng DB2 của IBM (DB2 Universal Database - DB2 UDB) cho các hệ điều hành Linux, UNIX và Windows cho phép dùng các kiểu dữ liệu có cấu trúc. Kiểu dữ liệu có cấu trúc là dạng dữ liệu do người dùng định nghĩa có chứa một chuỗi các thuộc tính, mỗi thuộc tính trong các thuộc tính đó có một kiểu dữ liệu. Mỗi thuộc tính là đặc tính giúp mô tả thể hiện của kiểu. Ví dụ: Nếu chúng ta định nghĩa một kiểu có cấu trúc có tên là address_t, thì thành phố có thể là một trong những thuộc tính của kiểu dữ liệu có cấu trúc. Các kiểu dữ liệu có cấu trúc giúp dễ sử dụng dữ liệu, chẳng hạn như địa chỉ, hoặc là một đơn vị dữ liệu đơn, hoặc là các mục dữ liệu riêng biệt, mà không cần phải lưu giữ mỗi dữ liệu của các dữ liệu (hoặc các thuộc tính) này trong một cột riêng biệt. Thường xảy ra nhất là các kiểu dữ liệu có cấu trúc được lưu trữ trong cơ sở dữ liệu:  Như là các giá trị trong một hoặc nhiều cột, được định nghĩa bằng cách sử dụng các kiểu dữ liệu có cấu trúc như là các kiểu dữ liệu của chúng. (Xem Liệt kê 1 và Liệt kê 2.)  Như là các hàng trong một bảng (bảng được định kiểu) có các cột được định nghĩa bởi các thuộc tính của các kiểu dữ liệu có cấu trúc. Trong trường hợp này, bảng được tạo ra bằng kiểu dữ liệu có cấu trúc, và bạn không xác định riêng cho cột trong định nghĩa bảng. (Xem Liệt kê 3.) Các kiểu dữ liệu có cấu trúc Kiểu dữ liệu có cấu trúc có thể được sử dụng như là kiểu cho một cột trong bảng thông thường (Liệt kê 1 và 2), kiểu cho toàn bộ bảng (hoặc khung nhìn), hoặc như là thuộc tính của một kiểu dữ liệu có cấu trúc khác (Liệt kê 3). Khi được sử dụng làm kiểu cho bảng, thì bảng được biết như là bảng được định kiểu (Liệt kê 3). Bạn có thể tạo một bảng với các cột có kiểu dữ liệu cấu trúc với cùng cách mà bạn sẽ tạo ra bảng bằng cách sử dụng các kiểu dữ liệu đã có sẵn trong DB2. Trong cả hai trường hợp, bạn phải chỉ định kiểu dữ liệu cho mỗi cột trong bảng. Nếu cột này là cột của kiểu dữ liệu có cấu trúc, thì bạn xác định tên của kiểu dữ liệu cấu trúc làm kiểu dữ liệu của nó (Liệt kê 1). Các kiểu dữ liệu có cấu trúc có hành vi gọi là tính kế thừa. Một kiểu dữ liệu có cấu trúc có thể có các kiểu dữ liệu con, là các kiểu dữ liệu có cấu trúc khác sử dụng lại tất cả các thuộc tính của nó và chứa các thuộc tính cụ thể của nó. Kiểu dữ liệu mà kiểu dữ liệu con thừa kế các thuộc tính được gọi là siêu kiểu của nó. Một phân cấp kiểu là tập các kiểu dữ liệu con dựa trên cùng một siêu kiểu; các siêu kiểu ưu việt trong hệ thống phân cấp được gọi là kiểu gốc của hệ thống phân cấp. Hãy sử dụng câu lệnh CREATE TYPE để tạo kiểu dữ liệu có cấu trúc, và sử dụng câu lệnh DROP để xóa kiểu dữ liệu có cấu trúc. Khi bạn tạo một kiểu dữ liệu có cấu trúc, thì DB2 tự động tạo ra một hàm kiến thiết đối với kiểu dữ liệu này và tạo ra cả hai trình biến đổi và trình quan sát đối với các thuộc tính của kiểu dữ liệu. Bạn có thể sử dụng hàm kiến thiết và phương thức trình biến đổi để tạo ra các thể hiện của kiểu dữ liệu có cấu trúc và sau đó bạn có thể chèn các các thể hiện này vào cột của bảng.  Chức năng trình kiến thiết có cùng tên với kiểu dữ liệu có cấu trúc gắn với nó. Hàm kiến thiết không có tham số và trả về thể hiện của kiểu dữ liệu với tất cả các thuộc tính của nó được đặt giá trị rỗng.  Phương thức trình biến đổi tồn tại đối với từng thuộc tính của kiểu dữ liệu có cấu trúc. Khi bạn gọi ra phương thức trình biết đổi trên một thể hiện của kiểu dữ liệu có cấu trúc và xác định một giá trị mới cho thuộc tính liên quan của nó, phương thức này trả về một thể hiện mới với thuộc tính được cập nhật với giá trị mới.  Phương thức trình quan sát tồn tại cho từng thuộc tính của kiểu dữ liệu có cấu trúc. Khi bạn gọi ra phương thức trình quan sát trên một thể hiện của kiểu dữ liệu có cấu trúc, phương thức trả về giá trị của thuộc tính cho thể hiện đó. Để gọi phương thức trình biến đổi hoặc quan sát trên một thể hiện của kiểu dữ liệu có cấu trúc, bạn hãy sử dụng toán tử hai dấu chấm (..) (Liệt kê 1, 2, và 3). Về đầu trang Bảng được định kiểu Một bảng được định kiểu là bảng được xác định với kiểu dữ liệu có cấu trúc do người dùng định nghĩa. Các bảng được định kiểu lưu giữ các thể hiện của các kiểu dữ liệu có cấu trúc theo hàng, trong đó mỗi thuộc tính của kiểu dữ liệu được lưu trữ trong một cột riêng biệt, trong thực tế, tên và kiểu dữ liệu của các thuộc tính của các dữ liệu có cấu trúc trở thành tên và kiểu dữ liệu của các cột của bảng được định kiểu. Tương tự như các kiểu dữ liệu có cấu trúc, các bảng được định kiểu có thể là một phần của hệ thống bảng, bao gồm một bảng gốc đơn, các siêu bảng và các bảng con. Bạn hãy sử dụng câu lệnh CREATE TABLE để tạo ra một bảng được định kiểu, và sử dụng câu lệnh DROP để xóa bảng được định kiểu. Một bảng được định kiểu đang bị xóa không thể có bất kỳ bảng con nào. Bạn cũng có thể xóa toàn bộ hệ thống phân cấp bảng bằng cách chỉ định các từ khoá HIERARCHY trong câu lệnh DROP (DROP TABLE HIERARCHY ). Về đầu trang Ví dụ chi tiết Đối với ví dụ này, ta sẽ sử dụng cơ sở dữ liệu SAMPLE đi kèm với DB2 UDB. Ví dụ của chúng tôi yêu cầu kết nối cơ sở dữ liệu để bắt đầu, và chỉ vậy, và đối với ví dụ này, cơ sở dữ liệu SAMPLE sẽ làm việc khá tốt. Chúng ta sẽ tạo ra bảng có tên là CLIENTS với bốn cột. CLIENT_ID là cột định tên do hệ thống tạo ra; CLIENT_LNAME và CLIENT_FNAME chứa tương ứng họ và tên mới của khách; và ADDRESS là cột của kiểu dữ liệu có cấu trúc của kiểu Address_t. (qui ước thường dùng là đặt tên cho kiểu dữ liệu cấu trúc với hậu tố '_t ' để định tên của chúng như kiểu dữ liệu có cấu trúc một cách rõ ràng.) Chúng ta sẽ cần phải tạo kiểu dữ liệu có cấu trúc trước khi chúng ta tạo ra bảng CLIENTS (Liệt kê 1). Chúng ta sẽ sử dụng hình thức đơn giản nhất của câu lệnh CREATE TYPE, bằng cách chỉ xác định bốn thuộc tính (đường, thành phố, tỉnh và mã bưu điện - postal_code) và câu MODE DB2_SQL yêu cầu. Để lấy ra dữ liệu có cấu trúc từ một bảng, phải có một số cách để chuyển đổi kiểu thành một giá trị vô hướng đơn mà kiểu của nó, bản thân kiểu dựa trên một trong những dữ liệu DB2 được xây dựng sẵn. Để thực hiện việc chuyển đổi này, trước tiên chúng ta phải tạo hàm biến đổi FROM SQL và sau đó kết hợp hàm biến đổi này với một nhóm biến đổi. Trước tiên, chúng ta sẽ tạo ra một hàm biến đổi vô hướng có tên là ADDRESS_TRANSFORM, bằng cách sử dụng câu lệnh CREATE FUNCTION (SQL vô hướng, bảng hay dòng). Trong ví dụ này, chúng ta xác định tham số đầu vào có tên là addr của kiểu Address_t. Chúng ta cũng xác định hàm này sẽ trả về VARCHAR (42), đủ lớn để chứa các thuộc tính địa chỉ được nối vào nhau. Thân của hàm SQL bao gồm câu lệnh RETURN, trong đó các thuộc tính địa chỉ đối với một thể hiện của kiểu dữ liệu có cấu trúc Address_t tìm được thông qua các phương thức quan sát của chúng (được xác định bởi toán tử '..') và được nối với nhau (bởi toán tử '||' ) thành một chuỗi đơn để hình thành một địa chỉ gửi thư. Trước khi có thể sử dụng hàm biến đổi này, chúng ta phải sử dụng câu lệnh CREATE TRANSFORM để kết hợp các hàm biến đổi ADDRESS_TRANSFORM với tên nhóm và kiểu dữ liệu. Câu lệnh CREATE TRANSFORM cho phép dùng hàm hiện có như một hàm biến đổi. Nếu bạn không xác định tên nhóm khi bạn chạy một ứng dụng tham chiếu đến kiểu dữ liệu có cấu trúc, thì DB2 sử dụng tên nhóm có tên là DB2_PROGRAM và giả định rằng tên nhóm này đã được xác định cho kiểu dữ liệu có cấu trúc. Nếu DB2_PROGRAM chưa được xác định cho kiểu dữ liệu có cấu trúc, thì bạn có thể tạo nhóm đó cho kiểu dữ liệu có cấu trúc của bạn. Chúng tôi sẽ đưa ra câu lệnh CREATE TRANSFORM, xác định kiểu dữ liệu có cấu trúc Address_t và tên nhóm DB2_PROGRAM. Câu lệnh FROM SQL xác định hàm cụ thể sẽ được sử dụng để biến đổi một giá trị thành giá trị của kiểu dữ liệu được xây dựng sẵn biểu diễn kiểu dữ liệu có cấu trúc. Mệnh đề WITH FUNCTION xác định hàm biến đổi chức năng (trường hợp này là ADDRESS_TRANSFORM). Bây giờ chúng ta đã sẵn sàng để chèn một số giá trị vào bảng CLIENTS. Mệnh đề VALUES của câu lệnh INSERT bao gồm lệnh gọi đến address_t (), nó gọi trình kiến thiết cho kiểu dữ liệu có cấu trúc address_t để tạo ra một thể hiện của kiểu dữ liệu này với tất cả các thuộc tính được thiết lập giá trị rỗng. Toán tử hai dấu chấm gọi ra các phương thức biết đổi để thiết lập giá trị cho mỗi thuộc tính địa chỉ. Truy vấn tiếp theo xác định cột ADDRESS trong bảng CLIENTS lấy ra thông tin địa chỉ như là địa chỉ gửi thư được kết nối. Liệt kê 1. Sử dụng kiểu dữ liệu có cấu trúc như là kiểu dữ liệu cho cột trong một bảng thông thường connect to sample ... create type address_t as (street varchar(12), city varchar(12), province varchar(12), postal_code char(6)) mode db2sql create table clients (client_id integer generated always as identity, client_lname varchar(12), client_fname varchar(12), address address_t) create function address_transform (addr address_t) returns varchar(42) language sql return addr..street || ', ' || addr..city || ', ' || addr..province || ' ' || addr..postal_code create transform for address_t db2_program (from sql with function address_transform) insert into clients (client_lname, client_fname, address) values ('Nicholson', 'James', address_t() ..street('20 Indian Rd') ..city('Toronto') ..province('Ontario') ..postal_code('M6T2R1')) select client_id, client_fname, client_lname, address from clients CLIENT_ID CLIENT_FNAME CLIENT_LNAME ADDRESS ----------- ------------ ------------ --------------------------------------- --- 21 James Nicholson 20 Indian Rd, Toronto, Ontario M6T2R1 1 record(s) selected. connect reset Bây giờ, giả sử chúng ta muốn chỉ gọi ra phần tử địa chỉ (ví dụ: city) từ bảng CLIENTS. Để làm được điều đó, chúng ta sẽ gọi ra trình quan sát (bằng cách sử dụng toán tử hai dấu chấm) cho thuộc tính thành phố của giá trị của kiểu dữ liệu có cấu trúc trong cột ADDRESS (Liệt kê 2). Tương tự như vậy, nếu chúng ta muốn cập nhật giá trị của thuộc tính thành phố, chúng ta có thể gọi ra phương thức trình biến đổi của nó trong câu lệnh UPDATE (Liệt kê 2). Liệt kê 2. Cập nhật cá thể của kiểu dữ liệu có cấu trúc trong cột của một bảng thông thường connect to sample ... select client_id, client_lname, address..city as city from clients CLIENT_ID CLIENT_LNAME CITY ----------- ------------ ------------ 21 Nicholson Toronto 1 record(s) selected. update clients set address..city = 'Oakville' where client_id = 21 select client_id, client_lname, address..city as city from clients CLIENT_ID CLIENT_LNAME CITY ----------- ------------ ------------ 21 Nicholson Oakville 1 record(s) selected. connect reset Để minh họa hệ thống phân cấp bảng định kiểu đơn giản và thuộc tính kế thừa, chúng ta hãy tạo ra ba kiểu dữ liệu cấu trúc mới (Liệt kê 3): Emp_t, Salesperson_t, và Engineer_t. Emp_t là kiểu dữ liệu gốc, và định nghĩa của nó bao gồm các kiểu dữ liệu cấu trúc Address_t mà chúng ta đã tạo trước đây. Mệnh đề REF USING INTEGER xác định rằng kiểu dữ liệu INTEGER sẽ được sử dụng để biểu diễn kiểu REFERENCE của kiểu dữ liệu cấu trúc này và tất cả các nhóm con của nó. Bản thân REFERENCE là kiểu hệ thống, tức là kiểu dữ liệu của cột định danh đối tượng của bất kỳ bảng được định kiểu nào. (Bởi vì các bảng được định kiểu chứa các đối tượng có thể được tham chiếu bởi các đối tượng khác, mỗi bảng được định kiểu phải có một cột định tên đối tượng làm cột đầu tiên của nó.) Salesperson_t và Engineer_t là kiểu dữ liệu con, được tạo ra theo Emp_t, là siêu kiểu của chúng. Các định nghĩa cho các dữ liệu con này bao gồm các thuộc tính bổ sung mà không phải là một phần của dữ liệu kiểu Emp_t, nhưng nó làm cho các dữ liệu con này khác với siêu kiểu của chúng; tuy nhiên, các thuộc tính là một phần của định nghĩa siêu kiểu, được kế thừa bởi các kiểu dữ liệu con của chúng. Sau khi tạo ra các kiểu dữ liệu có cấu trúc cần thiết, chúng ta có thể tạo các bảng được định kiểu dựa trên các kiểu dữ liệu này (Liệt kê 3). Siêu bảng trong hệ thống phân cấp bảng nhỏ này được đặt tên là EMP. Tiếp tục sáng tạo theo trí tưởng tượng, chúng ta đặt tên cho cột trình định tên đối tượng là OID và chỉ rõ rằng giá trị của nó sẽ được người dùng tạo. Sau khi giá trị của cột OID được chèn vào, nó không thể sửa đổi được. Mệnh đề INHERIT SELECT PRIVILEGES quy định rằng bất kỳ người sử dụng hay nhóm người sử dụng nào giữ đặc quyền SELECT trên một siêu bảng sẽ được cấp đặc quyền tương đương trên các bảng con mới. Bây giờ ta đã sẵn sàng để chèn dữ liệu vào các bảng ENGINEER và SALESPERSON. Mệnh đề VALUES của câu lệnh INSERT trong cả hai trường hợp bao gồm lệnh gọi address_t (), lệnh này, như trong ví dụ trước đây, gọi trình kiến thiết đối với kiểu dữ liệu có cấu trúc address_t để tạo một thể hiện hiện của kiểu dữ liệu này với tất cả các thuộc tính được đặt các giá trị là rỗng . Toán tử hai dấu chấm gọi ra phương thức biến đổi để thiết lập giá trị cho từng thuộc tính địa chỉ. Mệnh đề VALUES cũng bao gồm một hàm lấy giá trị đối với các giá trị do người sử dụng quy định của cột OID, bởi vì giá trị phải được lấy cho kiểu REFERENCE của bảng đích. Theo mặc định, tên của hàm lấy giá trị giống với tên kiểu dữ liệu có cấu trúc (trong trường hợp này các kiểu dữ liệu tương ứng là Engineer_t và Salesperson_t,). Việc thực hiện truy vấn trong ba bảng trong hệ thống phân cấp này chứng tỏ rằng các bảng con ENGINEER và SALESPERSON đã kế thừa các cột trong siêu bảng của chúng (EMP), bao gồm cả cột trình định tên đối tượng (OID). Liệt kê 3. Hệ thống phân cấp bảng được định kiểu, chứng minh tính kế thừa connect to sample ... create type emp_t as (empno integer, lname varchar(12), fname varchar(12), deptno char(4), salary decimal(7,2), address address_t) ref using integer mode db2sql create type salesperson_t under emp_t as (commission decimal(7,2)) mode db2sql create type engineer_t under emp_t as (perf_bonus decimal(7,2), recog_award decimal(7,2)) mode db2sql create table emp of emp_t (ref is oid user generated) create table salesperson of salesperson_t under emp inherit select privileges create table engineer of engineer_t under emp inherit select privileges insert into engineer (oid, empno, lname, fname, deptno, salary, perf_bonus, recog_award, address) values (engineer_t(1), 42, 'Kidman', 'Jennifer', 'Z004', 65000.00, 4000.00, 2000.00, address_t() ..street('7 Dorval Rd') ..city('Markham') ..province('Ontario') ..postal_code('L6G2R1')) insert into salesperson (oid, empno, lname, fname, deptno, salary, commission, address) values (salesperson_t(2), 69, 'Theron', 'Maggie', 'C012', 49000.00, 15000.00, address_t() ..street('7 River St') ..city('Ottawa') ..province('Ontario') ..postal_code('K9G6R2')) select * from emp OID EMPNO LNAME FNAME DEPTNO SALARY ADDRESS ----------- ----------- ------------ ------------ ------ --------- ---------- ---------- 1 42 Kidman Jennifer Z004 65000.00 7 Dorval Rd, Mark... 2 69 Theron Maggie C012 49000.00 7 River St, Ottaw... 2 record(s) selected. select oid, empno, lname, deptno, salary, perf_bonus, recog_award, address from engineer OID EMPNO LNAME DEPTNO SALARY PERF_BONUS RECOG_AWARD ADDRESS ----------- ----------- ------------ ------ --------- ---------- ----------- ---------- 1 42 Kidman Z004 65000.00 4000.00 2000.00 7 Dorva... 1 record(s) selected. select oid, empno, lname, deptno, salary, commission, address from salesperson OID EMPNO LNAME DEPTNO SALARY COMMISSION ADDRESS ----------- ----------- ------------ ------ --------- ---------- ------------ ---------- 2 69 Theron C012 49000.00 15000.00 7 River St, Ottawa,... 1 record(s) selected. connect reset