Database
Objects:
Synonyms:
A
synonym is Alias for any Database object. We can create synonym for
all the DB objects.
Syntax:
Create
synonym syn1 for emp;
We
can do any DML operation.
We
cannot do DDL operation in Synonym except Drop
command.
Once
we created synonyms on some other table, the link will be there
between synonym and the table. If we dropped the table, the synonym
is no longer valid. However, if we recreated the table with the same
name. The link will be created again with the existing synonym.
Example:
Create
synonym
syn1 for emp;
Drop
table emp;
//The emp table will be dropped from the DB.
Now
synonym will no longer valid. It will be an inactive state.
If
we recreated the table with the same name as emp,
the synonym will be
activate again and mapped with the new emp
table.
Types
of Synonym:
Public
Synonym
Private
Synonym
Public
Synonym:
DBA
have access to create Public Synonym
Syntax:
Create
public synonym syn1 for emp;
Private
Synonym:
Creating
the synonym by using them username & Password.
Syntax:
Create synonym syn1 for emp;
If
public synonym name as SYN1 and private synonym name as SYN1, if we
run the query the private synonym will run in the DB.
If
we would like to access public synonym, we should drop the private
synonym 1st
to access public synonym.
Rownum:
Rownums
are Result set number for the Database query. It will changes result
set by result set.
Example:
select rownum,id,name from mouse;
|
ROWNUM
|
ID
|
NAME
|
|
1
|
1004
|
Ramesh
|
|
2
|
1005
|
Barath
|
|
3
|
1037
|
YYY
|
|
4
|
1001
|
Vijay
|
|
ROWNUM
|
ID
|
NAME
|
|
1
|
1001
|
Vijay
|
select rownum,id,name from
mouse where id=1001
To
find Nth Max Salary Values:
select
min(sal) from (select * from mouse order by sal desc) where
rownum<=n;
While
executing the above query Rownum
will assign the value
as below. The query will be checking the data one by one to find out
the requested data. However, when it is reaching the value as 0, the
rownum condition will be failed and quit to checking. For that reason
we are using <= symbol, while fetching the data.
-
|
Data
|
Rownum
Assigning theValue as
|
|
1
|
1
|
|
2
|
0
|
|
3
|
1
|
|
4
|
0
etc.,
|
Row
Id:
Inline
View:
The
result of the query is converting as a table and the values are
assigned in where condition.
Example:
To
fetch Even Record data.
Select
* from (select
rownum rn, e.* from mouse e)rslt
where mod(rslt.rn,2)=0
To
Fetch Odd Record data.
Select
* from(select rownum
rn, e.* from mouse e)rslt
where mod(rslt.rn,3)=0
SEQUENCES:
Sequences
are used to generate primary key values automatically. Once the
sequences are generated, it should not rollback.
Syntax:
Create
Sequence seq1
Increment by 1
Starts with 1
min value
0
max Value 1000
Cycle/no cycle // The process whether the
cycle process or not, it will be decided here
Cache/no cache //
Increment
by:
Specify
the interval between sequence numbers. Increment by should not be 0.
The value must be less than the MIN value and MAX value. If increment
by value is positive, the ascending process will be happen. If the
value is negative then the descending process will be happen.
Starts
with:
Specify
the first sequence number to be generated. The start with value
should >= MIN value and < MAX value.
Min
Value:
MINVALUE
must be <= START WITH and < MAXVALUE.
Cycle:
Specify
CYCLE to indicate that the sequence continues to generate values
after reaching either its maximum or minimum value.
NoCycle:
Specify
NOCYCLE to indicate that the sequence cannot generate more values
after reaching its maximum or minimum value. This is the default.
Cache:
Specify
how many values of the sequence the database preallocates and keeps
in memory for faster access. Cache value should be less than the
start with value.
Alter
the sequence:
Alter
sequence seq_name
increment by 124;
Properties:currval,
nextval
Using
Sequences through query:
Select
sequence_name.currval from dual;
Insert
into mouse(id,name) values (sequence_name.nextval,’Vijayan’);
Points
to remember:
If
the sequence condition reaches maximum value, it should go to
minimum value for the next cycle.
If
you used “Select seq1.currval, seq1.nextval from dual” or
“Select seq1.nextval, seq1.currval from dual”. It will provide
the result of nextval result of both the columns. For example, if
the sequence value is 4, when we are using the above query the
output will give the result of currval5,
nextval5.
View:
A
view is simply the representation of a SQL statement that is stored
in memory so that it can easily be re-used.
If
we written 1000 lines of query, every time we should not type all
those lines in DB while executing. For that purpose we are creating a
view. We can simply call view, whenever we need to execute those 1000
lines query.
Syntax:
Create
view v1 as select id, name from mouse;
Types
of View:
Normal
View
Simple
View
Complex
View
Materialized
View
We
can see the view in all_views
table.
Query:
Select
text from all_views where view_name=’v1’;
If
anything inserted after view created, the data will not fetch by the
view
Once
the view created, we should not drop any data from the database. If
you do so, it will throw an error.
We
can do DML operation through view, if not null column (primary key)
should be added while creating the view.
By
using the view, we will not store any data. Since view is a Virtual
table.
If
we need to delete or update any data, the view should contain the
data.
Simple
View:
Create
view v1 as select id, name from mouse;
Complex
View:
Complex
views contain the difficult query, which is written by the user as
below
Materialized
View:
A
materialized view is a database
object that
contains the results of a query.
They are local copies of data located remotely, or are used to create
summary tables
based on aggregations of a table's data. Materialized views, which
store data based on remote tables, are also known as snapshots.
Once
we are doing any DML operation, the data will not reflect
immediately in the database
The
updating process can be done in Materialize view in the Regular
Intervals.
The
performance will be improved
Force
View:
We
can create a view, which the table does not exist in the database by
using the force view.
Syntax:
create force view invalid_view as
select * from table_that_does_not_exist
A
view can be created even if the defining query of the view cannot be
executed, as long as the CREATE VIEW command has no syntax errors. We
call such a view a view
with errors. For
example, if a view refers to a non-existent table or an invalid
column of an existing table, or if the owner of the view does not
have the required privileges, then the view can still be created and
entered into the data dictionary.
Advantages:
Data
isn't dependent on structure of the database.
Restrict
access to a database.
Subset
of data contained in a table.
Join
and simplify multiple tables into a single virtual table.
Acts
as aggregated tables, where aggregated data (sum, average etc.) are
calculated and presented as part of the data.
Gives
real time data.
To
hide data complexity.
To
protect the data.
Disadvantages:
Rows
available through a view are not sorted and are not ordered either.
Cannot
use DML operations on a View.
When
table is dropped view becomes inactive, it depends on the table
objects.
It
affects performance, querying from view takes more time than
directly querying from the table
Index
An
index is a performance-tuning method of allowing faster retrieval of
records.
Syntax:
Create
index index_name on table_name(column_names);
Types
of Index:
B-Tree
Index (Default Index)
Bitmap
Index
Composite
Index
Unique
Index
Reverse
key Index
B-Tree
Index (Default Index):
The
column should contain the results of More
Distinct (unique)
values and less
duplicate values.
Create index index_name
on table_name(column_names);
Bitmap
Index:
The
column should contain the results of More
duplicate values and
less Distinct (unique)
values.
create
bitmap index i1 on mouse(id);
B-tree
indexes are virtually useless on low-cardinality columns (columns
with only a few distinct values). By the time 4% or more of a table
is read via an index, it would be faster to perform a full table
scan. Bitmap Indexes do not solve this problem in general, but they
do solve a specific case. If a table contains 2 or more
low-cardinality columns and they
are used in combination in a SQL statement to reduce the number of
rows returned, then bitmap indexes can perform faster than a full
table scan.
Use
bitmap indexes when:
A
table has 2 or more low cardinality columns. Create a bitmap index
on each column
Two
or more bitmap indexed columns are used together in AND and OR
conjunctions, using equals (=), IN, and NOT IN conditions.
The
WHERE clauses on the bitmap indexed columns, when combined, reduce
the rows returned to a small percentage of the table or partition.
The
bitmap indexed table has low volatility (does not change much), or
all updates are performed by a single session. Bitmap indexes on
high volatility tables can cause locking problems.
Composite
Index:
A
composite index is an index that is made up of more than one column.
But normal index can be used in only one column.
Create index i1 on mouse (id,
name, sal, desg);
Unique
Index:
Improving
the performance by fetching the unique values. It will allow the
null values.
Composite
index can be applied.
If
any constraints are used, we don’t need to create unique index
explicitly. Since automatically unique index will be triggered.
Reverse
Key Index:
The
data most likely common, but the last digit should be differing. To
this scenario we should use Reverse key index.
For
example:
100001
100002
100003
Functional
Index:
If
we are using any functional statement on the table, we can create
functional index.
It's
easy and provides immediate value.
It
can be used to speed up existing applications without changing any
of their logic or queries.
It
can be used to supply additional functionality to applications with
very little cost.
Create
Index i1 on mouse(upper(name);
Locks:
Locks
are mechanisms that prevent destructive interaction between
transactions accessing the same resource.
Types
of Index:
Row
level locking
Table
level locking
2.1) Share mode
2.2)
Share update mode
2.3)
Exclusive
Row
level Locking:
A,
B users logged on to the same database, if A is trying to do update
on any particular row to the particular table, A is explicitly locked
the particular table. Hence the user B should not able to update any
record to the particular table until commit/Rollback operation by A.
If
the commit/rollback given by A, the locks will be released.
Lock
the Rowlevel:
Syntax:
Select * from mouse where id=1001for update;
Table
Level Lock;
We
can able to lock the whole table.
Share
mode:
Multiple
users can lock the single table at a time. However, if a user trying
to do DML operation on the particular table, the other users unable
to do anything on the table until commit/Rollback operation by the
particular user.
Syntax:
Lock
table emp in share mode;
Share
update mode:
It
is similar to Row level lock, but it is lock the whole table not the
single row.
Lock
table emp in share update mode;
Exclusive
lock:
If
you locked by exclusive mode, none other user could not do any
operation on the particular table until commit/Rollback operation by
the particular user.
Syntax: lock table emp in exclusive mode;
Deadlock:
A
deadlock is a condition where two or more users are waiting for data
locked by each other. Oracle automatically detects a deadlock and
resolves them by rolling back one of the statements involved in the
deadlock, thus releasing one set of data locked by that statement.
Statement rolled back is usually the one which detects the deadlock.
Deadlocks
are mostly caused by explicit locking because oracle does not do lock
escalation and does not use read locks. Multitable deadlocks can be
avoided by locking the tables in same order in all the applications,
thus precluding a deadlock
Example:
User
1 is locked emp table.
User 2 is locked department table.
Now,
user1 typed query in the DB to lock the department table, which is
locked by user2. The same as user2 typed query in the DB to lock the
emp table, which is locked by user1. In this scenario the deadlock
occurs.
PL/SQL
Boolean
value
Null, True, False
Structure
of PL/SQL code:
Declaration
Part
Execution
Part
Mandatory
Exception
Part
%type
name1 mouse.name%type
If
you are specifying any particular column %type will be used.
%rowtype
emprec emp%rowtype;
We
can get the whole table columns values to the emprec variable. We can
call it as Record.
Simple
explanation:
%type
for the single column values
%rowtype
for all the column values
Types
of Loops:
Normal
Loop
For
Loop
While
Loop
Loop:
Syntax:
Declare
//declare the variables
begin
loop
exit when
(condition)
end loop
end
For
Loop:
begin
for
I in 1..50
loop
if (mod(1,2)=0)
dbms_output.put_line(i);
end if;
end loop;
end;
While
Loop:
Begin
i:=0;
while(i<=50)
loop
i:=i+2;
if
(mod(I,2)=0)
dbms_output.put_line(i);
end if;
end
loop;
end;
Cursor:
Cursor
is used to fetch more than one value from the select query. ( To
handle more than one row).
Types
of Cursors:
Static
Implicit
Cursor
Explicit
Cursor
Dynamic
Reference
Cursor
Implicit
Cursor:
While
doing the insert & update through the sql query the implicit
cursor automatically called by the oracle.
Explicit
Cursor:
If
the select statements fetching more than one record at a time we will
use Explicit cursor.
We
should manually define the cursor by the below way:
Cursor
cursor_name is select id, name from mouse;
Cursor
Attributes:
%is
open
%found
%not
found
%rowcount
%Isopen,
%found & %notfound attributes are Boolean datatype. Because these
attributes are a yes or no option.
%Rowcount
will tells current number of record, which is fetching from the DB.
Cursor
Commands:
Five
steps needs to be follow, when we specifying Cursor:
1.
Declare the variables to store the columns values from the SELECT
statements.
2. Declare the cursor to specify the SELECT
statement.
3. Open the cursor
4. Fetch the values from the
cursor
5. Close the cursor
Simple
example:
Declare
Cursor
c1 is select id, name from mouse;
cid mouse.id%type;
cname
mouse.name%type;
begin
open c1;
fetch c1 into cid,
cname;
dbms_output.put_line(‘ ‘ || cid ‘ ‘ ||
cname);
exit when c1%notfound
endloop;
close c1;
end;
For
Cursor:
By
using the normal cursor, If 100 variables are there on the tables; we
should define the each and every variable on the declaration section
and fetch the 100 data by using the fetch command. To avoid these we
are going to For cursor.
Usage
No
need to declare the variables
No
need to open the cursor
No
need to fetch the data.
No
need to close the cursor.
Simple
example:
Declare
cursor
c1 is select * from mouse;
begin
for rec in
c1
loop
dbms_output.put_line(c1%rowcount || rec.sal);
end
loop;
end;
If
we are using any cursor attribute in For cursor, we should define
those attributes within the loop section. Because after ending the
loop For cursor automatically closed the cursor. Or else, it will
throw an error.
Parameterized
Cursor:
We can
pass the value in the run time.
Example
with Normal cursor:
Declare
Cursor
c1 (id1 in number) is select id, name from mouse where id=id1;
a_id
mouse.id%type;
a_name mouse.name%type;
begin
open
c1(&id1)
loop
fetch c1 into a_id, a_name;
exit when
c1%notfound;
dbms_output.put_line(a_id || ‘ ‘ || a_name);
end
loop;
end;
Example
with For cursor:
begin
for
rec in (select id,name from mouse where
id=&id)
loop
dbms_output.put_line(rec.id || ' ' ||
rec.name);
end loop;
end;
To
find out the how many number of records are updating in DB:
default
cursor name: sql
example:
sql%rowcount
begin
update
temp1 set product_id=10032 where
product_id=10033;
dbms_output.put_line(sql%rowcount || ' Records
are updated');
end;
Reference
Cursor:
A
REF CURSOR is basically a data type. A variable created based
on such a data type is generally called a cursor variable. A
cursor variable can be associated with different queries at
run-time. The primary advantage of using cursor variables is
their capability to pass result sets between sub programs (like
stored procedures, functions, packages etc.).
By
using the Reference cursor we can open multiple select statements at
a time. Also, it is not require closing the cursor. Automatically
cursor will be closed by Ref cursor.
Characteristics:
Ref
cursor resides in server memory not in client machine.
Ref
cursor is not updatable.
Can
access only serial manner not reverse.
Ref
cursor is a plsql data type
Types:
Strong
Return type defined.
For
the strong ref cursor the returning columns with datatype and length
need to be known at compile time.
Example:
TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
Weak
Return type not defined.
For
the weak ref cursor the structure does not need to be known at
compile time.
Example
: TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
We
have to manually define the datatype in Ref cursor.
To define the data type:
Type typ1 is Ref Cursor;
C1 typ1;
Reference Cursor examples:
declare
type typ1 is ref
cursor;
c1 typ1;
id1 mouse.id%type;
name1
mouse.name%type;
sal1 mouse.sal%type;
begin
begin
open c1 for select id,name from mouse;
loop
fetch c1 into id1,name1;
exit when
c1%notfound;
dbms_output.put_line(' ' || id1 || ' ' ||
name1);
end loop;
end;
begin
open c1 for select id,name,sal from mouse;
loop
fetch c1 into id1,name1,sal1;
exit when
c1%notfound;
dbms_output.put_line(' ' || id1 || ' ' ||
name1 || ' ' || sal1);
end loop;
end;
end;
Exceptions:
Run-time errors
arise from design faults, coding mistakes, hardware failures, and
many other sources. Although you cannot anticipate all possible
errors, you can plan to handle certain kinds of errors meaningful to
your PL/SQL program.
Types of Exceptions:
Pre-defined
Exception (Named System Exceptions)
User-defined
Exception
Un-defined
Exception (Unnamed System Exceptions)
Pre-defined
Exception (Named System Exceptions):
There are some
system exceptions which are raised frequently, so they are
pre-defined and given a name in Oracle which are known as Pre-defined
Exception.
Example:
Too_many_rows
Zero_divide
No_data_found
CURSOR_ALREADY_OPEN
INVALID_CURSOR
Example:
Declare
id1
mouse.id%type;
begin
select id into id1 from mouse where
id='1003';
Exception
When too_many_rows
then
dbms_output.put_line('too many rows
');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
End;
User-defined
Exception:
We
can explicitly define the exceptions based on business rules. These
are known as user-defined exceptions.
Three parts of
the exceptions:
Declare the
Exceptions
Raise
an Exceptions
Handle
the exceptions
declare
e1
exception;
m1 number;
m2 number;
m3 number;
begin
m1
:=&m1;
m2 :=&m2;
m3 :=&m3;
if(m1 < 25 or
m2 < 25 or m3 < 25) then
raise e1;
end
if;
dbms_output.put_line('Total ' || (m1+m2+m3));
exception
when
e1 then
dbms_output.put_line('Enter the number < 25');
end;
Raise_Application_Error:
RAISE_APPLICATION_ERROR
is a built-in procedure in oracle which is used to display the
user-defined error messages along with the error number whose range
is in between -20000 and -20999.
Whenever a message
is displayed using RAISE_APPLICATION_ERROR, all previous transactions
which are not committed within the PL/SQL Block are rolled back
automatically (i.e. change due to INSERT, UPDATE, or DELETE
statements).
RAISE_APPLICATION_ERROR
raises an exception but does not handle it.
RAISE_APPLICATION_ERROR
is used for the following reasons,
Syntax:
RAISE_APPLICATION_ERROR
(error_number, error_message);
declare
e1
exception;
m1 number;
m2 number;
m3 number;
begin
m1
:=&m1;
m2 :=&m2;
m3 :=&m3;
if(m1 < 25 or
m2 < 25 or m3 < 25) then
raise e1;
end
if;
dbms_output.put_line('Total ' || (m1+m2+m3));
exception
when
e1 then
RAISE_APPLICATION_ERROR(-20001,
'Enter the number < 25');
end;
Un-defined
Exception (Unnamed System Exceptions):
The
system exception for which oracle does not provide a name is known as
unnamed system exception (Pragma_exception_init).
There are two ways
to handle unnamed system exceptions:
1. By using the WHEN
OTHERS exception handler, or
2. By associating the exception code
to a name and using it as a named exception.
Note: If we
are using when others exception, it should be define in the last of
the pl/sql block. Or else, it will throw an error.
We can assign a name
to unnamed system exceptions using a Pragma called
EXCEPTION_INIT.
EXCEPTION_INIT
will associate a predefined Oracle error number to a
programmer_defined exception name.
Steps
to be followed to use unnamed system exceptions are :
• They
are raised implicitly.
• If
they are not handled in WHEN Others they must be handled explicity.
• To
handle the exception explicity, they must be declared using Pragma
EXCEPTION_INIT as given above and handled referecing the user-defined
exception name in the exception section.
Syntax:
DECLARE
exception_name EXCEPTION;
PRAGMA
EXCEPTION_INIT
(exception_name, Err_code);
BEGIN
Execution
section
EXCEPTION
WHEN exception_name THEN
handle the
exception
END;
Simple program:
Declare
v_ename
varcahar2(10);
e1 exception;
prgma_exception_init(e1,
-1)
begin
insert into emp select * from emp;
exception
when
e1 then
dbms_output.put_line(‘Unique constraint
violation’);
When others
then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrom);
end;
SQLCODE
Returns the type and oracle code
SQLERRM
Returns the text of the oracle error message
Note:
Sub-Programs:
The
subprograms called as a Named block.
Procedures
Functions
Packages
Procedures:
A stored
procedure or in simple a proc is a named PL/SQL
block which performs one or more specific task
A stored procedure
is a set of SQL commands that has been compiled and stored on the
database server.
Once the stored
procedure has been "stored", client applications can
execute the stored procedure over and over again without sending it
to the database server again and without compiling it again.
Stored procedures
improve performance by reducing network traffic and CPU load.
Procedures may or
may not return a value
Advantages:
Modularity
split the codes of some modules
Maintainability
Reusability
Three
Parameters:
In
Out
Inout
In:
We
can pass the values. But we cannot do any manipulation.
Out
& Inout:
It
will allow manipulation. If we are passing any values through the out
parameters, the final result of the out parameter value should be the
null.
We
will get and print the values. Also, it will allow doing the
manipulation.
Syntax:
Create
procedure prc1(A in number, b out varchar2, c out
number)
is
//declaration section
begin
//execution
partEXCEPTION
Exception section
end;
Simple
example:
create or replace
procedure p1 As
i number(10);
begin
for i in
1..50
loop
dbms_output.put_line(i);
end loop;
end;
Execute a Stored
Procedure
There are two ways
to execute a procedure.
1) From the SQL
prompt.
EXECUTE [or EXEC]
procedure_name;
2) Within another
procedure – simply use the procedure name.
procedure_name;
Functions:
A function is a
named PL/SQL Block which is similar to a procedure. A function must
always return a value.
Syntax:
CREATE
[OR REPLACE] FUNCTION function_name [parameters]
RETURN
return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return
return_variable;
EXCEPTION
exception
section
Return
return_variable;
END;
Return
Type: The header section defines the return type of the
function. The return datatype can be any of the oracle datatype like
varchar, number etc.
The
execution and exception section both should return a value which is
of the datatype defined in the header section.
Simple
Program:
CREATE
OR REPLACE FUNCTION employer_details_func
RETURN VARCHAR2
IS
emp_name VARCHAR(20);
BEGIN
SELECT name INTO
emp_name
FROM mouse WHERE id = 1001;
RETURN emp_name;
END;
Execute a PL/SQL
Function:
A function can be
executed in the following ways.
1) Since a function
returns a value we can assign it to a variable.
employee_name
:= employer_details_func;
2) As a part of a
SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL
Statements like,
dbms_output.put_line(employer_details_func);
Note:
If the functions
contain out/inout parameters, we should not execute the functions
through the select statement. If you are trying to execute the
functions through the select statments, which is contains out/inout
parameters, it will return a null value.
By
using the function we cannot do any DML operation.
Functions
are compiled and executed at run time
Package:
A package
is a schema object that groups logically related PL/SQL types, items,
and subprograms. Packages usually have two parts, a specification and
a body, although sometimes the body is unnecessary. The specification
is the interface to your applications; it declares the types,
variables, constants, exceptions, cursors, and subprograms available
for use. The body fully defines cursors and
subprograms, and so implements the specification.
Package
specification is a mandatory part.
Advantages
of Package:
Information
Hiding
Better
Performance
Global
Variable Declaration
Function
Overloading
Different parameters and different datatypes.
Program:
c
Global variable declaration. We
can call this variable outside package also.
reate or replace package firstpack1
is
cnt number
:=0005413510;
procedure
prc1;
procedure prc2;
function fun1 return number;
end;
c
If
we are declaring the subprograms inside the package body, we can
call this subprogram within the package only, could not call outside
of package. For information hiding purpose we are declaring
subprograms and writing code within the package.
reate or replace package body firstpack1
is
procedure
prc_hide; //
procedure prc1 is
begin
dbms_output.put_line('This is my
first procedure');
end;
Function
Overloading. Because procedure prc1 we are using twice with
different parameters.
procedure
prc1(a out number) is
begin
dbms_output.put_line('This is my first procedure and A
value is : ' || a);
end;
procedure prc2
is
begin
dbms_output.put_line('2nd procedure');
firstpack1.prc_hide();
end;
function fun1 return number
is
begin
return 10;
end;
procedure prc_hide is
begin
dbms_output.put_line('information hiding');
end;
end;
Better
Performance:
If
10 standalone procedures/functions are there in the DB, whenever
while executing those subprograms, it will hit the disk directly and
load only one subprogram at a time. Even though, if we called a
procedure or a function in another subprogram, it will again hit the
disk directly and load only one subprogram at a time. So performance
will get reduced.
But
if we have written packages with the subprograms, it will load whole
packages (All the subprograms, whichever we written within the
package) into buffer. So while executing the subprogram, it fetch the
necessary information from the buffer not from the disk. So
performance will get increased.
Triggers:
Like a stored
procedure, a trigger is a named PL/SQL unit that is stored in the
database and can be invoked repeatedly. Unlike a stored procedure,
you can enable and disable a trigger, but you cannot explicitly
invoke it. While a trigger is enabled, the database automatically
invokes it—that is, the trigger fires.
A trigger is a pl/sql
block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table. A trigger is
triggered automatically when an associated DML statement is executed.
Reasons to Use
Triggers
Triggers let you
customize your database management system. For example, you can use
triggers to:
•Automatically generate virtual column
values
•Log events
•Gather statistics on table
access
•Modify table data when DML statements are issued against
views
•Enforce referential integrity when child and parent
tables are on different nodes of a distributed database
•Publish
information about database events, user events, and SQL statements to
subscribing applications
•Prevent DML operations on a table
after regular business hours
•Prevent invalid
transactions
•Enforce complex business or referential integrity
rules that you cannot define with constraints
Caution:
Triggers are not
reliable security mechanisms, because they are programmatic and easy
to disable. For high-assurance security, use Oracle Database Vault,
Order in Which
Triggers Fire
If two or more
triggers with different timing points are defined for the same
statement on the same table, then they fire in this order:
1.All BEFORE
STATEMENT triggers
2.All BEFORE EACH ROW triggers
3.All AFTER
EACH ROW triggers
4.All AFTER STATEMENT triggers
Note: If you
are creating two or more triggers with the same timing point, and
the order in which they fire is important, then you can control
their firing order using the FOLLOWS and PRECEDES
Trigger Types:
DML Triggers
Insert, update and delete
DDL
Triggers Alter or create
System
Triggers initiating the job in
oracle
DML
Trigger:
If
the trigger is created on a table or view, then the triggering event
is composed of DML statements, and the trigger is called a DML
trigger.
DML
Trigger types:
Statement
Level
Row
Level.
|
Statement
Level
|
Row
Level
|
|
Before
|
After
|
Before
|
After
|
1.
insert
|
1.
insert
|
1.
insert
|
1.
insert
|
2.
update
|
2.
update
|
2.
update
|
2.
update
|
3.
delete
|
3.
delete
|
3.
delete
|
3.
delete
|
Statement
Level:
� Statement-level
triggers execute once for each transaction.
For example, if a single transaction inserted 500 rows into
the Customer table, then a statement-level trigger on that table
would only be executed once.
�
Statement-level triggers therefore are not often used for
data-related activities; they are normally used to enforce
additional security measures on the types of transactions that may
be performed on a table.
�
Statement-level triggers are the default type of triggers created
and are identified by omitting the FOR EACH
ROW clause in the CREATE TRIGGER command.
It
will execute the block only once.
We
could not track the history of data by using the statement level
trigger.
It
is the default trigger.
Example:
CREATE OR REPLACE
TRIGGER T3
BEFORE INSERT ON mouse
BEGIN
DBMS_OUTPUT.PUT_LINE('New employees are about to be added');
END;
Whenever values are
inserting in mouse tables, the dbms messages will printed.
Row
Level:
Row-level
triggers for data-related
activities
� Row-level
triggers execute once for each row in a
transaction.
�
Row-level triggers are the most common type of triggers; they are
often used in data auditing applications.
�
Row-level trigger is identified by the FOR EACH ROW
clause in the CREATE TRIGGER command.
It
will execute the statement for each row
We
can track the old data and new data by using the keyword old
and
new. Example (new.empid, new.empname, old.sal)
Example:
CREATE
or REPLACE TRIGGER T2
BEFORE Update OF unit_price
ON tempp2
FOR EACH ROW
BEGIN
INSERT INTO tempp1 VALUES
(:old.product_id, :old.product_name, :old.supplier_name,
:old.unit_price);
END;
If
you are updating any data in the tempp2
tables, the same updated values will inserted in the tempp1
tables.
Advantages:
Replication
purpose (Reflecting the data in the same table)
We
can track the history of data by using old
and new
keyword.
Data
validation (By using constraints)
Mutating
tables:
A
mutating table is a table that is currently being modified by an
update, delete, or insert statement(possibly by the effects of a
DELETE CASCADE constraint). (A view being modified by an INSTEAD OF
trigger is not considered to be mutating.) . When a trigger tries to
reference a table that is in state of flux (being changed), it is
considered "mutating", and raises an error since Oracle
should never return inconsistent data.
Mutating
errors:
A
Row level trigger throws an error.
This
error can occur is if the trigger has statements to change the
primary, foreign or unique key columns of the table off which it
fires.
Caution:
Oracle
Database does not enforce the mutating-table restriction for a
trigger that accesses remote nodes, because the database does not
support declarative referential constraints between tables on
different nodes of a distributed database.
Similarly,
the database does not enforce the mutating-table restriction for
tables in the same database that are connected by loop-back database
links. A loop-back database link makes a local table appear remote
by defining an Oracle Net path back to the database that contains
the link.
Example
Trigger Causes Mutating-Table Error
--
Create log table
DROP
TABLE log;
CREATE TABLE log ( emp_id NUMBER(6),
l_name
VARCHAR2(25),
f_name VARCHAR2(20));
-- Create trigger
that updates log and then reads employees
CREATE
OR REPLACE TRIGGER log_deletions
AFTER DELETE ON employees
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
INSERT INTO
log VALUES ( :OLD.employee_id, :OLD.last_name,:OLD.first_name);
SELECT
COUNT(*) INTO n FROM employees;
DBMS_OUTPUT.PUT_LINE('There are
now ' || n || ' employees.');
END;
--
Issue triggering statement:
DELETE
FROM employees WHERE employee_id = 197;
Result:
DELETE
FROM employees WHERE employee_id = 197
*
ERROR at
line 1:
ORA-04091: table HR.EMPLOYEES is mutating,
trigger/function might not see it
ORA-06512: at
"HR.LOG_DELETIONS", line 10
ORA-04088: error during
execution of trigger 'HR.LOG_DELETIONS'
DDL triggers:
If the trigger is
created on a schema or the database, then the triggering event is
composed of either DDL or database operation statements, and the
trigger is called a system trigger.
Autonomous
transaction
An
autonomous
transaction
is an independent transaction that is initiated by another
transaction, and executes without interfering with the parent
transaction. When an autonomous transaction is called, the
originating transaction gets suspended. Control is returned when the
autonomous transaction does a COMMIT
or ROLLBACK.
A
trigger
or procedure
can be marked as autonomous by declaring it as PRAGMA
AUTONOMOUS_TRANSACTION;.
You may need to increase the TRANSACTIONS
parameter to allow for the extra concurrent transactions.
Example
SQL>
CREATE OR REPLACE TRIGGER tab1_trig
2
AFTER insert ON tab1
3
DECLARE
4
PRAGMA
AUTONOMOUS_TRANSACTION;
5
BEGIN
6
INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
7
COMMIT; -- only allowed in autonomous triggers
8
END;
9
/
Instead of
triggers:
When the triggers
needs to create on Views then instead of triggers should be used.
An INSTEAD OF DML
trigger is a DML trigger created on a noneditioning view, or on a
nested table column of a noneditioning view. The database fires the
INSTEAD OF trigger instead of running the triggering DML statement.
An INSTEAD OF trigger cannot be conditional.
An INSTEAD OF
trigger is the only way to update a view that is not inherently
updatable.
An INSTEAD OF
trigger is always a row-level trigger. An INSTEAD OF trigger can
read OLD and NEW values, but cannot change them.
Examples:
CREATE
OR REPLACE TRIGGER Dept_emplist_tr
INSTEAD OF INSERT ON
Dept_view
REFERENCING NEW AS Employee
PARENT AS
Department
FOR EACH ROW
BEGIN--
The insert on the nested table is translated to an insert on the
base table: INSERT INTO Emp_tab
VALUES (
:Employee.Empno,
:Employee.Empname,:Employee.Salary, :Department.Deptno);
END;
Compound DML
Triggers:
A compound DML
trigger created on a table or editioning view can fire at multiple
timing points. Each timing point section has its own executable part
and optional exception-handling part, but all of these parts can
access a common PL/SQL state. The common state is established when
the triggering statement starts and is destroyed when the triggering
statement completes, even when the triggering statement causes an
error.
A compound DML
trigger created on a noneditioning view is not really compound,
because it has only one timing point section.
A compound trigger
can be conditional, but not autonomous.
Two common uses of
compound triggers are:
• To accumulate
rows destined for a second table so that you can periodically
bulk-insert them
• To avoid the
mutating-table error (ORA-04091)
Compound DML
Trigger Structure
The optional
declarative part of a compound trigger declares variables and
subprograms that all of its timing-point sections can use. When the
trigger fires, the declarative part runs before any timing-point
sections run. The variables and subprograms exist for the duration
of the triggering statement.
A compound DML
trigger created on a noneditioning view is not really compound,
because it has only one timing point section. The syntax for
creating the simplest compound DML trigger on a noneditioning view
is:
CREATE trigger FOR dml_event_clause ON view
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS BEGIN
statement;
END INSTEAD OF EACH ROW;
A compound DML
trigger created on a table or editioning view has at least one
timing-point section in Table 9-2. If the trigger has multiple
timing-point sections, they can be in any order, but no timing-point
section can be repeated. If a timing-point section is absent, then
nothing happens at its timing point.
Table 9-2
Compound Trigger Timing-Point Sections
|
|
|
Before the triggering statement runs
|
BEFORE
STATEMENT
|
After the triggering statement runs
|
AFTER
STATEMENT
|
Before each row that the triggering statement affects
|
BEFORE
EACH ROW
|
After each row that the triggering statement affects
|
AFTER EACH
ROW
|
A
compound DML trigger does not have an initialization section, but
the BEFORE STATEMENT section, which runs before any other
timing-point section, can do any necessary initializations.
If a compound DML
trigger has neither a BEFORE STATEMENT section nor an AFTER
STATEMENT section, and its triggering statement affects no rows,
then the trigger never fires.
Compound DML
Trigger Restrictions
•OLD,
NEW, and PARENT cannot appear in the declarative part, the BEFORE
STATEMENT section, or the AFTER STATEMENT section.
•Only
the BEFORE EACH ROW section can change the value of NEW.
•A
timing-point section cannot handle exceptions raised in another
timing-point section.
•If
a timing-point section includes a GOTO statement, the target of the
GOTO statement must be in the same timing-point
section.
Performance Benefit of
Compound DML Triggers
A compound DML
trigger has a performance benefit when the triggering statement
affects many rows.
For example,
suppose that this statement triggers a compound DML trigger that has
all four timing-point sections.
INSERT INTO Target
SELECT c1, c2, c3
FROM Source
WHERE Source.c1 > 0
Although the BEFORE
EACH ROW and AFTER EACH ROW sections of the trigger run for each row
of Source whose column c1 is greater than zero, the BEFORE STATEMENT
section runs only before the INSERT statement runs and the AFTER
STATEMENT section runs only after the INSERT statement runs.
A
compound DML trigger has a greater performance benefit when it uses
bulk SQL
A compound DML trigger is useful for accumulating
rows destined for a second table so that you can periodically
bulk-insert them. To get the performance benefit from the compound
trigger, you must specify BULK COLLECT INTO in the FORALL statement
(otherwise, the FORALL statement does a single-row DML operation
multiple times).
Using Compound DML
Triggers to Avoid Mutating-Table Error
A compound DML
trigger is useful for avoiding the mutating-table error (ORA-04091).
Scenario:
A business rule states that an employee's salary increase must not
exceed 10% of the average salary for the employee's department. This
rule must be enforced by a trigger.
Solution:
Define a compound trigger on updates of the table hr.employees.
Triggers for
Ensuring Referential Integrity
You can use
triggers and constraints to maintain referential integrity between
parent and child tables,
|
|
Constraint to Declare on
Table
|
Triggers to Create on Table
|
Parent
|
PRIMARY KEY
or UNIQUE
|
One or more triggers that ensure that when PRIMARY
KEY or UNIQUE
values are updated or deleted, the desired action (RESTRICT,
CASCADE, or SET
NULL) occurs on corresponding
FOREIGN KEY
values.
No action is required for inserts into
the parent table, because no dependent foreign keys exist.
|
Child
|
FOREIGN KEY,
if parent and child are in the same database. (The database does
not support declarative referential constraints between tables
on different nodes of a distributed database.)
Disable this foreign key constraint to prevent the
corresponding PRIMARY KEY
or UNIQUE constraint from being
dropped (except explicitly with the CASCADE
option).
|
One trigger that ensures that values inserted or updated in
the FOREIGN KEY
correspond to PRIMARY KEY
or UNIQUE values in the parent
table.
|
System Triggers
A system trigger is
created on either a schema or the database. Its triggering event is
composed of either DDL statements (listed in "ddl_event")
or database operation statements (listed in "database_event").
A system trigger
fires at exactly one of these timing points:
•Before the
triggering statement runs
(The trigger is called a BEFORE
statement trigger or statement-level BEFORE trigger.)
•After
the triggering statement runs
(The trigger is called a AFTER
statement trigger or statement-level AFTER trigger.)
•Instead
of the triggering CREATE statement
(The trigger is called an
INSTEAD OF CREATE trigger.)
Types:
•SCHEMA
Triggers
•DATABASE Triggers
•INSTEAD OF CREATE Triggers
SCHEMA Triggers
A SCHEMA trigger is
created on a schema and fires whenever the user who owns it is the
current user and initiates the triggering event.
Example:
BEFORE Statement Trigger on Sample Schema HR
CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
DATABASE
Triggers:
A DATABASE trigger
is created on the database and fires whenever any database user
initiates the triggering event.
Note:
An AFTER
SERVERERROR trigger fires only if Oracle relational database
management system (RDBMS) determines that it is safe to fire error
triggers.
Example Triggering monitoring logons.
CREATE OR REPLACE TRIGGER check_user
AFTER LOGON ON DATABASE
BEGIN
check_user;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
/
INSTEAD OF
CREATE Triggers:
An INSTEAD OF
CREATE trigger is a SCHEMA trigger whose triggering event is a
CREATE statement. The database fires the trigger instead of
executing its triggering statement.
Example INSTEAD OF
CREATE Trigger on Schema
CREATE OR REPLACE TRIGGER t
INSTEAD OF CREATE ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';
END;
/
Trigger Design
Guidelines
For
example, use a trigger to ensure that whenever anyone updates a
table, its log file is updated.
For
example, do not create a trigger to reject invalid data if you can do
the same with constraints.
For
example, do not assign a value to a global package variable in a row
trigger if the current value of the variable depends on the row being
processed by the row trigger. If a trigger updates global package
variables, initialize those variables in a BEFORE
statement trigger.
An
AFTER
row trigger fires when the triggering statement results in ORA-2292.
Note:
AFTER
row triggers are slightly more efficient than BEFORE
row triggers. With BEFORE
row triggers, affected data blocks are read first for the trigger and
then for the triggering statement. With AFTER
row triggers, affected data blocks are read only for the trigger.
If the triggering statement of a
BEFORE
statement trigger is an UPDATE
or DELETE
statement that conflicts with an UPDATE
statement that is running, then the database does a transparent
ROLLBACK
to SAVEPOINT
and restarts the triggering statement. The database can do this many
times before the triggering statement completes successfully. Each
time the database restarts the triggering statement, the trigger
fires. The ROLLBACK
to SAVEPOINT
does not undo changes to package variables that the trigger
references. To detect this situation, include a counter variable in
the package.
Do
not create recursive triggers.
For
example, do not create an AFTER
UPDATE
trigger that issues an UPDATE
statement on the table on which the trigger is defined. The trigger
fires recursively until it runs out of memory.
If
you create a trigger that includes a statement that accesses a
remote database, then put the exception handler for that statement
in a stored subprogram and invoke the subprogram from the trigger..
Use
DATABASE
triggers judiciously. They fire every time any database user
initiates a triggering event.
If
a trigger runs the following statement, the statement returns the
owner of the trigger, not the user who is updating the table:
SELECT
Username FROM USER_USERS;
A
trigger is committed, implicitly, after the CREATE
TRIGGER
statement that creates it succeeds. Therefore, the following
statement cannot fire the trigger that it creates:
CREATE
OR REPLACE TRIGGER my_trigger
AFTER CREATE ON DATABASE
BEGIN
NULL;
END;
/
Each
trigger sees the changes made by the previously fired triggers. Each
trigger can see OLD
and NEW
values.
Trigger
Restrictions
In
addition to the restrictions that apply to all PL/SQL units triggers
have these restrictions:
Trigger
Size Restriction
Trigger
LONG and LONG RAW Data Type Restrictions
Mutating-Table
Restriction
Only
an autonomous trigger can run TCL or DDL statements.
For
information about autonomous triggers.
For
more information about subprograms invoked by triggers.
If
the logic for your trigger requires much more than 60 lines of PL/SQL
source text, then put most of the source text in a stored subprogram
and invoke the subprogram from the trigger. For information about
subprograms invoked by triggers.
Trigger
LONG and LONG RAW Data Type Restrictions
Note:
Oracle supports the LONG
and LONG
RAW
data types only for backward compatibility with existing
applications.
In
addition to the restrictions that apply to all PL/SQL units, triggers
have these restrictions:
A
trigger cannot declare a variable of the LONG
or LONG
RAW
data type.
A
SQL statement in a trigger can reference a LONG
or LONG
RAW
column only if the column data can be converted to the data type
CHAR
or VARCHAR2.
A trigger cannot use the
correlation name NEW
or PARENT
with a LONG
or LONG
RAW
column.
Collection:
A
collection is an ordered group of elements all of the same type. It
is a general concept that encompasses lists, arrays, and other
familiar data types.
Each
element has a unique subscript that determines its position in the
collection.
The data stored in
the collection may be accessed more rapidly by the database than if
you were to use two tables instead.
Advantages:
Types of
collections:
Associative arrays
(formerly known as index-by tables)
Varrays
Nested tables
List
of Collection methods:
EXISTS(row) returns
TRUE if the row specified exists.
COUNT
returns the number of rows.
FIRST
returns the row number of the first populated row.
LAST
returns the row number of the last populated row.
PRIOR(row)
returns the row number of the last row populated before the row
specified.
NEXT(row)
returns the row number of the next row populated after the row
specified.
DELETE
removes all rows.
DELETE(row)
removes the specified row.
DELETE(start_row,end_row)
removes all rows between and including the start_row and end_row.
TRIM
removes the last row.
TRIM(n)
removes the last n rows.
EXTEND
adds one row.
EXTEND(n)
adds n rows.
EXTEND(n,m) adds n
copies of row m.
Index-by-tables:
Index-by-tables
is the integer and it is contain positive and negative values.
Program:declare
Type
typ1 is table of varchar2(25) index by binary_integer;
name1
typ1;
cursor c1 is select name from mouse;
begin
open
c1;
loop
fetch c1 bulk collect into name1 limit
5;
if(name1.count>0)
then
dbms_output.put_line(name1.first);
dbms_output.put_line(name1.last);
dbms_output.put_line(name1.count);
for
i in name1.first..name1.last
loop
dbms_output.put_line('Name
is : ' || name1(i));
end loop;
end if;
exit when
c1%notfound;
end loop;
end;
Nested Tables:
A nested table is a
table that is embedded within another table.
You
can insert, update, and delete individual elements in a nested
table.
Because
you can modify individual elements in a nested table, this makes
them more flexible than a varray.
The
elements for nested tables are stored in separate tables.
Program:
declare
Type typ1
is table of varchar2(25);
name1 typ1;
cursor c1 is select name
from mouse;
begin
open c1;
loop
fetch c1 bulk collect
into name1 limit 5;
if(name1.count>0)
then
dbms_output.put_line(name1.first);
dbms_output.put_line(name1.last);
dbms_output.put_line(name1.count);
for
i in name1.first..name1.last
loop
dbms_output.put_line('Name
is : ' || name1(i));
end loop;
end if;
exit when
c1%notfound;
end loop;
end;
Varrays:
You can use a
varray to store an ordered set of elements having an index
associated with it.
The
elements in a varray are of the same type.
A
varray has a maximum size that you set when creating it.
Elements
in a varray can only be modified as a whole, not individually.
The
elements stored in a varray are stored with the table when the size
of the varray is 4KB or less, otherwise the varray is stored outside
of the table.
When
a varray is stored with the table, accessing its elements is faster
than accessing elements in a nested table.
Program:
declare
Type
typ1 is VARRAY(5) of varchar2(25);
name1 typ1;
cursor c1 is
select name from mouse;
begin
open c1;
loop
fetch c1 bulk
collect into name1 limit 5;
if(name1.count>0)
then
dbms_output.put_line(name1.first);
dbms_output.put_line(name1.last);
dbms_output.put_line(name1.count);
for
i in name1.first..name1.last
loop
dbms_output.put_line('Name
is : ' || name1(i));
end loop;
end if;
exit when
c1%notfound;
end loop;
end;
Creating
Collection Data type:
CREATE
Or Replace TYPE Address AS VARRAY(2) OF VARCHAR2(100);
Assigning
in Table:
Create
table table_name (Addressdata Address);
Insert
the value:
Insert
into table_name(Addressdata) values (Address(‘102, Nehru nagar’,
‘Chennai-118’);
Avoiding
Collection Exceptions
In
most cases, if you reference a nonexistent collection element, PL/SQL
raises a predefined exception. Consider the following example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
nums NumList; -- atomically
null
BEGIN
/* Assume execution continues despite the raised
exceptions. */
nums(1) := 1; -- raises
COLLECTION_IS_NULL (1)
nums := NumList(1,2); --
initialize table
nums(NULL) := 3 -- raises VALUE_ERROR
(2)
nums(0) := 3; -- raises
SUBSCRIPT_OUTSIDE_LIMIT (3)
nums(3) := 3; -- raises
SUBSCRIPT_BEYOND_COUNT (4)
nums.DELETE(1); -- delete element
1
IF nums(1) = 1 THEN ... -- raises NO_DATA_FOUND
(5)
The
following list shows when a given exception is raised:
-
|
|
|
COLLECTION_IS_NULL
|
you try to operate on an atomically null
collection.
|
NO_DATA_FOUND
|
a subscript designates an element that
was deleted, or a nonexistent element of an associative array.
|
SUBSCRIPT_BEYOND_COUNT
|
a subscript exceeds the number of
elements in a collection.
|
SUBSCRIPT_OUTSIDE_LIMIT
|
a subscript is outside the allowed range.
|
VALUE_ERROR
|
a subscript is null or not convertible to
the key type. This exception might occur if the key is defined
as a PLS_INTEGER range, and the
subscript is outside this range.
|
Bulk
Binds :
The
assigning of values to PL/SQL variables in SQL statements is called
binding.
PL/SQL binding operations fall into three categories:
in-bind
When a PL/SQL
variable or host variable is stored in the database by an INSERT or
UPDATE statement.
out-bind
When a database
value is assigned to a PL/SQL variable or a host variable by the
RETURNING clause of an INSERT, UPDATE, or DELETE statement.
define
When a database value is assigned to a PL/SQL variable or a host
variable by a SELECT or FETCH statement.
A
DML statement can transfer all the elements of a collection in a
single operation, a process known as bulk
binding. If the
collection has 20 elements, bulk binding lets you perform the
equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a
single operation. This technique improves performance by minimizing
the number of context switches between the PL/SQL and SQL engines.
With bulk binds, entire collections, not just individual elements,
are passed back and forth.
To
do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose
the SQL statement within a PL/SQL FORALL statement.
To
do bulk binds with SELECT statements, you include the BULK COLLECT
clause in the SELECT statement instead of using INTO.
Example:
Performing a Bulk Bind with DELETE
The
following DELETE statement is sent to the SQL engine just once, even
though it performs three DELETE operations:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList :=
NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i
IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno =
depts(i);
END;
Handling
FORALL Exceptions with the %BULK_EXCEPTIONS Attribute
The
syntax follows:
FORALL
index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt |
update_stmt | delete_stmt}
All
exceptions raised during the execution are saved in the new cursor
attribute %BULK_EXCEPTIONS, which stores a collection of records.
Each record has two fields. The first field,
%BULK_EXCEPTIONS(i).ERROR_INDEX, holds the "iteration" of
the FORALL statement during which the exception was raised. The
second field, %BULK_EXCEPTIONS(i).ERROR_CODE, holds the
corresponding Oracle error code.
The
values stored by %BULK_EXCEPTIONS always refer to the most recently
executed FORALL statement. The number of exceptions is saved in the
count attribute of %BULK_EXCEPTIONS, that is,
%BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.
If
you omit the keywords SAVE EXCEPTIONS, execution of the FORALL
statement stops when an exception is raised. In that case,
SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS
contains just one record. If no exception is raised during
execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.
The following
example shows how useful the cursor attribute %BULK_EXCEPTIONS can
be:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList :=
NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors,
-24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE
EXCEPTIONS
DELETE FROM emp WHERE sal >
500000/num_tab(i);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i
IN 1..errors LOOP
dbms_output.put_line('Error ' || i || '
occurred during '||
'iteration ' ||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
Output:
Number of errors is
3
Error 1 occurred during iteration 2
Oracle error is
ORA-01476: divisor is equal to zero
Error 2 occurred during
iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is
ORA-01476: divisor is equal to zero
Restrictions
on BULK COLLECT
The
following restrictions apply to the BULK
COLLECT
clause:
You cannot bulk
collect into an associative array that has a string type for the
key.
You can use the
BULK
COLLECT
clause only in server-side programs (not in client-side programs).
Otherwise, you get the error this feature is not supported in
client-side programs.
All targets in a
BULK
COLLECT
INTO
clause must be collections, as the following example shows:
DECLARE
TYPE
NameList IS TABLE OF emp.ename%TYPE;
names NameList;
salary emp.sal%TYPE;
BEGIN
SELECT ename, sal BULK COLLECT
INTO names, salary -- illegal target
FROM emp WHERE ROWNUM
< 50;
…
END;
Composite targets
(such as objects) cannot be used in the RETURNING
INTO
clause. Otherwise, you get the error unsupported feature with
RETURNING
clause.
When implicit
datatype conversions are needed, multiple composite targets cannot
be used in the BULK
COLLECT
INTO
clause.
When an implicit
datatype conversion is needed, a collection of a composite target
(such as a collection of objects) cannot be used in the BULK
COLLECT
INTO
clause.
Materialized
Views:
A
Materialized View is effectively a database table that contains the
results of a query. The power of materialized views comes from the
fact that, once created, Oracle can automatically synchronize a
materialized view's data with its source information as required with
little or no programming effort.
Also, It would be getting
the data from remote database to local db. It is called as
Snapshots.
Materialized views can be used for many purposes,
including:
1. Denormalization
2. Validation
3. Data
Warehousing
4. Replication.
Views
vs Materialized Views
1. Views
and Materialized views store the same data.
2. The rowid would
be different for materialized view.
3. Once the data has been
updated in the source table, then the data would be reflected
immediately in views. But, Materialized view does not. It should be
updated in the regular intervals or have to refresh the MV to synch
the data manually.
Different
types of refresh method in Materialized view:
1.
COMPLETE REFRESH
2. FAST REFRESH
3. FORCE REFRESH
4.
NEVER REFRESH
COMPLETE
REFRESH:
The
row id would be change everytime when the complete refresh done on
the materialized view data. Everytime changing the rowid would be the
expensive operation. So, if it is having the large data then the
complete refresh would not be suitable.
Syntax:
create
materialized view mv
REFRESH COMPLETE
as select * from
t;
execution:
execute
DBMS_MVIEW.REFRESH( LIST => 'MV', METHOD => 'C'
);
Materialized
View logs:
As
mentioned earlier, complete refreshes of materialized views can be
expensive operations. Fortunately there is a way to refresh only the
changed rows in a materialized view's base table. This is called fast
refreshing. Before a materialized view can perform a fast refresh
however it needs a mechanism to capture any changes made to its base
table. This mechanism is called a Materialized View Log.
create
materialized view log on emp;
It is not require to give any
name to materialized view logs. Because, each table can have only one
materialized view logs.
After you done the any modification on
the source table, you could execute the below query to find the
number of rows are modified.
select * from MLOG$_emp;
The
MV logs can be created by using WITH Column List , WITH SEQUENCE ,
WITH ROWID and WITH PRIMARY KEY.
FAST
REFRESH :
create
materialized view mv
REFRESH FAST
as select * from
emp;
Execution:
execute
dbms_mview.refresh( list => 'MV', method => 'F' );
During
the fast refresh, rowid never been changed after the update in the
source table and fast refresh execution done.
Purging
Materialized View Logs :
Materialized
view logs automatically purged after the any kinds of refresh
done.
If a materialized view log needs to be purged manually
for some reason a procedure called DBMS_MVEW.PURGE_LOG can be used.
Syntax:
execute
DBMS_MVIEW.PURGE_LOG( master => 'emp', num => 9999, flag =>
'delete' ) ;
Once a materialized view log has been purged any
materialized views dependent on the deleted rows cannot be fast
refreshed. Attempting a fast refresh will raise an ORA-06512 error.
Such materialized views will need to be refreshed completely.
REFRESH FAST
Categories
There
are three ways to categorize a materialized view's ability to be fast
refreshed.
1.It can never be fast refreshed.
Materialized
logs should be created 1st before create materialized view with FAST
refresh. else, it will throw an error.
2.It can always be fast
refreshed.
It is a normal one. 1st materialized view logs
would be created on the source table, then materialized view would be
created.
3.It can be fast refreshed after certain kinds of
changes to the base table but not others
Materialized view
would be created for insert only and not for the update or deletion.
If you done so, it will throw an error. In such a case you have to
complete refresh.
create materialized view log on t2
with
primary key, rowid, sequence ( t_key, amt )
including new
values;
create materialized view mv
REFRESH FAST
as
select t_key, max( amt ) amt_max from t2
group by
t_key;
Restrictions
on Fast Refresh:
In
general materialized views cannot be fast refreshed if the base
tables do not have materialized view logs or the defining query:
•contains an analytic function|
•contains
non-repeating expressions like SYSDATE or ROWNUM
•contains RAW
or LONG RAW data types
•contains a subquery in the SELECT
clause
•contains a MODEL clause
•contains a HAVING
clause
•contains nested queries with ANY, ALL, or NOT
EXISTS
•contains a CONNECT BY clause
•references remote
tables in different databases
•references remote tables in a
single database and defaults to the ON COMMIT refresh
mode
•references other materialized views which are not join or
aggregate materialized views.
FORCE
REFRESH :
The
REFRESH FORCE method performs a FAST refresh if possible, otherwise
it performs a COMPLETE refresh. The force refresh would be good for
many scenarios to decide the refresh type oracle instead of
us.
create materialized view log on t2
with primary key,
rowid, sequence ( t_key, amt )
including new values;
create
materialized view mv
REFRESH FORCE as
select t_key, max(
amt ) amt_max from t2 group by t_key;
execute
dbms_mview.refresh( list => 'MV' );
NEVER
REFRESH
For
some reason we need to prevent refresh operations of any sort, FAST
or COMPLETE, on our materialized views we can use the NEVER REFRESH
method.
create materialized view mv
NEVER REFRESH
as select * from t;
When we attempt to refresh the data then
we receive ORA-06512: at "SYS.DBMS_SNAPSHOT" error.
ON
COMMIT:
In
some situations it would be convenient to have Oracle refresh a
materialized view automatically whenever changes to the base table
are committed. This is possible using the ON COMMIT refresh mode.
create materialized view log on t ;
create
materialized view mv
REFRESH FAST ON COMMIT
as select *
from t;
Restrictions
Materialized views can only refresh ON COMMIT in certain
situations.
1. The materialized view cannot contain object
types or Oracle-supplied types.
2. The base tables will never have
any distributed transactions applied to them.
The first case
produces an error during the CREATE MATERIALIZED VIEW command.
--
this materialized view is not fast refreshable
-- because the
materialized view contains an Oracle-supplied type
create
materialized view mv2
REFRESH FAST ON COMMIT
as select key,
val, sys_xmlgen( val ) as val_xml from t;
as select key,
val, sys_xmlgen( val ) as val_xml from t
*
ERROR at line 3:
ORA-12054:
cannot set the ON COMMIT refresh attribute for the materialized
view
The second case generates an error when a distributed
transaction is attempted on the base table. In the following example
materialized view MV (created at the top of this page) was created
with REFRESH FAST. Attempting a distributed transaction on its base
table, T, will therefore raise an error.
insert into t select
key+10, val from T@REMOTE ;
commit;
commit
*
ERROR at
line 1:
ORA-02050: transaction 5.21.5632 rolled back, some remote
DBs may be in-doubt
ORA-02051: another session in same transaction
failed
QUERY_REWRITE:
It
is a performance tuning method, when the data is fetching from
materialize view.
It
is a query optimization technique that transforms a user query
written in terms of tables and views, to execute faster by fetching
data from materialized views. It is completely transparent to the end
user, requiring no intervention or hints in the SQL application
because the Oracle Database will automatically rewrite any
appropriate SQL application to use the materialized views.
Syntax:
CREATE
MATERIALIZED VIEW some_cust_sales_mv BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS SELECT query...
PARTITION:
Partitioning
addresses the key problem of supporting very large tables and indexes
by allowing you to decompose them into smaller and more manageable
pieces called partitions. Once partitions are defined, SQL statements
can access and manipulate the partitions rather than entire tables or
indexes. Partitions are especially useful in data warehouse
applications, which commonly store and analyze large amounts of
historical data.
Advantages:
1.
Partitions can be stored in different tablespaces.
2. Partitions
can be added/ removed while users are working. DBAs can perform
maintenance without having to bring down an entire table (import/
export/ load/ etc).
Partition
information can be found in this table: user_tab_partitions
Disadvantages:
Performance
Disadvantages
Indexes
can pose a serious problem if they fail. If an index goes down or is
damaged, the underlying tables can be damaged beyond repair, making
them unusable and unrecoverable. The only solution is to revert to
backup -- if you have one -- or to rebuild the index. In a partition
context, this can take a very long time.
Manageability
Disadvantages
Partitions
are much harder to manage than standard tables devoid of them. This
is because the partition aspects have to be identified and managed as
part of an operation, such as the use of "truncate." It's
not enough to use the simple truncate command; the correct command
would be "alter table truncate partition." Therefore,
partitions are harder to manage.
Partitioning
types:
1.
Range
partitioning
2. Hash partitioning
3. Composite partitioning
4. List partitioning
5. Interval partitioning
6. System
partitioning
7. Reference partitioning
Range
partitioning:
Range
partitioning is a partitioning technique where ranges of data is
stored separately in different sub-tables.
MAXVALUE is
provided as a catch-all for values that exceed all ranges specified.
Note that Oracle sorts NULLs greater than all other values, except
MAXVALUE.
Example:
CREATE TABLE emp ( empno NUMBER(4),
ename VARCHAR2(30), sal NUMBER)
PARTITION BY RANGE(empno) (
partition e1 values less than (1000) tablespace ts1,
partition e2 values less than (2000) tablespace ts2,
partition e3 values less than (MAXVALUE) tablespace ts3);
by
varchar2:
PARTITION
BY RANGE ( name ) ( PARTITION p1 VALUES LESS THAN ('L')
TABLESPACE ts1,
PARTITION p2 VALUES LESS THAN (MAXVALUE)
TABLESPACE ts2 )
by
Time based:
(PARTITION
t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01',
'YYYY-MM-DD')),
PARTITION t1p3 VALUES LESS THAN
(TO_DATE('2008-01-01', 'YYYY-MM-DD')),
PARTITION t1p4 VALUES
LESS THAN (MAXVALUE) );
Hash
partitioning:
Hash
partitioning is a partitioning technique where a hash key is used to
distribute rows evenly across the different partitions (sub-tables).
This is typically used where ranges aren't appropriate, i.e. employee
number, productID, etc.
example:
create
table emp2 ( empno number(4), ename varchar2(30), sal number)
partition by hash(empno) ( partition e1 tablespace emp1,
partition e2 tablespace emp2,
partition e3 tablespace emp3,
partition e4 tablespace emp4); Composite
partitioning:
Composite
partitioning is a partitioning technique that combines some of the
other partitioning methods. The table is initially partitioned by the
first data distribution method and then each partition is
sub-partitioned by the second data distribution method
Composite
partitioning methods:
1.
Range-hash partitioning was introduced in Oracle 8i
2. Range-list
partitioning was introduced in Oracle 9i
3. Range-range
partitioning was introduced in Oracle 11g
4. List-range
partitioning was introduced in Oracle 11g
5. List-hash
partitioning was introduced in Oracle 11g
6. List-list
partitioning was introduced in Oracle 11g
7. Interval-range
partitioning was introduced in Oracle 11g
8. Interval-list
partitioning was introduced in Oracle 11g
9. Interval-hash
partitioning was introduced in Oracle 11g
10. Hash-hash
partitioning was introduced in Oracle 11gR2
Example:
CREATE
TABLE orders( ord# NUMBER, orderdate DATE, prod# NUMBER,
quantity NUMBER)
PARTITION BY RANGE(orderdate)
SUBPARTITION
BY HASH(prod#) SUBPARTITIONS 4 -- STORE IN(ts1, ts2, ts3, ts4)
(
PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-2009',
'DD-MON-YYYY')),
PARTITION q2 VALUES LESS THAN
(TO_DATE('01-JUL-2009', 'DD-MON-YYYY')),
PARTITION q3 VALUES
LESS THAN (TO_DATE('01-OCT-2009', 'DD-MON-YYYY')),
PARTITION q4
VALUES LESS THAN (MAXVALUE));
List
partitioning:
List
partitioning is a partitioning technique where you specify a list of
discrete values for the partitioning key in the description for each
partition.
Example:
CREATE
TABLE myemp_work ( emp# NUMBER PRIMARY KEY, ename VARCHAR2(30),
salary NUMBER(8,2), deptno NUMBER)
PARTITION BY LIST
(deptno) ( -- Add list partitioning
PARTITION p10 VALUES
(10),
PARTITION p20 VALUES (20),
PARTITION p30
VALUES (30,40));
Interval
partitioning:
Interval
partitioning is a partitioning method introduced in Oracle 11g. This
is a helpful addition to range partitioning where Oracle
automatically creates a partition when the inserted value exceeds all
other partition ranges.
The
following restrictions apply:
1.
You can only specify one partitioning key column, and it must be of
NUMBER or DATE type.
2. Interval partitioning is NOT supported
for index-organized tables.
3. You can NOT create a domain index
on an interval-partitioned table
System
partitioning:
System
partitioning is a partitioning method, introduced in Oracle 11g, that
allows an application to control partition selection. System
partitions doesn't have partition keys like other partitioning
schemes. As such, partition extended syntax must be used when
inserting rows into system partitioned tables.
The
following restrictions apply:
1.
Unique local indexes are not supported (no partition key)
2. CTAS
into system partitioned tables (into what partition should it go?)
3. SPLIT PARTITION operations.
Examples:
CREATE
TABLE syspart (c1 NUMBER, c2 NUMBER)
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE ts1,
PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3);
Reference
partitioning:
Reference
partitioning is a partitioning method introduced in Oracle 11g. Using
reference partitioning, a child table can inherit the partitioning
characteristics from a parent table.
Example:
Create
a parent table with range partitioning:
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY, order_date DATE NOT NULL,
customer_id NUMBER NOT NULL, shipper_id NUMBER)
PARTITION BY
RANGE (order_date) ( PARTITION y1 VALUES LESS THAN
(TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
PARTITION y2 VALUES
LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
PARTITION y3
VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')));
Create
child table with reference partitioning:
CREATE
TABLE order_items ( order_id NUMBER NOT NULL, product_id NUMBER
NOT NULL, price NUMBER, quantity NUMBER, CONSTRAINT
order_items_fk FOREIGN KEY (order_id) REFERENCES orders)
PARTITION
BY REFERENCE (order_items_fk);
Rowid
Stored in Oracle as:
The
format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is
the block in the database file;
RRRR is the row in the
block;
FFFFF is the database file.
PRAGMA
SERIALLY_REUSABLE?
Serially
Reusable PL/SQL Packages
PL/SQL packages normally consume user
global area (UGA) memory corresponding to the number of package
variables and cursors in the package. This limits scalability because
the memory increases linearly with the number of users. The solution
is to allow some packages to be marked as SERIALLY_REUSABLE (using
pragma syntax).
For serially reusable packages, the package
global memory is not kept in the UGA per user, but instead it is kept
in a small pool and reused for different users. This means that the
global memory for such a package is only used within a unit of work.
At the end of that unit of work, the memory can therefore be released
to the pool to be reused by another user (after running the
initialization code for all the global variables).
The unit
of work for serially reusable packages is implicitly a CALL to the
server, for example, an OCI call to the server, or a PL/SQL
client-to-server RPC call or server-to-server RPC call.
Package
States
The state of a nonreusable package (one not marked
SERIALLY_REUSABLE) persists for the lifetime of a session. A
package's state includes global variables, cursors, and so on.
The
state of a serially reusable package persists only for the lifetime
of a CALL to the server. On a subsequent call to the server, if a
reference is made to the serially reusable package, Oracle creates a
new instantiation (described below) of the serially reusable package
and initializes all the global variables to NULL or to the default
values provided. Any changes made to the serially reusable package
state in the previous CALLs to the server are not visible.
Note:
Creating a new instantiation of a serially reusable package on a CALL
to the server does not necessarily imply that Oracle allocates memory
or configures the instantiation object. Oracle simply looks for an
available instantiation work area (which is allocated and configured)
for this package in a least-recently used (LRU) pool in SGA. At the
end of the CALL to the server this work area is returned back to the
LRU pool. The reason for keeping the pool in the SGA is that the work
area can be reused across users who have requests for the same
package.
Why
Serially Reusable Packages?
Since the state of a non-reusable package persists for the
lifetime of the session, this locks up UGA memory for the whole
session. In applications such as Oracle Office a log-on session can
typically exist for days together. Applications often need to use
certain packages only for certain localized periods in the session
and would ideally like to de-instantiate the package state in the
middle of the session once they are done using the package.
With
SERIALLY_REUSABLE packages the application developers have a way of
modelling their applications to manage their memory better for
scalability. Package state that they care about only for the duration
of a CALL to the server should be captured in SERIALLY_REUSABLE
packages.
