Oracle 常用SQL整理

中文字段排序的方法：

拼音：

select empname

from tab_name

order by nlssort(empname,'nls_sort=schinese_pinyin_m');

按偏旁部首:

" p="" style="TEXT-ALIGN: left; MARGIN: 0mm 0mm 0pt; mso-pagination: widow-orphan; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto" class="MsoNormal" align="left" font="" size="2" font="" color="#993366" face="">按笔画 :
select empname

from tab_name

order by nlssort(empname,'nls_sort=schinese_stroke__m');

注：remark为varchar2 ，不能是nvarchar2<FONT color="#993366" face=""">或nchar

  

创建外键

ALTER TABLE D0010

ADD CONSTRAINT  FK_D0010_M0060             

    FOREIGN KEY ( TANCD )

    REFERENCES  M0060( TANCD );

 

删除外键

ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME；

 

创建索引

CREATE INDEX ACTY.IDX_DS010

    ON ACTY.DS010 ( WINNAME, WINTITLE, TERMINAL, STATE )

            STORAGE (       INITIAL         1M

                            NEXT            1M

                            PCTINCREASE     0       )

             TABLESPACE INDX；

 

alter index INDEXNAME rebuild；