数据仓库--postgresql for 循环记录完成存储过程总调度-CSDN博客

本文链接：https://blog.csdn.net/Angelina7/article/details/78121328

本文介绍如何在PostgreSQL中创建一个存储过程来批量调用其他五个存储过程，并通过for循环和动态SQL实现对任务状态的检查及更新。针对Oracle与PostgreSQL之间的差异，解决了select into与执行存储过程的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

		
业务需求：需要创建一个存储过程调度五个存储过程，思路使用FOR循环获取表名，由于存储过程是P_加表名，定义一个变量获取存储过程名。
执行存储过程必须判断task表里执行的状态，如果是0则执行存储过程，并更新task里的状态和执行时间。


在此遇见两个问题：oracle和POSTSQL还是有很大区别的
遇见一个错误：
1、在执行select into SQL语句的时候会报错，解决方法：先定义一个SQL语句，然后使用动态SQL执行EXECUTE SQL语句。
cannot EXECUTE a null querystring
2、执行存储过程，使用PERFORM执行存储过程。
补充：SQL语句执行存储过程 select P_ALL_tables()/*存储过程名*/;


dis_task_info 表记录一个执行存储过程（CDS层表）是否成功的记录和每一个子任务（对应的ODS层的表）
syscd          taskcd                 tabnm          taskst   taskdt     starttime                   endtime                         errmsg
DW-ODS-1	ODS-1-E-CDS_P_WITHDRAW	CDS_P_WITHDRAW		0	20170926	2017-09-27 20:56:33.219061	2017-09-27 20:56:33.219061	 ------CDS层表
（其实是相当于是日志表、不过对历史数据不进行留痕）

创建存储过程：


CREATE OR REPLACE FUNCTION dis.P_ALL_tables() RETURNS void AS
  $$          declare l_taskst text; ----获取任务状态
  l_tab_name  text; ---获取表名
  l_prc_name  text; ---获取存储过程名
  l_sql       text; ---执行脚本获取任务状态
  l_sql1      text; ---执行脚本获取ETL时间
  tab_cur     record; ----定义记录类型
  l_taskdt    text; ---获取昨日时间
  l_starttime TIMESTAMP WITHOUT TIME ZONE; ---定义任务开始时间
  l_endtime   TIMESTAMP WITHOUT TIME ZONE; ----定义任务结束时间
  l_sqlerrm text ;------获取错误信息
BEGIN

	----for 循环
  for tab_cur in (select 'CDS_S_TAB_NAME1' table_name----需要写需要的表名
				union all
				select 'CDS_S_TAB_NAME2' table_name
				union all 
				select 'CDS_S_TAB_NAME3' table_name
				union all
				select 'CDS_S_TAB_NAME4'table_name 
				union all
				select 'CDS_S_TAB_NAME5' table_name) loop
    l_tab_name := tab_cur.table_name;
    l_prc_name := 'cds.P_' || tab_cur.table_name || '()';
    l_sql      := 'SELECT max(t.taskst)  FROM  dis.dis_task_info t  where tabnm=''' || l_tab_name || '''';
    EXECUTE l_sql
      into l_taskst;
    l_sql1 := 'select to_char(to_date (max(taskdt),''yyyymmdd'')+1,''yyyymmdd'') from dis.dis_task_info where tabnm=''' || l_tab_name || '''';
    EXECUTE l_sql1 into l_taskdt;
    IF l_taskst in ('0') THEN
      l_starttime := now();
	  ---执行存储过程
      PERFORM l_prc_name;
      l_endtime := now();
	  ----更新执行状态、时间
      EXECUTE 'update dis_task_info set taskst=0,starttime=''' || l_starttime || ''',endtime=''' || l_endtime || ''' ,taskdt=''' || l_taskdt || ''' where tabnm=''' || l_tab_name || '''';
    else
      return;
    END IF;
  end loop;
EXCEPTION
  WHEN others THEN
  l_sqlerrm :=SQLERRM;---获取错误
    EXECUTE 'update dis_task_info set taskst=2,,starttime=''' || l_starttime || ''',endtime=''' || l_endtime || ''',errmsg=''' || l_sqlerrm || '''  where tabnm=''' || l_tab_name || '''where tabnm=''' || l_tab_name || '''';
    RAISE EXCEPTION '(%)', SQLERRM;
  
END $$
  LANGUAGE PLPGSQL;