Chương 10 : Giới thiệu về Kỹ thuật lập trình SQL

Nội dung bài viết

---

Trong Chương 6 và 7, chúng tôi đã mô tả một số khía cạnh của ngôn ngữ SQL, là tiêu chuẩn cho cơ sở dữ liệu quan hệ. Chúng tôi đã mô tả các câu lệnh SQL để định nghĩa dữ liệu, sửa đổi lược đồ, truy vấn, view và cập nhật. Chúng tôi cũng đã mô tả cách chỉ định các ràng buộc khác nhau đối với nội dung cơ sở dữ liệu, chẳng hạn như các ràng buộc toàn vẹn khóa và tham chiếu.

Trong chương này và chương tiếp theo, chúng ta thảo luận về một số phương pháp đã được phát triển để truy cập cơ sở dữ liệu từ các chương trình ứng dụng. Hầu hết việc truy cập cơ sở dữ liệu trong các ứng dụng thực tế được thực hiện thông qua các chương trình phần mềm triển khai các ứng dụng cơ sở dữ liệu. Phần mềm này thường được phát triển bằng ngôn ngữ lập trình như Java, C/C++/C#, COBOL (trong lịch sử) hoặc một số ngôn ngữ lập trình khác. Ngoài ra, nhiều ngôn ngữ khác, chẳng hạn như PHP, Python và JavaScript, cũng đang được sử dụng để lập trình truy cập cơ sở dữ liệu trong các ứng dụng Web. Trong chương này, chúng tôi tập trung vào cách cơ sở dữ liệu có thể được truy cập từ các ngôn ngữ lập trình truyền thống C/C++ và Java, ở chương tiếp theo, chúng tôi giới thiệu cách cơ sở dữ liệu được truy cập từ các ngôn ngữ như PHP. Nhớ lại từ Phần 2.3.1 rằng khi các câu lệnh cơ sở dữ liệu được chèn trong một chương trình, ngôn ngữ lập trình đa năng được gọi là ngôn ngữ máy chủ, trong khi ngôn ngữ cơ sở dữ liệu—SQL, trong trường hợp của chúng ta—được gọi là ngôn ngữ con dữ liệu. Trong một số trường hợp, các ngôn ngữ lập trình cơ sở dữ liệu đặc biệt được phát triển riêng để viết các ứng dụng cơ sở dữ liệu. Mặc dù nhiều ngôn ngữ trong số này được phát triển dưới dạng prototype, nhưng một số ngôn ngữ lập trình cơ sở dữ liệu đáng chú ý đã được sử dụng rộng rãi, chẳng hạn như PL/SQL của Oracle (Programming Language/SQL).

Điều quan trọng cần lưu ý là lập trình cơ sở dữ liệu là một chủ đề rất rộng. Có rất nhiều bộ sách giáo khoa dành cho từng kỹ thuật lập trình cơ sở dữ liệu và cách kỹ thuật đó được thực hiện trong một hệ thống cụ thể. Các kỹ thuật mới luôn được phát triển và những thay đổi đối với các kỹ thuật hiện có được tích hợp vào các phiên bản và ngôn ngữ hệ thống mới hơn. Một khó khăn khác trong việc trình bày chủ đề này là mặc dù có các tiêu chuẩn SQL, nhưng bản thân các tiêu chuẩn này liên tục phát triển và mỗi nhà cung cấp DBMS có thể có một số biến thể so với tiêu chuẩn. Vì điều này, chúng tôi đã chọn giới thiệu về một số loại kỹ thuật lập trình cơ sở dữ liệu chính và so sánh các kỹ thuật này, thay vì nghiên cứu chi tiết một phương pháp hoặc hệ thống cụ thể. Các ví dụ chúng tôi đưa ra nhằm minh họa những khác biệt chính mà một lập trình viên sẽ gặp phải khi sử dụng từng kỹ thuật lập trình cơ sở dữ liệu này. Chúng tôi sẽ cố gắng sử dụng các tiêu chuẩn SQL trong các ví dụ của chúng tôi hơn là mô tả một hệ thống cụ thể. Khi sử dụng một hệ thống cụ thể, các tài liệu trong chương này có thể dùng như phần giới thiệu nhưng nên được bổ sung bằng các hướng dẫn sử dụng hệ thống hoặc sách mô tả hệ thống cụ thể.

1. Tổng quan về kỹ thuật lập trình cơ sở dữ liệu và các vấn đề

Bây giờ chúng ta chú ý đến các kỹ thuật đã được phát triển để truy cập cơ sở dữ liệu từ các chương trình ứng dụng và đặc biệt là vấn đề làm thế nào để truy cập cơ sở dữ liệu SQL từ các chương trình ứng dụng. Phần trình bày của chúng ta về SQL trong Chương 6 và 7 tập trung vào các cấu trúc ngôn ngữ cho các thao tác cơ sở dữ liệu khác nhau—từ định nghĩa lược đồ và đặc tả ràng buộc đến truy vấn, cập nhật và chỉ định các dạng xem. Hầu hết các hệ thống cơ sở dữ liệu đều có giao diện tương tác trong đó các lệnh SQL này có thể được nhập trực tiếp vào màn hình để hệ thống cơ sở dữ liệu thực thi. Ví dụ: trong một hệ thống máy tính cài đặt Oracle RDBMS, lệnh SQLPLUS khởi động giao diện tương tác. Người dùng có thể nhập các lệnh hoặc truy vấn SQL trực tiếp trên nhiều dòng, kết thúc bằng dấu chấm phẩy và phím Enter (nghĩa là ";"). Ngoài ra, một tệp lệnh có thể được tạo và thực thi thông qua giao diện tương tác bằng cách nhập @. Hệ thống sẽ thực hiện các lệnh ghi trong file và hiển thị kết quả nếu có

Giao diện tương tác khá thuận tiện cho việc tạo lược đồ và ràng buộc hoặc cho các truy vấn đặc biệt không thường xuyên. Tuy nhiên, trên thực tế, phần lớn các tương tác với cơ sở dữ liệu được thực hiện thông qua các chương trình đã được thiết kế và thử nghiệm cẩn thận. Các chương trình này thường được gọi là chương trình ứng dụng hoặc ứng dụng cơ sở dữ liệu và được người dùng cuối sử dụng trong các giao dịch, như đã thảo luận trong Phần 1.4.3. Một cách sử dụng phổ biến khác của lập trình cơ sở dữ liệu là truy cập cơ sở dữ liệu thông qua một chương trình ứng dụng triển khai giao diện Web, chẳng hạn như khi đặt vé máy bay hoặc mua hàng trực tuyến. Trên thực tế, phần lớn các ứng dụng thương mại điện tử Web bao gồm một số lệnh truy cập cơ sở dữ liệu. Chương 11 giới thiệu tổng quan về lập trình cơ sở dữ liệu Web sử dụng PHP, một ngôn ngữ đã được sử dụng rộng rãi.

Trong phần này, đầu tiên, chúng tôi đưa ra một cái nhìn tổng quan về các cách tiếp cận chính đối với lập trình cơ sở dữ liệu. Sau đó, chúng tôi thảo luận về một số vấn đề xảy ra khi cố gắng truy cập cơ sở dữ liệu từ ngôn ngữ lập trình có mục đích chung và chuỗi lệnh điển hình để tương tác với cơ sở dữ liệu từ chương trình phần mềm

1.1 Các phương pháp lập trình cơ sở dữ liệu

Một số kỹ thuật tồn tại để bao gồm các tương tác cơ sở dữ liệu trong các chương trình ứng dụng. Các cách tiếp cận chính để lập trình cơ sở dữ liệu như sau:

• Nhúng các lệnh cơ sở dữ liệu vào một ngôn ngữ lập trình. Theo cách tiếp cận này, các câu lệnh cơ sở dữ liệu được nhúng vào ngôn ngữ lập trình máy chủ, nhưng chúng được xác định bằng một tiền tố đặc biệt. Ví dụ: tiền tố cho SQL nhúng là chuỗi EXEC SQL, đứng trước tất cả các lệnh SQL trong chương trình ngôn ngữ máy chủ. Trình biên dịch hoặc bộ tiền xử lý quét mã nguồn chương trình để xác định các câu lệnh cơ sở dữ liệu và trích xuất chúng để DBMS xử lý. Chúng được thay thế trong chương trình bằng cách gọi hàm do DBMS tạo ra. Kỹ thuật này thường được gọi là SQL nhúng.
• Sử dụng thư viện các hàm hoặc lớp (class) cơ sở dữ liệu. Một thư viện hàm được cung cấp cho ngôn ngữ lập trình máy chủ để gọi cơ sở dữ liệu. Ví dụ: có thể có các hàm để kết nối với cơ sở dữ liệu, chuẩn bị truy vấn, thực hiện truy vấn, thực hiện cập nhật, lặp lại kết quả truy vấn trên bản ghi tại một thời điểm, v.v. Các lệnh cập nhật và truy vấn cơ sở dữ liệu thực tế cũng như bất kỳ thông tin cần thiết nào khác đều được bao gồm dưới dạng tham số trong các lệnh gọi hàm. Cách tiếp cận này cung cấp cái được gọi là giao diện lập trình ứng dụng (API) để truy cập cơ sở dữ liệu từ các chương trình ứng dụng. Đối với các ngôn ngữ lập trình hướng đối tượng (OOPL), thư viện lớp được sử dụng. Ví dụ, Java có thư viện lớp JDBC, có thể tạo ra nhiều loại đối tượng như: đối tượng kết nối tới một cơ sở dữ liệu cụ thể, đối tượng truy vấn và đối tượng kết quả truy vấn. Mỗi loại đối tượng có một tập các thao tác gắn với lớp tương ứng với đối tượng.
• Thiết kế một ngôn ngữ hoàn toàn mới. Ngôn ngữ lập trình cơ sở dữ liệu được thiết kế từ đầu để tương thích với mô hình cơ sở dữ liệu và ngôn ngữ truy vấn. Các cấu trúc lập trình bổ sung như vòng lặp và câu lệnh điều kiện được thêm vào ngôn ngữ cơ sở dữ liệu để chuyển đổi nó thành ngôn ngữ lập trình chính thức. Một ví dụ về cách tiếp cận này là PL/SQL của Oracle. Tiêu chuẩn SQL có ngôn ngữ SQL/PSM để chỉ định các thủ tục được lưu trữ.

Trong thực tế, hai cách tiếp cận đầu tiên phổ biến hơn, vì nhiều ứng dụng đã được viết bằng ngôn ngữ lập trình đa năng nhưng yêu cầu một số quyền truy cập cơ sở dữ liệu. Cách tiếp cận thứ ba phù hợp hơn cho các ứng dụng có tương tác cơ sở dữ liệu chuyên sâu. Một trong những vấn đề chính với hai cách tiếp cận đầu tiên là impedance mismatch, điều này không xảy ra trong cách tiếp cận thứ ba

1.2 Impedance Mismatch

Impedance mismatch là thuật ngữ được sử dụng để chỉ các vấn đề xảy ra do sự khác biệt giữa mô hình cơ sở dữ liệu và mô hình ngôn ngữ lập trình. Ví dụ: mô hình quan hệ thực tế có ba cấu trúc chính: cột (thuộc tính) và kiểu dữ liệu của chúng, hàng (còn được gọi là bộ hoặc bản ghi) và bảng (bộ hoặc nhiều bộ bản ghi). Vấn đề đầu tiên có thể xảy ra là kiểu dữ liệu của ngôn ngữ lập trình khác với kiểu dữ liệu thuộc tính có sẵn trong mô hình dữ liệu. Do đó, cần phải có một ràng buộc cho từng ngôn ngữ lập trình máy chủ chỉ định cho từng loại thuộc tính các loại ngôn ngữ lập trình tương thích. Một ràng buộc khác nhau là cần thiết cho mỗi ngôn ngữ lập trình vì các ngôn ngữ khác nhau có các kiểu dữ liệu khác nhau. Ví dụ: các kiểu dữ liệu có sẵn trong C/C++ và Java là khác nhau và cả hai đều khác với kiểu dữ liệu SQL, là kiểu dữ liệu tiêu chuẩn cho cơ sở dữ liệu quan hệ

