CH-4:- PL/SQL

Difference between SQL and

PL/SQL. DETAILING:-

SQL PL/SQL
SQL statements are passed to the PL/SQL sends an entire block of SQL
Oracle Engine one at a time. Each time statements to the Oracle engine all in one
an SQL statement is executed, a call is go. Since the Oracle engine got the SQL
made to the engine’s resources, which statements as a single block, it processes
increases network traffic. this code much faster than one sentence
at a time.
SQL does not have any procedural PL/SQL is development tool that supports
capabilities like Conditional checking using facilities of conditional checking, branching
conditional statement, looping and and looping, declaration and use of
branching, declaration and use of variable variable to store intermediate result.
to store intermediate result.
SQL has no facility for programmed PL/SQL also permits dealing with errors as
handling of errors and so if an error required, and facilitates displaying user-
occurs, the Oracle engine displays its own friendly messages, when errors are
error messages. encountered.
In SQL it is not possible to perform PL/SQL facilitates all sorts of calculations
calculations without the use of the Oracle can be done quickly and efficiently without
Engine. the use of the Oracle engine.
Structured query language. Procedural language structure query
language.

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. FULL FORM OF SQL STRUCTURE QUERY
LANGUAGE
2 SQL IS LANGUAGE? YES
3 PL/SQL MEANS PROCEDURAL
STRUCTURE QUERY
LANGUAGE.
4 WHY WE CAN USE PL/SQL? IN PL SQL WE CAN USE
BRANCHING
STATEMENT SO WE
CAN
USE THIS.

EXPLAIN PL/SQL BLOCK STRUCTURE.

A single PL/SQL code block consists of a set of SQL statements, clubbed

together, and passed to the Oracle engine entirely.
A PL/SQL block has a definite structure, which can be divided
into sections.
 The sections of PL/SQL block are:

· The Declare section.

· The Begin Section.
· The Exception section is optional.
· The end section.

The Declare Section

· PL/SQL block start with a declaration section, in which,
memory variables and other Oracle objects can be declared and
initialized.
The Begin Section
· It consists of a set of SQL and PL/SQL statements.
· Data manipulation statements, retrieval of data using SELECT,
conditional statements, looping and branching statements are
specified in this section.

The Exception Section

· This section deals with handling of errors that arise during
execution of the data manipulation statements and also
user can generate its own user-defined error message.

The End Section

DECLARE

Declarations of memory variables, constants, cursors, etc. in

PL/SQL

BEGIN

SQL executable statements

PL/SQL executable statements

EXCEPTION
SQL or PL/SQL code to handle errors that may arise during the execution of the
code block between BEGIN and EXCEPTION section

EXPLAIN DIFFERENTS TYPES OF LOOP IN PL/SQL. DETAILING:

·
Iterative control indicates the ability to repeat sections of a code block.
· A loop marks a sequence of statements that has to be repeated.
· The keyword loop has to be placed before the first statement in the sequence
of statements to be repeated, while the keyword end loop is placed
immediately after the last statement in the sequence.

PL/SQL supports the following structures for iterative control:

Simple Loop

· In simple loop, the keyword loop should be placed before the first
statement in the sequence and the keyword end loop should be
written at the end of the sequence to end the loop.

Syntax:
Loop
Sequence of statements;
End loop;

declare
num number := 0;
begin
loop
num:=num + 2;
exit when num > 10;
end loop;
dbms_output.put_line(‘Loop exited when the value of num has reached’
to_char(i));
end;
The WHILE loop
Syntax:
WHILE condition
LOOP
Action;
END LOOP;

declare
PI number (4, 2):= 3.14;
radius number(5);
area number(14,2);
begin
radius := 3;
while radius <=
7 loop
area := PI * power(radius,2);
insert into areas values(radius, area);
radius := radius + 1;
end loop;
end;
The FOR Loop

Syntax:
FOR variable IN [REVERSE] start..end
LOOP
Action;
END LOOP;

· Note: The variable in the FOR loop need not be declared. Also the
increment value cannot be specified. By default the for loop
variable is always incremented by 1.

