有如这样的一些数据需要入库,如
001/002/003,
001/002,
001,
003/002/001
还有一个数据字典,比如,001代表北京,002代表南京,003代表西京,
我想通过上面的代码获得中文,以/分割后获得每个代码所代表的中文,写入到数据库中,下面是我写的过程,但不能执行,
貌似语法还有些不对,征求各方面帮助,我自己也在看,谢谢我存储过程的意思,是将有/的数据写入到临时表,然后从临时表里在进行操作
CREATE OR REPLACE PROCEDURE PCR_CODE
IS
ROW_COUNT NUMBER(10);
STRINDEX NUMBER(3);
NAME VARCHAR(50);
BEGIN
CREATE TABLE TEMP_YUE AS
SELECT ZT_ZTLX_CODE ZTLX
FROM TEST_ZTRY
WHERE INSTR(ZT_ZTLX_CODE,'/')!=0;
ROW_COUNT= SELECT COUNT(*) FROM TEMP_YUE;
IF(ROW_COUNT>0) THEN
BEGIN
STRINDEX:=0;
NAME:=SELECT NAME FROM DIC_ZTLX,TEMP_YUE WHERE SUBSTR(ZTLX,1,6)=DIC_ZTLX.CODE;
STRINDEX=:STRINDEX+1;
WHILE((SELECT INSTR(ZTLX,'/',1,_INDEX+1) FROM TEMP_YUE)>0) LOOP
BEGIN
NAME:=NAME||'/'||SELECT NAME FROM DIC_ZTLX,TEMP_YUE WHERE SUBSTR(ZTLX,@_INDEX*7+1,6)=DIC_ZTLX.CODE;
STRINDEX:=STRINDEX+1;
END;
END LOOP;
END;
END IF;
UPDATE TEMP_YUE SET ZTLX=NAME;
SELECT * FROM TEMP_YUE;
END PCR_CODE;
谢谢,
001/002/003,
001/002,
001,
003/002/001
还有一个数据字典,比如,001代表北京,002代表南京,003代表西京,
我想通过上面的代码获得中文,以/分割后获得每个代码所代表的中文,写入到数据库中,下面是我写的过程,但不能执行,
貌似语法还有些不对,征求各方面帮助,我自己也在看,谢谢我存储过程的意思,是将有/的数据写入到临时表,然后从临时表里在进行操作
CREATE OR REPLACE PROCEDURE PCR_CODE
IS
ROW_COUNT NUMBER(10);
STRINDEX NUMBER(3);
NAME VARCHAR(50);
BEGIN
CREATE TABLE TEMP_YUE AS
SELECT ZT_ZTLX_CODE ZTLX
FROM TEST_ZTRY
WHERE INSTR(ZT_ZTLX_CODE,'/')!=0;
ROW_COUNT= SELECT COUNT(*) FROM TEMP_YUE;
IF(ROW_COUNT>0) THEN
BEGIN
STRINDEX:=0;
NAME:=SELECT NAME FROM DIC_ZTLX,TEMP_YUE WHERE SUBSTR(ZTLX,1,6)=DIC_ZTLX.CODE;
STRINDEX=:STRINDEX+1;
WHILE((SELECT INSTR(ZTLX,'/',1,_INDEX+1) FROM TEMP_YUE)>0) LOOP
BEGIN
NAME:=NAME||'/'||SELECT NAME FROM DIC_ZTLX,TEMP_YUE WHERE SUBSTR(ZTLX,@_INDEX*7+1,6)=DIC_ZTLX.CODE;
STRINDEX:=STRINDEX+1;
END;
END LOOP;
END;
END IF;
UPDATE TEMP_YUE SET ZTLX=NAME;
SELECT * FROM TEMP_YUE;
END PCR_CODE;
谢谢,
我简化成下面这样 ,可是还有个问题 就是说如果
数据 表A
001/005/003
001/002
001
字典表B
001 张三
002 李四
004王五我在 表A里有数据的代码 我想加个冗余列进去
张三/李四/王五
张三/李四
张三
我用下面的代码
UPDATE TEMP_YUE SET NAME=(SELECT NAME FROM DIC_ZTLX WHERE SUBSTR(TEMP_YUE.ZTLX,1,3)=DIC_ZTLX.CODE)UPDATE TEMP_YUE SET NAME=NAME||'\'||(SELECT NAME FROM DIC_ZTLX WHERE SUBSTR(TEMP_YUE.ZTLX,5,3)=DIC_ZTLX.CODE and instr(TEMP_YUE.ZTLX,'\',1,1)>0)UPDATE TEMP_YUE SET NAME=NAME||'\'||(SELECT NAME FROM DIC_ZTLX WHERE SUBSTR(TEMP_YUE.ZTLX,9,3)=DIC_ZTLX.CODE and instr(TEMP_YUE.ZTLX,'\',5,1)>0)
这样可是读出数据,可是是这么个样子
张三/李四/王五
张三/李四/
张三//
给个好的解决法子
如 str:='select * from tb where name='张三''
张三这该如何写?单引号里面又用到了单引号。。
a :='张三';
str:='select * from tb where name='||a
sql_command varchar2(500);begin
sql_command :='''A''';
sql_command := 'select * from test where name= ' || sql_command;
dbms_output.put_line(sql_command);
end;
当然也可以用存储过程,不过需要用游标,速度会有影响另外临时表不是象你这样建的,你这样建的表,只是名字里有temp而已,数据会一直保留的,而且会有并发的问题,你去搜索下临时表的建法,临时表可以事先建好的,然后存储过程调用,可以做到每个进程互不干扰,而且COMMIT后数据就会自动没的其实你的update语句可以一句SQL出来的,就是复杂点,我想想看
10G的话,你可以这样
假设A表字段
id_string id_name(你想更新的名字)
001/002/003
001/002
001
003/002/001 字典表 b
code_id code_name
001 北京
002 南京
003 西京写个函数CREATE OR REPLACE FUNCTION fn_get_name (in_string VARCHAR2)
RETURN VARCHAR2
IS
out_name VARCHAR2 (200);
BEGIN
SELECT REPLACE (wmsys.wm_concat (b.code_name), ',', '/')
INTO out_name
FROM b,
(SELECT DECODE (LENGTH (:in_string),
3, :in_string,
SUBSTR (:in_string,
DECODE (ROWNUM,
1, 1,
INSTR (:in_string,
'/',
1,
ROWNUM - 1
)
+ 1
),
3
)
) code_id
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (:in_string)
- LENGTH (REPLACE (:in_string, '/', ''))
+ 1) c
WHERE b.code_id = c.code_id; RETURN out_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
NULL;
END fn_get_name;然后用update 调用此函数update a
set id_name=fn_get_name(id_string)
函数这样写CREATE OR REPLACE FUNCTION fn_get_name (in_string VARCHAR2)
RETURN VARCHAR2
IS
out_name VARCHAR2 (200);
BEGIN
SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (code_name, '/')), 2)
INTO out_name
FROM (SELECT ROWNUM rn, b.code_name
FROM b,
(SELECT DECODE
(LENGTH (:in_string),
3, :in_string,
SUBSTR (:in_string,
DECODE (ROWNUM,
1, 1,
INSTR (:in_string,
'/',
1,
ROWNUM - 1
)
+ 1
),
3
)
) code_id
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (:in_string)
- LENGTH (REPLACE (:in_string, '/', ''))
+ 1) c
WHERE b.code_id = c.code_id)
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn; RETURN out_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
NULL;
END fn_get_name;
比
where length(TEMP_YUE.ZTLX)-length(replace(TEMP_YUE.ZTLX),'/')=2表示是001/002/003的数据