ORA-00903、ORA-06512，同时计算所有用户表的行数(动态 sql)

Question

我想计算每个用户表的行数，但是通过动态sql，并且存在这样的错误。ORA-00903：表名无效ORA-06512：在第 19 行 SQL2.sql 20 2

DECLARE 
      TYPE cursor_type IS REF CURSOR;
      curs1 cursor_type;
      ammount NUMBER;
      stmnt1 VARCHAR2(200);
      stmnt2 VARCHAR2(200);
      T_NAME VARCHAR2(100);
    BEGIN 
      stmnt1 := 'SELECT TABLE_NAME FROM USER_TABLES';
      stmnt2 := 'SELECT COUNT(*) FROM :1';
      IF curs1%ISOPEN THEN
        CLOSE curs1;
      END IF;
      OPEN curs1 FOR stmnt1;
      LOOP
        FETCH curs1 INTO T_NAME;
        EXIT WHEN curs1%NOTFOUND; 
        EXECUTE IMMEDIATE stmnt2 INTO ammount USING T_NAME;
        DBMS_OUTPUT.PUT_LINE('Table ' || T_NAME || ' - ' || ammount || ' rows');
      END LOOP;
      CLOSE curs1;
    END;

Answer 1

不能将表名作为绑定变量提供。您必须将其连接到语句中：

DECLARE
  TYPE cursor_type IS REF CURSOR;
  curs1   cursor_type;
  amount NUMBER;
  stmnt1  VARCHAR2(200);
  stmnt2  VARCHAR2(200);
  T_NAME  VARCHAR2(100);
BEGIN
  stmnt1 := 'SELECT TABLE_NAME FROM USER_TABLES';
  stmnt2 := 'SELECT COUNT(*) FROM ';
  IF curs1%ISOPEN THEN
    CLOSE curs1;
  END IF;
  OPEN curs1 FOR stmnt1;
  LOOP
    FETCH curs1
      INTO T_NAME;
    EXIT WHEN curs1%NOTFOUND;
    EXECUTE IMMEDIATE stmnt2 || t_name
      INTO amount;      
    DBMS_OUTPUT.PUT_LINE('Table ' || T_NAME || ' - ' || amount || ' rows');
  END LOOP;
  CLOSE curs1;
END;

更新一个更简单（恕我直言，更具可读性）的版本，使用光标循环：

declare
  amount number;
begin
  for cur in (select table_name from user_tables) 
  loop
    execute immediate 'SELECT COUNT(*) FROM ' || cur.table_name
      into amount;
    DBMS_OUTPUT.PUT_LINE('Table ' || cur.table_name || ' - ' || amount ||
                         ' rows');
  end loop;
end;