oracle db上动态where列的快速搜索查询

Question

我有一个表(ResponseData)，其中列RESPONSE_ID、ResponseData、KEY1、KEY2、KEY3、KEY4、VALUE1、VALUE2、VALUE3、VALUE4用户可以插入以下任何类别数据。

• 1，“My response One”,“name”,null,null,null,“Apple”,null,null,null
• 2,“My response Two”,“Name”,“Age”,null,null,“Apple”,“22”,null,null

稍后在不同页面中，当用户请求name=“apple”Responsedata时，如果name=“apple”age=“22”用户请求name=“apple”age=“22”用户请求name=“apple”age=“22”用户请求name=“apple”age=“22”用户请求name=“apple”age=“22”age=“22”age=“22”age=“22”age=“22”age=“22”age=“

如何在这个场景中形成搜索查询。我尝试使用UTL_match.jaro_winkler_similary(uper(VALUE1VALUE2VALUE3VALUE4)，(USERINPUTREQUEST))以下的UTL_match.jaro_winkler_similary，方法是在所有现有记录中获取最匹配的记录。但是，如果在表中激发具有更多记录的查询，则会产生延迟响应。感谢您的投入。

Answer 1

declare
  cursor c(k1 varchar2,
           k2 varchar2,
           k3 varchar2,
           k4 varchar2,
           v1 varchar2,
           v2 varchar2,
           v3 varchar2,
           v4 varchar2)
   is(select *
        from (select ResponseData.*,
                     case
                       when    (k1 = key1 and v1 <> value1)
                            or (k1 = key2 and v1 <> value2)
                            or (k1 = key3 and v1 <> value3)
                            or (k1 = key4 and v1 <> value4)
                            or (k2 = key1 and v2 <> value1)
                            or (k2 = key2 and v2 <> value2)
                            or (k2 = key3 and v2 <> value3)
                            or (k2 = key4 and v2 <> value4)
                            or (k3 = key1 and v3 <> value1)
                            or (k3 = key2 and v3 <> value2)
                            or (k3 = key3 and v3 <> value3)
                            or (k3 = key4 and v3 <> value4)
                            or (k4 = key1 and v4 <> value1)
                            or (k4 = key2 and v4 <> value2)
                            or (k4 = key3 and v4 <> value3)
                            or (k4 = key4 and v4 <> value4)
                       then 'not match'
                       else 'match'
                     end ok
                from ResponseData)
       where ok = 'match'
          or ok = 'not match');  -- debug mode
begin
  for r in c('name', null, null, 'age', 'Apple', null, null, '22') loop
    dbms_output.put_line(r.id||': ('||r.key1||'=>'||r.value1||'), ('||r.key2||'=>'||r.value2||'), ('
                                    ||r.key3||'=>'||r.value3||'), ('||r.key4||'=>'||r.value4||') - '||r.ok);
  end loop;
end;