EXECUTE IMMEDIATE into的使用

最新推荐文章于 2022-06-10 11:23:36 发布

转载最新推荐文章于 2022-06-10 11:23:36 发布 · 2.7k 阅读

标签

#数据库

收录于

本文提供了一个PL/SQL脚本示例，用于分析指定模式中带有CREATE_TIMESTAMP字段的所有表，并找出当天有更新记录的表。

Use "EXECUTE IMMEDIATE into" to analyze which tables being updated[@more@]

DECLARE

v_table_name varchar2(30);
v_count number;
v_rows number;
v_sql_statement VARCHAR2(300);

CURSOR c1 IS select table_name from dba_tab_columns where owner='Schema_owner' and column_name='CREATE_TIMESTAMP' order by 1;

BEGIN

OPEN c1; -- open the cursor before fetching
LOOP
FETCH c1 INTO v_table_name;
EXIT WHEN c1%NOTFOUND;

v_sql_statement := 'select count(*) from Schema_owner .' || v_table_name || ' where trunc(create_timestamp)=trunc(sysdate)' ;

EXECUTE IMMEDIATE v_sql_statement into v_rows;

IF v_rows > 0 THEN
DBMS_OUTPUT.PUT_LINE( 'Table' || ': ' || v_table_name );
end if;

END LOOP;
CLOSE c1;

END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14377/viewspace-1029624/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14377/viewspace-1029624/