Cursor/ Procedure in TD

Tools & Utilities
Enthusiast

Cursor/ Procedure in TD

Hello all,
I am new to TD . However with my background in Oracle , i have written a procedure .I was wondering if someone coudl tell me certain aspect in coding this procedure ,
Here is the procedure,

emp_check is a volatile table. Since I wanted to store the results and return them in the form of a table , i perform an insert operation in the volatile table.

create procedure parameter_rolldown (IN dpt_id varchar(25) )

BEGIN
DECLARE emp_count INTEGER;
DECLARE emp_val VARCHAR(25);
DECLARE emp_prior INTEGER;

DECLARE Cur0 CURSOR FOR
SELECT min(emp_priority) from EMP_HIERARCHY;

DECLARE Cur1 CURSOR FOR
SELECT emp_nm , emp_priority FROM EMP_HIERARCHY order by emp_priority;

open Cur0;
open Cur1;

FETCH CUR0 into emp_count;
FETCH CUR1 into emp_val, emp_prior ;

insert into emp_check
select * from
(
(QUERY1)
UNION
(
select emp_id from emp_check
)
)main1;
delete emp_prior, emp_val where emp_prior = 1; -- This I am doign because I want to move to the next record

select (emp_count+1) into emp_count;
while (emp_count>= 2 && emp_count <= 32) & & emp_prior = emp_count

LOOP

insert into emp_check
select * from
(QUERY) ABC
INTERSECT
(
select emp_id from emp_check
)
)main2;

delete emp_prior & emp_val where emp_prior = emp_count;

(** I want to move to the next record in Cur1 , which will have record 2 ..
I dont know how this can be done in TD .. Is there a syntax for NEXT ? **)

select (emp_count+1) into emp_count; -- incrementing the counter to move to the next record in loop

END LOOP;

end while;

end Cur1;
end Cur0;

END;

I have one more question ..
If I have to use the variables in a query in the procedure ,
Can i do the following,
select * from employee where emp_id = emp_prior ..

More commonly , how can i use the vaiables declared in the cursor...Do I need to use a identifier like % to distingush the vaiable from the table columns ?

Also how can I use the input variable declared for the procedure in a query ?
Can i do the following,
select * from department where dept_id = dpt_id

Thank you already .... :)

Thank you ,
az_maverick