Home » SQL & PL/SQL » SQL & PL/SQL » Trying to create a Counter for the number of inserts run in a block (Oracle 10g)
Trying to create a Counter for the number of inserts run in a block [message #444129] Thu, 18 February 2010 15:35 Go to next message
memphis
Messages: 1
Registered: February 2010
Location: England
Junior Member
Hi All,

I have a plsql Proc, which accepts a few parameters and inevitably loops through a cursor and runs a bunch of insert statements. With quite a few IF conditions.

Each insert statement has a value which i want to increment by (+1) every time an insert statement is executed in the same loop.. This is for a student housing database and this is for their room preferences so 1 is the first, 2 is there second preference e.t.c.

Please take a look at the code below: in the Insert values() I have put a? Where I want the number to increment from.
There are a lot more inserts which I haven't put below. I hope I have made myself clear as this has been quite difficult to explain. So for example if the 2nd two inserts are run, then I was the first one to insert with a 1 and the second with a 2.

BEGIN

FOR rec IN c1
LOOP

IF c1%FOUND THEN

INSERT INTO table (PK_A, fk_rms_id, application_type, application_person_type) VALUES (NULL, rec.pk_rms_id, app_type, app_person_type) RETURNING PK_APPLICATION_NO INTO x;

--

IF pref_campus_0 = 'g' THEN
IF pref_room_0 ='0' THEN
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_community) VALUES(x, ?, 'g');
ELSE

IF pref_room_0 !='0' THEN
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, pref_room_0);
END IF;

END IF;
END IF;
--
IF pref_campus_0 = 'a' THEN
IF pref_room_0 ='0' THEN
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_community) VALUES(x, ?, 'a');
ELSE
IF pref_room_0 = 'AH1' THEN
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, 'ARA');
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, 'BOL');
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, 'SEY');
ELSE

IF pref_room_0 = 'AH2' THEN
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, 'PAR');
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, 'CLE');
INSERT INTO r.appl_t_room_preferences (CK_APPLICATION_NO, ck_preference_no, fk_building_id) VALUES(x, ?, 'HOW');
END IF;
END IF;
END IF;

END IF;

many thanks




CM: added code tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.

[Updated on: Thu, 18 February 2010 15:47] by Moderator

Report message to a moderator

Re: Trying to create a Counter for the number of inserts run in a block [message #444132 is a reply to message #444129] Thu, 18 February 2010 15:54 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Declare number variable.
Set it to 1 at the start of the loop
Increment by 1 after every insert.

There's no clever trick for this, you've just got to do it manually.

The cursor%found check is pointless by the way, cursor for loops only every execute the code they contain if the cursor is found by definition.
Re: Trying to create a Counter for the number of inserts run in a block [message #444133 is a reply to message #444129] Thu, 18 February 2010 15:55 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
CNT := CNT + 1;

place as many times as desired in procedure.
Previous Topic: how to check if primary key exists
Next Topic: Option for Temp table
Goto Forum:
  


Current Time: Wed Sep 28 21:11:51 CDT 2016

Total time taken to generate the page: 0.08897 seconds