存储过程，游标，sql过程，触发器，ODBC api

先导

begin
use h1_1
select * from course_1
select * from Htable1_1
select * from sc_1
select * from stu_gpa
select * from student_1
end

在作业1，2的时候总共生成了5个表

课程表格

专业号

成绩表

学生信息表

不及格同学名单

存储过程实现与调用

1、设计关系表，计算并保存学生选课统计信息

create procedure info_stu_cou as
begin
--定义变量学号、姓名、选课数量、平均成绩
declare @sno char(10),@sname char(10),@total int,@ave_grade float,@cnt int
select @cnt=0

--创建表
create table stu_cou(
sno char(10) not null primary key,
sname char(10) not null,
scount int not null,
sgrade float not null,
)

--定义游标
declare s_cur cursor
for select student_1.sno as '学号',sname as '姓名',count(*) as '选课数量',CONVERT(decimal(10,2),avg(grade)) as '平均成绩'
from student_1,sc_1
where student_1.sno=sc_1.sno
group by sc_1.sno,student_1.sno,sname

--打开游标
open s_cur

--滚动读取数据
fetch s_cur into @sno,@sname,@total,@ave_grade
while (@@FETCH_STATUS=0)
begin
	--插入数据进入表格
	insert into stu_cou values (@sno,@sname,@total,@ave_grade)
	select @cnt=@cnt+1
	fetch s_cur into @sno,@sname,@total,@ave_grade
end

--关闭\释放游标
close s_cou
deallocate s_cou
end

--执行存储程序
--drop procedure info_stu_cou删除存储过程
exec info_stu_cou
select * from stu_cou
go

2、期末计算所有学生GPA，并分段表示

create procedure GPA as
begin
	--定义变量 学号、姓名、选课序号、学分、成绩、gpa
	declare @sno char(10),@sname char(10),@cno char(10),@credit float,@grade float,@gpa float,@cnt1 int,@cnt2 int,@cnt3 int
	select @gpa=0,@cnt1=0,@cnt2=0,@cnt3=0
	--创建表
	create table s_GPA(
	sno char(10) not null,
	sname char(10) not null,
	gpa float not null
	)
	--创建游标
	declare s_co cursor for 
	select student_19377215.sno as '学号',sname,convert(decimal(10,2),sum(ccredit*grade)/sum(ccredit)*4.0/100.0) as 'GPA'
	from student_1,sc_1,course_1
	where student_1.sno=sc_1.sno and sc_1.cno=course_1.cno
	group by student_1.sno,sname
	--打开游标
	open s_co
	--滚动读取数据
	fetch s_co into @sno,@sname,@gpa
	while (@@FETCH_STATUS=0)
	begin
	--插入数据进入表格
		insert into s_GPA values (@sno,@sname,@gpa)
		
	--分段统计
		if (@gpa>3.6) select @cnt1=@cnt1+1
		else if (@gpa<=3.6 and @gpa>3.4) select @cnt2=@cnt2+1
		else  select @cnt3=@cnt3+1
		fetch s_co into @sno,@sname,@gpa
	end

	--关闭\释放游标
	close s_co
	deallocate s_co
	print str(@cnt1) + '人 成绩优秀'
	print str(@cnt2) + '人 成绩良好'
	print str(@cnt3) + '人 成绩一般'
end
drop procedure GPA
drop table s_GPA
exec GPA
select * from s_GPA
go

3、设置年度优秀学生评选条件

--年度优秀学生
create procedure annual_stu as
begin
    --变量定义 学号，学生姓名，学生gpa
	declare @sno char(10),@sname char(10),@gpa float
	--定义游标
	declare s_co cursor for select sno,sname,gpa
	from s_GPA
	--打开游标
	open s_co
	--滚动读取
	fetch s_co into @sno,@sname,@gpa
	while(@@FETCH_STATUS=0)
	begin
		if (@gpa>3.80) print '优秀学生：' + ' '+@sno+' '+@sname
		fetch s_co into @sno,@sname,@gpa
	end
	close s_co
	deallocate s_co
