Assertions

• Mechanism: CREAT ASSERTION

– Components include:
• a constraint name,
• followed by CHECK,
• followed by a condition

• Both CHECK and ASSERTION are database constraints

defined by the SQL standards.

• An important distinction is that a CHECK is applied to a

specific base table, whereas an ASSERTION is applied to the
whole database.
• An assertion is a piece of SQL which makes sure a condition
is satisfied.
• It could mean locking out the whole table or even the whole
● 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

2
 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 );
3
 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 );
4
 Example 3

Customer city is always not null

Create Assertion CityCheck Check

( NOT EXISTS (
Select *
From customer
Where customer_city is null));

5
 Assertions: An Example
• “The salary of an employee must not be greater
than the salary of the manager of the department
that the employee works for’’
constraint
name,
CREAT ASSERTION SALARY_CONSTRAINT CHECK,
CHECK (NOT EXISTS (SELECT * condition
FROM EMPLOYEE E, EMPLOYEE M,
DEPARTMENT D
WHERE E.SALARY > M.SALARY AND
E.DNO=D.NUMBER AND D.MGRSSN=M.
SSN))
 Assertions vs. Triggers
▪ Assertions do not modify the data, they only check certain
conditions

▪ Triggers are more powerful because they 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

▪ Oracle does not have 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.

Trigger Event: Before Insert

We need triggers, assertions cannot be used

Create Trigger OpeningBal

Before Insert On Customer
For Each Row
Begin
IF (:new.balance is null or :new.balance < 100) Then
RAISE_APPLICATION_ERROR(-20004, 'Balance should be >= $100');
End IF;
End;
Embedded SQL
• Embedded SQL is a method of combining the computing
power of a programming language and the database
manipulation capabilities of SQL.

• Embedded SQL statements are SQL statements written

inline with the program source code of the host language.

• The first technique for sending SQL statements to the DBMS

is embedded SQL. Because SQL does not use variables and
control-of-flow statements, it is often used as a database
sublanguage that can be added to a program written in a
conventional programming language, such as C, C++ or
Fortran.

• This is a central idea of embedded SQL: placing SQL

statements in a program written in a host programming
language.

• Approach: Embed SQL in the host language.

– A preprocessor converts the SQL statements into special API
• SQL provides a powerful declarative query language.
There are queries that cannot be expressed in SQL, but can
be programmed in C, Fortran, Pascal, Cobol, etc.

• Nondeclarative actions -- such as printing a report,

interacting with a user, or sending the result to a GUI --
cannot be done from within SQL.

• The SQL standard defines embedding of SQL as embedded

SQL and the language in which SQL queries are embedded
is referred as host language. The result of the query is
made available to the program one tuple (record) at a time.
To identify embedded SQL requests to the preprocessor,
we use EXEC SQL statement:
EXEC SQL embedded SQL statement END-EXEC

A semi-colon is used instead of END-EXEC when SQL is

embedded in C or Pascal.
• Before executing any SQL statements, the program must
first connect to the database. This is done using:
EXEC-SQL connect to server user user-name using
password;
Here, server identifies the server to which a connection is
to be established.

• Variables of the host language can be used within embedded

SQL statements. They are preceded by a colon (:) to
distinguish from SQL variables (e.g., :credit_amount )

• Variables used as above must be declared within DECLARE

section.
• Syntax for declaring the variables, however, follows the
usual host language syntax.
EXEC-SQL BEGIN DECLARE SECTION
int credit-amount ;
● To write an embedded SQL query, we use the
declare c cursor for <SQL query>
statement. The variable c is used to identify the query

● Example:
● From within a host language, find the ID and name of
students who have completed more than the number of
credits stored in variable credit_amount in the host language

● Specify the query in SQL as follows:

EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount
END_EXEC
The variable c (used in the cursor declaration) is used to
identify the query
• The open statement for our example is as follows:
EXEC SQL open c ;

This statement causes the database system to execute

the query and to save the results within a temporary
relation.
• Embedded SQL statements: declare cursor, open,
and fetch statements.
EXEC SQL
declare c cursor for
select cname, ccity
from deposit, customer
where deposit.cname = customer.cname and deposit.
balance > :amount
END-EXEC

• where amount is a host-language variable.

EXEC SQL open c END-EXEC;

• This statement causes the DB system to execute the

query and to save the results within a temporary
relation.
• A series of fetch statement are executed to make
tuples of the results available to the program.
EXEC SQL fetch c into :cn, :cc END-EXEC

