Thư mục

Hỗ trợ kỹ thuật

  • (Hotline:
    - (04) 66 745 632
    - 0982 124 899
    Email: hotro@violet.vn
    )

Thống kê

  • lượt truy cập   (chi tiết)
    trong hôm nay
  • lượt xem
    trong hôm nay
  • thành viên
  • Chào mừng quý vị đến với Thư viện Bài giảng điện tử.

    Quý vị chưa đăng nhập hoặc chưa đăng ký làm thành viên, vì vậy chưa thể tải được các tư liệu của Thư viện về máy tính của mình.
    Nếu đã đăng ký rồi, quý vị có thể đăng nhập ở ngay ô bên phải.

    Giáo Trình SQL bài 11


    (Tài liệu chưa được thẩm định)
    Nguồn: Hồ Xuân Kiên
    Người gửi: Trần Thị Thanh Diệu
    Ngày gửi: 13h:56' 26-11-2009
    Dung lượng: 531.0 KB
    Số lượt tải: 160
    Số lượt thích: 0 người
    1
    PROCEDURES và
    FUNCTIONS
    2
    1. Khái niệm cơ bản về Stored procedure:
    Stored procedure (thủ tục) là một tập các lệnh T-SQL và một số cấu trúc điều kiện
    Một khi Stored procedure được thiết lập thì những ứng dụng cần thiết tương tự chỉ cần thực gọi để thực hiện Store procedure
    Thủ tục lưu được tối ưu hoá lần đầu tiên khi chúng được biên dịch.
    Thủ tục nâng cao an toàn thông tin thông qua việc cô lập hoặc mã hóa
    3
    System sp: Được lưu trữ trong CSDL master và được đặt tên với tiếp đầu ngữ là sp. Chúng đóng vai trò khác nhau của các tác vụ được cung cấp trong SQL Server.
    Ví dụ: Sp_help, Sp_helpConstraint,…
    Local sp: Được lưu trữ trong các CSDL của người dùng, nó thực thi các tác vụ (Task) trong Database chứa nó.
    Khi thực thi thủ tục, cần cung cấp các giá trị của tham số của các thủ tục nếu có. Một thủ tục có thể được gọi thực thi hoặc tự động thực thi khi SQL Server khởi động. Gọi thực thi bằng từ khóa EXCUTE.
    4
    2. Tạo Store Procedure:

    CREATE PROC[EDURE] procedure_name
    [ { @parameter data_type }
    [ VARYING ] [ = default ] [ OUTPUT ] ]
    [ ,...n ]

    [ WITH { RECOMPILE | ENCRYPTION } ]

    [ FOR REPLICATION ]

    AS

    sql_statement [ ...n ]
    5
    6
    VD:
    Create Procedure sp_SinhVien
    @NamSinh int
    AS
    Select * From tbl_SinhVien
    Where Year(NgaySinh) = @NamSinh
    -------------------
    3. Thực thi Store Procedure:
    EXEC[ute] procedure_name
    [[@parameter =]{value |@variable [OUTPUT] | [DEFAULT]]
    [,…n]

    VD: EXEC sp_SinhVien 1984
    7
    Create Proc Sp_ChenKhachHang
    @MaKH varchar (10), @TenKH nvarchar (50),
    @Address nvarchar (50), @Phone varchar (24),
    @Fax nvarchar (24), @mail nvarchar (50)
    AS
    Insert Into KhachHang (MaKH, TenKH, Address, Phone, Fax, eMail)
    Values (@MaKH, @TenKH, @Address, @Phone, @Fax, @Mail)

    Gọi thủ tục:
    Sp_ChenKhachHang `KH01`, N`Trường Tin học ABC`, `12 Nguyễn Văn Bảo`, `886699`, `791175`, `@yahoo.com`
    8
    a. Store Procedure and Input Parameters:
    Create Procedure sp_NhanVien
    @Ho nvarchar(50),
    @Ten nvarchar(10)
    As
    Select * From NhanVien
    Where Ho = @Ho And Ten = @Ten

    ------------ Câu 2.10. trong Bài tập - Bài7 ---------------
    Create Procedure sp_ThongTinHoaDonSo
    @So_HoaDon int
    As
    Select d.MaNhanVien, d.NgayGiaoHang, d.NoiGiaoHang
    From Dondathang as d INNER JOIN NhanVien as n
    ON d.MaNhanVien = n.MaNhanVien
    Where SoHoaDon = @So_HoaDon
    9
    a. Store Procedure and Default Parameters:
    Create Procedure sp_NhanVien
    @Ho nvarchar(50) ,
    @Ten nvarchar(10) = `%`
    As
    Select * From NhanVien
    Where Ho Like @Ho And Ten Like @Ten

    ------------ Câu 2.10. trong Bài tập - Bài7 ---------------
    Create Procedure sp_ThongTinHoaDonSo
    @So_HoaDon int = 1
    As
    Select d.MaNhanVien, d.NgayGiaoHang, d.NoiGiaoHang
    From Dondathang as d INNER JOIN NhanVien as n
    ON d.MaNhanVien = n.MaNhanVien
    Where SoHoaDon = @So_HoaDon
    10
    Store Procedure and Output Parameters:
    Create Procedure sp_GiaBan
    @MaHang int, @GiaBan money OUTPUT
    AS
    Select @GiaBan = Giaban
    From MatHang
    Where MaHang = @MaHang


    Gọi thủ tục:
    Declare @price money
    Execute sp_GiaBan 77, @price Output
    Print Convert (varchar(10), @price)
    11
    Ví dụ: Giả sử ta cần thực hiện một chuỗi các thao tác như sau trên cơ sở dữ liệu:

    Bổ sung thêm môn học “cơ sở dữ liệu” có mã “TI-005” và số đơn vị học trình là 5 vào bảng “MONHOC”

    Nhập điểm thi môn “cơ sở dữ liệu” cho các sinh viên học lớp có mã C24102 (tức là bổ sung thêm vào bảng DIEMTHI các bản ghi với cột MAMONHOC nhận giá trị TI-005, cột MASV nhận giá trị lần lượt là mã các sinh viên học lớp có mã C24105 và các cột điểm là NULL).
    12
    VD: Nếu thực hiện yêu cầu trên thông qua các câu lệnh SQL như thông thường, ta phải thực thi hai câu lệnh như sau:

    Insert Into MonHoc(MaMH,TenMH,SoDVHT)
    Values(`TI-005`, N`Cơ sở dữ liệu`, 5)

    Insert Into DiemThi(MaMonHoc,MaSV)
    Select `TI-005`, MaSV
    From SinhVien
    Where MaLop = `C24102`
    13
    Create Proc sp_LenDanhSachDiem
    @maMH nvarchar(10), @tenMH nvarchar(50),
    @sodvht smallint, @maLop nvarchar(10)
    @KiemTra int OUTPUT
    As
    BEGIN
    Insert Into MonHoc
    Values(@maMH, @ tenMH, @sodvht);

    Insert Into DiemThi(MaMH, MaSV)
    Select @maMH, MaSV FROM SinhVien
    Where MaLop=@maLop
    If @@ERROR <> 0 Then
    Set @KiemTra = 1 -- SET @KiemTra = @@ERROR
    Else
    Set @KiemTra = 0
    END
    14
    Ghi chú:
    Khi thực thi SP có tham số OUTPUT ta phải khai báo (DECLARE) một biến trước khi gọi SP thực thi và phải có từ khóa OUTPUT ngay sau tham số đó.

    VD: gọi thực thi ví dụ trên.

    DECLARE @Kiem_Tra int

    EXEC sp_LenDanhSachDiem `TI-005`, N`Cơ sở dữ liệu`, 5, `C24102‘, @Kiem_Tra OUTPUT

    PRINT @KiemTra
    15
    VD (tt):
    Create Proc sp_NhanVienCungNamSinh
    @Dem int Output
    As
    Select a.Ho + ` ` + a.Ten as HoTen, a.NgaySinh
    From NhanVien a Inner Join NhanVien b
    On Year(a.NgaySinh) = Year(b.NgaySinh)
    Where a.MaNhanVien <> b.MaNhanVien
    Set @Dem = @@ROWCOUNT

    Gọi thực thi:
    Declare @dem int
    Exec sp_NhanVienCungNamSinh @dem output
    Print @Dem
    16
    VD: Store Procedure ENCRYPTION

    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = sp_encrypt_this` AND type = `P`)
    DROP PROCEDURE sp_encrypt_this
    GO
    USE pubs
    GO
    CREATE PROCEDURE sp_encrypt_this
    WITH ENCRYPTION
    AS
    SELECT * FROM authors
    GO
    EXEC sp_helptext sp_encrypt_this
    EXEC sp_encrypt_this
    17
    3. Hiệu chỉnh Store Procedure:
    ALTER PROC[EDURE] procedure_name
    [ { @parameter data_type }
    [ VARYING ] [ = default ] [ OUTPUT ] ]
    [ ,...n ]
    [ WITH
    { Recompile | Encryption | Recompile , Encryption } ]
    [ FOR REPLICATION ]
    AS
    sql_statement [ ...n ]
    --------------------------------
    Hoặc trong SQL Query Analyzer  phải chuột  Edit
    18

    5. Xem nội dung Store Procedure:
    sp_helptext procedure_name

    6. Xóa Store Procedure:
    DROP PROCEDURE procedure_name
    19
    II. FUNCTION:
    CREATE FUNCTION [ owner_name. ] function_name
    ([{ @parameter_name data_type [ = default ]} [ ,...n ]])
    RETURNS return_data_type
    AS
    BEGIN
    [ function_body ]
    RETURN ( expression )
    END
    20
    1. Ví dụ về Hàm trả về một giá trị:
    CREATE FUNCTION fn_Thu (@ngay datetime)
    RETURNS nvarchar(10)
    AS
    BEGIN
    Declare @st nvarchar(10)
    Select @st=CASE Datepart (DW, @ngay)
    When 1 Then N`Chu nhật`
    When 2 Then N`Thứ hai`
    When 3 Then N`Thứ ba`
    When 4 Then N`Thứ tư`
    When 5 Then N`Thứ năm`
    When 6 Then N`Thứ sáu`
    Else N`Thứ bảy`
    END
    RETURN ( @st ) /* Trị trả về của hàm */
    END
    21
    Sử dụng hàm:
    SELECT MaSV, HoDem, Ten, dbo.Thu(ngaysinh), NgaySinh
    FROM SinhVien
    WHERE MaLop= `C24102`
    22
    2. Ví dụ về Hàm trả dữ liệu kiểu TABLE:
    CREATE FUNCTION fn_XemSV(@khoa smallint)

    RETURNS TABLE

    AS
    RETURN ( SELECT MaSV, HoDem, Ten, NgaySinh
    FROM SinhVien INNER JOIN Lop
    ON SinhVien.MaLop = Lop.MaLop
    WHERE Khoa=@khoa )


    Gọi hàm:
    SELECT * FROM dbo.fn_XemSV (25)
    23
    CREATE FUNCTION fn_TongSV(@khoa smallint)
    RETURNS @bangthongke TABLE
    ( MaKhoa nvarchar(5), TenKhoa nvarchar(50), tongsosv int )
    AS
    BEGIN
    IF @khoa=0
    Insert Into @bangthongke
    Select k.MaKhoa, k.TenKhoa, Count(s.MaSV)
    From Khoa as k Inner Join Lop as l On k.MaKhoa = l.MaKhoa
    Inner Join SinhVien as s On l.MaLop = s.MaLop
    Group By k.makhoa, k.TenKhoa
    ELSE
    Insert Into @bangthongke
    Select k.MaKhoa, k.TenKhoa, Count(s.MaSV)
    From Khoa as k Inner Join Lop as l On k.MaKhoa = l.MaKhoa
    Inner Join SinhVien as s On l.MaLop = s.MaLop
    Where l.Khoa=@khoa
    Group By k.makhoa, k.TenKhoa
    RETURN /*Trả kết quả về cho hàm*/
    END /* Select * From fn_TongSV(25) */
     
    Gửi ý kiến
    print