Example:
declare
strval varchar(10);
str_length number(2);
reverse_str varchar(10);
begin
strval:=’&strval’;
str_length := length(strval);
for i in reverse 1..str_length
loop
reverse_str := reverse_str || substr(strval,i,1);
end loop;
dbms_output.put_line(‘Original String is : ‘ || strval);
dbms_output.put_line(‘Reverse String is : ‘ || reverse_str);
end;
EXPLAIN %TYPE AND %ROWTYPE WITH EXAMPLE.
DETAILING:

%TYPE

· PL/SQL uses the %TYPE attribute to declare variables based on

definitions of columns in a table so user doesn’t need to remember
data type and size of column of table.
· If a column’s attributes change, the variable’s attributes will change as
well.
· This provides for data independence, reduces maintenance costs, and
allows program to adapt to changes made to the table.

Syntax:

Tablename.columnname%TYPE;
Example:
declare
/*data type of mempno and msal is similar to the data type
and size of empno and salary column of emp table.*/

mempno emp.empno%TYPE;
msal emp.sal%TYPE;
incre_amt number(4);
finalsal number(10,2);
begin
mempno:=&mempno;
incre_amt:=&incre_amt;
select sal into msal from emp where empno=mempno;
finalsal:=msal+incre_amt;
update emp set sal=finalsal where empno=mempno;
insert into oldsal values(mempno,msal,sysdate);
end;
%ROWTYPE

· PL/SQL uses the %ROWTYPE attribute to declare variables based on

definitions of entire column in a table so user doesn’t need to remember
data type and size of columns of table.
· If a column’s attributes change, the variable’s attributes will change as well.
· This provides for data independence, reduces maintenance costs, and allows
program to adapt to changes made to the table.

Syntax:
tablename%ROWTYPE

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. TYPE AND ROW TYPE ARE ATTRIBUTE.

2 TYPE MEANS ONE COLUMN

3 ROW TYPES MEANS ALL COLUMN

WHAT IS
CURSOR?
DETAILING:-
· The Oracle Engine uses a work area for its internal processing in order to
execute an SQL statement.
· This work area is called a Cursor. A cursor is opened at the client end.
· The data is stored in the cursor is called the Active Data Set.
· The size of the cursor in memory is the size required to hold the number of rows
in the Active Data Set.
Example:
When a user fires a select statement as:
SELECT empno, ename, sal FROM emp WHERE deptno=10;

CURSOR

Active Data Set

E1 Ivan 8500
SERVER E4 Peter 5700
E5 Robert 9000
E8 Julie 6500

Types of Cursors
Cursors are classified into two types.
1. Implicit Cursor
2. Explicit Cursor
 A cursor that is created, opened and managed by Oracle Engine for its
internal processing is known as Implicit Cursor.
A cursor that is created, opened and managed through PL/SQL block by the
user is known as Explicit Cursor.

Cursor Attributes:
· Cursor Attributes are a set of four system variables, which keep track of the
Current status of a cursor.
· Both Implicit and Explicit cursors have four attributes.

They are described below:

Attribute Name Description

%ISOPEN Returns TRUE if cursor is open, FALSE otherwise.
%FOUND Returns TRUE if record was fetched successfully,
FALSE otherwise.
%NOTFOUND Returns TRUE if record was not fetched successfully,
FALSE otherwise.
%ROWCOUNT Returns number of records processed from the
cursor.

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. CURSOR MEANS WORK AREA
2 IMPLICIT MEANS ORALCE ENGINE
3 EXPLICIT USER
4 HOW MANY TYPES OF 4
ATTRIBUTE ARE IN CURSOR?
5 ADT MEANS ACTIVE DATA SET.
 EXPLAIN IMPICIT CURSOR WITH
EXAMPLE. DETAILING:-
Implicit Cursor

· A cursor that is created, opened and managed by Oracle Engine for its internal
processing is known as Implicit Cursor.
· Since the implicit cursor is opened and managed by the Oracle engine internally,
the function of reserving an area in memory, filling this area with appropriate
data, processing the data in the memory area, releasing the memory area when
the processing is completed, is taken care of by the Oracle engine.
· The name of implicit cursor is always SQL.

