How to put SQL update into a procedure

[raygg]

I have a sql procedure that runs too long and I need to put it into a procedure so I can do a commit every 10000 rows or so. Does someone have a sample set of code to do this?

Here's the code:
INSERT INTO emp_0 (
SELECT
older.emp_id,
older.emp_time_stamp,
min(newer.emp_time_stamp),
newer.record_expired
FROM
emp_t older, emp_t newer
WHERE
older.emp_id = newer.emp_id and
older.emp_time_stamp < emp_time_stamp and
older.record_expired is null and
newer.record_updated = 'N'
GROUP BY
older.emp_id,
oleder.emp_time_stamp,
newer.record_expired);

 

[Kenrae]

You can try that:

CREATE OR REPLACE PROCEDURE name IS

CURSOR curEmp_t IS
  SELECT
  older.emp_id,
  older.emp_time_stamp,
  min(newer.emp_time_stamp),
  newer.record_expired
  FROM
   emp_t older, emp_t newer
  WHERE
  older.emp_id = newer.emp_id and
  older.emp_time_stamp < emp_time_stamp and
  older.record_expired is null and
  newer.record_updated = 'N'
  GROUP BY
  older.emp_id,
  oleder.emp_time_stamp,
  newer.record_expired;

i NUMBER;

BEGIN

i:=0;
OPEN curEmp_t;
LOOP
  FETCH curEmp_t INTO ... (query vars);
EXIT WHEN curEmp_t%NotFound;
  INSERT INTO emp_0(query vars);
  IF i=10000 THEN
    commit;
    i=0;
  END IF;
END LOOP;
CLOSE curEmp_t;

EXCEPTION WHEN OTHERS THEN
  (treat your exceptions)

END;

 

[raygg]

I apologize for my ignorance but I am not an experienced plsql person.

Do I code a record name - like 'emp_rec' where you have
FETCH curEmp_t INTO ... (query vars);
as
FETCH curEmp_t INTO emp_rec%TYPE);

The line
INSERT INTO emp_0(query vars);
is to insert to contents of emp_t row into the emp_0 table so I am not sure what to code for
FETCH curEmp_t INTO ... (query vars);

Ray

 

[Kenrae]

Oh, yes, those query vars are procedure variables. Like that:

PROCEDURE ...

CURSOR ...

i    NUMBER;
vRec emp_rec%type;

BEGIN
  (...)
  FETCH curEmp_t INTO vRec;
  (...)
  INSERT INTO emp_0(vRec);
END;

 

[raygg]

I can't figure out this error - please help.

SQL> CREATE OR REPLACE PROCEDURE RGW_UPDATE01 IS
2
3 CURSOR cur_emp_t IS
4 (SELECT
5 older.emp_id,
6 older.emp_time_stamp,
7 min(newer.emp_time_stamp),
8 newer.record_expired
9 FROM
10 emp_t older, emp_t newer
11 WHERE
12 older.emp_id = newer.emp_id and
13 older.emp_time_stamp < emp_time_stamp and
14 older.record_expired is null and
15 newer.record_updated = 'N'
16 GROUP BY
17 older.emp_id,
18 older.emp_time_stamp,
19 newer.record_expired
20 );
21
22 i NUMBER;
23
24 BEGIN
25
26 i:=0;
27 OPEN cur_emp_t;
28 LOOP
29 FETCH cur_emp_t INTO emp_rec%TYPE;
30 EXIT WHEN cur_emp_t%NOTFOUND;
31 INSERT INTO emp_0(emp_rec);
32 IF i := 100 THEN
33 commit;
34 i := 0;
35 END IF;
36 END LOOP;
37 CLOSE cur_emp_t;
38
39 --EXCEPTION WHEN OTHERS THEN
40 -- (treat your exceptions)
41
42 END;
43 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE RGW_UPDATE01:

LINE/COL ERROR
-------- -----------------------------------------------------------------
31/29 PLS-00103: Encountered the symbol &quot;;&quot; when expecting one of the
following:
( select values

 

[listener22]

Although in an insert method,
order by is not needed... Jim

oracle, vb

 

[carp]

raygg -
It's not a very good error message.
I think the following code should work for you.
CREATE OR REPLACE PROCEDURE RGW_UPDATE01 IS
2
3 CURSOR cur_emp_t IS
4 (SELECT
5 older.emp_id,
6 older.emp_time_stamp,
7 min(newer.emp_time_stamp) min_time_stamp,
8 newer.record_expired
9 FROM
10 emp_t older, emp_t newer
11 WHERE
12 older.emp_id = newer.emp_id and
13 older.emp_time_stamp < emp_time_stamp and
14 older.record_expired is null and
15 newer.record_updated = 'N'
16 GROUP BY
17 older.emp_id,
18 older.emp_time_stamp,
19 newer.record_expired
20 );

21 emp_rec cur_emp_t%ROWTYPE;

22 i NUMBER;
23
24 BEGIN
25
26 i:=0;
27 OPEN cur_emp_t;
28 LOOP
29 FETCH cur_emp_t INTO emp_rec;
30 EXIT WHEN cur_emp_t%NOTFOUND;
31 INSERT INTO emp_0 (emp_id,end_time_stamp,min_time_stamp,record_expired)
VALUES (emp_rec.emp_id, emp_rec.end_time_stamp,emp_rec.min_time_stamp,emp_rec.record_expired);
32 IF i := 100 THEN
33 commit;
34 i := 0;
35 END IF;
36 END LOOP;
37 CLOSE cur_emp_t;
38
39 --EXCEPTION WHEN OTHERS THEN
40 -- (treat your exceptions)
41
42 END;
43 /

 

[raygg]

I had to add i = i+1; but it finally worked. Thanks for your help.

 

[Kenrae]

Hey!, it was a code made in a minute, anyone can have some syntax errors ;-)

 

[raygg]

I did not mean to be critical - this has really helped because I am inexperienced in plsql procedures. Please tell me something - hwen am I supposed to add the '/' at the end? Do I include it in the block of code when I paste it onto sqlplus, or do I exclude it, do the copy and paste, and then add it?

 

[Kenrae]

Wherever you want (except inside a procedure).
This '/' is a commit, it compiles the above procedure/s. You can put it after every procedure (recommended) or whenever you want.
By the way, don't you have procedure builder? It's an Oracle application useful for procedure editing.
Mmm, errr, well, TOAD is a *very much* useful application for Oracle developers and DBAs, far better than any Oracle application. (I haven't said it ;-)).

 

[raygg]

I asked about the '/' - because if I include it with the script and paste the script into sqlplus, nothing happens. But if I paste in the script and then enter the '/' it commits and comes back with a message that the procedure has errors or compiled.
I have TOAD.

 

[Kenrae]

But, what do you want to do? compile the procedure or execute it?
If you want to execute it, you must compile it first (seems you have already done it), and then do into sqlplus:

execute RGW_UPDATE01;

 

[raygg]

Run it. I am a DBA. Where do I find procedure builder?
Ray