Oracle

Re: loop with procedure

wmec — Mon, 06 Jul 2009 06:25:53 GMT

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;
/

Re: loop with procedure

khalid2008 — Mon, 06 Jul 2009 05:02:19 GMT

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

Re: loop with procedure

wmec — Mon, 06 Jul 2009 02:04:21 GMT

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.

loop with procedure

khalid2008 — Sun, 05 Jul 2009 08:59:53 GMT

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