Một vấn đề khác xảy ra do kết quả của hầu hết các truy vấn là các bộ hoặc nhiều bộ (hàng) và mỗi bộ được tạo thành từ một chuỗi các giá trị thuộc tính. Trong chương trình, thường cần phải truy cập các giá trị dữ liệu riêng lẻ trong các bộ dữ liệu riêng lẻ để in hoặc xử lý. Do đó, cần có một ràng buộc để ánh xạ cấu trúc dữ liệu kết quả truy vấn, là một bảng, tới một cấu trúc dữ liệu thích hợp trong ngôn ngữ lập trình. Cần có một cơ chế để lặp qua các bộ dữ liệu trong kết quả truy vấn để truy cập một bộ dữ liệu tại một thời điểm và trích xuất các giá trị riêng lẻ từ bộ dữ liệu đó. Các giá trị thuộc tính được trích xuất thường được sao chép vào các biến chương trình thích hợp để chương trình xử lý thêm. Biến con trỏ hoặc biến vòng lặp thường được sử dụng để lặp qua các bộ dữ liệu trong kết quả truy vấn. Các giá trị riêng lẻ trong mỗi bộ sau đó được trích xuất thành các biến chương trình riêng biệt thích hợp

Impedance mismatch ít xảy ra vấn đề hơn khi một ngôn ngữ lập trình cơ sở dữ liệu đặc biệt được thiết kế sử dụng cùng một mô hình dữ liệu và kiểu dữ liệu như mô hình cơ sở dữ liệu. Một ví dụ về ngôn ngữ như vậy là PL/SQL của Oracle. Tiêu chuẩn SQL cũng có đề xuất cho một ngôn ngữ lập trình cơ sở dữ liệu như vậy, được gọi là SQL/PSM. Đối với cơ sở dữ liệu đối tượng, mô hình dữ liệu đối tượng (xem Chương 12) khá giống với mô hình dữ liệu của ngôn ngữ lập trình Java, do đó, Impedance mismatch được giảm đáng kể khi Java được sử dụng làm ngôn ngữ máy chủ để truy cập cơ sở dữ liệu đối tượng tương thích với Java. Một số ngôn ngữ lập trình cơ sở dữ liệu đã được triển khai dưới dạng prototype

1.3 Tương tác điển hình trong lập trình cơ sơ dữ liệu

Khi một lập trình viên hoặc kỹ sư phần mềm viết một chương trình yêu cầu quyền truy cập vào cơ sở dữ liệu, việc chương trình chạy trên một hệ thống máy tính trong khi cơ sở dữ liệu được cài đặt trên một hệ thống máy tính khác là điều khá phổ biến. Nhớ lại từ Phần 2.5 rằng kiến trúc phổ biến để truy cập cơ sở dữ liệu là mô hình server/client ba tầng, trong đó chương trình client xử lý việc hiển thị thông tin trên máy tính xách tay hoặc thiết bị di động, thường là ứng dụng Web hoặc ứng dụng di động, một ứng dụng tầng giữa, chương trình triển khai logic nhưng bao gồm một số lệnh gọi đến một hoặc nhiều máy chủ cơ sở dữ liệu ở tầng dưới cùng để truy cập hoặc cập nhật dữ liệu. Khi viết một chương trình ứng dụng như vậy, một trình tự tương tác thông thường như sau:

1. Khi chương trình ứng dụng yêu cầu quyền truy cập vào một cơ sở dữ liệu cụ thể, trước tiên chương trình phải thiết lập hoặc mở kết nối đến máy chủ cơ sở dữ liệu. Thông thường, điều này liên quan đến việc chỉ định địa chỉ Internet (URL) của máy đặt máy chủ cơ sở dữ liệu, cùng với việc cung cấp tên tài khoản đăng nhập và mật khẩu để truy cập cơ sở dữ liệu.
2. Sau khi kết nối được thiết lập, chương trình có thể tương tác với cơ sở dữ liệu bằng cách gửi truy vấn, cập nhật và các lệnh cơ sở dữ liệu khác. Nói chung hầu hết các loại câu lệnh SQL đều có thể đưa vào một chương trình ứng dụng
3. Khi chương trình không còn cần truy cập vào một cơ sở dữ liệu cụ thể, nó sẽ chấm dứt hoặc đóng kết nối với cơ sở dữ liệu.

Một chương trình có thể truy cập nhiều cơ sở dữ liệu nếu cần. Trong một số phương pháp lập trình cơ sở dữ liệu, tại một thời điểm chỉ có một kết nối có thể hoạt động, trong khi ở các phương pháp khác, nhiều kết nối có thể được thiết lập đồng thời

2. Embedded SQL, Dynamic SQL, và SQLJ

Trong phần này, chúng tôi đưa ra một cái nhìn tổng quan về các kỹ thuật nhúng các câu lệnh SQL trong một ngôn ngữ lập trình có mục đích chung. Chúng tôi tập trung vào hai ngôn ngữ: C và Java. Các ví dụ được sử dụng với ngôn ngữ C, được gọi là SQL nhúng, được trình bày trong Phần 2.1 đến 2.3 và có thể được điều chỉnh cho phù hợp với các ngôn ngữ lập trình tương tự khác. Các ví dụ sử dụng Java, được gọi là SQLJ, được trình bày trong Phần 2.4 và 2.5. Trong phương pháp nhúng này, ngôn ngữ lập trình được gọi là ngôn ngữ máy chủ. Hầu hết các câu lệnh SQL—bao gồm dữ liệu hoặc định nghĩa ràng buộc, truy vấn, cập nhật hoặc định nghĩa dạng xem—có thể được nhúng trong chương trình ngôn ngữ máy chủ

2.1 Truy xuất các bộ dữ liệu đơn lẻ bằng Embedded SQL

Để minh họa các khái niệm về embedded SQL, chúng tôi sẽ sử dụng C làm ngôn ngữ lập trình máy chủ. Trong một chương trình C, một câu lệnh SQL nhúng được phân biệt với các câu lệnh ngôn ngữ lập trình bằng cách đặt trước nó bằng từ khóa EXEC SQL để trình biên dịch có thể tách các câu lệnh embedded SQL khỏi mã nguồn ngôn ngữ máy chủ. Các câu lệnh SQL trong một chương trình được kết thúc bởi một END-EXEC phù hợp hoặc bởi một dấu chấm phẩy (;). Các quy tắc tương tự áp dụng cho việc nhúng SQL vào các ngôn ngữ lập trình khác

Trong một lệnh embedded SQL, lập trình viên có thể tham chiếu đến các biến chương trình C được khai báo đặc biệt; chúng được gọi là các biến dùng chung vì chúng được sử dụng trong cả chương trình C và các câu lệnh SQL nhúng. Các biến được chia sẻ có tiền tố là dấu hai chấm (:) khi chúng xuất hiện trong câu lệnh SQL. Điều này phân biệt tên biến chương trình với tên của cấu trúc lược đồ cơ sở dữ liệu chẳng hạn như thuộc tính (tên cột) và quan hệ (tên bảng). Nó cũng cho phép các biến chương trình có cùng tên với tên thuộc tính, vì chúng có thể phân biệt được bằng tiền tố dấu hai chấm (:) trong câu lệnh SQL. Tên của các cấu trúc lược đồ cơ sở dữ liệu—chẳng hạn như các thuộc tính và quan hệ—chỉ có thể được sử dụng trong các lệnh SQL, nhưng các biến chương trình dùng chung có thể được sử dụng ở nơi khác trong chương trình C mà không cần tiền tố dấu hai chấm (:).

Giả sử rằng chúng ta muốn viết chương trình C để xử lý cơ sở dữ liệu COMPANY trong Hình 5.5. Chúng ta cần khai báo các biến chương trình sao cho phù hợp với các loại thuộc tính cơ sở dữ liệu mà chương trình sẽ xử lý. Lập trình viên có thể chọn tên của các biến chương trình; chúng có thể có hoặc không có tên trùng với thuộc tính cơ sở dữ liệu tương ứng của chúng. Chúng ta sẽ sử dụng các biến chương trình C được khai báo trong đoạn code dưới cho tất cả các ví dụ của chúng ta và hiển thị các đoạn chương trình C mà không có khai báo biến. Các biến dùng chung được khai báo trong phần khai báo của chương trình, như trong đoạn code dưới (dòng 1 đến dòng 7). Dưới đây là một số liên kết phổ biến của các loại C với các loại SQL. Các kiểu SQL INTEGER, SMALLINT, REAL và DOUBLE tương ứng được ánh xạ tới các kiểu dữ liệu C là long, short, float và double. Các chuỗi có độ dài cố định và độ dài khác nhau (CHAR [i], VARCHAR [i]) trong SQL có thể được ánh xạ tới các mảng ký tự (char [i+1], varchar [i+1]) trong C dài hơn một ký tự hơn kiểu SQL vì các chuỗi trong C được kết thúc bằng ký tự NULL (\0), ký tự này không phải là một phần của chính chuỗi ký tự đó. Mặc dù varchar không phải là kiểu dữ liệu C tiêu chuẩn, nhưng nó được cho phép khi C được sử dụng để lập trình cơ sở dữ liệu SQL.

0) int loop ;

1) EXEC SQL BEGIN DECLARE SECTION ;

2) varchar dname [16], fname [16], lname [16], address [31] ;

3) char ssn [10], bdate [11], sex [2], minit [2] ;

4) float salary, raise ;

5) int dno, dnumber ;

6) int SQLCODE ; char SQLSTATE [6] ;

7) EXEC SQL END DECLARE SECTION ;

Lưu ý rằng các lệnh embedded SQL duy nhất trong đoạn code là các dòng 1 và 7, lệnh này yêu cầu trình biên dịch lưu ý các tên biến C giữa BEGIN DECLARE và END DECLARE vì chúng có thể được bao gồm trong các câu lệnh embedded SQL —miễn là chúng được đặt trước bằng dấu hai chấm (:). Các dòng từ 2 đến 5 là các khai báo chương trình C thông thường. Các biến chương trình C được khai báo từ dòng 2 đến dòng 5 tương ứng với các thuộc tính của bảng EMPLOYEE và DEPARTMENT từ cơ sở dữ liệu COMPANY trong Hình 5.5 được khai báo bởi SQL DDL trong Hình 6.1. Các biến được khai báo ở dòng 6—SQLCODE và SQLSTATE—được gọi là các biến giao tiếp SQL; chúng được sử dụng để giao tiếp lỗi và điều kiện ngoại lệ giữa hệ thống cơ sở dữ liệu và chương trình thực thi. Dòng 0 hiển thị một vòng lặp biến chương trình sẽ không được sử dụng trong bất kỳ câu lệnh SQL nhúng nào, vì vậy nó được khai báo bên ngoài phần khai báo SQL

Kết nối với Cơ sở dữ liệu. Lệnh SQL để thiết lập kết nối tới cơ sở dữ liệu có dạng sau:

CONNECT TO <server name>AS <connection name>

AUTHORIZATION <user account name and password> ;

Nói chung, vì người dùng hoặc chương trình có thể truy cập vào một số máy chủ cơ sở dữ liệu, nên có thể thiết lập một số kết nối nhưng chỉ có một kết nối có thể hoạt động tại bất kỳ thời điểm nào. Lập trình viên hoặc người dùng có thể sử dụng chúng để thay đổi từ kết nối hiện đang hoạt động sang một kết nối khác bằng cách sử dụng lệnh sau:

SET CONNECTION <connection name> ;

Khi kết nối không còn cần thiết nữa, nó có thể bị chấm dứt bằng lệnh sau:

DISCONNECT <connection name> ;

Trong các ví dụ trong chương này, chúng tôi giả sử rằng kết nối thích hợp đã được thiết lập với cơ sở dữ liệu COMPANY và đó là kết nối hiện đang hoạt động.

