Gaussdb数据库对象

PL/SQL（Procedural Language/SQL）是数据库对SQL语言的过程化扩展，它在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

核心优势：
过程化能力：支持条件判断、循环等编程结构。
高性能：减少网络传输，批量处理数据。
安全性：封装业务逻辑，减少SQL注入风险。
完整性：结合SQL的数据操纵能力和过程语言的数据处理能力。

一、基本概念
数据库的对象有表、视图、函数、存储过程、游标等。
表：存放原始数据的核心容器。
视图：查看数据的安全窗口或简化视图。
函数：用于计算并返回一个结果的工具。
存储过程：可自动执行复杂任务的操作脚本。
游标：用来逐行处理查询结果集的指针。
触发器：一种在指定数据库事件（如增、删、改）发生时自动执行的特殊存储过程，充当数据操作的幕后规则或自动脚本。

二、表
2.1 表创建
存储引擎-列存储，a字段为列存表，b字段为分区键
create table p_table(a int,b int,c int,d int) with (orientation = column)
distribute by hash (a)
partition by range (b)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40)
);

2.2 查询表的模式名和表名
select tablename,schemaname from pg_tables where tablename=‘p_table’;

2.3 查询表的所在节点nodeoids信息
select pc.nodeoids from pg_class c join pgxc_class pc on c.oid=pc.pcrelid where c.relname=‘p_table’;

2.4 查询表所在的节点实例信息
select * from pgxc_node
where oid in (select unnest(pc.nodeoids) from pg_class c join pgxc_class pc on c.oid=pc.pcrelid where c.relname=‘p_table’);

三、SQL语法实践
示例一：student(sno int,sname varchar(50),score int,cno int);
classes(cno int,cname varchar(50));
1、查询学号为130的学生名字、总成绩以及所在班级
select sname,score,name
from student join classes on student.cno=classes.cno
where sno=130;

2、查看每个班级（cno）月考总分（score）前三名，其中要求分数相同的人具有相同的编号
select * from (select sno,score,cno,dense_rank() over (partition by cno order by score desc) as ranking from student) where ranking <=3;

示例二：create table stu(id int,math int,art int,phy int);
1、查看每门成绩是否大于每门平均成绩
select id,math,art,phy,
case when math <= avg(math) over() then ‘不大于’ else ‘大于’ end as morethan_avgmath,
case when art <= avg(art) over() then ‘不大于’ else ‘大于’ end as morethan_avgart,
case when phy <= avg(phy) over() then ‘不大于’ else ‘大于’ end as morethan_avgphy
from stu;
2、编写函数，获取成绩绩点，输入学生id和科目名称输出对应的绩点值，0-59给0，60-69给0.1，70-79给0.2，80-89给0.3，90-100给0.4。
create or replace function fun_cal_point(id1 int,coursename varchar(30)) returns decimal(10,1)
as
declare point decimal(10,1);
begin
if coursename = ‘math’ then
select (case when math <= 59 then 0
when math <= 69 then 0.1
when math <= 79 then 0.2
when math <= 89 then 0.3
when math <= 100 then 0.4
else 0 end) into point from stu where id=id1;
elsif coursename = ‘art’ then
select (case when art <= 59 then 0
when art <= 69 then 0.1
when art <= 79 then 0.2
when art <= 89 then 0.3
when art <= 100 then 0.4
else 0 end ) into point from stu where id=id1;
elsif coursename = ‘phy’ then
select (case when phy <= 59 then 0
when phy <= 69 then 0.1
when phy <= 79 then 0.2
when phy <= 89 then 0.3
when phy <= 100 then 0.4
else 0 end) into point from stu where id=id1;
else raise notice ‘please input right course name;’;
end if;
return point;
end;
3、id含 ‘3’ 的同学，求总的绩点，返回绩点最大的ID和总绩点
select id,
(case when math >=0 and math < 60 then 0 when math >=60 and math < 70 then 0.1 when math >=70 and math < 80 then 0.2 when math >=80 and math < 90 then 0.3 when math >=90 and math <= 100 then 0.4 else 0 end) as totlemath,
(case when art >=0 and art < 60 then 0 when art >=60 and art < 70 then 0.1 when art >=70 and art < 80 then 0.2 when art >=80 and art < 90 then 0.3 when art >=90 and art <= 100 then 0.4 else 0 end) as totleart,
(case when phy >=0 and phy < 60 then 0 when phy >=60 and phy < 70 then 0.1 when phy >=70 and phy < 80 then 0.2 when phy >=80 and phy < 90 then 0.3 when phy >=90 and phy <= 100 then 0.4 else 0 end) as totlephy,
(totlemath + totleart + totlephy) as maxtotle
from stu
where id like ‘%3%’
order by maxtotle desc
limit 1;
4、求总绩点，返回绩点最大的ID和总绩点
select id, (case when math<60 then 0
when math>=60 and math<70 then 0.1
when math>=70 and math<80 then 0.2
when math>=80 and math<90 then 0.3
when math>=90 and math<=100 then 0.4
else 0 end)+(case when art<60 then 0
when art>=60 and art<70 then 0.1
when art>=70 and art<80 then 0.2
when art>=80 and art<90 then 0.3
when art>=90 and art<=100 then 0.4
else 0 end)+(case when phy<60 then 0
when phy>=60 and phy<70 then 0.1
when phy>=70 and phy<80 then 0.2
when phy>=80 and phy<90 then 0.3
when phy>=90 and phy<=100 then 0.4
else 0 end) as sum_point
from stu
order by sum_point desc
limit 1 ;
5、按照总绩点输出
select id,(
(case when math >= 0 and math < 60 then 0 when math >= 60 and math < 70 then 0.1 when math >= 70 and math < 80 then 0.2 when math >= 80 and math < 90 then 0.3 when math >= 90 and math <= 100 then 0.4 else 0 end) +
(case when art >= 0 and art < 60 then 0 when art >= 60 and art < 70 then 0.1 when art >= 70 and art < 80 then 0.2 when art >= 80 and art < 90 then 0.3 when art >= 90 and art <= 100 then 0.4 else 0 end) +
(case when phy >= 0 and phy < 60 then 0 when phy >= 60 and phy < 70 then 0.1 when phy >= 70 and phy < 80 then 0.2 when phy >= 80 and phy < 90 then 0.3 when phy >= 90 and phy <= 100 then 0.4 else 0 end)
) as totlevalue
from stu
order by totlevalue;
6、编写add_mask(id1,id2)函数，当id1是当前查询用户时，显示正常ID，如果不是则显示为id2
create or replace function add_mask(id1 varchar(200),id2 varchar(200))
returns varchar(200)
as
begin
if id1=current_user then
return id1;
else
return id2;
end if;
END;
language plpgsql;

