Chapter 9 Assertions, Views and Programming Techniques

Control structure Example: 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.09 ELSE 0.1 END FROM [Order Details] ORDER BY Quantity, ProductId

pdf40 trang | Chia sẻ: vutrong32 | Ngày: 19/10/2018 | Lượt xem: 26 | Lượt tải: 0download
Bạn đang xem trước 20 trang tài liệu Chapter 9 Assertions, Views and Programming Techniques, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Chapter 9 Assertions, Views and Programming Techniques Constraints as assertions In SQL, users can specify general constraints via declarative assertions, using the CREATE ASSERTION statement of the DDL (data definition language). Each assertion is given a constraint name and is specified via a condition similar to the WHERE clause of an SQL query. Syntax: CREATE ASSERTION CHECK (search condition) [] Constraints as assertions Example: CREATE ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS ( SELECT * FROM Employee E, employee M, department D WHERE E.SALARY>M.SALARY AND E.DNO=D.DNUMBER AND D.MGRSSN=M.SSN ) ); (reference page 256) Constraints as assertions Specify a query that violates (vi phạm) the condition; include inside a NOT EXISTS clause Query result must be empty if the query result is not empty, the assertion has been violated SQL Triggers Objective: to monitor a database and take action when a condition occurs. Triggers are expressed in a syntax similar to assertions and include the following:  Event (e.g., an update operation)  Condition Action (to be taken when the condition is satisfied) SQL Triggers A trigger to compare an employee’s salary to his/her supervisor during insert or update operations: CREATE TRIGGER INFORM_SUPERVISOR BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE FOR EACH ROW WHEN (NEW.SALARY> (SELECT SALARY FROM EMPLOYEE WHERE SSN=NEW.SUPERVISOR_SSN)) INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN; Views (virtual tables) in SQL Concept of a View: A view is a single table that is derived from other tables. These other tables could be base tables or previously defined views. A view does not necessarily exist in physical form, it is considered a virtual table Allows for limited update operations. Allows full query operations. A convenience (thuận lợi) for expressing certain operations Views (virtual tables) in SQL Specification of Views: Example: CREATE VIEW view_name [(column[ ,...n ])] AS select_statement [ WITH CHECK OPTION ] CREATE VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER; Views (virtual tables) in SQL Example: CREATE VIEW DEPTJNFO (DEPT_NAME,NO_OF_EMPS,TOTAL_SAL) AS SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME; Views (virtual tables) in SQL Example: Specify a different WORKS_ON table CREATE TABLE WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME; Views (virtual tables) in SQL DELETE VIEW: DROP VIEW view_name RENAME Views: sp_rename old_viewname, new_viewname CHECK VIEW: sp_helptext viewname MODIFY VIEW : ALTER VIEW view_name (column_list) AS select_statement Programming Techniques Approaches to Database Programming: Several techniques exist for including database interactions in application programs. The programs include variable, statement SQL, control structure. The basic concept:  Identifiers Batch (tập các câu lệnh T-SQL liên tiếp kết thúc bằng lệnh GO)  Script (tập của 1 hoặc nhiều batch được lưu thành một tập tin .SQL) Programming Techniques Data type: have two type  System - supplied data type User- defined data type Reference to object: Server.database.owner.object Variables Local variable Declare:  Example: DECLARE @EmpIDVar int DECLARE@ VariableName var_type Variables Assign value for the variable: When a variable is declared, its value is Null.  Example: DECLARE @temp_name varchar(20) SELECT @temp_name = companyname FROM customers WHERE customerid = ‘adsff’ SET @VariableName = expression or SELECT{@VariableName=expression} [,n] Variables  Example 2: DECLARE @temp_city varchar(10) SET @temp_city = ‘london’ SELECT * FROM Customers WHERE city = @temp_city Variables  Example 3: DECLARE @temp_CustID Char(5), @temp_name varchar(50) SET @temp_CustID = ‘ALFKI’ SELECT @temp_name = CompanyName FROM Customers Where CustomerID = @temp_CustID PRINT ‘CustomerID is ‘ + @temp_CustID + ‘ and Name is ‘+ @temp_name Variables Global Variables: is a System function  Return value of the function is displayed by statement SELECT @@Variablename. Not assign the value to the global variables. Global variables have no data type. Variable name begins with @@. Some Global Variables: @@SERVERNAME: Server name @@ROWCOUNT: number of rows are affected by the closest statement Variables Example: Update Employees set LastName = ‘Brooke’ Where LastName =‘Brook’ If(@@rowcount =0) begin print ‘No rows were updated’ return end Variables @@ERROR: return the index of error @@IDENTITY: return IDENTITY Execution of the SQL statement Dynamic SQL statement: Example: DECLARE @vname varchar(20), @table varchar(20), @vdbase varchar(20) SET @vname="'White'" SET @table='authors' SET @vdbase='pub' EXECUTE ('USE'+@vdbase + 'SELECT * FROM '+ @ vtable + 'WHERE au_lastname=‘+@vname) EXEC [USE] ({@string_variable| [ N ] 'tsql_string'} [+ ...n ] ) Execution of the SQL statement Batches: the set of the SQL statement is sent to server and they are executed at the same time.  If any statement in the batch has error then SQL server will not execute all statements in the batches.  Each batch cannot contain all of these following statements: CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW, CREATE RULE, CREATE DEFAULT. Execution of the SQL statement Example: go use master if exists(select * from sysdatabases where name like 'sales') drop database sales go create database sales on ( name = sales_data, filename ='e:\sales_data.mdf', size = 1, maxsize = 5, filegrowth =1) log on ( name = sales_log, filename ='e:\sales_log.ldf', size = 1, maxsize = 2, filegrowth =1) Execution of the SQL statement Transact-SQL Scripts: A script is a set of the T-SQL statement stored in a file of one or many batches. Transactions: is a work unit with 4 characteristics Atomic  Consistent (nhất quán)  Isolated (cô lập) Durable (bền) Execution of the SQL statement Transaction Structure: BEGIN TRANSACTION [] [WITH MARK ] [ SAVE TRANSACTION ] ROLLBACK TRANSACTION [ | ] COMMIT TRANSACTION Execution of the SQL statement Example: BEGIN TRAN UPDATE authors SET city=‘San Jose’ Where au_lname=‘smith’ INSERT titles VALUES(‘BU1122’,’Teach Yourself SQL’,’business’, ‘9988’, $35.00, $1000,10,4501,’a great book’) SELECT *from titleauthor COMMIT TRAN Execution of the SQL statement Example: BEGIN TRAN DELETE Sales where titles_id =‘BU1032’ if @@ERROR >0 ROLLBACK TRAN (huỷ hoàn toàn giao tác) else COMMIT TRAN Control structure IF ELSE IF boolean_expression {sql_statement | statement_block} [ELSE boolean_expression {sql_statement | statement_block}] Control structure BEGIN END BEGIN {sql_statement | statement_ block} END Control structure Example: IF ( SELECT COUNT(*) FROM authors WHERE contract =0) >0 BEGIN PRINT 'These authors do not have contracts on file: ' SELECT au_lname, au_fname, au_id FROM authors WHERE contract=0 END ELSE BEGIN PRINT 'All authors have contracts on file.' END Control structure WHILE WHILE boolean_expression {sql_statement | statement_block} [BREAK] {sql_statement | statement_block} [CONTINUE] Control structure Example 1: DECLARE @counter INT SET @counter=0 WHILE (@counter<20) BEGIN INSERT INTO Pubs..paractice VALUES ('last'+CAST(@counter as char(2)), 'First') SET @counter=@counter+1 END Control structure Example 2: WHILE (SELECT AVG(price) FROM titles) < $30 BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear' Control structure CASE  Simple CASE function CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ELSE else_result_expression ] END Control structure Searched CASE function CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Control structure Example: SELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking ELSE 'Not yet categorized‘ END CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE AVG(price) BY type Control structure Example: 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.09 ELSE 0.1 END FROM [Order Details] ORDER BY Quantity, ProductId Control structure PRINT: Display the SQL result RETURN WAITFOR PRINT ‘any ACII Text’|@local_variable| @@FUNTION| String_expr RETURN [integer_expression] integer_expression : return value WAITFOR { DELAY 'time' | TIME 'time' } Control structure Example: BEGIN WAITFOR TIME '22:20' EXECUTE update_all_stats END Control structure RAISERROR RAISERROR({msg_id | msg_str} { , severity , state } [ , argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]

Các file đính kèm theo tài liệu này:

  • pdfchapter9_42.pdf