Home » SQL & PL/SQL » SQL & PL/SQL » calling a function from a proc
calling a function from a proc [message #188632] Mon, 21 August 2006 01:48 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
I have a package as mentioned below:

CREATE OR REPLACE PACKAGE BODY PCK_REPORT_BOOK
AS


PROCEDURE GEN_REPORT(o_EmpCursor IN OUT empcur)
IS
BEGIN
OPEN o_EmpCursor FOR
SELECT E.EMPLOYEE_NUMBER, E.FULL_NAME, E.PERSON_ID,
CALC_YIELD(E.PERSON_ID) as prev_yield
FROM
DEV_REPORT_BOOK E ;

END GEN_REPORT;

FUNCTION CALC_YIELD(v_person_id in number)
return NUMBER
IS
yield_val NUMBER;
prev_week_date DATE;
BEGIN
SELECT
CASE TO_CHAR(WEEK_DATE, 'D')
WHEN '1' THEN WEEK_DATE-13
WHEN '2' THEN WEEK_DATE-7
WHEN '3' THEN WEEK_DATE-8
WHEN '4' THEN WEEK_DATE-9
WHEN '5' THEN WEEK_DATE-10
WHEN '6' THEN WEEK_DATE-11
WHEN '7' THEN WEEK_DATE-12
END START_DAY_OF_PREV_WEEK INTO prev_week_date
FROM DISC_WEEK
WHERE
WEEK_DATE=TO_CHAR(SYSDATE, 'DD-MON-RRRR');


SELECT SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100 INTO yield_val
FROM YIELD_CALC WHERE PERSON_ID=v_person_id AND START_DATE=prev_week_date;
return yield_val;


END CALC_YIELD;

END PCK_REPORT_BOOK ;
/



Question:
I am calling a function inside the proc for each person_id. Is it a good idea to do this (Performance wise)? Or is there any other way to accomplish this?
When I run this, it takes long time to give the output.

Pls help
Re: calling a function from a proc [message #188633 is a reply to message #188632] Mon, 21 August 2006 01:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If I see it correctly, you are selecting a date from a table where this date equals sysdate (rounded). Why not using sysdate itself? This SELECT INTO construction is not necessary unless you want to trap a NO_DATA_FOUND somewhere.

MHE
Re: calling a function from a proc [message #188634 is a reply to message #188632] Mon, 21 August 2006 01:59 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
That particular select into construction is necessary to get the Start Day of the previous week.
MONDAY is defined as the start date of every week.
Re: calling a function from a proc [message #188637 is a reply to message #188634] Mon, 21 August 2006 02:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And you are saying that you cannot perform the calculation with sysdate itself? You feel that you need to select the same date from a table and then perform calculations on that date (which is equal to sysdate)?

I don't get it.

Isn't this the same:
SELECT   SUM (hours_worked)
       / DECODE ((SUM (hours_per_week) - SUM (leave_hours)),
                 0, 1,
                 (SUM (hours_per_week) - SUM (leave_hours))
                )
       * 100
INTO   yield_val
FROM   yield_calc
WHERE  person_id = v_person_id
AND    start_date =
         CASE TO_CHAR (SYSDATE, 'D')
           WHEN '1'
             THEN trunc(sysdate) - 13
           WHEN '2'
             THEN trunc(sysdate) - 7
           WHEN '3'
             THEN trunc(sysdate) - 8
           WHEN '4'
             THEN trunc(sysdate) - 9
           WHEN '5'
             THEN trunc(sysdate) - 10
           WHEN '6'
             THEN trunc(sysdate) - 11
           WHEN '7'
             THEN trunc(sysdate) - 12
         END;


But then again: it is still early on a monday so it is not impossible that I'm missing the point here.

And another thing: if week_date is a date you should compare it to trunc(sysdate) and not to a to_char(sysdate).

MHE

[Updated on: Mon, 21 August 2006 02:05]

Report message to a moderator

Re: calling a function from a proc [message #188638 is a reply to message #188634] Mon, 21 August 2006 02:06 Go to previous messageGo to next message
rathish123
Messages: 3
Registered: August 2006
Location: Oman
Junior Member
I have an answer for you with in few minutes.
Re: calling a function from a proc [message #188641 is a reply to message #188637] Mon, 21 August 2006 02:11 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi,
I did not know previously that we can use a CASE statement in a where clause.
This also solves the purpose. Thanks for making me aware of this.
But the original question still remains (Whether calling this function in proc is a good idea or there is better approach).

Thanks
Re: calling a function from a proc [message #188647 is a reply to message #188641] Mon, 21 August 2006 02:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you are going to use this function in a lot of places, then it might be worth doing. It will be slower than simply merging the login into the query that calls the function as it will generate a large number of Pl/Sql - Sql context switches, but that my well be outweighed by only having one place to make changes if the logic needs modification.
Re: calling a function from a proc [message #188651 is a reply to message #188632] Mon, 21 August 2006 02:27 Go to previous messageGo to next message
rathish123
Messages: 3
Registered: August 2006
Location: Oman
Junior Member
Yes, that is correct.

If you are using this function in many times, then it would be a good one.
Re: calling a function from a proc [message #188704 is a reply to message #188637] Mon, 21 August 2006 04:46 Go to previous message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi,
We have a problem with this SELECT CONSTRUCT. It may happen we may not exactly land on a Monday because of uneven no. of days in a month.

Do you see a possible solution in this?

Regards
Previous Topic: n number of CREATE statements in single procedure
Next Topic: DBMS_RLS
Goto Forum:
  


Current Time: Tue Dec 06 12:14:58 CST 2016

Total time taken to generate the page: 0.14561 seconds