示例三：create table su(id int primary key not null,firstname varchar(50) not null,familyname varchar(50) not null,shorterform varchar(50) not null,mask char(1) not null,score int not null);
1、请查询姓名和姓氏，以姓名·姓氏的格式输出，要求首字母大写，姓名和姓氏直接使用”·”拼接
select initcap(concat(firstname,‘·’,familyname)) from su;

2、插入一条新数据(1,‘tom’,‘jerry’,‘tom’,‘H’,63)，当出现主键冲突时，将mask改为’F’
insert into su values(1,’tom’,’jerry’,’tom’,’H’,63) on duplicate key update mask=‘F’;

3、查询表，检查姓名是否是sec，展示姓名，判断结果result
select firstname,(case when firstname like ‘sec%’ then ‘T’ else ‘F’ end) as result from su;

4、查询表中所有列的数据，按照成绩进行排序，并显示名次（position），名次为连续的。要求展示所有字段，名字字段为position
select id,dense_rank() over (order by score desc) as position from su;

示例四：create table student(student_id int,math int,phy int,art int,m2 int);
create table weight(weight_no int,math numeric,phy numeric,art numeric,m2 numeric);
1、求math、phy总成绩以及art、m2的总成绩
select student_id,math+phy as sum_m_p,art+m2 as sum_a_m from student;

2、根据维度表、安装两种加权算法算出每个学生的加权成绩，展示包含student_id,weitht_sum，单个学生加权成绩可以两行输出
select s.student_id,(s.math* w.math + s.phy* w.phy + s.art* w.art + s.m2* w.m2) as weight_sum
from student s,weight w;

3、根据维度表，安装两种加权算法技术出每个学生的加权成绩，展示包含student_id，weight_sum，单个学生加权成绩要求一行输出
select s.student_id,w1.weight1_sum,w2.weight2_sum
from student s
join
(select s.student_id,s.math* w.math+s.phy* w.phy+s.art* w.art+s.m2+w.m2 as weight1_sum from student s,weight w where w.weight_no=1) w1 on s.student_id=w1.student_id
join
(select s.student_id,s.math* w.math+s.phy* w.phy+s.art* w.art+s.m2+w.m2 as weight2_sum from student s,weight w where w.weight_no=2) w2 on s.student_id=w2.student_id;

