Stored Procedures, Functions, Triggers and transaction concepts Commit, Rollback

1
Stored Procedures, Functions, Triggers and
transaction concepts(Commit, Rollback)
2
Commit/Rollback What tables are updated ?

• Update customers set discnt discnt .01
• Commit
• Update Products set price price 1.03
• Update Orders set dollars dollars .06
• Rollback

3
Why commit rollback

• Implement the concept of a transaction. Command
  means end of transaction (releases all locks).
• Transaction a set of operations where all has
  to be executed or none.
• Example transfer 500 money from checking
• to savings.
• ChkAccount ChkAccount 500
• SavAccount SavAccount 500

4
Implicit and Explicit Commit/Rollback

• When there is a power failure, the system
  automatically executes a rollback (implicit
  rollback)
• When you leave an SQL session through the proper
  way (exit command) the system executes a commit
  (implicit commit)

5
PROCEDURES / FUNCTIONS

• ProcedureA series of statements accepting and/or
  returning zero or more variables.
• FunctionA series of statements accepting zero
  or more variables that returns one value.

6
Example of Function

• CREATE FUNCTION find_circle_area (
• p_radius IN circle.radiusTYPE
• ) RETURN NUMBER IS
• My_area number 0
• Pi constant number 3.14159265358
• BEGIN
• My_area (p_radiusp_radius) Pi
• Return my_area
• END

7
Triggers

• A series of PL/SQL statements attached to a
  database table that execute whenever a
• triggering event (select, update, insert,
  delete) occurs.

8
Example of Triggers

• create or replace trigger trigafter insert on
  ordersfor each row begin update
  customers set discnt discnt11 where
  customers.cidnew.cidend/ 
•