Các biến giao tiếp SQLCODE và SQLSTATE. Hai biến truyền thông đặc biệt được DBMS sử dụng để truyền đạt các điều kiện lỗi hoặc ngoại lệ cho chương trình là SQLCODE và SQLSTATE. Biến SQLCODE được hiển thị trong đoạn code là một biến số nguyên. Sau mỗi lệnh cơ sở dữ liệu được thực thi, DBMS trả về một giá trị trong SQLCODE. Giá trị 0 chỉ ra rằng câu lệnh đã được DBMS thực thi thành công. Nếu SQLCODE > 0 (hoặc cụ thể hơn, nếu SQLCODE = 100), điều này cho biết rằng không có thêm dữ liệu (bản ghi) nào trong kết quả truy vấn. Nếu SQLCODE < 0, điều này cho biết đã xảy ra lỗi. Trong một số hệ thống—ví dụ: trong Oracle RDBMS—SQLCODE là một trường trong cấu trúc bản ghi được gọi là SQLCA (vùng giao tiếp SQL), do đó, nó được tham chiếu là SQLCA.SQLCODE. Trong trường hợp này, định nghĩa của SQLCA phải được đưa vào chương trình C bằng cách đưa vào dòng sau:

EXEC SQL include SQLCA ;

Trong các phiên bản sau của tiêu chuẩn SQL, một biến giao tiếp được gọi là SQLSTATE đã được thêm vào, là một chuỗi gồm năm ký tự. Giá trị '00000' trong SQLSTATE cho biết không có lỗi hoặc ngoại lệ; các giá trị khác chỉ ra các lỗi hoặc ngoại lệ khác nhau. Ví dụ: '02000' biểu thị 'không còn dữ liệu' khi sử dụng SQLSTATE. Hiện tại, cả SQLSTATE và SQLCODE đều có sẵn trong tiêu chuẩn SQL. Nhiều mã lỗi và ngoại lệ được trả về trong SQLSTATE được cho là đã được chuẩn hóa cho tất cả các nhà cung cấp và nền tảng SQL, trong khi các mã được trả về trong SQLCODE không được chuẩn hóa nhưng được định nghĩa bởi nhà cung cấp DBMS. Do đó, nói chung tốt hơn là sử dụng SQLSTATE vì điều này làm cho việc xử lý lỗi trong các chương trình ứng dụng trở nên độc lập với một DBMS cụ thể

Ví dụ về lập trình embedded SQL. Ví dụ đầu tiên của chúng tôi để minh họa lập trình embedded SQL là một đoạn chương trình sử dụng (vòng lặp) lấy đầu vào là số Ssn của một nhân viên và in một số thông tin từ bản ghi EMPLOYEE tương ứng trong cơ sở dữ liệu. Mã chương trình C được hiển thị dưới dạng đoạn chương trình E1 bên dưới. Chương trình đọc (nhập) một giá trị Ssn và sau đó truy xuất bộ EMPLOYEE với Ssn đó từ cơ sở dữ liệu thông qua lệnh SQL nhúng. Mệnh đề INTO (dòng 5) chỉ định các biến chương trình mà các giá trị thuộc tính từ bản ghi cơ sở dữ liệu được truy xuất. Các biến chương trình C trong mệnh đề INTO được bắt đầu bằng dấu hai chấm (:), như chúng ta đã thảo luận trước đó. Mệnh đề INTO chỉ có thể được sử dụng theo cách này khi kết quả truy vấn là một bản ghi duy nhất; nếu nhiều bản ghi được truy xuất, một lỗi sẽ được tạo ra.

//Program Segment E1:

 0) loop = 1 ;

1) while (loop) {

2) prompt("Enter a Social Security Number: ", ssn) ;

3) EXEC SQL

4) SELECT Fname, Minit, Lname, Address, Salary

5) INTO :fname, :minit, :lname, :address, :salary

6) FROM EMPLOYEE WHERE Ssn = :ssn ;

7) if (SQLCODE = = 0) printf(fname, minit, lname, address, salary)

8) else printf("Social Security Number does not exist: ", ssn) ;

9) prompt("More Social Security Numbers (enter 1 for Yes, 0 for No): ", loop) ;

10) }

Dòng 7 trong E1 minh họa giao tiếp giữa cơ sở dữ liệu và chương trình thông qua biến đặc biệt SQLCODE. Nếu giá trị được trả về bởi DBMS trong SQLCODE là 0, thì câu lệnh trước đó đã được thực thi mà không có lỗi hoặc điều kiện ngoại lệ. Dòng 7 kiểm tra điều này và giả định rằng nếu xảy ra lỗi, đó là do không có bộ EMPLOYEE nào tồn tại với Ssn đã cho; do đó, nó xuất ra một thông báo cho hiệu ứng đó (dòng 8).

Khi một bản ghi được truy xuất như trong ví dụ E1, lập trình viên có thể gán trực tiếp các giá trị thuộc tính của nó cho các biến chương trình C trong mệnh đề INTO, như trong dòng 5. Nói chung, một truy vấn SQL có thể truy xuất nhiều bộ dữ liệu. Trong trường hợp đó, chương trình C thường sẽ lặp qua các bộ đã truy xuất và xử lý từng bộ một. Khái niệm về cursor được sử dụng để cho phép chương trình ngôn ngữ máy chủ xử lý kết quả truy vấn cùng một lúc. Chúng tôi giới thiệu về cursor ở phần tiếp theo

2.2 Xử lý kết quả truy vấn bằng cursor

Cursor là một biến tham chiếu đến một bộ (hàng) đơn lẻ từ kết quả truy vấn truy xuất một tập hợp các bộ. Nó được sử dụng để lặp lại kết quả truy vấn, mỗi lần một bản ghi. Cursor được khai báo khi truy vấn SQL được khai báo. Sau đó trong chương trình, lệnh OPEN CURSOR tìm nạp kết quả truy vấn từ cơ sở dữ liệu và đặt con trỏ ở vị trí trước hàng đầu tiên trong kết quả của truy vấn. Hàng này trở thành hàng hiện tại cho con trỏ. Sau đó, các lệnh FETCH được đưa ra trong chương trình; mỗi FETCH di chuyển con trỏ đến hàng tiếp theo trong kết quả của truy vấn, biến nó thành hàng hiện tại và sao chép các giá trị thuộc tính của nó vào các biến chương trình C (ngôn ngữ máy chủ) được chỉ định trong lệnh FETCH bằng mệnh đề INTO. Biến con trỏ về cơ bản là một là một biến chạy trong vòng lặp qua các bộ trong kết quả truy vấn—mỗi lần một bộ

Để xác định thời điểm tất cả các bộ trong kết quả của truy vấn đã được xử lý, biến giao tiếp SQLCODE (hoặc, cách khác, SQLSTATE) được chọn. Nếu một lệnh FETCH được đưa ra dẫn đến việc di chuyển con trỏ qua bộ cuối cùng trong kết quả của truy vấn, thì một giá trị dương (SQLCODE > 0) được trả về trong SQLCODE, cho biết rằng không tìm thấy dữ liệu (bộ) nào (hoặc chuỗi ' 02000' được trả về trong SQLSTATE). Lập trình viên sử dụng điều này để chấm dứt vòng lặp trên các bộ dữ liệu trong kết quả truy vấn. Nói chung, có thể mở nhiều cursor cùng một lúc. Một lệnh CLOSE CURSOR được đưa ra để cho biết rằng chúng ta đã hoàn thành việc xử lý kết quả của truy vấn được liên kết với cursor đó

Một ví dụ về việc sử dụng cursor để xử lý kết quả truy vấn có nhiều bản ghi được hiển thị trong đoạn code dưới trong đó cursor có tên EMP được khai báo ở dòng 4. Cursor EMP được liên kết với truy vấn SQL được khai báo ở dòng 5 đến 6, nhưng truy vấn là không được thực thi cho đến khi lệnh OPEN EMP (dòng 8) được xử lý. Lệnh OPEN thực thi truy vấn và tìm nạp kết quả dưới dạng bảng vào không gian làm việc của chương trình, nơi chương trình có thể lặp qua từng hàng (bộ) riêng lẻ bằng các lệnh FETCH tiếp theo (dòng 9). Chúng ta giả định rằng các biến chương trình C phù hợp đã được khai báo như trong Hình 10.1. Đoạn chương trình trong E2 đọc (nhập) tên bộ phận (dòng 0), truy xuất số bộ phận phù hợp từ cơ sở dữ liệu (dòng 1 đến 3), sau đó truy xuất các nhân viên làm việc trong bộ phận đó thông qua cursor EMP đã khai báo. Một vòng lặp (dòng 10 đến 18) lặp lại từng bản ghi trong kết quả truy vấn, mỗi lần một bản ghi và in tên nhân viên, sau đó đọc (nhập) số tiền tăng lương cho nhân viên đó (dòng 12) và cập nhật lương của nhân viên trong cơ sở dữ liệu theo số tiền tăng (dòng 14 đến 16).

//Program Segment E2:

0) prompt("Enter the Department Name: ", dname) ;

1) EXEC SQL

2) SELECT Dnumber INTO :dnumber

3) FROM DEPARTMENT WHERE Dname = :dname ;

4) EXEC SQL DECLARE EMP CURSOR FOR

5) SELECT Ssn, Fname, Minit, Lname, Salary

6) FROM EMPLOYEE WHERE Dno = :dnumber

7) FOR UPDATE OF Salary ;

8) EXEC SQL OPEN EMP ;

9) EXEC SQL FETCH FROM EMP INTO :ssn, :fname, :minit, :lname, :salary ;

10) while (SQLCODE = = 0) {

11) printf("Employee name is:", Fname, Minit, Lname) ;

12) prompt("Enter the raise amount: ", raise) ;

13) EXEC SQL

14) UPDATE EMPLOYEE

15) SET Salary = Salary + :raise

16) WHERE CURRENT OF EMP ;

17) EXEC SQL FETCH FROM EMP INTO :ssn, :fname, :minit, :lname, :salary ;

18) }

 19) EXEC SQL CLOSE EMP ;

Ví dụ này cũng minh họa cách lập trình viên có thể cập nhật các bản ghi cơ sở dữ liệu. Khi một cursor được xác định cho các hàng sẽ được sửa đổi (cập nhật), chúng ta phải thêm mệnh đề FOR UPDATE OF trong phần khai báo cursor và liệt kê tên của bất kỳ thuộc tính nào sẽ được chương trình cập nhật. Điều này được minh họa trong dòng 7 của đoạn mã E2. Nếu các hàng bị xóa, phải thêm từ khóa FOR UPDATE mà không chỉ định bất kỳ thuộc tính nào. Trong lệnh UPDATE (hoặc DELETE) được nhúng, điều kiện WHERE CURRENT OF xác định rằng bộ hiện tại được cursor tham chiếu là bộ sẽ được cập nhật (hoặc xóa), như trong dòng 16 của E2.

Không cần đưa mệnh đề FOR UPDATE OF vào dòng 7 của E2 nếu kết quả của truy vấn chỉ được sử dụng cho mục đích truy xuất (không cập nhật hoặc xóa).

Các tùy chọn chung cho một khai báo con trỏ. Một số tùy chọn có thể được chỉ định khi khai báo một con trỏ. Dạng tổng quát của một khai báo con trỏ như sau:

DECLARE <cursor name> [ INSENSITIVE ] [ SCROLL ] CURSOR

[ WITH HOLD ] FOR <query specification>

[ ORDER BY <ordering specification> ]

[ FOR READ ONLY | FOR UPDATE [ OF <attribute list> ] ] ;

Chúng ta đã thảo luận ngắn gọn về các tùy chọn được liệt kê trong dòng cuối cùng. Mặc định là truy vấn dành cho mục đích truy xuất (FOR READ ONLY). Nếu một số bộ trong kết quả truy vấn được cập nhật, chúng ta cần chỉ định FOR UPDATE OF và liệt kê các thuộc tính có thể được cập nhật. Nếu một số bộ dữ liệu bị xóa, chúng ta cần chỉ định FOR UPDATE mà không có bất kỳ thuộc tính nào được liệt kê.

FETCH [ [ <fetch orientation> ] FROM ] <cursor name> INTO <fetch target list>;

Mệnh đề ORDER BY sắp xếp thứ tự các bộ sao cho lệnh FETCH sẽ tìm nạp chúng theo thứ tự đã chỉ định. Nó được chỉ định theo cách tương tự như mệnh đề tương ứng cho các truy vấn SQL (xem Phần 6.3.6). Hai tùy chọn cuối cùng khi khai báo một cursor (INSENSITIVE và WITH HOLD) đề cập đến các đặc tính giao dịch của các chương trình cơ sở dữ liệu, mà chúng ta sẽ thảo luận trong Chương 20

