Home » SQL & PL/SQL » SQL & PL/SQL » Optimize PL/SQL code and get rid of one by one If then else statement check (Oracle 11g)
| Optimize PL/SQL code and get rid of one by one If then else statement check [message #638991] |
Mon, 29 June 2015 03:53  |
 |
raman.virk
Messages: 4
Registered: June 2015
|
Junior Member |
|
|
Hi All,
I have a PROCEDURE in DatawareHouse where I wish to load data from a staging table into the fact table. The code is using Cursor and For loop to access data from staging table and then it checks each record one by one using If-Then-Else condition and then inserts data into the main fact table. Now I want to know if it is possible to optimize this piece of code and get rid of the If then else. If I can use bulk collect to fetch data from staging table and then use If-Then-Else to check conditions, but is there any other way I can do it and get rid of If-Then-Else. Sample Code is as follows:
PROCEDURE X
AS
v_row_cnt NUMBER;
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(350);
tr_stg_rec Table_Stg%ROWTYPE;
tr_stg_rec_nxt Table_Stg%ROWTYPE;
l_from_user Table_Stg.act_2user%TYPE;
l_to_user Table_Stg.act_2user%TYPE;
l_actn Table_Fact.actn%TYPE;
l_to_login_name user.login_name%TYPE;
l_f_user Table_Fact.from_user_key%TYPE;
l_ft_user Table_Fact.to_user_key%TYPE;
v_pr_from_user Table_Stg.act_2user%TYPE;
v_pr_to_user Table_Stg.act_2user%TYPE;
CURSOR case_tr_cur
IS
SELECT DISTINCT act_2case, COUNT(*) FROM Table_Stg GROUP BY act_2case ;
CURSOR tr_stg_cur (p_case_key NUMBER)
IS
SELECT *
FROM Table_Stg
WHERE act_2case = p_case_key
ORDER BY act_2case,
entry_time;
FUNCTION f_key(
p_user_objid IN VARCHAR) RET NUMBER
IS
l_user_key NUMBER := 0;
BEGIN
SELECT user_key INTO l_user_key FROM USER WHERE user_objid = p_user_objid;
RET l_user_key;
EXCEPTION
WHEN OTHERS THEN
RET 0;
END f_key;
------END OF FUNCTION--------
FUNCTION F_GET_USER_OBJID(
p_login_name IN VARCHAR) RET NUMBER
IS
l_user_objid NUMBER := 0;
BEGIN
SELECT user_objid INTO l_user_objid FROM USER WHERE login_name = p_login_name;
RET l_user_objid;
EXCEPTION
WHEN OTHERS THEN
RET 0;
END F_GET_USER_OBJID;
------END OF FUNCTION--------
BEGIN
FOR tr_rec IN case_tr_cur
LOOP
-- dbms_output.put_line('1 main cursor '||tr_rec.act_2case );
OPEN tr_stg_cur(tr_rec.act_2case);
LOOP
-- dbms_output.put_line('2 2nd cursor before fetch ' );
FETCH tr_stg_cur
INTO tr_stg_rec;
EXIT
WHEN tr_stg_cur%NOTFOUND;
-- dbms_output.put_line('3 after fetch ' ||tr_stg_rec.act_2case );
IF tr_stg_rec.s_title = 'DISP' THEN
l_from_user := tr_stg_rec.act_2user;
FETCH tr_stg_cur INTO tr_stg_rec_nxt;
IF tr_stg_cur%NOTFOUND THEN
----- Insert for DISP Record ------.
l_actn := 'DISP';
l_f_user := f_key(l_from_user);
BEGIN
INSERT
INTO Table_Fact
(
case_key,
from_user_key,
to_user_key,
actn
)
VALUES
(
Table_Fact_seq.NEXTVAL,
l_f_user,
l_f_user,
l_actn
);
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
END;
GOTO END_LOOP;
END IF;
IF tr_stg_rec_nxt.s_title = 'ACC' THEN
l_to_user := tr_stg_rec_nxt.act_2user;
l_actn := 'DISP/ACC';
l_f_user := f_key(l_from_user);
l_ft_user := f_key(l_to_user);
BEGIN
INSERT
INTO Table_Fact
(
case_key,
from_user_key,
to_user_key,
actn
)
VALUES
(
Table_Fact_seq.NEXTVAL,
l_f_user,
l_ft_user,
l_actn
);
v_pr_from_user := l_from_user;
v_pr_to_user := l_to_user;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SUBSTR(SQLERRM, 1, 350);
END;
ELSIF tr_stg_rec_nxt.s_title <> 'ACC' THEN
l_to_user := tr_stg_rec_nxt.act_2user;
l_actn := 'DISP';
l_f_user := f_key(l_from_user);
l_ft_user := f_key(l_to_user);
BEGIN
INSERT
INTO Table_Fact
(
case_key,
from_user_key,
to_user_key,
actn
)
VALUES
(
Table_Fact_seq.NEXTVAL,
l_f_user,
l_ft_user,
l_actn
);
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
END;
IF tr_stg_rec_nxt.s_title = 'ASSG' THEN
l_from_user := tr_stg_rec_nxt.act_2user;
l_to_login_name := TRIM(REPLACE(SUBSTR(tr_stg_rec_nxt.addnl_info, INSTR(UPPER(tr_stg_rec_nxt.addnl_info), 'TO',1,1)+2, INSTR(UPPER(tr_stg_rec_nxt.addnl_info), ',',1,1) -
INSTR(UPPER(tr_stg_rec_nxt.addnl_info), 'TO',1,1)), ',', ''));
l_to_user := F_GET_USER_OBJID(l_to_login_name);
l_actn := 'ASSG';
l_f_user := f_key(l_from_user);
l_ft_user := f_key(l_to_user);
BEGIN
INSERT
INTO Table_Fact
(
case_key,
from_user_key,
to_user_key,
actn
)
VALUES
(
Table_Fact_seq.NEXTVAL,
l_f_user,
l_ft_user,
l_actn
);
v_pr_from_user := l_from_user;
v_pr_to_user := l_to_user;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
END;
END IF;
ELSIF tr_stg_rec.s_title <> 'DISP' THEN
IF tr_stg_rec.s_title = 'ASSG' THEN
l_from_user := tr_stg_rec.act_2user;
l_to_login_name := TRIM(REPLACE(SUBSTR(tr_stg_rec.addnl_info, INSTR(UPPER(tr_stg_rec.addnl_info), 'TO',1,1)+2, INSTR(UPPER(tr_stg_rec.addnl_info), ',',1,1) -
INSTR(UPPER(tr_stg_rec.addnl_info), 'TO',1,1)), ',', ''));
l_to_user := F_GET_USER_OBJID(l_to_login_name);
l_f_user := f_key(l_from_user);
l_ft_user := f_key(l_to_user);
l_actn := 'ASSG';
BEGIN
INSERT
INTO Table_Fact
(
case_key,
from_user_key,
to_user_key,
actn
)
VALUES
(
Table_Fact_seq.NEXTVAL,
l_f_user,
l_ft_user,
l_actn
);
v_pr_from_user := l_from_user;
v_pr_to_user := l_to_user;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
END;
ELSIF tr_stg_rec.s_title = 'RET' THEN
l_to_user := v_pr_from_user;
l_from_user := tr_stg_rec.act_2user;
l_f_user := f_key(l_from_user);
l_ft_user := f_key(l_to_user);
l_actn := 'RET';
BEGIN
INSERT
INTO Table_Fact
(
case_key,
from_user_key,
to_user_key,
actn
)
VALUES
(
Table_Fact_seq.NEXTVAL,
l_f_user,
l_ft_user,
l_actn
);
v_pr_from_user := l_from_user;
v_pr_to_user := l_to_user;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
END;
END IF;
END IF;
END LOOP; -- tr_stg_cur --.
CLOSE tr_stg_cur;
COMMIT;
END LOOP; -- case_tr_cur --
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
END Table_Fact_prc;
[Updated on: Mon, 29 June 2015 04:09] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Optimize PL/SQL code and get rid of one by one If then else statement check [message #639049 is a reply to message #639026] |
Tue, 30 June 2015 02:48  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
What else has been altered? If you're going to change the code that you supply at least make sure that it is syntactically correct.
The first thing that you need to do is to create a set of requirements. I haven't taken a huge amount of time to look at your code (I pretty much stopped when I saw the syntax errors) but the knee jerk reaction for this sort of thing is that it can probably be done in pure SQL. That means that you're going to write an entirely new piece of code. Start from scratch, get that set of requirements that clearly state what the end result should be for the process. Write the SQL from those requirements.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:20:18 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
