Transcript Advanced SQL

Advanced SQL
Part II
Instructor: Mohamed Eltabakh
[email protected]
1
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
Today’s Roadmap

Triggers

Assertions

Cursors

Stored Procedures

ODBC/JDBC
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
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 <name>
Before| After
Insert| Update| Delete On <tableName>
Referencing
OLD AS oldRec,
NEW AS newRec
For Each Row | For Each Statement
When <condition>
Begin
….
End;
Event
Pointers to old and
new records
Event Granularity
Condition
Action
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
Indicate two events at the
same time
Begin
newRec.bonus := newRec.salary * 0.03;
End;
The bonus value is always
computed automatically
6
Before vs. After

Before Event

When checking certain conditions that may cause the operation to be
cancelled


When modifying values before the operation


E.g., if the name is null, do not insert
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 := ….
After Insert Trigger:
newRec.x := …
//Changing value x that will be inserted
//meaningless because the value is already inserted
Example
For Employees with salary < 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
Can specify which updated column
After Update Of salary On Employee
Referencing
OLD AS oldRec, NEW AS newRec
For Each Row
Begin
IF (newRec.sal < 50,000 and oldRec.sal >=50,000) THEN
Insert into LowSalaryEmp values (newRec.EmpId, newRec.sal);
ELSIF (newRec.sal < 50,000 and oldRec.sal < 50,000)
Update LowSalaryEmp set sal = newRec.sal Where empId = newRec.EmpId;
ELSIF (newRec.sal >= 50,000 and oldRec.sal < 50,000)
Delete from LowSalaryEmp Where empId = newRec.EmpId;
END IF;
End;
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 < 60000) THEN
RAISE_APPLICATION_ERROR (-20004, 'below min salary');
END IF;
IF (UPDATING AND newRec.salary < oldRec.salary) THEN
RAISE_APPLICATION_ERROR (-20004, ‘Salary Decreasing !!');
END IF;
END;
cs3431
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)
Today’s Roadmap

Triggers

Assertions

Cursors

Stored Procedures

ODBC/JDBC
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
Must return True or False
12
Example 1
Sum of loans taken by a customer does not
exceed 100,000
Must return True or False
(not a relation)
Create Assertion SumLoans Check
( 100,000 >= ALL
Select Sum(amount)
From borrower B , loan L
Where B.loan_number = L.loan_number
Group By customer_name );
13
Example 2
Number of accounts for each customer in a given
branch is at most two
Create Assertion NumAccounts Check
( 2 >= ALL
Select count(*)
From account A , depositor D
Where A.account_number = D.account_number
Group By customer_name, branch_name );
14
Example 3
Customer city is always not null
Create Assertion CityCheck Check
( NOT EXISTS (
Select *
From customer
Where customer_city is null));
15
Example 4 (Exercise)
The customer city must be as the branch city to have
an account or a loan in that branch
16
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
Example: Trigger vs. Assertion
All new customers opening an account must have opening balance >= $100.
However, once the account is opened their balance can fall below that amount.
We need triggers, assertions cannot be used
Trigger Event: Before Insert
Create Trigger OpeningBal
Before Insert On Customer
Referencing
NEW AS newRec
For Each Row
Begin
IF (newRec.bal is null or newRec.bal < 100) Then
RAISE_APPLICATION_ERROR(-20004, ‘Balance should be >= $100’);
End IF;
End;
Today’s Roadmap

Triggers

Assertions

Cursors

Stored Procedures

ODBC/JDBC
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 <name> IS <SQL query>;
Cursor HighSalEmp IS
Select empID, name, salary
From Employee
Where salary > 120,000;
Cursor Operations


Create cursor
Put pointer to the first tuple
Fetch next tuple


Open HighSalEmp;
Open cursor


Cursor HighSalEmp IS
Select empID, name, salary
From Employee
Where salary > 120,000;
Fetch HighSalEmp into <variable>;
Pointer moves automatically when a tuple is fetched
Close cursor
Close HighSalEmp;
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 (C1%Found) THEN
// make sure the fetch was successful
….
ELSE
exit;
// break the loop
END IF;
End Loop;
close C1;
// close the cursor
End;
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;
Cursor Attributes

These are attributes maintained by the system

Attributes include:

