FUNCTION INSIDE A PROCEDURE

[Vandy02]

I have a procedure that basically runs cursors and a few select statements. I want to be able to run a function within the procedure that will return a TRUE OR FALSE based on the select statement within the function

In the following I would like to be able to run...as an example... the "Select count(*) from LABOR" as a function...passing the current cursor record r_c1.ssn to the function and then return a boolean so I can test based on the returned value. The following procedure works, but I need to use a function as I have additional selects I need to unclude.

=======================================
CREATE OR REPLACE PROCEDURE CAL_TEST7
--
IS
--
-- ADDED COLUMN 'PROBLEM' ALONG WITH UPDATE PROCESS IF LABOR RECORD NOT IN LABOR TABLE
--
---------------------------------------------------------------------------------------
----------OBTAIN UNPROCESSED LABOR RECORDS FOR CALENDAR UPDATES
CURSOR c_c1
IS
SELECT *
FROM wdw_interface_gems_maximo_2
WHERE processflag not in ('Y', 'L') and ssn IN ('JOHN', '131349696');
---------------------------------------------------------------------------------------
--
---------------------------------------------------------------------------------------
----------OBTAIN CALNUM FROM WORKPERIOD BASED ON STARTTIME & ENDTIME
CURSOR c_c2
(
i_starttime IN wdw_interface_gems_maximo_2.starttime%type,
i_endtime IN wdw_interface_gems_maximo_2.endtime%type
)
IS
select distinct calnum, shiftnum, starttime, endtime, workhours
from workperiod
where to_char(starttime, 'HH24MI') = i_starttime;
--and to_char(endtime, 'HH24MI') = i_endtime;
---------------------------------------------------------------------------------------
--
---------------------------------------------------------------------------------------
----------UPDATE LABOR.CALNUM & WDW_INTERFACE_GEMS_MAXIMO_2
CURSOR c_c3
(i_ssn wdw_interface_gems_maximo_2.ssn%type)
--
IS
SELECT *
FROM LABOR
WHERE la3 = i_ssn;
---------------------------------------------------------------------------------------
--
---------------------------------------------------------------------------------------
----------SET VARIABLES
calnum_exists varchar2(10); --USED TO SEE IF LABOR RECORD EXISTS WITHIN LABOR TABLE
starttime number(4);
endtime number(4);
v_date date;
v_calnum varchar2(8);
v_description varchar2(50);
count_of_labor number(1) := 0; --USED TO SEE IF LABOR RECORD EXISTS WITHIN LABOR TABLE
no_time EXCEPTION;
--
---------------------------------------------------------------------------------------
BEGIN

FOR r_c1 IN c_c1
LOOP
--DBMS_OUTPUT.PUT_LINE('COUNT ONE: '||r_c1.starttime||' '||r_c1.endtime);
SELECT distinct calnum, to_char(starttime, 'HH24MI') starttime, to_char(endtime, 'HH24MI') endtime
INTO calnum_exists, starttime, endtime
FROM workperiod
WHERE to_char(starttime, 'HH24MI') = r_c1.starttime;



IF endtime != r_c1.endtime and r_c1.processflag != 'C' THEN
v_date := to_date(r_c1.scheduledate, 'YYYY-MM-DD');
v_calnum := substr(r_c1.posdesc, 1, 7);
v_description := 'CALNUM: '||v_calnum||' INTERVAL: '||r_c1.starttime||' '||r_c1.endtime;

INSERT INTO CALENDAR
(CALNUM, STARTDATE, ENDDATE, DESCRIPTION)
VALUES(v_calnum, v_date, v_date, v_description);

UPDATE wdw_interface_gems_maximo_2
SET processflag = 'C', problem = 'UPDATE WORKPERIODS IN CALENDAR'
WHERE ssn = r_c1.ssn and processflag = 'N';
ELSE
FOR r_c2 IN c_c2(r_c1.starttime, r_c1.endtime)
LOOP
SELECT count(*)
INTO count_of_labor
FROM LABOR
WHERE la3 = r_c1.ssn;
IF count_of_labor = 0 THEN
-- UPDATE PROBLEM IF LABOR RECORD NOT IN LABOR TABLE
UPDATE wdw_interface_gems_maximo_2
SET problem = 'NO LABOR'
WHERE ssn = r_c1.ssn and processflag = 'L';
ELSE
FOR r_c3 IN c_c3(r_c1.ssn)
LOOP
UPDATE labor
SET calnum = r_c2.calnum
WHERE la3 = r_c1.ssn;

-- UPADTE DATA TABLE

UPDATE wdw_interface_gems_maximo_2
SET processflag = 'Y'
WHERE ssn = r_c1.ssn and processflag = 'N';
END LOOP; --r_c3
END IF;
END LOOP; --r_c2
END IF;
END LOOP; --r_c1
--
COMMIT;
EXCEPTION
WHEN no_time THEN

UPDATE wdw_interface_gems_maximo_2
SET problem = 'NO TIME';
--WHERE ssn = r_c1.ssn and processflag = 'N';



END CAL_TEST7;
/

 

[sem]

To be honest I do not completely understand what you need and what you need it for. How are you going to get any result from procedure? Why don't you place both function and procedure into package?

To declare a nested function within procedure you should place function definition at the end of declare section:

create or replace procedure dummyProc
is
var boolean;
cursor c is select from dual;
function dummyFunction return boolean
is
begin
  return true;
end;
begin
  var := dummyFunction;
end;

Don't forget that you can not invoke that function from outside your procedure.

Regards, Dima

http://seminihin.narod.ru

 

[LKBrwnDBA]

You can try coding something like this:

CREATE OR REPLACE PROCEDURE CAL_TEST7
--
IS
...
FUNCTION LABOR_IS TRUE(p_SSN varchar2)
RETURN BOOLEAN IS
l_count_of_labor number;
Begin
  Select Count(*)
    Into L_Count_Of_Labor
    From Labor
   Where La3 = P_Ssn;
  If L_Count_Of_Labor > 0 Then
    Return True;
  Else
    Return False;
  End If;
End;
...
Begin
...etc...
              If Labor_Is True(R_C1.Ssn) Then          
                  -- Update Problem If Labor Record Not In Labor Table         
                  Update Wdw_Interface_Gems_Maximo_2
                     Set Problem = 'No Labor'
                   Where Ssn = R_C1.Ssn And Processflag = 'L'; 
              Else
                  For R_C3 In C_C3(R_C1.Ssn)
                  Loop              
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb