Home » SQL & PL/SQL » SQL & PL/SQL » Without temp table senario
Without temp table senario [message #393533] Mon, 23 March 2009 04:21 Go to next message
ORAGENASHOK
Messages: 238
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 #394202 is a reply to message #393533] Thu, 26 March 2009 01:07 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
If the current code works fine while using temporary table then why do you want to change it to use plsql table (associative array).

[Updated on: Thu, 26 March 2009 01:07]

Report message to a moderator

Re: Without temp table senario [message #394643 is a reply to message #394202] Sun, 29 March 2009 03:14 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I am not have priviledge to create temp table.
Re: Without temp table senario [message #394655 is a reply to message #393533] Sun, 29 March 2009 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I am not have priviledge to create temp table.
Then how did you test & validate posted code?

>SELECT COUNT (a.empid) empcnt, a.empid
What is empcnt & from where does it originate?
Re: Without temp table senario [message #394779 is a reply to message #393533] Mon, 30 March 2009 05:34 Go to previous message
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.
Previous Topic: Updating status
Next Topic: Same Conect By query works fine in 9i but not in 10g - Please help
Goto Forum:
  


Current Time: Wed Dec 07 10:48:20 CST 2016

Total time taken to generate the page: 0.12109 seconds