• The program can then manipulate the variable cn

and cc using the features of the host programming
language.

• A single fetch request returns only one tuple. We

need to use a while loop (or equivalent) to process
each tuple of the result until no further tuples.

• We need to use close statement to tell the DB system

to delete the temporary relation that held the result
of the query.

EXEC SQL close c END-EXEC

Embedded SQL can execute any valid update, insert,

or delete statements.
 Updates Through Embedded SQL
● Embedded SQL expressions for database modification (update,
insert, and delete)
● Can update tuples fetched by cursor by declaring that the cursor is
for update
EXEC SQL
declare c cursor for
select *
from instructor
where dept_name = ‘Music’
for update

● We then iterate through the tuples by performing fetch

operations on the cursor and after fetching each tuple we execute
the following code:
update instructor
set salary = salary + 1000
where current of c
Dynamic SQL
 Dynamic SQL
• Dynamic SQL is a programming methodology for
generating and running statements at run-time.

• It is mainly used to write the general-purpose

and flexible programs where the SQL statements
will be created and executed at run-time based
on the requirement.

❑ Ways to write dynamic SQL

• PL/SQL provides two ways to write dynamic SQL
✔ NDS – Native Dynamic SQL
✔ DBMS_SQL
 NDS
• Native Dynamic SQL is the easier way to write dynamic
SQL. It uses the 'EXECUTE IMMEDIATE' command to
create and execute the SQL at run-time. But to use this
way, the datatype and number of variable that to be
used at run time needs to be known before. It also gives
better performance and less complexity when compares
to DBMS_SQL.
• Syntax Explanation:
• The above syntax shows EXECUTE IMMEDIATE
command.
• Clause INTO is optional and used only if the dynamic
SQL contains a select statement that fetches values.
• The variable type should match with the variable type
of the select statement.
• Clause USING is optional and used only if the dynamic
SQL contains any bind variable.

• Example1: In this example, we are going to fetch the data from

emp table for emp_no '1001' using NDS statement.
• Code Explanation:
• Code line 2-6: Declaring variables.
• Code line 8: Framing the SQL at run-time. SQL
contains the bind variable in where condition ':
empno'.
• Code line 9: Executing the framed SQL text (which is
done in code line 8) using the NDS command
'EXECUTE IMMEDIATE'
• The variables in 'INTO' clause (lv_emp_name,
ln_emp_no, ln_salary, ln_manager) is used to hold the
fetched values from the SQL query (emp_name,
emp_no, salary, manager)
• 'USING' clause gives the values to the bind variable
in the SQL query (:emp_no).
• Code line 10-13: Displaying the fetched values.
 DBMS_SQL for Dynamic SQL
• PL/SQL provide the DBMS_SQL package that allows you to
work with dynamic SQL. The process of creating and
executing the dynamic SQL contains the following process
• OPEN CURSOR: The dynamic SQL will execute in the same
way as a cursor. So in order to execute the SQL statement,
we must open the cursor.
• PARSE SQL: The next step is to parse the dynamic SQL. This
process will just check the syntax and keep the query ready
to execute.
• BIND VARIABLE Values: The next step is to assign the
values for bind variables if any.
• DEFINE COLUMN: The next step is to define the column
using their relative positions in the select statement.
• EXECUTE: The next step is to execute the parsed query.
• FETCH VALUES: The next step is to fetch the executed
values.
• Code Explanation:
• Code line 1-9: Variable declaration.
• Code line 10: Framing the SQL statement.
• Code line 11: Opening the cursor using DBMS_SQL.
OPEN_CURSOR. It will return the cursor id which is opened.
• Code line 12: After the cursor is opened, the SQL is parsed.
• Code line 13: Bind variable '1001' is assigning to the cursor
id instead ':empno'.
• Code line 14-17: Defining the column name based on their
relative position in the SQL statement. In our case, the
relative position is (1) emp_name, (2) emp_no (3) salary (4)
manager. So based on this position we are defining the
target variable.
• Code line 18: Executing the query using DBMS_SQL.
EXECUTE. It returns the number of records processed.
• Code line 19-33: Fetching the records using a loop and
displaying the same.
• Code line 20: DBMS_SQL.FETCH_ROWS will fetch one
record from the rows processed. It can be called repeatedly
to fetch all the rows. If it cannot fetch rows, it will return 0,
thus exiting the loop.
 How to grant privlediges
• previledges
END