Bài giảng hệ quản trị csdl it10 Đại học mở hà nội

Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (4.55 MB, 79 trang )

Trang 1<div class="page_container" data-page="1">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 1

BÀI 1: TỔNG QUAN VỀ HỆ QUẢN TRỊ

CƠ SỞ DỮ LIỆU - SQL SERVER

Mục tiêu:

Trong bài này, Anh/Chị cần đạt được những mục tiêu sau:

1. Nắm được tổng quan về hệ quản trị cơ sở dữ liệu SQL Server bao gồm các thành phần, các đối tượng, kiến trúc,... của hệ quản trị CSDL SQL Server 2008.

2. Biết thao tác và làm việc với hệ quản trị.

Nội dung:

I. GIỚI THIỆU SQL SERVER.

SQL Server là hệ thống quản trị cơ sở dữ liệu quan hệ (Relational DataBase Management System- RDBMS) sử dụng các lệnh giáo chuyển Transaction-SQL để trao đổi dữ liệu giữa Client Computer và Server Computer.

Internet

 Cho phép lập trình kết nối với nhiều ngơn ngữ lập trình khác dùng xây dựng các ứng dụng đặc thù (Visual Basic, C, C++, ASP, ASP.NET, XML,...).

 Sử dụng câu lệnh truy vấn dữ liệu Transaction-SQL (Access là SQL, Oracle là PL/SQL).

Các ấn bản của SQL Server.

SQL Server có các ấn bản chính sau:

</div>Trang 2<div class="page_container" data-page="2">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 2

 Enterpise Manager: Là ấn bản đầy đủ của SQL Server có thể chạy trên 32CPU và 64GB RAM. Có các dịch vụ phân tích dữ liệu Analysis Service.  Standard: Giống như Enterprise nhưng bị hạn chế một số tính năng cao

cấp, có thể chạy trên 2CPU, 4GB RAM.

 Personal: Phiên bản này chủ yếu để chạy trên PC, nên có thể chạy trên các hệ điều hành Windows 9x, Windows XP, Windows 2000, Windows 2003…

 Developer: Là phiên bản tương tự như Enterprise nhưng bị giới hạn bởi số user kết nối đến.

 Desktop Engine: Là phiên bản một engine chỉ chạy trên desktop và khơng có giao diện người dùng (GUI), kích thước CSDL giới hạn bởi 2GB.  Win CE: Sử dụng cho các ứng dụng chạy trên Windows CE.

 Trial: Phiên bản dùng thử, bị giới hạn bởi thời gian.

 SQL Client: Là phiên bản dành cho máy khách, khi thực hiện khai thác sẽ thực hiện kết nối đến phiên bản SQL Server, phiên bản này cung cấp giao diện GUI khai thác cho người sử dụng.

 SQL Connectivity only: Là phiên bản sử dụng chỉ cho các ứng dụng để kết nối đến SQL Server, phiên bản này không cung cấp công cụ GUI cho người dùng khai thác SQL Server.

Các phiên bản này được cài đặt phụ thộc vào bộ cài đặt mà bạn chọn hoặc lựa chọn khai cài đặt (ví dụ phiên bản Enterprise, Standard, Personal,… bạn phải chọn theo bộ cài đặt, phiên bản SQL Client, Connectivity,… do bạn chọn trong các hộp thoại trong quá trình cài đặt).

Một số tính năng của Enterprise manager.

 Dễ cài đặt

 Hỗ trợ mơ hình Client/Server.

 Thích hợp trên các hệ điều hành Windows.  Hoạt động với nhiều giao thức truyền thông.  Hỗ trợ dịch vụ Data Warehousing.

 Thích hợp với chuẩn ANSI/ISO SQL-92.  Hỗ trợ nhân bản dữ liệu.

 Cung cấp dịch vụ tìm kiếm Full-Text.

</div>Trang 3<div class="page_container" data-page="3">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 3

 Sách trợ giúp- Book Online.

I.1.1. Mô hình hoạt động của sql server trên mạng máy tính. Mơ hình chung SQL Server trên mạng.

SQL Server là hệ quản trị CSDL hoạt động trên mạng, có thể thực hiện trao đổi dữ liệu theo nhiều mơ hình mạng khác nhau, nhiều giao thức và phương thức truyền tin khác nhau.

Trong sơ đồ trên thể hiện ba kiểu kết nối ứng dụng đến SQL Server:

</div>Trang 4<div class="page_container" data-page="4">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 4

 Kết nối trên Desktop: Có thể trên cùng máy tính với SQL Server hoặc kết nối qua mạng nội bộ.

 Kết nối qua mạng diện rộng: Thông qua đường truyền mạng xa kết nối đến SQL Server.

 Kết nối qua mạng Internet: Các ứng dụng kết nối thông qua máy chủ Internet, dịch vụ IIS thực hiện ứng dụng trên Internet (ASP, JSP, ASP.net,…)

</div>Trang 5<div class="page_container" data-page="5">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 5

Như sơ đồ trên nhận thấy SQL Server cho phép các ứng dụng kết nối theo các phương thức sau: OLE DB, ODBC, DB-Library, Embedded SQL, đây là các phương thức kết nối hữ ích cho những nhà phát triển ứng dụng.

</div>Trang 6<div class="page_container" data-page="6">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 6

Nếu xem xét cụ thể hơn ta có thể xem sơ đồ sau:

Trong sơ đồ trên cho thấy, SQL Server có thể thực hiện trao đổi dữ liệu với các ứng dụngt heo nhiều giao thực truyền tin khác nhau (TCP/IP, NetBeUI, Names Pipes,…), các ứng dụng có thể sử dụng nhiều phương thức kết nối khác nhau (OLE DB, ODBC, DB-Library).

I.1.2. Mơ hình kết nối ứng dụng trên mạng Internet.

Nếu xét riêng các ứng dụng kết nối với SQL Server trên mạng Internet, các máy chủ SQL Server sẽ được quản lý thông qua các hệ thống máy chủ mạng, hệ điều hành mạng, các ứng dụng (COM+, ASP, IIS) sẽ thông qua máy chủ mạng kết nối đến SQL Server, mô hình này có thể áp dụng cho các mạng nội bộ, diện rộng, ứng dụng được khai thác trên trình duyệt Internet Browser.

I.1.3. Các thành phần của sql server.

SQL Server được cấu thành bởi nhiều thành phần khác nhau, các thành phần có mối quan hệ trong một hệ thống, phối hợp với nhau để tạo thành một giải pháp hồn chính, nâng cao hiệu quả quản trị, phân tích, lưu trữ dữ liệu.

