What is NOCOPY?
By default the IN parameter
is passed by reference and the OUT and IN OUT parameters are passed by value.
NOCOPY: is a compiles
hint that can be used with OUT and IN OUT parameter to request to pass by
reference. This improves the performance with OUT and INOUT parameters.
***************************************************************
Advantages of stored functions and procedures
*Applications can be modularized.
*Easy maintenance.
- Routines
can be modified online without interfering other users.
- One
routine can be modified to effect multiple applications.
* Improved data security and integrity.
- Indirect
access to database objects can be controlled from non-privileged users with
security privileges.
* Improved performance.
- Reparsing
for multiple users can be avoided by exploiting the shared SQL area.
- PL/SQL
parsing at run-time can be avoided by pursing at compile time.
- Number of
calls to the database can be reduced and network traffic decreased by bundling
commands.
***************************************************************
Difference between a function and a procedure
Functions
Procedures
* Invoke as a part of an expression.
Execute as PL/SQL statement.
* Must contain a RETURN clause in the
header. Do not contain a RETURN
Clause in the header.
* Must return a
value.
Can return none, one or many
values.
* Must contain at fast one RETURN
Can contain a RETURN
Statement.
Statement.
* Do not contain OUT and INOUT
Can contain IN, Out and
IN OUT
parameters.
IN OUT Parameters.
***************************************************************
About Cursors
- Oracle server
uses some private work areas to execute SQL statements and to store processing
information.
* By using PL/SQL cursors these private SQL
areas can be named and the stored information can be accessed.
Two Types:
* Implicit Cursors.
- Implicit
cursors are declared by PL/SQL implicitly for all DML and PL/SQL select
statements, including queries that return only one row.
- Oracle Server
implicitly opens a cursor to process each SQL statement not associated with on
explicitly declared cursor.
- The most
recent implicit cursor can be returned as the SQL cursor.
* Explicit
Cursors
- For queries
that return more than one row, explicit cursors are declared and named by the
programmes and manipulated through specific statements in the block’s
executable actions.
- Explicit
cursors are used to individually process each row returned by a multiple-row
SELECT statement.
- The set of
rows returned by a multiple – row query is called as active set.
Declare
Open
Fetch
Empty?
Close
Cursor Attributes: -
Attribute
Type
Description
% Is open Boolean
Evaluates to TRUE if the cursor is open.
% Not found
Boolean Evaluates
to TRUE if the most recent
fetch doesn’t return a row.
% Found
Boolean Evaluate to
TRUE if the most recent
fetch
returns a row. Complement of % not
found.
% Row Count
Number Evaluates
the total number of rows
returned so far.
Parameterized Cursors:-
- Parameters
can be passed to the cursor in a cursor for loop.
- It allow to
open and close an explicit cursor several times in a block, returning a different
active set on each occasion for each execution, the previous cursor is closed
and reopened with a new set of parameters.
- Sizes should
not be mentioned for the data types of parameters the parameters names are for
references in the query expression of the cursor.
***************************************************************
What is % Row type
- % Row
types is used to declare a record based on a collection of columns in a
database table or view.
- The fields in
the record take their names and data types from the columns of the table or
view.
- The record
can also store an entire row of data fetched from a cursor or cursor variable.
- % Row type
should be prefixed with the database table.
Ex: Declare
Emp_record employee%
row type.
Then emp_record will have a structure consisting of all the fields
each representing a column in the employees table.
***************************************************************
What is a Ref Cursor?
- Oracle server
uses unnamed memory spaces to store data used in implicit cursors.
- Ref cursors
are used to define a cursor variable, which will point to that memory space and
can be used like pointers in SQL ‘S’.
***************************************************************
About Exceptions
- An exception
is an identifier in PL/SQL that is raised during the execution of a block that
terminates its main body of actions.
- A block
always terminates when PL/SQL raises an exception so that an exception handler
should be specified to perform final actions.
* Exception can be raised in two ways exception is raised
automatically.
- Ex:- when no rows are retrieved from the database in a SELECT statement, then error ORA-01403 occurs and the exception NO-DATA-FOUND is raised by PL/SQL.
- Ex:- Exception can be raised explicitly by issuing the RAISE statement with in the block.
- The exception
being raised may be either.
User-Defined or Pre Defined
Trapping an exception:-
- If the
exception is raised in executable section of the block, processing branches to
the corresponding exception handler in the exception section of the block.
- If PL/SQL
successfully handles the exception, then the exception doesn’t propagate to the
enclosing block or calling environment.
- The PL/SQL
block terminates successfully.
Propagating an exception:-
- If the exception is raised in the executable section of the
block and there is no corresponding exception handler, the PL/SQL block
terminates with failure and the exception will be propagated to the calling
environment.
Types of exceptions:-
A) Pre-Defined Oracle Server Exceptions. -à Implicitly Raised.
B) Non-Pre defined Oracle server exceptions. -à Implicitly Raised.
C) User-defined exceptions -à Explicitly Raised.
Pre-Defined Oracle Server Exceptions: -
- These are the
error (20) that occurs most often in PL/SQL code. These exceptions need not be
declared and raised implicitly by Oracle Server, NO-DATA-FOUND, LOGIN_DENIED,
ZERO_DIVIDE.
Non-Pre-Defined Oracle Server Exceptions:-
- These are the
other standard Oracle Server errors.
- These
exceptions need to be declared ion the declarative section and raised by Oracle
server implicitly.
User Defined Exceptions: -
These are the conditions that the developer determines as
abnormal.
These need to be declared and raised explicitly.
PRAGMA EXCEPTION_INIT
Statement is used to associate a declared exception with the
standard Oracle Server error number.
Syntax:- PRAGMA EXCEPTION_INIT (exception, error number)
* SQLCODE, SQL ERRM are two functions which can be used to
identify the associated error code or error message when exception occurs.
- SQLCODE
function returns the numeric value for the error code.
- SQLERRM
function returns the character data containing the message associated with the
error number.
- SQLCODE f
SQLERRM cannot be used directly in SQL statements.
***************************************************************
What is Dynamic SQL?
- Dynamic SQL
is a SQL statement that contains variables that can change during runtime.
- It is a SQL
statement with placeholders and is stored as a character string.
- Dynamic SQL
enables DDL, DCL or session control statements to be written and executed (by)
from PL/SQL.
* Dynamic SQL can be written in two ways.
A) DBMS_SQL. -à 8i
B) Native Dynamic SQL. -à 8i
- Basically
Dynamic SQL means creating the SQL statements dynamically at runtime by using
variables.
Ex:- Dynamic SQL can be used to create a procedure that operates
on a table whose name is not known until runtime or to execute DDL/DCL/SCS
statements.
----à In Pl/SQL such statements cannot be executed statically.
--à EXECUTE IMMEDIATE Statement can perform dynamic single row
queries.
Declare
D_str varchar2 (200);
Val varchar2 (20);
Begin
D_str= insert into table1 values (;val);
Val= ‘ Bye’ Execute Immediate str using val;
***************************************************************
What is Bulk binding of Bulk collect?
Bulkbind:-
- The
assignment of values to PL/SQL variables in SQL statements is called binding.
- The binding
of an entire collection at once is refilled to as bulk binding.
- Bulk bind
improves performance by minimizing the number of context switches between
PL/SQL and SQL engines while they pass an entire collection of elements
(varray, nested tables, index-by table or host array) as bind variables back
and forth.
- Prior to
Oracle 8i, the execution of every SQL statements required a switch between the
Pl/SQL and SQL engines, where as bulk binds use only one context switch.
* Bulk binding includes the following
A) Input collections; use the FORALL statement.
B) Output collections, use the BULK COLLECT clause.
Input Collections:-
- Input
collections are data passed from Pl/SQL engine to the SQL engine to execute
INSERT, UPDATE and DELETE statements.
Syntax:- FORALL index in lower_bound.. upper_bound sql_statement;
Output Collections:-
- Output
collections are the data passed from the SQL engine to the PL/SQL engine as a
result of SELECT or FETCH statements.
- The keyword
BULK COLLECT can be used with SELECT INTO, FETCH INTO and RETURNING INTO
clauses.
Syntax:- BULK COLLECT into collection_name, ……
***************************************************************
What are Materialized Views and Snapshots?
Materialized View:-
- A
Materialized view is a replica of a target master from a single point in time.
- In Oracle 7,
it is termed as SNAPSHOT
- Oracle 7.1.6
--à Uptable Snapshots
- Oracle 7.3 -à
Primary Key Snapshots
- Oracle 8 -à
Materialized view
- Oracle 9 -à
Multifier Materialized View.
- Materialized
views can be used both for creating summaries to be utilized in data warehouse
environments.
* Replicating data in distributed environments.
Target Master -à The table(s) which is (are) referenced by the
MVIEW query.
Best Table -à The tables are that is (are) created by MVIEW create
statement and that stores data that satisfy the MVIEW query.
Syntax:- Create materialized view
Refresh fast
Start with sysdate
Next sysdate +1 as
Select *from ;
- Since this is
a fast refreshed MVIEW the master table should have a log (Master log) to
record the changes on it that can be created by running.
Create materialized view log on master_table;
-à This statement creates the following objects
- a table called
MLOG$_Master_table
- an internal
trigger on Master_table that populates the log table.
* Master Log tables (MLOG$) are used by fast refresh procedure.
Refreshing Materialized Views:-
- Initially a
materialized view contains the same data as in the master table.
- After the
MVIEW is created, changes can be made to the master table and possibly also to
the MVIEW.
- To keep a
MVIEW data relatively current with the data in the master table, the MVIEW must
be periodically refreshed.
* Refresh can be accomplished by one of the following procedures.
Dbms_mview.refresh (, )
Dbms _ refresh.refresh ()
Refresh Types -à Complete Refresh, Fast Refresh, Force Refresh
* Complete Refresh is performed by deleting the rows from the
snapshot and inserting the rows satisfying the MVIEW query.
* In Fast refresh only the rows updated since last refresh are
pulled from the master table to insert into MVIEW.
* This requires a log table called as MVIEW Log to be created on
the Master Table.
* Force refresh first tries to run a Fast refresh if possible.
* If fast refresh is not possible, it performs complete refresh.
Refresh Groups
- These are
used to refresh multiple snapshots in a transitionally consistent manner.
- When a
refresh group is refreshed all MVIEWS in that group are populated with data
from a consistent point in time.
- Refresh
groups are managed by using the procedures in the package DBMS – REFRESH.
- DBMS-REFRESH,
MAKE of DBMS-REFRESH.ADD are used to create a refresh group and add new snap
shots to an existing group.
Types of Materialized Views:-
1 Read-only
materialized views
- DML cannot be
performed on the snapshots in this category.
2 Up datable
materialized views
- These MVIEWS
eliminates the restriction of DML’s on snapshots.
3 Sub query
materialized views
- These are the
MVIEW’S that are created with sub queries in the WHERE clause of a MVIEW query.
4 Row id Vs
Primary Key materialized views
- MVIEW’S that
use Row id for refresh are called Row id MVIEW’s (Oracle 7).
- MVIEW’S that
use primary key for refresh are called primary key MVIE’S (Oracle 8).
* Fast refresh requires some association (mapping) Between rows at
snapshot and master tables.
5 Multifier materialized views (Oracle 9)
- In this type
MVIEW, its master table itself is a MVIEW.
- This feature
enables fast refresh of MVIEW’S that have MVIEW’S as their masters.
- Many
companies are structured on at least three levels
A) International
B) National
C) Local
- Many nodes at
both the national and local levels are required
- The best
possible solution in such cases is to use multifier MVIEW’S.
6 Simple Vs
Complex MVIEW’S.
- MVIEW’S being
simple or complex determines whether it can be fast refreshed or not.
- A MVIEW is
fast refreshable if it is simple.
- A MVIEW is
not fast refreshable if it is complex.
- A MVIEW can
be considered CONNECT BY, INTERSECT, MINUS or UNION or UNION ALL clauses in its
destining query.
* The following data types are not supported in
MVIEW replication.
A) LONG
B) LONG RAW
C) BFILE
D) UROWID
- MVIEW’S are
typically used in data warehouse or decision support systems.
Snapshots
- Snapshots are
mirror or replace of tables in a single point of time.
- A Snapshot is
a local copy of a table data that originates from one or more remote master
tables.
- To keep a
snapshots data current with the data of its master the Oracle server must
periodically refresh the snapshot.
***************************************************************
VIEWS
- Views are
built using the columns from one or more tables.
- The single
table view can be updated, but the view with multiple tables cannot be updated.
* A snapshot contains a complete or partial copy
of a target master table from a single point in time.
- A snapshot may be read only or updatable.
How duplicate
rows are deleted?
- Duplicate
rows are deleted by using ROWID
Syntax à delete from Where ROWID not in (Select max (ROWID) from
Group by );
How do you call function and procedure in PL/SQL as well as
in SQL prompt?
isql*plus à EXECUTE <
Function/Procedure name > ;
(SQL prompt)
PL/SQL à < Procedure Name / Function
Name>;
(from another procedure)
Development Tools à ;
Difference between IN and OUT parameters.
Three types of parameters
1. IN
2. OUT 3.IN OUT
IN parameter:
- This
parameter passes a values from the calling environment into the procedure.
- This is the
default mode.
- A formal
parameter of In mode cannot be assigned a value (we IN parameter cannot be
modified in the body of the procedure)
- IN parameters
can be assigned a default value in the parameter list.
- IN parameters
are passed by reference.
OUT parameters:
- OUT parameter
must be assigned a value before returning to the calling environment.
- OUT parameter
passes a value from the procedure to the calling environment
- OUT parameter
cannot be assigned a default value in the parameter list.
IN OUT parameter:
- This type of
parameter pass a value from the calling environment into the procedure and a
possibly different value from the procedure back to calling environment using
the same parameter.
- IN OUT
parameter cannot be assigned a default value. * By default OUT & IN OUT
parameters are passed by value.
- These can be
passed by reference by using NOCOPY.
- ***************************************************************
Triggers:
- A trigger is
a PL/SQL block or a PL/SQL procedure associated with a table ,view ,schema or
the database.
- The code in
the trigger executes implicitly whenever a particular event occurs.
Two types of
triggers:
Application trigger
- Fires
whenever an event occurs with in a particular application
Database Trigger
- Fires
whenever a data event (Such as DML) or system event (such as log on or shut
down) occurs on a schema or database.
- Executes
implicitly when a data event. Such as DML on a table (insert, delete or
Update), an INSTEAD OF trigger on a VIEW or DDL statements are issued no matter
which user is connected or which application is used.
- Also executes
implicitly when some user or data base system actions occur.
- Ex. When user
logs on to the system.
When DBA shuts down the
database.
- Date base
triggers can be defined on tables and on views.
- If a
DML operations as issued on a view, the INSTEAD OF trigger defines what action
takes place, if these actions include any DML operations on tables, then any
triggers on the base tables are fired.
- Data base
triggers can be system triggers on a database or a schema.
- With a
database, triggers fire for each event for all users, with a schema, triggers
fire for each event for the specific user.
Recursive trigger:
- This is a
trigger that contains a DML operation changing the very same table.
Cascading Trigger:
- The action of
one trigger cascades to another trigger, causing this second trigger to fire.
- Oracle server
allows up to 32 triggers to cascade at any one time.
- This number
can be changed by changing the value of the OPEN - CURSORS. Data bases
initialization parameter. (default value is 50).
- * A
triggering statement should contain
1 Trigger Timing Before, After
(For Table)
Instead of (For View)
- Determines
when the trigger needs to be fired in relation to the triggering event.
2 Triggering
Event
Insert, Update, Delete
- Determines
on which the table or view causes the trigger to fire.
3 Trigger Type
Statement, Row
- Determines
how many times the trigger body executes
4 Table name
Table,
View
5 Trigger
body à PL/SQL – block
- Determines
what actions the trigger should perform.
* INSTEAD of triggers are used to provide a transparent way to
modifying views that cannot be modified directly through SQL, DML statements
because the view is not modifiable.
-à INSTEAD of triggers provide writing of Insert, Update and
Delete statements against the view.
- The INSTEAD
if trigger works invisibly in the background performing the action coded in the
trigger body directly on the underlying tables.
- INSTEAD of
trigger execute the trigger body instead of the triggering statement.
Statement Triggers
- In this type
of triggers, the trigger body executes once for the triggering event.
- This is the
default.
- Statement
trigger fires once, even if no rows are affected at all.
Row Trigger
- In this type,
the trigger body executes once for each row affected by the triggering event.
- Row
trigger is not executed if the triggering event affects no rows.
* A view cannot be modified by normal DML if the view query
contains set operators, group functions, group by, connect By, start with
clauses or joins.
Mutating Table
- A Mutating
table is a table that is currently being modified by an UPDATE, DELETE OR
INSERT statement, or a table that might need to be updated by the effects of a
declarative DELETE CASCADE referential integrity action.
- A table is
not considered mutating for statement triggers.
- A mutating
table cannot be changed because the resulting DML could change data that is in
consistent state.
***************************************************************
What is SQL Trace?
- SQL Trace is
the main method for collecting SQL execution information in Oracle collecting a
wide range of information and statistics that can be used to tune SQL
operations.
- The SQL –
Trace facility can be enabled / disabled for an individual session or at the
instance level.
- If the
initialization parameter SQL-TRACE is set to TRUE in the init.ora of an
instance, then all sessions will be traced.
- SQL-TRACE
can be set at the instance level by using the initialization parameter SQL-TRACE.
- SQL-TRACE can
also be enabled / disabled at the system/session level by using.
Alter system/session set SQL-TRACE = TRUE/FALSE.
Explain Plan
- Explain plan
command generates information that details the execution plan that will be used
on a particular query.
- Uses a pre
created table (PLAN_TABLE) in the current schema to store information about the
execution plan chosen by the optimizer.
à
Creating the plan table
- Plan table is
created by using the script utl x plan, sql
(Oracle Home / RDBMS / admin / uti x plan.sql)
Unix à $ ORACLE_HOME / rdbms / admin
- This script
creates an output table, called PLAN-TABLE for holding the output of the
explain command.
à Populating
the PLAN TABLE
- PLAN TABLE is
populated using the explain plan.
SQL> Explain Plan for select * from emp where emp no =
1000;
- This command
inserts the execution plan of the SQL statement into the plan table.
- A name tag
can be added to explain information by using the set statement_id clause.
Displaying the Execution Plan
- Once the
table has been populated, the explain info needs to be retrieved and formatted.
Number of scripts are available to format the plan table data.
$ ORACLE_HOME / rdbms / admin / utlxpls. Sql – to format serial
explain plans.
$ ORACLE_HOME/ rdbms/admin/utlxpil. Sql – to format parallel
explain plans.
* AUTOTRACE
- The AUTOTRACE
facility in SQL* plus allows analysts to view the execution pan d some useful
statistics for a SQL statement within a SQL*plus session.
- AUTOTRACE
needs to be initiated in the SQL*Plus session prior to executing the statement.
SET AUTOTRACE [OPTIONS] [EXPLAIN] [STATISTICS]
- As with the
explain plan command, to obtain an execution plan the PLAN-TABLE must be
created in the user’s schema prior to Auto Tracing.
SQL> Set Auto trace trace only explain
SQL> Select * from dual;
- To enable
viewing of STATISTICS data, the auto tracing user must have access to dynamic
performance tables.
- To achieve
this, grant PLUS TRACE role to the user.
PLUS TRACE role is created by the plus trace. Sql script
$ ORACLE_HOME / sql plus admin SYS user must run this script.
DBA can them grant the role to the users who wish to use the
AUTOTRACE.
TK PROF
- Tk prof
facility accepts as input a SQL Trace File and produces a formatted output
file.
- Tk Prof
Filename_source filename_output EXPLAIN = [user name / password] sys = [yes/no]
TABLE = [Table Name]
A) How do you add trace to a report?
- By using the
package SRW.TRACE_ADD_OPTION
B) How do you execute a specified DDL in a report?
- BY using the
package PW.DO_SQL
C) How do you generate message in reports?
- By using the packages PW.MESSAGE (Reg Num);
D) Explain BLOBS of CLOBS?
LOBà A LOB is a data type that is used to store large,
unstructured data such as text, graphic images, video, clippings etc.
* Four large object data types.
BLOB: Represents a
binary large object
CLOB: Represents a
character large object
NCLOB: Represents a multibyte character object.
BFILE: Represents a binary file store of in an os binary file outside
the database.
LOB’S à Internal LOBS (CLOB, BLOB, NCLOB)external Files
(BFILE)
- Depending on
the storage aspects and their interpretation by Oracle server.
* LONG_TO_LOB API is used to migrate LONG columns to LOB columns.
LOB’S
LOB Locator
- A table can
have multiple LOB columns
- The maximum
size of a LOB can be 4 GB
- LOB’S return
the locator
- LOB’S store a
locator in the table end data in a different segment unless the data is less
than 4000 bytes.
LOBà LOB value (real data)
LOB locator (pointer to the location of the LOB value)
- A LOB column
doesn’t contain the data and it contains the located of the LOB value.
- When a table
is created with LOB column, the default storage is ENABLE STORAGE IN ROW.
- If DISABLE
storage in Row option is used the LOB value is not stored in the ROW even if
the size is less then 4000 bytes.
Internal LOB’S
- Stored inside
the Oracle server.
- BLOB, NCLOB,
CLOB.
BFILE
- BFILE’S are
external LOB’S.
- These are
stored in OS files out side the database table spaces.
- The data type
is BFILE.
- BFILE data
file stores a locator to the physical file.
- BFILE can be
GIF, JPEG, MPEG, text or other formats.
*DBMS_LOB.READ of DBMS_ LOB.WRITE are used to manipulate LOBS.
******************************************************************************
What is sub query and correlated sub query?
Sub Query
- A Sub Query
is a SELECT statement that is embedded in a clause of another SQL statements
called the parent statement.
- Sub query
(Inner Query) returns a value that is used by the outer query.
- Scalar sub
query is a sub query that returns exactly one column value from one row.
Correlated
Sub Query
- Correlated
sub query are used for row-by–row processing.
- Each sub
query is executed once for every row of the outer query.
- A correlated
sub query is one way of reading (data) every row in a table and comparing
values in each row against related data.
- Oracle server
performs correlated sub query when the sub query references a column from a
table in the parent query.
- The inner
query is driven by the outer query in correlated sub queries.
- 
A correlated sub query is evaluated once for
each row processed by the parent statement.
A correlated sub query is evaluated once for
each row processed by the parent statement.
GET
Candidate row from the outer query
 |
