Help on Stored Procedures 1

[mynwo]

Hi All,

I need some help on stored procedures. I need to write procedures to generate autogenerated numbers. Also how can we make use of cursors in stored procedures. It would be great if you could give some examples. Thanks in advance.

Best Regards,

lloyd

 

[vpshriyan]

Hi,

If you mean "generate autogenerated numbers" by able to get the sequence number generated for a serial data type of a column; then the short procedure below puts light on that.

CREATE PROCEDURE GetSerialNumber() RETURNING integer;
RETURN DBINFO('sqlca.sqlerrd1');
END PROCEDURE

The following sample SP demonstrates how a cursor can be used for sql manipulations (updates and deletes).

CREATE PROCEDURE foreach_example()
DEFINE i, j INT;

FOREACH cursor1 FOR SELECT col1, col2 INTO i, j FROM tab1
IF j > 500 THEN
DELETE FROM tab1 WHERE CURRENT OF cursor1;
CONTINUE FOREACH;
END IF
UPDATE tab1 SET col1 = col1 + 10 WHERE CURRENT OF cur1;
END FOREACH

END PROCEDURE

Regards,
Shriyan

"If you have knowledge, let others light their candles with it."

 

[mynwo]

Hi Shriyan,

Thanks for your prompt reply. How could i call the GetSerialNumber() procedure in my front-end and assign a unique number. For eg. i have a customer table & i enter data into it, then while inserting this data into my table, automatically the serial no should be inserted too, how will i call this procedure in my program. Pls. advice.

Best Regards,

lloyd

 

[vpshriyan]

Hi lloyd,

I think there is some confusion, with regard to serial datatype and its manipulation. While inserting a row in a table having serial datatype field, one need to pass 0 (zero) as a place holder for the data, in order to generate the sequence number by the database server.

Demo Customer table definition:
create table customer
(
customer_num serial(101),
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
);

Insert Statement Example:
INSERT INTO customer VALUES (0,'Ravi','Kumar','ABC Ltd', '213, SV Road', 'Santacruz West', 'Mumbai', 'MH', '054', '091-022-1234');

To know the customer number generated for the above insert statement you may execute SQL:
SELECT DBINFO('sqlca.sqlerrd1') FROM systables WHERE tabid=1 ;

The above select statement is equivalent to SP:
CREATE PROCEDURE GetSerialNumber() RETURNING integer;
RETURN DBINFO('sqlca.sqlerrd1');
END PROCEDURE

To call the above SP execute SQL:
EXECUTE PROCEDURE GetSerialNumber()

Regards,
Shriyan

"Man who knows, knows, he knows nothing!"

 

[mynwo]

Hi Shriyan,

Thanks once again for you prompt reply.

Best Regards,

lloyd