Oracle存储过程使用游标，循环，和执行 EXECUTE IMMEDIATE 赋值变量用法

最新推荐文章于 2022-05-27 14:06:57 发布

盈欢

最新推荐文章于 2022-05-27 14:06:57 发布

阅读量1.7k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/weixin_44421196/article/details/108347240

版权

Oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一. 需求：每天循环更新表的数据量

create or replace PROCEDURE TEST 
/*
作者：盈欢
时间：2020/08/21
*/

AS 

COUNTS  NUMBER ;
SQLMSG  VARCHAR2(100);

	CURSOR COR_LIST_STG IS SELECT TABLE_NAME FROM ALL_TABLES  WHERE OWNER ='STG';
	CURSOR COR_LIST_ODS IS SELECT TABLE_NAME FROM ALL_TABLES  WHERE OWNER ='ODS';

BEGIN 
		
	/*
	STG表更新数据量
	*/
	FOR EACH_TAB IN COR_LIST_STG

	LOOP
	
  SQLMSG:='SELECT COUNT(1)  FROM STG.'|| EACH_TAB.TABLE_NAME;
	EXECUTE IMMEDIATE SQLMSG INTO COUNTS;
	COMMIT;

  UPDATE AUDIT_TABLE_SUMMARY SET TABCOUNT=COUNTS WHERE TABNAME=EACH_TAB.TABLE_NAME;
  
  COMMIT;
	
	END LOOP ;
	
	/*
	ODS表更新数据量
	*/
	
	FOR EACH_TAB IN COR_LIST_ODS

	LOOP
	
  SQLMSG:='SELECT COUNT(1) FROM ODS.'|| EACH_TAB.TABLE_NAME;
	EXECUTE IMMEDIATE SQLMSG INTO COUNTS;
	COMMIT;

  UPDATE AUDIT_TABLE_SUMMARY SET TABCOUNT=COUNTS WHERE TABNAME=EACH_TAB.TABLE_NAME;
  
  COMMIT;
	
  END LOOP ;	
	

END ;