数据库取拼音码的存储过程(Oracle、Sql Server)
Oracle数据库:
Sql代码
CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_strlen INT;
v_return VARCHAR2 (500);
v_ii INT;
v_n INT;
v_c VARCHAR2 (2);
v_chn VARCHAR2 (2);
v_rc VARCHAR2 (500);
/*************************************************************************
生成汉字拼音码的函数。 wallimn 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line(v_str);
v_rc := v_str;
v_strlen := LENGTH (v_rc);
v_return := '';
v_ii := 0;
WHILE v_ii < v_strlen
LOOP
v_ii := v_ii + 1;
v_n := 63;
SELECT SUBSTR (v_rc, v_ii, 1)
INTO v_chn
FROM DUAL;
SELECT v_n + MAX (rowsf)
INTO v_n
FROM (SELECT chn, ROWNUM rowsf
FROM (SELECT chn
FROM (SELECT '吖' chn
FROM DUAL
UNION
SELECT '八'
FROM DUAL
UNION ALL
SELECT '嚓'
FROM DUAL
UNION ALL
SELECT '咑'
FROM DUAL
UNION ALL
SELECT '妸'
FROM DUAL
UNION ALL
SELECT '发'
FROM DUAL
UNION ALL
SELECT '旮'
FROM DUAL
UNION ALL
SELECT '铪'
FROM DUAL
UNION ALL
SELECT '丌'
FROM DUAL --because have no 'i'
UNION ALL
SELECT '丌'
FROM DUAL
UNION ALL
SELECT '咔'
FROM DUAL
UNION ALL
SELECT '垃'
FROM DUAL
UNION ALL
SELECT '嘸'
FROM DUAL
UNION ALL
SELECT '拏'
FROM DUAL
UNION ALL
SELECT '噢'
FROM DUAL
UNION ALL
SELECT '妑'
FROM DUAL
UNION ALL
SELECT '七'
FROM DUAL
UNION ALL
SELECT '呥'
FROM DUAL
UNION ALL
SELECT '仨'
FROM DUAL
UNION ALL
SELECT '他'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '夕'
FROM DUAL
UNION ALL
SELECT '丫'
FROM DUAL
UNION ALL
SELECT '帀'
FROM DUAL
UNION ALL
SELECT v_chn
FROM DUAL) a
ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
WHERE chn = v_chn;
v_c := CHR (v_n);
IF CHR (v_n) = '@'
THEN --英文直接返回
v_c := v_chn;
END IF;
v_return := v_return || v_c;
v_return := lower(v_return);
END LOOP;
RETURN v_return;
END fgetpy;
CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_strlen INT;
v_return VARCHAR2 (500);
v_ii INT;
v_n INT;
v_c VARCHAR2 (2);
v_chn VARCHAR2 (2);
v_rc VARCHAR2 (500);
/*************************************************************************
生成汉字拼音码的函数。 wallimn 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line(v_str);
v_rc := v_str;
v_strlen := LENGTH (v_rc);
v_return := '';
v_ii := 0;
WHILE v_ii < v_strlen
LOOP
v_ii := v_ii + 1;
v_n := 63;
SELECT SUBSTR (v_rc, v_ii, 1)
INTO v_chn
FROM DUAL;
SELECT v_n + MAX (rowsf)
INTO v_n
FROM (SELECT chn, ROWNUM rowsf
FROM (SELECT chn
FROM (SELECT '吖' chn
FROM DUAL
UNION
SELECT '八'
FROM DUAL
UNION ALL
SELECT '嚓'
FROM DUAL
UNION ALL
SELECT '咑'
FROM DUAL
UNION ALL
SELECT '妸'
FROM DUAL
UNION ALL
SELECT '发'
FROM DUAL
UNION ALL
SELECT '旮'
FROM DUAL
UNION ALL
SELECT '铪'
FROM DUAL
UNION ALL
SELECT '丌'
FROM DUAL --because have no 'i'
UNION ALL
SELECT '丌'
FROM DUAL
UNION ALL
SELECT '咔'
FROM DUAL
UNION ALL
SELECT '垃'
FROM DUAL
UNION ALL
SELECT '嘸'
FROM DUAL
UNION ALL
SELECT '拏'
FROM DUAL
UNION ALL
SELECT '噢'
FROM DUAL
UNION ALL
SELECT '妑'
FROM DUAL
UNION ALL
SELECT '七'
FROM DUAL
UNION ALL
SELECT '呥'
FROM DUAL
UNION ALL
SELECT '仨'
FROM DUAL
UNION ALL
SELECT '他'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '夕'
FROM DUAL
UNION ALL
SELECT '丫'
FROM DUAL
UNION ALL
SELECT '帀'
FROM DUAL
UNION ALL
SELECT v_chn
FROM DUAL) a
ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
WHERE chn = v_chn;
v_c := CHR (v_n);
IF CHR (v_n) = '@'
THEN --英文直接返回
v_c := v_chn;
END IF;
v_return := v_return || v_c;
v_return := lower(v_return);
END LOOP;
RETURN v_return;
END fgetpy;
Sql Server数据库:
Sql代码
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)
select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '嘸'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
set @return=@return+@c
end
return(@return)
end
Sql代码
CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_strlen INT;
v_return VARCHAR2 (500);
v_ii INT;
v_n INT;
v_c VARCHAR2 (2);
v_chn VARCHAR2 (2);
v_rc VARCHAR2 (500);
/*************************************************************************
生成汉字拼音码的函数。 wallimn 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line(v_str);
v_rc := v_str;
v_strlen := LENGTH (v_rc);
v_return := '';
v_ii := 0;
WHILE v_ii < v_strlen
LOOP
v_ii := v_ii + 1;
v_n := 63;
SELECT SUBSTR (v_rc, v_ii, 1)
INTO v_chn
FROM DUAL;
SELECT v_n + MAX (rowsf)
INTO v_n
FROM (SELECT chn, ROWNUM rowsf
FROM (SELECT chn
FROM (SELECT '吖' chn
FROM DUAL
UNION
SELECT '八'
FROM DUAL
UNION ALL
SELECT '嚓'
FROM DUAL
UNION ALL
SELECT '咑'
FROM DUAL
UNION ALL
SELECT '妸'
FROM DUAL
UNION ALL
SELECT '发'
FROM DUAL
UNION ALL
SELECT '旮'
FROM DUAL
UNION ALL
SELECT '铪'
FROM DUAL
UNION ALL
SELECT '丌'
FROM DUAL --because have no 'i'
UNION ALL
SELECT '丌'
FROM DUAL
UNION ALL
SELECT '咔'
FROM DUAL
UNION ALL
SELECT '垃'
FROM DUAL
UNION ALL
SELECT '嘸'
FROM DUAL
UNION ALL
SELECT '拏'
FROM DUAL
UNION ALL
SELECT '噢'
FROM DUAL
UNION ALL
SELECT '妑'
FROM DUAL
UNION ALL
SELECT '七'
FROM DUAL
UNION ALL
SELECT '呥'
FROM DUAL
UNION ALL
SELECT '仨'
FROM DUAL
UNION ALL
SELECT '他'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '夕'
FROM DUAL
UNION ALL
SELECT '丫'
FROM DUAL
UNION ALL
SELECT '帀'
FROM DUAL
UNION ALL
SELECT v_chn
FROM DUAL) a
ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
WHERE chn = v_chn;
v_c := CHR (v_n);
IF CHR (v_n) = '@'
THEN --英文直接返回
v_c := v_chn;
END IF;
v_return := v_return || v_c;
v_return := lower(v_return);
END LOOP;
RETURN v_return;
END fgetpy;
CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_strlen INT;
v_return VARCHAR2 (500);
v_ii INT;
v_n INT;
v_c VARCHAR2 (2);
v_chn VARCHAR2 (2);
v_rc VARCHAR2 (500);
/*************************************************************************
生成汉字拼音码的函数。 wallimn 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line(v_str);
v_rc := v_str;
v_strlen := LENGTH (v_rc);
v_return := '';
v_ii := 0;
WHILE v_ii < v_strlen
LOOP
v_ii := v_ii + 1;
v_n := 63;
SELECT SUBSTR (v_rc, v_ii, 1)
INTO v_chn
FROM DUAL;
SELECT v_n + MAX (rowsf)
INTO v_n
FROM (SELECT chn, ROWNUM rowsf
FROM (SELECT chn
FROM (SELECT '吖' chn
FROM DUAL
UNION
SELECT '八'
FROM DUAL
UNION ALL
SELECT '嚓'
FROM DUAL
UNION ALL
SELECT '咑'
FROM DUAL
UNION ALL
SELECT '妸'
FROM DUAL
UNION ALL
SELECT '发'
FROM DUAL
UNION ALL
SELECT '旮'
FROM DUAL
UNION ALL
SELECT '铪'
FROM DUAL
UNION ALL
SELECT '丌'
FROM DUAL --because have no 'i'
UNION ALL
SELECT '丌'
FROM DUAL
UNION ALL
SELECT '咔'
FROM DUAL
UNION ALL
SELECT '垃'
FROM DUAL
UNION ALL
SELECT '嘸'
FROM DUAL
UNION ALL
SELECT '拏'
FROM DUAL
UNION ALL
SELECT '噢'
FROM DUAL
UNION ALL
SELECT '妑'
FROM DUAL
UNION ALL
SELECT '七'
FROM DUAL
UNION ALL
SELECT '呥'
FROM DUAL
UNION ALL
SELECT '仨'
FROM DUAL
UNION ALL
SELECT '他'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '夕'
FROM DUAL
UNION ALL
SELECT '丫'
FROM DUAL
UNION ALL
SELECT '帀'
FROM DUAL
UNION ALL
SELECT v_chn
FROM DUAL) a
ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
WHERE chn = v_chn;
v_c := CHR (v_n);
IF CHR (v_n) = '@'
THEN --英文直接返回
v_c := v_chn;
END IF;
v_return := v_return || v_c;
v_return := lower(v_return);
END LOOP;
RETURN v_return;
END fgetpy;
Sql Server数据库:
Sql代码
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)
select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '嘸'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
set @return=@return+@c
end
return(@return)
end
相关推荐
下边是部分目录,觉得有用的话就顶一个 C:. │ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql │ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整tempdb数据库的文件...
│ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql │ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整tempdb数据库的文件属性.sql │ ├─第02章 │ │ 2.1 日期概念理解中的一些测试.sql │ │ ...
车辆管理:车辆及人员动态、车辆登记、需年审车本、年检记录、维修记录、事故管理、值班日志、值班安排、报废登记、车辆购买申请、...10、网络版本支持环境sqlserver2000、Oracle、Informix、Sybase等多个大型数据库。
系统硬件信息、存储设备管理、鼠标及键盘、声音和视频、图形和图像、网络、数据库) 《远程控制编程技术》源代码 内含(重启、图片操作、ip操作、键盘与鼠标、客户端以及服务端、文件传输等实例源码) 多个VC++...
系统硬件信息、存储设备管理、鼠标及键盘、声音和视频、图形和图像、网络、数据库) 《远程控制编程技术》源代码 内含(重启、图片操作、ip操作、键盘与鼠标、客户端以及服务端、文件传输等实例源码) 多个VC++...
系统硬件信息、存储设备管理、鼠标及键盘、声音和视频、图形和图像、网络、数据库) IOCP 完成端口编程 《远程控制编程技术》源代码 内含(重启、图片操作、ip操作、键盘与鼠标、客户端以及服务端、文件传输等实例...
系统硬件信息、存储设备管理、鼠标及键盘、声音和视频、图形和图像、网络、数据库) IOCP 完成端口编程 《远程控制编程技术》源代码 内含(重启、图片操作、ip操作、键盘与鼠标、客户端以及服务端、文件传输等实例...
系统硬件信息、存储设备管理、鼠标及键盘、声音和视频、图形和图像、网络、数据库) IOCP 完成端口编程技术 《远程控制编程技术》源代码 内含(重启、图片操作、ip操作、键盘与鼠标、客户端以及服务端、文件传输等...
系统硬件信息、存储设备管理、鼠标及键盘、声音和视频、图形和图像、网络、数据库) 《远程控制编程技术》源代码 内含(重启、图片操作、ip操作、键盘与鼠标、客户端以及服务端、文件传输等实例源码) 多个VC++...
第一步:运行ServerData.java 启动服务器,然后服务器处于等待状态 第二步:运行LoginData.java 启动(客户端)登陆界面 输入用户名 ip为本机localhost 第三步:在登陆后的界面文本框输入文本,然后发送 可以同时启动...
在有状态SessionBean中,用累加器,以对话状态存储起来,创建EJB对象,并将当前的计数器初始化,调用每一个EJB对象的count()方法,保证Bean正常被激活和钝化,EJB对象是用完毕,从内存中清除…… Java Socket 聊天...