万能动态SQL

CREATE OR REPLACE PROCEDURE p_test_renbing(i_from_table in varchar2,i_to_table in varchar2)
IS
V_COUNT NUMBER(10);
V_RESULT NUMBER(19,2);
V_COLUMN VARCHAR2(60);
IS_TABLE NUMBER;
BEGIN
  SELECT COUNT(*) INTO IS_TABLE FROM(SELECT table_name FROM user_tables t where t.TABLE_NAME=''||i_to_table||'');
  IF IS_TABLE>0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE '||i_to_table||' ';
    EXECUTE IMMEDIATE 'CREATE TABLE '||i_to_table||' (ID NUMBER(19,2) )';
  ELSE
    EXECUTE IMMEDIATE 'CREATE TABLE '||i_to_table||' (ID NUMBER(19,2) )';
  END IF;
  EXECUTE IMMEDIATE 'INSERT INTO '||i_to_table||'(ID) VALUES(1)';  --初始化
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (SELECT ACCOUNTNAME FROM '||i_from_table||' )' INTO V_COUNT;
  FOR COLUMN_ARRAY IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = ''||i_from_table||'' ORDER BY COLUMN_NAME)
  LOOP
    V_COLUMN := COLUMN_ARRAY.COLUMN_NAME;
    EXECUTE IMMEDIATE 'ALTER TABLE '||i_to_table||' ADD('||V_COLUMN||' NUMBER(19,2))';
    --处理逻辑
    
    EXECUTE IMMEDIATE 'SELECT (SELECT COUNT(*) FROM '||i_from_table||' WHERE '||V_COLUMN||' IS NOT NULL)*100/'||V_COUNT||' FROM DUAL'
                      into V_RESULT
                       ;
    EXECUTE IMMEDIATE 'UPDATE '||i_to_table||' SET '||V_COLUMN||'='||V_RESULT||'';
  END LOOP;
  COMMIT;
END p_test_renbing;