Stored-Procedures

與數據庫無關的儲存過程呼叫傳遞名稱/值對數組

  • May 17, 2018

背景

希望使用 JDBC 以與數據庫無關的方式將一組名稱/值對傳遞到儲存過程中。一個數據庫結構定義如下:

CREATE TYPE array_parameters AS (
 v_name VARCHAR2(255),
 v_value CLOB
);

這種結構在大多數現代關係數據庫中可以具有等效的定義,被提議作為一種將任意數量的名稱/值對傳遞到儲存過程中的方法。儲存過程呼叫類似於:

SELECT rxm( '...map...', array_parameters );

其中...map...可以包含任意數量的變數引用,採用以下形式

account.id = $id &&
person.last_name = $surname && ...

理論上,array_parameters可以填充為:

array_parameters[0].v_name = "$id";
array_parameters[0].v_value = "123456789";
array_parameters[1].v_name = "$surname";
array_parameters[1].v_value = "O'Malley, The \"Great\"";

問題

JDBC4定義了一個名為createArrayOf的方法,即新華南商城API:

如果沒有創建名稱/值對數組的能力,我看不到在不求助於特定於數據庫的實現(例如使用 Oracle 的ARRAY或鈍化扭曲來支持 MySQL)的情況下傳遞值的明顯方法。

您將如何定義並呼叫一個儲存過程,該儲存過程可以以與數據庫無關的方式採用任意數量的名稱/值對?

想法#1

一種想法是定義兩個字元串數組,而不是對像數組結構,並按如下方式呼叫儲存過程:

SELECT rxm( '...map...', array_names, array_values );

這兩個數組將是索引連結的,但這也可能取決於createArrayOf()

想法#2

可以將配對作為逗號分隔的字元串傳遞。但是,這些值本身可能包含逗號,這使得使用逗號分隔字元串的參數編碼變得棘手。(一般來說,任何分隔符都可以作為字元出現在值中的某處,其中也包括轉義的分隔符,例如\,.)

這似乎是最與數據庫無關的解決方案,但在 PL/SQL 中跨多個數據庫實現 CSV 解碼常式既不簡單也不高效。

想法#3

使用 Hibernate 作為抽象層,然後實現一個傳入名稱/值對數組的JPQL常式。例如,呼叫query.setParameterList可能僅適用於IN子句,而不適用於儲存過程參數。

另一種可能:

  1. 使用Base64對值進行編碼。
  2. 將值連接成逗號分隔的字元串。
  3. 傳遞結果字元串。
  4. 拆分儲存過程中的字元串。
  5. 使用 Base64 解碼字元串。

Base64 在其預設字元集中不編碼逗號,並且支持或似乎支持:

請注意,對於 SQL Server,不支持 UTF-8 編碼的文本,因此必須使用 UTF-16。這依賴於相當普遍的基本字元串拆分功能。

將值作為 XML 傳遞(在 VARCHAR 中)。以下數據庫都支持從查詢語言中分解 XML:

  • Postgres
  • MySQL
  • SQL 伺服器
  • 甲骨文
  • DB2 UDM

顯然,並非地球上每個不起眼的數據庫都支持它。但實際上,要打入 99.9% 的數據庫市場,您只需要關心以上這些。

引用自:https://dba.stackexchange.com/questions/91450