</div>Trang 7<div class="page_container" data-page="7">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 7 Relational DataBase Engine.

Đây là một engine có khả năng chứa dữ liệu dưới nhiều quy mô khác nhau, theo dạng bảng, hỗ trợ nhiều phương thức kết nối ADO, OLE DB, ODBC.

Replication.

Là công cụ dùng nhân bản dữ liệu, bạn có thể tạo một Server khác với bộ dữ liệu giống bộ dữ liệu trên Server chính. Cơng cụ tạo cơ chế tự đồng bộ dữ liệu giữa Server chính và Server nhân bản. Mục đích của việc tạo Server nhân bản là giảm tải cho Server chính, nâng cao hiệu quả phục vụ với số lượng người, phiên giao dịch lớn.

Data Transformation Service – DTS.

Là công cụ giúp bạn chuyển dữ liệu giữa các Server quản trị CSDL khác nhau, DTS có thể chuyển dữ liệu từ SQL Server sang Oracle, Access, DB,… trước khi chuyển dữ liệu DTS định dạng kiểu dữ liệu để chuyển sang hệ quản trị CSDL khác.

</div>Trang 8<div class="page_container" data-page="8">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 8 Analysis service.

Là công cụ giúp khai thác phân tích dữ liệu, hay khai phá dữ liệu theo phương thức đa chiều. Từ một tập dữ liệu sẵn có bạn có thể khai phá rồi từ đó đưa ra những nhận định, phân tích, đánh giá và dự đoán theo lĩnh vực nào đố, mỗi chiều trong ngữ cảnh này được coi là một tiêu chí xem xét của dữ liệu.

I.1.4. Cấu trúc cơ sở dữ liệu.

Chắc hẳn khi nghiên cứu đến hệ quản trị CSDL SQL Server bạn đã xem xét đến các hệ quản trị CSDL như DBase hoặc Access, với hệ quản trị CSDL như trên mỗi cơ sở dữ liệu khi sử dụng (thực hiện mở CSDL) sẽ mở trực tiếp từ tập tin chứa CSDL, tập tin chứa CSDL sẽ có một tập tin chính (ví dụ *.dbf hoặc *.mdb) và tập tin phụ nhưng khi ta thao tác ta chỉ cần quan tâm đến tập tin chính. Nên trong các ứng dụng thơng thường ta thường dùng các thao tác mở (open) để mở tập tin chính chứa CSDL và đóng (close) đóng tập tin chính chứa CSDL mà khơng cần quan tâm đến việc đã kết nối đến CSDL chưa (khơng có phương thức kết nối).

SQL Server quản lý trực tiếp các CSDL, danh sách mỗi Server sẽ gồm danh sách các tên CSDL, tên các CSDL là duy nhất, không trùng nhau. Mỗi CSDL SQL Server sẽ quản lý các cấu trúc vật lý của nó. Chính từ cách thức quản lý như trên mà việc quản trị cơ sở dữ liệu có một số đặc điểm sau:

+ Để Client khai thác CSDL trước hết phải thực hiện kết nối đến Server quản trị CSDL đó.

+ Chỉ thực hiện khai thác với các CSDL có tên trong danh sách các CSDL mà Server quản lý.

+ Không có các phương thức mở CSDL trực tiếp từ tập tin như Dbase hoặc Access.

+ Khi đã kết nối đến Server, Client chỉ thực hiện được quyền khai thác theo quy định đã định sẵn trong CSDL (phân quyền trong CSDL).

Cấu trúc cơ sở dữ liệu.

</div>Trang 9<div class="page_container" data-page="9">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 9

Cơ sở dữ liệu trong SQL Server lưu trữ theo 2 phần: phần dữ liệu (gồm một tập tin bắt buộc *.mdf và các tập tin phụ *.ndf) và phần nhật ký (*.ldf). Như vậy một cơ sở dữ liệu có ít nhất 2 tập tin.

Cấu trúc logic trong CSDL gồm các table, view và các object khác. Sau đây là cấu trúc một CSDL.

Sơ đồ quản trị cơ sở dữ liệu của SQL Server.

Cơ sở dữ liệu trong SQL Server chia thành 2 loại: Cơ sở dữ liệu hệ thống (do SQL Server sinh ra khi cài đặt) và cở sở dữ liệu người dùng (do người dùng tạo ta).

Cơ sở dữ liệu hệ thống gồm:

- Master: Lưu trữ các thơng tin login account, cấu hình hệ thống, thông tin quản trị các CSDL, là CSDL quan trọng nên thường được sao lưu để bảo đảm an toàn cho hệ thống.

</div>Trang 10<div class="page_container" data-page="10">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 10

- Tempdb: Chứa các table tạm thời và các thủ tục được lưu trữ tạm thời. Các table và thủ tục nói trên được lưu trữ trong CSDL này phục vụ cho các user.

- Model: Được sử dụng khi template được sử dụng cho các CSDL được tạo trên một hệ thống.

- Msdb: Sử dụng bởi SQL Agent. Tập tin của các CSDL nói trên như sau:

Tập tin CSDL Tên tập tin vật lý Kích thước ngầm định

Cấu trúc vật lý của CSDL.

Như các cấu trúc các CSDL hệ quản trị CSDL thông thường (Dbase, Access), SQL Server cũng quản lý tập tin dữ liệu của CSDL ở dạng vật lý theo trang (page) và phân đoạn (extent).

Page.

SQL Server quản lý một page có kích thước là 8KB, như vậy 1MB có 128 page, trong mỗi trang có 96 byte chứa thơng tin của trang. Có 8 kiểu page như sau:

Data Chứa tất cả các kiểu dữ liệu loại trừ text, ntext và image

</div>Trang 11<div class="page_container" data-page="11">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 11

Text/Image Text, ntext, and image data.

Global Allocation Map, Secondary Global Allocation Map

Chứa các thông tin định vị của các extent.

Page Free Space Chứa thông tin khoảng trống của page.

Index Allocation Map Chứa các thông tin về Extent đã sử dụng cho Index và Page.

Bulk Changed Map Chứa thông thông tin về các lệnh BACKUP LOG. Differential Changed Map Chứa các thông tin lệnh BACKUP DATABASE.

Đối với các tập tin nhật ký (*.ldf), các bản ghi được ghi lại liên tục, không phân trang.

Dữ liệu trong một trang sẽ bắt đầu lưu trữ từ sau phần thông tin Header, và lưu trữ liên tiếp, mỗi hàng có kích thước tối đa là 8060byte. Riêng đối với dữ liệu kiểu text, ntext, image đây là kiểu dữ liệu phức tạp và có kích thước lơn, SQL Server sẽ có chiến lược quản lý khác, phân tran riêng nhằm tăng hiệu quả truy vấn dữ liệu.

