Chương 4 Lập trình với T-SQL Nội dung Kỹ thuật thi hành lệnh T-SQL Các cấu trúc điều khiển Stored Procedure (thủ tục) Function (hàm) 2 Cơ bản về lập trình bằng T_SQL Khai báo và sử dụng biến Các cấu trúc lệnh RaiseError IDENTIFIER (Định danh) Tên của các đối tượng đều được gọi là định danh. Trong SQL Server, có các định danh như Server, Databases, Object of Database as Table, View, Index, Constraint,… Qui tắc định danh: Tối đa 128 ký tự. Bắt đầu là một ký tự từ A-Z Bắt đầu là một ký hiệu @, # sẽ có một ý nghĩa khác. Những định danh nào có dấu khoảng trắng ở giữa thì phải kẹp trong dấu [] hoặc “ “ Đặt định danh sao cho ngắn gọn, đầy đủ ý nghĩa, phân biệt giữa các đối tượng với nhau, không trùng lặp, không trùng với từ khóa của T-SQL.
Tham chiếu đến các đối tượng trong SQL Server Cú pháp: Server.Object Hay: Server.Object Ví dụ: Create Table Northwind.Customers Create Table Northwind.Customers Data type, Batch, Script Kiểu dữ liệu (Data type) : có hai loại Kiểu dữ liệu hệ thống: Do hệ thống cung cấp Kiểu dữ liệu do người dùng định nghĩa (User – defined data types.) Gói lệnh (Batch) Bao gồm các phát biểu T-SQL và kết thúc bằng lệnh GO. Các lệnh trong gói lệnh sẽ được biên dịch và thực thi cùng một lúc. Nếu một lệnh trong batch bị lỗi thì batch cũng xem như lỗi Các phát biểu Create bị ràng buộc trong một batch đơn. Ex : Use Northwind Select * from Customers GO Kịch bản (Script ) Một kịch bản là một tập của một hay nhiều bó lệnh được lưu lại thành một tập tin .SQL Biến – Biến cục bộ Biến là một đối tượng dùng để lưu trữ dữ liệu.
Biến phải được khai báo trước khi dùng. Có 2 loại biến: cục bộ và toàn cục Biến cục bộ: Được khai báo trong phần thân của một bó lệnh hay một thủ tục. Phạm vi hoạt động của biến bắt đầu từ điểm mà nó được khai báo cho đến khi kết thúc một bó lệnh, một thủ tục hay một hàm mà nó được khai báo. Tên của biến bắt đầu bằng @ Sử dụng biến cục bộ Khai báo DECLARE @var_name = expression SELECT {@var_name = expression}[,…n] Ví dụ DECLARE @makh CHAR(5) SET @makh = 'ANTON' SELECT * FROM Customers WHERE Customerid = @makh Sử dụng biến cục bộ VD : DECLARE @manv int SET @manv = 2 SELECT * FROM Employees WHERE Employeeid = @manv DECLARE @manv int, @country nvarchar(15) SET @manv = 3 SET @country ='Usa' SELECT * FROM Employees WHERE Employeeid = @manv and country =@country Sử dụng biến cục bộ VD DECLARE @tong int Select @tong = Sum(quantity * Unitprice) From [Order details] SELECT @tong as tongtien Print 'Tong tien = '+ convert(varchar(20),@tong) DECLARE @masp int Select @masp = productid From Northwind.Products Select @masp –của dòng cuối trong bảng DECLARE @Masp int Select @masp = Productid From Northwind.Products Order by Productid DESC Select @masp as 'Ma SP' Biến toàn cục Biến toàn cục được định nghĩa như hàm hệ thống.
Các biến này không có kiểu. Tên bắt đầu bằng @@ Các biến toàn cục Variable Return value @@Trancount Number of transactions currently open on the connection @@Servername Name of local servers running SQL Server @@Rowcount Number of rows affected by the latest SQL statement @@Identity Return last Number Identity @@Error Return order number Error when SQL exculate, return 0 when The command completed successfully @@Fetch_status Return status of Fetch command of pointer variable (0 :Success, -1 : Mistake or exceed range, -2 : Unsuccess Các biến toàn cục VD: --How many are transaction opening If (@@Trancount>0) Begin Raiserror ('Take can not be executed within a transaction',10,1) Return End --Number of rows affected by the latest SQL statement Use Northwind Update Employees Set LastName ='Brooke' where LastName ='Lan' If (@@RowCount =0) Begin Print 'Warning : No rows were updated' Return End Update Customers Set Phone ='030' + Phone Where Country = 'German' Print @@Rowcount Các biến toàn cục --Tra ve so Identitity phat sinh sau cung Create table hd (Mahd int identity Primary key, Ghichu varchar(20)) Create table cthd(Mahd int,Masp char(10), Soluong int) Insert into hd Values ('Record 1') Insert into hd Values ('Record 2') Declare @maso int Set @maso = @@identity Insert into cthd Values (@maso,'sp001',5) Insert into cthd Values (@maso,'sp002',12) Select * from hd Select * from cthd Cấu trúc điều khiển Cấu trúc điều khiển Khối BEGIN.END: Nếu nhiều phát biểu cần thực thi có liên quan với nhau thì đặt các phát biểu này trong Begin…End Cú pháp: BEGIN statement | statement_block END RETURN: Trả về một giá trị, lệnh này nằm trong một block (khối) hay procedure. Nếu gặp phát biểu Return, quá trình xử lý kết thúc Cú pháp Return [Integer_expression] Cấu trúc điều khiển Lệnh PRINT: Dùng để in thông tin ra màn hình kết quả của SQL. Cú pháp: PRINT ‘any ASCII text’|@local_variable|@@Function |string_expr Ví dụ: Print 'Hello' Print N'Chào bạn' Print getdate() Print @@version Declare @ten nvarchar(15) Set @ten ='Nguyen Minh' Print @ten Cấu trúc điều khiển Cấu trúc điều khiển IF.ELSE: Cho phép thực thi một hay nhiều lệnh tùy thuộc vào một điều kiện nào đó.
Cú pháp: If Condition statements [Else [Condition 1] statements] Ví dụ : If (Select Count(*) From Customers Where Country ='Germany')>0 print 'Co khach hang o Germany' Else print 'Khong co khach hang o Germany' Cấu trúc điều khiển Ví dụ: Declare @msg varchar(100) If (Select Count(Unitprice) From Products Where QuantityPerunit like '%box%')>0 Begin Set NOCOUNT ON Set @msg = 'Co vai sp co don vi tinh co chu box. Cac sp do la :' Select @msg Select ProductName From Products where QuantityPerunit like '%box%' End Else print 'Khong co sp nao co dvt co chu box' Cấu trúc điều khiển Ví dụ: If (Select Avg(Unitprice) From Products where QuantityPerUnit like '%box%' )>0 Begin Set NOCOUNT on Declare @msg nvarchar(30) Set @msg = 'Co vai sp có don vi tinh co chu box. Cac sp do la:' Select @msg Select ProductName From Products where QuantityPerUnit like '%box%' End Else print 'Khong co sp nao co dvt co chu box' Cấu trúc điều khiển CASE : là một biểu thức điều kiện được áp dụng bên trong một phát biểu khác. Case trả về các giá trị khác nhau tùy vào điều kiện hay một điều khiển nào đó.
Cú pháp 1 : Case input_expression When when_expression Then resulf_expression[…n] [ELSE else_result_expression] End Cú pháp 2 : Case Boolean_expression When Boolean_expression Then resulf_expression[…n] [ ELSE else_result_expression ] End Cấu trúc điều khiển Example 1 : Declare @a int, @b int, @Hieu int Set @a = 15 Set @b =27 Set @hieu = Case When @a<@b then @b-@a When @a>@b then @a-@b Else 0 End Print 'Hieu='+convert(varchar(20),@hieu) Example 2 : Select ProductName, Unitprice, 'Classification'=CASE When Unitprice<10 then 'Low price' When Unitprice Between 10 and 20 then 'Moderately Price' When Unitprice>20 then 'Expensive' Else 'Unknown' END From Products Cấu trúc điều khiển Select Productid, Quantity, UnitPrice, [discount%]= CASE When Quantity <=5 then 0.05 When Quantity between 6 and 10 then 0.07 When Quantity between 11 and 20 then 0.1 END From [Order Details] Order by Quantity, Productid Cấu trúc điều khiển GOTO: chuyển thực thi chương trình đến vị trí nhãn (label) Example Declare @a int, @b int, @Hieu int Set @a = 39 Set @b =10 hieu_loop: if @a>@b begin Set @hieu =@A-@B print 'a= '+convert(varchar(20),@a) print 'b= '+convert(varchar(20),@b) print 'hieu= '+convert(varchar(20),@hieu) Set @a =@hieu Goto hieu_loop end print 'a='+convert(varchar(20),@a) print 'b='+convert(varchar(20),@b) print 'hieu='+convert(varchar(20),@hieu) Cấu trúc điều khiển Phát biểu lặp WHILE: Vòng lặp sẽ thực thi cho đến khi biểu thức điều kiện (Boolean expression) trong While mang giá trị False. Cú pháp 1 : WHILE Boolean_expression {sql_statement | statement_block} [BREAK] {sql_statement | statement_block} [CONTINUE] Cấu trúc điều khiển Example : Use Northwind While (Select Avg(unitprice) From [Order Details]) <$50 Begin Update [Order Details] SET Unitprice = Unitprice *2 Select Max(Unitprice) From [Order Details] If (Select Max(Unitprice) From [Order Details])>$50 BREAK Else CONTINUE End Print 'Too much for the market to bear' Cấu trúc điều khiển WAITFOR: SQL Server tạm dừng một thời gian trước khi xử lý tiếp các phát biểu sau đó. Cú pháp : WAITFOR {DELAY ‘time’ |TIME ‘time’} Time : hh:mm:ss Deplay ‘time’: hệ thống tạm dừng trong khoảng thời gian time TIME ‘time’: hệ thống tạm dừng trong khoảng thời gian time chỉ ra Ví dụ: WAITFOR DELAY '00:00:02' SELECT EmployeeID FROM Northwind.Employees Cấu trúc điều khiển Lệnh RAISERROR: phát sinh lỗi của người dùng Cú pháp RAISERROR ({msg_id | msg_str}{, severity, state} [WITH option[,.n]] • Msg_id: Là thông báo, được lưu trong bảng sysmessage. Mã thông báo của người dùng phải bắt đầu từ trên 50000 • Msg_str: Nội dung thông báo, tối đa 400 ký tự.
• Để truyền tham số vào trong thông báo thì dùng dạng %<Loại ký tự> • Loại ký tự là d,I,o,x,X hay u • Lưu ý: số float, double, char không được hỗ trợ Cấu trúc điều khiển Lệnh RAISERROR: phát sinh lỗi của người dùng Các ký tự Mô tả d hoặc I Biểu hiện là số nguyên (integer) O Octal không dấu P Con trỏ S Chuỗi U Số nguyên không dấu x or X Hexadecimal không dấu Cấu trúc điều khiển Severity Levels: Mức lỗi của một thông báo lỗi cung cấp một sự biểu thị loại vấn đề mà SQL Server gặp phải. Mức lỗi 10 là lỗi về thông tin và biểu thị nguyên nhân do thông tin nhập vào. Mức lỗi từ 11 đến 16 thì thông thường là do các user. Mức từ 17 đến 25 do lỗi phần mềm hoặc phần cứng.
Bạn nên báo cho nhà quản trị hệ thống bất cứ khi nào sự cố xảy ra. Nhà quả trị hệ thống phải giải quyết sự cố đó và theo dõi chúng thường xuyên. Khi mức lỗi 17,18,19 xảy ra, bạn có thể tiếp tục làm việc, mặc dù bạn không thể thực thi lệnh đặc biệt. Cấu trúc điều khiển Mức lỗi 17: Những thông báo này cho biết rằng câu lệnh SQL Server cạn kiệt tài nguyên (Ví dụ như lock hoặc thiếu không gian đĩa cho CSDL) hoặc vượt quá tập giới hạn bởi nhà quản trị Người quản trị hệ thống nên giám sát tất cả các sự cố xảy ra có mức trầm trọng từ 17 đến 25 và in ra giải thích lỗi bao gồm các thông tin để quay lại từ lỗi.
Mức lỗi từ 20 đến 25 chỉ ra sự cố hệ thống. Đó là lỗi không tránh được, có nghĩa là tiến trình không còn đang chạy. Tiến trình tê liệt trước khi nó dừng, ghi nhận thông tin về cái gì xảy ra, và sau đó kết thúc. Cấu trúc điều khiển State: Là một số nguyên tùy ý từ 1 đến 127 mô tả thông tin diễn giải về trạng thái lỗi.
Argument: Là tham số dùng trong việc thay thế cho biến để định nghĩa thông báo lỗi hoặc thông báo tương ứng với mã lỗi msg_id. Có thể không có hoặc có nhiều tham số, tuy nhiên, không được quá 20. Mỗi tham số thay thế có thể là một biến local hoặc một trị bất kỳ trong các kiểu dữ liệu int, char, varchar, binary, varbinary. Các kiểu khác không được cung cấp.