hedgezzz的部落格

sysdbopen() and sysdbclose() 這兩個 stored procedure 可以用來
給每一個新的 session 設參數,環境變數等用途,如果找不到你的名字的 sysdbopen ,
那就會找 public.sysdbopen() ... 以此類推 public.sysdbclose() ....

create procedure sp_ins_quotex(idx char(6),
p_ptrade_new decimal(12,2),p_ptrade_old decimal(12,2) )

define global quotey_serial integer default 0 ;
begin
if (quotey_serial = 0) then
let quotey_serial = sp_max_quotey() ;
end if ;

let quotey_serial = quotey_serial + 1 ;

update quotey
set ptrade_new = p_ptrade_new ,
ptrade_old = p_ptrade_old ,
update_serial = quotey_serial
where id = idx ;
end
end procedure ;


create procedure sp_max_quotey()
returning integer ;
define c_update_serial integer ;

select max(update_serial) + 10
into c_update_serial
from quotey ;
return c_update_serial ;
end procedure;

create procedure sp_testx(c_fld1 char(5) , c_fld2 integer)
define eisam integer ;
define esql integer ;
define x char(6),y char(10) ;
begin
on exception set esql,eisam
if esql = -239 then
raise exception -746 ,0,"table fld1 duplicated" ;
end if
end exception

insert into table1(fld1,fld2) values(c_fld1,c_fld2) ;
end
end procedure ;

create procedure sp_quoteid3(incoditicu char(30))
returning char(7), char(21) ;
define p_id char(7) ;
define p_name char(50) ;
foreach
select id,name into p_id,p_name
from quote where id[1,2] in (incoditicu)
return p_id,p_name with resume ;
end foreach;
end procedure;

create procedure sp_quoteid(incoditicu char(30))
returning char(7), char(21) ;
define p_id char(7) ;
define p_name char(50) ;
foreach
select id,name into p_id,p_name
from quote where id[1,2] = incoditicu
return p_id,p_name with resume ;
end foreach;
end procedure;

create trigger ins_quotex update on quotex
referencing new as newx old as oldx
for each row
when(newx.ptrade != oldx.ptrade)
(execute procedure sp_ins_quotex(newx.id,newx.ptrade,oldx.ptrade));

create trigger quote_update
update of ptrade,volume on quote referencing old as pre new as post
for each row when(post.ptrade != pre.ptrade or post.volume != pre.volume)
(insert into quotewatch(id,pre_ptrade,post_ptrade,pre_volume,post_volume)
values(pre.id,pre.ptrade,post.ptrade,pre.volume,post.volume) ) ;


create procedure "informix".show_details(p_fld1 integer)
returning integer , char(21) ;
define strqry char(250) ;
define l_fld1 integer ;
define l_fld2 char(21) ;

let strqry = "select fld1,fld2 from testa where fld1 < ?" ;
prepare idx from strqry ;
declare cursorx cursor for idx ;
open cursorx using p_fld1 ;
while(1=1)
fetch cursorx into l_fld1,l_fld2 ;
if(SQLCODE != 100) then
return l_fld1,l_fld2 with resume ;
else
exit ;
end if
end while
close cursorx ;
free cursorx ;
free idx ;
end procedure ;


create procedure "informix".dyna_details
(p_table_name char(40),p_fld1 integer)
returning integer , char(21) ;
define strqry char(250) ;
define l_fld1 integer ;
define l_fld2 char(21) ;

let strqry = "select fld1,fld2 from " || p_table_name || " where fld1 < ?" ;
prepare idx from strqry ;
declare cursorx cursor for idx ;
open cursorx using p_fld1 ;
while(1=1)
fetch cursorx into l_fld1,l_fld2 ;
if(SQLCODE != 100) then
return l_fld1,l_fld2 with resume ;
else
exit ;
end if
end while
close cursorx ;
free cursorx ;
free idx ;
end procedure ;


create procedure "informix".proc1()
referencing old as prex new as postx for testa ;

if(inserting) then
insert into testb(fld1,fld2) values(postx.fld1,postx.fld2);
end if

if(updating) then
update testb set fld2 = postx.fld2 where fld1 = postx.fld1 ;
end if

if(deleting) then
delete from testb where fld1 = prex.fld1 ;
end if

if(selecting) then
insert into testa_select(fld1,fld2) values(prex.fld1,prex.fld2);
end if
end procedure ;


create procedure "informix".sp_quotey(idx char(6) ,
p_ptrade_new decimal(12,2),p_ptrade_old decimal(12,2) )
begin
update quotey
set ptrade_new = p_ptrade_new ,
ptrade_old = p_ptrade_old ,
seqno = seq_quotey.NEXTVAL ,
currentx = current
where id = idx ;
end
end procedure ;


create procedure "informix".sp_exception1(p_id char(6),p_ptrade decimal(12,2) )
define eisam integer ;
define esql integer ;
begin
on exception set esql,eisam
if(esql = -239) then
update quotex set ptrade = p_ptrade where id = p_id ;
end if
raise exception -746,0,"insert error:" ||
esql || " ,isam:" || eisam || " happened!!" ;
end exception
insert into quotex values(p_id,p_ptrade) ;
end
end procedure ;