DELIMITER $$CREATE PROCEDURE `message`.`hz2py_procs`(tablename VARCHAR(50))#需要操作的表名 BEGIN DECLARE py VARCHAR(3000); DECLARE hz VARCHAR(500); DECLARE allaccount INT; DECLARE onlyCur CURSOR FOR SELECT MSGCONTENT FROM tablename; #取需要转换的列 SELECT COUNT(*) INTO allaccount FROM tablename;#取记录总数
OPEN onlyCur; FETCH onlyCur INTO hz; WHILE allaccount > 0 DO #这是我写的一个汉字转拼音的函数,单独执行该函数可以正确转换,现在就是不清楚怎么将转换的py插入对应取出的那一条记录后面(我在后面新增的了一列保存拼音) SET py = (SELECT hz2py_func(hz));#py接下来该怎么操作 SET allaccount = allaccount - 1; FETCH onlyCur INTO hz; END WHILE; CLOSE onlyCur; END$$DELIMITER ;麻烦版主帮忙看下,对数据库不太熟。没专门写过存储过程!!!
刚看到你回的一个帖子说不能用动态定义游标,修改了,不过还是不知道怎么对游标所指的当前行修改(不是修改的当前游标所指列) DELIMITER $$ CREATE PROCEDURE `message`.`hz2py_procs`(tablename VARCHAR(50)) BEGIN DECLARE py VARCHAR(3000); DECLARE hz VARCHAR(500); DECLARE allaccount INT; DECLARE cur1 CURSOR FOR SELECT MSGCONTENT FROM v_wondyfox; #DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP VIEW IF EXISTS v_wondyfox ; SET @SQL=CONCAT("create view v_wondyfox as select * from ", tablename); PREPARE stmt1 FROM @SQL; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; #DECLARE onlyCur CURSOR for select MSGCONTENT,hz2py from tablename; SELECT COUNT(*) INTO allaccount FROM v_wondyfox; OPEN onlyCur; FETCH onlyCur INTO hz; WHILE allaccount > 0 DO SET hz = (SELECT hz2py_func(hz)); #获得转为拼音后怎么在汉字列后插入拼音,汉字列为msgcontent(游标所指列),拼音列为hz2py #update tablename set hz2py = hz where CURRENT OF onlyCur; SET allaccount = allaccount - 1; FETCH onlyCur INTO hz; END WHILE; CLOSE onlyCur; END$$ DELIMITER ;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;public class GetPinYin {
/**
* 返回汉字的拼音
* @param src
* 需要pinyin4j-2.5.0.jar
* @return
*/
public static String getPinYin(String src) {
char[] t1 = null;
t1 = src.toCharArray();
String[] t2 = new String[t1.length];
HanyuPinyinOutputFormat t3 = new HanyuPinyinOutputFormat();
t3.setCaseType(HanyuPinyinCaseType.LOWERCASE);
t3.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
t3.setVCharType(HanyuPinyinVCharType.WITH_V);
String t4 = "";
int t0 = t1.length;
try {
for (int i = 0; i < t0; i++) {
// 判断是否为汉字字符
if (java.lang.Character.toString(t1[i]).matches(
"[\\u4E00-\\u9FA5]+")) {
t2 = PinyinHelper.toHanyuPinyinStringArray(t1[i], t3);
t2[0]=t2[0].substring(0,1).toUpperCase()+t2[0].substring(1,t2[0].length());
t4 += t2[0];
} else {
t4 += java.lang.Character.toString(t1[i]);
}
}
return t4;
} catch (BadHanyuPinyinOutputFormatCombination e1) {
e1.printStackTrace();
}
return t4;
} /**
* 返回汉字拼音的首字母
*
* @param str
* @return
*/
public static String getPinYinHeadChar(String str) {
String convert = "";
for (int j = 0; j < str.length(); j++) {
char word = str.charAt(j);
String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(word);
if (pinyinArray != null) {
convert += pinyinArray[0].charAt(0);
} else {
convert += word;
}
}
return convert;
} //
// public static String getCnASCII(String cnStr){
// StringBuffer strBuf = new StringBuffer();
// byte[] bGBK = cnStr.getBytes();
// for(int i=0;i <bGBK.length;i++){
// strBuf.append(Integer.toHexString(bGBK[i]&0xff));
// }
// return strBuf.toString();
// } public static void main(String[] args) {
System.out.println(getPinYin("陕西紫辰科技"));
// 返回拼音的全拼样式
System.out.println(getPinYinHeadChar("西安市"));
// 返回拼音的首字母
// System.out.println(getCnASCII("西安市"));
// 返回拼音的ASCII
}
}
PROCEDURE `message`.`hz2py_procs`(tablename VARCHAR(50))#需要操作的表名
BEGIN
DECLARE py VARCHAR(3000);
DECLARE hz VARCHAR(500);
DECLARE allaccount INT;
DECLARE onlyCur CURSOR FOR SELECT MSGCONTENT FROM tablename; #取需要转换的列
SELECT COUNT(*) INTO allaccount FROM tablename;#取记录总数
OPEN onlyCur;
FETCH onlyCur INTO hz;
WHILE allaccount > 0 DO
#这是我写的一个汉字转拼音的函数,单独执行该函数可以正确转换,现在就是不清楚怎么将转换的py插入对应取出的那一条记录后面(我在后面新增的了一列保存拼音)
SET py = (SELECT hz2py_func(hz));#py接下来该怎么操作
SET allaccount = allaccount - 1;
FETCH onlyCur INTO hz;
END WHILE;
CLOSE onlyCur;
END$$DELIMITER ;麻烦版主帮忙看下,对数据库不太熟。没专门写过存储过程!!!
DELIMITER $$
CREATE
PROCEDURE `message`.`hz2py_procs`(tablename VARCHAR(50))
BEGIN
DECLARE py VARCHAR(3000);
DECLARE hz VARCHAR(500);
DECLARE allaccount INT;
DECLARE cur1 CURSOR FOR SELECT MSGCONTENT FROM v_wondyfox;
#DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP VIEW IF EXISTS v_wondyfox ;
SET @SQL=CONCAT("create view v_wondyfox as select * from ", tablename);
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
#DECLARE onlyCur CURSOR for select MSGCONTENT,hz2py from tablename;
SELECT COUNT(*) INTO allaccount FROM v_wondyfox;
OPEN onlyCur;
FETCH onlyCur INTO hz;
WHILE allaccount > 0 DO
SET hz = (SELECT hz2py_func(hz));
#获得转为拼音后怎么在汉字列后插入拼音,汉字列为msgcontent(游标所指列),拼音列为hz2py
#update tablename set hz2py = hz where CURRENT OF onlyCur;
SET allaccount = allaccount - 1;
FETCH onlyCur INTO hz;
END WHILE;
CLOSE onlyCur;
END$$
DELIMITER ;