C1%ROWCOUNT: The number of tuples in C1

C1%FOUND: TRUE if the last fetch was successful

C1%NOTFOUND: TRUE if the last fetch was not successful

C1%ISOPEN: TRUE if C1 is open
Parameterized Cursor

Cursors can take parameters while opening them
Create Trigger OpeningBal
After Insert On Customer
Reference NEW as newRec
Define a parameter
Declare
cursor C1(type_Of_Interest string) Is
Select productId, name, price
From products
where type = type_Of_Interest;
Pass the parameter
Begin
For rec in C1(newRec.type) Loop
Insert into Temp values (rec.productId, rec.name, rec.price);
End Loop;
End;
Summary of Cursors

Efficient mechanism to iterate over a relation tuple-bytuple

Main operations



Open, fetch, close
Usually used inside loops
Cursors can be parameterized

What they return depends on the passed parameters
Today’s Roadmap

Triggers

Assertions

Cursors

Stored Procedures

ODBC/JDBC
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
Stored Procedures in Oracle

Stored procedures in Oracle follow a
language called PL/SQL

PL/SQL: Procedural Language SQL
cs3431
Creating A Stored Procedure
CREATE [OR REPLACE] PROCEDURE <procedureName> [(<paramList>)] AS
<localDeclarations>
<procedureBody>;
A parameter in the paramList is specified as:
<name> <mode> <type>
<mode> is one of {IN, OUT, INOUT}
Example:
Create Procedure test (id in int, name out string) As
Begin
….
End;
cs3431
Example
Define a variable
By default, it is IN
In PL/SQL a ‘;’ ends a line without execution
Execute the command and
create the procedure
Calling a Stored Procedure

SQL> exec <procedureName> [(<paramList>)];
SQL > exec remove_emp (10);
Printing From Stored Procedures
Taking three parameters
Printing them to screen
Features in Stored Procedures
IN parameters
OUT parameters
Create Procedure profiler_control(start_stop IN VARCHAR2,
run_comm IN VARCHAR2,
ret OUT BOOLEAN) AS
ret_code INTEGER;
BEGIN
ret_code:=10;
IF ret_code !=0 THEN
ret:=FALSE;
ELSIF start_stop NOT IN ('START','STOP') THEN
ret:=FALSE;
ELSIF start_stop = 'START' THEN
ret:=FALSE;
ELSE
ret:=FALSE;
END IF;
END profiler_control;
/
Variable declaration
Variable assignment
IF statement
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 > 15) THEN
EXIT;
END IF;
END LOOP;
END;
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;
/
Stored Functions

Similar to stored procedures except that they return value
CREATE [OR REPLACE] FUNCTION <functionName>
RETURN <type> [(<paramList>)] AS
<localDeclarations>
<functionBody>;
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
Example
CREATE FUNCTION MaxNum() RETURN int AS
num1 int;
BEGIN
SELECT MAX (sNumber) INTO num1 FROM Student;
RETURN num1;
END;
/
SQL> Select * from Student where sNumber = MaxNum();
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)
Today’s Roadmap

Triggers

Assertions

Cursors

Stored Procedures

ODBC/JDBC
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
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
JDBC: Code Example
JDBC Code
public static void JDBCexample(String dbid, String userid, String passwd)
{
Connecting to Oracle DB
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@aura.bell-labs.com:2000:bankdb", userid, passwd);
Statement stmt = conn.createStatement();
Do Actual Work
.
DB name, port number, userId, password
stmt.close();
Holder for SQL statement
conn.close();
}
catch (SQLException sqle) {
System.out.println("SQLException : " + sqle);
}
}
ase System Concepts - 5th Edition, June 15, 2005
4.38
©Silberschatz, Korth and Sudarshan
JDBC: Code Example (Cont’d)
ODBC

Similar to JDBC, but has its own syntax

Works with C, C++, C# languages
End of Advanced SQL

Triggers

Assertions

Cursors

Stored Procedures

ODBC/JDBC
To check any syntax  Google is you friend !!!
Subqueries in DML
Subquery in DML DELETE

DML: Data Manipulation Language
49
Subquery in DML UPDATE
50
Subquery in DML INSERT
Any order of columns
Follows the order in
CREATE TABLE command
51