on 01-17-2011 8:18 PM
Hi MaxDB Experts,
Can anyone give me an example of a stored procedure that does not use a cursor? I am just trying to understand how to do something simple for testing purposes. All I can find is one rather complicated for a newbie example in the documentation and I have had no success locating samples via google searching.
Any help is much appreciated!
Thanks,
Diana Hoppe
>
> Hi MaxDB Experts,
>
> Can anyone give me an example of a stored procedure that does not use a cursor? I am just trying to understand how to do something simple for testing purposes. All I can find is one rather complicated for a newbie example in the documentation and I have had no success locating samples via google searching.
HI Diana,
yes, this chapter is not too well documented, right.
Anyhow, although a stored procedure without a cursor barely seems to make any sense in most cases, here comes the wanted example:
create DBPROC mona.use_no_cursor (IN hotel_no fixed(4), IN hotel_name char(50) ) as
UPDATE HOTEL.HOTEL
SET NAME = :hotel_name
WHERE HNO = :hotel_no;
Ok, this super-duper-clever-as-hell procedure is a kind of application interface to change the name of a hotel.
Provide the hotel number and the new name of it and the procedure will 'magically' perform the change.
As you see, I use the standard demo schema HOTEL/MONA here, so make sure to have installed if you want to copy/paste this example!
Let's check it:
Select * from hotel.hotel where hno=50
-- HNO;NAME;ZIP;ADDRESS;INFO;
-- 50;Lake Michigan;60601;354 OAK Terrace;?
Ok, Lake Michigan is not that creative for a Hotels name, is it?
Let's go 'pimp it up'
call use_no_cursor (50, 'Lake M Lodge')
Select * from hotel.hotel where hno=50
-- HNO;NAME;ZIP;ADDRESS;INFO;
-- 50;Lake M Lodge;60601;354 OAK Terrace;?
WOW - pure magic in the hands of mere mortals... hahaha..
Enough kiddin'! Hope this is what you where looking for.
Have fun,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Lars
Thanks for the example, I'm saving that as a learning tool! I've been trying to apply it to my test procedure. Problem is, and I should have mentioned this, I need to return a single value. I found an example on the MySQL website and just changed a couple things to fit my needs:
CREATE PROCEDURE order_tot_amt @o_id int, @p_tot int output AS
SELECT @p_tot = sum(VAL1 * VAL2)
FROM MYTEST
WHERE ID = o_id
I am trying to tweek this to work in MaxDB if possible. I guess what I'm not sure about is do I need a cursor to return a single value? I can do things in MSSQL, but I'm finding the documentation for MaxDB is so sparse on this topic, usually I can figure these things out. I really appreciate any suggestions you can give, your MaxDB suggestions in the past have been very helpful and I certainly wouldn't be this far without them!
Kind Regards,
Diana
Oh bloody hell, I really should check my spelling before hitting the "Post Message" button...
Anyway, let's see:
You want to avoid the cursor declaration for simple statements?
No problem!
I simply took the example procedure from the tutorial in the documentation [here|http://maxdb.sap.com/doc/7_8/44/e2497ff8bd03fee10000000a1553f6/content.htm]:
CREATE DBPROC hotel.avg_price (IN zip CHAR(5), OUT avg_price FIXED(6,2)) AS
VAR sum FIXED(10,2); price FIXED(6,2); hotels INTEGER;
TRY
SET sum = 0; SET hotels = 0;
DECLARE dbproccursor CURSOR FOR
SELECT price FROM hotel.room,hotel.hotel WHERE zip = :zip AND
room.hno = hotel.hno AND type = 'single';
WHILE $rc = 0 DO BEGIN
FETCH dbproccursor INTO :price;
SET sum = sum + price;
SET hotels = hotels + 1;
END;
CATCH
IF $rc <> 100 THEN STOP ($rc, 'unexpected error');
CLOSE dbproccursor;
IF hotels > 0 THEN SET avg_price = sum / hotels
ELSE STOP (100, 'no hotel found');
If you look at it you'll find it to be a home-grown average function for hotel prices.
As this wouldn't make sense to implement yourself (since MaxDB provides an AVG function for SQL queries), let's see how we can change this to an easier, cursor-less version:
CREATE DBPROC hotel.avg_price_no_cursor (IN zip CHAR(5), OUT avg_price FIXED(6,2)) AS
TRY
SELECT avg(price) INTO :avg_price
FROM hotel.room,hotel.hotel
WHERE zip = :zip
AND room.hno = hotel.hno
AND type = 'single';
CATCH
IF $rc <> 100 THEN
STOP ($rc, 'unexpected error')
ELSE
STOP (100, 'no hotel found');
There we go!
Orignial procedure:
call hotel.avg_price( 60601, :avg_price)
Out(1)
------
84.00
Changed procedure:
call hotel.avg_price_no_cursor( 60601, :avg_price)
Out(1)
------
84.00
Looks close to me
regards,
Lars
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.