我试图获取模式中所有表的记录计数。我无法编写PL / sql。这是我到目前为止所做的，但我收到错误。请建议任何更改：
- DECLARE
- v_owner varchar2(40);
- v_table_name varchar2(40);
- cursor get_tables is
- select distinct table_name,user
- from user_tables
- where lower(user) = 'SCHEMA_NAME';
- begin
- open get_tables;
- fetch get_tables into v_table_name,v_owner;
- INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
- SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM v_table_name;
- CLOSE get_tables;
- END;
这应该做到：
- declare
- v_count integer;
- begin
- for r in (select table_name,owner from all_tables
- where owner = 'SCHEMA_NAME')
- loop
- execute immediate 'select count(*) from ' || r.table_name
- into v_count;
- INSERT INTO STATS_TABLE(TABLE_NAME,CREATED)
- VALUES (r.table_name,r.owner,v_count,SYSDATE);
- end loop;
- end;
