Hướng Dẫn Tìm Hiểu View + Proc Trong SQL - Lập Trình SQL Server

TRẦN VĂN ĐIỆP

2021-04-10 12:29:32

-- Active database use C2010L -- TEST select * from Student select * from Subject select * from Marks -- Query ---- Hien thi thong tin diem thi sinh vien: roll_number, student_name, subject_name, marks select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id ---- Bai toan: Luu lai truy van tren thi lam cach nao -> Cach thong thuong -> save file sql -> sau mo lai va test ---- Bai toan 2: Neu phat trien du an theo team (nhieu thanh vien) -> cung su dung chung 1 database --------- Lam sao de chia se cac query -> thanh vien trong du an co the su dung lai query ban da viet thi lam cach nao??? ---- Bai toan 3: Phat trien chuong trinh phan mem: --------- Cach 1: Nhung sql trong code ngon ngu lap trinh --------- Cach 2: Luu toan sql trong database -> su dung thong qua 1 view & proc -> su dung dc query ---- View & Proc => giup chung ta => quan ly dc cau query trong chinh database -- I) View: Luu 1 truy van select -> view se dc hieu nhu 1 table tam thoi. create view view_student_marks as select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id ---- TEST select * from view_student_marks order by marks asc select * from view_student_marks where marks >= 8 order by marks asc ---- Sua view alter view view_student_marks as select top(10) Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id ---- Xoa view drop view view_student_marks -- II) proc: Duoc hieu nhu 1 function (ham & method & phuong thuc) ------ Điều này có nghĩa là gì: proc -> có thể lưu nhiều câu truy vấn khác nhau: Có thể là nhiều lệnh select, insert, update, delete create proc proc_test as begin select * from Student select * from Subject select * from Marks end ---- TEST exec proc_test create proc proc_view_student_marks_and_test as begin select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id select * from Student select * from Subject select * from Marks end exec proc_view_student_marks_and_test -- Proc: Hiểu nó như là 1 function trong ngôn ngữ lập trình ---- Do vậy: Tham số đầu vào, dữ liệu trả về (output) ---- Proc -> xem thông tin điểm thi của sinh viên -> điều kiện tham số đầu vào là minMarks create proc view_student_marks_by_min @minMarks float as begin select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id and Marks.marks >= @minMarks end exec view_student_marks_by_min 8 ---- Bài toán proc gồm nhiều tham số đầu vào thì làm thế nào. ------ Xem thông tin điểm thi sinh viên: @minMark & @roll_number create proc view_student_marks_by_min_rollno @minMarks float, @rollno nvarchar(20) as begin select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id and Marks.marks >= @minMarks and Student.roll_number = @rollno end exec view_student_marks_by_min_rollno 7, 'R001' ---- Đếm xem có bao nhiêu sinh viên có có điểm thi >= minMarks - Hiển thị dữ liệu đầu ra. create proc view_count_student_marks_by_min @minMarks float, @count int output -- float, int, nvarchar, ... as begin select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id and Marks.marks >= @minMarks -- count(*): Có nghĩa là đếm tổng số bản ghi trong câu truy vấn: Với cách viết này không cần dùng thêm group by. select @count = count(*) from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id and Marks.marks >= @minMarks end ---- Khi khai báo 1 biến trong lệnh sql -> cần đặt từ khóa declare trước tên biến. declare @count2 int exec view_count_student_marks_by_min 6, @count = @count2 output ---- Không thể cộng chuỗi với số nguyên ---- Do vậy: Để in được kết quả đầu ra -> chuyển số nguyên về chuỗi (int -> nvarchar) ------- Hàm chuyển int -> nvarhhar: CONVERT(nvarchar(20), @count2) print N'So ban ghi tim duoc: ' + CONVERT(nvarchar(20), @count2) -- Sua stored (proc) -> alter -- Xoa stored (proc) -> drop drop proc view_count_student_marks_by_min ---- Overview: group by -- group by: Khi nhom dữ liêu -> thông qua các hàm: count, sum, min, max, avg. select Student.roll_number, Student.student_name, count(Marks.marks) 'So Mon Co Diem Thi' from Student, Subject, Marks where Student.roll_number = Marks.roll_number and Subject.subject_id = Marks.subject_id group by Student.roll_number, Student.student_name