informix 存储过程小实验1

--1、创建table1和table2两张表

CREATE TABLE table1

(

    nameid varchar(128) PRIMARY KEY,

    name varchar(255)

);

INSERT INTO table1 (nameid,name) VALUES ('1','tommy');

INSERT INTO table1 (nameid,name) VALUES ('2','Richard');

INSERT INTO table1 (nameid,name) VALUES ('3','Charles');

INSERT INTO table1 (nameid,name) VALUES ('4','Henry');

INSERT INTO table1 (nameid,name) VALUES ('5','Mark');

INSERT INTO table1 (nameid,name) VALUES ('6','David');

-------------------

CREATE TABLE table2

(

    id varchar(128) PRIMARY KEY,

    gender nchar(5),

    height char(4),

    job varchar(255)

);

insert into table2 (id,gender,height,job) values ('1','man','164CM','doctor');

insert into table2 (id,gender,height,job) values ('2','woman','13CM','salesman');

insert into table2 (id,gender,height,job) values ('3','man','167CM','manager');

insert into table2 (id,gender,height,job) values ('4','woman','156CM','secretary');

insert into table2 (id,gender,height,job) values ('5','man','167CM','box');

insert into table2 (id,gender,height,job) values ('6','woman','153CM','model');

--给表table2插入一列type1

ALTER TABLE table2 ADD type1 integer DEFAULT 0;

-------------------

CREATE TABLE table3

(

    yjid integer PRIMARY KEY,

    mustyj float,

    realityyj float,

    percentyj float

);

insert into table3 (yjid,mustyj,realityyj,percentyj) values ('1','10','8.9','');

insert into table3 (yjid,mustyj,realityyj,percentyj) values ('2','10','7.9','');

insert into table3 (yjid,mustyj,realityyj,percentyj) values ('3','10','8.4','');

insert into table3 (yjid,mustyj,realityyj,percentyj) values ('4','10','8.3','');

insert into table3 (yjid,mustyj,realityyj,percentyj) values ('5','10','7.3','');

--以下所有实验使用以上3个表

--2、模拟环境：table2表中gender列只有一行为空的情况下

drop table table3;

drop procedure table_function;

CREATE PROCEDURE table_function()

define id_v varchar(128);

define name_v varchar(255);

define gender_v char(4);

create table table3 (id varchar(128),name varchar(255),gender char(4));

select first 1 t1.nameid,t1.name,t2.gender into id_v,name_v,gender_v from table1 t1,table2 t2 where t1.nameid=t2.id and t2.gender='';

insert into table3 values (id_v,name_v,gender_v);

update table2 set gender='NO' where id=id_v;

end procedure;            

execute procedure table_function();  

--3、模拟环境：table2表中gender列多行为空的情况下

drop table table3;

drop procedure table_function;

create procedure table_function()

  define id_v varchar(128);

  define name_v varchar(255);

  define gender_v char(4);    

  let gender_v='';

  --create table可以去掉

  create table table3 (id varchar(128),name varchar(255),gender char(4));

while gender_v =''

  select first 1 t1.nameid,t1.name,t2.gender into id_v,name_v,gender_v from table1 t1,table2 t2 where t1.nameid=t2.id and t2.gender='';

  --insert into 可以去掉

insert into table3 values (id_v,name_v,gender_v);

update table2 set gender='NO' where id=id_v;

continue while;

end while;

end procedure;   

execute procedure table_function();

--4、模拟环境：如果table2表中的type1为0侧为man为1侧为woman

drop procedure table_function;

create procedure table_function()

  define id_v varchar(128);

  define name_v varchar(255);

  define gender_v char(4);

  define type1_v integer;    

  let gender_v='';

while gender_v =''

  select first 1 t1.nameid,t1.name,t2.gender into id_v,name_v,gender_v from table1 t1,table2 t2 where t1.nameid=t2.id and t2.gender='';

  select type1 into type1_v from table2 where id=id_v;

    if type1_v =0 then

update table2 set gender='man' where id=id_v;

    end if;

    if type1_v =1 then

      update table2 set gender='woman' where id=id_v;

    end if;

continue while;

end while;

end procedure;   

execute procedure table_function();

--5、模拟环境：计算员工的业绩百分比

drop procedure table_function;

create procedure table_function()

  define yjid_v integer;

define mustyj_v float;

define realityyj_v float;

define percentyj_v float;

  define i integer;

  define x integer;

  select count(*) into i from table3 where percentyj is null;

for x =1 to i

select first 1 yjid,mustyj,realityyj into yjid_v,mustyj_v,realityyj_v from table3 where percentyj is null;

let percentyj_v =realityyj_v/mustyj_v; 

update table3 set percentyj=percentyj_v where yjid=yjid_v;

end for;

end procedure;

execute procedure table_function();

--6、模拟环境：当业绩percentyj小于60%或者percentyj大于90%的时候不做优化，当percentyj大于0.7并且小于0.9的时候开始做优化。

drop procedure table_function;

create procedure table_function()

  define yjid_v integer;

define mustyj_v float;

define realityyj_v float;

define realityyj_v1 float;

define percentyj_v float;

define percentyj_v1 float;

  define i integer;

  define x integer;

  select count(*) into i from table3 where percentyj is null;

for x =1 to i

select first 1 yjid,mustyj,realityyj into yjid_v,mustyj_v,realityyj_v from table3 where percentyj is null;

let percentyj_v =realityyj_v/mustyj_v; 

if percentyj_v <=0.6 or percentyj_v >=0.9 then

let percentyj_v1 =percentyj_v;

update table3 set percentyj=percentyj_v1 where yjid=yjid_v;

elif percentyj_v >0.6 and percentyj_v <0.9 then

let realityyj_v1 =mustyj_v*0.98;

let percentyj_v1 =realityyj_v1/mustyj_v; 

update table3 set percentyj=percentyj_v1 where yjid=yjid_v;

end if;

end for;

end procedure;

execute procedure table_function();

--7、模拟环境：随机数存储过程

CREATE PROCEDURE sp_random() RETURNING INTEGER;

        DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;

        DEFINE d DECIMAL(20,0);

        LET d = (seed * 1103515245) 12345;

        -- MOD function does not handle 20-digit values...  Dammit!!

        LET seed = d - 4294967296 * TRUNC(d / 4294967296);

        RETURN MOD(TRUNC(seed / 65536), 32768);

END PROCEDURE;

execute procedure sp_random();

--8、模拟环境：将变量值打印到日志中

drop procedure table_function;

create procedure table_function(id_v integer default 3,percentyj_v float default 44)

update table3 set percentyj=percentyj_v where yjid=id_v;

system 'echo ' || percentyj_v || ' > /home/informix/data/b.log';

system 'echo ' || percentyj_v || ' > $(tty)'; 

end procedure;

execute procedure table_function();