CREATE TABLE STAGING_tab
(
E_ID NUMBER(10),
E_NAME VARCHAR2(30),
E_LOC VARCHAR2(30),
VALIDATION_STATUS varchar2(30),
validation_msg varchar2(30),
req_id number(10)
);
insert into staging_tab values(1,'A','AA',null,null,1);
insert into staging_tab values(2,'B','BB',null,null,1);
insert into staging_tab values(3,'C','CC',null,null,1);
insert into staging_tab values(null,'D','DD',null,null,2);
insert into staging_tab values(null,'E','EE',null,null,2);
insert into staging_tab values(null,'F','GG',null,null,2);
CREATE TABLE tab_ref
(
ref_id number(10),
ref_name varchar2(30)
);
insert into tab_ref values(1,'aa');
insert into tab_ref values(2,'bb');
insert into tab_ref values(3,'cc');
insert into tab_ref values(4,'dd');
CREATE TABLE tab_ref_2
(
ref_id number(10),
ref_name varchar2(30)
);
insert into tab_ref_2 values(1,'ee');
insert into tab_ref_2 values(2,'ff');
insert into tab_ref_2 values(3,'gg');
insert into tab_ref_2 values(4,'hh');
CREATE TABLE SUMMARY_TAB
(
TOT_RECORDS NUMBER(10,0),
SUCCESS_RECORDS NUMBER(10,0),
FAILED_RECORDS NUMBER(10,0),
process_status varchar2(30)
);
CREATE TABLE TARGET_TAB
(
E_ID NUMBER(10,0),
E_NAME VARCHAR2(30),
E_LOC VARCHAR2(30)
);
存盤程序：
create or replace procedure sp_stage_target(iv_req_id IN sys.OdciNumberList,ov_err_msg OUT varchar2) is
lv_succ_rec number(30);
lv_fail_rec number(30);
lv_count_ref number(10);
lv_count_ref2 number(10);
lv_threshold_cnt number(10);
lv_RejectedCount number(10);
lv_status varchar2(30);
begin
lv_succ_rec := 0;
lv_fail_rec := 0;
lv_threshold_cnt := 5;
/*First checking whether data is present in reference table or not.
If data is not present then process should stop*/
select count(1) into lv_count_ref from tab_ref;
select count(1) into lv_count_ref2 from tab_ref_2;
if lv_count_ref = 0 then
ov_err_msg := 'Records are not present in the reference table !!Cannot proceed';
elsif lv_count_ref2 = 0 then
ov_err_msg := 'Records are not present in the reference table !!Cannot proceed';
else
dbms_output.put_line('Data are present into reference tables');
merge into staging_tab d
using (
select 'Fail' as validation_status, t.column_value as req_id
from table(iv_req_id) t
) s
on (d.req_id = s.req_id)
when matched then
update set
d.validation_status = s.validation_status
, d.validation_msg = case
when e_id is null then 'Id is not present'
else 'Id is longer than expected'
end
where e_id is null OR LENGTH(e_id) > 4;
lv_RejectedCount := SQL%ROWCOUNT;
end if;
--If rejected count is less than lv_threshold_cnt i.e 5
--then success records will go in target_tab and failed records will go in reject_tab
if lv_RejectedCount <= lv_threshold_cnt then
lv_status := 'Success';
dbms_output.put_line('Success');
merge into target_tab t
using (
select e_id, e_name, e_loc
from staging_tab
where validation_status is null and req_id in (select column_value from table(iv_req_id))
) s
on (t.e_id = s.e_id)
when matched then
update set
t.e_name = s.e_name,
t.e_loc = s.e_loc
when not matched then
insert (t.e_id,t.e_name,t.e_loc)
values (s.e_id,s.e_name,s.e_loc);
lv_succ_rec := SQL%ROWCOUNT;
end if;
insert into reject_tab(e_id, e_name, e_loc, validation_status,validation_msg)
select e_id, e_name, e_loc, validation_status,validation_msg
from staging_tab
where validation_status = 'Fail' and req_id in (select column_value from table(iv_req_id));
lv_fail_rec := SQL%ROWCOUNT;
--In Summary table keeping track of all the records i.e success record, failed records
dbms_output.put_line('Inserting into Summary table');
insert into summary_tab(tot_records, success_records, failed_records, process_status)
values (lv_succ_rec lv_fail_rec, lv_succ_rec, lv_fail_rec, lv_status);
ov_err_msg := 'Procedure completed succesfully';
commit;
end;
呼叫程式：
declare
err_msg varchar2(4000);
begin
sp_stage_target(sys.OdciNumberList(1,2),err_msg);
dbms_output.put_line(err_msg);
end;
當我呼叫一個程序時，我沒有得到結果。理想情況下，它應該將記錄從 staging 插入到 target_tab、reject_tab 和 summary_tab。但是沒有記錄。有人可以幫忙嗎？我只在同一個會話中查詢呼叫程序。
uj5u.com熱心網友回復：
謝謝你的測驗用例；很少見到。
據我所知，您的程式有效。雖然，你沒有發布reject_table描述，所以我在程式中注釋了那段代碼，但是 - 其余的似乎沒問題。
SQL> declare
2 err_msg varchar2(4000);
3 begin
4 sp_stage_target(sys.OdciNumberList(1,2),err_msg);
5 dbms_output.put_line(err_msg);
6 end;
7 /
Data are present into reference tables
Success
Inserting into Summary table
Procedure completed succesfully
PL/SQL procedure successfully completed.
SQL> select * From target_tab;
E_ID E_NAME E_LOC
---------- ---------- ------------------------------
3 C CC
1 A AA
2 B BB
SQL> select * from summary_tab;
TOT_RECORDS SUCCESS_RECORDS FAILED_RECORDS PROCESS_STATUS
----------- --------------- -------------- ------------------------------
6 3 3 Success
6 3 3 Success
SQL>
uj5u.com熱心網友回復：
你如何運行他的呼叫程式？您的結果取決于 DBMS_OUTPUT。但它實際上并沒有生成任何輸出，它創建并在客戶端（這里是您的呼叫程序）可以處理或不處理的緩沖區中。如果通過 SQL*Plus 運行呼叫程序，請確保set serveroutput on在程序之前有。如果通過另一個 api，您可以dbms_output.enable在開始后嘗試。
SET SERVEROUTPUT ON --- THIS
declare
err_msg varchar2(4000);
begin
dbms_output.enable; -- OR THIS
sp_stage_target(sys.OdciNumberList(1,2),err_msg);
dbms_output.put_line(err_msg);
end;
/
不幸的是，并非所有工具都支持 dbms_output。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/354687.html
上一篇：sql查詢到plsql程式