advanced sql
DESCRIPTION
Advanced SQL. Instructor: Mohamed Eltabakh [email protected]. Part II. Remaining Lectures. Today Complete Advanced SQL Dec 6. Brief Introduction to Transactions and Indexes Due date for HW4 & Phase 4 - PowerPoint PPT PresentationTRANSCRIPT
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
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
Event Granularity
Condition
Action
Pointers to old and new records
Example: Maintenance of Derived Attributes
6
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;
The bonus value is always computed automatically
Indicate two events at the same time
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
Example
What triggers do we need? After Insert, After Update, After Delete on Employee
For Employees with salary < 50,000, keep the EmpID and salary in table LowSalaryEmp
Create Trigger EmpUpdate
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;
Can specify which updated column
cs3431
Combining Multiple Events in One Trigger
CREATE TRIGGER salaryRestrictionsBEFORE INSERT OR UPDATE ON Professor
Referencing
OLD AS oldRec, NEW AS newRec For Each RowBEGIN
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;
Can write different code for different events
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)
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
12
Must return True or False
Example 1
13
Sum of loans taken by a customer does not exceed 100,000
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 );
Must return True or False (not a relation)
Example 2
14
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 );
Example 3
15
Customer city is always not null
Create Assertion CityCheck Check
( NOT EXISTS (
Select *
From customer
Where customer_city is null));
Example 4 (Exercise)
16
The customer city must be as the branch city to have an account or a loan in that branch
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 usedTrigger 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;
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
Open cursor Put pointer to the first tuple
Fetch next tuple Pointer moves automatically when a tuple is fetched
Close cursor
Cursor HighSalEmp IS
Select empID, name, salary
From Employee
Where salary > 120,000;
Open HighSalEmp;
Fetch HighSalEmp into <variable>;
Close HighSalEmp;
ExampleCreate 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
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;
Define a parameter
Pass the parameter
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
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
cs3431
Stored Procedures in Oracle
Stored procedures in Oracle follow a language called PL/SQL
PL/SQL: Procedural Language SQL
cs3431
Creating A Stored Procedure
Example: Create Procedure test (id in int, name out string) As Begin
…. End;
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
By default, it is INDefine a variable
Execute the command and create the procedure
In PL/SQL a ‘;’ ends a line without execution
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_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;/
IN parameters
OUT parameters
Variable declaration
Variable assignment
IF statement
More Features: LOOP Statement
CREATE PROCEDURE testProcedure (name string) ASnum1 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)
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
Connecting to Oracle DB
DB name, port number, userId, password
Holder for SQL statement
End of Advanced SQL
Triggers
Assertions
Cursors
Stored Procedures
ODBC/JDBC
To check any syntax Google is you friend !!!