1.Oracle IN()使用子查询得出的字符串参数:
SELECT *
FROM tname
WHERE tname.id IN
( SELECT *
FROM (SELECT REGEXP_SUBSTR('1,2,3,4', '[^,]+', 1, ROWNUM) n FROM dual CONNECT BY ROWNUM < 5)
WHERE n IS NOT NULL
)//参数1是从最头那个字母开始,rownum是第几次出现
-------------------------------------------------------------
2.oracle查询表的所有列名:
SELECT lower(column_name) FROM all_tab_columns WHERE table_name = upper('table_name') ORDER BY column_id
-------------------------------------------------------------
3.oracke列转为行,中间添加逗号:
select listagg(column1, ',') within group(order BY column2) as c from table
-------------------------------------------------------------
SELECT *
FROM tname
WHERE tname.id IN
( SELECT *
FROM (SELECT REGEXP_SUBSTR('1,2,3,4', '[^,]+', 1, ROWNUM) n FROM dual CONNECT BY ROWNUM < 5)
WHERE n IS NOT NULL
)//参数1是从最头那个字母开始,rownum是第几次出现
-------------------------------------------------------------
2.oracle查询表的所有列名:
SELECT lower(column_name) FROM all_tab_columns WHERE table_name = upper('table_name') ORDER BY column_id
-------------------------------------------------------------
3.oracke列转为行,中间添加逗号:
select listagg(column1, ',') within group(order BY column2) as c from table
-------------------------------------------------------------