Home » SQL & PL/SQL » SQL & PL/SQL » Without temp table senario
Without temp table senario [message #393533] |
Mon, 23 March 2009 04:21  |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
I have a scenario and procedure below by using the temp table but my need is Without using the temp table i want to execute the below procedure i used collections but i am new to collections and records so i am confused about that.
The scenario is i have a cursor which selects data from one table and it will insert the selected records into collection of table(as of now temp table) and manipulate the amount column value and update the manipulated value temporarily in collection table,(as of now temp table).It should not affect the corresponding column of database table.The main purpose of updating the new manipulated value is ,i can use that same value another time for processing in some other iteration with new update value.
My problem is how to update the manipulated amount value and how to select that manipulated value in another iteration of process temporarily.
Below is my testing procedure with built-in tables.In this given procedure i was confused how to replace the collection by temp table temp_empexpn_bal.
Anyone helped out for my requirement.
create table empl
(
empid varchar2(10),
age number,
sex varchar2(1),
grade varchar2(2),
salary number(30,2),
dept varchar2(10),
constraint PK_EMPSAL primary key (empid))
create table expn
(
expnid varchar2(10),
amt number(30,2),
reason varchar2(200),
emp_limit number,
emp_coverage number,
status varchar2(20)
)
create table emp_expn_map
(
empid varchar2(10),
expnid varchar2(10)
)
create table temp_empexpn_bal
(
emp_id varchar2(10),
bal_amt number(30,2)
)
CREATE OR REPLACE PROCEDURE pro_emp_bal
AS
v_lmp_le_id VARCHAR2 (19);
v_spec_cnt NUMBER;
v_spec_sec_id VARCHAR2 (10);
v_balamt NUMBER;
v_check_balamt NUMBER;
v_req_amt NUMBER;
v_allocated_amt NUMBER;
v_sf_diff_val NUMBER;
v_sf_balamt NUMBER;
in_allocat_amt NUMBER := 0;
Type temp_emp_bal is record
( empid varchar2(10),
amt number(30,2)
);
TYPE Temp_balamt IS TABLE OF temp_emp_bal index by pls_integer;
Tempbalamt Temp_balamt;
CURSOR cur_bal_amt
IS
SELECT empid,salary
FROM empl;
BEGIN
EXECUTE IMMEDIATE 'Delete from temp_empexpn_bal';
COMMIT;
OPEN cur_bal_amt;
FETCH cur_bal_amt BULK COLLECT INTO Tempbalamt;
FOR l IN Tempbalamt.FIRST .. Tempbalamt.LAST
LOOP
INSERT INTO temp_empexpn_bal
(emp_id,
bal_amt
)
VALUES (Tempbalamt(l).empid,
Tempbalamt(l).amt
);
END LOOP;
DECLARE
CURSOR cur_expn_dtls
IS
SELECT expnid,amt,reason,emp_limit,emp_coverage
FROM expn
WHERE UPPER (status) = 'ACTIVE'
ORDER BY expnid;
TYPE reclist IS TABLE OF cur_expn_dtls%ROWTYPE;
recs reclist;
BEGIN
OPEN cur_expn_dtls;
FETCH cur_expn_dtls BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
DECLARE
CURSOR emp_expn_map
IS
SELECT COUNT (a.empid) empcnt, a.empid
FROM emp_expn_map a,
(SELECT empid
FROM emp_expn_map
WHERE expnid = recs (i).expnid) b
WHERE a.empid = b.empid
GROUP BY a.empid
ORDER BY empcnt, a.empid;
TYPE seclist IS TABLE OF emp_expn_map%ROWTYPE;
secs seclist;
BEGIN
v_sf_diff_val := 0;
OPEN emp_expn_map;
FETCH emp_expn_map BULK COLLECT INTO secs;
FOR j IN secs.FIRST .. secs.LAST
LOOP
v_balamt := 0;
v_check_balamt := 0;
BEGIN
SELECT NVL (bal_amt, 0)
INTO v_balamt
FROM temp_empexpn_bal
WHERE emp_id = secs (j).empid;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
IF ((v_balamt IS NOT NULL) AND (v_balamt > 0)) then
v_req_amt :=( NVL (recs (i).emp_limit, 0)* NVL (recs (i).emp_coverage, 0)/ 100)- NVL (v_sf_diff_val, 0);
/* calculation for allocated CMV*/
IF (v_balamt >= v_req_amt)
THEN
DBMS_OUTPUT.put_line ('Inside BSV>RAV');
IF ((secs.COUNT = 1) AND (secs (j).empcnt = 1))
THEN
v_allocated_amt := v_req_amt;
in_allocat_amt :=
NVL (v_balamt, 0)
- NVL (v_allocated_amt, 0);
ELSE
v_allocated_amt := LEAST (v_req_amt, v_balamt);
in_allocat_amt :=
NVL (v_balamt, 0)
- NVL (v_allocated_amt, 0);
END IF;
UPDATE temp_empexpn_bal
SET bal_amt = NVL (v_balamt, 0)
- NVL (v_allocated_amt, 0)
WHERE emp_id = secs (j).empid;
EXIT;
ELSE
v_sf_balamt := NVL (v_balamt, 0) - NVL (v_req_amt, 0);
v_sf_diff_val := NVL (ABS (v_sf_balamt), 0);
IF (j = secs.LAST)
THEN
UPDATE temp_empexpn_bal
SET bal_amt = NVL (bal_amt, 0) - NVL (bal_amt, 0)
WHERE emp_id = secs (j).empid;
END IF;
END IF;
ELSE
DBMS_OUTPUT.put_line ('Balance of balance');
END IF;
END LOOP;
COMMIT;
END;
END LOOP;
END;
END pro_emp_bal;
/
[Updated on: Mon, 23 March 2009 04:29] Report message to a moderator
|
|
|
|
|
|
Re: Without temp table senario [message #394779 is a reply to message #393533] |
Mon, 30 March 2009 05:34  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If I were you, I'd replace your definition TYPE Temp_balamt IS TABLE OF temp_emp_bal index by pls_integer; with TYPE Temp_balamt IS TABLE OF number(30,2) index by varchar2(10);
You can then reference the balance amounts in this table explicitly by using the EMPID as the index - ie to get at the balance for empid='FRED', you just refer to TempBalAmt('FRED')
Also, and unrelated - you don't need to use EXECUTE IMMEDIATE to do a DELETE.
|
|
|
Goto Forum:
Current Time: Thu Feb 13 23:47:07 CST 2025
|