2.3 Chỉ định truy vấn ở runtime với dynamic SQL

Trong các ví dụ trước, các truy vấn SQL  được viết như một phần của mã nguồn chương trình máy chủ. Do đó, bất cứ khi nào chúng ta muốn viết một truy vấn khác, chúng ta phải sửa đổi code chương trình và thực hiện tất cả các bước liên quan (biên dịch, sửa lỗi, kiểm tra, v.v.). Trong một số trường hợp, việc viết một chương trình có thể thực hiện các truy vấn SQL khác nhau hoặc cập nhật (hoặc các thao tác khác) một cách linh hoạt trong thời gian chạy (runtime) là điều thuận tiện. Ví dụ: chúng ta có thể muốn viết một chương trình chấp nhận một truy vấn SQL được nhập từ màn hình, thực thi nó và hiển thị kết quả của nó, chẳng hạn như các giao diện tương tác có sẵn cho hầu hết các DBMS quan hệ. Một ví dụ khác là khi giao diện thân thiện với người dùng tạo các truy vấn dynamic SQL cho người dùng dựa trên đầu vào của người dùng thông qua giao diện Web hoặc Ứng dụng dành cho thiết bị di động. Trong phần này, chúng tôi đưa ra một cái nhìn tổng quan ngắn gọn về dynamic SQL, đây là một kỹ thuật để viết loại chương trình cơ sở dữ liệu này, bằng cách đưa ra một ví dụ đơn giản để minh họa cách dynamic SQL có thể hoạt động.

//Program Segment E3:

0) EXEC SQL BEGIN DECLARE SECTION ;

1) varchar sqlupdatestring [256] ;

2) EXEC SQL END DECLARE SECTION ; ...

3) prompt("Enter the Update Command: ", sqlupdatestring) ;

4) EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring ;

5) EXEC SQL EXECUTE sqlcommand ;

Đoạn chương trình E3 đọc một chuỗi do người dùng nhập vào (chuỗi đó phải là một lệnh cập nhật SQL trong ví dụ này) vào biến chương trình sqlupdatestring trong dòng 3. Sau đó, nó chuẩn bị chuỗi này dưới dạng một lệnh SQL trong dòng 4 bằng cách liên kết nó với biến sqlcommand. Dòng 5 sau đó thực hiện lệnh. Lưu ý rằng trong trường hợp này, không thể kiểm tra cú pháp hoặc các loại kiểm tra khác đối với lệnh tại thời điểm biên dịch, vì lệnh SQL không khả dụng cho đến khi chạy. Điều này trái ngược với các ví dụ trước đây của chúng tôi về embedded SQL, trong đó truy vấn có thể được kiểm tra tại thời điểm biên dịch vì văn bản của nó nằm trong mã nguồn chương trình

Trong E3, lý do để tách PREPARE và EXECUTE là nếu lệnh được thực hiện nhiều lần trong một chương trình, nó chỉ có thể được chuẩn bị một lần. Chuẩn bị lệnh thường liên quan đến cú pháp và các loại kiểm tra khác của hệ thống, cũng như tạo mã để thực thi lệnh. Có thể kết hợp các lệnh PREPARE và EXECUTE (dòng 4 và 5 trong E3) thành một câu lệnh duy nhất bằng cách viết

EXEC SQL EXECUTE IMMEDIATE :sqlupdatestring ;

Điều này rất hữu ích nếu lệnh chỉ được thực hiện một lần. Ngoài ra, lập trình viên có thể tách hai câu lệnh để bắt bất kỳ lỗi nào sau câu lệnh PREPARE như trong E3

Mặc dù việc bao gồm lệnh cập nhật động tương đối đơn giản trong dynamic SQL, truy vấn truy xuất động phức tạp hơn nhiều. Điều này là do lập trình viên không biết các loại hoặc số lượng thuộc tính sẽ được truy xuất bởi truy vấn SQL khi viết chương trình. Một cấu trúc dữ liệu phức tạp là cần thiết để cho phép các số lượng và loại thuộc tính khác nhau trong kết quả truy vấn nếu không có thông tin trước về truy vấn động. Các kỹ thuật tương tự như những kỹ thuật mà chúng ta sẽ thảo luận trong phần 3 có thể được sử dụng để gán các kết quả truy vấn truy xuất (và các tham số truy vấn) cho các biến chương trình.

2.5 SQLJ: Embedding SQL Commands (Java)

Trong các tiểu mục trước, chúng tôi đã giới thiệu tổng quan về cách các lệnh SQL có thể được nhúng trong ngôn ngữ lập trình truyền thống, sử dụng ngôn ngữ C trong các ví dụ của chúng tôi. Bây giờ chúng ta chú ý đến cách SQL có thể được nhúng trong ngôn ngữ lập trình hướng đối tượng, cụ thể là ngôn ngữ Java. SQLJ là một tiêu chuẩn đã được một số nhà cung cấp áp dụng để nhúng SQL vào Java. Về mặt lịch sử, SQLJ được phát triển sau JDBC, được sử dụng để truy cập cơ sở dữ liệu SQL từ Java bằng các thư viện lớp và lệnh gọi hàm. Chúng tôi thảo luận về JDBC trong Phần 3.2. Trong phần này, chúng tôi tập trung vào SQLJ vì nó được sử dụng trong Oracle RDBMS. Trình dịch SQLJ nói chung sẽ chuyển đổi các câu lệnh SQL thành Java, sau đó có thể được thực thi thông qua giao diện JDBC. Do đó, cần phải cài đặt trình điều khiển JDBC khi sử dụng SQLJ. Trong phần này, chúng ta tập trung vào cách sử dụng các khái niệm SQLJ để viết SQL nhúng trong chương trình Java

Trước khi có thể xử lý SQLJ bằng Java trong Oracle, cần phải nhập một số thư viện lớp, như trong đoạn code dưới đây. Chúng bao gồm các lớp JDBC và IO (dòng 1 và 2), cộng với các lớp bổ sung được liệt kê trong dòng 3, 4 và 5. Ngoài ra, trước tiên chương trình phải kết nối với cơ sở dữ liệu mong muốn bằng cách sử dụng lệnh gọi hàm getConnection, đây là một trong những các phương thức của lớp oracle trong dòng 5. Hàm này, trả về một đối tượng context mặc định, như sau

public static DefaultContext

getConnection(String url, String user, String password,

Boolean autoCommit)

 throws SQLException ;

1) import java.sql.* ;

2) import java.io.* ;

3) import sqlj.runtime.* ;

4) import sqlj.runtime.ref.* ;

5) import oracle.sqlj.runtime.* ; ...

6) DefaultContext cntxt =

7) oracle.getConnection("", "", "", true) ;

8) DefaultContext.setDefaultContext(cntxt) ;

Ví dụ, chúng ta có thể viết các câu lệnh từ dòng 6 đến 8 trong đoạn code trên để kết nối với cơ sở dữ liệu Oracle đặt tại URL <url name> bằng cách sử dụng thông tin đăng nhập của <user name > và <password > với cam kết tự động của từng lệnh và sau đó đặt kết nối này làm defaultcontext cho các lệnh tiếp theo.

Trong các ví dụ sau, chúng tôi sẽ không hiển thị các lớp hoặc chương trình Java hoàn chỉnh vì chúng tôi không có ý định dạy Java. Thay vào đó, chúng tôi sẽ hiển thị các đoạn chương trình minh họa việc sử dụng SQLJ. Đoạn code dưới cho thấy các biến chương trình Java được sử dụng trong các ví dụ của chúng tôi. Đoạn chương trình J1 đọc Ssn của nhân viên và in một số thông tin của nhân viên từ cơ sở dữ liệu

1) string dname, ssn , fname, fn, lname, ln, bdate, address ;

 2) char sex, minit, mi ;

3) double salary, sal ;

4) integer dno, dnumber ;

//Program Segment J1:

1) ssn = readEntry("Enter a Social Security Number: ") ;

2) try {

3) #sql { SELECT Fname, Minit, Lname, Address, Salary

4) INTO :fname, :minit, :lname, :address, :salary

5) FROM EMPLOYEE WHERE Ssn = :ssn} ;

6) } catch (SQLException se) {

7) System.out.println("Social Security Number does not exist: " + ssn) ;

8) Return ;

9) }

10) System.out.println(fname + " " + minit + " " + lname + " " + address + " " + salary)

Lưu ý rằng vì Java đã sử dụng khái niệm exception cho xử lý lỗi, nên một exception đặc biệt gọi là SQLException được sử dụng để trả về các lỗi hoặc điều kiện ngoại lệ sau khi thực hiện một lệnh cơ sở dữ liệu SQL. Điều này đóng vai trò tương tự như SQLCODE và SQLSTATE trong SQL embedded. Java có nhiều loại exception được xác định trước. Mỗi thao tác Java (chức năng) phải chỉ định các exception có thể được đưa ra—nghĩa là các điều kiện ngoại lệ có thể xảy ra trong khi thực thi mã Java của thao tác đó. Nếu một exception được xác định xảy ra, hệ thống sẽ chuyển quyền kiểm soát sang mã Java được chỉ định để xử lý exception. Trong J1, xử lý ngoại lệ cho SQLException được chỉ định trong dòng 7 và 8. Trong Java, cấu trúc sau

try {<operation>} catch (<exception>) {<exception handling code>} <continuation code>

được sử dụng để xử lý các exception xảy ra trong quá trình thực thi. Nếu không có exception xảy ra, nó sẽ được xử lý trực tiếp. Các exception mà mã có thể đưa ra trong một thao tác cụ thể phải được chỉ định như một phần của khai báo thao tác hoặc giao diện—ví dụ: theo định dạng sau:

<operation return type> <operation name> (<parameters>)

throws SQLException, IOException ;

Trong SQLJ, các lệnh SQL nhúng trong một chương trình Java được đặt trước #sql, như được minh họa trong J1 dòng 3, để chúng có thể được trình biên dịch xác định. #sql được sử dụng thay cho các từ khóa EXEC SQL được sử dụng trong SQL nhúng với ngôn ngữ lập trình C (xem Phần 2.1). SQLJ sử dụng mệnh đề INTO—tương tự như mệnh đề được sử dụng trong SQL nhúng—để trả về các giá trị thuộc tính được truy xuất từ cơ sở dữ liệu bằng truy vấn SQL vào các biến chương trình Java. Các biến chương trình được đặt trước dấu hai chấm (:) trong câu lệnh SQL, như trong SQL nhúng.

Trong J1, một bộ dữ liệu duy nhất được truy xuất bằng truy vấn SQLJ nhúng; đó là lý do tại sao chúng ta có thể gán trực tiếp các giá trị thuộc tính của nó cho các biến chương trình Java trong mệnh đề INTO ở dòng 4 trong đoạn code J1. Đối với các truy vấn truy xuất nhiều bộ dữ liệu, SQLJ sử dụng khái niệm vòng lặp, tương tự như cursor trong SQL nhúng

2.5 Xử lý kết quả truy vấn trong SQLJ bằng Iterators

Trong SQLJ, iterator là một loại đối tượng được liên kết với một tập hợp (bộ hoặc nhiều bộ) bản ghi trong kết quả truy vấn. Vòng lặp được liên kết với các bộ dữ liệu và thuộc tính xuất hiện trong kết quả truy vấn. Có hai loại iterator

1. Named Iterator được liên kết với kết quả truy vấn bằng cách liệt kê tên và loại thuộc tính xuất hiện trong kết quả truy vấn. Các tên thuộc tính phải tương ứng với các biến chương trình Java được khai báo thích hợp
2. Positional iterator chỉ liệt kê các loại thuộc tính xuất hiện trong kết quả truy vấn

