Oracle 问题

Question

     给出数据表名，根据表名查询出列名，根据列的数据类型插入为空的数据，比如 int 插入 0  VARCHAR2插入“”等等
  求个SQL 怎么解决

Answer 1

--根据表名查询出表中所有列名  表名都是大写
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'SYSMODEL' ORDER BY COLUMN_ID
插入默认值就行了

Answer 2

  哈哈  我不会玩ORACLE 才问  MSSQL 我就会 也不会来问

Answer 3

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 
 

Answer 4

其实就是根据字段类型插入信息，我不会这ORACLE才在这里问问

Answer 5

   求个动态INSERT的存储过程

Answer 6

  动态插入的方法，insert