DECLARE
v_sql VARCHAR2(4000);
v_cnt INTEGER;
TYPE col_type IS TABLE OF col%ROWTYPE INDEX BY BINARY_INTEGER;
v_col col_type;
CURSOR cour IS SELECT * FROM col WHERE tname=upper('execlog');
BEGIN
OPEN cour;
FETCH cour BULK COLLECT INTO v_col;
CLOSE cour;
FOR i IN 1..v_col.COUNT LOOP
v_sql:='update execlog set '||v_col(i).cname||'=''NULL'' where '||v_col(i).cname||' is null';
EXECUTE IMMEDIATE v_sql;
COMMIT;
dbms_output.put_line(v_sql);
END LOOP;
END;
v_sql VARCHAR2(4000);
v_cnt INTEGER;
TYPE col_type IS TABLE OF col%ROWTYPE INDEX BY BINARY_INTEGER;
v_col col_type;
CURSOR cour IS SELECT * FROM col WHERE tname=upper('execlog');
BEGIN
OPEN cour;
FETCH cour BULK COLLECT INTO v_col;
CLOSE cour;
FOR i IN 1..v_col.COUNT LOOP
v_sql:='update execlog set '||v_col(i).cname||'=''NULL'' where '||v_col(i).cname||' is null';
EXECUTE IMMEDIATE v_sql;
COMMIT;
dbms_output.put_line(v_sql);
END LOOP;
END;
解决方案 »
- 如何提高SQL的效率,请教!
- PROC中ORACLE数据库的游标以及CHAR型数据查询出来后的具体内容
- 250分 求解自定义聚合函数的问题,谁解决全送谁!
- 急需一 select 写法 急!!!!!!!!!!!!!
- 一条非常简单的SQL,难住我了!!救救我啊
- replace 问题 !!!
- 此SQL有什么问题?
- dba认证方式??
- 急!中文字符无法插入Oracle数据库
- 数据库方案DBLink,迁移
- 这个update语句怎么在oracle中实现? 急啊! -_-!
- 在SQL Server 中可以顺利执行的 update set from where 语句到Oracle中执行不了。Oracle中相应语句怎么写?
你的代码不行 而且写错了
DECLARE
v_sql VARCHAR2(4000);
v_cnt INTEGER;
TYPE col_type IS TABLE OF col%ROWTYPE INDEX BY BINARY_INTEGER;
v_col col_type;
CURSOR cour IS SELECT * FROM col WHERE tname=upper('teacher');
BEGIN
OPEN cour;
FOR i IN 1..v_col.COUNT LOOP
FETCH cour BULK COLLECT INTO v_col;
v_sql:='update teacher set '||v_col(i).cname||'=''NULL'' where '||v_col(i).cname||' is null';
EXECUTE IMMEDIATE v_sql;
COMMIT;
dbms_output.put_line(v_col(i).cname);
END LOOP;
CLOSE cour;
END;