该楼层疑似违规已被系统折叠 隐藏此楼查看此楼
CREATE OR REPLACE FUNCTION p_dw_dmi_user_d_change_or_new() RETURNS void AS$BODY$-- *****************************************************************-- * (日)-- *****************************************************************declare V_PKG VARCHAR(40); V_PROCNAME VARCHAR(40); V_DATE VARCHAR(8); V_ACCT_MONTH VARCHAR(6); V_DAY VARCHAR(2); V_DAY2 VARCHAR(2); begin V_PKG := 'DW'; V_PROCNAME:='P_DW_DMI_USER_D_CHANGE_OR_NEW'; V_DATE:=to_char(current_date-1,'YYYYMMDD'); --日志部分 perform P_INSERT_LOG(V_DATE,V_PKG, V_PROCNAME, '12', to_char(date_trunc('second', to_timestamp(timeofday(),'Dy Mon DD HH24:MI:SS.US YYYY')),'YYYY-MM-DD HH24:MI:SS'));
V_ACCT_MONTH := SUBSTR(V_DATE,1,6); V_DAY:=SUBSTR(V_DATE,7,2); V_DAY2:=SUBSTR(to_char(current_date-2,'YYYYMMDD'),7,2) begin; savepoint tt; --对于在 V_DAY2 有记录、V_DAY没记录的用户也插入insert into dw_dmi_user_d_changeselect a.*from (select * from dw_dmi_user_d where month_id=V_ACCT_MONTH and day_id=V_DAY2) a where not exists (select * from (select * from dw_dmi_user_d where month_id=V_ACCT_MONTH and day_id=V_DAY) b where b.user_no=a.user_no); perform P_UPDATE_LOG(V_DATE,V_PKG,V_PROCNAME,'SUCCESS',to_char(date_trunc('second', to_timestamp(timeofday(),'Dy Mon DD HH24:MI:SS.US YYYY')),'YYYY-MM-DD HH24:MI:SS'),'完成'); -- EXECUTE sql; EXCEPTION WHEN OTHERS THEN ROLLBACK to tt; perform P_UPDATE_LOG(V_DATE,V_PKG,V_PROCNAME,'FAIL',to_char(date_trunc('second', to_timestamp(timeofday(),'Dy Mon DD HH24:MI:SS.US YYYY')),'YYYY-MM-DD HH24:MI:SS'),SQLERRM); commit; end;
$BODY$ LANGUAGE plpgsql VOLATILE;
该过程可以顺利建立,当时在执行的时候报错
不知道那位大侠可以帮帮我!