41773_zh-TW

liliconcom

在JSL中使用SQL綁定變量

我正在把大數據集合在一起。 為了保持查詢的返回時間可管理並保持在100個項目的Oracles列表限制內，我正在運行多個查詢，這些查詢除了一組鍵之外是相同的。 PL / SQL支持“綁定變量”，它允許編譯查詢，然後在運行時更改提交的項目。 這大大加快了響應速度。 有沒有辦法在JSL中執行此操作？示例查詢可能是：

 

選擇

    日期，時間，溫度，SerialNum

從

   Data_Table

哪裡

    SerialNum IN（'1234'，'2345'.....）但其中有數千個）

 

謝謝

射線

Re：在JSL中使用SQL綁定變量

您可以在一個SQL語句中使用UNION在列表中的1000個元素之間完成所有操作。 這個功能可以做到：

//------------------------------------------------------------------------------
/*
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