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
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 Cur1; end Cur0;
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