要写个存储过程,作用是 从表A查出所有行的名字,然后用游标一行一行取,取出名字之后对其进行分割为一个一个字,将分割后的字放入变量,再根据变量中的字到表B查找对应的拼音并取出拼音,将名字的拼音合并之后,添加到表A中的一个字段中。
现在取出名字之后,名字中间没有间隔什么字符,不知道怎么分割,还请高手请教啊。知道这个存储过程怎么写的话麻烦也帮帮忙,指点一二 。
现在取出名字之后,名字中间没有间隔什么字符,不知道怎么分割,还请高手请教啊。知道这个存储过程怎么写的话麻烦也帮帮忙,指点一二 。
调试欢乐多
SELECT substr(a, LEVEL, 1) t
FROM (SELECT '中国人' a FROM dual)
CONNECT BY level <= length(a)--
1 中
2 国
3 人
............
cnName varchar2(100) := '';
strSql varchar(1000) := '';
--游标相关
TYPE myref IS REF CURSOR;
cur myref;
singleChar varchar2(1);
strResult varchar2(1);
begin
--从表A(由于楼主未列出表结构,我用我自己的表测试)查出名字,循环遍历
for nameList in (select indCnName from hi_indicators) loop
--查询结果赋值给变量
cnName := nameList.Indcnname;
--逐字拆分
strSql := 'SELECT substr(a, LEVEL, 1) t ' ||
' FROM (SELECT ' || cnName || ' a FROM dual)' ||
'CONNECT BY level <= length(a)'
dbms_output.put_line('--开始处理:' || cnName);
--游标
OPEN cur FOR strSql;
loop
Fetch cur into singleChar;
Exit when cur%notfound;
--将分割后的字放入变量,再根据变量中的字到表B查找对应的拼音并取出拼音
--没有B表的结构,楼主自己代入
strSql := 'select 拼音 from 表B where 字=''' || singleChar || '''';
execute immediate strSql into strResult;
dbms_output.put_line('字符:' || singleChar || ',拼音为:' || strResult);
end loop
end loop;
end;
iscursor c_split_chinesestr(v_chineseStr varchar2) is SELECT substr(a, LEVEL, 1) t
FROM (SELECT v_chineseStr a FROM dual)
CONNECT BY level <= length(a);v_sigle_chinese c_split_chinesestr%rowtype;
v_pingyin 表B.拼音%type;begin
open c_split_chinesestr('中国银行');
loop
fetch c_split_chinesestr into v_sigle_chinese; exit when c_split_chinesestr%notfound;
--单个汉字
dbms_output.put_line('---'||v_sigle_chinese.t);
--将分割后的字放入变量,再根据变量中的字到表B查找对应的拼音并取出拼音
--没有B表的结构,楼主自己代入
select 拼音 into v_pingyin from 表B where 字=v_sigle_chinese.t; dbms_output.put_line('字符:' || v_sigle_chinese.t || ',拼音为:' || v_pingyin);
end loop;
close c_split_chinesestr;end;
with tb as
(select '中国人' nam from dual)
select substr(nam,rownum,1) from tb connect by rownum<=length(nam)
ORA-06550: line 1, column 7:
PLS-00905: object RCMT_TEST.PROC_SPLIT_CHINESE_STRING is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
CREATE TABLE T91
(
ID NUMBER(4),
NAME VARCHAR2(20),
Anounce VARCHAR2(20)
);
INSERT INTO T91 VALUES(1, '刘亦非', NULL);
INSERT INTO T91 VALUES(2, '王刚', NULL);
INSERT INTO T91 VALUES(3, '张大迁', NULL);
-- 测试表,存储每个汉字的拼音
CREATE TABLE AnounceDict
(
ID NUMBER(4),
Leter VARCHAR2(20),
LeterAnounce VARCHAR2(20)
)
INSERT INTO AnounceDict VALUES(1, '刘', 'liu');
INSERT INTO AnounceDict VALUES(2, '张', 'zhang');
INSERT INTO AnounceDict VALUES(3, '王', 'wang');
INSERT INTO AnounceDict VALUES(4, '亦', 'ying');
INSERT INTO AnounceDict VALUES(5, '刚', 'gang');
INSERT INTO AnounceDict VALUES(6, '大', 'da');
INSERT INTO AnounceDict VALUES(7, '非', 'fei');
INSERT INTO AnounceDict VALUES(8, '迁', 'qian');
-- 一定要commit
COMMIT;
下面是建立存储过程CREATE OR REPLACE PROCEDURE ProcedureGetAnounce
IS
CURSOR curT91 IS SELECT NAME FROM T91;
anounceResult VARCHAR2(50);
strName VARCHAR2(20);
nLength NUMBER(4);
strLeter VARCHAR2(5);
strPartAnounce VARCHAR2(20);
BEGIN
FOR r IN curT91 LOOP
anounceResult := '';
strName := r.NAME;
nLength := length(strName);
FOR i IN 1..nLength LOOP
strLeter := substr(strName, i, 1);
SELECT LeterAnounce INTO strPartAnounce
FROM AnounceDict WHERE Leter = strLeter;
anounceResult := anounceResult || strPartAnounce;
END LOOP;
UPDATE T91 SET Anounce = anounceResult WHERE NAME = strName;
END LOOP;
COMMIT;
END ProcedureGetAnounce;
执行存储过程:begin
-- Call the procedure
proceduregetanounce;
end;
查看存储过程执行后,表中的数据