Dữ liệu trong SQL Server được lưu trữ trên đĩa và tạo chỉ mục Index theo cấu trúc dữ liệu kiểu B-Tree Plus (có thể tham khảo thêm trong những nội dung cấu trúc dữ liệu nâng cao).

</div>Trang 12<div class="page_container" data-page="12">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 12

Cấu trúc Extent như sau:

File.

Tập tin lưu trữ một CSDL trong SQL Server có 3 loại.

Primary data file: Là file chính lưu trữ dữ liệu (*.mdf = Master Data File), mỗi CSDL có một file primary, lưu trữ điểm bắt đầu của một CSDL và các điểm kết nối đến các file lưu trữ tiếp theo (sencondary).

Secondary data file: Là tập tin lưu trữ dữ liệu sau Primary data file, một CSDL có thể có nhiều tập tin sencondary. Loại tập tin này cho phép một CSDL có thể phân tán dữ liệu ở nhiều nơi trên máy tính hoặc trên mạng.

Log file: Là loại tập tin lưu trữ thông tin nhật ký của CSDL.

Giả sử tạo một CSDL có tên MyDB, thơng thường hệ thống ngầm định các tập tin như sau:

</div>Trang 13<div class="page_container" data-page="13">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 1Trang 13

Các tập tin lưu trữ dữ liệu phân thành từng trang, các trang đánh số id liên tiếp theo từng file:

File group.

SQL Server sử dụng công cụ file group để giúp người dùng dễ dàng quản lý file, các file lưu trữ dữ liệu của một CSDL có thể nhóm thành từng nhóm, gồm 2 kiểu nhóm chính:

- Primary: Là nhóm bắt buộc có, dùng xác định cho file primary (*.mdf) và những file khác.

- User-defined: Nhóm do người dùng tạo ta, tự đặt tên để dễ quản lý.

Chúc Anh/Chị học tập tốt!

TÀI LIỆU THAM KHẢO

1. Professional SQL Server 2000 Database Design, Louis Davidson, Wrox PressLtd

2. SQL Server 2008 Books Online.

</div>Trang 14<div class="page_container" data-page="14">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 1

BÀI 2: GIỚI THIỆU NGÔN NGỮ T - SQL

Mục tiêu:

Trong bài này, Anh/Chị cần đạt được những mục tiêu sau:

1. Nắm được các lệnh T - SQL: biến, kiểu dữ liệu, nắm được các cấu trúc điều khiển if..else, while, select case when,...

2. Sử dụng được các hàm cơ bản và lập trình sử dụng được các thành phần đã giới thiệu trên

Nội dung:

I. KIỂU DỮ LIỆU.

SQL Server gồm những kiểu dữ liệu sau:

Binary: Là kiểu dữ liệu chứa dạng số ở hệ hexa, gồm 3 kiểu dữ liệu Binary,

Varbinary, Image.

Text: Là kiểu ký tự, chứa chữ cái, ký hiệu, số, gồm những kiểu dữ liệu sau:

- Char: Kiểu ký tự, khi xác định độ dài thì độ dài trong CSDL sẽ xác định theo độ dài đặt trước mà không theo độ dài dữ liệu thực có, khơng sử dụng với ký tự dạng Unicode, độ dài tối đa là 8000.

- Nchar: Tương tự như Char nhưng sử dụng với ký tự Unicode, độ dài tối đa 4000.

- Nvarchar: Tương tự như NChar nhưng kích thước trong CSDL sẽ là kích thước thực dữ liệu hiện có, khơng tính theo kích thước đặt trước, kích thước tối đa là 4000.

- Varchar: Tương tự như Nvarchar nhưng không hỗ trợ Unicode.

- Text: Kiểu văn bản, chứa cả ký tự xuống dịng, lưu trữ theo dạng văn bản, có kích thước lớn, có thể lên đến vài Gb, cơ chế quản lý kiểu dữ liệu theo dạng con trỏ và cách thức chèn và cập nhật sẽ khác, kiểu dữ liệu này không hỗ trợ cho Unicode.

- Ntext: Tương tự như Text nhưng có hỗ trợ Unicode.

Data/Time: Kiểu dữ liệu ngày, thời gian, ngày và thời gian, gồm 2 kiểu:

- DateTime: Đầy đủ cả ngày và thời gian.

</div>Trang 15<div class="page_container" data-page="15">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 2

- SmallDataTime: Chỉ ngày hoặc thời gian.

Numeric: Dữ liệu kiểu số, gồm các kiểu dữ liệu sau:

- Int, smallint, tinyint, bigint: Số nguyên - Float, real, decimal, numeric: Số thực.

Monetary: Tiền tệ:

- Money, Smallmoney.

Bit: Kiểu số 0, 1.

Sql_variant: Là kiểu dữ liệu xác định theo kiểu dữ liệu khác, một cột dữ liệu được

định nghĩa dữ liệu kiểu này có thể lưu trữ nhiều dữ liệu có kiểu khác nhau trong cùng một bảng. Ví dụ có thể lưu trữ nhiều kiểu dữ liệu int, binary, char, nhưng không chứa dữ liệu kiểu text, ntext, image, timestamp, sql_variant.

Timestamp: Là kiểu dữ liệu có kích thước 8 byte, lưu trữ dạng số nhị phân do hệ

thống tự sinh ra, mỗi giá trị timestamp trong CSDL là duy nhất.

Table: Là kiểu dữ liệu đặc biệt lưu trữ tập hợp các hàng (dạng bảng), mục đích

sử dụng chính là lưu trữ tạm thời tập hợp các hàng sau truy vấn. Text in row.

Như xem xét trước, dữ liệu kiểu char, varchar có độ dài tối đa là 8000byte, dữ liệu kiểu text, ntext có 2 kiểu lưu trữ: lưu trữ trực tiếp, lưu trữ quản lý theo kiểu con trỏ.

- Đối với lưu trữ theo kiểu trực tiếp, kích thước tối đa đối với text là 8000, đối với ntext là 4000 (kích thước 1 ký tự ở mã Unicode là 2 byte, mã không Unicode là 1 byte).

- Lưu trữ, quản lý theo con trỏ kích thước lên đến GB.

Để lưu trữ dữ liệu theo kiểu con trỏ đầu tiên ta phải đặt chức năng Text in row về trạng thái On, thuộc tính này hiệu ứng cả với kiểu dữ liệu image.

Sử dụng thủ tục sp_tableoption để thay đổi thuộc tính, thuộc tính thay đổi theo bảng dữ liệu.

