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
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:
chapter9_42.pdf