Trong cả hai trường hợp, danh sách phải theo thứ tự giống như các thuộc tính được liệt kê trong mệnh đề SELECT của truy vấn. Tuy nhiên, việc lặp qua kết quả truy vấn là khác nhau đối với hai loại vòng lặp. Đầu tiên, chúng tôi đưa ra một ví dụ về việc sử dụng một vòng lặp có tên trong Hình 10.8, đoạn chương trình J2A. Dòng 9 trong đoạn code dưới cho thấy cách khai báo kiểu trình vòng lặp có tên Emp. Lưu ý rằng tên của các thuộc tính trong một loại trình vòng lặp được đặt tên phải khớp với tên của các thuộc tính trong kết quả truy vấn SQL. Dòng 10 cho biết cách một đối tượng lặp e thuộc loại Emp được tạo trong chương trình và sau đó được liên kết với một truy vấn (dòng 11 và 12)

Khi đối tượng thuộc vòng lặp được liên kết với một truy vấn (dòng 11 và 12 trong đoạn code J2A), chương trình sẽ tìm nạp kết quả truy vấn từ cơ sở dữ liệu và đặt biến lặp đến vị trí trước hàng đầu tiên trong kết quả của truy vấn. Nó trở thành hàng hiện tại cho iterator. Sau đó, các hoạt động tiếp theo được thực hiện trên đối tượng iterator; mỗi lần tiếp theo di chuyển biến lặp đến hàng tiếp theo trong kết quả của truy vấn, biến nó thành hàng hiện tại. Nếu hàng tồn tại, thao tác sẽ truy xuất các giá trị thuộc tính cho hàng đó vào các biến chương trình tương ứng. Nếu không còn hàng nào tồn tại, thao tác tiếp theo trả về NULL và do đó có thể được sử dụng để kiểm soát vòng lặp. Lưu ý biến lặp được đặt tên không cần mệnh đề INTO, bởi vì các biến chương trình tương ứng với các thuộc tính được truy xuất đã được chỉ định khi loại biến lặp được khai báo (dòng 9).

Trong đoạn code trên, lệnh (e.next()) ở dòng 13 thực hiện hai chức năng: Nó lấy bộ tiếp theo trong kết quả truy vấn và điều khiển vòng lặp WHILE. Khi chương trình hoàn tất việc xử lý kết quả truy vấn, lệnh e.close() (dòng 16) sẽ đóng vòng lặp.

Tiếp theo, hãy xem xét ví dụ tương tự bằng cách sử dụng các biến lặp vị trí như trong đoạn chương trình J2B. Dòng 9 cho thấy cách khai báo kiểu biến lặp vị trí Emppos. Sự khác biệt chính giữa cái này và trình vòng lặp có tên là không có tên thuộc tính (tương ứng với tên biến chương trình) trong vòng lặp vị trí—chỉ có các loại thuộc tính. Điều này có thể mang lại sự linh hoạt hơn, nhưng nó làm cho quá trình xử lý kết quả truy vấn phức tạp hơn một chút. Các loại thuộc tính vẫn phải tương thích với các loại thuộc tính trong kết quả truy vấn SQL và theo cùng một thứ tự. Dòng 10 cho biết cách một đối tượng lặp vị trí e thuộc loại Emppos được tạo trong chương trình và sau đó được liên kết với một truy vấn (dòng 11 và 12)

//Program Segment J2B:

0) dname = readEntry("Enter the Department Name: ") ;

1) try {

2) #sql { SELECT Dnumber INTO :dnumber

3) FROM DEPARTMENT WHERE Dname = :dname} ;

4) } catch (SQLException se) {

 5) System.out.println("Department does not exist: " + dname) ;

6) Return ;

7) }

8) System.out.printline("Employee information for Department: " + dname) ;

9) #sql iterator Emppos(String, String, String, String, double) ;

10) Emppos e = null ;

11) #sql e = { SELECT ssn, fname, minit, lname, salary

12) FROM EMPLOYEE WHERE Dno = :dnumber} ;

13) #sql { FETCH :e INTO :ssn, :fn, :mi, :ln, :sal} ;

14) while (!e.endFetch()) {

15) System.out.printline(ssn + " " + fn + " " + mi + " " + ln + " " + sal) ;

16) #sql { FETCH :e INTO :ssn, :fn, :mi, :ln, :sal} ;

 17) } ;

18) e.close() ;

Vòng lặp vị trí hoạt động theo cách giống với SQL nhúng hơn (xem Phần 2.2). Cần có lệnh FETCH INTO để lấy bộ dữ liệu tiếp theo trong kết quả truy vấn. Lần đầu tiên tìm nạp được thực hiện, nó nhận được bộ dữ liệu đầu tiên (dòng 13). Dòng 16 lấy bộ dữ liệu tiếp theo cho đến khi không còn bộ dữ liệu nào tồn tại trong kết quả truy vấn. Để kiểm soát vòng lặp, một hàm lặp vị trí e.endFetch() được sử dụng. Hàm này tự động được đặt thành giá trị TRUE khi trình vòng lặp ban đầu được liên kết với truy vấn SQL (dòng 11) và được đặt thành FALSE mỗi khi lệnh tìm nạp trả về một bộ giá trị hợp lệ từ kết quả truy vấn. Nó được đặt lại thành TRUE khi lệnh tìm nạp không tìm thấy bất kỳ bộ dữ liệu nào nữa.

3. Lập trình cơ sở dữ liệu bằng cách gọi hàm và sử dụng thư viện lớp: SQL/CLI và JDBC

SQL nhúng (xem Phần 2) đôi khi được gọi là phương pháp lập trình cơ sở dữ liệu tĩnh vì văn bản truy vấn được viết trong mã nguồn chương trình và không thể thay đổi nếu không biên dịch lại hoặc xử lý lại mã nguồn. Việc sử dụng các lệnh gọi hàm là một cách tiếp cận năng động hơn cho lập trình cơ sở dữ liệu so với SQL nhúng. Chúng ta đã thấy một kỹ thuật lập trình cơ sở dữ liệu động— SQL dynamic— trong Phần 2.3. Các kỹ thuật thảo luận ở đây cung cấp một cách tiếp cận khác để lập trình cơ sở dữ liệu động. Một thư viện chức năng, còn được gọi là giao diện lập trình ứng dụng (API), được sử dụng để truy cập cơ sở dữ liệu. Mặc dù điều này mang lại sự linh hoạt hơn vì không cần bộ tiền xử lý, nhưng một nhược điểm là cú pháp và các kiểm tra khác đối với các lệnh SQL phải được thực hiện trong thời gian chạy (runtime). Một nhược điểm khác là đôi khi nó yêu cầu lập trình phức tạp hơn để truy cập kết quả truy vấn vì loại và số thuộc tính trong kết quả truy vấn có thể không được biết trước.

Trong phần này, chúng tôi đưa ra cái nhìn tổng quan về hai giao diện gọi hàm. Đầu tiên chúng ta thảo luận về SQL/CLI, là một phần của tiêu chuẩn SQL. Điều này được phát triển như một tiêu chuẩn hóa của thư viện chức năng phổ biến được gọi là ODBC (Kết nối cơ sở dữ liệu mở). Chúng tôi sử dụng C làm ngôn ngữ máy chủ trong các ví dụ SQL/CLI của mình. Sau đó, chúng tôi đưa ra tổng quan về JDBC, là giao diện hàm gọi để truy cập cơ sở dữ liệu từ Java. Mặc dù người ta thường cho rằng JDBC là viết tắt của Java Database Connectivity, JDBC chỉ là nhãn hiệu đã đăng ký của Sun Microsystems (nay là Oracle), không phải là từ viết tắt.

Ưu điểm chính của việc sử dụng giao diện gọi hàm là nó giúp truy cập nhiều cơ sở dữ liệu trong cùng một chương trình ứng dụng dễ dàng hơn, ngay cả khi chúng được lưu trữ trong các gói DBMS khác nhau. Chúng ta sẽ thảo luận thêm về điều này trong Phần 3.2 khi thảo luận về lập trình cơ sở dữ liệu Java với JDBC, mặc dù ưu điểm này cũng áp dụng cho lập trình cơ sở dữ liệu với SQL/CLI và ODBC

3.1 Lập trình cơ sở dữ liệu với SQL/CLI bằng C làm ngôn ngữ chủ

Trước khi gọi hàm trong SQL/CLI, cần cài đặt các gói thư viện thích hợp trên máy chủ cơ sở dữ liệu. Các gói này được lấy từ nhà cung cấp của DBMS đang được sử dụng. Bây giờ chúng tôi đưa ra một cái nhìn tổng quan về cách SQL/CLI có thể được sử dụng trong một chương trình C. Chúng tôi sẽ minh họa phần trình bày của mình bằng đoạn chương trình mẫu CLI1 được hiển thị trong đoạn code sau

//Program CLI1: 
0) #include sqlcli.h ; 
1) void printSal() { 
2) SQLHSTMT stmt1 ; 
3) SQLHDBC con1 ; 
4) SQLHENV env1 ; 
5) SQLRETURN ret1, ret2, ret3, ret4 ; 
6) ret1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env1) ; 
7) if (!ret1) ret2 = SQLAllocHandle(SQL_HANDLE_DBC, env1, &con1) else exit ; 
8) if (!ret2) ret3 = SQLConnect(con1, "dbs", SQL_NTS, "js", SQL_NTS, "xyz", SQL_NTS) else exit ; 
9) if (!ret3) ret4 = SQLAllocHandle(SQL_HANDLE_STMT, con1, &stmt1) else exit ; 
10) SQLPrepare(stmt1, "select Lname, Salary from EMPLOYEE where Ssn = ?", SQL_NTS) ; 
11) prompt("Enter a Social Security Number: ", ssn) ; 
12) SQLBindParameter(stmt1, 1, SQL_CHAR, &ssn, 9, &fetchlen1) ; 
13) ret1 = SQLExecute(stmt1) ; 14) if (!ret1) { 
15) SQLBindCol(stmt1, 1, SQL_CHAR, &lname, 15, &fetchlen1) ; 
16) SQLBindCol(stmt1, 2, SQL_FLOAT, &salary, 4, &fetchlen2) ; 
17) ret2 = SQLFetch(stmt1) ; 18) if (!ret2) printf(ssn, lname, salary) 
19) else printf("Social Security Number does not exist: ", ssn) ; 
20) } 
21) }

Handles environment, connection, statement, và description records. Khi sử dụng SQL/CLI, các câu lệnh SQL được tạo động và chuyển dưới dạng tham số chuỗi trong các lệnh gọi hàm. Do đó, cần theo dõi thông tin về các tương tác của chương trình máy chủ với cơ sở dữ liệu trong cấu trúc dữ liệu trong runtime vì các lệnh cơ sở dữ liệu được xử lý trong runtime. Thông tin được lưu giữ trong bốn loại bản ghi, được biểu diễn dưới dạng struct trong kiểu dữ liệu C. Handles environment được sử dụng làm vùng chứa để theo dõi một hoặc nhiều kết nối cơ sở dữ liệu và để đặt thông tin môi trường. Connection theo dõi thông tin cần thiết cho một kết nối cơ sở dữ liệu cụ thể. Statement theo dõi thông tin cần thiết cho một câu lệnh SQL. Description records theo dõi thông tin về các bộ dữ liệu hoặc tham số—ví dụ: số lượng thuộc tính và loại của chúng trong một bộ hoặc số lượng và loại tham số trong một lệnh gọi hàm. Điều này là cần thiết khi lập trình viên không biết thông tin này về truy vấn khi viết chương trình. Trong các ví dụ của chúng tôi, chúng tôi giả định rằng lập trình viên biết chính xác truy vấn, vì vậy chúng tôi không hiển thị bất kỳ description record nào

Mỗi bản ghi có thể truy cập vào chương trình thông qua một biến con trỏ C—được gọi là một điều khiển cho bản ghi. Điều khiển được trả về khi một bản ghi được tạo lần đầu tiên. Để tạo một bản ghi và trả về phần điều khiển của nó, hàm SQL/CLI sau đây được sử dụng:

SQLAllocHandle(<handle_type>, <handle_1>, <handle_2>)

Trong chức năng này, các tham số như sau:

• <handle_type> cho biết loại bản ghi được tạo. Các giá trị có thể có cho tham số này là các từ khóa SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT hoặc SQL_HANDLE_DESC, tương ứng cho environment, connection, statement, or description record, respectively
• <handle_1> cho biết vùng chứa trong đó hàm thực thị được tạo. Ví dụ: đối với bản ghi kết nối, đây sẽ là môi trường trong đó kết nối được tạo và đối với bản ghi câu lệnh, đây sẽ là kết nối cho câu lệnh đó.
• <handle_2> là con trỏ (handle) tới bản ghi kiểu <handle_type> mới tạo.

