成绩管理系统——数据库编程（服务器端）

数据库编程分为数据库客户端编程与数据库服务器端编程，在完成任务之前需要对控制语句的格式、函数编程 ，存储过程编程，游标编程、触发器以及嵌入式SQL编程等的知识有所了解。

变量声明

使用变量之前，必须在PL/pgSQL块的DECLARE下进行声明

DECLARE

对于行类属性变量（类似于结构体，包含了一个表的所有列属性）varible  table% ROWTYPE，应用于游标逐行处理。

对于表内某一属性变量：varible  table.column% TYPE。

函数编程

基本语法格式

CREATE [ OR REPLACE ] FUNCTION  name      ( type args)

    RETURNS retype 

AS $$         //$$用于声明函数的实际代码的开始

DECLARE

        -- 声明段

BEGIN

        --函数体语句

END;

$$ LANGUAGE lang_name;  //$$ 表明代码的结束, LANGUAGE后面指明所用的编程语言

其中OR REPLACE的作用是查看是否已有同名函数，有的话就替代，name的声明与C语言类似，有参数的话就声明参数，如fun_name(type args），然后是返回类型RETURN type，下面就是函数的基本语法格式，以$$声明函数开始和结束，最后加上语言，比如plpgsql（Procedural Language/PostgreSQL）。

1.查询窗口执行函数的形式：select fuction(参数);

2.在函数或存储过程中调用数据库函数，语法形式select  into 接收结果的变量  from 函数名（参数），不需要返回值的话，直接PERFORM 函数名（）

游标编程

游标（Cursor）是一种临时的数据库对象；用来存放从数据库表中查询返回的数据记录；提供了从结果集中提取并分别处理每一条记录的机制；游标总是与一条SQL查询语句相关联。个人理解类似于java的iterator（迭代器）或C语言的指针

1.游标声明（在DECLARE部分）：name CURSOR FOR SELECT * FROM table;

2.打开游标：OPEN unbound_cursor FOR query;

分为定义时绑定与未绑定
绑定：bound_cursor CURSOR FOR SELECT * FROM table;

OPEN bound_cursor;

未绑定：name RECURSOR；

OPEN unbound_cursor FOR SELECT * FROM table (WHERE ...);

3.从游标获取数据给变量：FETCH cursor INTO SID, Sname, sex;因为游标在声明不进行限定（SELCT *）的话读取到的是行数据，属性列必须与目标列的数量一致，并且类型兼容。

4.关闭游标：CLOSURE cursor;

存储过程编程

作用：把经常会被重复使用的SQL语句逻辑块封装起来，经编译后，存储在数据库服务器端

类似于函数声明

CREATE OR REPLACE PROCEDURE procedureName([argmode] argname argtype, ... )

AS $$        //指明存储过程开始

DECLARE

   变量1 数据类型 :=  初始值1;  

   ......

BEGIN

   -- 程序执行体语句;

END;

$$ LANGUAGE plpgsql   //指明存储过程结束，以及使用PL/pgSQL语言实现

 不同于函数的是对于参数和返回值全部放在一起，声明式需指出是输入参数还是输出，argtype包括IN、OUT或INOUT参数模式。IN为输入参数；OUT为输出参数，INOUT则是在过程中操作之后返回，就不用声明全局变量或者指针了。

触发器

触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行

触发器执行的次数可分：语句级触发器（FOR  EACH  STATEMENT）和行级触发器（FOR  EACH  ROW）

 按触发时间分为：BEFORE,AFTER,INSTEAD OF

相关变量：NEW存有INSERT或UPDATE操作产生的新的数据行,OLD存有被UPDATE或DELETE操作修改或删除的旧的数据行,TG_OP值为INSERT、UPDATE、DELETE 的一个字符串，它说明触发器是为哪个操作引发

CREATE  TRIGGER  触发器名  

    { BEFORE | AFTER | INSTEAD OF }

     ON 表名

     [ FOR [ EACH ] { ROW | STATEMENT } ]

     EXECUTE PROCEDURE 存储过程名 ( 参数列表 )

 触发器指明触发条件以及触发的函数。

服务器端

数据库后端编程练习

在一个成绩管理系统数据库GradeDB中，定义如下关系表：

        STUDENT(SID,SName,Age,Sex)

　　GRADE(SID,CID,Score, Note)

　　COURSE(CID,CName,Teacher)

（1）编写SQL程序创建数据库及其关系表，并插入10个学生的2门课程（“数据库原理及应用”、“数据结构与算法”）成绩数据；

（2）编写触发器程序实现GRADE表数据修改在日志表（GradeLOG）记录相关操作数据，如操作用户、学号、课程号、修改时间、修改前成绩、修改后成绩；

（3）编写存储过程程序实现统计各课程不及格学生人数，并在屏幕输出。

(1)

CREATE DATABASE GradeDB

CREATE table student
(
	sid char(13) PRIMARY KEY,
	sname varchar(10) NOT NULL,
	age int NOT NULL,
	sex char(2) NOT NULL
)

CREATE table grade
(
	sid char(13) NOT NULL,
	CID char(8) NOT NULL,
	score numeric(5,1) NOT NULL,
	note varchar(50) NULL,
	CONSTRAINT score PRIMARY KEY(sid,cid)
)

CREATE table course
(
	cid char(8) NOT NULL PRIMARY KEY,
	cname varchar(20) NOT NULL  CHECK(cname in('数据库原理及应用','数据结构与算法')),
	teacher  varchar(10) NOT NULL
)

插入学生成绩数据前，要先对学生信息和课程信息初始化

INSERT INTO student values
('2000090901001','赵一',20,'男'),
('2000090901002','钱二',20,'男'),
('2000090901003','孙三',20,'男'),
('2000090901004','李四',20,'男'),
('2000090901005','周五',20,'男'),
('2000090901006','郑六',20,'女'),
('2000090901007','王七',20,'男'),
('2000090901008','冯八',20,'男'),
('2000090901009','陈九',20,'女'),
('2000090901010','马十',20,'男')

INSERT INTO course values
('R0907920','数据库原理及应用','文老师'),
('P0900630','数据结构与算法','何老师')

INSERT INTO grade values
('2000090901001','R0907920',85,'优秀'),
('2000090901002','R0907920',59,'不及格'),
('2000090901003','R0907920',94,'优秀'),
('2000090901004','R0907920',75,'良好'),
('2000090901005','R0907920',98,'优秀'),
('2000090901006','R0907920',73,'良好'),
('2000090901007','R0907920',86,'优秀'),
('2000090901008','R0907920',90,'优秀'),
('2000090901009','R0907920',84,'良好'),
('2000090901010','R0907920',53,'不及格'),

('2000090901001','P0900630',98,'优秀'),
('2000090901002','P0900630',94,'优秀'),
('2000090901003','P0900630',73,'良好'),
('2000090901004','P0900630',75,'良好'),
('2000090901005','P0900630',90,'优秀'),
('2000090901006','P0900630',58,'不及格'),
('2000090901007','P0900630',86,'优秀'),
('2000090901008','P0900630',85,'优秀'),
('2000090901009','P0900630',84,'良好'),
('2000090901010','P0900630',97,'优秀')

至此，数据库初始化已经完成

（2）编写触发器程序实现GRADE表数据修改在日志表（GradeLOG）记录相关操作数据

本题考点是函数和触发器的编程，先要创建一个日志表，创建触发器使得数据操作时茶法函数调用，在日志表里新增记录

CREATE table gradelog
(
	username varchar(10) NOT NULL,
	sid char(13) NOT NULL,
	cid char(8) NOT NULL,
	audit_time time NOT NULL,
	old_score numeric(5,1),
	new_score numeric(5,1)
)

CREATE OR REPLACE FUNCTION score_audit()
RETURNS TRIGGER
AS $$
	
BEGIN 
	IF(TG_OP='DELETE')
	THEN
		INSERT INTO gradelog 
		SELECT user,old.sid,old.cid,now(),old.score;
		RETURN old;
	ELSEIF(TG_OP='INSERT')THEN
		INSERT INTO gradelog 
		SELECT user,new.sid,new.cid,now(),NULL,new.score;
		RETURN new;
	ELSEIF(TG_OP='UPDATE')THEN
		INSERT INTO gradelog 
		SELECT user,old.sid,old.cid,now(),old.score
		WHERE old.sid=new.sid AND old.cid=new.cid;
		RETURN new;
	END IF;
	RETURN NULL;
END
$$ LANGUAGE plpgsql

CREATE TRIGGER score_audit_trig
AFTER INSERT OR DELETE OR UPDATE
ON grade
FOR EACH ROW
EXECUTE PROCEDURE score_audit();

看一下效果，分别用插入，修改，删除语句使触发器起作用

INSERT INTO grade values('2000090901023','R0907920',85,'优秀');

UPDATE grade 
SET score=84 , note='良好'
WHERE sid='2000090901023';

DELETE
FROM grade
WHERE sid='2000090901023'

结果是下图，没什么问题

（3）存储过程程序实现统计各课程不及格学生人数

本题的考点是存储过程和游标的使用，由于是以各课程分组，所以用游标读取课程号

CREATE OR REPLACE PROCEDURE countnum()
AS $$
DECLARE
	cname course.cname%TYPE;
	cnt integer;
	tmp course.cid%TYPE;
	cs CURSOR FOR SELECT cid FROM course;
BEGIN 
	OPEN cs;
	LOOP 
		FETCH cs into tmp;
		--验证游标有误数据
		IF NOT FOUND THEN 
			EXIT;
		END IF;
		
		SELECT COUNT(*) INTO cnt
		FROM grade
		WHERE cid = tmp and score<60;
		IF tmp='R0907920' THEN 
			cname='数据库原理及应用';
		ELSE cname='数据结构与算法';
		END IF;
		
		--RAISE INFO输出语句到Message窗口
		RAISE INFO '课程 % 有 % 人不及格',cname,cnt;
	END LOOP;
	CLOSE cs;
END
$$ LANGUAGE plpgsql;

用call调用存储过程，结果正确

服务器端的内容就是这些，客户端的内容请参见另一篇博文 成绩管理系统——数据库编程（客户端）。