在program 窗口中执行了一条update语句,发现了好几个错误,还以为update语句不能这样写,结果用过程块重写了,用一种更笨的方法重复了工作~
表:distin_file_name 新增了一列,值为空,现在要将表:local_prom_file 里的path值对应填充到表:distin_file_name 里, 两个表的字段如下:
表:distin_file_name
FILE_NAME PATH
a
b
表:local_prom_file
PROM_ID LASTCHG PATH FILE_NAME
1 2010-12-22 D:/.. a
2 2010-12-23 D:/.. a
3 2010-12-24 D:/.. a
4 2010-12-25 D:/.. a
实现代码如下:
UPDATE distin_file_name dfn SET path =
( SELECT path
FROM local_prom_file
WHERE local_prom_file.file_name LIKE '%'||dfn.file_name||'%'
AND ROWNUM<2 )
过程块实现如下:
DECLARE
CURSOR add_path IS
SELECT file_name FROM distin_file_name;
all_record add_path%ROWTYPE;
v_path VARCHAR2(200);
BEGIN
FOR a IN add_path
LOOP
BEGIN
SELECT path
INTO v_path
FROM local_prom_file
WHERE local_prom_file.file_name LIKE '%'||a.file_name||'%'
AND ROWNUM<2;
END;
BEGIN
UPDATE distin_file_name SET path = v_path
WHERE distin_file_name.file_name = a.file_name;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
END LOOP;
END;
在增加path这一列时就应该给它赋值:
modify distin_file_name dfn add path varchar2(200) defaule (
SELECT path
FROM local_prom_file
WHERE local_prom_file.file_name LIKE '%'||dfn.file_name||'%'
AND ROWNUM<2 )
这样可以吧,
这样不行,报错: ORA-22818:SUBQUERY EXPRESSIONS NOT ALLOWED HERE