Oracle 问题
给出数据表名，根据表名查询出列名，根据列的数据类型插入为空的数据，比如 int 插入 0 VARCHAR2插入“”等等
求个SQL 怎么解决
[解决办法]
CREATE TABLE T(ID INT,XH INT,XM VARCHAR2(10));INSERT INTO T VALUES (1,NULL,'A');INSERT INTO T VALUES (2,2,'B');INSERT INTO T VALUES (3,NULL,NULL);INSERT INTO T VALUES (4,4,NULL);INSERT INTO T VALUES (5,NULL,NULL);COMMIT;CREATE OR REPLACE PROCEDURE PRO_UPDATE_TEST(I_TABLE_NAME VARCHAR2)ASV_UPDATE_SQL VARCHAR2(4000);BEGIN V_UPDATE_SQL := 'UPDATE '||I_TABLE_NAME||' SET '; FOR I IN (SELECT T.COLUMN_NAME,T.DATA_TYPE FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='T') LOOP V_UPDATE_SQL := V_UPDATE_SQL||' '||I.COLUMN_NAME||'=NVL('||I.COLUMN_NAME||','; case i.data_type when 'NUMBER' THEN v_update_sql := v_update_sql||'0),'; when 'INT' THEN v_update_sql := v_update_sql||'0),'; when 'INTEGER' THEN v_update_sql := v_update_sql||'0),'; when 'VARCHAR2' THEN V_UPDATE_SQL:= V_UPDATE_SQL||'''''),'; ELSE V_UPDATE_SQL:= V_UPDATE_SQL||'NULL),'; END CASE; END LOOP; V_UPDATE_SQL:= TRIM(',' FROM V_UPDATE_SQL); DBMS_OUTPUT.put_line(V_UPDATE_SQL); EXECUTE IMMEDIATE V_UPDATE_SQL; COMMIT;END;SQL> SELECT * FROM T; ID XH XM--------------------------------------- --------------------------------------- ---------- 1 A 2 2 B 3 4 4 5 SQL> EXEC PRO_UPDATE_TEST('T'); PL/SQL procedure successfully completed SQL> SELECT * FROM T; ID XH XM--------------------------------------- --------------------------------------- ---------- 1 0 A 2 2 B 3 0 4 4 5 0