Giả sử bật chức năng text in row như sau:

Sp_tableoption N'TacGia', 'text in row', 'ON' Tắt chức năng text in row như sau:

Sp_tableoption N'TacGia', 'text in row', 'OFF'

</div>Trang 16<div class="page_container" data-page="16">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 3

Để cập nhật dữ liệu khi thuộc tính được bật, ta phải dùng lệnh READTEXT, UPDATETEXT, WRITETEXT (sẽ bàn kỹ câu lệnh này sau).

Auto number.

Đặt cột dữ liệu kiểu số, tăng tự động khi một hàng được thêm, cột kiểu này không sửa dữ liệu. Dữ liệu kiểu này tương ứng với việc khi thêm hàng dữ liệu chèn thêm giá trị dạng số theo hàm NewID().

II. CÁC HÀM

Hàm SQL là một đặc điểm làm tăng khả năng sử dụng câu lệnh SQL. Hàm SQL có thể nhận nhiều tham số vào và trả về chỉ một giá trị.

Hàm SQL có một số đặc điểm sau:

 Thực hiện việc tính tốn ngay trên dữ liệu

 Có thể thao tác, thay đổi ngay trên từng mục dữ liệu trả về  Hoặc cũng có thể thao tác trên nhóm các dữ liệu trả về

 Có thể định dạng lại các dữ liệu trả về có kiểu số, hay kiểu thời gian  Có thể chuyển đổi kiểu dữ liệu trả về

Hàm SQL có thể phân ra làm hai loại:

 Hàm tác động trên từng dòng dữ liệu: Giá trị trả về tương ứng với từng dữ liệu đầu vào tại mỗi dòng dữ liệu.

 Hàm tác động trên nhóm các dịng dữ liệu: Giá trị trả vê tương ứng với các phép thao tác trên nhóm dữ liệu trả về.

Với mỗi Hệ quản trị cơ sở dữ liệu khác nhau thì hàm mà nó cung cấp cũng khác nhau. Sau đây trình bày một số hàm thông dụng

II.1.1. Hàm thao tác trên từng dòng dữ liệu

a. Hàm thao tác trên dữ liệu kiểu số

ROUND(n,[m]) Cho giá trị của n với m chữ số phần thập phân, mặc định m=0

Round(20.55, 1) = 20.5

SQRT(n) Căn bậc hai của n Sqrt(16)=4 EXP(n) Giá trị của en Exp(2) =7.39

</div>Trang 17<div class="page_container" data-page="17">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 4

ABS(n) Giá trị tuyệt đối của n Abs(2)=2 POWER(n,m) Lũy thừa bậc m của n Power(2,2)=4 MOD(n,m) Cho phần dư của phép chia n cho

Mod(5,2)=1

b. Hàm thao tác trên dữ liệu kiểu ký tự

LEN(s) Cho độ dài chuỗi s Len(‘CNTT’) =4 LTRIM(s) Cắt kí tự trống bên trái

chuỗi s

Ltrim(‘ sss’)= ‘sss’ RTRIM(s) Cắt kí tự trống bên phải

chuỗi s

Rtrim(‘ sss ’)= ‘ sss’ INITCAP(s) Cho chuỗi với kí tự đầu

chuỗi s

Concat(‘s’,’t’) = st

</div>Trang 18<div class="page_container" data-page="18">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 5

c. Hàm thao tác trên dữ liệu kiểu thời gian

ISDATE() Kiểm tra dữ liệu có là kiểu Date/Time

DATEPART(datepart, date) Trả về giá trị ngày, tháng hoặc năm của một biểu thức kiểu Date/Time DATEDIFF ( datepart , startdate ,

enddate )

Xác định độ lệch giữa 2 giá trị startdate và enddate

Trong đó giá trị của datepart có thể nhận

</div>Trang 19<div class="page_container" data-page="19">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 6

d. Hàm chuyển đổi kiểu

o STR (float_expression [ , length [ , decimal ] ] ): Chuyển dữ liệu kiểu số sang chuỗi.

Ví dụ chuyển số sang chuỗi có độ dài 6, làm tròn sau dấu phảy 1 số. STR(123.45, 6, 1)

Kết quả là chuỗi ‘123.5’

o CONVERT(data_type, expression, [style]): Chuyển dữ liệu của biểu thức expression sang kiểu data_type. Style nếu có áp dụng cho định dạng dữ liệu ngày tháng.

Bảng sau là các giá trị Style có thể nhận khi chuyển kiểu Date/Time sang kí tự

Value

(century yy)

Value (century yyyy)

</div>Trang 20<div class="page_container" data-page="20">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 7

11-04-11 11-04-2011

04 Nov 2011 11:45:34:243

II.1.2. Hàm thao tác trên tập hợp

SUM(tên cột) Tổng giá trị của cột AVG(tên cột) Giá trị trung bình của cột MIN(tên cột) Giá trị nhỏ nhất của cột MAX(tên cột) Giá trị lớn nhất của cột COUNT(*) Số dòng của bảng

</div>Trang 21<div class="page_container" data-page="21">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 8

COUNT(tên cột) Số dịng có giá trị khác Null

Chú ý: Tất cả các hàm trên nhóm mẫu tin đều bỏ qua giá trị NULL trừ hàm COUNT.

Ví dụ: Cho bảng

17 Nguyễn Thị Đào 100 Thanh Xuân 63 Lê Đức Hậu 300 Bình Minh 32 Nguyễn Ngọc Nga 200 Thanh Xuân Khi đó áp dụng các hàm trên ta có:

SUM(Luong) = 600 AVG(Luong) = 200 MIN(Luong) = 100 MAX(Luong) = 300 COUNT(Luong) = 3

III. CẤU TRÚC ĐIỀU KHIỂN

III.1. IF…ELSE

Cú pháp:

IF (Biểu_thức)

{ Câu lệnh hoặc nhóm lệnh được thực thi } ELSE

{ Câu lệnh hoặc nhóm lệnh được thực thi }

Lưu ý: Trong SQL nếu ta muốn thực thi 1 nhóm lệnh thì nhóm lệnh đó phải nằm trong từ khóa BEGIN…END

Ví dụ:

DECLARE @CharGender Char(1), @Gender Varchar(20);

SET @CharGender = 'F';

</div>Trang 22<div class="page_container" data-page="22">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 9

IF (@CharGender<>'F') SET @Gender='Male' ELSE

Ví dụ:

DECLARE @CharGender Char(1), @Gender Varchar(20);

SET @CharGender = 'F'; SET @Gender =