Các bước lập trình cơ sở dữ liệu. Khi viết một chương trình C sẽ bao gồm các cuộc gọi cơ sở dữ liệu thông qua SQL/CLI, sau đây là các bước điển hình được thực hiện. Chúng tôi minh họa các bước bằng cách tham khảo ví dụ CLI1, đọc số An sinh xã hội của một nhân viên và in ra họ và lương của nhân viên đó

• Khai báo thư viện. Thư viện các hàm bao gồm SQL/CLI phải được đưa vào chương trình C. Cái này được gọi là sqlcli.h, và được bao gồm bằng cách sử dụng dòng 0.
• Khai báo các biến xử lý. Khai báo các biến xử lý kiểu SQLHSTMT, SQLHDBC, SQLHENV và SQLHDESC tương ứng cho các câu lệnh, kết nối, môi trường và mô tả cần thiết trong chương trình (dòng 2 đến 4). Đồng thời khai báo biến kiểu SQLRETURN (dòng 5) để ghi nhận kết quả trả lại từ các lệnh gọi hàm SQL/CLI. Mã trả về 0 (không) cho biết việc thực hiện lệnh gọi hàm thành công
• Bản ghi môi trường. Một bản ghi môi trường phải được thiết lập trong chương trình bằng cách sử dụng SQLAllocHandle. Hàm để thực hiện việc này được hiển thị trong dòng 6. Do bản ghi môi trường không được chứa trong bất kỳ bản ghi nào khác nên tham số <handle_1> là giá trị NULL của SQL_NULL_HANDLE (con trỏ NULL) khi tạo môi trường. Gán (con trỏ) tới bản ghi môi trường mới tạo được trả về trong biến env1 ở dòng 6.
• Kết nối với cơ sở dữ liệu. Một bản ghi kết nối được thiết lập trong chương trình bằng SQLAllocHandle. Trong dòng 7, bản ghi kết nối được tạo có hàm thực thi con1 và được chứa trong môi trường env1. Sau đó, một kết nối được thiết lập trong con1 tới một cơ sở dữ liệu máy chủ cụ thể bằng cách sử dụng chức năng SQLConnect của SQL/CLI (dòng 8). Trong ví dụ của chúng tôi, tên máy chủ cơ sở dữ liệu mà chúng tôi đang kết nối là dbs và tên tài khoản và mật khẩu để đăng nhập lần lượt là js và xyz
• Khai báo câu lệnh. Một bản ghi câu lệnh được thiết lập trong chương trình bằng SQLAllocHandle. Trong dòng 9, bản ghi câu lệnh được tạo có điều khiển stmt1 và sử dụng kết nối con1.
• Preparing an SQL statement and statement parameters. The SQL state[1]ment is prepared using the SQL/CLI function SQLPrepare. In line 10, this assigns the SQL statement string (the query in our example) to the statement handle stmt1. The question mark (?) symbol in line 10 repre[1]sents a statement parameter, which is a value to be determined at run[1]time—typically by binding it to a C program variable. In general, there could be several parameters in a statement string. They are distinguished by the order of appearance of the question marks in the statement string (the first ? represents parameter 1, the second ? represents parameter 2, and so on). The last parameter in SQLPrepare should give the length of the SQL statement string in bytes, but if we enter the keyword SQL_NTS, this indicates that the string holding the query is a NULL-terminated string so that SQL can calculate the string length automatically. This use of SQL_NTS also applies to other string parameters in the function calls in our examples.
• Chuẩn bị một câu lệnh SQL và các tham số câu lệnh. Câu lệnh SQL được chuẩn bị bằng hàm SQL/CLI SQLPrepare. Ở dòng 10, phần này gán chuỗi câu lệnh SQL (truy vấn trong ví dụ của chúng ta) cho hàm thực thi câu lệnh stmt1. Ký hiệu dấu chấm hỏi (?) trong dòng 10 đại diện cho một tham số câu lệnh, là một giá trị được xác định trong runtime—thường bằng cách liên kết nó với một biến chương trình C. Nói chung, có thể có một số tham số trong một chuỗi câu lệnh. Chúng được phân biệt theo thứ tự xuất hiện của các dấu chấm hỏi trong chuỗi câu lệnh (dấu ? đầu tiên đại diện cho tham số 1, dấu ? thứ hai đại diện cho tham số 2, v.v.). Tham số cuối cùng trong SQLPrepare sẽ cung cấp độ dài của chuỗi câu lệnh SQL theo byte, nhưng nếu chúng ta nhập từ khóa SQL_NTS, điều này cho biết rằng chuỗi giữ truy vấn là một chuỗi kết thúc bằng NULL để SQL có thể tự động tính toán độ dài chuỗi. Việc sử dụng SQL_NTS này cũng áp dụng cho các tham số chuỗi khác trong lệnh gọi hàm trong các ví dụ của chúng tôi.
• Ràng buộc các tham số câu lệnh. Trước khi thực hiện truy vấn, bất kỳ tham số nào trong chuỗi truy vấn phải được liên kết với các biến chương trình bằng cách sử dụng hàm SQL/CLI SQLBindParameter. Trong ví dụ, tham số (được biểu thị bằng ?) cho truy vấn đã chuẩn bị được tham chiếu bởi stmt1 được liên kết với biến chương trình C ssn ở dòng 12. Nếu có n tham số trong trạng thái SQL, chúng ta sẽ có n lệnh gọi hàm SQLBindParameter, mỗi lệnh với vị trí tham số khác nhau (1, 2, …, n).
• Thực thi câu lệnh. Sau những chuẩn bị này, bây giờ chúng ta có thể thực thi câu lệnh SQL được tham chiếu bởi hàm stmt1 bằng cách sử dụng hàm SQLExecute (dòng 13). Lưu ý rằng mặc dù truy vấn sẽ được thực hiện ở dòng 13, nhưng kết quả truy vấn vẫn chưa được gán cho bất kỳ biến chương trình C nào
• Xử lý kết quả truy vấn. Để xác định nơi kết quả của truy vấn được trả về, một kỹ thuật phổ biến là cách tiếp cận của cột bị ràng buộc. Ở đây, mỗi cột trong kết quả truy vấn được liên kết với một biến chương trình C bằng cách sử dụng hàm SQLBindCol. Các cột được phân biệt theo thứ tự xuất hiện của chúng trong truy vấn SQL. Trong ví dụ dòng 15 và 16, hai cột trong truy vấn (Lname và Salary) được liên kết với các biến chương trình C name và salary tương ứng.
• Truy xuất các giá trị cột. Cuối cùng, để truy xuất các giá trị cột vào các biến chương trình C, hàm SQLFetch được sử dụng (dòng 17). Chức năng này tương tự như lệnh FETCH của SQL nhúng. Nếu kết quả truy vấn có một tập hợp các bộ dữ liệu, thì mỗi lệnh gọi SQLFetch sẽ nhận bộ dữ liệu tiếp theo và trả về các giá trị cột của nó vào các biến chương trình được liên kết. SQLFetch trả về mã exception (khác 0) nếu không có bộ nào nữa trong kết quả truy vấn.

Như chúng ta có thể thấy, việc sử dụng các lệnh gọi hàm động đòi hỏi nhiều sự chuẩn bị để thiết lập các câu lệnh SQL và để liên kết các tham số câu lệnh và kết quả truy vấn với các biến chương trình thích hợp.

Trong CLI1, một bộ dữ liệu duy nhất được chọn bởi truy vấn SQL. Cho thấy một ví dụ về truy xuất nhiều bộ dữ liệu. Chúng ta giả định rằng các biến chương trình C phù hợp đã được khai báo như trong Hình 10.1. Đoạn chương trình trong CLI2 đọc (nhập) mã số phòng ban và sau đó truy xuất các nhân viên làm việc trong phòng ban đó. Sau đó, một vòng lặp lặp lại qua từng bản ghi nhân viên, từng bản ghi một và in họ và lương của nhân viên.

3.2 Lập trình cơ sở dữ liệu với SQL/CLI bằng C làm ngôn ngữ chủ

Bây giờ chúng ta chú ý đến cách SQL có thể được gọi từ ngôn ngữ lập trình hướng đối tượng Java. Các thư viện lớp và các lệnh gọi hàm liên quan cho quyền truy cập này được gọi là JDBC. Ngôn ngữ lập trình Java được thiết kế để không phụ thuộc vào nền tảng—nghĩa là một chương trình có thể chạy trên bất kỳ loại hệ thống máy tính nào có cài đặt trình thông dịch Java. Do tính di động này, nhiều nhà cung cấp RDBMS cung cấp các trình điều khiển JDBC để có thể truy cập hệ thống của họ thông qua các chương trình Java.

JDBC driver. JBDC Driver về cơ bản là việc triển khai các lớp và các đối tượng liên quan cũng như các lệnh gọi hàm được chỉ định trong JDBC cho RDBMS của một nhà cung cấp cụ thể. Do đó, một chương trình Java với các đối tượng JDBC và các lệnh gọi hàm có thể truy cập bất kỳ RDBMS nào có sẵn trình điều hỗ trợ JDBC

Vì Java là ngôn ngữ lập trình hướng đối tượng nên các thư viện hàm của nó được triển khai dưới dạng các lớp. Trước khi có thể xử lý các lệnh gọi hàm JDBC bằng Java, cần phải khai báo các thư viện JDBC, được gọi là java.sql.*. Chúng có thể được tải xuống và cài đặt qua Web

JDBC được thiết kế để cho phép một chương trình Java duy nhất kết nối với một số cơ sở dữ liệu khác nhau. Đôi khi chúng được gọi là datasource được chương trình Java truy cập và có thể được lưu trữ bằng cách sử dụng RDBMS từ các nhà cung cấp khác nhau nằm trên các máy khác nhau. Do đó, các truy cập datasource khác nhau trong cùng một chương trình Java có thể yêu cầu các trình điều khiển JDBC từ các nhà cung cấp khác nhau. Để đạt được tính linh hoạt này, một lớp JDBC đặc biệt được gọi là lớp trình driver manager được sử dụng, lớp này theo dõi các trình driver. Driver phải được đăng ký với driver manager trước khi nó được sử dụng. Các hoạt động (phương thức) của lớp manager bao gồm getDriver, registerDriver và deregisterDriver. Chúng có thể được sử dụng để thêm và xóa driver cho các hệ thống khác nhau một cách linh hoạt. Các chức năng khác thiết lập và đóng kết nối với datasource.

Để tải trình driver JDBC một cách chính xác, có thể sử dụng hàm Java chung để tải một lớp. Ví dụ: để tải trình driver JDBC cho Oracle RDBMS, có thể sử dụng lệnh sau:

Class.forName("oracle.jdbc.driver.OracleDriver"

Thao tác này sẽ đăng ký driver với trình quản lý driver và cung cấp driver đó cho chương trình. Ví dụ, cũng có thể tải và đăng ký (các) driver cần thiết trong dòng lệnh chạy chương trình bằng cách đưa vào dòng lệnh sau:

-Djdbc.drivers = oracle.jdbc.driver

Các bước lập trình JDBC. Sau đây là các bước điển hình được thực hiện khi viết một chương trình ứng dụng Java với quyền truy cập cơ sở dữ liệu thông qua việc gọi hàm JDBC. Chúng tôi minh họa các bước bằng cách tham khảo ví dụ JDBC1, đọc số An sinh xã hội của một nhân viên và in ra họ và lương của nhân viên đó.

//Program JDBC1:

0) import java.io.* ;

1) import java.sql.* ...

2) class getEmpInfo {

3) public static void main (String args []) throws SQLException, IOException {

 4) try { Class.forName("oracle.jdbc.driver.OracleDriver")

5) } catch (ClassNotFoundException x) {

6) System.out.println ("Driver could not be loaded") ;

7) }

8) String dbacct, passwrd, ssn, lname ;

9) Double salary ;

10) dbacct = readentry("Enter database account:") ;

11) passwrd = readentry("Enter password:") ;

12) Connection conn = DriverManager.getConnection

 13) ("jdbc:oracle:oci8:" + dbacct + "/" + passwrd) ;

