vip_translateTopy 存储过程

 以前写的，转过来。

 

CREATE   PROCEDURE  dbo.vip_translateTopy  AS

 

SET  NOCOUNT  ON  

 

declare   @word   varchar ( 100 )                        /**/ /*@word存放从@str中取出的单个汉字*/

declare   @str   varchar ( 100 )                /**/ /*@str存放从newswriter中取出的“作者”（字符串）*/

declare   @py   varchar ( 100 )               /**/ /*@py存放生成的拼音（字符串）*/

declare   @tablename   varchar ( 50 )               /**/ /*@tablename存放表名（字符串）*/

declare   @empty   varchar ( 100 )                   /**/ /*@empty存放空字符串*/

declare   @writer   varchar ( 100 )                    /**/ /*@writer存放当前游标中的作者（字符串）*/

 

/**/ /*下面这一段判断是否存在表名为newswriter的表，如果不存在就建立一个表*/

set   @tablename = ( SELECT  TABLE_NAME  FROM  INFORMATION_SCHEMA.TABLES  where  TABLE_CATALOG = ' newsA '   and  TABLE_NAME = ' newswriter ' )      

 

if ( @tablename <> ' newswriter ' )

begin

          create   table  newswriter(作者  varchar ( 100 ),拼音  varchar ( 100 ),流水  int   identity )

          insert  newswriter(作者)

          select   distinct  作者1  from  newsinfo  where  作者1 <> ''

          union   select   distinct  作者2  from  newsinfo  where  作者2 <> ''

          union   select   distinct  作者3  from  newsinfo  where  作者3 <> ''

          union   select   distinct  作者4  from  newsinfo  where  作者4 <> ''

          union   select   distinct  作者5  from  newsinfo  where  作者5 <> ''

end

 

/**/ /*初始化数据*/

set   @py = ''

set   @empty = ''

 

/**/ /*创建游标，把结果传给@str和@writer*/

declare  cur_str  cursor   for

select  作者  from  newswriter  order   by  流水 

open  cur_str 

fetch   next   from  cur_str  into   @str

set   @writer = @str

 

/**/ /*利用双循环完成拼音的提取，连接成字符串，存入newswriter表这样的步骤 */

while  ( @@fetch_status <>- 1 )

begin

 

     while   len ( @str ) > 0

     begin

     set   @word = left ( @str , 1 )                                                                  /**/ /*提取字符串中第一个字符 */

     set   @py = @py + ( case   when   unicode ( @word )  between   19968   and   19968 + 20901      /**/ /*判断该字符是否是汉字 */

             then ( select  py  from  cn_word  where  name = @word )                         /**/ /*如果是汉字，那么从cn_word表中找出与之相对应得拼音首字母 */

             else   @empty   end  )                                                          /**/ /*如果不是汉字，那么就返回空值 */

     set   @str = right ( @str , len ( @str ) - 1 )                                                  /**/ /*从去掉第一个字符 */

     end

     update  newswriter  set  拼音 = @py   where  作者 = @writer                                /**/ /*把完成的拼音（字符串）填入表newswriter中 */

     set   @py = ''                                                                               /**/ /*初始化@py */

     fetch   next   from  cur_str  into   @str                                                    /**/ /*取下一个作者名字（字符串），并存入@str中间 */

     set   @writer = @str                                                                             /**/ /*初始化@writer */

    

end  

close  cur_str                                                                                   /**/ /*关闭游标 */

deallocate  cur_str

GO

SET  QUOTED_IDENTIFIER  OFF  

GO

SET  ANSI_NULLS  ON  

GO