1.cron服务来设置
计划任务查看与设置命令：crontab
包括条目：
分钟m：0-59
小时h：0-23
月日dom：1-31
月份mon：1-12
星期dow：0-7
例子：
每隔2小时处理一个文件用：*/2
1,3,5,7,9 点间隔执行
0-4,8-12点执行
或者是*/2每隔2个小时备份文件或执行数据库操作
下面是工作中用到的sell脚本：
0 1 * * * /home/oracle/psql-jobs.sh */1 * * * * /usr/bin/curl http://127.0.0.1:60030/admin/serpro/qianzhangcron >/dev/null 2>&1
0 5 * * * /home/oracle/xbin-1.0.0/autolog.sh
#password stored in ~/.pgpass #psql -w -U acc_user -h localhost -d Factoring -c "select acc_user.p_loop_count_sheet()" psql -w -U acc_user Factoring -c "select acc_user.p_loop_count_sheet()"
案例：输入命令 打开任务编辑器
crontab -e
可以看到
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
# For more information see the manual pages of crontab(5) and cron(8)
# m h dom mon dow command 这样的格式
我们每5分钟把 /home/myfile 路径下的文件打包一次放到 /home/rar 目录下
我们就可以这样写：
*/5 * * * * tar czf /home/rar/myfile.tgz /home/myfile/*
然后重启服务：
daokr@DK:~/rar$ sudo service cron restart
[sudo] daokr 的密码：
利用stat 命令查看文件时间更新时间看是否已经执行成功
daokr@DK:~/rar$ stat myfile.tgz 文件：'myfile.tgz' 大小：128 块：8 IO 块：4096 普通文件 设备：801h/2049d Inode：530109 硬链接：1 权限：(0664/-rw-rw-r--) Uid：( 1000/ daokr) Gid：( 1000/ daokr) 最近访问：2018-03-29 23:15:21.837168254 +0800 最近更改：2018-03-29 23:17:01.897642845 +0800 最近改动：2018-03-29 23:17:01.897642845 +0800
crontab -l 查看任务
crontab -r 删除任务
工作中用到的计划任务
CREATE OR REPLACE FUNCTION "acc_user"."p_loop_count_sheet"(inccy varchar='CNY'::character varying) RETURNS "pg_catalog"."void" AS $BODY$ declare --v_ccy VARCHAR(100); --v_rate VARCHAR(100); v_code numeric; v_errm VARCHAR(256); v_count VARCHAR(1); v_s_count VARCHAR(20); v_a_count VARCHAR(20); v_b_count VARCHAR(20); v_c_count VARCHAR(20); v_d_count VARCHAR(20); BEGIN raise notice 'begin call p_count_sheet1'; INSERT INTO t_errors (code, MESSAGE, PRO_NAME, RUN_DATE) VALUES (0, 'run', 'p_loop_count_sheet', now()); perform p_count_sheet1('CNY','1'); raise notice 'begin insert into acc_user.t_acc_general_ledger_ac_his'; insert into acc_user.t_acc_general_ledger_ac_his (br_no, gl_code, d_curr_amt, c_curr_amt, d_count, c_count) WITH path1 AS (SELECT ent_id as agent_id, parent_ent_id as parent_id, SYS_CONNECT_BY_PATH(ent_id, ',') p_child FROM crm_user.t_enterprise CONNECT BY ent_id = PRIOR parent_ent_id), allpath AS (SELECT agent_id, SUBSTR(string_agg(p_child,','), 1, 4000) all_c_p FROM path1 GROUP BY agent_id), trade1 AS (SELECT BR_NO, GL_CODE, AC_DATE, d_curr_amt, c_curr_amt, d_count, c_count FROM acc_user.t_acc_general_ledger_his_temp WHERE (ac_date) = trunc(now() - 1)) SELECT allpath.agent_id, trade1.GL_CODE, SUM(DECODE(instr(all_c_p, trade1.BR_NO),0,0,nvl(d_curr_amt, 0))) d_curr_amt_sum, SUM(DECODE(instr(all_c_p, trade1.BR_NO),0,0,nvl(c_curr_amt, 0))) c_curr_amt_sum, SUM(DECODE(instr(all_c_p, trade1.BR_NO),0,0,nvl(d_count, 0))) d_count_sum, SUM(DECODE(instr(all_c_p, trade1.BR_NO),0,0,nvl(c_count, 0))) c_count_sum FROM allpath inner join trade1 on instr(all_c_p, trade1.BR_NO) > 0 GROUP BY allpath.agent_id, trade1.GL_CODE; update t_acc_general_ledger_ac_his set seq_no = t_acc_general_ledger_ac_his_seq_no_seq.nextval, ccy = INCCY, ac_date = trunc(now() - 1) where ac_date is null; select count(*) into v_s_count from (select br_no, sum(nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0)) s_d, sum(nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0)) s_c from t_acc_general_ledger_ac_his where ac_date = trunc(now() - 2) group by br_no) where s_d != s_c; raise notice 'v_s_count: %', v_s_count; if (v_s_count = '0') then raise notice 'begin update t_acc_general_ledger_ac_his -79'; update t_acc_general_ledger_ac_his t2 set t2.d_prev_bal = (with kk as (select br_no, gl_code, ac_date, lag(d_curr_bal::bigint, 1,0::bigint) over(partition by br_no, gl_code order by ac_date) d_prev_bal_next from t_acc_general_ledger_ac_his) select kk.d_prev_bal_next from kk where kk.br_no = t2.br_no and kk.gl_code = t2.gl_code and kk.ac_date = t2.ac_date) where ac_date = trunc(now() - 1); raise notice 'begin update t_acc_general_ledger_ac_his -95'; update t_acc_general_ledger_ac_his t2 set t2.c_prev_bal = (with kk as (select br_no, gl_code, ac_date, lag(c_curr_bal::bigint, 1,0::bigint) over(partition by br_no, gl_code order by ac_date) c_prev_bal_next from t_acc_general_ledger_ac_his) select kk.c_prev_bal_next from kk where kk.br_no = t2.br_no and kk.gl_code = t2.gl_code and kk.ac_date = t2.ac_date) where ac_date = trunc(now() - 1); raise notice 'begin update t_acc_general_ledger_ac_his -111'; update t_acc_general_ledger_ac_his t2 set d_curr_bal = (with kk as (SELECT br_no, t1.gl_code, debit_credit, t1.ac_date, CASE WHEN debit_credit = 'D' THEN nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) WHEN debit_credit = 'C' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) WHEN debit_credit = 'B' AND nvl(d_prev_bal, 0) = 0 and nvl(c_prev_bal, 0) = 0 and (d_curr_amt - c_curr_amt) > 0 THEN abs(nvl(d_curr_amt,0) - nvl(c_curr_amt,0)) when debit_credit = 'B' and nvl(d_prev_bal, 0) > 0 and (d_prev_bal + d_curr_amt - nvl(c_curr_amt, 0)) > 0 then abs(d_prev_bal + d_curr_amt - nvl(c_curr_amt, 0)) when debit_credit = 'B' and nvl(c_prev_bal, 0) > 0 and (c_prev_bal + c_curr_amt - nvl(d_curr_amt, 0)) < 0 then abs(c_prev_bal + c_curr_amt - nvl(d_curr_amt, 0)) --ELSE abs(c_prev_bal+c_curr_amt-nvl(d_curr_amt,d_curr_amt,0)) END curr_bal FROM t_acc_general_ledger_ac_his t1, t_acc_subject WHERE t1.gl_code = t_acc_subject.gl_code and ac_date = trunc(now() - 1)) select decode(kk.debit_credit, 'D', kk.curr_bal, 'B', kk.curr_bal, 0) from kk where kk.br_no = t2.br_no and kk.gl_code = t2.gl_code and kk.ac_date = t2.ac_date and t2.ac_date = trunc(now() - 1)) where ac_date = trunc(now() - 1); raise notice 'update t_acc_general_ledger_ac_his'; update t_acc_general_ledger_ac_his t2 set c_curr_bal = (with kk as (SELECT br_no, t1.gl_code, debit_credit, t1.ac_date, CASE WHEN debit_credit = 'D' THEN nvl(d_prev_bal,0) + nvl(d_curr_amt,0) - nvl(c_curr_amt,0) WHEN debit_credit = 'C' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) WHEN debit_credit = 'B' AND nvl(d_prev_bal, 0) = 0 and nvl(c_prev_bal, 0) = 0 and (d_curr_amt - c_curr_amt) < 0 THEN abs(nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0)) when debit_credit = 'B' and nvl(c_prev_bal, 0) > 0 and (c_prev_bal + c_curr_amt - nvl(d_curr_amt, 0)) > 0 then abs(c_prev_bal + c_curr_amt - nvl(d_curr_amt, 0)) when debit_credit = 'B' and nvl(d_prev_bal, 0) > 0 and (d_prev_bal + d_curr_amt - nvl(c_curr_amt, 0)) < 0 then abs(d_prev_bal + d_curr_amt - nvl(c_curr_amt, 0)) --ELSE abs(c_prev_bal+c_curr_amt-nvl(d_curr_amt,d_curr_amt,0)) END curr_bal FROM t_acc_general_ledger_ac_his t1, t_acc_subject WHERE t1.gl_code = t_acc_subject.gl_code and ac_date = trunc(now() - 1)) select decode(kk.debit_credit, 'C', kk.curr_bal, 'B', kk.curr_bal, 0) from kk where kk.br_no = t2.br_no and kk.gl_code = t2.gl_code and kk.ac_date = t2.ac_date and t2.ac_date = trunc(now() - 1)) where ac_date = trunc(now() - 1); select count(*) into v_count from t_acc_general_ledger_ac_his where 1=1 and ac_date = trunc(now() - 1); raise notice 'v_count: %',v_count; if (v_count > 0) then select count(*) into v_d_count from t_acc_general_ledger_ac_his where 1=1 and d_prev_bal > '0' and ac_date = trunc(now() - 1); select count(*) into v_c_count from t_acc_general_ledger_ac_his where 1=1 and c_prev_bal > '0' and ac_date = trunc(now() - 1); select count(*) into v_b_count from t_acc_general_ledger_ac_his where 1=1 and ac_date = trunc(now() - 1) and (d_prev_bal is null or d_prev_bal = '0') and (c_prev_bal is null or c_prev_bal = '0') and cast (d_curr_amt as numeric ) > cast (c_curr_amt as numeric); select count(*) into v_a_count from t_acc_general_ledger_ac_his where 1=1 and ac_date = trunc(now() - 1) and (d_prev_bal is null or d_prev_bal = '0') and (c_prev_bal is null or c_prev_bal = '0') and cast(d_curr_amt as numeric) < cast (c_curr_amt as numeric); if v_d_count > 0 then update t_acc_general_ledger_ac_his set d_curr_bal = (with kk as (select br_no, gl_code, d_prev_bal, c_prev_bal, ac_date, curr_bal from (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) when d_prev_bal > '0' then nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1) and 1=1) where d_prev_bal > '0') select decode(sign(curr_bal), 1, curr_bal, 0) from kk where t_acc_general_ledger_ac_his.br_no = kk.br_no and t_acc_general_ledger_ac_his.gl_code = kk.gl_code) where 1=1 and d_prev_bal > '0' and ac_date = trunc(now() - 1); update t_acc_general_ledger_ac_his set c_curr_bal = (with kk as (select br_no, gl_code, d_prev_bal, c_prev_bal, ac_date, curr_bal from (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) when d_prev_bal > '0' then nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1) and 1=1) where d_prev_bal > '0') select decode(sign(curr_bal), 1, 0, abs(curr_bal)) from kk where t_acc_general_ledger_ac_his.br_no = kk.br_no and t_acc_general_ledger_ac_his.gl_code = kk.gl_code) where 1=1 and d_prev_bal > '0' and ac_date = trunc(now() - 1); end if; if v_c_count > 0 then update t_acc_general_ledger_ac_his set d_curr_bal = (with kk as (select br_no, gl_code, d_prev_bal, c_prev_bal, ac_date, curr_bal from (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) when d_prev_bal > '0' then nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1) and 1=1) where c_prev_bal > '0') select decode(sign(curr_bal), 1, 0, abs(curr_bal)) from kk where t_acc_general_ledger_ac_his.br_no = kk.br_no and t_acc_general_ledger_ac_his.gl_code = kk.gl_code) where 1=1 and c_prev_bal > '0' and ac_date = trunc(now() - 1); update t_acc_general_ledger_ac_his set c_curr_bal = (with kk as (select br_no, gl_code, d_prev_bal, c_prev_bal, ac_date, curr_bal from (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) when d_prev_bal > '0' then nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1) and 1=1) where c_prev_bal > '0') select decode(sign(curr_bal), 1, abs(curr_bal), 0) from kk where t_acc_general_ledger_ac_his.br_no = kk.br_no and t_acc_general_ledger_ac_his.gl_code = kk.gl_code) where 1=1 and c_prev_bal > '0' and ac_date = trunc(now() - 1); end if; if v_b_count > 0 then UPDATE t_acc_general_ledger_ac_his SET d_curr_bal = (WITH kk AS (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, d_curr_amt, c_curr_amt, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) WHEN d_prev_bal > '0' THEN nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = TRUNC(now() - 1) AND 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND (cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) > cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric))) SELECT DECODE(SIGN(curr_bal), 1, curr_bal, 0) FROM kk WHERE t_acc_general_ledger_ac_his.br_no = kk.br_no AND t_acc_general_ledger_ac_his.gl_code = kk.gl_code) WHERE 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) > cast (t_acc_general_ledger_ac_his.C_CURR_AMT as numeric) AND ac_date = trunc(now() - 1); UPDATE t_acc_general_ledger_ac_his SET c_curr_bal = (WITH kk AS (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, d_curr_amt, c_curr_amt, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) WHEN d_prev_bal > '0' THEN nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = TRUNC(now() - 1) AND 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND (cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) > cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric))) SELECT DECODE(SIGN(curr_bal), 1, 0, ABS(curr_bal)) FROM kk WHERE t_acc_general_ledger_ac_his.br_no = kk.br_no AND t_acc_general_ledger_ac_his.gl_code = kk.gl_code) WHERE 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) > cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric) AND ac_date = trunc(now() - 1); end if; if v_a_count > 0 then UPDATE t_acc_general_ledger_ac_his SET c_curr_bal = (WITH kk AS (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, d_curr_amt, c_curr_amt, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) WHEN d_prev_bal > '0' THEN nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1) AND 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND (cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) < cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric))) SELECT DECODE(SIGN(curr_bal), -1, ABS(curr_bal), 0) FROM kk WHERE t_acc_general_ledger_ac_his.br_no = kk.br_no AND t_acc_general_ledger_ac_his.gl_code = kk.gl_code) WHERE 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) < cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric) AND ac_date = trunc(now() - 1); UPDATE t_acc_general_ledger_ac_his SET d_curr_bal = (WITH kk AS (SELECT br_no, gl_code, ac_date, d_prev_bal, c_prev_bal, d_curr_amt, c_curr_amt, CASE WHEN c_prev_bal > '0' THEN nvl(c_prev_bal, 0) + nvl(c_curr_amt, 0) - nvl(d_curr_amt, 0) WHEN d_prev_bal > '0' THEN nvl(d_prev_bal, 0) + nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) ELSE nvl(d_curr_amt, 0) - nvl(c_curr_amt, 0) END curr_bal FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1) AND 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND (cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) < cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric))) SELECT DECODE(SIGN(curr_bal), -1, 0, ABS(curr_bal)) FROM kk WHERE t_acc_general_ledger_ac_his.br_no = kk.br_no AND t_acc_general_ledger_ac_his.gl_code = kk.gl_code) WHERE 1=1 AND (c_prev_bal IS NULL OR c_prev_bal = '0') AND (d_prev_bal IS NULL OR d_prev_bal = '0') AND cast(t_acc_general_ledger_ac_his.D_CURR_AMT as numeric) < cast(t_acc_general_ledger_ac_his.C_CURR_AMT as numeric) AND ac_date = trunc(now() - 1); end if; end if; raise notice 'begin insert t_acc_general_ledger_ac_his T1'; insert into t_acc_general_ledger_ac_his (br_no, gl_code) select br_no, gl_code from t_acc_general_ledger_ac_his where ac_date = trunc(now() - 2) minus select br_no, gl_code from t_acc_general_ledger_ac_his where ac_date = trunc(now() - 1); UPDATE t_acc_general_ledger_ac_his t2 SET (d_prev_bal) = (WITH kk AS (SELECT br_no, gl_code, ac_date, lag(d_curr_bal::bigint, 1,0::bigint) over(order by ac_date) d_prev_bal_next, lag(c_curr_bal::bigint, 1,0::bigint) over(order by ac_date) c_prev_bal_next FROM t_acc_general_ledger_ac_his WHERE (br_no, gl_code) IN (SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 2) MINUS SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1))) SELECT d_prev_bal_next FROM kk WHERE kk.br_no = t2.br_no AND kk.gl_code = t2.gl_code AND kk.ac_date IS NULL) WHERE ac_date IS NULL AND (br_no, gl_code) IN (SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 2) MINUS SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1)); raise notice 'begin updte t_acc_general_ledger_ac_his T2'; UPDATE t_acc_general_ledger_ac_his t2 SET (c_prev_bal) = (WITH kk AS (SELECT br_no, gl_code, ac_date, lag(d_curr_bal::bigint, 1,0::bigint) over(partition BY br_no, gl_code order by ac_date) d_prev_bal_next, lag(c_curr_bal::bigint, 1,0::bigint) over(partition BY br_no, gl_code order by ac_date) c_prev_bal_next FROM t_acc_general_ledger_ac_his WHERE (br_no, gl_code) IN (SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 2) MINUS SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1))) SELECT c_prev_bal_next FROM kk WHERE kk.br_no = t2.br_no AND kk.gl_code = t2.gl_code AND kk.ac_date IS NULL) WHERE ac_date IS NULL AND (br_no, gl_code) IN (SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 2) MINUS SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1)); update t_acc_general_ledger_ac_his set d_curr_bal = d_prev_bal, c_curr_bal = c_prev_bal where ac_date is null; raise notice 'begin updte [last] t_acc_general_ledger_ac_his'; UPDATE t_acc_general_ledger_ac_his SET seq_no = t_acc_general_ledger_ac_his_seq_no_seq.nextval, d_count = '0', c_count = '0', d_curr_amt = '0', c_curr_amt = '0', ac_date = trunc(now() - 1), ccy = INCCY WHERE ac_date IS NULL AND (br_no, gl_code) IN (SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 2) MINUS SELECT br_no, gl_code FROM t_acc_general_ledger_ac_his WHERE ac_date = trunc(now() - 1)); --commit; else --dbms_output.put_line('error!'); raise notice 'error!'; --for language plpgsql end if; raise notice 'begin call P_SUBJECT_SHEET'; perform P_SUBJECT_SHEET('CNY'); EXCEPTION WHEN OTHERS THEN v_code := 0; v_errm := SUBSTR(SQLERRM, 1, 256); INSERT INTO t_errors (code, MESSAGE, PRO_NAME, RUN_DATE) VALUES (v_code, v_errm, 'P_LOOP_COUNT_SHEET', now()); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ; ALTER FUNCTION "acc_user"."p_loop_count_sheet"(inccy varchar) OWNER TO "acc_user";