一些特别的SQL语法

partition by语法。ORACLE才有的。 查询出表中的字段列表 Select  max(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;­