oracle查找表位置,ORACLE 查看表索引

== 查看索引列信息

SELECT INDEX_NAME,

MAX (DECODE (COLUMN_POSITION, 1, COLUMN_NAME || ' ' || DESCEND, NULL))

c1,

MAX (DECODE (COLUMN_POSITION, 2, COLUMN_NAME || ' ' || DESCEND, NULL))

c2,

MAX (DECODE (COLUMN_POSITION, 3, COLUMN_NAME || ' ' || DESCEND, NULL))

c3,

MAX (DECODE (COLUMN_POSITION, 4, COLUMN_NAME || ' ' || DESCEND, NULL))

c4,

MAX (DECODE (COLUMN_POSITION, 5, COLUMN_NAME || ' ' || DESCEND, NULL))

c5

FROM ( SELECT a.INDEX_NAME,

REPLACE (

DECODE (

descend,

'DESC', long_2_varchar (b.index_name,

b.table_NAME,

b.COLUMN_POSITION),

a.column_name),

'"',

'')

COLUMN_NAME,

a.COLUMN_POSITION,

DESCEND

FROM user_ind_columns a

LEFT JOIN

user_ind_expressions b

ON a.index_name = b.index_name

AND a.table_name = b.table_name

WHERE a.table_name = 'CM_XX_T_ZC_APP_POS'

ORDER BY INDEX_NAME, column_position)

GROUP BY INDEX_NAME;

== 根据列位置查找列名

CREATE OR REPLACE FUNCTION long_2_varchar (

p_index_name IN user_ind_expressions.index_name%TYPE,

p_table_name IN user_ind_expressions.table_name%TYPE,

p_COLUMN_POSITION IN user_ind_expressions.table_name%TYPE)

RETURN VARCHAR2

AS

l_COLUMN_EXPRESSION LONG;

BEGIN

SELECT COLUMN_EXPRESSION

INTO l_COLUMN_EXPRESSION

FROM user_ind_expressions

WHERE index_name = p_index_name

AND table_name = p_table_name

AND COLUMN_POSITION = p_COLUMN_POSITION;

RETURN SUBSTR (l_COLUMN_EXPRESSION, 1, 4000);

END;