end
exec annual_stu
go

触发器实现

1、禁止在非工作时间更新学生成绩

--安全性，禁止在特定时间更新数据
create trigger tsecure_sc on sc_1 for insert,delete,update as
begin 
	if ((DATENAME(weekday,getdate()))='星期六' or
	(DATENAME(weekday,getdate()))='星期日' or
	CONVERT(int,DATENAME(hour,getdate())) not between 9 and 17)
	begin
		raiserror('只许在工作时间操作',16,1)--16表示严重程度，1表示返回错误的位置
		rollback transaction
	end
end

update sc_19377215 set grade=90 where sno=19377210 and cno=1904

2、建立一个成绩更新记录表，每当成绩被更新，将操作时间【getdate()】，操作账户【system_user】、被更新学生的学号、旧成绩、新成绩写入更新记录表

--建立新表
--操作时间，操作账户，学号，旧成绩，新成绩
use h1_1
create table new_grade(
up_time char(50) not null,
up_user char(50) not null,
up_sno char(10) not null,
old_grade int not null,
new_grade int not null,
primary key(up_time)
)
go

create trigger up_grade on sc_19377215 for update
as
begin
	declare @time char(50),@user char(50),@sno char(10),@ograde int,@ngrade int
	--通过这一步将自带的特殊表内的值传入
	declare s_co cursor for select inserted.sno,deleted.grade,inserted.grade from inserted ,deleted 
	where inserted.sno=deleted.sno

	open s_co
	
	fetch s_co into @sno,@ograde,@ngrade
	while(@@FETCH_STATUS=0)
	begin
		insert into new_grade values(GETDATE(),SYSTEM_USER,@sno,@ograde,@ngrade)
		fetch s_co into @sno,@ograde,@ngrade
	end

	close s_co
	deallocate s_co
end

update sc_1 set grade=90 where sno=1 and cno=1904
select * from new_grade

选做 高级语言的数据库连接与数据表访问

配置数据源

需要打开sql sever browser才可以搜索到服务器

需要做以下环境配置

#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>
#include <stdio.h>
int main()
{
	HENV henv;
	HDBC hdbc;
	HSTMT hstmt;
	/*datasource指的是添加在用户DSN内的数据源名称，设置时我默认库为h1_*** */
	unsigned char datasource[]="*"; 
	unsigned char user[]="sa";
	unsigned char pwd[]="*";
	unsigned char search[]="select * from sc_19377215";
 	SQLRETURN retcode;
 	retcode = SQLAllocEnv(&henv);
 	retcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 	retcode = SQLAllocConnect(henv,&hdbc);
 	retcode = SQLConnect(hdbc,datasource,SQL_NTS,user,SQL_NTS,pwd,SQL_NTS);
 	retcode = SQLAllocStmt(hdbc,&hstmt);
 	retcode = SQLExecDirect(hstmt,search,SQL_NTS);
 	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
 	{
	 	char sno[10];
	 	char cno[10];
		char grade[10]; 
		SQLBindCol(hstmt,1,SQL_C_CHAR,sno,50,0);
		SQLBindCol(hstmt,2,SQL_C_CHAR,cno,50,0);
		SQLBindCol(hstmt,3,SQL_C_CHAR,grade,50,0);
		while(SQLFetch(hstmt)!=SQL_NO_DATA){
			printf("%-10s%-10s%-10s\n",sno,cno,grade);

		}
 	}
	SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
	SQLDisconnect(hdbc);
	SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
	SQLFreeHandle(SQL_HANDLE_ENV,henv);
	return 0;
}

成绩读取结果

LBindCol(hstmt,3,SQL_C_CHAR,grade,50,0);
while(SQLFetch(hstmt)!=SQL_NO_DATA){
printf(“%-10s%-10s%-10s\n”,sno,cno,grade);

	}
}
SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
SQLFreeHandle(SQL_HANDLE_ENV,henv);
return 0;

}

成绩读取结果