Re: Stored Procedure Help

Aaron Rouse wrote:
>
> I am attempting to make a store procedure that will loop over the data
> in my table and prepend some zeros to one columns value, convert it to
> a varchar and then insert the new value in another table. Upon
> running the script I created, my client(Toad) ends up just hanging and
> I eventually end up just ending the task on it. The record set I am
> looping over has 23,411 records, is the procedure just inefficient for
> that many rows or is their a flaw in the syntax I used in creating it?
>
> CREATE OR REPLACE procedure SP_SLB_SAPMERGE AS
> -- Cursor for SAP Org Numbers
> CURSOR SAPORG IS
> SELECT A.NO_EMP, A.NO_EMP_NUSER3
> FROM EMPLOYEES_DTL A, EMPLOYEES B
> WHERE A.NO_EMP_NUSER3 IS NOT NULL
> AND A.NO_EMP = B.NO_EMP
> AND B.NM_LST_UPDT != 'EGATE';
>
> -- For New Org Code
> SAPORG_NEW VARCHAR2(10);
>
> BEGIN
> FOR i IN SAPORG LOOP
> IF LENGTH(i.NO_EMP_NUSER3) = 1 THEN
> SAPORG_NEW := '0000000' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 2 THEN
> SAPORG_NEW := '000000' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 3 THEN
> SAPORG_NEW := '00000' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 4 THEN
> SAPORG_NEW := '0000' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 5 THEN
> SAPORG_NEW := '000' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 6 THEN
> SAPORG_NEW := '00' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 7 THEN
> SAPORG_NEW := '0' || to_char(i.NO_EMP_NUSER3);
> ELSIF LENGTH(i.NO_EMP_NUSER3) = 8 THEN
> SAPORG_NEW := to_char(i.NO_EMP_NUSER3);
> END IF;
>
> -- UPDATE Employees Table
> UPDATE EMPLOYEES
> SET CD_LOC = SAPORG_NEW
> WHERE NO_EMP = i.NO_EMP;
>
> END LOOP;
> COMMIT;
> END SP_SLB_SAPMERGE;
> /

Try the following. It should work just fine. Well... check the num mask in the TO_CHAR -- It has been a while and I do not have a FM with me right now.

CREATE OR REPLACE procedure SP_SLB_SAPMERGE AS
BEGIN
  FOR i IN (SELECT a.no_emp,

                   TO_CHAR(a.no_emp_nuser3,'00000009') AS saporg_new
              FROM employees_dtl a
                   employees     b
             WHERE a.no_emp_urser3 IS NOT NULL
               AND a.no_emp         = b.no_emp
               AND b.nm_lst_updt   != 'EGATE')
  LOOP
     UPDATE employees
        SET cd_loc = i.saporg_new
      WHERE no_emp = i.no_emp;

-- 
Andrew Allen