Advanced SQL

1
Advanced SQL
Part II

• Instructor Mohamed Eltabakh
• meltabakh_at_cs.wpi.edu

2
Remaining Lectures

• Today ? Complete Advanced SQL
• Dec 6. ? Brief Introduction to Transactions and
  
• Indexes
• ? Due date for HW4 Phase 4
• Dec 9. ? Revision short quiz (from the revision
  
• slides)
• Dec 13. ? Final exam

3
Todays Roadmap

• Triggers
• Assertions
• Cursors
• Stored Procedures
• ODBC/JDBC

4
Triggers Assertions

• To enforce complex constraints inside the DBMS,
  we use Triggers or Assertions
• Assertions are part of SQL standards, but not all
  DBMSs support them
• Triggers are more powerful

5
Recap on Triggers

• Three components
• Event When this event happens, the trigger is
  activated
• Condition (optional) If the condition is true,
  the trigger executes, otherwise skipped
• Action The actions performed by the trigger

Create Trigger ltnamegt Before After
Insert Update Delete On lttableNamegt
Referencing OLD AS oldRec,
NEW AS newRec For Each Row For
Each Statement When ltconditiongt Begin . End
6
Example Maintenance of Derived Attributes
Keep the bonus attribute in Employee table always
3 of the salary attribute
Create Trigger EmpBonus Before Insert Or Update
On Employee For Each Row Begin
newRec.bonus newRec.salary 0.03 End
7
Before vs. After

• Before Event
• When checking certain conditions that may cause
  the operation to be cancelled
• E.g., if the name is null, do not insert
• When modifying values before the operation
• E.g., if the date is null, put the current date
• After Event
• When taking other actions that will not affect
  the current operations
• The insert in table X will cause an update in
  table Y

Before Insert Trigger newRec.x .
//Changing value x that will be inserted After
Insert Trigger newRec.x
//meaningless because the value is already
inserted
8
Example
For Employees with salary lt 50,000, keep the
EmpID and salary in table LowSalaryEmp

• What triggers do we need?
• After Insert, After Update, After Delete on
  Employee

Create Trigger EmpUpdate After Update Of salary
On Employee Referencing OLD AS
oldRec, NEW AS newRec For Each Row Begin
IF (newRec.sal lt 50,000 and oldRec.sal gt50,000)
THEN Insert into LowSalaryEmp values
(newRec.EmpId, newRec.sal) ELSIF
(newRec.sal lt 50,000 and oldRec.sal lt 50,000)
Update LowSalaryEmp set sal newRec.sal
Where empId newRec.EmpId ELSIF
(newRec.sal gt 50,000 and oldRec.sal lt 50,000)
Delete from LowSalaryEmp Where empId
newRec.EmpId END IF End
9
Combining Multiple Events in One Trigger
Can write different code for different events

