DBMS Notes - unit 5
|
Unit-V:
|
|
PL/SQL: Introduction, Structure of PL/SQL, PL/SQL Language Elements ,Data Types, Control Structure,, Steps to Create a PL/SQL Program, Iterative
Control ,Cursors , Steps to Create a Cursor
, Procedure, Function ,Packages ,Exceptions Handling, Database Triggers,
Types of Triggers.
|
Q) Write a note on PL/SQL?
PL/SQL stands for Procedural
Language/Structured Query Language,
which is provided by Oracle as a
procedural extension to SQL. SQL is a declarative language. In SQL, the statements have no control to the program and
can be executed in
any order. PL/SQL, on the other hand, is a procedural language that makes up for all the missing
elements in
SQL. PL/SQL arose from the desire of programmers to
have a
language structure that was more familiar than SQL’s purely declarative nature.
Q) What are the Shortcomings in SQL?
SQL is a powerful tool for accessing the database but it suffers from some
deficiencies as follows:
(a) SQL statements can be executed
only one at a time. Every time to execute
a SQL statement, a call is made to
Oracle
engine, thus it results in
an increase in database overheads.
(b) While processing an SQL statement, if an error occurs, Oracle generates its own error message, which is sometimes difficult to understand.
(c) SQL is not able to do the conditional query on RDBMS, this means one cannot use the conditions like if ... then, in a SQL statement. Also looping
facility is
not provided by SQL.
Q) Explain the Structured of PL/SQL.
PL/SQL is a block structured language. This means a PL/SQL program is made up
of blocks, where block is a smallest piece of
PL/SQL code having
logically related statements and declarations. A
block consists of four
sections namely: 1)Declare 2)Begin and 3)Exception and 4)End section.
1) Declare Section: Declare section
declares the variables, constants, processes, functions, etc., to be used in the other parts of
program. It is an optional section.
2) Begin Section: It is the executable section. It consists
of a set of SQL and PL/SQL statements, which is executed when PL/SQL block runs. It is a compulsory section.
3)Exception Section: This section
handles the errors,
which occurs during execution
of the PL/SQL block. This
section allows the user to define his/her own error messages. This section executes only when an error occurs. It
is an optional section.
4)End Section: This section indicates the end of PL/SQL block.
Q)
Explain PL/SQL Language Elements such as Character Set, Lexical Units etc.,
1. Character Set: A PL/SQL program consists of text having specific set of characters. Character set may include the following characters:
Alphabets, both in upper case
[A–Z] and lower case [a–z]
Numeric digits
[0–9]
Blank spaces, tabs, and
carriage returns.
PL/SQL is not case sensitive, so lowercase letters are equivalent to corresponding uppercase
letters
except within string and character literals
2. Lexical Units:
A line of PL/SQL
program contains groups
of characters known as lexical units, which can be classified
as follows:
A.
Delimiters
B.
Identifiers
C.
Literals
D. Comments
A. Delimiter is
a simple or compound symbol that has a special meaning
to PL/SQL. Simple symbol consists
of one character,
while compound symbol consists of more than one character.
PL/SQL supports following
simple
symbol delimiters:
Compound symbol delimiters legal in PL/SQL are as follows:
<> ! == ˆ= <= >= := ** .. || << >>
B. Identifiers are used in the PL/SQL programs to
name the PL/SQL program items as constants, variables, cursors,
cursor variables, subprograms, etc. Identifiers can consists of
alphabets, numerals, dollar
signs, underscores, and number signs only. Any other characters
like hyphens, slashes, blank spaces, etc. are illegal. An identifier
must begin with an alphabetic letter optionally followed by one or more characters (permissible in
identifier). An identifier cannot contain more than 30 characters.
C.
literal is an explicitly defined character,
string, numeric, or Boolean value, which is not represented by an identifier. In
the following sections we will discuss about each of these literals
in detail numeric literal:
A numeric literal is an integer or a real value. An
integer literal may be a
positive, negative, or
unsigned whole number without a
decimal point. Some
examples of integer numeric literals
are as follows:
100 006 −10 0 +10
A real literal
is a positive, negative, or unsigned
whole or fractional number with a decimal point. Some examples
of real integer literals
are as follows:
−19.0 3.56219 +43.99 .6
7.
−4.56
Character Literals: A character literal is
an individual
character
enclosed by single quotes (apos-
trophes).
Ex:
“A” “@” “5” “?”
“,”
“(”
String Literals:
A character string
can be represented by an identifier or explicitly written as a
string literal. A string literal is
enclosed within single quotes and may consist of one or more characters.
Ex: “Good Morning!” , “TATA INFOTECH LTD” , “04-MAY-00” , “$15,000,000”
Boolean Literals: Boolean literals are the predefined values TRUE,
FALSE, and NULL.
Keep in mind Boolean
literals
are values,
not
strings.
For example a condition: if (x
= 10) is TRUE only
for the value of x equal to 10, for any other
value of
x it
is FALSE and
for no value of x it is NULL
D) Comments:
Comments are used in the
PL/SQL program
to improve the readability and
understandability of a program. A comment can appear
anywhere in the pro- gram code. The compiler ignores
comments. Generally, comments are used to describe the purpose and use of each code segment.
A PL/SQL comment may
be a single-line or multiline.
Single-line comments
begin with a double hyphen (–) anywhere
on a line and extend to the end of the line.
Multiline
comments begin
with a slash-asterisk (/*) and end with an asterisk- slash (*/), and can span multiple lines
Q)
Explain Operators and its Precedence in PL/SQL.
An operator is a symbol that tells the compiler to perform
specific mathematical or logical manipulation. PL/SQL language is rich in
built-in operators and provides the following types of operators −
1.
Arithmetic operators
2.
Relational operators
3.
Comparison operators
4.
Logical operators
5.
String operators
PL/SQL supports all the SQL operators; in
addition to those operators it has one more operator, named exponentiation (symbol
is **).
An
operand is a variable,
constant, literal, or function call
that
contributes a
value to an expression. An example of simple expression
follows:
A = B **3
where A, B, and 3 are operand; = and ** are operators.
B**3 is equivalent to value of thrice
multiplying the B, i.e., B*B*B.
Operators may be unary or binary. Unary operators such as the negation operator (−) operate
on one operand; binary operators such as the
division operator
(/) operate on two operands.
PL/SQL evaluates (finds the current
value of ) an expression by combining the values
of operands in ways specified
by the operators.
This always yields
a single value
and
datatype. PL/SQL
determines the datatype by examining the expression and the context
in which
it appears.
Operators Precedence
The operations
within an expression are done in a particular order depending on their precedence (priority).
Order
of operations
operator
operation
+, −
identity, negation
*, / multiplication, division
+, −, ||
addition, subtraction, concatenation
=, !=, <, >, <=, >=,
IS NULL,
comparison LIKE, BETWEEN, IN
AND conjunction
Table lists the operator’s
level of precedence from top to bottom. Operators listed in the same row have equal precedence.
Operators with higher precedence
are
applied
first,
but
if parentheses
are used, expression within
innermost parenthesis is evaluated first.
For example
the expression 8 + 4/2 **2
results in a value 9, because
exponentiation has the highest
priority followed by division and
addition. Now in the same expression if we put parentheses, the expression 8 +
((4/2) ∗∗2) results in a value 12 not 9, because now first it will solve the expression within innermost parentheses
Q) Explain briefly about Control
Structures in PL/SQL.
Control structure
is an essential part of any programming language. It controls the flow of process. Control
structure is
broadly divided into three categories:
Conditional
control,
Iterative control, and
Sequential control
Conditional Control: A conditional control structure tests
a condition to find out whether it is true or false and accordingly executes
the different blocks of SQL statements. Conditional control is
generally performed by IF statement. There are three forms of IF statement. IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF.
IF-THEN: It is the simplest form of
IF condition. The syntax for this statement is as follows:
IF condition THEN Sequence of statements
END IF;
IF-THEN-ELSE: As it is clear with the IF-THEN construct, if condition is FALSE the control exits
to next statement out of IF-THEN clause. To execute
some other set of
statements
in case condition evaluates
to FALSE, the second form of IF state- ment is used, it adds the keyword ELSE followed by an alternative
sequence
of statements, as
follows:
IF condition THEN
ELSE
END IF;
IF-THEN-ELSIF: In the previous
constructs of IF, we can check only one condition, whether it is true
or false. There is no provision
if we want to check some other conditions
if
first
condition evaluates to FALSE; for this purpose third
form of IF statement
is used. It selects an action from several mutually exclusive alternatives. The third form of IF statement uses the keyword ELSIF
(not ELSEIF) to introduce
additional conditions, as follows
IF condition1 THEN
ELSIF condition2 THEN
ELSE
END IF;
Iterative Control: In iterative control
a group of statements are executed repeatedly till certain
condition is true,
and
control exits from
loop to next statement when
the condition becomes false. There are mainly
three
types of loop statements: 1) LOOP,
2)WHILE-LOOP, 3)FOR-LOOP.
LOOP: It encloses a sequence of statements between
the keywords LOOP and
END LOOP.
Syntax:
LOOP
END LOOP;
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
WHILE-LOOP: The WHILE statement with
LOOP
checks the condition. If it is true
then only
the sequence of statements enclosed within
the loop gets executed. Then control resumes at the top of the loop and checks the condition
again; if it is true the
sequence of statements enclosed within the
loop gets executed. The process is repeated till the condition is true. The
control passes to the
next statement outside
the loop for FALSE or NULL condition
WHILE condition
LOOP
Sequence of statements
END LOOP;
FOR-LOOP: FOR loops iterate over a specified
range
of integers.
The
range
is part of iteration scheme, which is enclosed by the keywords FOR and LOOP. A
double dot (..) serves as the range operator. The syntax is as follows:
FOR counter IN initial_value .. final_value LOOP
LOOP statements;
END LOOP;
END LOOP;
The
range
is
evaluated
when
the
FOR loop
is
first
entered
and is never
re-evaluated. The sequence of statements is executed once for each integer
in the range. After
every iteration, the loop counter
is incremented
Sequential Control:The sequential
control unconditionally passes the control
to specified unique label;
it can be in the
forward direction or in the backward
direction.
For sequential
control GOTO statement is used. Overuse
of GOTO statement
may increase the complexity,
thus as far as possible
avoid the use of GOTO statement.
The syntax
is as follows:
GOTO label;
....... .
....... .
<<label>>
Statement
Q) What
are the Steps to Create a PL/SQL Program
First
a notepad file can be created as typing in the
Oracle
SQL editor.
Figure. shows the command to create
a file,
Then
a Notepad file will appear
and at the same time background
Oracle will
be disabled. It is shown in Fig.
In this
program Cursor (Cur rent S et o f
Records) concept
is used which
we will see in the following pages. Here
content of EMP
table
is opened
by the
cursor and
they are displayed by the DBMS OUTPUT package. Command
IF is used
to check whether the cursor has been opened
successfully by using %Found attribute.
Then we can execute
that
file as follows in Fig
Q) Explain Cursors.
Number
of rows returned by a query
can be zero,
one, or many, depending
on the query search conditions. In PL/SQL, it
is not possible
for an SQL statement to return more than one row.
In
such cases
we can use
cursors. A cursor
is a mechanism that can be used to process the multiple
row result sets
one row at a time.
In
other
words, cursors are constructs that enable the user
to
name
a private
memory
area to hold a specific statement for access at a later time.
Cursors are
an inherent structure in PL/SQL. Cursors allow users to easily store and process sets of information in PL/SQL program.
Types Of Cursors
There
Are Two Types Of Cursors
1.
Implicit cursors or simple cursors
2.
Explicit Cursors
Implicit
Cursors
Oracle
implicitly opens a cursor to process each sql statement not associated with
explicitly declared cursor. Pl/sql lets you refer to the most recent implicit
cursors as the sql cursor.
Implicit
cursors have four attributes, you can use in your program
sql%rowcount
It
returns the number of rows affected by an insert, update or delete
sql%found
This
attributes evaluates to true, if an insert or update or delete
affected
to one or more rows. It evaluates to false if no row is affected
sql%notfound
This
attributes is logical opposite to sql%found. It evaluates to true,
if
an insert or update or delete does not affected to any row. It evaluates to
false
if any one row is affected
%isopen
It
checks whether the cursor has been opened or not
Explicit Cursor: Explicit cursors are declared by the user and are used to process query results that return multiple rows. Multiple rows returned from a query form a set called an active set. PL/SQL
defines
the size of the active set as the number of rows that have met search criteria.
Inherent in
every cursor is a pointer that keeps track of the multiple rows being accessed, enabling
program to process
the
rows one at a time. An explicit cursor points to the current row in the active set. This allows the program
to process one row at a time
Steps to Create
a Cursor
Declare the Cursor
In PL/SQL a
cursor, like a variable, is declared in the
DECLARE section of a PL/SQL block or subprogram. A cursor must
be declared
before it can be referenced in
other
statements. A
cursor
is defined in the declarative part by naming it
and specifying
a SELECT query to
define
the active set.
The SELECT statement associated
with a cursor declaration can reference
previously declared variables
Declaring Parameterized Cursors
PL/SQL allows declaration of cursors that can accept
input parameters which can be used in the SELECT statement with WHERE clause to select specified
rows. Syntax to
declare a parameterized cursor:
Parameter is an input parameter defined with the syntax:
The
formal parameters of a cursor must be IN
parameters. As in the example above, cursor parameters can be initialized
to default values. That way, different numbers
of actual parameters can be passed to a cursor, accepting or
overriding the default values.
Moreover, new formal parameters can be added without
having to change every reference to
the cursor. The scope of a cursor parameter is
local only to the cursor. A cursor parameter
can be referenced only within the SELECT statement associated with the cursor declaration. The values passed to the cursor parameters are used by the SELECT
statement when the cursor is opened
Open the Cursor
After declaration, the cursor is opened with an OPEN statement for processing
rows in the cursor. The SELECT statement associated with the cursor is
executed when the cursor is opened, and the active set associated with the cursor is
created.
The active
set is defined when the cursor is declared, and is created when cursor
is opened.
The active set
consists of all rows that meet the SELECT statement criteria. Syntax of
OPEN statement is
as follows.
Fetch Data from the Cursor
After
a
cursor
has
been opened,
the
SELECT statement associated
with the
cursor
is executed and the active
set
is created.
To
retrieve the rows
in the active set one row at a time,
the
rows must be fetched
individually from the cursor. After each FETCH statement, the
cursor
advances to the next
row in the active
set and retrieves
it.
Syntax of FETCH is:
where
variable name is the
name
of a variable to which
a column value
is
assigned. For
each column
value returned by the query associated
with
the cursor,
there must be a corresponding
variable
in the INTO list. This variable datatype must
be compatible with the corresponding database column
Close the Cursor
Q) Write a note on Procedures in PL/SQL.
A procedure is a subprogram that performs some specific task, and stored in the data dictionary. A
procedure must have a
name,
so that it can be invoked or called by any PL/SQL program that appears within an application. Procedures can take parameters from the calling program
and perform the specific task. Before the procedure or function is
stored, the Oracle engine
parses
and compiles the procedure or function. When a procedure is created,
the Oracle automatically performs the following steps:
1. Compiles
the procedure
2. Stores
the procedure in the data dictionary
If an error occurs during creation
of procedure, Oracle displays a message that procedure
is created with compilation errors, but it
does not display the
errors. To see the errors following statement is used:
When the function is invoked, the Oracle loads the compiled procedure in the memory area called system
global area (SGA). Once loaded in the SGA
other
users can also access the
same
procedure provided they have granted permission for this.
Benefits of Procedures and Functions
Stored procedures
and functions have many benefits in addition to modulari-
zing application development.
1. It modifies one routine to
affect multiple applications.
2. It modifies one routine to
eliminate duplicate testing.
3. It ensures that related actions are performed together,
or not at all, by
doing the activity through
a single path.
4. It avoids PL/SQL parsing at runtime by parsing at compile time.
5. It reduces the number of calls to the database and database network traffic by bundling the commands.
Defining and Creating
Procedures
A procedure consists of
two parts: specification and body. The specification starts with keyword PROCEDURE and ends with parameter
list or procedure name. The procedures may accept parameters or may not. Procedures
that do not accept parameters are written parentheses.
The procedure body starts with the keyword IS and ends with keyword
END. The procedure body is further subdivided into three parts:
Declarative part which consists of local declarations placed between key- words IS and BEGIN
Executable part, which consists of actual
logic of the procedure,
included between keywords BEGIN and EXCEPTION.
At least one
executable statement is
a must
in the executable portion of a
procedure. Error/Exception
handling part, an optional
part
placed between EXCEP- TION and
END.
The syntax
for creating a procedure is follows:
BEGIN
executable
statements
EXCEPTION exception handlers
END [procedure
name];
Create: Creates a new procedure, if a procedure
of same name already
exists, it gives an error.
Replace: Creates a procedure,
if a procedure of same name already
exists, it replace
the older one by the new procedure definition.
Schema: If the schema is not specified then procedure
is created in user’s current schema.
Example: shows the procedure
to raise the
salary
of the employee. The name of the procedure
is raise sal
Executing/Invoking a Procedure
The
syntax used
to execute a procedure depends on the
environment from which the procedure is being called. From within SQLPLUS, a procedure can be executed by using the EXECUTE command, followed by
the procedure
name. Any
arguments to be passed to the procedure must
be
enclosed in parentheses following the procedure
name
Removing a Procedure
To remove a procedure
completely
from the database, following command is used:
DROP
PROCEDURE <PROCEDURE NAME>;
Q) Write a note on functions in PL/SQL.
(OR) Difference between Function and Procedure.
A Function is
similar to procedure except that it must return one and only one value to the calling
program. Besides this, a function can be used as part
of SQL expression, whereas
the procedure cannot.
Difference Between Function and
Procedure
Before we look at functions in
deep,
let us first discuss
the major differences between a function and a
procedure.
1. A procedure never returns a
value to the calling
portion of code, whereas
a function returns exactly one value to the calling
program.
2. As functions
are capable of returning
a value, they can be used as elements of SQL expressions, whereas
the procedures cannot. However, user-defined
functions cannot be used in CHECK or DEFAULT constraints and cannot
manipulate
database values,
to obey function
purity rules.
3. It is mandatory for a
function to have at
least
one RETURN statement,
whereas for procedures there is no restriction. A procedure may have a
RETURN statement or may not. In case of procedures with RETURN statement, simply the control of execution is transferred back to the portion of
code that called the procedure
The exact syntax for defining a
function is given below:
CREATE OR REPLACE FUNCTION [schema.] functionname [(argument IN
datatype, . . . .)] RETURN datatype {IS,AS}
[local variable
declarations];
BEGIN
executable statements;
EXCEPTION
exception handlers;
END [functionname];
where RETURN datatype is
the datatype of the function’s return value. It can be any PL/SQL datatype.
Thus a function has two parts: function
specification and function body. The function specification begins with keyword FUNCTION and ends with
RETURN clause which indicates the datatype of
the
value returned by the
function. Function body is enclosed between the
keywords IS and END. Some- times END is followed by function
name, but this is optional.
Like procedure, a
function body also is composed of three parts: declarative part, executable part, and an optional error/exception handling part.
Removing a Function
To remove a function, use following command:
Q) Write a brief description on Packages
in PL/SQL.
A package can be defined as a collection of
related program objects such as
procedures, functions, and associated cursors and variables
together as a
unit in the database. In
simpler term, a package is a group of related procedures
and functions stored
together and sharing
common variables, as well as
local procedures and functions. A
package contains two separate parts: the package specification and the package body. The package specification
and package body are compiled
separately and stored in the data dictionary
as two sepa- rate objects. The
package body is
optional and need not to be created
if the package specification does not contain any procedures
or functions. Applications or users can call packaged procedures and functions explicitly similar to standalone procedures
and functions.
Advantages of Packages
Packages offer a lot of advantages. They are as
follows.
1. Stored packages allow us to sum up (group logically) related stored pro-
cedures, variables, and data types, and so forth in a single-named, stored
unit in the database. This provides for better orderliness during the development process. In
other
words packages and its modules are easily understood because
of their logical grouping.
2. Grouping of related procedures, functions, etc. in a package also make
privilege management easier. Granting the privilege to use a package makes all components of the package accessible to
the grantee.
3. Package helps in achieving data abstraction. Package body hides the
details of the package contents
and the definition of private
program objects so that only the package contents are affected if the package body
changes.
4. An entire package is loaded into memory when a
procedure within the package is called for the first time. This load is completed in one opera-
tion,
as opposed to the separate loads required for standalone procedures.
Therefore, when calls to
related packaged procedures
occur, no disk I/O
is necessary to execute the compiled code already
in memory. This results in faster and efficient operation
of programs.
5. Packages provide better performance than stored procedures and functions because public package variables persist in
memory
for the duration of a session. So that they can be
accessed by all procedures
and functions that try to access them.
6. Packages allow overloading
of its member modules. More than one func- tion in a package can be of same name. The functions are differentiated, depending upon the type and number of parameters it
takes
Parts
of Package
A Package has two parts.
They are:
– Package specification
– Package body
Package Specification
The specification declares the types, variables, constants, exceptions, cursors, and subprograms that are public and thus available
for use outside the pack- age. In case in
the package
specification declaration there is
only
types, con- stants, exception, or variables, then there is no need for the package body because package specification are sufficient for them. Package body is required
when there is subprograms like cursors, functions,
etc.
Package Body
The package body fully defines subprograms such as cursors,
functions, and procedures. All the private declarations of the package are included
in the package body. It
implements the package specification. A
package specifica-
tion
and the package body are stored separately in
the database. This allows calling objects to depend on the specification only, not on both. This separa- tion enables to
change the definition
of program object in the package body without causing
Oracle to interfere
with other objects that call or reference
the program
object. Oracle invalidates the calling object if
the package spec-
ification is changed.
Creating a Package
A package consists
of package specification and package body. Hence creation of a package involves creation
of the package specification and then creation of the package body.
The package specification is declared using the CREATE PACKAGE com- mand
The syntax
for package specification declaration is as follows.
[AS/IS]
PL/SQL package specification
All the procedures,
sub programs, cursors declared in the
CREATE PACK- AGE command are described and implemented
fully in the package body along with private
members.
The
syntax for declaring
a package body is as follows:
[AS/IS]
PL/SQL package body
Member
functions
and
procedures can
be declared in a package and can be made
public
or private
member
using
the
keywords
public
and
private. Use of all the private
members of the package is restricted within the package while
the public members of the package can be accessed and used
outside the
package.
Q)
Briefly describe Exceptions Handling in PL/SQL.
An exception
is an error condition during a program execution. PL/SQL supports programmers
to catch such conditions using EXCEPTION block in the program and an
appropriate action is taken against the error condition. There are two types of
exceptions −
System-defined
exceptions
User-defined
exceptions
The general
syntax for exception handling is as follows. Here you can list down as many
exceptions as you can handle. The default exception will be handled using WHEN
others THEN −
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
Advantages of Using Exceptions
1.
Control over abnormal exits of executing
programs
on encountering
error conditions, hence
the behavior of application becomes more reliable.
2.
Meaningful
messages
can
be flagged
so that the developer can become aware of error and warning
conditions
and act upon them.
3.
In
traditional error
checking
system,
if same error is to
be checked at several places, you are required
to code the same
error check at all those places. But with exception handling technique, we will write the exception for that particular error only once in the entire code. Whenever that type error occurs at
any place in code, the exceptional handler will automati-
cally
raise the defined exception.
4. Being a part of PL/SQL,
exceptions can be coded at suitable places
and can be coded isolated
like procedures
and functions. This improves the overall readability of
a PL/SQL program.
5. Oracle’s internal exception mechanism combined
with user-defined
exceptions, considerably reduce
the development
efforts required for cumbersome error handling.
Types of
Exceptions
1. Pre
Defined Exceptions
2. User
Defined Exceptions
Pre Defined Exceptions
These
exceptions are used to handle some logical errors known to the system are pre
defined. The following are some of the important pre defined Exceptions
no_data_found
This
Exception raises when there is no rows to be retrieved from a table according
to given condition
dup_val_on_index
This
Exception raises when ever you try to store duplicate values into a table,
which has been indexed (unique indexed)
cur_already_open
This
Exception raises when ever your program attempts to open an already opened
cursor.
A cursor
must be closed before it can be re opened. A cursor for loop automatically
opens the cursor to which it refers. So Your program can not open that cursor
inside the loop
invalid_cursor
This
Exception raises when ever your program attempts an illegal cursor operation,
such as closing an un opened cursor
zero_divide
This
Exception raises when ever your program attempts to divide a number by zero
program_error
This
Exception raises when ever PL/SQL has internal problem
storage_error
This
Exception raises when ever PL/SQL runs out of memory
too_many_rows
This
Exception raises when ever a select statement returns more than one row
login_denied
This
Exception raises when ever your program attempts to logon to oracle with an
invalid user name and/or password
value_error
This
Exception raises when ever an arithmetic conversion or size constraint error
occurs
User Defined Exceptions
Unlike internally defined
exceptions, user-defined exceptions must be declared and raised explicitly by RAISE statements. Exceptions can be declared only in
the declarative part of a PL/SQL block, subprogram, or package. An exception is declared by introducing its
name,
followed by the keyword EXCEPTION.
Q) Briefly explain about PL/SQL Triggers.
A PL/SQL
trigger is a construct in PL/SQL that runs or "triggered" on
event of changes being made to a table in the database. The triggering event is
a INSERT, UPDATE or DELETE done on a table. The trigger can be made so
it can be "fired" either BEFORE or AFTER the Data Manipulation
Language is executed.
A database
trigger is a block of code that is automatically executed in response to
certain events.
Triggers are
executed implicitly whenever the triggering event happens.
The
triggering event is an INSERT, DELETE, or UPDATE command.
The timing
can be either BEFORE or AFTER, INSTEAD OF trigger.
Create Trigger Syntax
create or replace trigger <trigger_name>
{before/after/instead of}
{insert/update/delete}
[ of <column name> on <table name> ]
[ for each row [when <condition>] ]
Declare
variables declarations
-----------------
begin
Executable statements
-------------
Exception
Exception statements
---------------
end;
/
Parts of Trigger
A trigger has three basic parts:
A triggering event or statement
A trigger restriction
A trigger action
Trigger Event or Statement
A triggering event or
statement is the SQL statement, database
event, or user
event like update, delete,
insert, etc. that causes a trigger to be fired. It also specifies the table to which the trigger is associated.
Trigger statement or an event can be any of the following:
1. INSERT, UPDATE, or DELETE on a
specific table or view.
2. CREATE, ALTER, or DROP on any schema object.
3. Database
activities like startup and shutdown.
4. User activities like logon
and logoff.
5. A specific error message
on any error message.
Figure . shows a database application with some SQL statements that implicitly fire several triggers stored
in the database. It
shows three triggers,
which are associated with the INSERT, UPDATE, and DELETE operation in the database table. When these data manipulation commands are given, the
corresponding trigger
gets automatically fired performing the task described in the corresponding trigger body.
Trigger Restriction
A trigger restriction
is any logical
expression whose outcome
is TRUE/FALSE/ UNKNOWN. For a trigger to
fire,
this logical expression must evaluate to TRUE. Typically, a restriction is a part of trigger
declaration that follows the
keyword WHEN.
Trigger Action
A trigger action is the PL/SQL block that contains the SQL
statements and code to
be executed when a triggering statement is
issued
and the trigger restriction evaluates to TRUE. It is also called the trigger
body. Like stored
procedures, a
trigger action can contain SQL and PL/SQL.
Different
types of triggers can be:
Row Triggers and Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects. A row trigger fires once for each row affected by the triggering event.
BEFORE and AFTER Triggers: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.
INSTEAD OF Triggers: INSTEAD OF triggers describe how to perform insert, update, and delete operations against views that are too complex to support these operations natively. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select).
Triggers on System Events and User Events: You can use triggers to publish information about database events to subscribers. System events are for example Database startup and shutdown, Data Guard role transitions etc and User Events are User logon and logoff, DDL statements (CREATE, ALTER, and DROP) etc.
Dropping Triggers
Row Triggers and Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects. A row trigger fires once for each row affected by the triggering event.
BEFORE and AFTER Triggers: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.
INSTEAD OF Triggers: INSTEAD OF triggers describe how to perform insert, update, and delete operations against views that are too complex to support these operations natively. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select).
Triggers on System Events and User Events: You can use triggers to publish information about database events to subscribers. System events are for example Database startup and shutdown, Data Guard role transitions etc and User Events are User logon and logoff, DDL statements (CREATE, ALTER, and DROP) etc.
Dropping Triggers
Triggers can be dropped like tables using the drop trigger command. The
drop trigger command
removes the trigger structure from the database. User needs to have DROP ANY TRIGGER
system
privilege to drop a trigger.
The exact syntax for dropping a
trigger is as follows.
Syntax
Example of
creating a trigger based on the following two tables:
CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);
We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.
CREATE TRIGGER tr1
AFTER INSERT ON T1
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 5)
BEGIN
INSERT INTO T2
VALUES(:newRow.a);
END tr1;
CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);
We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.
CREATE TRIGGER tr1
AFTER INSERT ON T1
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 5)
BEGIN
INSERT INTO T2
VALUES(:newRow.a);
END tr1;
posted by mahaboobwithu @ September 16, 2019
0 Comments
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home