Hi All,

I want to excute one of two select query based on codition that I Have to get its value from database ,So I have to write a procedure

that get  value from database then loop through  condtion to decide which select statement will excute,the problem that is the time I will write procedure

So I need a help

I try with this

CREATE OR REPLACE procedure   GetTransferRequest(StartIndex number, EndIndex number,cursor_rs out SYS_REFCURSOR)
is
 cursor M is select TRANSFERTYP from EMPTRANSFER ;
 begin
  OPEN cursor_rs FOR
for F in M loop
if F.TRANSFERTYP=0  then

select statement    
     
 else
otherselect statement
    
end if ;
end loop;
 end GetTransferRequest;
 

Please help me ASAP

khalid

See this example
DECLARE
  v_jobid     employees.job_id%TYPE;     -- variable for job_id
  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  CURSOR c1 IS SELECT last_name, job_id FROM employees
                 WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK');
  v_employees employees%ROWTYPE;         -- record variable for row
BEGIN
  OPEN c1; -- open the cursor before fetching
  LOOP
    FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables
    EXIT WHEN c1%NOTFOUND;
    IF ... THEN
     SELECT ...
    ELSE
     SELECT ...
    END IF;
  END LOOP;
  CLOSE c1;
END;
/ 

Based on different conditions you can have different 'Select' statement there.

 thanks So much

but  please if you can explain for me ,where the result of the main select query will be stored because I want to return atable of data?????

khalid

 Here is an example to return a recordset. You can now work based on these 2 posts.
CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,
                    p_recordset OUT Types.cursor_type) AS
BEGIN
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/