• CREATE TRIGGER salaryRestrictions
• BEFORE INSERT OR UPDATE ON Professor
• Referencing
• OLD AS oldRec, NEW AS newRec
• For Each Row
• BEGIN
• IF (INSERTING AND newRec.salary lt 60000) THEN
• RAISE_APPLICATION_ERROR (-20004, 'below min
  salary')
• END IF
• IF (UPDATING AND newRec.salary lt oldRec.salary)
  THEN
• RAISE_APPLICATION_ERROR (-20004, Salary
  Decreasing !!')
• END IF
• END

10
Summary of Triggers

• Powerful mechanisms to enforce constraints in the
  DBMS
• Need to know what triggers to create
• On which tables
• On which events
• Can have many triggers on the same table,
  possibly of the same type (but different trigger
  names)

11
Todays Roadmap

• Triggers
• Assertions
• Cursors
• Stored Procedures
• ODBC/JDBC

12
Assertions

• An expression that should be always true
• When created, the expression must be true
• DBMS checks the assertion after any change that
  may violate the expression

13
Example 1
Sum of loans taken by a customer does not exceed
100,000
Create Assertion SumLoans Check ( 100,000 gt
ALL Select Sum(amount) From
borrower B , loan L Where B.loan_number
L.loan_number Group By customer_name )
14
Example 2
Number of accounts for each customer in a given
branch is at most two
Create Assertion NumAccounts Check ( 2 gt ALL
Select count() From account A ,
depositor D Where A.account_number
D.account_number Group By customer_name,
branch_name )
15
Example 3
Customer city is always not null
Create Assertion CityCheck Check ( NOT EXISTS
( Select From customer
Where customer_city is null))
16
Example 4 (Exercise)
The customer city must be as the branch city to
have an account or a loan in that branch
17
Assertions vs. Triggers

• Assertions do not modify the data, they only
  check certain conditions
• Triggers are more powerful because the can check
  conditions and also modify the data
• Assertions are not linked to specific tables in
  the database and not linked to specific events
• Triggers are linked to specific tables and
  specific events
• All assertions can be implemented as triggers
  (one or more)
• Not all triggers can be implemented as assertions

18
Example Trigger vs. Assertion
All new customers opening an account must have
opening balance gt 100. However, once the
account is opened their balance can fall below
that amount.
Create Trigger OpeningBal Before Insert On
Customer Referencing NEW AS newRec For Each
Row Begin IF (newRec.bal is null or
newRec.bal lt 100) Then RAISE_APPLICATION_ERR
OR(-20004, Balance should be gt 100) End
IF End
19
Todays Roadmap

• Triggers
• Assertions
• Cursors
• Stored Procedures
• ODBC/JDBC

20
What is a Cursor

• A mechanism to navigate tuple-by-tuple over a
  relation
• Typically used inside triggers, stored
  procedures, or stored functions
• Main Idea
• When we execute a query, a relation is returned
• It is stored in private work area for the query
• Cursor is a pointer to this area
• Move the cursor to navigate over the tuples
• Creating Cursor

Cursor HighSalEmp IS Select empID, name,
salary From Employee Where salary gt
120,000
Cursor ltnamegt IS ltSQL querygt
21
Cursor Operations
Cursor HighSalEmp IS Select empID, name,
salary From Employee Where salary gt
120,000

• Create cursor
• Open cursor
• Put pointer to the first tuple
• Fetch next tuple
• Pointer moves automatically when a tuple is
  fetched
• Close cursor

Open HighSalEmp
Fetch HighSalEmp into ltvariablegt
Close HighSalEmp
22
Example
Create Trigger OpeningBal After Insert On
Customer Declare p_Id int p_name
string p_price number(7,2) cursor
C1 Is
//define the cursor Select
productId, name, price From
products where type new
Begin open C1
//opened the cursor Loop
fetch C1 into p_id, p_name, p_price
//fetched the first tuple IF
(C1Found) THEN // make sure the
fetch was successful . ELSE
exit
// break the loop END IF
End Loop close C1
// close the cursor End
23
Another Way
Create Trigger OpeningBal After Insert On
Customer Declare cursor C1 Is
Select productId, name, price
From products where type
new Begin For rec in C1 Loop
//opened the cursor
Insert into Temp values (rec.productId, rec.name,
rec.price) // fetch values End Loop
// close
the cursor End
24
Cursor Attributes

• These are attributes maintained by the system
• Attributes include
• C1ROWCOUNT The number of tuples in C1
• C1FOUND TRUE if the last fetch was successful
• C1NOTFOUND TRUE if the last fetch was not
  successful
• C1ISOPEN TRUE if C1 is open

25
Parameterized Cursor

• Cursors can take parameters while opening them

Create Trigger OpeningBal After Insert On
Customer Reference NEW as newRec Declare
cursor C1(type_Of_Interest string) Is
Select productId, name, price
From products where type
type_Of_Interest Begin For rec in
C1(newRec.type) Loop Insert into
Temp values (rec.productId, rec.name, rec.price)
End Loop
End
26
Summary of Cursors

• Efficient mechanism to iterate over a relation
  tuple-by-tuple
• Main operations
• Open, fetch, close
• Usually used inside loops
• Cursors can be parameterized
• What they return depends on the passed parameters

27
Todays Roadmap

• Triggers
• Assertions
• Cursors
• Stored Procedures
• ODBC/JDBC

28
Stored Procedures

• What is stored procedure?
• Piece of code stored inside the DBMS
• SQL allows you to define procedures and functions
  and store them inside DBMS
• Advantages
• Reusability do not need to write the code again
  and again
• Programming language-like environment
• Assignment, Loop, For, IF statements
• Call it whenever needed
• From select statement, another procedure or
  function

29
Stored Procedures in Oracle

• Stored procedures in Oracle follow a language
  called PL/SQL
• PL/SQL Procedural Language SQL

30
Creating A Stored Procedure
CREATE OR REPLACE PROCEDURE ltprocedureNamegt
(ltparamListgt) AS ltlocalDeclarationsgt ltprocedur
eBodygt

• Example
• Create Procedure test (id in int, name
  out string) As
• Begin
• .
• End

31
Example
32
Calling a Stored Procedure

• SQLgt exec ltprocedureNamegt (ltparamListgt)

SQL gt exec remove_emp (10)
33
Printing From Stored Procedures
Taking three parameters
Printing them to screen
34
Features in Stored Procedures
Create Procedure profiler_control(start_stop IN
VARCHAR2,
run_comm IN VARCHAR2,

ret OUT BOOLEAN) AS ret_code
INTEGER BEGIN  ret_code10   IF ret_code !0
THEN    retFALSE   ELSIF start_stop NOT IN
('START','STOP') THEN    retFALSE  
ELSIF start_stop 'START' THEN   
retFALSE    ELSE     retFALSE   END
IF END profiler_control /
35
More Features LOOP Statement

• CREATE PROCEDURE testProcedure (name
  string) AS
• num1 int
• BEGIN
• num1 10
• LOOP
• INSERT INTO Student VALUES (num1, name)
• num1 num1 1
• IF (num1 gt 15) THEN
• EXIT
• END IF
• END LOOP
• END

36
More Features CURSOR FOR Statement
Create Procedure OpeningBal (p_type IN string)
AS cursor C1 Is Select
productId, name, price From
products where type p_type
Begin For rec in C1 Loop Insert into
Temp values (rec.productId, rec.name, rec.price)
End Loop End /
37
Stored Functions

• Similar to stored procedures except that they
  return value

CREATE OR REPLACE FUNCTION ltfunctionNamegt

RETURN lttypegt (ltparamListgt) AS ltlocalDeclaratio
nsgt ltfunctionBodygt
38
Using Stored Procedures or Functions

• Stored Procedures
• Called from other procedures, functions,
  triggers, or standalone
• Stored Functions
• In addition to above, can be used inside SELECT
  statement
• In WHERE, HAVING, or projection list

39
Example
CREATE FUNCTION MaxNum() RETURN int AS
num1 int BEGIN SELECT MAX (sNumber) INTO num1
FROM Student RETURN num1 END /
SQLgt Select from Student where sNumber
MaxNum()
40
Summary of Stored Procedures/Functions

• Code modules that are stored inside the DBMS
• Used and called repeatedly
• Powerful programing language style
• Can be called from other procedures, functions,
  triggers, or from select statement (only
  functions)

41
Todays Roadmap

• Triggers
• Assertions
• Cursors
• Stored Procedures
• ODBC/JDBC

42
ODBC/JDBC

• Interfaces that allow applications to connect to
  a database and execute queries
• Applications can be java, C, C, C programs
• Application makes calls to
• Connect with the database server
• Send SQL commands to the database server
• Get the results back in your program
• ODBC (Open Database Connectivity) works with C,
  C, C, and Visual Basic
• JDBC (Java Database Connectivity) works with Java

43
JDBC

• JDBC is a Java API for communicating with
  database systems supporting SQL
• JDBC supports a variety of features for querying
  and updating data, and for retrieving query
  results
• Model for communicating with the database
• Open a connection
• Create a statement object
• Execute queries using the Statement object to
  send queries and fetch results
• Exception mechanism to handle errors

44
JDBC Code Example
45
JDBC Code Example (Contd)
46
ODBC

• Similar to JDBC, but has its own syntax
• Works with C, C, C languages

47
End of Advanced SQL

• Triggers
• Assertions
• Cursors
• Stored Procedures
• ODBC/JDBC

To check any syntax ? Google is you friend !!!
48
Subqueries in DML
49
Subquery in DML DELETE

• DML Data Manipulation Language

50
Subquery in DML UPDATE
51
Subquery in DML INSERT
Any order of columns
Follows the order in CREATE TABLE command