17,088
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TB_STROKE(WORD VARCHAR2(2),STROKE_COUNT NUMBER(2,0));
INSERT INTO TB_STROKE(WORD,STROKE_COUNT)
SELECT '一',1 FROM DUAL
UNION ALL
SELECT '二',2 FROM DUAL
UNION ALL
SELECT '口',3 FROM DUAL
UNION ALL
SELECT '木',4 FROM DUAL
UNION ALL
SELECT '禾',5 FROM DUAL
UNION ALL
SELECT '众',6 FROM DUAL
UNION ALL
SELECT '沐',7 FROM DUAL
UNION ALL
SELECT '和',8 FROM DUAL
UNION ALL
SELECT '骨',9 FROM DUAL
UNION ALL
SELECT '徐',10 FROM DUAL
UNION ALL
SELECT '曹',11 FROM DUAL
UNION ALL
SELECT '彭',12 FROM DUAL;
CREATE OR REPLACE FUNCTION GET_STROKE_COUNT(F_WORD IN VARCHAR2 )
RETURN INT
AS
I INT:=0;
BEGIN
SELECT MAX(STROKE_COUNT)+1 INTO I FROM TB_STROKE where nlssort(WORD,'NLS_SORT=SCHINESE_STROKE_M')<nlssort(F_WORD,'NLS_SORT=SCHINESE_STROKE_M');
RETURN I;
END GET_STROKE_COUNT;
--测试
SELECT GET_STROKE_COUNT('甲') FROM DUAL;
--显示5
SELECT GET_STROKE_COUNT('骨') FROM DUAL;
--显示9
SELECT GET_STROKE_COUNT('文') FROM DUAL;
--显示4
--SQL内直接调用
SELECT MAX(STROKE_COUNT)+1 FROM TB_STROKE where nlssort(WORD,'NLS_SORT=SCHINESE_STROKE_M')<nlssort('甲','NLS_SORT=SCHINESE_STROKE_M');
SELECT nlssort('二','NLS_SORT=SCHINESE_STROKE_M') FROM DUAL
UNION ALL
SELECT nlssort('丁','NLS_SORT=SCHINESE_STROKE_M') FROM DUAL
UNION ALL
SELECT nlssort('人','NLS_SORT=SCHINESE_STROKE_M') FROM DUAL
如何修改系统默认规则:
alter session set nls_sort='schinese_pinyin_m';
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
Oracle9i之前,中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序,系统的默认排序方式为拼音排序
举例如下:
表名为 dept ,其中name字段是中文,下面分别实现按照单位名称的笔划、部首和拼音排序。
1: //按照笔划排序
2: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
3: //按照部首排序
4: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
5: //按照拼音排序,此为系统的默认排序方式
6: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); 注意,该SQL指令并非标准指令,在SQLServer下面的实现方式并不相同。
//按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
//按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
//按照拼音排序,此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
//按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
//按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
//按照拼音排序,此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');