OraFAQ Forum: SQL & PL/SQL » function values and fun query

Home » SQL & PL/SQL » SQL & PL/SQL » function values and fun query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

function values and fun query [message #4336]

Thu, 28 November 2002 10:47

cormac
Messages: 25
Registered: November 2002

Junior Member

Hi,
I'm very new to this pl/sql stuff and for some personal fun, I want to create a function that will give me an idea of how much I can win at the bookies(bookmakers!)
Can I use '&1'?
Any idea's how to do this better please let me know...
cheers
Cormac--see query below
CREATE OR REPLACE FUNCTION BOOKIE (p_BET in NUMBER ,
p_mul1 in number,p_div1 in number,p_winnings out number)
RETURN NUMBER
IS
cursor c_stake
is select '&1' from dual;
retval number;
e_max_bet exception;
v_bet number;
v_mul1 number;
v_div1 number;
BEGIN
v_bet := p_bet;
v_mul1 := p_mul1;
v_div1 := p_div1;

open c_stake;
if c_stake%found then
fetch c_stake into v_bet;
v_bet := (v_bet*v_mul1)/v_div1;
end if;
close c_stake;
v_bet := p_winnings;
return v_bet;
end;
/

Report message to a moderator

Re: function values and fun query [message #4338 is a reply to message #4336]

Fri, 29 November 2002 00:13

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Why would you use a cursor? DUAL always returns a row, since the table contains one row. You can remove the cursor and its calls.

The statement 'v_bet := p_winnings' will fail: p_winnings is defined being an OUT parameter, which can be used as the receptor of an assignment, but not as the source.

I assume this is a work in progress since you define retval and an exception which aren't used in the function.

MHE

Report message to a moderator

Re: function values and fun query [message #4340 is a reply to message #4338]

Fri, 29 November 2002 03:01

cormac
Messages: 25
Registered: November 2002

Junior Member

MME,
yes, it is work in progress. I intend to use the exception later: I want to set a maximum stake!
As I said, I'm new to this and this is a fun, but useful way of getting my head around these pl/sql functions.
I changed it to this (no cursor with dual):
It compiles ok, but how do you it to return a value?

CREATE OR REPLACE FUNCTION BOOKIE (p_BET in NUMBER ,
p_mul1 in number,p_div1 in number,p_winn out number)
RETURN NUMBER
IS
WINNINGS NUMBER(30);
e_max_bet exception;
v_bet number;
v_mul1 number;
v_div1 number;
v_winnings number;
BEGIN
v_bet := p_bet;
v_mul1 := p_mul1;
v_div1 := p_div1;
v_winnings := winnings;

v_bet := (v_bet*v_mul1)/v_div1;

v_bet := v_winnings;
return v_bet;
end;
/

Report message to a moderator

Re: function values and fun query [message #4347 is a reply to message #4338]

Sat, 30 November 2002 15:42

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Well...let's see. What do you want exactly? For now, the function will return v_bet, which contains the value of 'winnings'. So, your calculation will be lost. Don't you want to do sth with your calculation?

To 'run' the function, you call it from PL/SQL or plain SQL like:

SELECT my_function(par_1,par_2) "Fun"
  FROM dual;

BEGIN
  dbms_output.put_line('My function: '||my_function(par_1,par_2));
END;
/

HTH,
MHE

Report message to a moderator

Re: function values and fun query [message #4355 is a reply to message #4338]

Mon, 02 December 2002 02:55

cormac
Messages: 25
Registered: November 2002

Junior Member

Maaher,
thanks for that.
I did want to see the value returned!
This is exactly the sort of thing I needed.
Cheers
Cormac

Report message to a moderator

Previous Topic:	SQL Query - Urgent
Next Topic:	Emp,Dept--Doubt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 08:09:18 CDT 2024