Postgresql 函数、触发器写法
1、数据库环境-- Table: 学生分数表
CREATE TABLE stu_score
(
stuno serial NOT NULL, --学生编号
major character varying(16), --专业课程
score integer --分数
)
WITH (
OIDS=FALSE
);
ALTER TABLE stu_score OWNER TO postgres;
-- Table: 专业状态表，存储哪个专业有多少学生报名
CREATE TABLE major_stats
(
major character varying(16), --专业课程
total_score integer, --总分
total_students integer --学生总数
)
WITH (
OIDS=FALSE
);
ALTER TABLE major_stats OWNER TO postgres;
2、函数、存储过程
create or replace function fun_stu_major()
returns trigger as
$BODY$
DECLARE
rec record;
BEGIN
DELETE FROM major_stats;--将统计表里面的旧数据清空
FOR rec IN (SELECT major,sum(score) as total_score,count(*) as total_students
FROM stu_score GROUP BY major) LOOP
INSERT INTO major_stats VALUES(rec.major,rec.total_score,rec.total_students);
END LOOP;
return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
3、正式创建触发器trigger
create trigger tri_stu_major
AFTER insert or update or delete
on stu_score
for each row
execute procedure fun_stu_major()
页:
[1]