17,086
社区成员
发帖
与我相关
我的任务
分享
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)
AS
V_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
--根据表名查询出表中所有列名 表名都是大写
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'SYSMODEL' ORDER BY COLUMN_ID