14) String stmt1 = "select Lname, Salary from EMPLOYEE where Ssn = ?" ;

15) PreparedStatement p = conn.prepareStatement(stmt1) ;

16) ssn = readentry("Enter a Social Security Number: ") ;

17) p.clearParameters() ;

18) p.setString(1, ssn) ;

19) ResultSet r = p.executeQuery() ;

20) while (r.next()) {

21) lname = r.getString(1) ;

22) salary = r.getDouble(2) ;

23) system.out.printline(lname + salary) ;

24) } }

 25) }

• Import thư viện JDBC. Thư viện lớp JDBC phải được import vào chương trình Java. Các lớp này được gọi là java.sql.*, và có thể được nhập bằng dòng 1. Bất kỳ thư viện lớp Java bổ sung nào mà chương trình cần cũng phải được khai báo.
• Tải JDBC friver. Điều này được hiển thị trong các dòng 4 đến 7. Exception trong dòng 5 xảy ra nếu driver không được tải thành công.
• Khai báo các biến phù hợp. Đây là những biến cần thiết trong chương trình Java (dòng 8 và 9).
• Connection object. Một Connection object được tạo bằng hàm getConnection của lớp DriverManager của JDBC. Trong dòng 12 và 13, Connection object được tạo bằng cách sử dụng lệnh gọi hàm getConnection(urlstring), trong đó urlstring có dạng jdbc:oracle:<driverType>:<dbaccount>/<password> hoặc bạn có thể dùng hàm getConnection(url, dbaccount, password)
• Prepared statement object. Một Prepared statement object được tạo trong chương trình. Trong JDBC, có một lớp câu lệnh cơ bản, Statement, với hai lớp con đặc biệt: PreparedStatement và CallableStatement. Ví dụ trên minh họa cách các đối tượng PreparedStatement được tạo và sử dụng. Ví dụ tiếp theo minh họa kiểu đối tượng Statement khác. Ở dòng 14 trong ví dụ trên, một chuỗi truy vấn với một tham số đơn—được biểu thị bằng dấu ?—được tạo trong biến chuỗi stmt1. Trong dòng 15, một đối tượng p thuộc loại PreparedStatement được tạo dựa trên chuỗi truy vấn trong stmt1 và sử dụng đối tượng kết nối conn. Nói chung, lập trình viên nên sử dụng các đối tượng PreparedStatement nếu một truy vấn được thực hiện nhiều lần, vì nó sẽ chỉ được chuẩn bị, kiểm tra và biên dịch một lần, do đó tiết kiệm chi phí này cho các lần thực hiện truy vấn bổ sung.
• Cấu hình parameters cho câu lệnh. Ký hiệu dấu chấm hỏi (?) trong dòng 14 biểu thị một tham số câu lệnh, là một giá trị được xác định trong runtime, thường bằng cách liên kết nó với một biến chương trình Java. Nói chung, có thể có một số tham số, được phân biệt bởi thứ tự xuất hiện của các dấu chấm hỏi trong chuỗi câu lệnh (đầu tiên ? đại diện cho tham số 1, thứ hai ? đại diện cho tham số 2, v.v.), như chúng ta đã thảo luận trước đây.
• Ràng buộc các tham số câu lệnh. Trước khi thực hiện một truy vấn PreparedStatement, bất kỳ tham số nào cũng phải được liên kết với các biến chương trình. Tùy thuộc vào loại tham số, các hàm khác nhau chẳng hạn như setString, setInteger, setDouble, v.v. được áp dụng cho đối tượng PreparedStatement để đặt tham số cho nó. Hàm thích hợp nên được sử dụng để tương ứng với loại dữ liệu của tham số được đặt. Trong ví dụ trên, tham số (được biểu thị bằng ?) trong đối tượng p được liên kết với biến chương trình Java ssn trong dòng 18. Hàm setString được sử dụng vì ssn là một biến chuỗi có thể. Nếu có n tham số trong câu lệnh SQL, chúng ta nên có n hàm set..., mỗi hàm có một vị trí tham số khác nhau (1, 2, …, n). Nói chung, nên xóa tất cả các tham số trước khi đặt bất kỳ giá trị mới nào (dòng 17).
• Thực thi câu lệnh SQL. Sau những bước chuẩn bị này, bây giờ chúng ta có thể thực thi câu lệnh SQL được tham chiếu bởi đối tượng p bằng cách sử dụng hàm execQuery (dòng 19). Có một hàm chung thực thi trong JDBC, cộng với hai hàm chuyên dụng: execUpdate và execQuery. execUpdate được sử dụng cho các câu lệnh chèn, xóa hoặc cập nhật SQL và trả về một giá trị số nguyên cho biết số lượng bộ dữ liệu bị ảnh hưởng. execQuery được sử dụng cho các câu lệnh truy xuất SQL và trả về một đối tượng kiểu ResultSet mà chúng ta sẽ thảo luận tiếp theo
• Xử lý đối tượng ResultSet. Trong dòng 19, kết quả của truy vấn được trả về trong một đối tượng r thuộc loại ResultSet. Điều này giống như một mảng hai chiều hoặc một bảng, trong đó các bộ dữ liệu là các hàng và các thuộc tính được trả về là các cột. Một đối tượng ResultSet tương tự như một cursor trong SQL nhúng và iterator trong SQLJ. Trong ví dụ của chúng tôi, khi truy vấn được thực thi, r đề cập đến một bộ trước bộ đầu tiên trong kết quả truy vấn. Hàm r.next() (dòng 20) di chuyển đến bộ tiếp theo (hàng) trong đối tượng ResultSet và trả về NULL nếu không còn đối tượng nào nữa. Điều này được sử dụng để kiểm soát vòng lặp. Lập trình viên có thể tham chiếu đến các thuộc tính trong bộ dữ liệu hiện tại bằng cách sử dụng các hàm get ... khác nhau tùy thuộc vào loại của từng thuộc tính (ví dụ: getString, getInteger, getDouble, v.v.). Lập trình viên có thể sử dụng các thuộc tính vị trí (1, 2) hoặc tên thuộc tính thực tế ("Lname", "Salary") với các hàm get …. Trong các ví dụ của chúng tôi, chúng tôi đã sử dụng ký hiệu vị trí trong dòng 21 và 22.

//Program Segment JDBC2:
 0) import java.io.* ;
 1) import java.sql.*
 ...
 2) class printDepartmentEmps {
 3)   public static void main (String args [])
 throws SQLException, IOException {
 4) try { Class.forName("oracle.jdbc.driver.OracleDriver")
 5) } catch (ClassNotFoundException x) {
 6) System.out.println ("Driver could not be loaded") ;
 7) }
 8) String dbacct, passwrd, lname ;
 9) Double salary ;
10) Integer dno ;
11) dbacct = readentry("Enter database account:") ;
12) passwrd = readentry("Enter password:") ;
13) Connection conn = DriverManager.getConnection
14) ("jdbc:oracle:oci8:" + dbacct + "/" + passwrd) ;
15) dno = readentry("Enter a Department Number: ") ;
16) String q = "select Lname, Salary from EMPLOYEE where Dno = " +
 dno.tostring() ;
17) Statement s = conn.createStatement() ;
18) ResultSet r = s.executeQuery(q) ;
19) while (r.next()) {
20) lname = r.getString(1) ;
21) salary = r.getDouble(2) ;
22) system.out.printline(lname + salary) ;
23) } }
24) }

Nói chung, lập trình viên có thể kiểm tra các exception SQL sau mỗi lệnh gọi hàm JDBC. Chúng tôi đã không làm điều này để đơn giản hóa các ví dụ.

Lưu ý rằng JDBC không phân biệt giữa truy vấn trả về một bộ và truy vấn trả về nhiều bộ, không giống như một số kỹ thuật khác. Điều này là hợp lý vì một bộ kết quả chỉ là một trường hợp đặc biệt

Trong ví dụ JDBC1, một bộ dữ liệu duy nhất được chọn bởi truy vấn SQL, vì vậy vòng lặp trong dòng 20 đến 24 được thực thi nhiều nhất một lần. Ví dụ trong JDBC2 minh họa việc truy xuất nhiều bộ dữ liệu. Đoạn chương trình trong JDBC2 đọc (nhập) một mã số phòng ban và sau đó truy xuất các nhân viên làm việc trong phòng ban đó. Sau đó, một vòng lặp lặp lại qua từng bản ghi nhân viên, từng bản ghi một và in họ và lương của nhân viên. Ví dụ này cũng minh họa cách chúng ta có thể thực hiện một truy vấn trực tiếp mà không cần phải chuẩn bị nó như trong ví dụ trước. Kỹ thuật này được ưu tiên cho các truy vấn sẽ chỉ được thực hiện một lần vì nó đơn giản hơn để lập trình. Trong dòng 17 của JDBC2, lập trình viên tạo một đối tượng Statement (thay vì PreparedStatement, như trong ví dụ trước) mà không liên kết nó với một chuỗi truy vấn cụ thể. Chuỗi truy vấn q được chuyển đến đối tượng câu lệnh s khi nó được thực thi ở dòng 18.

4. Stored Procedures and SQL/PSM

Phần này giới thiệu thêm hai chủ đề liên quan đến lập trình cơ sở dữ liệu. Trong Phần 4.1, chúng tôi thảo luận về khái niệm stored procedure, là các mô-đun chương trình được lưu trữ bởi DBMS tại máy chủ cơ sở dữ liệu. Sau đó, trong Phần 4.2, chúng tôi thảo luận về các phần mở rộng cho SQL được chỉ định trong tiêu chuẩn để bao gồm các cấu trúc lập trình mục đích chung trong SQL. Các phần mở rộng này được gọi là SQL/PSM (SQL/Persistent Stored Modules) và có thể được sử dụng để viết các stored procedure. SQL/PSM cũng đóng vai trò là một ví dụ về ngôn ngữ lập trình cơ sở dữ liệu mở rộng mô hình cơ sở dữ liệu và ngôn ngữ—cụ thể là SQL—với các cấu trúc ngôn ngữ lập trình, chẳng hạn như câu lệnh điều kiện và vòng lặp

4.1 Stored Procedure và Functions

Trong phần trình bày của chúng tôi về các kỹ thuật lập trình cơ sở dữ liệu cho đến nay, có một giả định ngầm định rằng chương trình ứng dụng cơ sở dữ liệu đang chạy trên máy client, hoặc nhiều khả năng hơn là tại máy chủ ứng dụng ở tầng giữa của kiến trúc client/server 3-tier (xem Mục 2.5.4 và Hình 2.7). Trong cả hai trường hợp, máy mà chương trình đang thực thi khác với máy chủ cơ sở dữ liệu—và phần chính của gói phần mềm DBMS—được cài đặt. Mặc dù điều này phù hợp với nhiều ứng dụng, nhưng đôi khi nó hữu ích để tạo các mô-đun chương trình cơ sở dữ liệu—các thủ tục hoặc function—được lưu trữ và thực thi bởi DBMS tại máy chủ cơ sở dữ liệu. Về mặt lịch sử, chúng được gọi là thủ tục lưu trữ cơ sở dữ liệu, mặc dù chúng có thể là hàm hoặc thủ tục. Thuật ngữ được sử dụng trong tiêu chuẩn SQL cho các thủ tục được lưu trữ là các mô-đun được lưu trữ liên tục vì các chương trình này được lưu trữ liên tục bởi DBMS, tương tự như dữ liệu được lưu trữ bởi DBMS

Các thủ tục được lưu trữ rất hữu ích trong các trường hợp sau:

• Nếu một số ứng dụng cần một chương trình cơ sở dữ liệu, nó có thể được lưu trữ trên máy chủ và được gọi bởi bất kỳ chương trình ứng dụng nào. Điều này làm giảm nỗ lực trùng lặp và cải thiện tính mô đun của phần mềm.
• Thực thi một chương trình tại máy chủ có thể giảm chi phí truyền dữ liệu và liên lạc giữa máy khách và máy chủ trong một số trường hợp nhất định.
• Các thủ tục này có thể nâng cao sức mạnh mô hình hóa được cung cấp bởi các view bằng cách cho phép các loại dữ liệu dẫn xuất phức tạp hơn được cung cấp cho người dùng cơ sở dữ liệu thông qua các thủ tục được lưu trữ. Ngoài ra, chúng có thể được sử dụng để kiểm tra các ràng buộc phức tạp nằm ngoài khả năng đặc tả của assertions và trigger

