Oracle Practical file

INTRODUCTION TO THE ORACLE
Oracle consists of a comprehensive set of application building &
end-user products, aimed at providing complete information
technology solutions .Oracle applications are portable across wide
range of plateforms & operating systems , from personal computer
to large parellel processors.
Oracle provides a flexible Database Management System (DBMS)
- the Oracle Server - for storage & management of information
used by applications .
ORACLE SERVER
The Oracle server is used to store & manage the information by
applications. The latest oracle server is Oracle 9i manages a
dbase with all the advantages of relational structure , plus ability to
store & execute dbase objects such as procedures & triggers.
It has following features:-
1. Storage of data in designated dbase areas.
2. Retrieval of data for applications , using appropriate optimization
techniques.
3. Database security, & the tasks perfmitted for specific users.
4. Consistency & protection of data.
5. Communication & integration of data where dbases are disributed
across a network..
Oracle applications may run on the same computer as the oracle
server. Applications and tools supporting them may be run on
another system.In the client -server computing environment , a
wide range of computing resource may be used .
Oracle server options :-
1. procedural option:- It provides a PL/SQL version 2 engine with
in the Oracle server , which includes the ability to store dbase
procedures , functions & triggers for use by application.
2. Distributed option :- It supports transactions which update data
across more than one dbase in a distributed dbase network ,
employing two-phase commit .
3. parallel option :- It supports loosely-coupled systems.
4. Trusted Oracle :- Provides a set of additional high security
features .
ORACLE PRODUCTS
1 Oracle text retrieval :- A technlogy that adds fully functional text
retrieval capabilities to an oracle dbase .
2 Pro * oracle :- A series of precompliers that allows oracle dbase
access with in C , Cobol , PL/1 , Pascal , & Ada Programming
Languages .
3 Oracle Card :- An end-user interface for producing easy-to-use
dbase applications incorporating graphics & multimedia features.
4 Oracle Case :- A family of tools to aid the analysis , design &
generation of oracle applications.
5 SQL * Plus :- A tool that allows direct & interactive use of the
SQL language to access the oracle server , using ad-hoc commands or
by runnig command files.
INTRODUCTION TO SQL * PLUS
A database management system requires a query language to enable
users to access data . Structured Query Language(SQL)- pronounced
‘Sequel’ is the language was used by the most relational database
systems.
The SQL language was developed in a prototype relational database
management system – system R-by IBM in the mid 1970 ‘ s . In 1979
, oracle Corp. introduced the first commercially available
implementation of SQL .
Features of SQL are described below :-
I. Sql is an english like language .It uses words such as select , update ,
insert , delete as part of its command set .
II. Sql is the non-procedural language . In other words Sql does not
requires to specify the method to the data .
III. Sql process sets of records rather than a single record at a time . The
most common form of records is table .
IV.Sql can be used by a range of users including DBA ‘s , application –
programmers , management personal .
QUERYING DATABASE TABLES
I. the most commonly uesd command ; it is used to retrieve the data
from the database table.
By using simple Select command : - This is
Syntax :- select columnname from tablename ;
For e . g :- select ename , empno , sal , hiredate from emp
Where ename like ‘s%’;
II. By using condtional retrieval :- The ‘ where ‘ clause is used for
this purpose.It corresponds to the restriction operator of relational
algebra.
It contains the condition that rows must meet in order to be displayed
.The ‘where ’ clause must follow the from clause .
Syntax :- select columns
From table
Where certain conditions are met .
For e . g :- If we want to retrieve the information of those employees
whose department name is ‘ Salesman ‘, then :-
S :\> select ename , empno , deptno , hiredate
From emp
Where job = ‘SALESMAN ‘ ;
OPERATORS IN ‘ WHERE ‘ CLAUSE
 BETWEEN Clause :- This operator is used to tests value between ,
& inclusive of , low & high range . for e . g :– If we want to see those
employee s whose salaries are ranging between 1000 & 2000
S :\> Select ename , deptno , empno , sal , from emp
Where sal between 1000 and 2000 ;
 IN Clause :- It test values in a specified list . If the characters & dates
