| Function [message #1064] |
Tue, 26 March 2002 13:16  |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
I need to create a function that will return a value based on the status field in my table. So, let's say I have cust_id = 1, site_id = 2 and status = 'C'
I want to return 1 for C. There are three possibilities for status. Here is my code which is not compiling - any thoughts?
CREATE or REPLACE FUNCTION customers
(custid_in IN varchar2,
siteid_in IN varchar2)
RETURN Number is
returnValue Number(1);
BEGIN
returnValue := NULL;
BEGIN
SELECT STATUS from customers
where cust_id = custid_in
and site_id = siteid_in;
IF status = 'C' then returnValue := 1;
ELSIF status = '?' then returnValue := 2;
ELSIF status = 'P' then returnValue := 3;
End IF;
End;
/
Thank you
|
|
|
|
| Re: Function [message #1065 is a reply to message #1064] |
Tue, 26 March 2002 14:06  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
1) You won't be able to name this function 'Customers', since that is the name of your table.
2) A SELECT in PL/SQL needs to be INTO a variable of some type.
3) I would recommend using DECODE to translate the status into a number in the SELECT statement itself instead of extra IF/THEN logic afterward.
4) Exception handling will cover those (potential) cases where the row is not found or an unexpected error occurs.
5) To see why something won't compile in SQL*Plus, type 'show errors' after the compile.
CREATE or REPLACE FUNCTION customer_status (
custid_in IN customers.cust_id%type,
siteid_in IN customers.site_id%type)
RETURN Number
is
returnValue Number(1);
begin
select decode(status, 'C', 1, '?', 2, 'P', 3, 0)
into returnValue
from customers
where cust_id = custid_in
and site_id = siteid_in;
return (returnValue);
exception
when no_data_found then
return (-1);
when others then
return (-99);
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
