Thanks for the question, Patibandla.
Asked: April 13, 2001 - 12:08 am UTC
Last updated: April 13, 2001 - 12:08 am UTC
Version: 8.1.6.0
Viewed 1000+ times
You Asked
I have sql statement like this
SELECT
FIND_PRODUCT(A.SM_PSC_ID,A.SM_PSC_TYPE) ,
FIND_BARCODE(A.SM_PSC_ID,A.SM_PSC_TYPE) ,
A.SM_PRODSRLNO,
FIND_SKU(A.SM_PSC_ID,A.SM_PSC_TYPE) ,
A.SM_ACTIVITY,
A.SM_FULFILL_STATUS,
SM_FULFILL_DESC,
A.SM_OBJECTID
FROM SM_PRODMOVEMENT A
WHERE A.SM_OBJECTTYPE='WORK ORDER'
AND A.SM_STATUS_TYPE='RECEIVE'
AND A.SM_ISCURREC(+)='Y'
the function is something like this
FUNCTION Find_Product(
M_PSC_ID IN NUMBER,
M_PSC_TYPE IN VARCHAR2)
RETURN VARCHAR2
IS
M_PROD_NAME VARCHAR2(200):=' ';
MPSC_TYPE VARCHAR2(50);
BEGIN
MPSC_TYPE:=UPPER(M_PSC_TYPE);
IF MPSC_TYPE='PRODUCTS' OR M_PSC_TYPE='PRODUCT' THEN
SELECT SM_PROD_NAME INTO M_PROD_NAME FROM SM_PRODUCTS WHERE SM_PRODSRLID=M_PSC_ID AND ROWNUM<2;
ELSIF MPSC_TYPE='SPARES' OR M_PSC_TYPE='SPARE' THEN
SELECT SM_SPARENAME INTO M_PROD_NAME FROM SM_SPAREPARTS WHERE SM_SPARESRLID=M_PSC_ID AND ROWNUM<2;
ELSIF MPSC_TYPE='SPARE PART UNIT' THEN
SELECT SM_SPARENAME INTO M_PROD_NAME FROM SM_SPAREPARTS WHERE SM_SPARESRLID=M_PSC_ID AND ROWNUM<2;
ELSIF MPSC_TYPE='CUSTOMER UNIT' OR M_PSC_TYPE='VENDOR UNIT' THEN
SELECT SM_SPARE_NAME INTO M_PROD_NAME FROM SM_CUSTPARTS WHERE SM_PSC_ID=M_PSC_ID AND ROWNUM<2;
ELSIF MPSC_TYPE='CSP' THEN
SELECT SM_CSP_TITLE INTO M_PROD_NAME FROM SM_CSP WHERE SM_CSPID=M_PSC_ID AND ROWNUM<2;
END IF;
RETURN TRIM(M_PROD_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
LOG_ERROR;
RETURN TRIM(M_PROD_NAME);
WHEN OTHERS THEN
LOG_ERROR;
RETURN TRIM(M_PROD_NAME);
END Find_Product;
The functions find_barcode and find_sku are also
same as find_product except for change in the column.
i.e. based on the type and id they get the data from
the same table same row (no duplicate rows).
In such a scenario
Are 3 functions individually evaluated
u hit the table thrice for each of the function.
or 3 functions combined evaluated.
Regards
Koshal
and Tom said...
All three functions are individually evaluated, they have to be -- different inputs, different functions, different results.
You hit the table three times.
You could improve this by returning an object type from a SINGLE function. That would avoid the overhead of 2 of the function calls and if you make the single function as efficient as possible, doubling up the work where possible -- it'll run faster over all.
consider:
Here are the three types we want to return, a number, date and string:
ops$tkyte@ORA8I.WORLD> create or replace type my_data as object
2 ( a int, b date, c varchar2(25) )
3 /
Type created.
now we create a function that returns that type -- its just a demo, your logic would have to be put into it of course
ops$tkyte@ORA8I.WORLD> create or replace function f( p_x in number, p_y in number ) return my_data
2 as
3 begin
4 -- your logic here to collect together
5 -- the necessary data
6 return my_data( 1, sysdate, 'hello' );
7 end;
8 /
Function created.
we can now fetch it in SQL
ops$tkyte@ORA8I.WORLD> select dummy, f( 1, 2 ) my_data
2 from dual
3 /
D
-
MY_DATA(A, B, C)
----------------------------------------------------------------------------------------------------
X
MY_DATA(1, '13-APR-01', 'hello')
and if your client cannot deal with OBJECTS, we can turn them into scalars easy enough using an inline view:
ops$tkyte@ORA8I.WORLD> select dummy, a.my_data.a, a.my_data.b, a.my_data.c
2 from ( select dummy, f( 1, 2 ) my_data
3 from dual ) A
4 /
D MY_DATA.A MY_DATA.B MY_DATA.C
- ---------- --------- -------------------------
X 1 13-APR-01 hello
Is this answer out of date? If it is, please let us know via a Comment