begin
update emp set sal=sal+(sal*0.15) where empno = &mempno;
if sql%found then
dbms_output.put_line('Employee exists…Increment done successfully')
end if;
if sql%notfound then
dbms_output.put_line('Employee does not exist…Try another number
end if;
end;
 The implicit cursor has following attribute.

Attribute Name Description

%ISOPEN Returns TRUE if cursor is open, FALSE otherwise.
%FOUND Returns TRUE if record was fetched successfully,
FALSE otherwise.
%NOTFOUND Returns TRUE if record was not fetched successfully,
FALSE otherwise.
%ROWCOUNT Returns number of records processed from the
cursor.

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. IMPLICIT MEANS ORALCE ENGINE
2 NAME OF IMPLICIT CURSOR SQL
3 HOW MANY ATTRIBUTE ? 4

EXPLAIN EXPLICIT CURSOR WITH EXAMPLE.

DETAILING:
· When individual records in a table have to be processed inside a PL/SQL code
block, a cursor is used. This cursor will be declared and mapped to a SQL query
in the Declare Section of the PL/SQL block and used within its Executable ;
Section.
· A cursor thus created and used is known as Explicit Cursor.
');
The steps involved in using an explicit cursor and manipulating data in its active
set are:

 Declare a cursor mapped to a SQL select statement that

retrieves data for processing.
 Open the cursor.
 Fetch data from the cursor one row at a time into
memory variables.
 Process the data held in the memory variables as required using
a loop.
 Exit from the loop after processing is complete.
 Close the cursor.

· Cursor Declaration

 A cursor is defined in the declarative part of a PL/SQL block.

 Naming the cursor and mapping it to a SELECT query is done at
this
 stage.
 When a cursor is declared, the Oracle engine is informed that a
cursor of the said name needs to be opened. The declaration is
only information.
  There is no memory allocation at this point in time.

Syntax:
CURSOR CursorName IS SELECT statement;

The Functionality of Open, Fetch and Close Commands

Initialization and Opening of a cursor takes place via the Open statement. Open
Statement will:
Executes a query associated with the cursor.
Creates the Active Data Set that contains all rows, which meet the query search
criteria
Sets the row pointer in the Active Data Set to the first record.

Syntax:
Open <cursorname>;

Fetching Data from Cursor

o A fetch statement then moves the data held in the Active Data Set into
memory variables.
o Data held in the memory variables can be processed as desired.
o The fetch statement is placed inside a Loop…End Loop construct, which
causes the
 data to be fetched into the memory variables and processed until all the
rows in
 the Active Data Set are processed.
o The fetch loop then exits.

Syntax:
FETCH CursorName INTO Variable1, Variable2,…;

Closing a Cursor
The Close Statement will release the memory occupied by the cursor and its Data
Set both on the Client and on the Server.

Syntax: CLOSE <Cursor Name>;

declare
/*data type of mempno,mdept and msal is similar to the data type and size
of empno, dept and salary column of emp table.*/

/*Defining Cursor*/
cursor c_emp is select empno,sal from emp where dept=’HR’;
mempno emp.empno%type;
msal emp.sal%type;
incre_amt number(4);
finalsal emp.sal%type;
begin
/*Open Cursor*/
open c_emp; /* Fetching of rows from
cursor*/ loop
fetch c_emp into mempno,msal;

if c_emp%found then
incre_amt:=(msal*15)/100;
finalsal:=msal+incre_amt;
update emp set sal=finalsal where empno=mempno;
insert into hroldsal values(mempno,msal,sysdate);
else
exit;
end
if; end loop;
 ONE WORD QUESTION AND ANSWER
SR.NO QUESTION ANSWER
1. EXPLICIT MEANS USER

2 WHO WILL CREATE THIS USER

CRSOR
3 CURSOR IS INDEPENDENT NO
OBJECT?
4 IN CURSOR WE CAN USE TYPE YES
AND ROW TYPE?

WHAT ARE FUNCTION AND

PROCEDURE? DETAILING:
e A Procedure or Function is a logically grouped set of SQL and PL/SQL statements
that perform a specific task.
e A stored procedure or function is a named PL/SQL code block that has been
compiled and stored in one of the Oracle engine's system tables.

Procedures and Functions are made up of:

÷ A declarative part
÷ An executable part
÷ An optional exception-handling part

Declarative Part
÷ The declarative part may contain the declarations of cursors, constants,
variables, exceptions and subprograms. These objects are local to the
procedure or function.

Executable Part
÷ The executable part is a PL/SQL block consisting of SQL and PL/SQL
statements that assign values, control execution and manipulate data.
÷ The data that is to be returned back to the calling environment is also
returned from here. The variables declared are put to use within this
block.

Exception Handling Part

÷ This part contains code that deals with exceptions that may be
raised during the execution of code in the executable part.
÷ An Oracle exception handler can be redirected to the exception
handling section of the procedure or function where the procedure
or function determines the actual action that must be carried out
by Oracle's exception handler.

Procedures and Functions are stored in the Oracle database. Before a

procedure or function is stored, the Oracle engine parses and compiles the
procedure or function.

The Oracle engine while creating a procedure performs the following

steps automatically:
 ÷ Compiles the procedure or function
÷ Stores the procedure or function in the database

Note: The compilation process does not display the errors. These errors can be viewed by
giving
“show err” command at sql prompt or by using “SELECT * FROM USER_ERRORS”
statement. The status of a procedure or function is shown by the use of a select
statement as
follows:

SELECT ObjectName, ObjectType, Status FROM User_Objects

WHERE ObjectType = 'PROCEDURES';

O
R

SELECT Object_Name, Object_Type, Status FROM User_Objects

WHERE ObjectType = “FUNCTION”;

ADVANTAGES OF USING A PROCEDURE OR FUNCTION

1. Security
2. Performance
3. Memory Allocation
4. Productivity
5. Integrity

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. PROCEDURE AND FUNCTION SPECIAL SPACE THAT
MEANS WORK ON SPECIAL
PROGRAM
2 PROCEDURE AND FUNCTION SYSTEM TABLE
ARE STORE IN_
3 PROCEDURE AND FUCNTION YES
COMPILE AUTOMATICALLY

EXPLAIN HOW TO CREATE FUNCTION?

DETAILING:

Syntax:
CREATE OR REPLACE FUNCTION <Function Name> (Argument IN Data type...)
RETURN <Data type> {IS, AS}
Variable declarations;
Constant declarations;
BEGIN
PL/SQL subprogram body;
EXCEPTION
Exception PL/SQL blocks;
END;
Function also needs pl/sql block to call it.
Declare
Variable declaration
Begin
Variable=call function
(); Subprogram body;
End;
 Keywords and Parameters
The keywords and the parameters used for creating database functions are
explained below:
REPLACE Recreates the function if it already exists. This option is used to change
the definition of an existing function without dropping, recreating and
re- granting object privileges previously granted on it. If a function is
redefined, Oracle recompiles it.
Function Is the name of the function to be created?
Argument Is the name of an argument to the function? Parentheses can be omitted
if no arguments are present.
IN Indicates that the parameter will accept a value from the user.
RETURN data type Is the data type of the function's return value? Because every function
must return a value, this clause is required. It supports any data type
supported by PL/SQL.
PL/SQL Is the definition of function consisting of PL/SQL statements.
subprogram body

EXAMPLE:create or replace function get_deptnm(vempno in number)

return number is mdeptnm varchar2(10);
begin
select dept.deptnm into mdeptnm from emp,dept where
dept.deptno=emp.deptno and emp_no=vempno;
return(mdeptnm);

declare
meno number(3);
dnm varchar2(10);
begin
meno:=&meno;
dnm:=get_deptnm(meno);
dbms_output.put_line(‘Employee No. : ‘ || meno || ‘ Department Name : ‘ || dnm);
end;
DELETING A STORED PROCEDURE OR FUNCTION

Syntax:
DROP FUNCTION <Function Name>;

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. PROCEDURE AND FUNCTION SPECIAL SPACE THAT
MEANS WORK ON SPECIAL
PROGRAM
2 DIFFERENCE BETWEEN PROCEDURE SHOULD
PROCEDURE AND FUNCTION RETURN VALUE AND
FUNCTION MUST
RETURN VALUE
3 REPLACE MEANS WE CAN OVERWRITE
THE FUNCTION OR
PROCEDURE
4 HOW MANY BLCOK ARE IN 2
FUNCTION

EXPLAIN HOW TO CREATE PROCEDURE?

DETAILING:

Syntax:
CREATE OR REPLACE PROCEDURE <Procedure Name>
(Argument {IN, OUT, IN OUT) Data Type ...) {IS, AS}
Variable declarations;
Constant declarations;
BEGIN
PL/SQL subprogram body;
 EXCEPTION
Exception PL/SQL blocks;
END;

Keywords and Parameters

The keywords and the parameters used for creating database procedures are
explained below:

REPLACE Recreates the procedure if it already exists. This

option is used
to change the definition of an existing procedure
without dropping, recreating and re-granting object
privileges previously granted on it. If a procedure is
Procedure Is the name of the procedure to be created?
Argument Is the name of an argument to the procedure?
Parentheses can
IN Indicates that the parameter will accept a value from
OUT Indicates that the parameter will return a value to the
IN OUT Indicates that the parameter will either accept a
value from the
Data type Is the data type of an argument? It supports any data
type
PL/SQL body Is the definition of procedure consisting of PL/SQL

Procedures can be executed with EXECUTE statement.

Syntax:
Execute procedure name;

create or replace procedure search_employee(vempno in number, vename out varchar2, vjob

out varchar2) is
begin
select ename,job into vename,vjob from emp where emp_no=vempno;
end;

declare
mename emp.ename%type;
mjob emp.job%type;
mempno number := 101;
begin
search_employee(mempno,mename,mjob);
dbms_output.put_line('Employee # : ' || mempno);
dbms_output.put_line('Name : ' || mename || ' Job ' ||
end; mjob);
 ÷ In the above example, value of employee number is passing to
search_employee procedure through PL/SQL block and value of two OUT
parameters vename and vjob will return back to the calling block and stored in
mename and mjob respectively.

DELETING A STORED PROCEDURE

A procedure can be deleted by using the following syntax

Syntax:
DROP PROCEDURE <Procedure Name>;

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. PROCEDURE AND FUNCTION SPECIAL SPACE THAT
MEANS WORK ON SPECIAL
PROGRAM
2 PROCEDURE AND FUNCTION SYSTEM TABLE
ARE STORE IN_
3 PROCEDURE AND FUCNTION YES
COMPILE AUTOMATICALLY

XPLAIN PACKAGE WITH

EXAMPLE. DETAILING:-
÷ A package is an Oracle object, which holds other objects within it.
÷ Objects commonly held within a package are procedures, functions, variables, constants,
cursors and exceptions.
÷ The tool used to create a package is SQL* Plus.
÷ It is a way of creating generic, encapsulated, re-useable code.
÷ A package once written and debugged is compiled and stored in an Oracle
Database.
÷ All users who have executed permissions on the Oracle Database can then use the package.

A package has usually two components:

1. Package specification
2. Package body.

A package's specification part declares functions, procedures, global memory variables,

constants, exceptions, cursors, etc.
A package's body fully defines procedures, functions, and cursors that are
defined in speciation part.

Packages offer the following advantages:

÷ Packages enable the organization of commercial applications into efficient modules.

÷ Each package is easily understood and the interfaces between packages are simple,
clear and well defined.
÷ Packages allow granting of privileges efficiently.
÷ A package's public variables and cursors persist for the duration of the session.
÷ Therefore all cursors and procedures that execute in this environment can share them.
÷ Packages enable the overloading of procedures and functions when required.
÷ Packages improve performance by loading multiple objects into memory at once.
÷ Therefore, subsequent calls to related subprograms in the package require no I/O.
÷ Packages promote code reuse through the use of libraries that contain stored procedures
and functions, thereby reducing redundant coding.

Syntax of Package Specification:

CREATE OR REPLACE PACKAGE package name IS
Declaration of function;
Declaration of procedure; Variable
declaration;
END packagename;

Syntax of Package Body:

CREATE OR REPLACE PACKAGE BODY packagebodyname IS
Definition of function;
Definition of procedure;
Variable declaration;
END packagebodyname;

Example:
Create or replace package pck_employee is
Function get_deptnm (vempno in number) return number is;
Procedure search_employee (vempno in number, vename out
varchar2, vjob out varchar2) is;
End pck_employee;

Create or replace package body pck_employee is

Function get_deptnm (vempno in number) return number is mdeptnm varchar2 (10);
begin
Select dept.deptnm into mdeptnm from emp,dept where dept.deptno=emp.deptno
emp_no=vempno;
Return (mdeptnm);
end;

Procedure search_employee (vempno in number, vename out varchar2, vjob out

varchar2) is
begin
Select ename, job into vename, vjob from emp where emp_no=vempno;
end;
 end pck_employee;

Execution of Package:

When a package is invoked, the Oracle engine performs three steps to execute it:

1. Verify user access:

Confirms that the user has EXECUTE system privilege granted for the subprogram
2. Verify procedure validity:
Checks with the data dictionary to determine whether the procedure and functions
used in package are valid or not.
3. Execute:
The package subprograms are executed.
Syntax:
 P ac kag en am e.su b program
ONE WORD QUESTION A N D A N SW ER
name;
SR.NO QUESTION ANSWER
1. PACKAGE MEANS? ORACLE OBJECT
2 CAN PACKAGE HOLD OTHER YES
OBJECT WITH IN IT?
3 HOW MANY TYPES 2
OFPACKAGE

WHAT IS TRIGGER? EXPLAIN TYPES OF

TRIGGER. DETAILING-
÷ Database triggers are database objects created via the SQL* Plus tool on the
client and stored on the Server in the Oracle engine's system table.
÷ These database objects consist of the following distinct sections:
e A named database event
e A PL/SQL block that will execute when the event occurs
÷ The Oracle engine allows the procedures that are implicitly executed by the user
when an insert, update or delete is issued against a table.
÷ These procedures are called database triggers.
÷ The major issues that make these triggers standalone are that, they are fired
implicitly (i.e. internally) by the Oracle engine itself and not explicitly called by
the user.

Use of Trigger:

Note: The PL/SQL block cannot contain transaction control SQL statements like COMMIT,
ROLLBACK, and SAVEPOINT in trigger

A trigger has three basic parts:

÷ A triggering event or statement(SQL PART)
÷ A trigger restriction (WHEN CLAUSE)
÷ A trigger action(PL/SQL COMMAND)

Triggering Event or Statement(SQL COMMAND OR PART)

÷ It is a SQL statement that causes a trigger to be fired.
÷ It can be INSERT, UPDATE or DELETE statement for a specific table. (i.e.
Table write operations)

Trigger Restriction
÷ A trigger restriction specifies a Boolean (logical) expression that must be
TRUE for the trigger to fire.
÷ A trigger restriction is specified using a WHEN clause.
÷ In short, when the expression given with WHEN true then only
trigger action becomes executes for triggering event.

Trigger Action(PL/SQL PART)

÷ A trigger action is the PL/SQL code to be executed when a
triggering event fires and any trigger restriction (if any) evaluates
to TRUE.
÷ The PL/SQL block can contain SQL and PL/SQL statements.
 TYPES OF TRIGGERS
There are mainly two types of triggers:
1. Row Triggers
2. Statement Triggers
Row Triggers
÷ A row trigger is fired each time a row in the table is affected by
the triggering statement.
÷ For example, if an UPDATE statement updates 5 rows of a table, a
row trigger is fired once for each row means it fires 5 times.

÷ If the triggering statement affects no rows, the trigger is not executed at all.
÷ Row triggers should be used when some processing is required whenever
a triggering statement affects a single row in a table.

Statement Triggers
÷ A statement trigger is fired once on behalf of the triggering statement,
independent of the number of rows the triggering statement affects.
÷ Even if no rows are affected, trigger fired.
÷ Statement triggers should be used when a triggering statement affects rows in a
table but the processing required is completely independent of the number of rows
affected.

Row Trigger and Statement Triggers are further classified into

1. Before Trigger
2. After Trigger.

Before Triggers(PL/SQL PELA MOKLE THEN SQL PART)

Before triggers execute before the data has been committed into the
database. ...
After Triggers(PELA SQL PA6I PL/SQL)
After triggers execute after the data has been inserted or updated in
the database. Usually after triggers are used because you need access to a formula field
or the Id in the case of an insert.

EXPLAIN TRIGGER WITH

EXAMPLE. DETAILING:-

Syntax:
CREATE OR REPLACE TRIGGER <trigger name>
BEFORE, AFTER
{DELETE, INSERT, UPDATE [OF Column ...]}
ON <table name>
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN Condition] ]