4、对两种加权总成绩进行排序。要求输出格式student_id,weight_sum、rank1、weight2_sum、rank2
select student_id,weight1_sum,dense_rank() over (partition by 1 order by weight1_sum desc) as rank1,weight2_sum,dense_rank() over (partition by 1 order by weight2_sum desc) as rank2 from
(select s.student_id,w1.weight1_sum,w2.weight2_sum from student s
join
(select s.student_id,s.math* w.math+s.phy* w.phy+s.art* w.art+s.m2* w.m2 as weight1_sum from student s,weight w where w.weight_no=1) w1 on s.student_id=w1.student_id
join
(select s.student_id,s.math* w.math+s.phy* w.phy+s.art* w.art+s.m2* w.m2 as weight2_sum from student s,weight w where w.weight_no=2) w2 on s.student_id=w2.student_id);

示例五：create table RESIDENTS(name varchar(20),age int,sex char(1),building int);
1、为了方便按楼栋给婴儿送纸尿裤，查出每栋age<1的数量，最后显示楼栋信息和对应数量
select building,count(* ) from residents where age<1 group by building;

2、由于每栋楼各个年龄段的人都有，故按age年龄段分组
select age,case when age<18 then ‘group1’
when age>=18 and age<35 then ‘group2’
when age>=35 and age<55 then ‘group3’
else ‘group4’ end as age_group from residents;

3、age_group按每组人数多少排序，查询出age_group、人数、最大年龄、最小年龄、平均年龄（平均年龄向下取整）
select case when age<18 then ‘group1’
when age>=18 and age<35 then ‘group2’
when age>=35 and age<55 then ‘group3’
else ‘group4’ end as age_group,
count(age) as “人数”,
max(age) as “最大年龄”,min(age) as “最小年龄”,floor(avg(age)) as “平均年龄”
from residents
group by age_group
order by “人数” desc;

4、由于需要每天要送食物，增加如下每个年龄段所需食物营养价值表，需要统计出该小区每天总营养值
select sum(case when age<18 then 5 when age>=18 and age<35 then 7 when age>=35 and age<55 then 6
else 5 end) as “每天总营养值” from residents;

如果需要计算每个人需要的总营养值如下：
select name,age,case when age<18 then ‘group1’
when age>=18 and age<35 then ‘group2’
when age>=35 and age<55 then ‘group3’
else ‘group4’ end as age_group,
case when age_group=‘group1’ then 5
when age_group=‘group2’ then 7
when age_group=‘group3’ then 6
when age_group=‘group5’ then 5
end as nutrition_value from residents;

5、按楼栋求出每栋楼所需营养值
select building,
sum (case when age<18 then 5 when age>=18 and age<35 then 7 when age>=35 and age<55 then 6 else 5 end) as “每栋楼所需营养价值” from residents group by building;

示例六：create table student( sno int, sname varchar(20), score int, month int, cno int);
create table class(cno int, cname varchar(20));
1、输出每月月考总分都比学号为5的同学分数高的所有学生信息
lect student.*
from student join (select t1.sno,count(* ) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month
where t1.score>t2.score
group by t1.sno
having count=(select count(distinct month) from student)) stu2 on student.sno=stu2.sno;

2、输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数
select month,sname,cno,count(1) as “缺考次数”
from student
where score is null
group by month,sname,cno;

3、输出每次月考都和tom同时缺考的所有学生信息，要求打印学号、姓名和月考总分
select month,cno,sname,sum(nvl(score,0))
from student
where month in (select month from student where sname=‘Tom’ and score is NULL)
and score is NULL and sname != ‘Tom’ group by month,cno,sname;

4、输出全校月考中位数分数
select avg(nvl(score,0))
from (select score from student order by nvl(score,0) limit (select ceil(count(1)/2)-1 from student),case when (select count(1)%2 from student)=0 then 2 else 1 end);

5、统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分数
select cname,month,max(score)
from student,class
where student.cno=class.cno
group by cname,month
order by cname,month;

示例七：create table student( sno int, sname varchar(20), score int, month int, cno int);
create table class(cno int, cname varchar(20));
1、输出class1班级中比class2班级每月月考最低分还低的学生信息，要求打印学号、姓名和月考总分
select s.sno,s.sname,s.score from student s join
(select month,min(nvl(score,0)) score from student where cno=2 group by month) s1
on s.month = s1.month where nvl(s.score,0) < s1.score and s.cno = 1;

