Oracle:将字符串拆分为行 [英] Oracle: split string to rows
本文介绍了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.
这篇关于Oracle:将字符串拆分为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文