一些特别的SQL语法

partition by语法。ORACLE才有的。

查询出表中的字段列表

Selectmax(SYS_CONNECT_BY_PATH(COLUMN_NAME, ', '))
From (select A.COLUMN_NAME,A.TABLE_NAME, ROWNUM AS ROWNO
from USER_TAB_COLUMNS A
where TABLE_NAME = '表名称'
ORDER BY A.COLUMN_ID)
start with ROWNO = 1
connect by ROWNO = rownum;

--重建索引

declare­

STR VARCHAR2(400);­

begin­

-- 重建ORACLE索引­

FOR TMP_IDX IN (SELECT TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME­

FROM ALL_INDEXES­

WHERE OWNER = 'HNACMS'­

AND temporary = 'N' ­

--AND TABLE_NAME = 'K_TASK'­

--AND TABLESPACE_NAME <> 'HNACMS_INDX'­

ORDER BY TABLESPACE_NAME, TABLE_NAME) LOOP­

STR := 'ALTER INDEX ' || TMP_IDX.OWNER || '.' || TMP_IDX.INDEX_NAME ||­

' Rebuild Tablespace HNACMS_INDX';­

EXECUTE IMMEDIATE STR;­

END LOOP;­

end;­