I do not know how to write a specific stored procedure in Informix, so I had to write the logic in my mind in SQL Server which I know how to write (Microsoft simplicity)... So I will write my stored procedure in the SQL syntax and please any one who know Informix syntax and how to write tell me the equivalent of my stored procedure in Informix with little hints to learn the techniques used in both...
My stored procedure is:
ALTER PROC [dbo].[DetectFine] @prog_code int
AS
---------------------------------
DECLARE @lect_code int;
DECLARE @prog_code_temp int;
DECLARE @confirm_reg int;
DECLARE @start_date_temp DATE;
---------------------------------
DECLARE Mycursor CURSOR FOR
SELECT l.confirm_reg,p.[start_date],l.lect_code,l.prog_code
FROM cc1lectprog l INNER JOIN cc1progtimes p
ON l.prog_code = p.prog_code
WHERE p.prog_code = @prog_code;
---------------------------------
OPEN Mycursor ;
---------------------------------
FETCH NEXT FROM Mycursor
INTO @confirm_reg,@start_date_temp,@lect_code,@prog_code_temp;
---------------------------------
WHILE @@FETCH_STATUS =0
BEGIN
Select @confirm_reg as Cconfirm_reg, @start_date_temp as Cstart_date ,@lect_code as Clect_code,@prog_code_temp as Cprog_code_temp;
-- This is executed as long as the previous fetch succeeds.
IF @confirm_reg = 0 AND GETDATE()>= DATEADD(DD,-3,@start_date_temp)
-----------------------------------------------------------------------
UPDATE cc1lectprog
SET fine = 0
WHERE lect_code = @lect_code AND prog_code = @prog_code_temp;
-----------------------------------------------------------------------
FETCH NEXT FROM Mycursor
INTO @confirm_reg,@start_date_temp,@lect_code,@prog_code_temp;
-----------------------------------------------------------------------
END
CLOSE Mycursor
DEALLOCATE Mycursor
-----------------------------------------------------------------------
Note: this stored procedure is required to be executed every period of time like a job. Is it possible to execute procedure with parameters every period of time or not? And what are the alternatives if not?
CREATE PROCEDURE dbo.DetectFine(prog_code int)
DEFINE l_lect_code INT;
DEFINE l_prog_code INT;
DEFINE l_confirm_reg INT;
DEFINE l_start_date DATE;
FOREACH SELECT l.confirm_reg, p.start_date, l.lect_code, l.prog_code
INTO l_confirm_reg, l_start_date, l_lect_code, l_prog_code
FROM cc1lectprog l INNER JOIN cc1progtimes p
ON l.prog_code = p.prog_code
WHERE p.prog_code = prog_code
IF l_confirm_reg = 0 AND TODAY >= l_start_date - 3 THEN
UPDATE cc1lectprog
SET fine = 0
WHERE lect_code = l_lect_code AND prog_code = l_prog_code;
END IF;
END FOREACH;
END PROCEDURE;
If you named the cursor in the FOREACH loop, you could use WHERE CURRENT OF in the UPDATE statement instead of the searched condition.
FOREACH c_name FOR SELECT ...
UPDATE ...
WHERE CURRENT OF c_name;
END FORRACH;
The placement of semi-colons is always one of the finer arts in SPL. However, the stored procedure above does compile - even without either of the tables in the database. Obviously, it would fail if run (executed) without the relevant tables.
Assuming you are using IBM Informix Dynamic Server (IDS) 11.50 or later, then there is a facility (colloquially) known as DB-Cron which can be used to schedule regularly executed jobs.