如何写一个像java分割函数的oracle SQL？ [英] How to write an oracle SQL like java split function?

问题描述

现在我想编写sql，如果我传递了像'Aaron`这样的关键字，那么我搜索结果：

Now I want to write the sql,if I passed the keyword like `Aaron` ,then I search the result:

`select * from worker where name like %Aaron%`

如果我传递了像'Aaron Alina Wendy`这样的关键字，那么我搜索结果：

if I passed the keyword like `Aaron Alina Wendy`,then I search the result:

`select * from worker where name like %Aaron% or name like '%Alina%' or name like '%Wendy%'`

我知道必须有一种简单的方法，比如分割功能或其他方式获取搜索结果，我不需要将关键字拆分为spl冰sql语句，怎么做？我可以这样做：

I know there must have a easy way like split function or other way to get the search result,and I do not need to split the keyword to splice the sql statement,How to do that?Can I do the job like this:

select * from worker where fn_getSearchCondition('Aaron Alina Wendy')

我的尝试：



我尝试使用java代码编写sql，但我认为必须有一种简单的方法，比如split函数或其他方式来获取搜索结果，我不需要拆分关键字来拼接sql语句。

What I have tried:

I have try to write the sql using java code,but I think there must have a easy way like split function or other way to get the search result,and I do not need to split the keyword to splice the sql statement.

推荐答案

您不能在SQL语句中使用动态条件。根据参数中的名称数量， WHERE 子句中的条件数量会有所不同。



但是，您可以使用动态SQL的PL / SQL在变量中创建SQL语句然后执行它。请查看 PL / SQL动态SQL [ ^ ]。



为了能够很好地使用这个程序，我个人会使用表函数。换句话说，我创建了一个函数，它返回一个结果集，可以像 FROM 子句中的表一样使用。例如，请查看创建Oracle函数的示例 [ ^ ]。在那里你应该找到一个分裂字符串和返回表格的例子。

You can't use a dynamic condition in an SQL statement. Depending on the amount of names in your parameter, you would have different amount of conditions in the WHERE clause.

However, you can use dynamic SQL ín PL/SQL to create an SQL statement into a variable and then execute it. Have a look at the examples in PL/SQL Dynamic SQL[^].

To be able to use the procedure nicely I'd personally use a table function. In other words I'd create a function that would return a result set that can be used like a table in FROM clause. For examples, have a look at Examples for Creating Oracle Functions[^]. There you should find an example for both splitting a string and returning a table.

这篇关于如何写一个像java分割函数的oracle SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！