Trong bài viết này Stanford – Dạy kinh nghiệm lập trình sẽ hướng dẫn các bạn học cơ sở dữ liệu SQL cách thức tạo ra bảng tạm để mục đích lưu trữ tạm, xử lý dữ liệu được dễ dàng khi cần.
Nội dung chính
- 1. Sự khác biệt giữa 2 loại bảng tạm trong SQL Server
- 2. Sử dụng để tạo ra bảng tạm trong SQL Server
- Video liên quan
Tóm Tắt
1. Sự khác biệt giữa 2 loại bảng tạm trong SQL Server
Đối với SQL Server các bạn có 2 dạng bảng tạm đó là:
-
Local temporary table: Sử dụng để tạo ra bảng tạm và tồn tại trong kết nối của người dùng tạo ra bảng đó và sẽ bị huỷ khi ngắt kết nối.
-
Global temporary table: Sử dụng để tạo ra bảng tạm và tồn tại đến khi nào tất cả các kết nối đến cơ sở dữ liệu làm việc đóng hết. Có thể sử dụng ở kết nối của người dùng khác.
2. Sử dụng để tạo ra bảng tạm trong SQL Server
Để thực hiện tạo bảng tạm dạng local, các bạn học cơ sở dữ liệu SQL cần thực hiện dòng lệnh sau và chú ý luôn cần có dấu # ở trước tên bảng:
Tạo bảng dữ liệu tạm dạng global trong SQL Server như sau:
Sau khi tạo sau các bảng tạm để phục vụ cho việc lưu trữ tạm thời. Chúng ta có thể thực hiện các lệnh truy vấn, thực thi với bảng tạm giống như bảng thật bình thường trong SQL Server.
Hy vọng qua bài hướng dẫn này các bạn đã biết cách tạo bảng tạm trong SQL Server để phục vụ công việc của mình. Bên cạnh đó các bạn muốn được đào tạo bài bản về cơ sở dữ liệu SQL cùng chuyên gia giàu kinh nghiệm Stanford có thể tham khảo khoá học SQL tại đây: Khoá SQL Server
=============================
☎ STANFORD – ĐÀO TẠO VÀ PHÁT TRIỂN CÔNG NGHỆ
Hotline: 0963 723 236 – 0866 586 366
Website: https://stanford.com.vn
Facebook: http://bit.ly/2FN0TYb
Youtube: http://bit.ly/2TkKT7I
Có nhiều RDBMS hỗ trợ Bảng tạm (Temporary Table). Bảng tạm (Temporary Table) là một tính năng tuyệt vời giúp bạn lưu giữ và xử lý các kết quả trung gian bằng việc sử dụng các khả năng như chọn, cập nhật, kết hợp giống như bạn có thể sử dụng với các bảng điển hình trong SQL Server.
Bảng tạm (Temporary Table) có thể rất hữu ích trong một số trường hợp khi bạn muốn lưu giữ dữ liệu tạm thời. Điều quan trọng nhất bạn cần nên biết về Bảng tạm (Temporary Table) là chúng có thể bị xóa khi Client Session hiện tại kết thúc.
Bảng tạm (Temporary Table) là có sẵn từ MySQL phiên bản 3.23 trở đi. Nếu bạn sử dụng một phiên bản cũ hơn, bạn không thể sử dụng tính năng này, nhưng bạn có thể sử dụng các Heap Table.
Như đã đề cập, bảng tạm sẽ chỉ tồn tại khi session là còn tồn tại. Nếu bạn chạy code trong PHP script, bảng tạm sẽ bị hủy tự động khi script kết thúc việc thực thi. Nếu bạn được kết nối với MySQL Database thông qua chương trình MySQL Client, thì bảng tạm sẽ tồn tại tới khi bạn đóng Client hoặc hủy bảng.
Ví dụ
Ví dụ sau minh họa cách sử dụng của bảng tạm.
mysql> CREATE TEMPORARY TABLE DOANHTHU (
-> ten_san_pham VARCHAR(50) NOT NULL
-> , tong_doanh_thu DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , gia_trung_binh DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , tong_so_luong INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO DOANHTHU
-> (ten_san_pham, tong_doanh_thu, gia_trung_binh, tong_so_luong)
-> VALUES
-> (‘iphone7’, 30, 15, 2);
mysql> SELECT * FROM DOANHTHU;
+————–+————-+—————-+——————+
| ten_san_pham | tong_doanh_thu | gia_trung_binh | tong_so_luong |
+————–+————-+—————-+——————+
| iphone7 | 30 | 15 | 2 |
+————–+————-+—————-+——————+
1 row in set (0.00 sec)
Khi bạn thông báo một lệnh SHOW TABLES, thì bảng tạm của bạn sẽ không được liệt kê trong danh sách. Bây giờ, nếu bạn đăng xuất khỏi MySQL session và sau đó bạn thông báo một lệnh SELECT, thì bạn sẽ không thấy dữ liệu nào có sẵn trong Database. Ngay khi đó, bảng tạm của bạn cũng sẽ không tồn tại.
Quảng cáo
Theo mặc định, tất cả bảng tạm bị xóa bởi MySQL khi kết nối tới Database của bạn kết thúc. Nếu bạn vấn muốn xóa chúng, bạn thông báo một lệnh DROP TABLE.
Ví dụ sau minh họa cách xóa một bảng tạm trong SQL.
mysql> CREATE TEMPORARY TABLE DOANHTHU (
-> ten_san_pham VARCHAR(50) NOT NULL
-> , tong_doanh_thu DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , gia_trung_binh DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , tong_so_luong INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO DOANHTHU
-> (ten_san_pham, tong_doanh_thu, gia_trung_binh, tong_so_luong)
-> VALUES
-> (‘iphone7’, 30, 90, 2);
mysql> SELECT * FROM DOANHTHU;
+————–+————-+—————-+——————+
| ten_san_pham | tong_doanh_thu | gia_trung_binh | tong_so_luong |
+————–+————-+—————-+——————+
| iphone7 | 30 | 15 | 2 |
+————–+————-+—————-+——————+
1 row in set (0.00 sec)
mysql> DROP TABLE DOANHTHU;
mysql> SELECT * FROM DOANHTHU;
ERROR 1146: Table ‘VIETJACK.DOANHTHU’ doesn’t exist
Bảng tạm (temporary table) và biến kiểu bảng (table variable) là 2 phương tiện để lưu dữ liệu tạm thời khi đang xử lý. Bảng tạm có tên bắt đầu bằng dấu “#”, bạn có thể tạo bảng tạm bằng lệnh CREATE TABLE và khai báo các cột của nó; hoặc bạn có thể vừa tạo và thêm dữ liệu vào bằng SELECT INTO. Với biến kiểu bảng thì bạn phải khai báo trước mới có thể dùng được, và khi khai báo bạn phải định nghĩa các cột của nó:
–tạo bảng trực tiếp
CREATE TABLE #t1 (C VARCHAR(50))
–vừa tạo bảng và thêm dữ liệu vào
SELECT C
INTO #t2
FROM dbo.SomeTable
–khai báo biến kiểu bảng
DECLARE @t TABLE(C VARCHAR(50))
INSERT INTO @t SELECT 1
SELECT * FROM @t
Bảng tạm khai báo ở trên gọi là bảng tạm cục bộ (local temp table), SQL Server còn cung cấp bảng tạm toàn cục (global temp table) với tên bắt đầu bằng hai dấu “##”. Trong bài viết này khi nhắc đến bảng tạm là tôi chỉ nói về bảng tạm cục bộ, vì bạn sẽ chủ yếu dùng đến loại này.
Cả bảng tạm và biến kiểu bảng chỉ tồn tại trong phạm vi (scope) mà chúng được tạo. Khi bạn tạo chúng trong thủ tục, chúng sẽ tự động bị xóa sau khi thủ tục thực hiện xong. Nếu bạn tạo chúng trong đoạn SQL động bên trong thủ tục, chúng cũng được xóa sau khi đoạn SQL động thực hiện xong.
CREATE PROC dbo.p1
AS
BEGIN
CREATE TABLE #t1(C VARCHAR(10))
INSERT INTO #t1 SELECT ‘abc’
END
EXEC dbo.p1
SELECT * FROM #t1 –lỗi, #t1 không tồn tại nữa
CREATE PROC dbo.p2
AS
BEGIN
DECLARE @SQL VARCHAR(1000)
SELECT @SQL = ‘DECLARE @t TABLE(C VARCHAR(10))
INSERT INTO @t SELECT 1
‘
EXEC(@SQL)
SELECT * FROM @t –lỗi, biến @t không tồn tại nữa
END
Cả bảng tạm và biến kiểu bảng đều được lưu trong tempdb, chúng được lưu trong bộ nhớ khi có đủ chỗ và bị đẩy ra đĩa cứng khi cần thiết. Vì thế việc truy xuất biến kiểu bảng không có gì nhanh hơn so với bảng tạm.
Vì cả bảng tạm và biến kiểu bảng chỉ tồn tại trong phạm vi chúng được khai báo, nên tên của chúng cũng chỉ có ý nghĩa trong phạm vi đó. Nếu bạn mở hai cửa sổ trong Management Studio và ở cả hai cửa sổ bạn tạo bảng tạm có tên #t, SQL Server sẽ không phàn nàn gì vì chúng thuộc hai phạm vi khác nhau (vì thế bảng tạm kiểu này gọi là bảng tạm cục bộ). Tương tự cùng một thủ tục được gọi đồng thời nhiều lần từ ứng dụng, dù thủ tục tạo cùng một tên bảng tạm nhưng mỗi lần gọi đều thuộc một phạm vi riêng nên không có xung đột gì về tên bảng.
Về thực chất, SQL Server khi tạo bảng tạm trong tempdb sẽ thêm một chuỗi định danh vào tên bảng để đảm bảo nó duy nhất và chỉ phạm vi tạo bảng đó có thể truy xuất được bảng này. Bạn có thể xem các bảng tạm hiện có trong hệ thống bằng lệnh sau:
SELECT * FROM tempdb.INFORMATION_SCHEMA.tables
ORDER BY TABLE_NAME
Ngoài các bảng tạm hệ thống, bạn sẽ thấy các bảng tạm do người dùng tạo với tên kiểu như “#t______A145G”.
Bạn có thể truy vấn bảng tạm và biến kiểu bảng giống như đối với bảng thông thường, bạn có thể JOIN với chúng, dùng subquery… Bạn cũng có thể INSERT, UPDATE, hoặc DELETE các bản ghi trong bảng tạm và biến kiểu bảng.
Đi vào chi tiết hơn, bảng tạm và biến kiểu bảng có những đặc điểm khác biệt, và những đặc điểm này giúp bạn quyết định dùng cái nào thích hợp hơn.
• Bạn có thể xóa (DROP TABLE) bảng tạm và tạo lại ngay trong cùng một thủ tục, nhưng bạn không thể làm như vậy với biến kiểu bảng, vì nó là một biến nó sẽ tồn tại đến khi thủ tục thực hiện xong.
• Bạn có thể tạo index cho các cột của bảng tạm và tạo khóa primary key, unique key cho nó, nhưng bạn không thể làm vậy với biến kiểu bảng. Đây là ưu thế vượt trội của bảng tạm khi cần xử lý lượng dữ liệu lớn và chạy các câu lệnh phức tạp. Chỉ có khóa ngoại là không được tạo trên bảng tạm (ở bản SQL 2008).
• Bạn có thể TRUNCATE bảng tạm nhưng chỉ có thể DELETE biến kiểu bảng. Khi cần xóa một lượng dữ liệu lớn, bảng tạm sẽ có ưu thế hơn.
• Khi bạn có thủ tục cha gọi một thủ tục con, bảng tạm tạo trong thủ tục cha sẽ được nhìn thấy trong thủ tục con. Thủ tục con có thể truy vấn và cập nhật dữ liệu trong bảng này, và khi quay về thủ tục cha nó vẫn nhìn thấy dữ liệu đã cập nhật. Đây là một cách hữu hiệu để trao đổi dữ liệu giữa các thủ tục. Ví dụ, tôi tạo hai thủ tục p1 và p2, và trong p1 tôi có lời gọi đến p2:
CREATE PROC dbo.p1
AS
BEGIN
IF OBJECT_ID(‘tempdb..#t1’) IS NOT NULL DROP TABLE #t1
CREATE TABLE #t1(i INT)
INSERT INTO #t1 SELECT 1
SELECT * FROM #t1 — chỉ có 1 bản ghi insert bởi p1
EXEC p2
SELECT * FROM #t1 –có thêm bản ghi insert bởi p2
END
go
ALTER PROC dbo.p2
AS
BEGIN
INSERT INTO #t1 SELECT 2
END
EXEC dbo.p1 — nhận được 2 bản ghi
Kết quả của đoạn lệnh trên là 2 bản ghi insert bởi p1 và p2. Đối với biến kiểu bảng thì không làm được như vậy. Nếu không thấy biến được khai báo, thủ tục sẽ báo lỗi ngay từ khi biên dịch.
• Bảng tạm luôn kèm theo statistics là các thông tin trợ giúp việc tối ưu hóa thực hiện. Đây là một overhead khi dùng bảng tạm. Vì thế khi phương án thực thi tối ưu không phụ thuộc nhiều vào lượng dữ liệu tạm, nói cách khác statistics của bảng tạm không có tác dụng gì, bạn nên dùng biến kiểu bảng.
• Khi viết hàm, vì hàm không cho phép dùng bảng tạm nên bạn chỉ có một lựa chọn là biến kiểu bảng.
Nguồn: http://www.sqlviet.com/blog/bang-tam-va-bien-kieu-bang
