Sunday, October 21, 2007

loop through query result and print each row in stored procedure

have this query which returns a set of rows, How can I loop through each row and display them using dbms.output.put_line? Please notice in the where clause there is a variable 'v_trainer_id', the variable value is known (retrieved from a column of a current row of another cursor)

select customer.id as customer_id, membership.join_date, membership.join_fee,round(months_between(sysdate, membership.join_date),0) as months_joined, membership_plan.monthly_price, membership.monthly_discount, (membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0) as total_paidfrom membership,membership_plan,customerwhere membership.membership_plan_id = membership_plan.id andcustomer.id=membership.customer_id andmembership.trainer_id = v_trainer_idorder bycustomer.id;


solution:

v_cur_tid := r_trainer.trainer_id;FOR r_mem IN(select membership.customer_id as cust_id, membership.join_date, membership.join_fee,round(months_between(sysdate, membership.join_date),0) as months_joined, membership_plan.monthly_price, membership.monthly_discount, (membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0) as total_paidfrom membership,membership_planwhere membership.membership_plan_id = membership_plan.id andmembership.trainer_id = v_cur_tidorder bymembership.customer_id)LOOP

No comments: