3.

3 VIEWS (S-16, S-17

A view is a virtualtable,through which a selective portion of the data from one ormore tables can be
seen.Views allow users to do the following:
Indixan index ra a thuma objtch that can spud up Hu
LebHievat of RowT by uitng potnl. An ndur pxovid
diHAct dnd fayt acus to HolUT tn a labll
 eges o PL/SQL are given belovW:
1. (Block Structures: PL/sQL consists of blocks of code, which can be nested within each
otherEacn
block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in thedatabase and
reused.
2. Procedural Language Capability:(PL/SQL consists of procedural language constructs such as
conditional statements (if else statements) and loops like (FOR loops).)
3.(Better Performance:) PL/sQL engine processes multiple sQL statements simultaneously as a
single block, thereby reducing network traffic, and provides high performance for the
application.
4. Error Handling: PL/SQL handles errors or exceptions effectively during the executionbf a
PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the
typeof the exception or it can be displayed to the user with a message.
[4.1]
 3.27
SaL...
Interactive SQL and Advance
34 SEQUENCES
3 4

nce isis aa database

Asequeno datab
object that (S-16, W-17, S-18)
edto
automaticaly
toautomatical
generate generates unique integerr numbers
numbers in sequential order. It canbe
nces can be either in
primary key values in sequen
Sequen
ascending
or
unique key values
res/Characteristics of Sequences: descending order.
or

available to all users of

1 Sequences are
the database.
(S-18)
Sequences are created using sQL statements.
Sequences have a
minimum and maximum
maximum=263-1); they can be dropped,maximum
but not
value (the defaults
reset
are minimum=0 and
minima
4 Once
4 Onceaa
sequence returns a value, the sequence can never return that
5. While sequence values are not tied to any same value.
particular table, a sequence is usually used to
values for only one table. generate
6 Sequences increment by an amount specified when
created (the default is 1).
3.4.1 Creating Sequences (S-16, W-17
To create a sequence in your schema, you must have the CREATE SEQUENCE system
privilege.
The syntax for creating the sequence is,
CREATE SEQUENCE<sequence_name>
(START WITH star_num]
INCREMENT BY increment_num]
I(MAXVALUE maximum_num NOMAXVALUE}]
MINVALUE minimum_num NOMINVALUE}]
IfCYCLE I NOCYCLE}]
CACHE cache_num NO CACHE}]
IfORDER | NOORDER)}]5
 8
5.3.2 ACID Properties a unit of program that updates
maintains the
maintains the following
o ensure the integrity of data during a transaction(Au database
system
(the
virlous data items, read m o r e about it here),
properties. The ACID properties are explained below: explained
below:

propertíes and nsaction reflec

reflect in
These properties are widely known as ACID of a transaction
either all the operations this: Suppoe
ensures that
understand
Atomicity: This property
banking system to pose
example of to AccountR
take an transrerring 100S
udtaDase) or none. Let's 700$. AccountA is
400S and B has balance (b) Creating100s
ACCOunt Ahas a balance of Debiting 100$ from A's
transaction that has two operations (a) second failed, in this case A'
nis is a successfully while
balance. Let's say first operation passed is unacceptable ina
tOBS 700$ instead of 800$. This
B would be having of the operation or it
Dalance would be 300$ while should fail without executing any
Either the transaction
banking system. ensures that.
The Atomicity property
should process both the operations. the execution of
transaction shouldtake
the consistency of database,
2. Consistency: To preserve other transaction should run concurrently when there is a
place in isolation (that means
no
a balance of 400$ and itis
For example account A is having
transaction already running). have two transactions here. Let's say these
B and C both. So we
transferring 100$ to account that case the final
concurrently and both the transactions read 400$ balance, in
transactions run
be 300$ instead of 200$. This is wrong. If the transaction were to runin
balance of Awould
the correct balance 300$ (before debiting
isolation then the second transaction would have read
went successful.
100$) once the first transaction
3. Isolation: If there are multiple transactions executing simultaneously, then all the transaction
should be processed as if they are single transaction. But individual transaction in it should not
alter or affect the other transaction.That means each transaction should be executed as iftney
are independent.
4, (Durability: The database should be strong enough to handle any system failure,It should not o
working for single transaction alone. It should be able to handle multiple transactions too.
4,4 CURSORS (S-16, W-16, S-17, s-18)
PL/SQL Cursor is a pointer that points to the results set of the SQL query against database table.
Cursoris a work area in pl/sql which is used by sql server used to store the result of a query.
Each column value is pointed using pointer. You can independently manipulate cursorvalues. A bit
aboutit's working, suppose you ask for a query stored in the server. At first a cursor consisting of
query result is created in server.now the cursor is transferred to the clientwhere again cursor is
created and hence the result is displayed.
Acursoris a mechanism by which we can assign a name to a "SELECT statement"and manipulate the
information within that SQL statement.
Thereare two kinds of cursors:
Amplicit and
V. Explicit. inulatian amente i n c n d i n a aueries t h a t
(*)Database secwty o t e cts and Se cuUL&
daabaoe aaeinit untentuomol o auidenta
tueods.
Datuoaoe Secuity as bD vauiou
mea aRs O an 2atoma t E a to ema R
De a e Proto cto o) o m Jmtuno aund
Coteunal thueot
uALd to emu conyidentiauitu intigait
and atcessalaildu
 Failure

Transaction Failure System Crash Disk Failure

- he
txplarn concoph of angg

AtHug9u naPL
PLQL blo ck titUthR whrth ra fttud
AmL stahmunh rke tryet, Neluhe, Updah n e
whin
databan fabl ) xecutd on a

CREATE OR REPLRE
TRIGGE rgge. nam
BEFORE/nFTER)
[INSERT /vPDArt /BELETEJ
ON tabe name (FOR ACH
[WREN ConstiOnNT sThtemENT (FOR EAtH ROLO
PL 8QL block
4.6 FUNCTIONS (w-16,S-17, S-18)
A function is a logically grouped set of SQL and PL/SQL statements that perform a specific task.
A function is a named pl/sql code block that have been compiled and stored in one of the oracle
engines's system tables.
4.5 PROCEDURES (S-17, S-18
A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific
task)This is similarto a procedure in other programming languages.
 GROUP BY Clause: (S-16, S-18)
GROUP BY clause was added to SQL because aggregate functions, (like SUM)return the aggregate of
allcolumn values every time they are called, and without the GROUP BY functionit was impossible
tofind the sum for each individual group of column values.
Thesyntax for the GROUP BY function is:
SELECT Ccolumn, SUM (column) FROM table GROUP BY column;
 .(subquery or Inner query or Nested query is a query in a query
Subquery is usually added in the WHERE Clause of the SQL statement.
thefollowing SQL statements along with the comparision operators like Subqueries can be used with
There are a few rules that subqueries must follow: etc.
=, <, >,>=, <=

1. Subqueries must be enclosed within parentheses.

2. Asubquery can have only one column in the SELECT clause, unless
main query for the subquery to compare its selected columns. multiple columns are in the
3. An 0RDER BY cannot be used in a subquery, although the main query can use an
GROUP BY can be used to perform the same function as the ORDER in a ORDER BY. The
BY subquery.
4. Subqueries that return more than one row can only be used with multiple value
as the IN operator.
operators, such
h 5. The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used
within the subquery.
1. Subqueries with the SELECT Statement:
Subqueries are most frequently used with the SELECT statement.
The basic syntax is as follows:
SELECT Column_name [, column_name
FROM table1 [, table2]
WHERE Column_name OPERATOR

(SELECT column_name [, column_name

FROM table1 [, table2 ]
[WHERE]) Ja in hoinog folloAring records: