Oracle脚本备忘
# 1 Oracle根据表名反查job和存储过程
select *
from user_dependencies
where referenced_name=upper('TAB_NET_SIGNOMITARRIVE_STAT')
1
2
3
2
3
select *
from user_jobs
where upper(what) like '%SIGNOMITARRIVE%'
1
2
3
2
3
select *
from tab_job_monitor
where upper(procedure_name) like '%SIGNOMITARRIVE%'
order by createdate desc
1
2
3
4
2
3
4
# 2 Oracle查询库表所有列
select
a.column_name 列名,
b.COMMENTS 注释,
CASE a.nullable
WHEN 'Y' THEN ''
WHEN 'N' THEN '否'
END 是否可为空
from all_tab_columns a
left join user_col_comments b
on a.COLUMN_NAME = b.COLUMN_NAME
and a.TABLE_NAME = b.TABLE_NAME
where a.table_name='表名'
AND a.owner = UPPER('用户名');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 3 merge（select与update结合）
merge into YL_OMS_OMS_WAYBILL a
USING (
select WAYBILL_NO,
IS_SIGN,
WAYBILL_STATUS_CODE,
SIGN_NETWORK_CODE,
SIGN_NETWORK_NAME,
SIGN_TIME,
DISPATCH_FINANCE_CODE,
DISPATCH_FINANCE_NAME,
DISPATCH_NAME,
DISPATCH_NETWORK_CODE,
DISPATCH_NETWORK_NAME,
DISPATCH_TIME,
DISPATCH_STAFF_CODE,
DISPATCH_STAFF_NAME,
DESTINATION_CODE,
DESTINATION_NAME
from tmp_litemain.yl_oms_oms_waybill
where waybill_no in (
'JTE300000803382',
'JTE300000243347',
'JTE300000507471',
'JTE300000681352',
'JTE300001012445'
)) b ON (a.WAYBILL_NO = b.waybill_no)
when matched then
update set
a.IS_SIGN = b.IS_SIGN,
a.WAYBILL_STATUS_CODE = b.WAYBILL_STATUS_CODE,
a.SIGN_FINANCE_CODE = 'UAE001',
a.SIGN_FINANCE_NAME = 'UAE HQ',
a.SIGN_NETWORK_CODE = b.SIGN_NETWORK_CODE,
a.SIGN_NETWORK_ID = nvl((select id
from YL_LMDM_SYS_NETWORK
where code = b.SIGN_NETWORK_CODE
and rownum = 1), 0),
a.SIGN_NETWORK_NAME = b.SIGN_NETWORK_NAME,
a.SIGN_TIME = b.SIGN_TIME - 1 / 24,
a.DISPATCH_FINANCE_CODE = b.DISPATCH_FINANCE_CODE,
a.DISPATCH_FINANCE_NAME = b.DISPATCH_FINANCE_NAME,
a.DISPATCH_NAME = b.DISPATCH_NAME,
a.DISPATCH_TIME = b.DISPATCH_TIME - 1 / 24,
a.DISPATCH_NETWORK_CODE = b.DISPATCH_NETWORK_CODE,
a.DISPATCH_NETWORK_ID = nvl((select id
from YL_LMDM_SYS_NETWORK
where code = b.DISPATCH_NETWORK_CODE
and rownum = 1), 0),
a.DISPATCH_NETWORK_NAME = b.DISPATCH_NETWORK_NAME,
a.DISPATCH_STAFF_CODE = b.DISPATCH_STAFF_CODE,
a.DISPATCH_STAFF_NAME = b.DISPATCH_STAFF_NAME,
a.DESTINATION_CODE = b.DESTINATION_CODE,
a.DESTINATION_ID = nvl((select id
from YL_LMDM_SYS_SETTLEMENT_DEST
where code = b.DESTINATION_CODE
and rownum = 1), 0),
a.DESTINATION_NAME = b.DESTINATION_NAME;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# 4 存储过程示例
# 4.1 创建过程
create
or replace procedure P_NW_SIGN_STAFF_STASTICTICS(
pi_date in date,
pi_execute_id in nvarchar2,
v_execute_time in date
)
is
v_begindate date;
v_enddate date;
v_id nvarchar2(50);
v_failed_reason nvarchar2(2000);
begin
v_begindate := trunc(pi_date);
v_enddate := v_begindate+1-1/86400;
v_id := sys_guid();
insert into TAB_JOB_RECORD
(id, execute_id, job_name, job_procedure, begin_time, end_time, execute_time, finish_time, failed_reason, create_time)
values (v_id, nvl(pi_execute_id, v_id), '报表分析业务员签收量统计', 'P_NW_SIGN_STAFF_STASTICTICS', v_begindate, v_enddate, v_execute_time, null, null, sysdate);
--提交
commit;
begin
--先删除数据
delete from TAB_NW_SIGN_STAFF_STASTICTICS WHERE sign_time >= trunc(pi_date) and sign_time < trunc(pi_date) + 1;
--插入数据
insert into TAB_NW_DIS_STAFF_STASTICTICS
(sign_time,
dispatch_finance_code,
dispatch_finance_name,
fran_code,
fran_name,
network_code,
network_name,
staff_code,
staff_name,
total,
refundcount,
weighttotal,
signtotal,
abnormaltotal,
dispatch_time)
select /*+use_hash(a b t) leading(a) swap_join_inputs(a) index(t UNI_DISPATCH_WAYBILL_NO)*/
trunc(b.dispatch_time,'hh24') sign_time,
max(a.financial_center_code) agent_code,
max(a.financial_center_desc) agent_name,
max(a.parent_network_code) fran_code,
max(a.parent_network_name) fran_name,
b.scansitecode network_code,
max(a.name) network_name,
b.send_deliver_usercode staff_code,
max(b.send_deliver_user) staff_name,
count(0) counttotal,
sum(case
when t.is_refund = 1 then
1
else
0
end) refundcount,
sum(nvl(t.charge_weight, 0)) weighttotal,
sum(case when t.is_sign = 1 then 1 else 0 end ) signtotal,
sum(case when t.is_abnormal = 1 then 1 else 0 end) abnormaltotal,
max(trunc(t.dispatch_time)) dispatch_time
from (select rn, billcode, scansitecode, trunc(scantime,'hh24') dispatch_time, send_deliver_usercode, send_deliver_user
from (select row_number() over(partition by billcode,trunc(scantime,'hh24') order by scantime desc) rn,
billcode, scansitecode, scantime, send_deliver_usercode, send_deliver_user
from TAB_BARSCAN_DELIVER
where scantime >= v_begindate and scantime <= v_enddate
) where rn = 1 ) b
left join yl_lmdm_sys_network a on b.scansitecode = a.code
left join YL_OMS_DISPATCH_WAYBILL t on t.waybill_no = b.billcode
where 1 = 1
group by b.send_deliver_usercode, b.scansitecode, trunc(b.dispatch_time,'hh24');
--提交
commit;
update TAB_JOB_RECORD
set finish_time=sysdate
where id=v_id;
--提交
commit;
exception
when others then
v_failed_reason := substr(sqlerrm,1,1000);
begin
update TAB_JOB_RECORD
set finish_time=sysdate,
failed_reason=v_failed_reason
where id=v_id;
--提交
commit;
exception
when others then
null;
end;
end;
end P_NW_SIGN_STAFF_STASTICTICS;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
# 4.2 创建JOB
CREATE
OR REPLACE PROCEDURE P_NW_SIGN_STAFF_STASTICTICS_JOB
is
v_id nvarchar2(50);
v_count number;
v_date date;
v_execute_time date;
begin
v_id := sys_guid()||'-1';
v_execute_time := sysdate;
--每0分钟跑一次前到天数据
for i in 0..3 loop
v_date := trunc(sysdate)-i;
select count(1)
into v_count
from TAB_JOB_RECORD
where BEGIN_TIME = v_date
and END_TIME = v_date + 1 - 1 / 86400
and EXECUTE_TIME >= sysdate - 30 / 1440
and JOB_PROCEDURE = 'P_NW_SIGN_STAFF_STASTICTICS'
and FINISH_TIME is not null;
if
v_count=0 then
P_NW_SIGN_STAFF_STASTICTICS(v_date,v_id,v_execute_time);
end if;
end loop;
--每4小时跑一次前到1天数据
for i in 4..31 loop
v_date := trunc(sysdate)-i;
select count(1)
into v_count
from TAB_JOB_RECORD
where BEGIN_TIME = v_date
and END_TIME = v_date + 1 - 1 / 86400
and EXECUTE_TIME >= sysdate - 1
and JOB_PROCEDURE = 'P_NW_SIGN_STAFF_STASTICTICS'
and FINISH_TIME is not null;
if
v_count=0 then
P_NW_SIGN_STAFF_STASTICTICS(v_date,v_id,v_execute_time);
end if;
end loop;
--每8小时跑一次前2到2天数据
for i in 32..62 loop
v_date := trunc(sysdate)-i;
select count(1)
into v_count
from TAB_JOB_RECORD
where BEGIN_TIME = v_date
and END_TIME = v_date + 1 - 1 / 86400
and EXECUTE_TIME >= sysdate - 2
and JOB_PROCEDURE = 'P_NW_SIGN_STAFF_STASTICTICS'
and FINISH_TIME is not null;
if
v_count=0 then
P_NW_SIGN_STAFF_STASTICTICS(v_date,v_id,v_execute_time);
end if;
end loop;
end P_NW_SIGN_STAFF_STASTICTICS_JOB;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
# 4.3 创建调度器
declare
job number;
begin
sys.dbms_job.submit
(job => job,
what => 'p_oms_dispatch_summary_job;',
next_date => to_date('00:00:00 17-12-2020', 'ss:mi:HH24 dd-mm-yyyy'),
interval => 'sysdate+30/1440');
commit;
end;
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 5 cursor使用示例
create or replace procedure PRO_DAY_DIS__STASTICTICS(begindate in date) is
/**************************************************************************************
*功 能：业务员签收统计(月)
*业务需求：对业务员日派件量进行统计
**************************************************************************************/
v_begindate date := trunc(begindate)-1;
v_enddate date := v_begindate+1-1/86400;
v_guid nvarchar2(80);
v_agent_code VARCHAR2(30);
v_agent_name VARCHAR2(60);
v_fran_code VARCHAR2(30);
v_fran_name VARCHAR2(60);
v_network_code VARCHAR2(30);
v_network_name VARCHAR2(60);
begin
declare
cursor cursor_all_networks is
select t2.code,t2.name,
(select max(t1.code) from YL_LMDM_SYS_NETWORK t1 where type_id= 334 start with t1.code = t2.code connect by prior parent_network_code = code) agentAreaCode,
(select max(t1.name) from YL_LMDM_SYS_NETWORK t1 where type_id= 334 start with t1.code = t2.code connect by prior parent_network_code = code) agentArea,
(select max(t1.code) from YL_LMDM_SYS_NETWORK t1 where is_first_franchisee = 1 start with t1.code = t2.code connect by prior parent_network_code = code) franchiseesCode,
(select max(t1.name) from YL_LMDM_SYS_NETWORK t1 where is_first_franchisee = 1 start with t1.code = t2.code connect by prior parent_network_code = code) franchisees
from YL_LMDM_SYS_NETWORK t2 where t2.type_id = 336;
begin
--新增执行开始信息
select SYS_GUID() into v_guid from dual;
insert into TAB_JOB_MONITOR
(
ID,
MENU_NAME,
PROCEDURE_NAME,
start_time,
create_time,
DESCRIPTION)
values
(v_guid,
'网点经营-报表分析-业务员派件统计-' || TO_CHAR(v_begindate, 'YYYY-MM-DD'),
'PRO_DAY_DIS__STASTICTICS',
sysdate,
sysdate,
'月业务员派件统计');
commit;
--step1 删除数据
delete from TAB_DAY_DIS_STAFF_STASTICTICS where sign_time >= v_begindate and sign_time <= v_enddate;
--step2 遍历所有网点，按派件员分组统计插入汇总表
for cursor_network in cursor_all_networks loop
v_agent_code := cursor_network.agentAreaCode;
v_agent_name := cursor_network.agentArea;
v_fran_code := cursor_network.franchiseesCode;
v_fran_name := cursor_network.franchisees;
v_network_code := cursor_network.code;
v_network_name := cursor_network.name;
insert into TAB_DAY_DIS_STAFF_STASTICTICS(
sign_time ,
dispatch_finance_code,
dispatch_finance_name,
fran_code ,
fran_name ,
network_code,
network_name,
staff_code ,
staff_name ,
total ,
refundcount,
weighttotal
)
select /*+index(t IDX_SIGN_NETWORK_AND_SIGN_TIME)*/
trunc(v_begindate) sign_time,
v_agent_code agent_code,
v_agent_name agent_name,
v_fran_code fran_code,
v_fran_name fran_name,
v_network_code network_code,
v_network_name network_name,
t.dispatch_staff_code staff_code,
max(t.dispatch_staff_name) staff_name,
count(0) counttotal,
sum(case when t.is_refund = 1 then 1 else 0 end) refundcount,
sum(nvl(t.charge_weight,0)) weighttotal
from YL_OMS_DISPATCH_WAYBILL t
where 1=1
and t.create_time >= v_begindate-14
and t.create_time <= v_enddate+3
and t.SIGN_TIME >= v_begindate
and t.SIGN_TIME <= v_enddate
and t.is_sign <> 0
and t.sign_network_code = v_network_code
group by
t.dispatch_staff_code;
end loop;
end;
commit;
--回写执行结束信息
update TAB_JOB_MONITOR
set END_TIME = sysdate,
DURATION = to_char(round((sysdate - start_time) * 24 * 60, 2),
'fm9999999990.00')
where ID = v_guid;
commit;
end PRO_DAY_DIS__STASTICTICS;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
# 6 调用存储过程并打印结果
控制台调用
DECLARE v_result sys_refcursor;
code NVARCHAR2(20);
name NVARCHAR2(100);
BEGIN
P_APP3_QUERYCUSTOMERINFO('DG00001','00340434321041705007',v_result);
LOOP
FETCH v_result INTO code, name;
EXIT WHEN v_result%notfound;
dbms_output.put_line(code || '-' || name);
END LOOP;
END;
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
存储过程
提示
select into 如果查询的记录为空时会报错。
可使用 nvl + max 的方式，参考以下存储过程。
CREATE OR REPLACE procedure YLPT.p_app3_queryCustomerInfo(p_sitecode in varchar2,
p_orderno in varchar2,
p_rf out sys_refcursor) is
v_centerMark number ;
v_billCode NVARCHAR2(64) := 'null';
begin
begin
select nvl(分拨中心标识,0) into v_centerMark from TAB_营业网点表 where 网点编号 = p_sitecode;
select nvl(max(BILLCODE),'null123') INTO v_billCode FROM TAB_US_NUMBER_ASSOCIATED WHERE TRACKING_NO = p_orderno AND rownum < 2;
if v_centerMark = 1 then
open p_rf for
select v.cuscode as code,v.cusname as name from TAB_Customer v
where cusname in (
select 寄件客户
from d_order
where logisticid in (p_orderno,v_billCode)
or billcode in (p_orderno,v_billCode)
);
end if;
if v_centerMark = 0 then
open p_rf for
select v.cuscode as code,v.cusname as name from TAB_Customer v
where cus_site in (select 网点名称 from TAB_营业网点表 where 网点编号 = p_sitecode)
and cusname in (
select 寄件客户
from d_order
where logisticid in (p_orderno,v_billCode)
or billcode in (p_orderno,v_billCode)
);
end if;
exception
when others then
dbms_output.put_line('查询客户信息异常...');
end;
end p_app3_queryCustomerInfo;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Mybatis映射文件
<select id="queryCustomerInfoList" parameterType="map" statementType="CALLABLE">
<![CDATA[
{CALL P_APP3_QUERYCUSTOMERINFO(
#{siteCode,jdbcType=VARCHAR,mode=IN},
#{billCode,jdbcType=VARCHAR,mode=IN},
#{p_rf,jdbcType=CURSOR,mode=OUT,resultMap=BaseDataMap,javaType=java.sql.ResultSet}
)}
]]>
</select>
<resultMap type="BaseData" id="BaseDataMap">
<result property="code" column="CODE" />
<result property="name" column="NAME" />
</resultMap>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
Java
List<BaseData> queryCustomerInfoList(Map<String, Object> params);
1
# 7 保留唯一数据
相同条件的只留一条，其他的更新状态
update table_1
set av_status_code = 'CANCEL'
where id in (
select a.id
from (
select tt1.id,
row_number() over(partition by tt1.fund_uuid,tt1.mem_acct_uuid,tt1.subacct_type_uuid order by tt1.id) rn
from (
select t.id,
t4.fund_uuid,
t.mem_acct_uuid,
t3.subacct_type_uuid
from table_1 t
join table_2 c
on c.tr_scheme_uuid = t.scheme_uuid and trunc(c.cycle_date) = trunc(t.eff_date) and
c.cycle_date_step_code = 2
join table_3 t3 on t3.instr_uuid = t.id
join table_4 t4 on t4.instr_subacct_uuid = t3.id
WHERE av_status_code = 'IN_PROGRESS'
) tt1
) a
where a.rn > 1
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23