13.6.6 Cursors
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
Asensitive: The server may or may not make a copy of its result table
Read only: Not updatable
Nonscrollable: Can be traversed only in one direction and cannot skip rows
Cursor declarations must appear before handler declarations and after variable and condition declarations.
Example:
CREATE PROCEDURE curdemo[]
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR[16];
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES [a,b];
ELSE
INSERT INTO test.t3 VALUES [a,c];
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
Khai báo và sử dụng Cursor
Trong các truy vấn T-SQL, như tại các Stored Procedure, ta có thể sử dụng các con trỏ
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product2 để duyệt qua dữ liệu. Ta hiểu
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product2 là một tập hợp kết quả truy vấn [các hàng], với
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product2 ta có thể duyệt qua từng hàng kết quả để thi hành những tác vụ phức tạp.
Ở một thời điểm,
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product2 có thể truy cập bởi một con trỏ đến một hàng của nó, bạn chỉ thể dịch chuyển con trỏ từ dòng này sang dòng khác.
Để sử dụng con trỏ trong các
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product6, cần thực hiện theo các bước:
Bước 1: khai báo con trỏ, trỏ đến một tập dữ liệu [kết quả của DECLARE cursorProduct CURSOR FOR
SELECT id, title FROM Product
7] bằng lệnh DECLARE cursorProduct CURSOR FOR
SELECT id, title FROM Product
8 khai báo con trỏ, trỏ đến một tập dữ liệu [kết quả của
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product7] bằng lệnh
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product8
Ví dụ có một tập dữ liệu từ câu lệnh Select như sau:
SELECT id,name FROM Product
Ta khai báo một con trỏ đặt tên là
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product9 thì sẽ viết như sau:
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product
Bước 2: Khi bắt đầu quá trình đọc các dòng dữ liệu từ Cursor trên, thì phải mở con trỏ, thực hiện như sau: Khi bắt đầu quá trình đọc các dòng dữ liệu từ Cursor trên, thì phải mở con trỏ, thực hiện như sau:
Open cursorProduct
Khi Cursor được mở, con trỏ sẽ trỏ tới dòng đầu tiên của tập dữ liệu, lúc này có thể đọc nội dung dòng đó bằng lệnh
Open cursorProduct0Cursor được mở, con trỏ sẽ trỏ tới dòng đầu tiên của tập dữ liệu, lúc này có thể đọc nội dung dòng đó bằng lệnh
Open cursorProduct0
Bước 3: Đọc dữ liệu sử dụng lệnh như sau: Đọc dữ liệu sử dụng lệnh như sau:
FETCH NEXT FROM cursorProduct INTO @id, @title
Lệnh trên sẽ đọc nội dung dòng hiện tại, lưu vào biến @id và @title [vì dữ liệu trong Cursor này có 2 cột]. Nếu đọc thành công thì dịch chuyển con trỏ tới dòng tiếp theo
Để kiệm tra việc
Open cursorProduct1 thành công thì kiểm tra điều kiện
Open cursorProduct2
Kết hợp những điều trên lại, để đọc từng dòng từ đầu đến cuối sẽ làm như sau:
WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'ID:' + CAST[@id as nvarchar] PRINT 'TITLE:' @title FETCH NEXT FROM cursorProduct INTO @it, @title END
Bước 4: sau khi không còn dùng đến Cursor, cần đóng lại và giải phóng các tài nguyên nó chiếm giữ sau khi không còn dùng đến Cursor, cần đóng lại và giải phóng các tài nguyên nó chiếm giữ
CLOSE cursorProduct DEALLOCATE cursorProduct
Tóm tắt lại các câu lệnh
Các câu lệnh sử dụng Cursor ở trên, tổng hợp lại trong một ví dụ hoàn chỉnh như sau:
--Khai báo biến @id, @title để lưu nội dung đọc DECLARE @id int DECLARE @title nvarchar[200] DECLARE cursorProduct CURSOR FOR -- khai báo con trỏ cursorProduct SELECT id, title FROM Product -- dữ liệu trỏ tới OPEN cursorProduct -- Mở con trỏ FETCH NEXT FROM cursorProduct -- Đọc dòng đầu tiên INTO @id, @title WHILE @@FETCH_STATUS = 0 --vòng lặp WHILE khi đọc Cursor thành công BEGIN --In kết quả hoặc thực hiện bất kỳ truy vấn --nào dựa trên kết quả đọc được PRINT 'ID:' + CAST[@id as nvarchar] PRINT 'TITLE:' @title FETCH NEXT FROM cursorProduct -- Đọc dòng tiếp INTO @id, @title END CLOSE cursorProduct -- Đóng Cursor DEALLOCATE cursorProduct -- Giải phóng tài nguyên
MySQL rất là phổ biến, được sử dụng nhiều, nhưng mọi người thường hay quên mất cú pháp của nó, lúc nào cần dùng lại đi tra google. Vì thế bài viết này sẽ tổng hợp lại các cú pháp, từ cơ bản cho đến nâng cao, để lúc nào cần mọi người có thể tiện sử dụng luôn.
Kết nối tới MySQL Server
Bước đầu tiên để làm việc với MySQL database là kết nối với nó. Trong terminal, gõ lệnh:
mysql [-h machine] -u -p [db_name]
Nếu server và client đều chạy trên cùng 1 máy, không cần thiết phải thêm
Open cursorProduct3.
Open cursorProduct4 cũng không bắt buộc, vì trong phần tiếp theo, ta sẽ thấy có thể tạo và thay đổi database hiện tại chỉ với 1 câu lệnh. Để kết thúc kết nối tới MySQL, ta có thể gõ
Open cursorProduct5,
Open cursorProduct6 hoặc
Open cursorProduct7.
Tạo User và Database
MySQL làm việc với user mặc định là root, tuy nhiên nó chỉ nên dùng để quản lý database, không phải để thao tác dữ liệu. Đó là lý do chúng ta nên tạo user.
CREATE USER 'mike' IDENTIFIED BY 'difficultpassword'
CREATE USER 'mike'@'localhost' IDENTIFED BY 'difficultpassword'
Đoạn code trên, chúng ta không tạo ra 2 user, mà là 1 user có thể sử dụng được kể cả khi client ở trong hay bên ngoài server. Giờ chúng ta sẽ xem cách để tạo database và phân quyền cho các user khác nhau.
SELECT id,name FROM Product0
Tạo bảng
Bảng là khái niệm cốt lõi của hệ quản trị cơ sở dữ liệu như MySQL. Chúng ta sẽ học cách tạo bảng đơn giản, và cách để set primary keys, restrictions, foreign keys, và giá trị mặc định.
SELECT id,name FROM Product1
Thêm bản ghi
Sau khi tạo bảng, bước tiếp theo là thêm data. Ví dụ tiếp theo bao gồm cả cách thêm 1 bản ghi và nhiều bản ghi cùng lúc
SELECT id,name FROM Product2
Thực hiện truy vấn
Truy vấn sẽ được hoàn thành chỉ với một câu lệnh
Open cursorProduct8, nó cho phép chúng ta lấy dữ liệu từ database. MySQL cho phép chúng ta thực hiện các câu truy vấn phức tạp, lấy dữ liệu từ nhiều bảng hoặc tạo các toán tử logic từ kết quả của các câu query khác.
SELECT id,name FROM Product3
Tạo View
View cho phép chúng ta gói lệnh
Open cursorProduct8 vào trong 1 view giống như 1 bảng mới. Sau đó chúng ta có thể set quyền cho các user đối với view đó. Tuy nhiên, nếu như lệnh
Open cursorProduct8 được gói có các hàm tính toán như
FETCH NEXT FROM cursorProduct INTO @id, @title1,
FETCH NEXT FROM cursorProduct INTO @id, @title2,... hoặc sử dụng
FETCH NEXT FROM cursorProduct INTO @id, @title3,
FETCH NEXT FROM cursorProduct INTO @id, @title4,... thì không thể thực hiện các thao tác
FETCH NEXT FROM cursorProduct INTO @id, @title5,
FETCH NEXT FROM cursorProduct INTO @id, @title6, hay
FETCH NEXT FROM cursorProduct INTO @id, @title7 với view đó.
SELECT id,name FROM Product4
Thực hiện Transaction
Transaction là 1 nhóm các lệnh, nếu 1 lệnh trong transaction không thành công MySQL sẽ hoàn tác lại các lệnh trước đó.
SELECT id,name FROM Product5
Tạo Stored Procedure
Stored Procedure là một chuỗi câu SQL có thể được gọi bất cứ lúc nào từ console hay file
FETCH NEXT FROM cursorProduct INTO @id, @title8. Nó tương tự như function trong các ngôn ngữ lập trình.
SELECT id,name FROM Product6
Chơi với các biến
Biến là một cách hữu dụng để lưu trữ tạm thời kết quả của câu query hoặc giá trị của 1 cột trong một bản ghi để dùng sau. Có 2 loại biến chính trong MySQL: local và user-defined [hay còn gọi là biến session]. Loại đầu tiên được khai báo trước khi sử dụng, và phạm vi của chúng được giới hạn trong
FETCH NEXT FROM cursorProduct INTO @id, @title9, nơi định nghĩa chúng. Loại thứ 2 không phải khai báo trước, giá trị của nó có thể sử dụng bất cứ lúc nào, nhưng chỉ trong session được tạo bởi client.
SELECT id,name FROM Product7
Chơi với Cursors
Cursor là một công cụ giống như vòng lặp chạy qua từng bản ghi trong kết quả của câu truy vấn. Nó sẽ rất hữu dụng khi muốn chuyển mỗi bản ghi thành 1 object chẳng hạn. Cách khai báo và sử dụng cũng rất dễ dàng:
SELECT id,name FROM Product8
Điều khiển luồng
Bên trong stored procedure, chúng ta có thể điều khiến luồng, giống như trong các ngôn ngữ lập trình khác
SELECT id,name FROM Product9
Tạo Trigger
Trigger là một phương thức được chạy trước hoặc sau một hành động nào đó, ví dụ như insert, update, hay delete trong table hoặc view. Chúng ta cần cẩn thận với trigger vì nó có thể tốn khá nhiều tài nguyên server.
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product0
Tạo một Scheduled Event
Scheduled Event là một trigger được thực thi vào một tời điểm cụ thể nào đó. Có sự kiện diễn ra 1 lần, lên lịch vào ngày, giờ cụ thể nào đó, hoặc là các sự kiện định kì, chạy vào mỗi phút, giờ, ngày,... trong 1 khoảng thời gian cố định.
DECLARE cursorProduct CURSOR FOR SELECT id, title FROM Product1
MySQL rất là phức tạp và có quá nhiều thứ để nói, không thể trình bày hết trong bài viết này. Tuy nhiên những điều trên chắc cũng khá đủ cho phần lớn các mục đích thông thường, nếu cần nhiều hơn, bạn có thể tham khảo tài liệu chính thức này.
Nguồn: //medium.com/better-programming/the-mysql-cheatsheet-we-all-need-d1af0377bdc6