Thứ Tư, 9 tháng 9, 2015

Bài tập lớn:store procedure và trigger trong sql


Sử dụng store procduced và trigger để xây dựng các chương trình quản lý
I)Tạo bảng
thành viên:
+lại văn dũng Nhóm trưởng:xây dựng ,phân công nhiệm vụ(
dung.1300984@thanhdo.edu.vn
)
+Lê xuân linh : viết chương trình kết nối csdl(
thoicungduoc0@gmail.com
)
+lê hữu đoàn:xây dựng thêm truy vấn liên quan(
doan.1300738@thanhdo.edu.vn
)
+hoàng văn sơn :hoàn thiện chương trình(
son.1300915@thanhdo.edu.vn
)
+lương kim lương:kiểm tra,test chương trình(
luongevil@gmail.com
+trần xuân minh:đóng góp ý tưởng liên quan)

 +)Tạo bảng đăng nhập
USE [QLsinhvien]
GO

/****** Object:  Table [dbo].[DangNhap]    Script Date: 9/8/2015 10:00:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DangNhap](
       [UserName] [nchar](50) NOT NULL,
       [Pass] [nchar](50) NOT NULL,
 CONSTRAINT [PK_DangNhap] PRIMARY KEY CLUSTERED
(
       [UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



+)Tạo bảng điểm
USE [QLsinhvien]
GO

/****** Object:  Table [dbo].[Diem]    Script Date: 9/8/2015 10:01:30 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Diem](
       [MaSV] [nchar](50) NOT NULL,
       [MaM] [nchar](50) NOT NULL,
       [Diem] [float] NULL
) ON [PRIMARY]

GO



+)Tạo bảng lưu sinh viên
USE [QLsinhvien]
GO

/****** Object:  Table [dbo].[luusv]    Script Date: 9/8/2015 10:02:01 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[luusv](
       [MSV] [nchar](50) NULL,
       [TenSV] [nchar](50) NULL,
       [NgaySinh] [datetime] NULL,
       [QueQuan] [nchar](50) NULL,
       [NgayTD] [datetime] NULL
) ON [PRIMARY]

GO



+)Tạo bảng môn học
USE [QLsinhvien]
GO

/****** Object:  Table [dbo].[MonHoc]    Script Date: 9/8/2015 10:02:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MonHoc](
       [MaM] [nchar](50) NOT NULL,
       [TenM] [nchar](50) NULL,
       [SoTC] [int] NULL,
       [HocKi] [int] NULL,
 CONSTRAINT [PK_MonHoc] PRIMARY KEY CLUSTERED
(
       [MaM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



+)Tạo bảng sinh viên
USE [QLsinhvien]
GO

/****** Object:  Table [dbo].[sv]    Script Date: 9/8/2015 10:02:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[sv](
       [MaSV] [nchar](50) NOT NULL,
       [TenSV] [nchar](50) NULL,
       [GioiTinh] [nchar](50) NULL,
       [NgaySinh] [datetime] NULL,
       [QueQuan] [nchar](50) NULL,
       [Hinhanh] [varbinary](max) NULL,
 CONSTRAINT [PK_sv] PRIMARY KEY CLUSTERED
(
       [MaSV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


II) StoredProcedure

+)Load dữ liệu masv,tensv lên combox sv
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[cboSinh]    Script Date: 9/8/2015 9:39:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[cboSinh]
as
select sv.MaSV,sv.TenSV
from sv

+)Load dữ liệu MaM,TenM lên combox MonHoc
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[cboTenMH]    Script Date: 9/8/2015 9:42:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[cboTenMH]

as
select  MonHoc.MaM, MonHoc.TenM
from MonHoc

+)chọn điểm SV
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[ChonD]    Script Date: 9/8/2015 9:43:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ChonD]
as
SELECT [MaSV]
      ,[MaM]
      ,[Diem]
  FROM [dbo].[Diem]

+)chọn MaMH:
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[ChonMH]    Script Date: 9/8/2015 9:44:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ChonMH]
as
SELECT [MaM]
      ,[TenM]
      ,[SoTC]
      ,[HocKi]
  FROM [dbo].[MonHoc]

+)chọn SV
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[Chonsv]    Script Date: 9/8/2015 9:44:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Chonsv]
as
SELECT [MaSV]
      ,[TenSV]
      ,[GioiTinh]
      ,[NgaySinh]
      ,[QueQuan]
    
  FROM [dbo].[sv]

+)chọn SV có hình SV
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[ChonSV1]    Script Date: 9/8/2015 9:46:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ChonSV1]
as
SELECT [MaSV]
      ,[TenSV]
      ,[GioiTinh]
      ,[NgaySinh]
      ,[QueQuan]
      ,[Hinhanh]
  FROM [dbo].[sv]

+)chọn hình ảnh cho SV
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[Hinhsv]    Script Date: 9/8/2015 9:46:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Hinhsv]
as
SELECT
      [Hinhanh]
  FROM [dbo].[sv]

+)kiểm tra đăng nhập
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[Log_in]    Script Date: 9/8/2015 9:47:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Log_in]
@UserName nchar(50),
@pass nchar(50)
as
SELECT [UserName]
      ,[Pass]
  FROM [dbo].[DangNhap]
  where @UserName =UserName and
@pass =Pass

+)sửa dữ liệu SV
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[SuaD]    Script Date: 9/8/2015 9:48:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SuaD]

 @MaSV nchar(50),
   @MaM nchar(50),
    @Diem float
       as

UPDATE [dbo].[Diem]
   SET [MaSV] = @MaSV
      ,[MaM] = @MaM
      ,[Diem] = @Diem
 WHERE [MaSV] = @MaSV

+)Sửa môn học
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[SuaMh]    Script Date: 9/8/2015 9:48:43 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SuaMh]
 @MaM nchar(50),
     @TenM nchar(50),
     @SoTC int,
      @HocKi int
         as
UPDATE [dbo].[MonHoc]
   SET [MaM] = @MaM
      ,[TenM] = @TenM
      ,[SoTC] = @SoTC
      ,[HocKi] = @HocKi
 WHERE [MaM] = @MaM

+)Sửa sinh viên
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[SuaSV1]    Script Date: 9/8/2015 9:49:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SuaSV1]
 @MaSV nchar(50)
      , @TenSV nchar(50)
      , @GioiTinh nchar(50)
      , @NgaySinh datetime
      , @QueQuan nchar(50)
      , @Hinhanh varbinary(max)
         as
UPDATE [dbo].[sv]
   SET [MaSV] = @MaSV
      ,[TenSV] = @TenSV
      ,[GioiTinh] = @GioiTinh
      ,[NgaySinh] = @NgaySinh
      ,[QueQuan] = @QueQuan
      ,[Hinhanh] = @Hinhanh
 WHERE  [MaSV] = @MaSV

+)Tìm Mã Môn học
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[TimMH]    Script Date: 9/8/2015 9:49:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[TimMH]
 @MaM nchar(50),
  @TenM nchar(50)
as
select MonHoc.MaM,MonHoc.TenM
from MonHoc
where @MaM =MonHoc.MaM or @TenM =MonHoc.TenM

+)Tìm Sinh Viên
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[Timsv]    Script Date: 9/8/2015 9:50:16 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Timsv]
@MaSV nchar(50),
@TenSV nchar(50)
as
SELECT [MaSV]
      ,[TenSV]
      ,[GioiTinh]
      ,[NgaySinh]
      ,[QueQuan]
  FROM [dbo].[sv]
  where @MaSV =MaSV or
@TenSV =TenSV

+)tim sinh viên tt
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[Timsv]    Script Date: 9/8/2015 9:50:16 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Timsv]
@MaSV nchar(50),
@TenSV nchar(50)
as
SELECT [MaSV]
      ,[TenSV]
      ,[GioiTinh]
      ,[NgaySinh]
      ,[QueQuan]
  FROM [dbo].[sv]
  where @MaSV =MaSV or
@TenSV =TenSV

+)Thêm điểm cho sv
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[ThemD]    Script Date: 9/8/2015 9:51:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ThemD]
(@MaSV nchar(50)
,@MaM nchar(50)
,@Diem float)
as
INSERT INTO [dbo].[Diem]
           ([MaSV]
           ,[MaM]
           ,[Diem])
     VALUES
           ( @MaSV
           ,@MaM
           ,@Diem )

+)Thêm Môn học
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[ThemMH]    Script Date: 9/8/2015 9:51:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ThemMH]
    (@MaM nchar(50)
           ,@TenM nchar(50)
           ,@SoTC int
           ,@HocKi int)
                 as
INSERT INTO [dbo].[MonHoc]
           ([MaM]
           ,[TenM]
           ,[SoTC]
           ,[HocKi])
     VALUES
       (@MaM
           ,@TenM
           ,@SoTC
           ,@HocKi )

+)Thêm Sinh Viên
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[ThemSV]    Script Date: 9/8/2015 9:52:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ThemSV]

 (@MaSV nchar(50)
           ,@TenSV nchar(50)
           ,@GioiTinh nchar(50)
           ,@NgaySinh datetime
           ,@QueQuan nchar(50)
           ,@Hinhanh varbinary(max))
                 as
INSERT INTO [dbo].[sv]
           ([MaSV]
           ,[TenSV]
           ,[GioiTinh]
           ,[NgaySinh]
           ,[QueQuan]
           ,[Hinhanh]
                 )
     VALUES
         (@MaSV
           ,@TenSV
           ,@GioiTinh
           ,@NgaySinh
           ,@QueQuan
           ,@Hinhanh)

+)Tra cứu Sinh Viên
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[TRaCuuSV]    Script Date: 9/8/2015 9:52:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[TRaCuuSV]
 @MaSV nchar(50)
 as


select sv.TenSV,sv.QueQuan,sv.GioiTinh,sv.Hinhanh,sv.NgaySinh,TEMP.DiemTB,xeploai=case  when TEMP.DiemTB >8 then 'gioi' when TEMP.DiemTB >7 then 'kha' when TEMP.DiemTB>5 then 'trung binh' else 'Yeu' end
from sv, (select Diem.MaSV,ROUND( sum(Diem.Diem*MonHoc.SoTC)/SUM(MonHoc.SoTC),2)AS DiemTB
from Diem inner join MonHoc on Diem.MaM=MonHoc.MaM
group by Diem.MaSV
--having @MaSV=Diem.MaSV
)AS TEMP
where @MaSV=sv.MaSV

+)Xóa điểm sinh viên
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[XoaD]    Script Date: 9/8/2015 9:52:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[XoaD]
 @MaSV nchar(50)
 as
DELETE FROM [dbo].[Diem]
      WHERE [MaSV] = @MaSV

+)Xóa dữ liệu điểm
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[XoaDLD]    Script Date: 9/8/2015 9:53:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[XoaDLD]
as
TRUNCATE TABLE Diem
+)Xóa dữ liệu trùng
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[XoaDLtrung]    Script Date: 9/8/2015 9:53:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  StoredProcedure [dbo].[XoaDLtrung]    Script Date: 07/09/2015 10:18:25 CH ******/
ALTER proc [dbo].[XoaDLtrung]
@MaSV nchar(50),
@MaM nchar(50)
as
select Diem.MaM,Diem.MaSV
from Diem
where @MaSV=MaSV and @MaM=MaM

+)Xóa mã môn học
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[XoaMH]    Script Date: 9/8/2015 9:54:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[XoaMH]
 @MaM nchar(50)
 as
DELETE FROM [dbo].[MonHoc]
      WHERE [MaM] = @MaM

+)Xóa Sinh Viên
USE [QLsinhvien]
GO
/****** Object:  StoredProcedure [dbo].[Xoasv]    Script Date: 9/8/2015 9:55:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Xoasv]
@masv nchar(50)
as
DELETE FROM [dbo].[sv]
      WHERE @masv=masv
III)trigger
+)xóa môn học:
USE [QLsinhvien]
GO
/****** Object:  Trigger [dbo].[XoaM]    Script Date: 9/8/2015 10:06:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[XoaM]
on [dbo].[MonHoc]
after Delete
as
begin
delete from Diem where Diem.MaM=(select MaM from deleted)
end

+)lưu sinh viên:
USE [QLsinhvien]
GO
/****** Object:  Trigger [dbo].[LuuS]    Script Date: 9/8/2015 10:06:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[LuuS]
on [dbo].[sv]
after delete
as
begin
declare @msv nchar(50)
declare @tensv nchar(50)
declare @Ngaysinh datetime
declare @QueQuan nchar(50)
declare @Ngaytd  datetime

 set @msv= (select MaSV from deleted)
set @tensv= (select TenSV from deleted)
set @Ngaysinh= (select NgaySinh from deleted)
set @QueQuan= (select QueQuan from deleted)
insert into LuuSV
values (@msv,@tensv,@Ngaysinh,@QueQuan,GETDATE())
end

+)xóa sinh viên:
USE [QLsinhvien]
GO
/****** Object:  Trigger [dbo].[XoaS]    Script Date: 9/8/2015 10:07:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  Trigger [dbo].[XoaM]    Script Date: 30/08/2015 7:43:10 CH ******/

ALTER trigger  [dbo].[XoaS]
on [dbo].[sv]
after Delete
as
begin
delete from Diem where Diem.MaSV=(select MaSV from deleted)
end













Người đăng: vào lúc Không có nhận xét nào:
Đăng ký: Bài đăng (Atom)