Oracle:将字符串拆分为行

本文介绍了Oracle:将字符串拆分为行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

让我们进行以下查询:

WITH temp as SELECT '123455' colum from dual SELECT * FROM big_table WHERE cod IN (SELECT colum from temp) UNION ALL SELECT * FROM big_table2 WHERE cod IN (SELECT colum from temp)

我想搜索值列表以及查找单个值，但如何构建行列表而又不必写很多UNION?

I'd like to search for a list of values as well as I can look for one single, but how can I build a list of rows without having to write a lot of UNION?

推荐答案

如果您有可用的字符串表类型，那么以下脚本可能会做您想要的

If you have a string table type available, then the following script might do what you want

create table big_table ( cod varchar2(4000) ); create table big_table2 ( cod varchar2(4000) ); insert into big_table (cod) values ('12345'); insert into big_table (cod) values ('12346'); insert into big_table (cod) values ('12347'); insert into big_table (cod) values ('12345'); insert into big_table (cod) values ('12348'); insert into big_table (cod) values ('12349'); --Example usage of the custom defined type stringarray SELECT column_value from table(stringarray('12345','12348')); WITH temp as (SELECT column_value from table(stringarray('12345','12348'))) SELECT * FROM big_table WHERE cod IN (SELECT column_value from temp) UNION ALL SELECT * FROM big_table2 WHERE cod IN (SELECT column_value from temp); drop table big_table; drop table big_table2;

您可以像这样创建stringarray类型

You can create the stringarray type like this

CREATE OR REPLACE TYPE STRINGARRAY as table of varchar2(30)

我希望能回答您的问题.

I hope that answers your question.