sql

CREATE OR REPLACE PROCEDURE generate_bonus
AS

cursor student_info is
	select distinct students.id,
  events.begindatetime,
	events.enddatetime,
	count(items.number_of_coupons) as coupons_collected,
	events.type from students
	join applies on applies.students_id = students.id
	join schedules on schedules.id = applies.schedules_id
	join events on events.id = schedules.events_id
	join orders on orders.students_id = students.id
	join orderitems on orderitems.orders_id = orders.id
	join items on items.id = orderitems.items_id
	join bars on bars.id = orders.bars_id
	where applies.status = 'PLANNED'
	and orderitems."NUMBER" is not null
	and bars.name is not null
	group by students.id, events.begindatetime, events.enddatetime, events.type
	order by students.id;

BEGIN

	DECLARE
	s_id integer(256);
	s_beginDate date;
	s_endDate date;
	s_noCoupons number(256);
	s_eventType varchar2(256);
	s_workedHours number(24) := 8;
	calculated_bonus number(256);
	count_rows integer(256);

	OPEN student_info;

		LOOP

		FETCH student_info into s_id, s_beginDate, s_endDate, s_noCoupons, s_eventType;

			Select count(*) into count_rows from student_bonus where students_id = s_id and rownum <= 1;

      EXIT WHEN count_rows = 1;

			IF (s_eventType = 'ROUGH') THEN
				calculated_bonus := s_workedHours * (s_workedHours / 100 * 7) * s_noCoupons;

				INSERT INTO student_bonus(students_id, bonus, events_id) VALUES (s_id, calculated_bonus, s_eventType);

				calculated_bonus := 0;

			ELSIF (s_eventType = 'NORMAL') THEN
				calculated_bonus := s_workedHours * (s_workedHours / 100 * 4) * s_noCoupons;

				INSERT INTO student_bonus(students_id, bonus, events_id) VALUES (s_id, calculated_bonus, s_eventType);

				calculated_bonus := 0;

			ELSE
				calculated_bonus := s_workedHours * (s_workedHours / 100 * 2) * s_noCoupons;

				INSERT INTO student_bonus(students_id, bonus, events_id) VALUES (s_id, calculated_bonus, s_eventType);

				calculated_bonus := 0;
			END IF;

		END LOOP;

	CLOSE student_info;

END generate_bonus;