All Forums Database
az_maverick 24 posts Joined 03/09
01 Apr 2009
Procedure/ Cursor 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) 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_idThank you already .... :)Thank you ,az_maverick

rgs 106 posts Joined 02/07
02 Apr 2009

You use: FETCH NEXT CUR1 into emp_val, emp_prior;You can just specify your local procedure variables in SQL statements. However if you have a column name that is the same as your local variable name then you either have to qualify the local name with the compound statement name or prefixed it with the colon character (:); otherwise it thinks it is the column name. This applies to parameters also.Example:MyBlock: BEGIN DECLARE MyVar INTEGER;DECLARE MyAns INTEGER;SELECT C1 INTO MyAns FROM T1 WHERE MyVar = C2;orSELECT C1 INTO :MyAns from T1 WHERE :MyVar = C2; orSELECT C1 INTO MyBlock.MyAns FROM T1 WHERE MyBlock.MyVar = C2;END MyBlock;

az_maverick 24 posts Joined 03/09
03 Apr 2009

Hello, Thanks a lot for your help..

You must sign in to leave a comment.