Yea Yeah Yeah
Additional SQL Concepts
•
•
•
•
Database Transactions o logical unit of work in a database system o consists of one or more database operations (insert, delete, update) which together reflect the data changes resulting from a “real-world transaction” o properties of transactions atomicity - the execution of the component operations of a transaction is an all-or-nothing affair;
i.e., either all the component operations are executed, or none at all are consistency - a (correct) transaction transforms the database from one consistent state to another consistent state, without necessarily preserving consistency at all intermediate points isolation durability - the effects of the updates of a committed transaction persists, even in the face of subsequent failures o DBMS transaction processing system (concurrency and recovery mechanisms) autocommit mode and isolation levels
SQL (transaction initiation, COMMIT, ROLLBACK, intermediate savepoints)
Triggers
o database object which defines an action the database should take some related database event occurs o fired (automatically executed) when some DML statement is issued (either before or after the event)
CREATE TRIGGER newinvdetail
AFTER INSERT ON invdetail FOR EACH ROW
BEGIN
UPDATE invoice
SET invamt = invamt + (:new.invqty * :new.unitprice)
WHERE invno = :new.invno;
UPDATE item
SET qtyonhand = qtyonhand - :new.invqty
WHERE itemno = :new.itemno;
END;
Stored Procedures and Functions o database program modules that are stored and executed by the DBMS (persistent stored modules) o usefulness stored module can be used by several applications server processing can reduce data transfer and communication cost allows more complex types of derived data to be made available to users o written in a general purpose programming language (e.g. PL/SQL in Oracle, Transact-SQL in MS SQL) o typically provides for explicit usage of IN, OUT, INOUT parameters
CREATE PROCEDURE procname
•
•
•
•
Database Transactions o logical unit of work in a database system o consists of one or more database operations (insert, delete, update) which together reflect the data changes resulting from a “real-world transaction” o properties of transactions atomicity - the execution of the component operations of a transaction is an all-or-nothing affair;
i.e., either all the component operations are executed, or none at all are consistency - a (correct) transaction transforms the database from one consistent state to another consistent state, without necessarily preserving consistency at all intermediate points isolation durability - the effects of the updates of a committed transaction persists, even in the face of subsequent failures o DBMS transaction processing system (concurrency and recovery mechanisms) autocommit mode and isolation levels
SQL (transaction initiation, COMMIT, ROLLBACK, intermediate savepoints)
Triggers
o database object which defines an action the database should take some related database event occurs o fired (automatically executed) when some DML statement is issued (either before or after the event)
CREATE TRIGGER newinvdetail
AFTER INSERT ON invdetail FOR EACH ROW
BEGIN
UPDATE invoice
SET invamt = invamt + (:new.invqty * :new.unitprice)
WHERE invno = :new.invno;
UPDATE item
SET qtyonhand = qtyonhand - :new.invqty
WHERE itemno = :new.itemno;
END;
Stored Procedures and Functions o database program modules that are stored and executed by the DBMS (persistent stored modules) o usefulness stored module can be used by several applications server processing can reduce data transfer and communication cost allows more complex types of derived data to be made available to users o written in a general purpose programming language (e.g. PL/SQL in Oracle, Transact-SQL in MS SQL) o typically provides for explicit usage of IN, OUT, INOUT parameters
CREATE PROCEDURE procname