Gọi một hàm Oracle với kiểu PL / SQL BOOLEAN từ SQL

Một trong những tính năng mong muốn nhất trong cơ sở dữ liệu Oracle là kiểu BOOLEAN. Tiêu chuẩn SQL đã chỉ định nó một thời gian trước và RDBMS như PostgreSQL cho thấy nó có thể mạnh mẽ như thế nào, ví dụ: khi sử dụng hàm tổng hợp EVERY () .

Ngôn ngữ PL / SQL đã hỗ trợ các kiểu boolean. Chúng tôi có thể viết:

CREATE OR REPLACE FUNCTION number_to_boolean (i NUMBER) 
RETURN BOOLEAN 
IS
BEGIN
  RETURN NOT i = 0;
END number_to_boolean;
/

CREATE OR REPLACE FUNCTION boolean_to_number (b BOOLEAN) 
RETURN NUMBER 
IS
BEGIN
  RETURN CASE WHEN b THEN 1 WHEN NOT b THEN 0 END;
END boolean_to_number;
/

Từ PL / SQL, bây giờ chúng ta có thể dễ dàng gọi các hàm trên:

SET SERVEROUTPUT ON
BEGIN
  IF number_to_boolean(1) THEN
    dbms_output.put_line('1 is true');
  END IF;
  IF NOT number_to_boolean(0) THEN
    dbms_output.put_line('0 is false');
  END IF;
  IF number_to_boolean(NULL) IS NULL THEN
    dbms_output.put_line('null is null');
  END IF;
END;
/

Các bản in trên:

1 is true
0 is false
null is null

Nhưng chúng ta không thể làm điều tương tự từ SQL engine:

SELECT 
  number_to_boolean(1), 
  number_to_boolean(0), 
  number_to_boolean(null) 
FROM dual;

Điều này mang lại:

ORA-00902: invalid datatype

Cuối cùng, Oracle sẽ khắc phục điều này bằng cách hỗ trợ các kiểu boolean trong SQL engine (hãy thể hiện tình yêu của bạn ở đây ).

Mệnh đề WITH

Cho đến lúc đó, chúng ta có thể sử dụng một giải pháp tốt bằng cách sử dụng chức năng mới từ Oracle 12c. Chúng ta có thể khai báo các hàm trong WITHmệnh đề! Chạy cái này:

WITH
  FUNCTION f RETURN NUMBER IS 
  BEGIN 
    RETURN 1; 
  END f;
SELECT f
FROM dual;

Bạn sẽ nhận được:

 F
---
 1

Điều đó thật tuyệt vời, và điều tuyệt vời hơn nữa, phần này của WITHmệnh đề được viết bằng PL / SQL, nơi chúng ta có thể sử dụng lại BOOLEANkiểu. Vì vậy, chúng ta có thể xác định các hàm cầu nối cho mỗi lần gọi hàm. Thay vì điều này:

SELECT 
  number_to_boolean(1), 
  number_to_boolean(0), 
  number_to_boolean(null) 
FROM dual;

Chúng ta có thể viết điều này:

WITH
  FUNCTION number_to_boolean_(i NUMBER)
  RETURN NUMBER
  IS
    b BOOLEAN;
  BEGIN
    -- Actual function call
    b := number_to_boolean(i);

    -- Translation to numeric result
    RETURN CASE b WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
  END number_to_boolean_;
SELECT 
  number_to_boolean_(1) AS a, 
  number_to_boolean_(0) AS b, 
  number_to_boolean_(null) AS c
FROM dual;

Điều này bây giờ mang lại:

 A   B   C
-------------
 1   0   null

Tất nhiên, chúng tôi không nhận được một kiểu boolean thực sự trở lại trong tập kết quả, vì công cụ SQL không thể xử lý điều đó. Nhưng nếu bạn đang gọi hàm này từ JDBC, 1/0 / null có thể được dịch rõ ràng thành true / false / null.

Nó cũng hoạt động để xâu chuỗi. Thay vì như sau, vẫn cho ra ORA-00902:

SELECT 
  boolean_to_number(number_to_boolean(1)), 
  boolean_to_number(number_to_boolean(0)), 
  boolean_to_number(number_to_boolean(null))
FROM dual;

Chúng ta có thể viết điều này:

WITH
  FUNCTION number_to_boolean_(i NUMBER)
  RETURN NUMBER
  IS
    b BOOLEAN;
  BEGIN
    -- Actual function call
    b := number_to_boolean(i);

    -- Translation to numeric result
    RETURN CASE b WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
  END number_to_boolean_;

  FUNCTION boolean_to_number_(b NUMBER)
  RETURN NUMBER
  IS
  BEGIN
    -- Actual function call
    RETURN boolean_to_number(NOT b = 0);
  END boolean_to_number_;
SELECT 
  boolean_to_number_(number_to_boolean_(1)) AS a, 
  boolean_to_number_(number_to_boolean_(0)) AS b, 
  boolean_to_number_(number_to_boolean_(null)) AS c
FROM dual;

... lại mang lại:

 A   B   C
-------------
 1   0   null

Và bây giờ, số nguyên 1/0 / null là kiểu kết quả mong muốn thực tế.

Kỹ thuật này có thể được tự động hóa đối với bất kỳ loại hàm PL / SQL nào chấp nhận và / hoặc trả về loại PL / SQL BOOLEAN hoặc thậm chí đối với các hàm chấp nhận tham số% ROWTYPE mà chúng tôi sẽ làm việc với jOOQ trong tương lai gần.

Có thể thấy một ví dụ thực tế hơn trong câu hỏi Stack Overflow này .

Hỗ trợ jOOQ 3.12

Trong jOOQ 3.12, chúng tôi sẽ thêm hỗ trợ gốc để sử dụng các hàm như vậy trong SQL đến # 8522. Chúng tôi đã hỗ trợ các kiểu boolean PL / SQL trong các lệnh gọi thủ tục độc lập kể từ jOOQ 3.8 . Với phiên bản tiếp theo, chúng ta có thể gọi một hàm như sau:

FUNCTION f_bool (i BOOLEAN) RETURN BOOLEAN;

Từ bất kỳ đâu trong câu lệnh jOOQ, ví dụ:

Record1<Integer> r =
create()
    .select(one())
    .where(PlsObjects.fBool(false))
    .fetchOne();

assertNull(r);

Khi ở trên được gọi, câu lệnh SQL sau được tạo bởi jOOQ 3.12, đằng sau:

with
  function "F_BOOL_"(I integer)
  return integer
  is
    "r" boolean;
  begin
    "r" := "TEST"."PLS_OBJECTS"."F_BOOL"(not I = 0);
    return case when "r" then 1 when not "r" then 0 end;
  end "F_BOOL_";
  select 1
from dual
where (F_BOOL_(0) = 1)

Chú ý làm thế nào các mã biểu thức boolean giống như một vị từ / boolean thực sự?

Cho chúng tôi biết suy nghĩ của bạn trong các ý kiến!

Nếu bạn thích bài viết này, bạn có thể đọc thêm suy nghĩ của Lukas jOOQ tại đây:

• Câu hỏi thường gặp: jOOQ có bộ nhớ đệm cấp độ đầu tiên không?
• jOOQ 3.11 được phát hành với 4 cơ sở dữ liệu mới, kết hợp Impicit, chẩn đoán và hơn thế nữa