Procedure/ Cursor in TD

Database
Enthusiast

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) )

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
2 REPLIES
rgs
Enthusiast

Re: Procedure/ Cursor in TD

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;

or

SELECT C1 INTO :MyAns from T1 WHERE :MyVar = C2;

or

SELECT C1 INTO MyBlock.MyAns FROM T1 WHERE MyBlock.MyVar = C2;

END MyBlock;
Enthusiast

Re: Procedure/ Cursor in TD

Hello,
Thanks a lot for your help..