The keywords and the parameters used for creating database triggers are
explained below:
OR REPLACE Recreates the trigger if it already exists. This option can be used to cha
the definition of an existing trigger without requiring the user to drop
trigger first.
Trigger Name Is the name of the trigger to be created?
BEFORE Indicates that the Oracle engine fires the trigger before executing
triggering statement.
AFTER Indicates that the Oracle engine fires the trigger after executing
triggering statement.
DELETE Indicates that the Oracle engine fires the trigger whenever a DEL
statement deletes any row of table.
INSERT Indicates that the Oracle engine fires the trigger whenever an INS
statement inserts any row in table. ow
ON Specifies the name of the table, which the trigger is to be created. A trigand
cannot be created on a table in the schema use
SYS.
SQL
ew

FOR EACH Designates the trigger to be a row trigger. The Oracle engine fires a r
ROW trigger once for each row that is affected by the triggering statement
meets the optional trigger constraint defined in the WHEN clause. If this ry.
cla
e
REFERENCING is omitted the trigger is a statement trigger.
Specifies correlation names. Correlation names can be used in the PL/
block and WHEN clause of a row triggers to refer specifically to old and
n values of the current row.
WHEN Specifies the trigger restriction. The trigger restriction contains a SQL
condition that must be satisfied for the Oracle engine to fire the trigger.
This condition must contain correlation names and cannot contain a qu
Syntax:
DROP TRIGGER <trigger name>;

