Tìm hiểu Cursor trong Oracle

Trong bài này mình sẽ giới thiệu một chức năng rất hay trong Oracle đó là cách tạo à sử dụng Cursor.

Cursor là một con trỏ đùng để trỏ tới một đoạn mã SQL nào đó, các đoạn mã này thường là các lệnh: SELECT, INSERT, UPDATE, DELETE.

Tạo một CURSOR giống như ta tạo một câu truy vấn và gán tên cho nó để có thể gọi và sử dụng ở nhiều nơi. 

Đọc qua chức năng thì bạn sẽ thấy nó giống như thủ tục phải không nào? Gần như vậy nhưng cú pháp và cách sử dụng đơn giản hơn, tiện lợi hơn cho những đoạn code ngắn.

Bài viết này được đăng tại [free tuts .net]

1. Tạo một Cursor trong Oracle

Trước tiên bạn cần xem cú pháp:

CURSOR cursor_name  
IS  
  SELECT_statement; 

Trình biên dịch sẽ nhận biết bắt đầu CURSOR là IS và kết thúc là đoạn cuối của câu lệnh đầu tiên vì các lệnh SELECT, INSERT, UPDATE, DELETE là lệnh đơn.

Ví dụ: Giả sử ta có bảng course, hãy tạo một CURSOR có chức năng lấy ra course_id của course_name có tên bằng với tham số name_in.

CURSOR c1  
IS  
  SELECT course_id  
  FROM courses  
  WHERE course_name = name_in;

Vì trong CURSOR c1 có tham số name_in nên khi gọi đến CURSOR này bạn phải chắc chắn đã khai báo một biến tên là name_in.

2. Lệnh open và close CURSOR

Lệnh OPEN dùng để khai báo bắt đầu sử dụng một CURSOR, còn lệnh CLOSE dùng khai báo kết thúc không sử dụng CURSOR đó nữa.

Như ở ví dụ trên mình sẽ sử dụng như sau:

DECLARE 
	name_in VARCHAR2;
BEGIN
	name_in := "Cuong";
	OPEN c1;
	/* SOME CODE HERE */	
	CLOSE c1;
END;

3. Lặp dữ liệu trả về từ CURSOR

Kết quả trả về của CURSOR thường là danh sách, vì vậy ta sẽ sử dụng FETCH ... INTO để lặp dữ liệu.

Như ở ví dụ trên mình sẽ lặp dữ liệu như sau:

DECLARE
name_in VARCHAR2;
id_in NUMBER;

BEGIN
    name_in := "Cuong"; 
    OPEN c1;
    FETCH c1 INTO id_in;
         /*SOME CODE HERE*/
    CLOSE c1;
END;

4. Ví dụ CURSOR sử dụng trong Function

Oracle cho ra đời tính năng này giúp xử lý chương trình SQL trở nên gọn gàng hơn.

Sau đây là một ví dụ về sử dụng CURSOR c1 mà mình đã tạo ở phần 1 ở bên  trong một function.

CREATE OR REPLACE FUNCTION FindCourse  
  ( name_in IN varchar2 )  
  RETURN number  
IS  
   cnumber number;  
CURSOR c1  
   IS  
     SELECT course_id  
     FROM courses  
   WHERE course_name = name_in;  
BEGIN  
OPEN c1;  
   FETCH c1 INTO cnumber;  
IF c1%notfound THEN  
      cnumber := 9999;  
 END IF;  
CLOSE c1;  
RETURN cnumber;  
END;  

Ngoài ra bạn có thể sử dụng CURSOR lồng nhau, lấy kết  quả của cái đầu tiên làm dữ liệu cho cái thứ hai, giống như bạn đang lặp dữ liệu.

CREATE OR REPLACE PROCEDURE MULTIPLE_CURSORS_PROC IS  
   v_owner varchar2(40);  
   v_table_name varchar2(40);  
   v_column_name varchar2(100);  
     
   /* First cursor */  
   CURSOR get_tables IS  
     SELECT DISTINCT tbl.owner, tbl.table_name  
     FROM all_tables tbl  
     WHERE tbl.owner = 'SYSTEM';  
       
   /* Second cursor */  
   CURSOR get_columns IS  
     SELECT DISTINCT col.column_name  
     FROM all_tab_columns col  
     WHERE col.owner = v_owner  
     AND col.table_name = v_table_name;  
       
   BEGIN  
     
   -- Open first cursor  
   OPEN get_tables;  
   LOOP  
      FETCH get_tables INTO v_owner, v_table_name;  
        
      -- Open second cursor  
      OPEN get_columns;  
      LOOP  
         FETCH get_columns INTO v_column_name;  
      END LOOP;  
     CLOSE get_columns;  
    END LOOP;  
   CLOSE get_tables;  
  EXCEPTION  
   WHEN OTHERS THEN  
 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
END MULTIPLE_CURSORS_PROC;  

Có một lưu ý là bạn phải dùng lệnh OPEN  và CLOSE đúng chỗ, nếu hkông kết quả sẽ không được như mong đợi.

Như vậy là mình đã giới thiệu xong cách sử dụng CURSOR trong  Oracle, hy vọng bài này sẽ giúp ích cho bạn, hẹn gặp lại các bạn trong bài trigger.