PL / SQL - Con trỏ
Trong chương này, chúng ta sẽ thảo luận về các con trỏ trong PL / SQL. Oracle tạo một vùng bộ nhớ, được gọi là vùng ngữ cảnh, để xử lý một câu lệnh SQL, vùng này chứa tất cả thông tin cần thiết để xử lý câu lệnh; ví dụ: số lượng hàng được xử lý, v.v.
A cursorlà một con trỏ đến khu vực ngữ cảnh này. PL / SQL điều khiển vùng ngữ cảnh thông qua con trỏ. Một con trỏ giữ các hàng (một hoặc nhiều) được trả về bởi một câu lệnh SQL. Tập hợp các hàng mà con trỏ giữ được gọi làactive set.
Bạn có thể đặt tên cho một con trỏ để nó có thể được tham chiếu đến trong một chương trình để tìm nạp và xử lý các hàng được trả về bởi câu lệnh SQL, từng hàng một. Có hai loại con trỏ -
- Con trỏ ngầm
- Con trỏ rõ ràng
Con trỏ ngầm
Các con trỏ ngầm được Oracle tạo tự động bất cứ khi nào một câu lệnh SQL được thực thi, khi không có con trỏ rõ ràng cho câu lệnh. Người lập trình không thể kiểm soát các con trỏ ngầm và thông tin trong đó.
Bất cứ khi nào một câu lệnh DML (INSERT, UPDATE và DELETE) được đưa ra, một con trỏ ngầm được liên kết với câu lệnh này. Đối với các thao tác INSERT, con trỏ giữ dữ liệu cần được chèn. Đối với các hoạt động CẬP NHẬT và XÓA, con trỏ xác định các hàng sẽ bị ảnh hưởng.
Trong PL / SQL, bạn có thể tham khảo con trỏ ngầm gần đây nhất là SQL cursor, luôn có các thuộc tính như %FOUND, %ISOPEN, %NOTFOUNDvà %ROWCOUNT. Con trỏ SQL có các thuộc tính bổ sung,%BULK_ROWCOUNT và %BULK_EXCEPTIONS, được thiết kế để sử dụng với FORALLtuyên bố. Bảng sau cung cấp mô tả về các thuộc tính được sử dụng nhiều nhất:
| S.Không | Thuộc tính & Mô tả |
|---|---|
| 1 | %FOUND Trả về TRUE nếu câu lệnh INSERT, UPDATE hoặc DELETE ảnh hưởng đến một hoặc nhiều hàng hoặc câu lệnh SELECT INTO trả về một hoặc nhiều hàng. Nếu không, nó trả về FALSE. |
| 2 | %NOTFOUND Đối lập logic với% FOUND. Nó trả về TRUE nếu câu lệnh INSERT, UPDATE hoặc DELETE không ảnh hưởng đến không có hàng hoặc câu lệnh SELECT INTO không trả về hàng nào. Nếu không, nó trả về FALSE. |
| 3 | %ISOPEN Luôn trả về FALSE cho các con trỏ không tường minh, vì Oracle tự động đóng con trỏ SQL sau khi thực hiện câu lệnh SQL liên quan của nó. |
| 4 | %ROWCOUNT Trả về số hàng bị ảnh hưởng bởi câu lệnh INSERT, UPDATE hoặc DELETE hoặc được trả về bởi câu lệnh SELECT INTO. |
Mọi thuộc tính con trỏ SQL sẽ được truy cập dưới dạng sql%attribute_name như thể hiện bên dưới trong ví dụ.
Thí dụ
Chúng ta sẽ sử dụng bảng CUSTOMERS mà chúng ta đã tạo và sử dụng trong các chương trước.
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+Chương trình sau sẽ cập nhật bảng và tăng lương của mỗi khách hàng thêm 500 và sử dụng SQL%ROWCOUNT để xác định số lượng hàng bị ảnh hưởng -
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
6 customers selected
PL/SQL procedure successfully completed.Nếu bạn kiểm tra các bản ghi trong bảng khách hàng, bạn sẽ thấy rằng các hàng đã được cập nhật -
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+Con trỏ rõ ràng
Con trỏ rõ ràng là con trỏ do lập trình viên xác định để giành được nhiều quyền kiểm soát hơn đối với context area. Một con trỏ rõ ràng nên được định nghĩa trong phần khai báo của Khối PL / SQL. Nó được tạo trên Câu lệnh SELECT trả về nhiều hơn một hàng.
Cú pháp để tạo một con trỏ rõ ràng là:
CURSOR cursor_name IS select_statement;Làm việc với một con trỏ rõ ràng bao gồm các bước sau:
- Khai báo con trỏ để khởi tạo bộ nhớ
- Mở con trỏ để cấp phát bộ nhớ
- Tìm nạp con trỏ để truy xuất dữ liệu
- Đóng con trỏ để giải phóng bộ nhớ được cấp phát
Khai báo con trỏ
Khai báo con trỏ xác định con trỏ với tên và câu lệnh SELECT liên quan. Ví dụ -
CURSOR c_customers IS
SELECT id, name, address FROM customers;Mở con trỏ
Việc mở con trỏ sẽ phân bổ bộ nhớ cho con trỏ và làm cho nó sẵn sàng để tìm nạp các hàng được câu lệnh SQL trả về vào đó. Ví dụ, chúng ta sẽ mở con trỏ được xác định ở trên như sau:
OPEN c_customers;Tìm nạp con trỏ
Tìm nạp con trỏ liên quan đến việc truy cập từng hàng một. Ví dụ, chúng tôi sẽ tìm nạp các hàng từ con trỏ đã mở ở trên như sau:
FETCH c_customers INTO c_id, c_name, c_addr;Đóng con trỏ
Đóng con trỏ có nghĩa là giải phóng bộ nhớ được cấp phát. Ví dụ, chúng ta sẽ đóng con trỏ đã mở ở trên như sau:
CLOSE c_customers;Thí dụ
Sau đây là một ví dụ đầy đủ để minh họa các khái niệm về con trỏ rõ ràng & minua;
DECLARE
c_id customers.id%type;
c_name customer.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.