大規模なデータセットをまとめています。 クエリからの戻り時間を管理しやすくし、Oraclesのリスト内の1000項目の制限内に留めるために、1セットのキーを除いて同一の複数のクエリを実行します。 PL / SQLは、問合せをコンパイルしてから実行時に送信される変更項目を許可する「バインド変数」をサポートしています。 これにより応答が大幅にスピードアップします。 JSL内でこれを行う方法はありますか？クエリの例は次のとおりです。
選択する
日付、時刻、温度、シリアル番号
から
データ表
どこで
SerialNum IN（ '1234'、'2345' .....）しかしこれらのうちの数千）
ありがとうございました
レイ
リスト内の1000要素間のUNIONを使用して、1つのSQLステートメントですべて実行できます。 この機能はそれを行います：
//------------------------------------------------------------------------------
/*
Function Name: get_sql_in_list
Description: Build an IN list from the list provided. If there are more than 1000
items in the list, separate them into 1000-element chunks connected
via UNION to avoid the 1000 limit in Oracle.
If the elements are of type String, single quotes will be replaced with
two single quotes, and single quotes will be put around each item.
Sample calls:
// List of Case IDs
case_id_list = {111, 222, 333, 444, 555};
preamble = "(SELECT ci1.case_id FROM jnjdss.case_info ci1 WHERE ci1.case_id IN (";
x = get_sql_in_list(case_id_list, preamble);
Returns: "((SELECT ci1.case_id FROM jnjdss.case_info ci1 WHERE ci1.case_id IN (111,222,333,444,555)))"
// List of product names
product_list = {"aaaaaaa", "bbbbbbbb", "cccccccc", "dddddddd"};
preamble = "(SELECT rc1.alert_name FROM brmsupport.rts_calendar rc1 WHERE rc1.alert_name IN (";
z = get_sql_in_list(product_list, preamble);
Returns: "((SELECT rc1.alert_name FROM brmsupport.rts_calendar rc1 WHERE rc1.alert_name IN ('aaaaaaa','bbbbbbbb','cccccccc','dddddddd')))"
Arguments:
_item_list List of items to create an IN list from
_preamble SQL string to preface the IN list with
Version Date Author Details of revision
--------------------------------------------------------------------------------
1.0 05-May-2016 Peter Mroz First Draft
*/
get_sql_in_list = function({_item_list, _preamble},
{Default Local},
pname = "get_sql_in_list";
nr = nitems(_item_list);
if (nr < 0,
throw("Empty list");
);
_sql_in_list = _preamble;
one_type = type(_item_list[1]);
if (one_type == "String",
single_quotes = 1;
// for strings replace single quotes with two single quotes
for (i = 1, i <= nr, i++,
one_item = _item_list[i];
if (contains(one_item, "'"),
_item_list[i] = substitute(one_item, "'", "''");
)
);
,
single_quotes = 0;
);
// This is the Oracle limit for IN lists
max_items = 1000;
keep_going = 1;
istart = 1;
iend = min(max_items, nr);
while(keep_going,
if (single_quotes,
// Convert a portion of the list to an IN list with each element surrounded by single quotes
if (nr > 1,
_sql_in_list = _sql_in_list || "'" || concat items(_item_list[istart::iend], "','") || "'";
,
_sql_in_list = _sql_in_list || "'" || _item_list[istart::iend] || "'";
)
,
// Convert a portion of the list to a string
one_segment = char(_item_list[istart::iend]);
// Strip off the leading and trailing curly braces
if (nr > 1,
_sql_in_list = _sql_in_list || substr(one_segment, 2, length(one_segment) - 2);
,
_sql_in_list = _sql_in_list || one_segment;
);
);
istart = istart + max_items;
iend = min(nr, (iend + max_items));
if (istart <= nr,
// then
_sql_in_list = _sql_in_list || ")) UNION " || _preamble;
,
// else we're done
keep_going = 0;
);
);
// Wrap it up
_sql_in_list = "(" || _sql_in_list || ")))";
); // end get_sql_in_list
