postgresql 创建表，创建存储过程 创建触发器 创建触发器函数

CREATE DATABASE test;

----- 创建表
CREATE TABLE a
(
  std_no character varying(32) NOT NULL, -- 学号
  subject character varying(32), -- 学科
  achievement numeric(5,2), -- 成绩
  CONSTRAINT constraint_stdno_subject UNIQUE (std_no , subject )
);
--创建学号和成绩的组合索引
CREATE INDEX index_std_no_subject
  ON a
  USING btree
  (std_no COLLATE pg_catalog."default" , subject COLLATE pg_catalog."default" );
COMMENT ON INDEX index_std_no_subject
  IS '学号和成绩科目组合索引';




CREATE TABLE b
(
  std_no character varying(32),
  tol_achievement numeric(6,2),
  ranking numeric(4,0),
  CONSTRAINT constraint_b_pkey PRIMARY KEY (std_no )
);




--创建更新学生成绩表存储过程
CREATE OR REPLACE FUNCTION add_program_score(par_std_no character varying,par_subject  character varying ,par_achievement  numeric)
  RETURNS character AS
$BODY$
declare
    num int;


begin


--判断科目参数是否合法 不合法则直接返回C
if par_subject !='Mathematics' and par_subject !='Chinese' and  par_subject !='English' then
return 'C';
end if;


--根据学号和科目查询数据库是否存在值 存在则更新 不存在则增加
select count(std_no) into num from a where subject=par_subject and std_no=par_std_no;


--不存在则增加
if num = 0 then


insert into a values(par_std_no,par_subject,par_achievement);


return 'B';


end if;


--存在则更新 
if num != 0 then


update a set achievement = par_achievement  where std_no=par_std_no and subject=par_subject ;


return 'A';
end if;


end;
$BODY$
  LANGUAGE plpgsql VOLATILE;








--才触发器函数
create or replace function fun_a_triger()  returns trigger as   
$BODY$  
    DECLARE  
    
num int;

sumcore numeric;


rec record;


orderid int;

BEGIN  


--获取更新学员的总成绩
select sum(achievement) into sumcore from a where std_no = NEW.std_no;
--在b表是否有改学生的信息，有则更新，没有则添加
select count(0) into num from b where std_no = NEW.std_no;


IF num=0 then


insert into b values(NEW.std_no,sumcore,9999);
ELSE


update b set tol_achievement = sumcore where std_no = NEW.std_no;
end IF;


orderid:=1;
--更新全体学员总成绩排名
FOR rec IN (select std_no , tol_achievement from b order by tol_achievement desc ) LOOP  

update b set ranking = orderid where std_no = rec.std_no;


orderid:=orderid+1;

END LOOP;  

return NEW;  
END;  
$BODY$  
  LANGUAGE 'plpgsql' VOLATILE  ;


--创建触发器
CREATE TRIGGER triger_a
AFTER INSERT OR update 
ON a
FOR EACH ROW
EXECUTE PROCEDURE fun_a_triger();






---验证


select * from b;
select * from a;


select add_program_score('6','Chinese',8);

select add_program_score('1','Mathematics',8);

select add_program_score('76494915','Chinese',8);