​​​​​​Thông thường, nhiều hệ quản trị cơ sở dữ liệu thương mại cho phép các stored procedure và function được viết bằng ngôn ngữ lập trình có mục đích chung. Ngoài ra, một số stored procedure có thể được tạo bằng các lệnh SQL đơn giản như truy xuất và cập nhật. Dạng chung khai báo của stored procedure như sau:

CREATE PROCEDURE <procedure name> (<parameters>)
<local declarations>
<procedure body> ;

<parameters> và <local declarations> là tùy chọn và chỉ được chỉ định nếu cần. Để khai báo một fucntion, bạn cần phải có một kiểu trả về, vì vậy mẫu khai báo là:

CREATE FUNCTION <function name> (<parameters>)
RETURNS <return type>
<local declarations>
<function body> ;

Nếu thủ tục (hoặc hàm) được viết bằng ngôn ngữ lập trình, thông thường sẽ chỉ định ngôn ngữ cũng như tên tệp lưu trữ mã chương trình. Ví dụ: định dạng sau có thể được sử dụng:

CREATE PROCEDURE <procedure name> (<parameters>)
LANGUAGE <programming language name>
EXTERNAL NAME <file path name> ;

Nói chung, mỗi tham số nên có một loại tham số là một trong các kiểu dữ liệu SQL. Mỗi tham số cũng phải chỉ định rõ là tham số IN, OUT hoặc INOUT. Các giá trị này tương ứng với các tham số có giá trị chỉ là đầu vào, chỉ đầu ra (được trả về) hoặc cả đầu vào và đầu ra tương ứng.

Bởi vì các procedure và function được lưu trữ liên tục bởi DBMS, nên có thể gọi chúng từ các giao diện và kỹ thuật lập trình SQL khác nhau. Câu lệnh CALL trong tiêu chuẩn SQL có thể được sử dụng để gọi một store procedure—từ một giao diện tương tác hoặc từ SQL nhúng hoặc SQLJ. Cấu trúc như sau:

CALL <procedure or function name> (<argument list>) ;

Nếu câu lệnh này được gọi từ JDBC, nó sẽ được gán cho một đối tượng câu lệnh kiểu CallableStatement

​​​​​​​4.2 SQL/PSM: Mở rộng SQL để chỉ định các mô-đun được lưu trữ liên tục

SQL/PSM là một phần của tiêu chuẩn SQL chỉ định cách viết các mô-đun được lưu trữ lâu dài. Nó bao gồm các câu lệnh để tạo các hàm và thủ tục mà chúng ta đã mô tả trong phần trước. Nó cũng bao gồm các cấu trúc lập trình bổ sung để nâng cao sức mạnh của SQL nhằm mục đích viết mã (hoặc phần thân) của các hàm và thủ tục được lưu trữ

Trong phần này, chúng ta thảo luận về các cấu trúc SQL/PSM cho các câu lệnh (phân nhánh) điều kiện và cho các vòng lặp. Những điều này sẽ mang lại những điều mới mẻ cấu trúc mà SQL/PSM đã kết hợp; sau đó chúng tôi đưa ra một ví dụ để minh họa cách sử dụng các cấu trúc này

Câu lệnh rẽ nhánh có điều kiện trong SQL/PSM có dạng sau:

IF <condition> THEN <statement list>
ELSEIF <condition> THEN <statement list>
…
ELSEIF <condition> THEN <statement list>
ELSE <statement list>
END IF ;

Hãy xem xét ví dụ trong đoạn code dưới dây, minh họa cách cấu trúc rẽ nhánh có điều kiện có thể được sử dụng trong một hàm SQL/PSM. Hàm trả về một giá trị chuỗi (dòng 1) mô tả quy mô của một bộ phận trong công ty dựa trên số lượng nhân viên. Có một tham số số nguyên đầu vào, deptno, cung cấp số bộ phận. Biến cục bộ NoOfEmps được khai báo ở dòng 2. Truy vấn ở dòng 3 và 4 trả về số lượng nhân viên trong bộ phận và nhánh có điều kiện ở dòng 5 đến 8 rồi trả về một trong các giá trị {'HUGE', 'LỚN', ' MEDIUM', 'SMALL'} dựa trên số lượng nhân viên.

//Function PSM1:
0) CREATE FUNCTION Dept_size(IN deptno INTEGER)
1) RETURNS VARCHAR [7]
2) DECLARE No_of_emps INTEGER ;
3) SELECT COUNT(*) INTO No_of_emps
4) FROM EMPLOYEE WHERE Dno = deptno ;
5) IF No_of_emps > 100 THEN RETURN "HUGE"
6) ELSEIF No_of_emps > 25 THEN RETURN "LARGE"
7) ELSEIF No_of_emps > 10 THEN RETURN "MEDIUM"
8) ELSE RETURN "SMALL"
9) END IF ;

SQL/PSM có một số cấu trúc để lặp. Có các cấu trúc vòng lặp while và repeat tiêu chuẩn, có các dạng sau:

WHILE <condition> DO
 <statement list>
END WHILE ;
REPEAT
 <statement list>
UNTIL <condition>
END REPEAT ;

Ngoài ra còn có một cấu trúc lặp dựa trên cursor. Danh sách câu lệnh trong một vòng lặp như vậy được thực hiện một lần cho mỗi bộ trong kết quả truy vấn. cấu trúc này có dạng sau:

FOR <loop name> AS <cursor name> CURSOR FOR <query> DO
 <statement list>
END FOR ;

Các vòng lặp có thể có tên và có câu lệnh LEAVE <tên vòng lặp> để ngắt vòng lặp khi một điều kiện được thỏa mãn. SQL/PSM còn nhiều tính năng khác, nhưng chúng nằm ngoài phạm vi trình bày của chúng tôi

5. Đánh giá về 3 hướng tiếp cận

Trong phần này, chúng tôi so sánh ngắn gọn ba cách tiếp cận để lập trình cơ sở dữ liệu và thảo luận về ưu điểm và nhược điểm của từng cách tiếp cận.

Phương pháp tiếp cận SQL nhúng. Ưu điểm chính của phương pháp này là văn bản truy vấn là một phần của chính mã nguồn chương trình và do đó có thể được kiểm tra lỗi cú pháp và xác thực dựa trên lược đồ cơ sở dữ liệu tại thời điểm biên dịch. Điều này cũng làm cho chương trình khá dễ đọc, vì các truy vấn có thể dễ dàng nhìn thấy trong mã nguồn. Nhược điểm chính là mất tính linh hoạt trong việc thay đổi truy vấn trong thời gian chạy và thực tế là tất cả các thay đổi đối với truy vấn phải trải qua toàn bộ quá trình biên dịch lại. Ngoài ra, do các truy vấn đã được biết trước nên việc lựa chọn các biến chương trình để lưu giữ các kết quả truy vấn là một công việc đơn giản và do đó việc lập trình ứng dụng nói chung dễ dàng hơn. Tuy nhiên, đối với các ứng dụng phức tạp, nơi các truy vấn phải được tạo trong thời gian chạy, phương pháp gọi hàm sẽ phù hợp hơn

Sử dụng thư viện và phương pháp gọi hàm. Cách tiếp cận này mang lại sự linh hoạt hơn trong đó các truy vấn có thể được tạo trong runtime nếu cần. Tuy nhiên, điều này dẫn đến việc lập trình phức tạp hơn, vì các biến chương trình khớp với các cột trong kết quả truy vấn có thể không được biết trước. Bởi vì các truy vấn được truyền dưới dạng các chuỗi câu lệnh trong các lệnh gọi hàm, không thể thực hiện kiểm tra tại thời điểm biên dịch. Tất cả kiểm tra cú pháp và xác thực truy vấn phải được thực hiện trong thời gian chạy bằng cách chuẩn bị truy vấn và lập trình viên phải kiểm tra và tính đến các lỗi thời gian chạy bổ sung có thể có trong mã chương trình.

Phương pháp tiếp cận bằng ngôn ngữ lập trình cơ sở dữ liệu. Cách tiếp cận này không gặp phải vấn đề không phù hợp về ngôn ngữ, vì các kiểu dữ liệu của ngôn ngữ lập trình giống với các kiểu dữ liệu của cơ sở dữ liệu. Tuy nhiên, các lập trình viên phải học một ngôn ngữ lập trình mới thay vì sử dụng một ngôn ngữ mà họ đã quen thuộc. Ngoài ra, một số ngôn ngữ lập trình cơ sở dữ liệu dành riêng cho nhà cung cấp, trong khi ngôn ngữ lập trình mục đích chung có thể dễ dàng hoạt động với các hệ thống từ nhiều nhà cung cấp

6. Tóm tắt

Trong chương này, chúng tôi đã trình bày các tính năng bổ sung của ngôn ngữ cơ sở dữ liệu SQL. Đặc biệt, chúng tôi đã trình bày tổng quan về các kỹ thuật quan trọng nhất để lập trình cơ sở dữ liệu trong Phần 1. Sau đó, chúng ta đã thảo luận về các cách tiếp cận khác nhau để lập trình ứng dụng cơ sở dữ liệu trong Phần 2 đến 4.

Trong Phần 2, chúng ta đã thảo luận về kỹ thuật chung được gọi là SQL nhúng, trong đó các truy vấn là một phần của mã nguồn chương trình. Một trình biên dịch thường được sử dụng để trích xuất các lệnh SQL từ chương trình để DBMS xử lý và thay thế chúng bằng các câu lệnh gọi hàm tới mã được biên dịch của DBMS. Chúng tôi đã trình bày tổng quan về SQL nhúng, sử dụng ngôn ngữ lập trình C làm ngôn ngữ máy chủ trong các ví dụ. Chúng ta cũng đã thảo luận về kỹ thuật SQLJ để nhúng SQL vào các chương trình Java. Các khái niệm về cursor (đối với SQL nhúng) và vòng lặp (đối với SQLJ) đã được trình bày và minh họa bằng các ví dụ để cho thấy cách chúng được sử dụng để lặp qua các bộ trong kết quả truy vấn và trích xuất giá trị thuộc tính thành các biến chương trình để xử lý thêm.

Trong Phần 3, chúng ta đã thảo luận về cách sử dụng các thư viện lệnh để truy cập cơ sở dữ liệu SQL. Kỹ thuật này năng động hơn nhúng SQL nhưng yêu cầu lập trình phức tạp hơn vì các loại thuộc tính và số trong kết quả truy vấn có thể được xác định trong thời gian chạy. Tổng quan về tiêu chuẩn SQL/CLI được trình bày, với các ví dụ sử dụng C làm ngôn ngữ máy chủ. Chúng ta đã thảo luận về một số hàm trong thư viện SQL/CLI, cách các truy vấn được truyền dưới dạng chuỗi, cách các tham số truy vấn được gán trong môi trường runtime và cách các kết quả được trả về cho các biến chương trình. Sau đó, chúng tôi đã giới thiệu tổng quan về thư viện lớp JDBC, được sử dụng với Java và thảo luận về một số lớp và hoạt động của nó. Đặc biệt, lớp ResultSet được sử dụng để tạo các đối tượng chứa kết quả truy vấn, sau đó có thể lặp lại kết quả này bằng thao tác next(). Các hàm get và set để truy xuất giá trị thuộc tính và cài đặt giá trị tham số cũng đã được thảo luận.

Trong Phần 4, chúng tôi đã giới thiệu tổng quan ngắn gọn về các stored procedure và thảo luận về SQL/PSM như một ví dụ về ngôn ngữ lập trình cơ sở dữ liệu. Cuối cùng, chúng tôi so sánh ngắn gọn ba cách tiếp cận trong Phần 5. Điều quan trọng cần lưu ý là chúng tôi đã chọn đưa ra một cái nhìn tổng quan so sánh về ba cách tiếp cận chính đối với lập trình cơ sở dữ liệu vì nghiên cứu sâu về một cách tiếp cận cụ thể là sẽ cần nhiều thời gian hơn.