|||
 |
|||
EXECUTE
Inner query using
the candidate row value
|
|
|||
 |
|||
USE
Values from inner query to qualify / disqualify candidate row
Ex:- Select last_name, salary, department_id from employers OUTER
where salary> (select
AVG (Salary) from employees
where department id =
outer.department_id);
Types of Joins
1 Equi Join
2 Non-Equi Join
3 Outer Join
4 self Join
Equi Join:-
- Is also
called simple or inner join.
- An equi join
is a join condition that contains equality sign.
Non Equi Join:-
- It is a join
condition that contains something other than the equality operator.
Outer Join:-
- Outer joins
are used to also see rows that do not meet the join condition.
- Outer join
operator is (+).
- The missing
rows can be returned if an outer join operator is used in the join condition.
- The operator
(+) is placed on the side of the join that is deficient in information.
- This operator
has the effect of creating one or more null rows to each one or more rows from
the (join condition) non deficient table can be joined.
Self Join
Self join is used to join a table to itself.
Ex:- to find the name of each employer’s manager
What are the advantages of packages?
Packages bundle related Pl/SQL types, items and sub-programs in to
one container.
A package should have its specification and body, stored
separately in the database.
Specification is the interface to the applications.
A declares the types, variables, constants, exceptions, cursors
and sub programmes available for use. The body fully defines the cursors and
sub programmes and so implements the specification.
Once written and compiled the contents can be shared by many
applications.
When a packaged PL/SQL construct is called for the first time the
whole package is loaded in to memory, thus later calls to constructs in the
same package require no disk I/O.
Public package constructs can be referenced from any Oracle server
environments.
Private package constructs can be referenced only by other
constructs which are part of the same package.
Advantages
1
Modularity
2
Easier
Application Design
3
Hiding
Information by using public and private.
4
Added
functionality.
5
Better
performance.
6
Over
loading.
Procedures and functions can be over loaded i.e. creating multiple
sub programmes with the same name in the same package each taking parameters of
different number or data type.
What is Decode function?
*Decode function decodes an expression in a way similar to the
IF_THEN_ELSE logic used in various languages.
Decode function decodes expression after comparing it to each
search condition.
If the expression is the same as search, result is returned.
If the default value is committed, a null value is returned where
a search value does not match any of the result values.
DECODE function facilitates conditional inquiries by doing the
work of a CASE or IF_THEN_ELSE statement.
DECODE (column, expression, search1, result1, search2, result2,
…..);
Ex:- Select last_name, job, salary,
DECODE (‘IT_PROG’, 1.10*SALARY,
‘ST_CLERK’, 1.15*SALARY,
‘ST_REP’, 1.20*SALARY,
SALARY) REVISED_SALARY from employees;
92. What are composite Data types?
* Composite
Data types are of two types
1.
PL/SQL Records
2.
PL/SQL Collections
à Index By Table
à Nested Table
à VARRAY
* Composite
data types are also known as collections
- They are
RECORD, TABLE, NESTED TABLE and VARRAY
RECORD data type:-
- A RECORD is a
group of related data items stored as fields each with its own name and data
type.
- PL/SQL
Records are similar to structures in 3GL’s
- A RECORD is
not the same as Row in a database table
- RECORD treats
a collection of fields as a logical unit.
- These are
(RECORD type) convenient for fetching a row of data from a table for processing
- RECORDS also
can be declared.
Syntax à TYPE
type name is RECORD
(field declaration,…..);
identifier type_name;
Ex:- TYPE
emp_record_type is RECORD
last_name varchar2(50),
job_id varchar2(10),
salary number(8,2));
emp_record emp_ record_type;
- fields
declared as NOT NULL must be initialized.
INDEX BY Table data types:-
* This data
type contains two components.
1. Primary key of data type BINARY_INTEGER
2. Column of scalar or record data type.
* Objects of
the TABLE type are called INDEX BY Tables
- They are
modeled as (but not the same as) data base tables.
- INDEX BY
Table is a primary key to provide the user with array-like access to rows.
- INDEX BY
table is similar to an ARRAY.
- It can be
increased in size dynamically because they are un constrained.
* There are
two steps involved in creating a INDEX BY table.
1. Declare a TABLE data type.
2. Declare a variable of that type.
- The size of
the INDEX BY Table is unconstrained increase dynamically so that INDEX BY Table
an increase dynamically, so that INDEX BY Table grows as new rows are added.
- INDEX BY
Tables can have one column and a unique identifier to that one column neither
of which can be named.
- The column
can belong to any scalar or record data type, but the primary key must be ling
to type BINARY_INTEGER.
- INDEX BY
Tables cannot be initialized at the time of its declaration and also it cannot
be populated at the time of declaration.
- An exploit
executable statement is required to initialize (populate) the INDEX BY TABLE.
INDEX BY TABLE STRUCTURE
Unique identifier
Column
|
…….
|
|
1
|
|
2
|
|
……
|
|
…
|
|
Gopi
|
|
Raj
|
|
….
|
BINARY_INTEGER
SCALOU
Syntax à TYPE
ename_table_type IS TABLE OF
Employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
-this can be reterened by
INDEX Bytable_name (primary_key_value);
- The Following methods are used with INDEX BY Tables.
1. EXISTS
2. COUNT
3. FIRST AND LAST
4. PRIOR
5. NEXT
6. TRIM
7. DELETE
INDEX BY Table of Records:_
- At a
given point of time. INDEX BY Table can store only the details of any one of
the columns of a database table
- To store al
the columns retried by a query,
INDEX BY Table of Records are used.
- Because only the table definition is needed to hold information
about all of the fields of a data base table, the table of records greatly
increases the functionality of INDEX BY Table.
Syntax à TYPE dept_table_type IS TABLE OF
Departments % ROWTYPE
INDEX BY BINARY_INTEGER;
Dept_table dept_table_type;
*% ROW TYPE attribute can be used to declare a record that
represents a row in a database table.
*The difference between the % ROWTYPE attribute and the composite
data type RECORD is that RECORD allows to specify the data types of fields in
the record or to declare new fields with new data types.
Nested Tables
* Nested Table is an ordered group of items of type TABLE.
Nested Table contains multiple columns and can be used as
variables, parameters, results, attributes and columns.
They can be thought of as one column database tables.
Rows of a nested table are not stored in any particular order.
The size of a nested table can be increased dynamically i.e.
nested tables are unbounded.
Elements in a table initially have consecutive subscripts, but as
elements are deleted, they can have non-consecutive subscripts.
The range of values for nested table subscripts is 1..2147483647.
To extend a nested table, the built-in procedure EXTEND must be
used.
To delete elements, the built-in procedure DELETE must be used.
An uninitialized nested table is automatically null, so the IS
NULL comparison operator can be used to sees if nested table is null.
The operators CAST, THE and MULTISET are used for manipulating
nested tables.
1. Creation of a Nested Table
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
(ELEM_ID Number (6),
PRICE Number (7,2));
/
2. Create a
table type ELEMENTS_TAB that stores ELEMENTS objects.
SQL> Create TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS
/
3. Create a
data base table STORAGE having type ELEMENTS_TAB as one of its columns.
SQL> Create Table STORAGE
(Saleman number(4),
Elem_id number(6),
Ordered Date,
Items Elements_Tab)
NESTED TABLE ITEMS STORE AS ITEMS_TAB;
VARRAYS: -
VARRAYS are ordered group of items of type VARRAY.
VARRAYS can be used to associate a single identifier with an
entire collection.
This allows manipulation of the collection as a whole and easy
reference of individual elements.
The maximum size of VARRAY needs to be specified in its type
definition.
The range of values for the index of a VARRAY is from 1 to the
maximum specified in its type definition.
If no elements are in the (table) VARRAY, then the VARRAY is
automatically null.
The main use of VARRAY is to group small of uniform-sized
collection of objects.
Elements of a VARRAY cannot be accessed individually SQL, although
they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
The type of the element of a VARRAY can be any PL/SQL type except
the following.
BOOLEAN, TABLE, VARRAY etc.
VARRAYS can be used to retrieve an entire collection as a value.
VARRAY data is stored in-line, in the table space as the other
data in its row.
When a VARRAY is declared, a constructor with the same name as the
VARRAY is implicitly defined.
The constructor creates a VARRAY from the elements passed to it.
A VARRAY can be assigned to another VARRAY, provided the data
types are the exact same type.
TYPE my_VARRAY1 IS VARRAY (10) OF MY_Type;
Is NULL comparison operator can be used to see if a VARRAY is
null.
VAARAYS cannot be compared for equality or in equality.
Creating a VARRAY:-
1. Defining object type ELEMENTS
SQL> Create TYPE MEDICINES AS OBJECT
(MED_ID NUMBER (6),
MED_NAME Varchar2 (14),
MANF_DATE DATE);
/
2. Define a VARRAY type MEDICINE_ARR which stores MEDICINES.
objects
SQL> Create TYPE MEDICINE_ARR AS VARRAY (40)
OF MEDICIES;
/
3. Creating a relational table MED_STORE which has MEDICINE_ARR as
a column type
SQL> Create table MED_STORE (
Location varchar2 (15),
Store_Size number (7),
Employees number (6),
Med_Items Medicine_Arr);
Differences between nested tables and Varrays
*Nested Tables are unbounded, where as Varrays have a maximum
size.
*Individual elements can be deleted from a nested table, but not
from a Varray.
Therefore nested tables can be spares, where as Varrays always are
dense.
*Varrays are stored by Oracle in-line (in the same table space),
where as nested table data is out-of-line in a store table, which is a system
generated data base table associated with the nested table.
*When stored in the database, nested tables do not retain their
ordering and subscripts, where as Varrays do.
*Nested tables support indexes while VARRAYS do not
No comments:
Post a Comment
Thanks for your comments submitted.,will review and Post soon! by admin.