2、打印月考总分平均分最高的学生信息，输出学号，姓名和月考总分平均分
select sno,sname,round(avg(nvl(score,0)),2) as avgscore
from student
group by sno,sname
order by avgscore desc limit 1;

3、输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学号、姓名、月考平均分和差距分数
select sno,sname,round(avg(nvl(score,0)),2) avgscore,(select max(avgscore) from (select round(avg(nvl(score,0)),2) avgscore from student group by sno))-avgscore from student group by sno,sname;

示例八：create table student(id int,score int,month int);
1、查询月考平均成绩比学生编号5的大的学生
select id,round(avg(nvl(score,0)),2) avgscore
from student
group by id
having avgscore >(select round(avg(nvl(score,0)),2) from student where id=5);

2、查询每次月考成绩大于平均成绩的学生
select * from (select s1.* ,avgscore from student as s1 join (select month,round(avg(nvl(score,0)),2) as avgscore from student where score is not null group by month) as s2 on s1.month=s2.month) where score>avgscore;

3、查询每次平均成绩差值
select month,avgscore,avgscore-lag(avgscore) over(order by month) as diff
from (select month,round(avg(nvl(score,0)),2) as avgscore from student group by month order by month);

4、查看所有学生每次和平均成绩差值
select distinct s1.* ,avgscore,nvl(score,0) - avgscore
from student s1 join
(select month,avg(score) over(partition by month) avgscore from student where score is not null group by month,score) s2 on s1.month=s2.month order by s1. id ;

示例九：create table stu(id int,math int,art int,phy int,music int);
1、求math、phy总成绩以及art、music的总成绩
select sum(math+phy),sum(art+music) from stu;

2、计算学生总成绩，并基于总成绩排序
select id,sum(math+phy+art+music) all_score from stu group by id order by all_score desc;

3、art和music总分排名前5的总成绩加5分，查询最终的所有学生总成绩
(select stu .id,sum(score+math+phy) allscore from stu,(select id,sum(art+music)+5 score from stu group by id order by score desc limit 5) stu2 where stu. id= stu2. id group by stu .id)
union all
(select stu .id,sum(score+math+phy) allscore from stu,(select id,sum(art+music) score from stu group by id order by score desc offset 5) stu2 where stu. id= stu2 .id group by stu .id);

示例十：create table scopes(student_id int,chinese int,math int,english int,music int);
1、计算每个学生的chinese和math总分，以及english和music总分，要求一条SQL语句实现，不能使用临时表
select student_id,sum(chinese+math),sum(english+music) from scopes group by student_id;

2、目前有一张权重表（各科有不同的权重，目前权重策略有2个），请算出每个学生结合权重计算后的成绩总和。要求一条SQL语句实现，不能使用临时表。每个学生都对应两个权重成绩。
select student_id,weight_id,sum(t1.chinese* t2.chinese + t1.math* t2.math + t1.english* t2.english + t1.music* t2.music) from scopes t1,weight t2 group by student_id,weight_id order by 1,2;