are used in the list they must be enclosed in a single quotations (‘ ‘) .
To find out all employees who have one of three mgr numbers .
S:\> select em[pno , ename , sal , mgr , from emp
Where mgr IN (7902 , 7566 , 7788) ;
 IS NULL operator :- The IS NULL operator specificallly tests for
values that are NULL . So to find out all employees who have no
managers , then .
S:\> select ename , mgr
From emp
Where mgr is NULL ;
For negating we use :-
 NOT BETWEEN
 IN
 NOT IN
 . LIKE :- Like operator is used to select the rows that match a
character pattern .The character pattern matching operation may be
referred to us a ‘ wild – card ‘ search . Two symbols can be used to
construct the search string .
Symbol Represents
% any sequence of zero or more characters .
- any single character.
If we want to list the names of those employees whose name starts
from letter ‘S ’
S:\> select ename from emp
Where ename like ‘S%’;
III By using null values :- A null value is a value which is either
unavailable ,
Unassigned , unknown or inapplicable.A null value is not the same as
zero.
Zero is a number . Null values taken up by one byte of internal
storage.
Null values are handelled correctly by SQL . In order to
Achieve a result for all employees , it is necessary to convert
The null values to a number . We uese the ‘ NVL ‘ function to
Convert the null value to a non – value. For e.g : –
S:\> select ename , sal*12 +NVL(comm ,0)
Annual _sal from emp ;
IV By using DISTINCT Clause : - The ‘ Distinct ‘ Clause is used to eliniminate
the
The duplicate values in the result . Multiple columns may be specified
after
The ‘ DISTINCT ’ clause & the DISTINCT affects all selected
columns .
For e .g :- To display the distinct values of deptno & job enter :
S:\> select DISTINCT deptno , job from emp ;
This display a list of all the different combinations
Of jobs & department numbers .
V By using ORDER BY clause : - Normally , the order of rows
returns in a query .
May be used to sort the rows . ORDER BY must always be the last
clause in
The select statement . The default sort order is in ascending order :
 Numeric values lowest first .
 Date values earliest first .
 Character values alphabatically .
To reverse this order , the command word DESC is specified after the
column name in the ‘ ORDER BY ‘ clause . For e . g –
S:\> select ename , job , hiredate
From emp
ORDER BY hiredate desc ;
VI By using GROUP BY Clause : - The GROUP BY clause of
the select statement is
Used to divides rows into smaller groups . Group functions can be
used to return
Information for each group . Rows may be pre – excluded with a ‘
WHERE ’
Clause ,before dividing them into group . For e . g : –
S:\> select job , avg (sal)
From emp
Group by job ;
VII Having Clause :- The ‘ Having ‘ clause is used only with the
expressions and / or
Columns that are specified with the ‘ GROUP BY ’ clause . For e .g –
S:\> select job , sum(sal) , avg (sal) , max (sal) , min (sal) from emp
Where deptno =20
Group by job
Having avg (sal)>1000 ;
VIII By using Roll up operation : - Roll up grouping produces a result set
containing the
The regular grouped rows & the subtotal values .
The Roll up operator delivers aggregate & subaggregates for
Expressions within the group by statements . The roll up operator
Can be used by the report writers to extract statistics & summary
Information from results sets. The Roll Up operator creates
Groupings moved from right to left , along the list of columns
Specified in the GROUP BY clause .
Syntax :-
Select [column ,….] group_function
(column) from table
[where condition]
group by [ROLL UP] [group_expression]
For e .g :-
S:\> select dept_id ,job_id,sum (sal)
From employees
Where dept_id<60
Group by ROLL UP(dept_id , job_id) ;
IX Cube operation : - The cube operator is an additional switch in
group by in select statement.The Cube operator can be applied to all
aggregate functions including Avg , max , min , sum ,Count .
It is used to produce results set that are typically used for across –
tabular reports .The cube operator is used with an aggregate function
to generate additional rows in a result set .
Syntax :- select [column,…] group_function (column)
From table
[where condition]
group by [CUBE] group_by_expression ;
for e .g : -
S:\> select dept_id , job_id , sum (sal)
From emp
Where dept_id < 60
Group by CUBE (dept_id , job_id) ;
ORACLE DATATYPES
The information in a data base is maintained in the form of tables .
Each table consists of rows & columns to store data . A particular
column in a table must contain similar data , which is of a particular
type .
The different data types available are :-
1. CHAR :- To store character type data.
2. VARCHAR2 :- Similar to char but can store variable number of
characters .
3. NUMBER :- Stores fixed & floating point numbers .
4. DATE :- stores point- in – time values in a table .
5. RAW :- Used to store binary data such as graphics , sound , etc .
6. LONG :- This type of data is used to store upto 2GB of characters .
7. LONGRAW :- Contains raw binary data otherwise the same as a long column.
Data type Descripti Column
on Length
(in bytes
)
1. CHAR (n) Fixed Fixed
length For
character every
data of row in
length (n). the table
. Max.
size 255
bytes per
column /
row .
2. VARCHAR2 Varirable Variable
(n) length for each
character row ,
data . upto
2000
bytes per
column /
row .
3. NUMBER ( Variable Variable
P , S) length for each
numeric row .
data . The Max
Maximum . space
pre - required
sicion ( P) for a
is 38 . given
column
is 21
bytes .
4. DATE Fixed Fixed at
length 7 bytes
date & for each
time data row in a
ranging table .
from (Jan
1 , 4712
B.C ) to
(Jan 1,
4712 A.D
. Default
format (
DD: MM
: yy) .
5. LONG Variable Its range
legth is upto
character 2GB .
data .
AGGREGATE FUNCTIONS IN ORACLE
1 COUNT () :- The COUNT() function is used to determine the
number of rows or non NULL column values .
For e.g :-
S:\> Select count (*) from emp ;
2 SUM () :- This function is use to determine the sum of all selected
columns .
For e . g :-
S:\> Select sum ( sal ) from emp ;
3 MAX () :- This function is used to calculate largest value of all
selected values of a column .
For e .g :-
S:\> select max ( sal ) from emp ;
4 MIN () :- This function is used to calculate the smallest value of all
selected values of a column .
For e .g :-
S:\> select min (comm ) from emp ;
5 AVG () :- This function is use to calculate the average of all selected
values of a column .
For e . g :-
S:\> select avg (sal ) from emp ;
QUERYING MULTIPLE TABLES
A join is used to when a Sql query requires data from more than one
table on data base . Rows in one table may be joined to rows of
another table according to rows common values existing in
corressponding columns . There types of joins :
1. Equi join :- When two tables are joined together using quality of
values in one or more than one columns , they make an equi join .
Table prefixes are utilized to prevent ambiguity & the clause specifies
the columns being joined .
For e . g :-
S:\> select empno , ename , emp . deptno , dname
from emp , dept
where emp . deptno = dept . deptno ;
Here deptno columns exists in column name should be qualified with
the table name.
2. Cartesian join :- When no ‘ where ‘ clause is specified , each row of
one table matches every row of other table . This results in a cartesian
product .
For e .g :-
S:\> select empno , ename , dname , loc , from emp , dept ;
3. Outer join :- If there are values in one table that do not have
corressponding values in other , in an equi join that row will not be
selected by using the the outer join symbol ( + ) . The corressponding
columns for that row will have NULLs.
For e . g :-
S:\> select empno , ename , emp.deptno , dname
, loc , dept . deptno , from emp , dept
where emp .dept no (+) = dept .deptno ;
If the symbol (+ ) is placed on the other side of the
Equation then all the employees details with no corressponding
Department name & location will be displayed with NULL values in
DNAME & LOC column .
The symbol ( + ) cannot be on the both sides.
4. Self – join :- To join the table to itself it means that each row of the
table is combined with itself & with every other row of the table . The
self join can be viewed as join of two copies of the same table . The
table is not actually copied , but SQL performs the command .
The syntax of the command for joining the table to itself is almost
same as that for joining two different tables . to distinguish the
column name from one another , aliases for the actual table names are
used . Table name aliases are defined in the ‘From ‘ clause of the
query .
For e.g :-
S:\> select worker . name , manager .ename from emp WORKER ,
Emp MANAGER where WORKER . mgr = MANAGER . mgr ;
5. Non – equi join :- The relation ship between EMP & SALGRADE
table is
a non-equi join , in that no column in EMP corressponds directly to a
column in SALGRADE . The relationship is obtained using an
operator other than the equal ( = ) . To evaluate employee’s grade , their
salary must be ‘ BETWEEN ‘ any pair of the low &
Hisal ranges . for e .g :-
S:\> select ename , e . sal , s .grade , from emp E , salgrade S
Where E . sal between s.losal and s.hisal ;
SET OPERATORS
Set operators are used to combine two or more queries into one result
. Data type of corressponding columns are same . There are three
types of set operators given below :-
11 UNION :- Multiple queries can be merged together & their results are
combined , using the ‘ UNION ‘ clause . The ‘ UNION ‘ clause
marges the outputs of two or more queries into a single set of rows &
columns . The queries are all executed independently , but their output
is merged . Only a final query is ends with a semicolon ( ; ) . The
number of columns in the queries must be the same & of the same
data type . For e .g :-
S:\> select job from emp
Where dept no= 20
UNION
S:\> select job from emp
Where dept no = 30 ;
12 INTERSECT :- The ‘ INTERSECT ‘ operator displays the jobs
which are common to department 10 & 20 . For e . g :-
S:\> select job from emp where deptno = 10
INTERSECT
S:\> select job from emp where deptno= 20 ;
13 MINUS :- The ‘MINUS’ operator display the list of those jobs which
are unique to department 20 . For e . g :-
S:\> select job from emp where deptno = 10
MINUS
S:\> select job from emp where deptno = 10
MINUS
S:\> select job from emp where deptno = 30 ;
NESTED QUERIES
When one query is defined in another query is called nesting queries .
It has following features which are given below :-
1 The result of one query is dynamically substituted in the condition
of another .
2 Sql first evaluates the inner query / or sub query with in the ‘
where ‘ clause .
3 The return value of inner query is then substituted in the condition
of the outer query .
4 There is no limitation to the level of nesting of queries in ORACLE 8
.
5 When using relational operators , ensure that the sub – query return
a single row output
6 In some cases , the ‘ DISTINCT ‘ clause can be used to ensure
single valued output .
For e . g :-
1 Select ename from emp where dept no = (select deptno from
emp where ename = ‘ MILLER ‘) ;
2 Select ename from emp
Where sal = (select max ( sal) from emp ) ;
3 Select ename from emp where empno IN ( select empno from incr ) ;
FUNCTIONS
Functions make the basic query block more powerful , & are used to
manipulate data values . Functions are used to manipulate data items .
They accept one or more than one arguments & return one value .
Functions can be used to :-
1. Performs calculations on data .
2 Modify indidvidual data items .
3 Manipulate output for group of rows .
4 Alter date formats for display .
5 Convert column data types .
Functions are of different types :-
1) Character Functions :- Single row character function accept
character data as input & can return both characters & number
values . The following character functions are :-
a) LOWER ( col | value ):- This function forces alpha
charcter values which are in uppercase or mixed case
into lower case . For e . g :-
S:\> select LOWER ( Dname ) , LOWER( ‘ Sql
Course ‘) from dept ;
b) UPPER :- This function is used to force the user input
to capitals
For e . g :- S:\> select ename
From emp where ename = UPPER (& Ename ) ;
c) INITCAP :- This function is used to captilized the
first letter of each
Word of the string . For e . g :-
S:\> select INITCAP ( Dname ) , INITCAP (Loc)
From dept ;
d) CONCAT :- This function is used to concatenate the
two words .
For e . g:- S:\> Select CONCAT (ename , job ) “ JOB
“
From emp where empno = 7900 ;
e) SUBSTR :- This function returns the string of n
characters form the
Column & literal value , starting at the position
number pos . For e . g :- S:\> select substr ( ‘
ORACLE ‘, 2 , 4 ) , substr ( ‘ Dname ‘ , 3 , 5 )
From dept ;
f) LTRIM :- This function removes from left leading
occurences of
Char specified . If char is not specified will trim any
blanks from the
Left where ‘ CHAR / s ‘ is treated as a set not as a
string .For e .g :-
S:\> select dname , LTRIM ( dname , ‘ A ‘ ) , LTRIM
(dname , ‘ AS’) , LTRIM (dname , ‘ASOP ‘) from dept
;
g) RTRIM :- This function removes from the right trailing
occurrences of
Character specified . For e .g :-
S:\> select dname , RTRIM (dname , ‘A’) , RTRIM (dname , ‘
GHS’ ) , RTRIM ( dname , ‘ N ’) , from dept ;
2) NUMBER FUNCTIONS :- Number function accept numeric accept
numeric input & return the no of numric values . The number of numeric
functions are :-
1 ROUND () :- Round function is used to round the column
expression or value to n decimal places . For e . g :-
S:\> select ROUND(2.456 , +2 ) from dual ;
2 TRUNC () :- The ‘ TRUNC ‘ function is used to
truncate the column or values to n decimal places . For e
. g :-
S:\> select TRUNC(2.678 , +1) from dual ;
3 CEIL ():- The ‘CEIL ‘ function is used to find smallest
integer greater than or equal to the column , expression
or value . For e .g :-
S:\> select CEIL( sal ) , CEIL (99.9 ) , CEIL ( 101.6 )
from emp ;
4 POWER () :- Raises the column / expression to the nth
power . Can also be negative . For e . g :-
S:\> select sal , power ( sal , 4) from dual ;
5 EXP ():- Returns the e raised to the nth power . e =
2.71828183 for e.g :- S:\> select EXP (4) from
dual ;
3. SQRT ():- It finds the square root of the any value specified in
brackets . For e.g :-
S:\> select SQRT(144 ) from dual ;
7. MOD () :- The ‘ MOD ‘ function is used to find out the
remainder of value 1 divide by value 2 . for e.g :-
S:\> select mod ( 12 ,3 ) from dual ;
3) DATE FUNCTION :- Date functions are operate on oracle
dbases . All date function returns the value of DATE
datatype except month_between which returns a
numeric value .Types of date functions are :-
1. SYSDATE :- Sysdate is a pseudo column that returns
the current date & time . It selects sysdate from a table called
dual table . For e .g :-
S:\> select sysdate from sys.dual ;
2. MONTHS_BETWEEN :- Finds the number of months
between date 1 & date 2 . The rows can be +ve & -ve , If
date 1 is later than date 2 . For e . g :-
S:\> select months_between ( sysdate , hiredate) ,
months_between ( ‘ 01-JAN – 84 ‘, ‘ 05 – NOV – 88 ‘) from
emp
Where months_between (sysdate , hiredate ) > 59 ;
3. ADD_MONTH :- Add n number of calender months to
date .n must be an integer & can be negative . For e .g :-
S:\> select hiredate , add_months ( hiredate , 3) ,
4. NEXT_DAY :- It displays date of the next specified
day of the week following date1. For e . g :-
S:\> select hiredate , next_day (hiredate , ‘ Friday ‘ ) ,
Next_day (hiredate , 6 ) from emp ;
5. ROUND (Date1 , ‘ Month ‘ ) :- This function returns
the first of the month date 1 if date1 is in the first half of the
month ; otherwise returns the first of the following month .
For e . g :-
S:\> select sysdate , round (sysdate , ‘ month ‘) from sys.dual ;
6. TRUNC :- This function finds the date of the first day of the
month containing in date1 when char = ‘ month ‘. For e . g :-
S:\> select sysdate , trunc (sysdate , ‘month ‘)
, trunc ( sysdate , ‘ year ‘) from sys.dual ;
7. TO_CHAR :- This function is frequently used to change date
format from the default to an alternative display format . For e . g :-
S:\> select to_char (sysdate , ‘ DAY , DDTH ,MONTH
YYYY ‘ ) from sys .dual ;
8. TO_NUMBER :- The to_number function is used to
transform a number store as a character to number datatypen .For e
. g :-
S:\> select empno , ename , job , sal
From emp
Where sal >= To_number ( ‘ 1500 ‘) ;
DATA MANIPULATION & CONTROL -_I
1) DDL COMMANDS :- DDL stands for ‘ Data Definition Language ‘.
DDL is the subset of sql commands use to create , modify , or remove
dbase structure , including tables . These commands have an immediate
effect on the database , & also record in the data dictionary . It include the
following set of commands :-
CREATE TABLE COMMAND :- The create table command is used to
create the table . It is the one of the simplest forms of this command is
where the basic information for each column defined together with their data
types & sizes .
Syntax :-
Create table table name
( column_ name type (size) ,
column_name type (size ) ,
);
For e . g :-
Create table item
( item_no number ( 5 ) ,
item_code varchar2 (4 ) ,
item_name varchar2 (20 ) ,
item_price number (5 , 2) ) ;
1 ALTER TABLE COMMAND :- Alter table command is used to add
a column in a database table
Syntax :-
Alter table table name add ( column_name type (size ) ) ;
For e . g :-
S:\>Alter table emp add ( spouses_name varchar2 ( 20 )) ;
2 DROP TABLE / COLUMN COMMAND :- We can delete
entire datatbase table using drop table command . We can
delete column by using the drop table command .
For e . g :-
S:\> drop table emp ;
S:\> drop emp empno ;
15 DML COMMANDS :- DML stands for ‘ Data Manipulation Language ‘
commands . The SQL DML includes a query language base on both the
relational algebra & the tuple of the relational calculus . It includes
following DML commands :-
3 INSERT COMMAND :- We can insert values into the dbase
table via ‘INSERT ‘ command . It is possible to a new row
with values in an each column , in which case the column list is
not require .
Syntax :- S:\.> insert into table name (column name 1 ,
Column name 2 , Column name 3 , …)
Values (value 1,value 2 , value 3 , … ) ;
For e . g :- S:\> insert into emp ( empno , ename , sal )
Values ( 7902 , ‘ pankaj singh ‘ , 7000 ) ;
When values are provided to the insert into command it is
Called a single row insert .
For e. g :- S:\> insert into emp Values ( 7311 , ‘ TOM ‘ , ‘
CLERK ‘ , 7001 ,
‘ 18 – Apr – 95 ‘, 3000 , NULL , 20 ) ;
4 UPDATE Command :- Columns in a table are updated using
the update command . Values of a single column or group of
columns can be updated . Updation can be carried out for all the
rows in a table or selected rows .Expressions can also be with
update .
Syntax :-
Update table (alias )
Set column = expression
Where condition
For e . g :-
S:\> update emp
Set job = ‘SALESMAN ‘
hiredate = sysdate
where ename = ‘ SCOTT ‘ ;
5 DELETE COMMAND :- Rows can be deleted from the table
using the delete command . The entire row is deleted from the
table . A set of rows can be deleted from the table .
Syntax :-
DELETE from table
[ Where condition ]
For e . g :-
S:\> delete from emp
Where ename = ‘ SCOTT ‘ ;
VIEWS
A view is a virtual table & is like a window to a table . It doesnot have any
data type of its own , but derives from the table it is associated with . The
advantages of the view are :-
6 Any updation of rows in the table will automatically reflect in
the view .
7 As a view does not store any data , the redundancy problem
does not arise .
8 Critical data in the database table is safeguarded as access to
such data can be controlled by using views .
9 Complicated queries are simplified using views
Views can be created using ‘ CREATE VIEW ‘ command .
Syntax :-
Create view [or replace ] [force ] view
View name [column 1 , column 2 , …] ;
For e.g :-
S:\> create view D10 emp
As select empno , ename , sal
From emp
Where deptno = 10 ;
MANIPULATING BASETABLES THROUGH VIEWS
We can manipulate the basetable through views from the following ways :-
1 By using insert , update , & delete commands can also be used with views .
2 While manipulating tables , certain rules are applicable .
3 The crieteria that determines whether the operations are througha view are :-
a) The view must be based on the single table .
b) The view must not have columns that are aggregate function .
c) The view must not specify ‘ Distinct ‘ in its Definition .
d) The view must not use group by or having in its definition .
e) The view must not use subqueries .
f) The views must not be read only view .
Droping a view
We can drop an existing view using drop view command . This command
removes the view definition from the database . Rows & columns are not
affected since they are stored on the tables from which view was derived .
Syntax :-
Drop view viewname ;
For e . g :-
S:\> drop view dept30 ;
DATA MANIPULATION & CONTROL - II
DATA BASE SECURITY & PRIVILEGES
Oraclae provides extensive security Features in order to safeguard the
user ‘s information from both unauthorized accesss & internal damage .
This security is provided by granting or revoking privileges on a user –
by – user & privilege – by – privilege basis .
Every oracle user has a username , & password & own tables , views &
other objects That he creates . An oracle role is set of privileges , which
determine the type of access that each user needs , depending upon his /
her status & responsibilities . Specific privilegs can be grant to roles &
roles are assigned to appropriate users . Database system privileges let
the user execute a specific set of commands . Database object privileges
give the user the ability to perform the certain operation on certain
specified classes of objects .
APPLICATIONS PRIVILEGES MANAGEMENT
The management of the application privileges are done by the following :-
1 By roles that collects the privileges .
2 Oracle 8 . 0 provides three standard roles : connect , resource , DBA .
3 Connect role only allows to use oracle .
4 Resource role allows to create oracle objects such as tables , views , etc .
5 DBA role has the system privileges such as creating users , creating databases , &
system administration .
6 Role can be created by
Create role <role – name >
[Identified by <password> ] .
Once the role has been created , the privileges can be granted .
GRANT < priv_list > TO < role_name >
A role is then granted to user .
When granted to the users , Roles are can be thought of as a set of
privileges . Instead of granting individual privileges to the each user , the
privileges can be granted to the role & the role is granted to the each user
. This simplifies the administrative tasks involved in the management of
pivileges .
ENHANCING PERFORMANCE
We can enhance the system performance by following ways :-
1 Indexing & clustering are two common way of
enhancing system performance in retrieving
information from the table .
2 SQL syntax does not change with these methods , as
these database objects are transparent to the user .
3 INDEXES
a ) Indexes provies a fast access path to columns
that are indexed .
b ) Index can also be used to ensure that no
duplicate values are entered into a column .
c ) Oracle analyzes each query to find out the
fastest paths to the data .
d ) Indexes are used to reference records , in all
the SQL statements . e)
Indexes are store seperately from the actual data .
f ) Indexes
do not have to be activated or deactivated .
g ) With every data
manipulation the appropriate index is automatically
updated .
SEQUENCES:- A Sequence is a database object used to generate unique
integers for use as a primary keys . A sequence is created through the ‘
CREATE SEQUENCE ‘ command .
Syntax : -
Create sequence seq_name
[ Increment by < n > ]
[ start with < m >] ;
The default increment value is 1 . ‘START WITH ‘ is the number
with which the sequence will begin . < seq_name > URRVAL
returns the current value of sequnce .< seq_name
> NEXT_VAL returns the next value of the
sequnce . It also increments the value .
For e . g :-
S:\> Create sequence empno INCREMENT by 1
start with 8890
Insert into em ( empno , ename , sal )
Values ( empno . nextval , ‘ HANS ‘ , 6000
);
MAINTAINING DATABASE OBJECTS
Data base objects are created & stored in a dbase . Tables , clusters , views
indexes , etc are the examples of a database objects . Maintainence of
database objects involves creating , altering & deleting them from the dbase
. Modifications are done using ‘ Alter Table ‘ command . Deleting is done
by using ‘ Delete Table ‘ command .
Syntax :-
ALTER TABLE < table _ name >
{ ADD ( < col – element > | < constraints > ……
[ MODIFY < col _ elements > ]
}
ALTER cluster < cluster_name >
GRANT :- The ‘ GRANT’ command is used to craete user &
grant access to the database . It requires DBA privilege , except
that user can change his / her passsword . A user can grant
access to hi database object to other user .
Syntax :-
GRANT < database _ priv [ , database_ priv ]
To < user_name >
Indentified by < password >
For e . g :-
S:\> GRANT CONNECT , RESOURCE
To thomas
Identified by enquist ;
S:\> GRANT ALL
On emp
To public ;
REVOKE :- The ‘ REVOKE ‘ command is used to revoke the
database privileges from the user by DBA . The user can be
revoke the grants to other on his objects .
Syntax :-
REVOKE < dbase priv >
FROM < user [ , user] > ;
For e.g :- S:\> REVOKE CONNECT , RESOURCE , FROM
thomas ;
S :\> Revoke update on emp from public ;
ROLLBACK :- The ‘ ROLLBACK ’ command is used to undo
the changes performed through insert , ROLLBACK ‘ command
ROLLBACK
is used to discard path or all the work the user has done in
transaction .
For e . g :-
S:\> insert into dept values ( GO , ‘ PURCHASE ‘ , ‘ DELHI ‘ ) ;
S:\> savepoint s1 ;
S :\> Update emp set deptno = 60
Where ename = ‘ SMITH ‘ ;
S :\> ROLLBACK to savepoint s1 ;
Save point identifies a point in a transcation to which one can later rollback via
‘ROLLBACK ‘ command .
COMMIT :- The ‘ COMMIT ‘ command is used to make the
changes permanent . When this command is executed . The ‘ ROLL
BACK ‘ is not use after the commit command .
For e . g :-
S:\> insert into dept values ( 50 , ‘ CORRICULUM ‘ , ‘ DELHI ‘ ) ;
S:\> select * from dept ;
S:\> commit ;
END
PL / SQL ( PROCEDURAL LANGUAGE SQL )
PL / SQL (PROCEDURAL LANGUAGE / SQL is an extension to
SQL , incorporating many of the design features of the programminng
languages . It allows the data manipulation & query statements of
SQL to be included with in the the block structure & procedural
units of code , making PL/ SQL Powerful transcation query
language .
The advantages of the PL / SQL are as following :-
1 BLOCK STRUCTURE :- PL / SQL is a block
structured language , meaninig that programs can
be divided into logical blocks , each block
containing the the language resources that are
logically required in that unit . Variables are
declared locally to the block in which they will be
used .
2 FLOW OF CONTROL :- Conditional statements ,
loops , & branches may be employed to control the
procedural flow of the program . This can avoid the
need to place commands into separate trigger –
steps .
3 PORTABILITY :- Since PL / SQL is native to
oracle , programs may be transported across any
host environment in which ORACLE & PL / SQL
are supported .
4 INTEGRATION :- PL / SQL is playing an
increasingly central role in both the RDBMS & in
ORACLE tools . The variables & dataypes of PL /
SQL are compatible with those of SQL . Therefore ,
PL / SQL bridges , the gap between convenient
access to dbase technology .
5 PERFORMANCE :- The use of PL / SQL can help
to improve the perfromance of an application .
PL / SQL BLOCK STRUCTURE
As we know that the PL / SQL is a block - structured language .
Every unit of PL / SQL compromises one or more blocks . These
blocks may be entirely separate , or nested one within another . One
block may represent just a small part of another , which in turn may
be just a part of the whole unit of code .
The PL / SQL block structure is given below :
DECLARE
< declaration >
BEGIN
< Executable Statements >
EXCEPTION
<Exception – handlers >
END ;
The BEGIN & END statements are mandatory , & enclosed the
body of the of actions to be performed . The DECLARE section is
optional & used to declare PL / SQL objects such as variables that are
to be referenced in the block , or a nested block with in it . Finally ,
the EXECPTION section is used to trap the predefined error
condtions .
DECLARING PL / SQL VARIABLES & CONSTANTS
PL / SQL supports a variety of data types which may optionally be
assigned a value when declare , and are allowed to change their
further assignments . Constants are identifiers which had a fixed value
that must be assigned when a constant is declared .
Variables are declared in the DECLARE section of the PL / SQL
block . Declaration must involved name of the variables followed by
its datatype . All statements must end with a semicolon ( ; ) .
Constants are declared by specifying the keyword CONSTANT
before the data type . The general syntax :-
Var_name type [ : = < value > ] ;
For e . g :-
Mname char (20 ) ;
Age number ( 3 ) ;
Dept no number ( 4 ) = 30 ;
To avoid the type & size conflict between a variable & the column of
the table , the attributes % TYPE is used . Advantage of this
method of defining a variable that , whenever the type & size of a
column in the table is changed , it is automatically reflected in the
variable declaration . For e . g :-
Temp_name emp.ename %TYPE ;
SCOPE & VISIBILTY OF A VARIABLE
The ‘ scope ‘ of an object is the region of the program over which
that object may potentially be used . These rules are apply to all
declared objects , including variables , cursors , user – defined
exceptions & contants .
The scope of an object is the complete block in which it is declared ,
including any sub blocks nested within it . Objects declared in
subblock are only available until that subblock has ended . For e . g :-
DECLARE
X number ;
BEGIN
...
DECLARE
Y number ;
BEGIN
...
END ;
END ;
In the example above , variable ‘ X ‘ of the subblock disable the use
of variable ‘ x ‘ from the main block until the
subblock’s variable from the main block until the subblock’s
variable reaches the end of its scope . During the subblock , the
variable ‘ x ‘ from the main block still it exists , but has no
visibilty .
ASSIGNMENTS & EXPRESSIONS
The PL / SQL assignment statements allows a value to be assigned or
reassigned to a variable after the declare section of the block . The
variable to receive the new value must be explicitly named on the left
of the assignment operator .
Syntax :-
Identifier := expression ;
Where ‘ identifier ‘ is the name of the target variable , or field , to
receive the value of ‘ expression ’.
The expression may be literal , the name of another existing variable ,
or any complexity of the expression necessary to determine the value
to be assigned . Generally , most of the expressions available in select
clause of an SQL query are available here . The functions supported in
PL / SQL expression are discuss later in this unit . For e . g :-
V_count : = v_count + 1 ;
Ann_sal : = month sal*12 + NVL ( COMM , 0 ) ;
Lev : = 6 ;
V_name : = ‘ KING ‘ ;
The assignments to boolean variables may be ‘ TRUE ‘ or ‘ FAlSE ‘
, or the result of a boolean expression .
Overpaid : = ann_sal > 35000 ;
Male : = upper ( title ) ;
Salary_ok : = not over paid ;
Female : = not male ;
Switch 1 : = TRUE ;
ORACLE PRACTICAL FILE
CREATED
BY
Varun bhardwaj
SUBMITTED BY :-
NAME :- KAMALJIT SINGH.
CLASS :- B.C.A – II
Rollno= 10739
1
2
Using commit:
A commit ends the current transaction and makes permanent any changes
made during the transaction . All transactional locks acquired on tables are
released .
Syntax:
COMMIT;
Using:
ROLLBACK
A rollback does exactly the opposite of commit . it ends the transaction but
undoes any changes made during the transaction .All transactional locks
acquired on tables are released .
syntax :
ROLLBACK[work][to[savepoint]savepoint]
Where:
Work:is optional and is provided for ansi compatibility.
Savepoint: is optional and is used to rollback a partial transaction as far as
the specified save point :
Creating save point :
Save point marks and saves the current point int the processing of a
transaction. When a save point is used with a rollback statement parts of a
transaction can be undone .an active save point is one that is specified since
the last commit or rollback .
SYNTAX :
Savepoint savepointname;
WHAT IS A CURSOR ?
The oracle engine uses awork area for its internal processing in order to
execute an sql statement. This work area is private to sql’s operations and is
called a cursor .
Example:
Select empno,ename ,job ,salary from employee where deptno=20;
:
Cursors are classified in two ways .
1.Implicit cursors.
2.explicit cursors.
When the sql statement is not associated with an explicitly declared curscr
then oracle implicitly opens a cursor to process sql statements .
2 . explicit cursor is declared within the declare section to specify the query
to be executed for the cursor .then the open statement executes the query
associated with the curs or to form the active set.