1 / 40

Assertions, Views and Programming Techniques

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).

bairn
Télécharger la présentation

Assertions, Views and Programming Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 9 Assertions, Viewsand Programming Techniques

  2. 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 <Constraint name> CHECK(search condition) [<constraint attributes>]

  3. 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)

  4. 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

  5. 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)

  6. 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 UPDATEOF 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;

  7. 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ậnlợi) for expressing certain operations

  8. Views (virtual tables) in SQL • Specification of Views: • Example: CREATE VIEW view_name[(column[ ,...n ])] ASselect_statement[ WITH CHECK OPTION ] CREATEVIEW WORKS_ON1 ASSELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER;

  9. Views (virtual tables) in SQL • Example: CREATE VIEW DEPTJNFO (DEPT_NAME,NO_OF_EMPS,TOTAL_SAL) ASSELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUPBY DNAME;

  10. Views (virtual tables) in SQL • Example: Specify a different WORKS_ON table CREATE TABLE WORKS_ON_NEW AS SELECTFNAME, LNAME, PNAME, HOURS FROMEMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME;

  11. Views (virtual tables) in SQL • DELETE VIEW: DROP VIEW view_name • RENAME Views: sp_renameold_viewname, new_viewname • CHECK VIEW: sp_helptextviewname • MODIFY VIEW : ALTER VIEW view_name (column_list) AS select_statement

  12. 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ậpcáccâulệnh T-SQL liêntiếpkếtthúcbằnglệnh GO) • Script (tậpcủa 1 hoặcnhiều batch đượclưuthànhmộttập tin .SQL)

  13. Programming Techniques • Data type: have two type • System - supplied data type • User- defined data type • Reference to object: • Server.database.owner.object

  14. Variables • Local variable • Declare: • Example: DECLARE @EmpIDVarint DECLARE@ VariableNamevar_type

  15. Variables • Assign value for the variable: When a variable is declared, its value is Null. • Example: DECLARE @temp_namevarchar(20) SELECT @temp_name = companyname FROM customers WHERE customerid = ‘adsff’ SET @VariableName = expression or SELECT{@VariableName=expression} [,…n]

  16. Variables • Example 2: DECLARE @temp_cityvarchar(10) SET @temp_city = ‘london’ SELECT * FROM Customers WHERE city = @temp_city

  17. Variables • Example 3: DECLARE @temp_CustID Char(5), @temp_namevarchar(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

  18. 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

  19. Variables • Example: Update Employees set LastName = ‘Brooke’ Where LastName =‘Brook’ If(@@rowcount =0) begin print ‘No rows were updated’ return end

  20. Variables • @@ERROR: return the index of error • @@IDENTITY: return IDENTITY

  21. Execution of the SQL statement • Dynamic SQL statement: • Example: DECLARE @vnamevarchar(20), @table varchar(20), @vdbasevarchar(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 ] )

  22. 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.

  23. 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)

  24. 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ấtquán) • Isolated (côlập) • Durable (bền)

  25. Execution of the SQL statement • Transaction Structure: BEGIN TRANSACTION [<transaction_name>] [WITH MARK <description>]… <T-SQL code>… [ SAVE TRANSACTION <savepoint name>]… <T-SQL code>… <test for errors > <if true> ROLLBACK TRANSACTION [<transaction_name> | <savepoint name>] <if false> COMMIT TRANSACTION

  26. 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

  27. Execution of the SQL statement • Example: BEGIN TRAN DELETE Sales where titles_id =‘BU1032’ if @@ERROR >0 ROLLBACK TRAN (huỷhoàntoàngiaotác) else COMMIT TRAN

  28. Control structure • IF … ELSE IF boolean_expression{sql_statement | statement_block} [ELSE boolean_expression{sql_statement | statement_block}]

  29. Control structure • BEGIN …END BEGIN {sql_statement | statement_ block} END

  30. 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

  31. Control structure • WHILE WHILE boolean_expression {sql_statement | statement_block} [BREAK] {sql_statement | statement_block}[CONTINUE]

  32. 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

  33. 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'

  34. Control structure • CASE • Simple CASE function • CASE input_expression • WHENwhen_expressionTHENresult_expression [ ...n ]      [ELSE else_result_expression] END

  35. Control structure • Searched CASE function • CASE WHEN Boolean_expression THEN result_expression [ ...n ]     [ ELSE else_result_expression] END

  36. 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

  37. 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

  38. 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' }

  39. Control structure • Example: BEGIN WAITFOR TIME '22:20' EXECUTE update_all_stats END

  40. Control structure • RAISERROR RAISERROR({msg_id | msg_str} { , severity , state } [ , argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]

More Related