Gọi thủ tục lồng nhau với EXECUTE: thực thi SQL động

Trước khi vào phần thực hành, tụi mình cùng trả lời câu hỏi: dynamic SQL là gì nhỉ? Hãy tưởng tượng bạn cần tạo một bảng với tên duy nhất, mà tên này được truyền vào như một tham số. Hoặc bạn cần truy vấn một bảng mà tên bảng chỉ biết khi chương trình chạy. Lúc này, SQL tĩnh không đủ xài đâu — đây là lúc thực thi động phát huy tác dụng.

PL/pgSQL cung cấp lệnh EXECUTE, cho phép bạn chạy câu lệnh SQL được truyền dưới dạng chuỗi. Nhờ đó, bạn có thể xây dựng và chạy SQL "tại chỗ", tạo ra các truy vấn thay đổi tùy theo tham số truyền vào.

Lý do dynamic SQL hữu ích:

1. Linh hoạt: Có thể xây dựng truy vấn động dựa trên dữ liệu đầu vào. Ví dụ, thao tác với bảng hoặc cột mà tên chưa biết trước.
2. Tự động hóa: Tạo bảng hoặc index với tên duy nhất.
3. Đa năng: Làm việc với nhiều cấu trúc dữ liệu khác nhau mà không phải viết lại thủ tục.

Ví dụ thực tế: giả sử bạn đang phát triển hệ thống phân tích, và mỗi khách hàng mới cần có một bảng riêng để lưu dữ liệu của họ. Tất cả có thể tự động hóa bằng EXECUTE.

Cú pháp EXECUTE

Dùng dynamic SQL qua EXECUTE như sau:

EXECUTE 'chuỗi-SQL';

Ví dụ truy vấn đơn giản:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

Đoạn code này sẽ tạo bảng test_table. Đơn giản thôi, nhưng tụi mình cùng xem các tình huống phức tạp hơn nhé.

Ví dụ sử dụng EXECUTE

1. Tạo bảng với tên động

Giả sử bạn cần tạo bảng với tên dựa trên ngày hiện tại. Làm như này nè:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Tạo tên bảng động
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Tạo bảng với tên động
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- In thông báo kiểm tra
  RAISE NOTICE 'Bảng % đã được tạo thành công', table_name;
END $$;

Ở đây, tên bảng động được tạo từ ngày hiện tại, và chuỗi SQL cuối cùng được truyền vào EXECUTE.

2. Thực thi truy vấn với tham số động

Giả sử bạn cần lấy dữ liệu từ bảng mà tên bảng được truyền vào như tham số. Tạo function như sau nhé:

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

Gọi function:

SELECT * FROM get_data_from_table('employees');

Cách này rất hợp để xây dựng các tiện ích đa năng, ví dụ hệ thống báo cáo động.

Vấn đề và hạn chế của dynamic SQL

Dynamic SQL cho bạn nhiều tự do, nhưng như ngoài đời, tự do cũng đi kèm trách nhiệm. Đây là những chỗ có thể gặp rắc rối:

1. SQL-injection: nếu bạn truyền tham số dạng chuỗi vào truy vấn mà không xử lý, kẻ xấu có thể thực thi SQL bất kỳ.

   Ví dụ code dễ bị tấn công:

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   Nếu user_input chứa chuỗi '; DROP TABLE users; --, truy vấn sẽ xóa bảng users luôn.

2. Khó debug: code động khó phân tích và debug hơn, vì truy vấn chỉ được tạo và chạy khi thực thi.

3. Giảm hiệu năng: truy vấn động làm PostgreSQL không cache được execution plan, có thể khiến hiệu năng giảm.

Làm sao chống SQL-injection

Để tránh bị SQL-injection, hãy dùng tham số hóa trong truy vấn động thay vì nối chuỗi đơn giản. Trong PL/pgSQL, dùng quote_literal() cho tham số chuỗi và quote_ident() cho identifier (tên bảng, cột).

Ví dụ code an toàn:

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

Thực thi: cập nhật bảng động

Đây là ví dụ thủ tục cập nhật giá trị trong bảng với tên truyền vào như tham số:

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

Gọi function:

SELECT update_table_data('test_table', 1, 'Giá trị đã cập nhật');

Ví dụ: tạo báo cáo cho khách hàng

Giả sử bạn quản lý đơn hàng theo khách hàng và muốn tự động tạo bảng báo cáo cho từng khách.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Tạo tên bảng báo cáo
  table_name := 'client_report_' || client_id;

  -- Tạo bảng báo cáo
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Đổ dữ liệu vào bảng
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Báo cáo cho khách % đã tạo: bảng %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Dynamic SQL với EXECUTE là công cụ cực mạnh, mở ra nhiều khả năng tự động hóa và linh hoạt trong PL/pgSQL. Nhưng nhớ dùng cẩn thận, đừng quên nguy cơ SQL-injection nhé. Nếu muốn truy vấn an toàn, hãy dùng quote_ident() và quote_literal().

Bài sau tụi mình sẽ đi sâu vào tạo thủ tục phức tạp, gồm kiểm tra dữ liệu, cập nhật bản ghi và ghi log thao tác. Chuẩn bị tinh thần vì làm việc với truy vấn động sẽ là nền tảng cho các tác vụ này đó!

1

Khảo sát/đố vui

, cấp độ , bài học

Không có sẵn

Giao dịch lồng nhau

Giao dịch lồng nhau

Mở