declare
oper varchar2(8);
begin
if updating then
oper := 'update';
end if;
if deleting then
oper := 'delete';
end if;
/*insert the old values in the audit_client table. */
insert into aO

udNit_EclWientOvaRluDes Q(:oUldE.cSlieTnItO_nNo, :AolNd.cDlienAt_NnmS,

W:olEd.Rbal_due, oper, sysd

SR.NO QUESTION ANSWER
1. WHICH TOOL USE IN CREATE SQL*PLUS
TORGGER
2 HOW MANY TYPES OF 2
TIRGGER
3 WHATIS TIRGGER? WE CAN CREATE
SEQURITY PURPOSE.
 EXPLAIN USER DEFINED DATATYPE IN ORACLE.
DETAILING:
Create Type command is used to create an abstract data type or user defines

data type.
 Syntax:
Create type <type name> as
object (
Name Data type (size),
Name Data type (size),
…,
…
);

÷ The create type command is the most important command in object-

relational databases.
÷ It will represented as having four attributes, named street, city, state, and
pin using the defined data types and lengths for each column.

Example:
Create type add_type as
object (
street varchar2(50),
city varchar2(20), state
varchar2(20), pin
number(6)
);

You can also use the user defined data type in creation of other user define
data type.

Example:
Create type person_type as
object (
name varchar2(20),
add1 add_type
);

Use of Data type:

Example:
Create table
customer (
cust_id number(3),
person
person_type
);
 Inserting records in table contain user define data type:

Ex. Insert into customer values (1, person_type(‘Neel’,add_type(3, south

extension, New-Delhi, Delhi, 110001)));

Selecting records from table contain user define data type:

Ex. Select cust_id, c.person.name,c.person.address.city from customer c;

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER

1. ABSTRACT DATA TYPE IS USER DEFINE DATA

ALSO CALLED TYPE
2 CREATE TYPE COMMAND USER DEFINE DATA
ALSO CALLED TYPE
3 CAN THIS DATA TYPE PUT IN YES
TABLE?

EXPLAIN VARRAY WITH

EXAMPLE. DETAILING-:
÷ Varray is known as collector means sets of elements that are treated
as part of a single row.
÷ A varying array allows you to store repeating attributes of a record in
a single row.
÷ For example, suppose Dona wants to track which of her tools
were borrowed by which of her worker.
÷ You should create a table to keep track of this.

Example:
Create table borrower
(
name varchar2(25),
tool varchar2(25)
);

÷ Thus, if a single worker borrowed three tools, the worker’s name

would be repeated in each of the three records.

÷ Collection such as varying arrays allows you to repeat only those

column values that change, potentially saving storage space.
Creating a Varray:
÷ You can create varray based on either an abstract datatype or one of
the oracle standard data type.
÷ To create varray use the ‘as varray ( )’ clause of the create type command.

Syntax: Create or replace type <type name> as varray (size) of data type (size)

Example: Create or replace type tools_va as varray (5) of varchar2 (20);

Example:
Create table borrower
(
name varchar2(25),
tool tools_va );
Inserting records into Varray (
):
÷ When a data type is created, the database automatically creates a
method called a constructor method for the data type.
÷ Since a varying array is an abstract data type, we need to use
constructor method to insert records into tables that use varying arrays.

Example:
÷ ThisIinsert into bfiorsrrtoswp ecrifviaelsutehse(‘vNaelueel’,otof
othlse_vna(m‘heamcomluemr’n,’.ax’,’sledge’,null,null));
÷ Because the name column is not a part of abstract data type.

Selecting data from varray ( )

÷ The maximum number of entries per row, called its LIMIT, and the current
number of entries per row, called its COUNT.
÷ This query cannot be performed directly vie a SQL select statement.
÷ To retrieve the COUNT AND LIMIT from varray you need to use PL/SQL.

ur
Os NE WORD QUESTION AND ANSWER
SR.NO QUESTION ANSWER
1. VARRAY MEANS - COLLECTOR
2 HOW MANY TYPES OF 255
ATTRIBUTE ARE STORE
IN ONE LINE
3 VARRAY MEANS VARRIYING ARRAY.

EXPLAIN NESTED TABLE WITH EXAMPLE.

DETAILING:
>;
÷ Varray have a limited number of entries, a second type of collector is
“Nested Table”.
÷ Nested Table has no limit on the number of entries per row.
÷ A nested table is, as its name implies, a table within a table.
÷ It is a table that is represented as a column within another table.
÷ You can have multiple rows in the nested table for each row in the
main table.

Syntax :
Create or replace type < table name> as table of <user define type>
Create table <table name>
(
columnname datatype(size),
columnname nested table
)
nested table <column name contain nested table data type> store as <table name
Example:
Create or replace type stud_ty as
object (
name varchar2(15),
add1 varchar(30),
city varchar2(15)
);
create type stud_nt as table of stud_ty;
create table studrec
(
stream varchar2(15),
studdet stud_nt
)
nested table studdet store as stud_tab;
 Inserting record into nested table:
Insert into studrec values (‘BCA4’,stud_nt(stud_ty(‘xyz’,’jagnath
plot’,’rajkot’),
stud_ty(‘abc’,’kalawad
road’,’rajkot’),
stud_ty(‘pqr’,’university
road’,’rajkot’)
));

÷ Varray cannot be indexed, while nested table can be indexed.

÷ In varray, the data in the array is stored with the rest of the data in the table.
÷ While in nested table, the data may be stored out-of-line.

SELECTING ROW FROM NESTED TABLE

÷ To support queries of the columns and rows of a nested
table, oracle provides a special keyword, THE.

EXAMPLE:
Select name from the (select studdet from studrec where stream
like 'BCA4')

ONE WORD QUESTION AND ANSWER

SR.NO QUESTION ANSWER
1. NESTED TABLE MEANS COLLECTOR
2 NESTED TABLE ALSO TABLE WITHIN TABLE
CALLED
3 CAN LIMIT APPLY IN N.T. NO