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
