All Forums Tools
az_maverick 24 posts Joined 03/09
02 Apr 2009
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) )BEGINDECLARE emp_count INTEGER;DECLARE emp_val VARCHAR(25);DECLARE emp_prior INTEGER;DECLARE Cur0 CURSOR FORSELECT min(emp_priority) from EMP_HIERARCHY;DECLARE Cur1 CURSOR FORSELECT 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_checkselect * 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 recordselect (emp_count+1) into emp_count; while (emp_count>= 2 && emp_count <= 32) & & emp_prior = emp_countLOOP insert into emp_checkselect * from (QUERY) ABCINTERSECT(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_idThank you already .... :)Thank you ,az_maverick

You must sign in to leave a comment.