Stored Procedure - Cursor

[campbere]

Please excuse my ignorance on this subject but I don't have much experience with stored procedures or cursors.

I have built a cursor which I would like to put into a stored procedure. Can this be done? The reason I want to put it into a stored procedure is because I want to be able to call it from a VB application. Can someone help walk me throught this.

Below is the cursor I wrote. I am basically taking the issue column and parsing it out into two new columns new_issue and spi.

Declare
int_length integer(20);
val_spi varchar2(10);
val_new_issue varchar2(10);

CURSOR order_issue IS
SELECT Issue, New_Issue, SPI FROM snapshot
FOR UPDATE OF New_Issue, SPI;

BEGIN
FOR item_rec IN order_issue LOOP

int_length = len(item_rec.issue)

/* The issue is only parsed out if it is greater than 4 characters. Also if it contains a hyphen,
or the letters A or B it is not parsed out. Instead the value inside issue is used to
update the new_issue column*/
IF int_length >= '4' THEN
IF item_rec.issue like '%-%' THEN
UPDATE snapshot SET New_Issue = item_rec.issue
WHERE CURRENT OF order_issue;
ELSEIF item_rec.issue like '%A%' THEN
UPDATE snapshot SET New_Issue = item_rec.issue
WHERE CURRENT OF order_issue;
ELSEIF item_rec.issue like '%B%' THEN
UPDATE snapshot SET New_Issue = item_rec.issue
WHERE CURRENT OF order_issue;
ELSE
/*This statement grabs the last four characters of the issue field and then
it is set to val_spi. That value is then used to update the spi column. */
val_spi = mid(item_rec.issue, (int_length - 3), int_length)

UPDATE snapshot SET SPI = val_spi
WHERE CURRENT OF order_issue;

/*This statement grabs the first three characters of the issue field and then
it is set to val_new_issue. That value is then used to update the new_issue column. */
val_new_issue = mid(item_rec.issue, 1, (int_length - 4)
UPDATE snapshot SET New_Issue = val_new_issue
WHERE CURRENT OF order_issue;
END IF;
ELSE
UPDATE snapshot SET New_Issue = item_rec.issue
WHERE CURRENT OF order_issue;
END IF;
END LOOP;

COMMIT;

CLOSE order_issue;
END;

 

[MikeJones]

Change the tpo line (Declare) to...

CREATE OR REPLACE PROCEDURE my_proc IS

when you run it it should say procedure created.

you can then exec the program from sqlplus.

i.e.

exec my_proc