Home » SQL & PL/SQL » SQL & PL/SQL » Cursor for loops
icon9.gif  Cursor for loops [message #217745] Mon, 05 February 2007 04:16 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi All,

I am facing some problems regarding Cursor for loops.
Please clear my doubt

I have created the below function to calc the no. of years of service for a particular employee.
In case the function is executed with an invalid emp id, then in that case it should not enter the for loop, then how is the "when no_data_found" exception being raised.
I understand that the "when no_data_found" exception is raised when the SELECT query returns no rows.

Thanks,
Aditi

create or replace function GET_SERVICE_YRS(p_emp_id NUMBER)
RETURN NUMBER IS
NO_OF_YRS NUMBER(10);
CURSOR CUR_EID IS
SELECT EID
FROM EMP1;
BEGIN
FOR EID IN CUR_EID LOOP
SELECT (SYSDATE-HIRE_DATE)
INTO NO_OF_YRS
FROM EMP1
WHERE EID=P_EMP_ID;
RETURN NO_OF_YRS;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA EXISTS');
END GET_SERVICE_YRS;
Re: Cursor for loops [message #217750 is a reply to message #217745] Mon, 05 February 2007 04:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You have forgotten the where clause in your loop cursor. If you add "where eid = p_emp_id" then you'd be fine. But in that case, why don't you calculate the years in that cursor as well. Now you're doing two lookups for one employee in one table.

MHE
Re: Cursor for loops [message #217756 is a reply to message #217750] Mon, 05 February 2007 05:13 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
do u mean like this?

create or replace function GET_SERVICE_YRS(p_emp_id NUMBER)
RETURN NUMBER IS
NO_OF_YRS NUMBER(10);
CURSOR CUR_EID IS
SELECT EID
FROM EMP1
WHERE EID=P_EMP_ID;
BEGIN
FOR EID IN CUR_EID LOOP
SELECT (SYSDATE-HIRE_DATE)
INTO NO_OF_YRS
FROM EMP1;
RETURN NO_OF_YRS;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA EXISTS');
END GET_SERVICE_YRS;
Re: Cursor for loops [message #217757 is a reply to message #217745] Mon, 05 February 2007 05:31 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
I think there is not need of cursor. Try following -

create or replace function GET_SERVICE_YRS(p_emp_id NUMBER)
RETURN NUMBER IS
NO_OF_YRS NUMBER(10);
BEGIN
SELECT
(SYSDATE-HIRE_DATE)
INTO NO_OF_YRS
FROM
EMP1
WHERE
EID=P_EMP_ID;
RETURN NO_OF_YRS;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA EXISTS');
END GET_SERVICE_YRS;
Re: Cursor for loops [message #217759 is a reply to message #217756] Mon, 05 February 2007 05:33 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Looking at it again, I see no need for a loop at all:
CREATE OR REPLACE FUNCTION get_service_yrs (p_emp_id NUMBER)
   RETURN NUMBER
IS
   no_of_yrs   NUMBER (10);
BEGIN
   SELECT (SYSDATE - hire_date)
   INTO   no_of_yrs
   FROM   emp1
   WHERE  eid = p_emp_id;

   RETURN no_of_yrs;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO DATA EXISTS');
      RETURN NULL;
END get_service_yrs;


Some remarks though:
1. Date subtraction is in units of DAYS, not years. You might want to look at the MONTHS_BETWEEN function.
2. A function should ALWAYS return a value.
3. DBMS_OUTPUT will not work if you call it from SQL or from another environment then the prompt.

MHE
Previous Topic: Date filter problem
Next Topic: what are database servers
Goto Forum:
  


Current Time: Fri Dec 09 23:30:55 CST 2016

Total time taken to generate the page: 0.23571 seconds