Sql Server数据库编程存储过程实现和调用

@[toc]

存储过程的实现和调用

设置关系表

之前的作业中已经设计过关系表，这里继续沿用之前设计的关系表

期末计算GPA并分段统计，选出年度三好学生

if (exists (select * from sys.objects where name = 'prt_stu_gpa'))
    DROP PROCEDURE prt_stu_gpa
go
Create procedure prt_stu_gpa as
begin 
    declare @v_no char(6),@v_gpa float
    declare s_cur cursor for select Sno,round(sum(Ccredit*Grade/25.0)/sum(Ccredit),2) as gpa
    from Course19377056,Sc19377056 where Course19377056.Cno = Sc19377056.Cno and Grade>=60
    group by Sno

    open s_cur
    fetch s_cur into @v_no,@v_gpa
    while (@@FETCH_STATUS=0)
    begin
        if (@v_gpa>=3.6) 
            begin
                print @v_no + convert(char(8),@v_gpa) + '成绩优秀'  
                print @v_no + '是优秀学生'
            end
        else if (@v_gpa>=3.2) print @v_no + convert(char(8),@v_gpa) + '成绩合格'
        else                print @v_no  + convert(char(8),@v_gpa) + '成绩不合格'
        fetch s_cur into @v_no,@v_gpa
    end
    close s_cur
    deallocate s_cur
end

Exec prt_stu_gpa
go

S001  3.6     成绩优秀
S001  是优秀学生
S002  2.98    成绩不合格
S003  3.17    成绩不合格
S101  3.15    成绩不合格
S201  2.64    成绩不合格
S202  3.44    成绩合格
S203  3.34    成绩合格
S301  3.44    成绩合格
S302  3.84    成绩优秀
S302  是优秀学生
S401  3.54    成绩合格
S402  3.59    成绩合格
S403  3.37    成绩合格
S501  3.59    成绩合格
S502  3.64    成绩优秀
S502  是优秀学生
S503  3.31    成绩合格

触发器实现

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

Create trigger Tsecure_sc on Sc19377056 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)
ROLLBACK TRANSACTION
End
End

update Sc19377056 set Grade =70 
where Sno='S002' and Cno = '8002'

Msg 50000, Level 16, State 1, Procedure Tsecure_sc, Line 10 [Batch Start Line 72]
只许在工作时间操作
Msg 3609, Level 16, State 1, Line 73
事务在触发器中结束。批处理已中止。

Completion time: 2021-11-21T10:24:35.4024546+08:00

建立成绩更新记录表

CREATE TABLE Stu_update(   #创建更新之后的保存表
    Sno char(8) not null, 
    Cno char(8) not null, 
    Old_grade tinyint null,
    New_grade tinyint null,
    Time_ud Datetime,
    Sys_user char(6))
Create trigger Td_student2 on Sc19377056 for update  #创建触发器，要求如题目所示
as
Begin
if @@rowcount = 0
return
insert into Stu_update(Sno,Cno,Old_grade,New_grade,Time_ud,Sys_user)
select deleted.Sno, deleted.Cno,deleted.Grade,inserted.Grade,
GETDATE(), SYSTEM_USER
from deleted,inserted
End

select * from Stu_update  #观察更新数据前后新表的变化

update Sc19377056 set Grade =90
where Sno='S002' and Cno = '8002'
update Sc19377056 set Grade =90
where Sno='S003' and Cno = '9001'

select * from Stu_update

更新前

更新后

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

# !usr/python/bin
# -*- coding:utf-8 -*-
import pyodbc #使用pyodbc操作数据库
 
sqlconn = pyodbc.connect(DRIVER='{SQL Server}',  #输入参数进行连接
                         SERVER='127.0.0.1,1433',
                         DATABASE='STUDENT19377056',
                         UID='sa',
                         PWD='root')
 
cursor = sqlconn.cursor()  #创建游标

sql = 'select Sno,round(sum(Ccredit*Grade/25.0)/sum(Ccredit),2) as gpa from Course19377056,Sc19377056\
        where Course19377056.Cno = Sc19377056.Cno and Grade>=60 group by Sno'
cursor.execute(sql)  #执行sql语句
rows = cursor.fetchall()
for row in rows: #获得学生的GPA
    print(row.Sno,row.gpa)

S001     3.6
S002     2.98
S003     3.17
S101     3.15
S201     2.64
S202     3.44
S203     3.34
S301     3.44
S302     3.84
S401     3.54
S402     3.59
S403     3.37
S501     3.59
S502     3.64
S503     3.31