CASE @CharGender WHEN 'm' THEN 'Male' WHEN 'M' THEN 'Male' WHEN 'f' THEN 'Female' WHEN 'F' THEN 'Female' END;

SELECT @Gender AS [Giới Tính]

III.3. Khối lệnh : BEGIN…END

Cú pháp: BEGIN

</div>Trang 23<div class="page_container" data-page="23">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 10

{ Câu lệnh hoặc nhóm lệnh được thực thi} END

III.4. Vòng lặp : WHILE

Cú pháp:

WHILE Biểu thức {

Câu lệnh hoặc nhóm lệnh được thực thi }

Ví dụ:

DECLARE @Number As int SET @Number = 1

WHILE @Number < 5 BEGIN

SELECT @Number AS Number SET @Number = @Number + 1 END

 BEARK: Thốt khỏi vịng lặp WHILE hoặc mệnh đề IF… ELSE được lồng bên trong vòng lặp WHILE. Các câu lệnh thực thi sau từ END được thực thi.

 CONTINOUS: Chạy lại vòng lặp WHILE. Các câu lệnh thực thi sau từ khóa CONTIOUS điều được bỏ qua.

</div>Trang 24<div class="page_container" data-page="24">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 11

WAITFOR {

DELAY <„thời gian‟> | TIME <„thời gian‟> }

Ví dụ USE msdb;

EXECUTE sp_add_job @job_name = 'TestJob'; BEGIN

WAITFOR TIME '10:00';

EXECUTE sp_update_job @job_name = 'TestJob', @new_name = 'UpdatedJob';

END; GO

Câu lệnh SELECT... FROM dùng để cho phép bạn có thể chọn lựa các dữ liệu cần thiết từ một

IV. CON TRỎ

Một biến con trỏ được sử dụng để duyệt qua các dòng dữ liệu trong kết quả của một truy vấn và được khai báo theo cú pháp như sau:

</div>Trang 25<div class="page_container" data-page="25">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 12

DECLARE tên_con_trỏ CURSOR FOR câu_lệnh_SELECT

Trong đó câu lệnh SELECT phải có kết quả dưới dạng bảng. Tức là trong câu lệnh không sử dụng mệnh đề COMPUTE và INTO.

Để mở một biến con trỏ ta sử dụng câu lệnh: OPEN tên_con_trỏ

Để sử dụng biến con trỏ duyệt qua các dòng dữ liệu của truy vấn, ta sử dụng câu lệnh FETCH. Giá trịcủa biến trạng thái @@FETCH_STATUS bằng không nếu chưa duyệt hết các dòng trong kết quả truy vấn.

Câu lệnh FETCH có cú pháp như sau:

FETCH [[NEXT|PRIOR|FIST|LAST] FROM] tên_con_trỏ [INTO danh_sách_biến ]

Trong đó các biến trong danh sách biến được sử dụng để chứa các giá trị của các trường ứng với dòng dữ liệu mà con trỏ trỏ đến. Số lượng các biến phải bằng với số lượng các cột của kết quả truy vấn trong câu lệnh DECLARE CURSOR.

Ví dụ: Tập các câu lệnh trong ví dụ dưới đây minh hoạ cách sử dụng biến con trỏ để duyệt qua các dòng trong kết quả của câu lệnh SELECT

DECLARE contro CURSOR

FOR SELECT mahang, tenhang, soluong FROM mathang OPEN contro

DECLARE @mahang NVARCHAR(10) DECLARE @tenhang NVARCHAR(10)

</div>Trang 26<div class="page_container" data-page="26">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 2Trang 13

DECLARE @soluong INT

/*Bắt đầu duyệt qua các dòng trong kết quả truy vấn*/ FETCH NEXT FROM contro

INTO @mahang,@tenhang,@soluong WHILE @@FETCH_STATUS=0 BEGIN

PRINT 'Ma hang:'+@mahang PRINT 'Ten hang:'+@tenhang PRINT 'So luong:'+STR(@soluong) FETCH NEXT FROM contro

INTO @mahang,@tenhang,@soluong END

/*Đóng con trỏ và giải phóng vùng nhớ*/ CLOSE contro

DEALLOCATE contro

Chúc Anh/Chị học tập tốt!

TÀI LIỆU THAM KHẢO

1. Professional SQL Server 2000 Database Design, Louis Davidson, Wrox PressLtd

2. SQL Server 2008 Books Online.

</div>Trang 27<div class="page_container" data-page="27">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 1

BÀI 3: STORED PROCEDURE VÀ TRANSACTION

Mục tiêu:

Trong bài này, Anh/Chị cần đạt được những mục tiêu sau:

1. Nắm được cú pháp và biết viết thủ tục nhằm truy vấn dữ liệu theo yêu cầu. 2. Hiểu về giao tác và biết viết lệnh

T- SQL store procedure tương tự như các ngơn ngữ lập trình khác, chúng chấp nhận các tham số nhập, trả về giá trị xuất thông qua tham số hoặc trả về thông điệp cho biết thủ tục thành công hay thất bại.

Các ứng dụng có thể giao tiếp với SQL Server thơng qua hai cách:

+ Chương trình ứng dụng gửi các phát biểu T-SQL từ client đến server. Các phát biểu này được gửi qua mạng và được SQL Server biên dịch lại mỗi khi thực thi chúng.

+ Tạo store procedure, chúng được lưu và biên dịch thành một kế hoạch ở server. Như vậy với cách này, sử dụng store procedure sẽ giảm được lưu thông mạng, hiệu quả nhanh hơn so với cách gửi các phát biểu T-SQL.

Cú pháp:

CREATE {PROC|PROCEDURE}[schema_name.] procedure_name [ ; number ]

