Friday, January 4, 2008

My 1st Stored Procedure

Create/run store procedure in MySQL SQL Browser
-script->create function/procedure
-click 'execute' button to compile and store in db (proc table)
-type "call sp_ginger()" to run it, without ';"
-database browser->sp_ginger right click to edit

// the task is to diff value between adjcent records in stat table which contains
// up to 14M records remember in sp, assign statement need to "set statement"
// one minor thing is in query "... ttime desc" doesn't work (i.e., ignored)
// in sp, has to be "..s.ttime desc" though it is OK in query, strange thing
// is that ttime is not ambiguous in the statement.
// note that in ginger_watchdog, the timestamp type column will be automatically
// populated with current timestamp during insertion.


DROP PROCEDURE IF EXISTS `webnmsdb`.`sp_ginger` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_ginger`()

declare done INT default 0;
declare firstItem int default 0;
declare pid, ttime, v bigint(20);
declare instance char(100);

declare pre_pid, pre_ttime, pre_v bigint(20) default -1;
declare pre_instance char(100) default null;
declare rate bigint(20);

declare cur cursor for select * from ginger_order_temp;
declare continue handler for not found set done = 1;

create table if not exists ginger_watchdog (desp char(100), cur_timestamp TIMESTAMP);
insert into ginger_watchdog(desp) values('started sp at');

drop table if exists ginger_result;
create table ginger_result(pollid bigint(20), instance char(100), ttime bigint(20), val bigint(20));

drop table if exists ginger_order_temp;
create table ginger_order_temp select s.pollid, s.instance, s.ttime, s.val from statsdata10_17_2007 s, ratesOidToConvert oid where s.pollid=oid.pollid and s.pollid=992 order by s.pollid, s.ttime desc;

open cur;

   fetch cur into pid, instance, ttime, v;
   if not done then
   if (firstItem = 0) then
     set pre_pid=pid, pre_instance=instance, pre_ttime=ttime, pre_v=v;
     set firstItem = 1;
     if (pid = pre_pid) then
       set rate = abs(pre_v - v)/((pre_ttime - ttime)/1000);
       insert into ginger_result values(pre_pid, pre_instance, pre_ttime, rate);
     end if;
     set pre_pid=pid, pre_instance=instance, pre_ttime=ttime, pre_v=v;
   end if;

end if;
until done end repeat;
close cur;

insert into ginger_watchdog(desp) values('finished sp at');

END $$

No comments: