3 积分	0 好友	1 主题

发消息

[SQL调优] PLSQL包执行过程中Commit的事务在包执行结束前为何看不到？

1^#

发表于 2014-5-19 21:26:03 | 查看: 3200| 回复: 1

遇到一个奇怪的现象，我在运行PLSQL包时，各个子过程都有commit，当前3个子过程运行结束，第4个子过程正在运行时，打开一个窗口去select第1个子过程insert的表，没有数据，要等整个程序执行完了才有数据。这是什么原因呢？（第1个子过程中，是先将表设为nologging，然后执行DML并Commit，再将表设为logging）。

主过程代码：

PROCEDURE Main_P(x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_object IN PLS_INTEGER DEFAULT 0,
p_mode IN PLS_INTEGER DEFAULT 1) IS
e_sql_block_err EXCEPTION;
l_debug_flag VARCHAR2(100) := '0';
BEGIN
KLAR_Fact_Load_P(x_errbuf, x_retcode, 0);
IF p_object = 0 THEN
KLAR_Business_Unit_Dim_Load_P(x_errbuf, x_retcode, p_mode);
KLAR_Customer_Dim_Load_P(x_errbuf, x_retcode, p_mode);
KLAR_Sales_Rigion_Dim_Load_P(x_errbuf, x_retcode, p_mode);
KLAR_Fact_Load_P(x_errbuf, x_retcode, p_mode);
ELSE
NULL;
END IF;
EXCEPTION
WHEN e_sub_proc_err THEN
-- e_sub_proc_err is an exit for exceptions of sub-procedures only.
NULL;
WHEN e_sql_block_err THEN
output_line(log, 'Main_P e_sql_block_err:');
output_line(log, x_errbuf);
x_errbuf := 'Main_P e_sql_block_err- ' || x_errbuf;
x_retcode := c_error;
WHEN OTHERS THEN
output_line(log,
'Main_P Error at ' || l_debug_flag || ': ' || CHR(10) ||
SQLERRM);
x_errbuf := 'Main_P Error at ' || l_debug_flag || ': ' || SQLERRM;
x_retcode := c_error;
END Main_P;

复制代码

第1个子过程的代码：

PROCEDURE KLAR_Business_Unit_Dim_Load_P(x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_mode IN PLS_INTEGER) IS
l_debug_flag VARCHAR2(100) := '0';
BEGIN
-- Disable idx
l_debug_flag := 'Disable idx 1';
EXECUTE IMMEDIATE 'ALTER INDEX KLCUX.KLAR_BUSINESS_UNIT_DIM_N1 UNUSABLE';
-- Load data
l_debug_flag := 'Load data 1';
MERGE INTO KLCUX.KLAR_BUSINESS_UNIT_DIM t
USING (SELECT hou.organization_id org_id,
hou.name operation_unit,
ffv.flex_value company_code,
ffvt.description company_name
FROM HR_ALL_ORGANIZATION_UNITS hou
JOIN HR_ORGANIZATION_INFORMATION hoi
ON hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'Operating Unit Information'
JOIN XLE_ENTITY_PROFILES xep
ON xep.legal_entity_id = hoi.org_information2
JOIN FND_FLEX_VALUES ffv
ON ffv.flex_value = xep.legal_entity_identifier
AND ffv.enabled_flag = 'Y'
JOIN FND_FLEX_VALUES_TL ffvt
ON ffvt.flex_value_id = ffv.flex_value_id
AND ffvt.language = 'ZHS'
JOIN FND_FLEX_VALUE_SETS ffvs
ON ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvs.flex_value_set_name = 'KL_COA_CO') s
ON (t.org_id = s.org_id)
WHEN MATCHED THEN
UPDATE
SET t.operation_unit = s.operation_unit,
t.company_code = s.company_code,
t.company_name = s.company_name
WHEN NOT MATCHED THEN
INSERT
(t.skid, t.org_id, t.operation_unit, t.company_code, t.company_name)
VALUES
(KLCUX.KLAR_BUSINESS_UNIT_DIM_S.nextval,
s.org_id,
s.operation_unit,
s.company_code,
s.company_name);
COMMIT;
-- Rebuilt and enable idx
l_debug_flag := 'Rebuilt idx 1';
EXECUTE IMMEDIATE 'ALTER INDEX KLCUX.KLAR_BUSINESS_UNIT_DIM_PK REBUILD';
l_debug_flag := 'Rebuilt idx 2';
EXECUTE IMMEDIATE 'ALTER INDEX KLCUX.KLAR_BUSINESS_UNIT_DIM_N1 REBUILD';
EXCEPTION
WHEN OTHERS THEN
output_line(log,
'KLAR_Business_Unit_Dim_Load_P Error at ' || l_debug_flag || ': ' ||
CHR(10) || SQLERRM);
x_errbuf := 'KLAR_Business_Unit_Dim_Load_P Error at ' || l_debug_flag || ': ' ||
SQLERRM;
x_retcode := c_error;
RAISE e_sub_proc_err;
END KLAR_Business_Unit_Dim_Load_P;