3、结合上面的结果，将一个学生对应的两个权重成绩，合到一行，要求一条SQL语句实现，不能使用临时表
select tb1.student_id,weight_sum1,weight_sum2 from
(select student_id,sum(t1.chinese* t2.chinese + t1.math* t2.math + t1.english* t2.english + t1.music* t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1
join
(select student_id,sum(t1.chinese* t2.chinese + t1.math* t2.math + t1.english* t2.english + t1.music* t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2
on tb1.student_id=tb2.student_id order by 1;

4、按照两个权重成绩之和的大小，进行从大到小排序，且生成排序序号，要求生成连续排序序号，相同的值具有相同序号。一条SQL语句实现，不能使用临时表
select tb1.student_id,weight_sum1,dense_rank() over(order by weight_sum1 desc) weight_rank1,weight_sum2,dense_rank() over(order by weight_sum2 desc) weight_sum2 from
(select student_id,sum(t1.chinese* t2.chinese + t1.math* t2.math + t1.english* t2.english + t1.music* t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1
join
(select student_id,sum(t1.chinese* t2.chinese + t1.math* t2.math + t1.english* t2.english + t1.music* t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2
on tb1.student_id=tb2.student_id;

四、游标
create table TEACHER(ID INTEGER NOT NULL,NAME VARCHAR(50) NOT NULL,DEPTNO INTEGER NOT NULL,
SALARY FLOAT NOT NULL,TITLE VARCHAR(100) NOT NULL);
create table DEPARTMENT(ID INTEGER NOT NULL,NAME VARCHAR(50) NOT NULL);

1、创建存储过程pro_curs_1，使用游标打印各部门总人数，按人数降序排序，打印格式如下：
部门名称 1—人数
部门名称 2—人数
create or replace procedure pro_curs_1()
as
declare cursor cur1 is select d. name as dname,count(* ) as perscount from teacher t,department d where t.deptno= d .id group by d .name order by perscount desc;
dname varchar(50);
perscount integer;
begin
open cur1;
loop
fetch next from cur1 into dname,perscount;
EXIT WHEN NOT FOUND;
DBE_OUTPUT.PRINT_LINE(concat(dname,‘—’,cast(perscount as varchar(10))));
end loop;
close cur1;
END;
/
call pro_curs_1();

2、创建存储过程pro_curs_2，使用游标读取薪水降序排序的前三位老师和后三位老师的信息，分别获取ID，姓名，部门名称，薪水和职称，请按以下格式打印:
ID-姓名-部门名称-薪水-职称
create or replace procedure pro_curs_2()
as
declare cursor cur2 is
select sid,sname,dname,salary,title from ((select * from teacher order by salary desc limit 3) union all (select * from teacher order by salary limit 3)) t join department d on t. partno=d. id ;
sid integer;
sname varchar(50);
dname varchar(50);
salary float;
title varchar(50);
begin
open cur2;
loop
fetch next from cur2 into sid,sname,dname,salary,title;
dbe_output.print_line(concat(cast(sid as varchar(20),‘-’,sname,‘-’,dname,‘-’,cast(salary as varchar(20)),‘-’,title)));
if cur2 %notfound then exit;
close cur2;
end if;
end loop;
end;
/

五、触发器
示例一
create table student(sno integer,sname varchar(50),ssex varchar(5),sage integer);
create table course(cno integer,cname varchar(50),credit integer);
create table elective(sno integer,cno integer,grade integer);
1、创建视图SELECT_SD，查看学生成绩信息，查看学生姓名，课程名称，课程成绩
create view SELECT_SD as
select s.sname,c.cname,e.grade
from student s,course c,elective e
where s.sno = e.sno and c.cno = e.cno;

2、编写函数FUNC_SUM，返回某个学生的分数总和。
create or replace function FUNC_SUM(stuid integer) returns integer as
declare result integer;
begin
select sum(grade) into result from elective where sno = stuid;
return result;
end;
language plpgsql;

3、创建触发器DELETE_ELE，在STUDENT表上绑定触发器DELETE_ELE，在删除表中某个学生时，将ELECTIVE表中该学生的选课记录一并删除。
create or replace function fun_delete_ele() returns trigger as
begin
delete from elective where sno = old.sno;
return old;
end;
language plpgsql;

create or replace trigger DELETE_ELE before delete on student for each row execute procedure fun_delete_ele();

示例二
create table teacher(id integer primary key,name varchar(50) not null,deptnd integer not null,tetle varchar(50) not null);
create table department(id integer primary key,name varchar(50) not null,number_of_senior integer default 0);
1、创建Tri_update_D触发器，如果修改Number_of_senior字段时提示"不能随便修改部门教授职称人数"，如果已经有了Tri_update_D触发器，则删除后再重建
create or replace function print_info() returns trigger
as
begin
dbe_output.print_line(‘不能随便修改部门教授职称人数’);
return null; – 返回null表示不允许修改，返回old表示可以修改
end;
language plpgsql;

drop trigger if exists Tri_update_D on department;
create trigger Tri_update_D before update of number_of_senior on department for each row execute procedure print_info();

2、禁止触发器，修改DEPARTMENT表中ID=1的NUMBER_OF_SENIOR=10，并查出表中数据
禁用触发器
alter table department disable trigger Tri_update_D;
执行更新操作
update department set number_of_senior=10 where id=1;
查询部门表，查看更新结果
select * from department;

3、启动触发器，修改DEPARTMENT表中ID=1的NUMBER_OF_SENIOR=20
启动触发器
alter table department enable trigger Tri_update_D;
执行更新操作
update department set number_of_senior=20 where id=1;
查询部门表，查看更新结果
select * from department;

示例三create table tab1(sname text, deptno int, salary float, title text);
create table dept(id int, dept_name text);
create table logger(sname text, dept_name text, log_date date);
1、创建触发器函数 T_INS_F
创建触发器，要求在tab1表插入一行数据时，自动往logger表中插入一条记录，记录sname和部门名称, 并用当天的日期来标记该行数据的生成时间。
create or replace function T_INS_F() returns trigger as
begin
insert into logger values(new.sname,(select dept_name from dept where id=new.deptno),sysdate);
return new;
end;
language plpgsql;

2、创建触发器 T_INS_TR
create trigger T_INS_TR after insert on tab1 for each row execute procedure T_INS_F();

3、禁用表 tab1 上的所有触发器
alter table tab1 disable trigger all;

六、存储过程
示例一：
create table student(id serial,starttime timestamp(0));
编写存储过程，生成记录，输入个数，生成student，id从100000开始，starttime是当前时间
create or replace procedure create_student_information(num int)
as
declare i int;
id int;
begin
id :=100000;
for i in 1…num loop
insert into student values(id,sysdate);
id :=id+1;
dbe_output.print_line(‘id=’||id);
end loop;
end;
/
call create_student_informantion(90000);
select count(* ) from aps_student;

示例二：
create table student(student_id int not null,math int not null,pysical int not null,art int not null,music int not null);
1、对学习math和pysical排名前十的学生，art加5分，求所有学生总成绩
(select stu.student_id,sum(math+pysical+art+5+music) allscore from student stu,(select student_id,sum(math+pysical) score from student group by student_id order by score desc limit 10) stu1 where stu.student_id=stu1.student_id group by stu.student_id)
union
(select stu.student_id,sum(math+pysical+art+music) allscore from student stu,(select student_id,sum(math+pysical) score from student group by student_id order by score desc offset 10) stu2 where stu.student_id=stu2.student_id group by stu.student_id);

2、获取art和music排名前十，同时math和pysical在art和music前十名的学生信息
select s1.* ,s2.mscore
from (select * ,(art+music) ascore from student order by ascore desc limit 10) s1
join (select * ,(math+pysical) mscore from student order by mscore desc limit 10) s2
on s1.student_id=s2.student_id;

3、编写存储过程，输入学生id返回总成绩
create or replace procedure get_total_score(id int,totalscore out int)
as
begin
select sum(math+pysical+art+music) into totalscore from student where student_id=id;
– return totalscore;
end;
/

4、编写存储过程，输入学号和科目名称，返回对应的平均成绩
create or replace procedure pro_avg_score(id int,coursename varchar(20),avg_score out int)
as
begin
if coursename=‘math’ then select avg(math) into avg_score from student where student_id=id;
elsif coursename=‘pysical’ then select avg(pysical) into avg_score from student where student_id=id;
elsif coursename=‘art’ then select avg(art) into avg_score from student where student_id=id;
elsif coursename=‘music’ then select avg(music) into avg_score from student where student_id=id;
else raise notice ‘please input right coursename and courseid;’;
avg_score := -1;
end if;
end;
/

5、编写存储过程，对于学生每科成绩，0-59，绩点0,60-69绩点0.1，70-79绩点0.2，80-89绩点0.3，90-100绩点0.4，输入学号和科目名称，返回对应的绩点
create or replace procedure pro2(id int,proname varchar(10),grade out float)
as
begin
if proname = ‘math’ then
select (case when math >= 0 and math < 60 then 0 when math >= 60 and math < 70 then 0.1 when math >= 70 and math < 80 then 0.2 when math >= 80 and math < 90 then 0.3 when math >= 90 and math <= 100 then 0.4 else 0 end) into grade from student where student_id = id limit 1;
elseif proname = ‘pysical’ then
select (case when pysical >= 0 and pysical < 60 then 0 when pysical >= 60 and pysical < 70 then 0.1 when pysical >= 70 and pysical < 80 then 0.2 when pysical >= 80 and pysical < 90 then 0.3 when pysical >= 90 and pysical <= 100 then 0.4 else 0 end) into grade from student where student_id = id limit 1;
elseif proname = ‘art’ then
select (case when art >= 0 and art < 60 then 0 when art >= 60 and art < 70 then 0.1 when art >= 70 and art < 80 then 0.2 when art >= 80 and art < 90 then 0.3 when art >= 90 and art <= 100 then 0.4 else 0 end) into grade from student where student_id = id limit 1;
elseif proname = ‘music’ then
select (case when music >= 0 and music < 60 then 0 when music >= 60 and music < 70 then 0.1 when music >= 70 and music < 80 then 0.2 when music >= 80 and pysical < 90 then 0.3 when music >= 90 and music <= 100 then 0.4 else 0 end) into grade from student where student_id = id limit 1;
else raise notice ‘please input right studentid and coursename’;
end if;
return grade;
end;
/