</div>Trang 28<div class="page_container" data-page="28">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 2

  [{@parameter [type_schema_name.] data_type } [VARYING][= default ][[ OUT|OUTPUT ]  [,...n ]

[ WITH <procedure_option> [ ,...n ] AS { [ BEGIN ] statements [ END ] } [;]

<procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] Trong đó:

+ procedure_name: là tên của store procedure sẽ được tạo.

+ parameter: Là các tham số truyền vào store procedure, ta phải định

nghĩa chúng trong phần khai báo của store procedure. Khai báo báo gồm tên của tham số (trước tên tham số sử dụng tiền tố @), kiểu dữ liệu của tham số và một số chỉ định đặc biệt phụ thuộc vào mục đích sử dụng của tham sớ đó. + ; number: Là số nguyên tùy chọn được sử dụng trong nhóm các thủ

tục có cùng tên.

+ data type: Kiểu của tham số trong phần khai báo.

+ [VARYING]: Đây là tùy chọn được chỉ định khi cursor trả về  như

một tham số.

+ [= default] : Gán giá trị mặc định cho tham số. Nếu không gán giá

trị mặc định thì tham sớ nhận giá trị NULL.

+ OUTPUT: Đây là từ khóa chỉ định tham sớ đó là tham số xuất. Tham số xuất không dùng được với kiểu dữ liệu Text và image.

+ [,...n]: Chỉ định rằng có thể khai báo nhiều tham số.

+ RECOMPILE:Chỉ định Database Engine không xây dựng kế hoạch cho thủ tục này và thủ tục sẽ được biên dịch tại thời điểm thực thi thủ tục. + ENCRYPTION:Chỉ định SQL Server sẽ mã hóa bản text lệnh

CREATE PROCEDURE. Users không thể truy cập vào các bảng hệ thống hoặc file dữ liệu để truy xuất bản text đã mã hóa.

</div>Trang 29<div class="page_container" data-page="29">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 3

* Thực thi stored procedure trong SQL Server: Để thực thi một thủ tục trong SQL

Server ta sử dụng cú pháp sau: { EXEC | EXECUTE }

{ module_name [ ;number ]} [ [ @parameter = ] { value

| @variable [ OUTPUT ] | [ DEFAULT ] }

] [ ,...n ]

[ WITH RECOMPILE ]

Trong đó: + module_name: Là tên thủ tục cần thực hiện. + ;number: Chỉ định thủ tục trong nhóm thủ tục cùng tên. + @parameter: Tên tham số trong thủ tục. + @variable: Chỉ định biến chứa các tham số hoặc trả về tham số. + DEFAULT: Chỉ định lấy giá trị mặc định của biến.

Ví dụ 4.30. Xây dựng thủ tục XemDSSV.

Use QLDiemSV  GO

CREATE PROCEDURE p_DSSV AS

SELECT MaSV, TenSV, Ngaysinh, MaLop From SinhVien

- Thực thi thủ tục p_DSSV Use QLDiemSV Go

EXEC p_DSSV

* Truyền tham sớ nhập vào trong store procedure.

Ví dụ 4.32. Xây dựng thủ tục pp_DSSV để hiển thị danh sách sinh viên theo tham

số mã lớp. Mã lớp được truyền vào khi thủ tục được thực hiện. Use QLDiemSV

CREATE PROCEDURE p_DSSV

</div>Trang 30<div class="page_container" data-page="30">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 4

@parMaLop Varchar(10)='TH%'

SELECT MaSV, TenSV, Ngaysinh

From Sinhvien Where MaLop like @parMaLop GO

Gọi thực thi thủ tục trên với truyền giá trị cho tham số nhập như sau: EXEC p_DSSV ‘TH03A’

EXEC p_DSSV @parMaLop=DEFAULT

* Sử dụng tham sớ x́t trong store procedure.

Ví dụ 4.33. Xây dựng thủ tục pp_Siso để xuất giá trị sĩ số của một lớp theo tham số

mã lớp. Mã lớp được truyền vào khi thủ tục được thực hiện. Use QLDiemSV 

CREATE PROCEDURE pp_Siso

@parMaLop Char(10), @parSiso Int OUTPUT AS

SELECT @parSiso=count(*)

From SINHVIEN Where MaLop=@parMaLop GO

DECLARE @siso int

exec pp_Siso 'TH03A',@parSiso=@siso OUTPUT Print 'Si so lop TH03A là :'+ convert(varchar(3),@siso) Go

Kết quả thực hiện chương trình: Si so lop TH03A là :12

* Sử dụng biến cục bộ: Các biến cục bộ được sử dụng trong bó lệnh, trong chương

trình gọi (Scipt) hoặc trong thủ tục Biến cục bộ thường được giữ các giá trị sẽ được kiểm tra trong phát biểu điều kiện và giữ giá trị sẽ được trả về bởi lệnh RETURN. Phạm vị của biến cục bộ trong store procedure là từ điểm biến đó được khai báo cho đến khi thốt store procedure. Ngay khi store procedure kết thúc thì biến đó không được tham chiếu nữa. Cú pháp khai báo biến cục bộ:

DECLARE <parameter> [AS] <data type>

Giống như khai báo các biến ở trên, trước tên biến phải có tiền tố @. Giá trị khởi tạo

</div>Trang 31<div class="page_container" data-page="31">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 5

ban đầu của biến là NULL. Để thiết lập giá trị của biến ta sử dụng cú pháp:

SET <parameter> = <expression> SELECT <parameter> = <expression>

* Câu lệnh PRINT: Dùng để hiển thị chuỗi thông báo tới người sử dụng. Ch̃i

thơng báo này nó thể dài tới 8000 ký tự. Cú pháp của lệnh PRINT như sau: PRINT < messages>

* Sử dụng SELECT đề trả về giá trị: Ta có thể trả về giá trị bằng việc sử dụng

SELECT trong thủ tục hoặc trả về kết quả thiết lập từ truy vấn SELECT.

Ví dụ 4.34. Xây dựng thủ tục pp_Siso để xuất giá trị sĩ số của một lớp theo tham sớ

mã lớp ra ngồi. Mã lớp được truyền vào khi thủ tục được thực hiện. Use QLDiemSV

CREATE PROCEDURE pp_Siso

@parMaLop Char(10), @parSiso Int OUTPUT AS

SELECT @parSiso=count(*) From SINHVIEN Where MaLop=@parMaLop GO

DECLARE @siso int

exec pp_Siso 'TH03A',@parSiso=@siso OUTPUT SELECT 'Si so lop TH03A là :'= @siso

* Lệnh RETURN: Ta có thể sử dụng lệnh RETURN để thốt khơng điều kiện khỏi

thủ tục. Khi lệnh RETURN được thực thi trong thủ tục, khi đó các câu lệnh sau RETURN trong thủ tục sẽ bị bỏ qua và thoát khỏi thủ tục để trở về dịng lệnh tiếp theo trong chương trình gọi.

Ngồi ra, ta có thể sử dụng lệnh RETURN để trả về giá trị cho chương trình gọi, giá trị trả về phải là một sớ ngun, nó có thể là một hằng số hoặc một biến. Cú pháp như sau:

RETURN [ integer_expression ]

Ví dụ 4.35. Cho CSDL pubs. Xây dựng thủ tục usp_4_31 kiểm tra một chủ đề có tờn

tại trong bảng titles hay không? Nếu tồn tại một chủ đề thì hiển thị chủ đề đó. Nếu khơng tờn tại chủ đề đó thì thủ tục trả về giá trị 1 hoặc có nhiều hơn một chủ đề đó thì trả về giá trị 2.

</div>Trang 32<div class="page_container" data-page="32">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 6

Use pubs GO

CREATE PROCEDURE usp_4_31

@vchTitlePattern VARCHAR(80) = '%'

SELECT @vchTitlePattern = '%' + @vchTitlePattern + '%'

IF (SELECT COUNT(*) FROM titles

WHERE title LIKE @vchTitlePattern) < 1 BEGIN

RETURN 1 END

IF (SELECT COUNT(*) FROM titles WHERE title LIKE @vchTitlePattern) > 1 BEGIN

RETURN 2 END

SELECT title, price FROM titles

WHERE title LIKE @vchTitlePattern RETURN 0

DECLARE @intReturnValue INT

EXEC @intReturnValue = usp_4_31 'Tin hoc'

IF (@intReturnValue = 1) BEGIN

PRINT 'There are no corresponding titles.'

END

IF (@intReturnValue = 2) BEGIN

PRINT 'There are multiple titles that match this criteria. Please narrow your search.'

ENDGO

3. Thay đởi, xóa stored procedure

a) Thay đổi store procedure Cú pháp:

</div>Trang 33<div class="page_container" data-page="33">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 7

ALTER {PROC|PROCEDURE}[schema_name.] procedure_name [ ; number ]

[{@parameter [type_schema_name.] data_type } [VARYING][= default ][[ OUT|OUTPUT ] [,...n ]

[ WITH <procedure_option> [ ,...n ] AS { [ BEGIN ] statements [ END ] } [;]

<procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ]

Ví dụ 4.36. Ta sửa lại thủ tục p_DSSV trong ví dụ 4.32 như sau:

Use QLDiemSV Go

ALTER PROCEDURE p_DSSV @parMaLop Char(10)

DROP PROCEDURE p_DSSV Go

II. TRANSACTION

1. Giao tác và các tính chất của giao tác

Một giao tác (transaction) là một chuỗi một hoặc nhiều câu lệnh SQL được kết hợp lại với nhau thành một khối công việc. Các câu lệnh SQL xuất hiện trong giao tác thường có mới quan hệ tương đối mật thiết với nhau và thực hiện các thao tác độc

</div>Trang 34<div class="page_container" data-page="34">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 8

lập. Việc kết hợp các câu lệnh lại với nhau trong một giao tác nhằm đảm bảo tính tồn vẹn dữ liệu và khả năng phục hời dữ liệu. Trong một giao tác, các câu lệnh có thể độc lập với nhau nhưng tất cả các câu lệnh trong một giao tác đòi hỏi hoặc phải thực thi trọn vẹn hoặc không một câu lệnh nào được thực thi.

Các cơ sở dữ liệu sử dụng nhật ký giao tác (transaction log) để ghi lại các thay đổi

mà giao tác tạo ra trên cơ sở dữ liệu và thơng qua đó có thể phục hời dữ liệu trong trường hợp gặp lỡi hay hệ thớng có sự cớ.

Một giao tác địi hỏi phải có được bờn tính chất sau đây:

Tính ngun tử (Atomicity): Mọi thay đổi về mặt dữ liệu hoặc phải được thực hiện

trọn vẹn khi giao tác thực hiện thành công hoặc khơng có bất kỳ sự thay đởi nào về dữ liệu xảy ra nếu giao tác không thực hiện được trọn vẹn. Nói cách khác, tác dụng của các câu lệnh trong một giao tác phải như là một câu lệnh đơn.

Tính nhất quán (Consistency): Tính nhất quan đòi hỏi sau khi giao tác kết thúc,

cho dù là thành công hay bị lỗi, tất cả dữ liệu phải ở trạng thái nhất quán (tức là sự toàn vẹn dữ liệu phải ln được bảo tồn).

Tính độc lập (Isolation): Tính độc lập của giao tác có nghĩa là tác dụng của mỗi

một giao tác phải giống như khi chỉ mình nó được thực hiện trên chính hệ thớng đó. Nói cách khác, một giao tác khi được thực thi đồng thời với những giao tác khác trên cùng hệ thống không chịu bất kỳ sự ảnh hưởng nào của các giao tác đó.

Tính bền vững (Durability): Sau khi một giao tác đã thực hiện thành cơng, mọi tác

dụng mà nó đã tạo ra phải tồn tại bền vững trong cơ sở dữ liệu, cho dù là hệ thớng có bị lỡi đi chăng nữa.

</div>Trang 35<div class="page_container" data-page="35">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 9

Giao tác SQL được định nghĩa dựa trên các câu lệnh xử lý giao tác sau đây: BEGIN TRANSACTION: Bắt đầu một giao tác

SAVE TRANSACTION: Đánh dấu một vị trí trong giao tác (gọi là điểm đánh dấu). ROLLBACK TRANSACTION: Quay lui trở lại đầu giao tác hoặc một điểm đánh dấu trước đó trong giao tác.

COMMIT TRANSACTION: Đánh dấu điểm kết thúc một giao tác. Khi câu lệnh này thực thi cũng có nghĩa là giao tác đã thực hiện thành công.

ROLLBACK [WORK]: Quay lui trở lại đầu giao tác. COMMIT [WORK]: Đánh dấu kết thúc giao tác. 

Một giao tác trong SQL được bắt đấu bởi câu lệnh BEGIN TRANSACTION. Câu lệnh này đánh dấu điểm bắt đầu của một giao tác và có cú pháp như sau:

BEGIN TRANSACTION [tên_giao_tác]

</div>Trang 36<div class="page_container" data-page="36">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 10

Một giao tác sẽ kết thúc trong các trường hợp sau:

 Câu lệnh COMMIT TRANSACTION (hoặc COMMIT WORK) được thực thi. Câu lệnh này báo hiệu sự kết thúc thành công của một giao tác. Sau câu lệnh này, một giao tác mới sẽ được bắt đầu.

 Khi câu lệnh ROLLBACK TRANSACTION (hoặc ROLLBACK WORK) được thực thi để huỷ bỏ một giao tác và đưa cơ sở dữ liệu về trạng thái như trước khi giao tác bắt đầu. Một giao tác mới sẽ bắt đầu sau khi câu lệnh ROLLBACK được thực thi.

 Một giao tác cũng sẽ kết thúc nếu trong quá trình thực hiện gặp lỗi (chẳng hạn hệ thống gặp lỗi, kết nối mạng bị “đứt”,...). Trong trường hợp này, hệ thống sẽ tự động phục hồi lại trạng thái cơ sở dữ liệu như trước khi giao tác bắt đầu (tương tự như khi câu lệnh ROLLBACK được thực thi để huỷ bỏ một giao tác). Tuy nhiên, trong trường hợp này sẽ khơng có giao tác mới được bắt đầu.

Ví dụ 6.1: Giao tác dưới đây kết thúc do lệnh ROLLBACK TRANSACTION và

mọi thay đổi vể mặt dữ liệu mà giao tác đã thực hiện (UPDATE) đều khơng có tác dụng.

BEGIN TRANSACTION giaotac1

UPDATE monhoc SET sodvht=4 WHERE sodvht=3 UPDATE lop SET tenlop = '07B5' WHERE malop = 2 ROLLBACK TRANSACTION giaotac1

còn giao tác dưới đây kết thúc bởi lệnh COMMIT và thực hiện thành công việc cập nhật dữ liệu trên các bảng MONHOC và DIEMTHI.

BEGIN TRANSACTION giaotac2

UPDATE monhoc SET sodvht=4 WHERE sodvht=3 UPDATE lop SET tenlop = '07B5' WHERE malop = 2 COMMIT TRANSACTION giaotac2

Câu lệnh:

SAVE TRANSACTION tên_điểm_dánh_dấu

được sử dụng để đánh dấu một vị trí trong giao tác. Khi câu lệnh này được thực thi, trạng thái của cơ sở dữ liệu tại thời điểm đó sẽ được ghi lại trong nhật ký giao tác. Trong quá trình thực thi giao tác có thể quay trở lại một điểm đánh dấu bằng cách sử dụng câu lệnh:

ROLLBACK TRANSACTION tên_điểm_đánh_dấu

</div>Trang 37<div class="page_container" data-page="37">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 11

Trong trường hợp này, những thay đổi về mặt dữ liệu mà giao tác đã thực hiện từ điểm đánh dấu đến trước khi câu lệnh ROLLBACK được triệu gọi sẽ bị huỷ bỏ. Giao tác sẽ được tiếp tục với trạng thái cơ sở dữ liệu có được tại điểm đánh dấu. Hình 6.2 mơ tả cho ta thấy hoạt động của một giao tác có sử dụng các điểm đánh dấu:

Sau khi câu lệnh ROLLBACK TRANSACTION được sử dụng để quay lui lại một điểm đánh dấu trong giao tác, giao tác vẫn được tiếp tục với các câu lệnh sau đó.

</div>Trang 38<div class="page_container" data-page="38">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 12

Nhưng nếu câu lệnh này được sử dụng để quay lui lại đầu giao tác (tức là huỷ bỏ giao tác), giao tác sẽ kết thúc và do đó câu lệnh COMMIT TRANSACTION trong trường hợp này sẽ gặp lỡi.

Ví dụ 6.2: Câu lệnh COMMIT TRANSACTION trong giao tác dưới đây kết thúc

thành công một giao tác

BEGIN TRANSACTION giaotac3

UPDATE diemthi SET diemlan2=0 WHERE diemlan2 IS NULL SAVE TRANSACTION a

UPDATE monhoc SET sodvht=4 WHERE sodvht=3 ROLLBACK TRANSACTION a

UPDATE monhoc SET sodvht=2 WHERE sodvht=3 COMMIT TRANSACTION giaotac3

và trong ví dụ dưới đây, câu lệnh COMMIT TRANSACTION gặp lỗi: BEGIN TRANSACTION giaotac4

UPDATE diemthi SET diemlan2=0 WHERE diemlan2 IS NULL SAVE TRANSACTION a

UPDATE monhoc SET sodvht=4 WHERE sodvht=3 ROLLBACK TRANSACTION giaotac4

UPDATE monhoc SET sodvht=2 WHERE sodvht=3 COMMIT TRANSACTION giaotac4

3. Giao tác lồng nhau

Các giao tác trong SQL có thể được lờng vào nhau theo từng cấp. Điều này thường gặp đối với các giao tác trong các thủ tục lưu trữ được gọi hoặc từ một tiến trình trong một giao tác khác.

Ví dụ dưới đây minh hoạ cho ta trường hợp các giao tác lờng nhau.

Ví dụ 6.3: Ta định nghĩa bảng T như sau: CREATE TABLE T

</div>Trang 39<div class="page_container" data-page="39">

Hệ quản trị cơ sở dữ liệu - SQL Server - Bài 3Trang 13

IF NOT EXISTS (SELECT * FROM T WHERE A=@A ) INSERT INTO T VALUES(@A,@B)

IF NOT EXISTS (SELECT * FROM T WHERE A=@A+1) INSERT INTO T VALUES(@A+1,@B+1)

COMMIT TRANSACTION T1 END

Lời gọi đến thủ tục sp_TransEx được thực hiện trong một giao tác khác như sau: BEGIN TRANSACTION T3

EXECUTE sp_tranex 10,20

ROLLBACK TRANSACTION T3

Trong giao tác trên, câu lệnh ROLLBACK TRANSACTION T3 huỷ bỏ giao tác và do đó tác dụng của lời gọi thủ tục trong giao tác khơng cịn tác dụng, tức là khơng có dịng dữ liệu nào mới được bở sung vào bảng T (cho dù giao tác T1 trong thủ tục

sp_tranex đã thực hiện thành công với lệnh COMMIT TRANSACTION T1).

Ta xét tiếp một trường hợp của một giao tác khác trong đó có lời gọi đến thủ tục

sp_tranex như sau:

BEGIN TRANSACTION EXECUTE sp_tranex 20,40 SAVE TRANSACTION a EXECUTE sp_tranex 30,60 ROLLBACK TRANSACTION a EXECUTE sp_tranex 40,80 COMMIT TRANSACTION

sau khi giao tác trên thực hiện xong, dữ liệu trong bảng T sẽ là: AB 20 40 21 41 40 80 41 81

Như vậy, tác dụng của lời gọi thủ tục sp_tranex 30,60 trong giao tác đã bị huỷ bỏ bởi câu lệnh ROLLBACK TRANSACTION trong giao tác.

Như đã thấy trong ví dụ trên, khi các giao tác SQL được lờng vào nhau, giao tác ngồi cùng nhất là giao tác có vai trị quyết định. Nếu giao tác ngồi cùng nhất được uỷ thác (commit) thì các giao tác được lồng bên trong cũng đồng thời uỷ thác; Và nếu giao tác ngoài cùng nhất thực hiện lệnh ROLLBACK thì những giao tác lờng bên trong cũng chịu tác động của câu lệnh này (cho dù những giao tác lồng bên trong đã thực hiện lệnh COMMIT TRANSACTION).

</div>