数据库取拼音码的存储过程(Oracle、Sql Server)

Oracle数据库：

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数据库：

 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

评论

1 楼 taochenpfj 2009-11-25  

是首字母啊！不是全部的