ORACLE
Chapter 1 : Introduction
Structure query language (SQL), Is this set commends that all programs and users must
use to access data within the oracle database.
Dr.E.F codd was published in June 1970.RDBMS-Relational Database Management
System.
Chapter 2 : SQL
SQL Statements
Four types
a. DDL(Data Definition Language)
b. DML (Data Manipulation Language)
c. DCL (Data Control Language)
d. TPL (Transactions Control Language)
DDL DML DCL TCL
Create select select commit
Alter insert grand rollback
Drop update revoke savepoint
Grant delete
Revoke lock table
Chapter 3: SQL*Plus
- SQL*Plus is Oracle's command-line interpreter.
- The `start' menu, SQL*Plus is listed under programs > oracle > application
- Development > SQL Plus.
- You will be prompted for your username and password.
- You can try to use scott for the username, and tiger for the password.
- You are now connected to a shared database, on which you have an account (called a
schema ).
Table :1
outlines the main Oracle SQL data types
| Table 1: The main SQL data types. | ||||||||||||||||||||||||
|
The NUMBER (p, s) type takes two arguments; precision and scale. The precision of a
number its number of significant decimal digits its scale is the number of digits after the
decimal point.
SQL Works
Ø Query in data .
Ø Inserting, updating, deleting and rows in a table.
Ø Creating, replacing, altering, and dropping objects.
Ø Controlling access to the database and its objects.
Ø Guaranteeing database consistency and integrity.
Chapter 4 : TABLE
Create table – [Table Name: empl]
create table empl(eno number(3),ename varchar2(8),DOB date,Basic number(5),
hra number(4), namt number(5));
Table created.
Table View
desc empl
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(3)
ENAME VARCHAR2(8)
DOB DATE
BASIC NUMBER(5)
HRA NUMBER(4)
NAMT NUMBER(5)
Alter Table
Additional width changes(modify)
alter table empl modify(eno number(5));
Table altered.
Table Show
desc empl;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(5)
ENAME VARCHAR2(8)
DOB DATE
BASIC NUMBER(5)
HRA NUMBER(4)
NAMT NUMBER(5)
Add Column
Alter Table empl add(grade varchar2(2));
Table Altered.
Table show
desc empl;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(4)
ENAME VARCHAR2(8)
DOB DATE
BASIC NUMBER(5)
HRA NUMBER(4)
NAMT NUMBER(5)
GRADE VARCHAR2(2)
Create Record- [Type-1]
insert into empl values(&eno,'&ename','&dob',&basic,&hra,&namt,&grade);
Enter value for eno: 100
Enter value for ename: Scott
Enter value for dob: 17-oct-83
Enter value for basic: 5000
Enter value for hra: 500
Enter value for namt: 0
Enter value for grade: null
old 1: insert into empl values(&eno,'&ename','&dob',&basic,&hra,&namt,&grade)
new 1: insert into empl values(100,'Scott','17-oct-83',5000,500,0,null)
1 row created.
/
Enter value for eno: 101
Enter value for ename: adler
Enter value for dob: 12-jan-88
Enter value for basic: 6000
Enter value for hra: 600
Enter value for namt: 0
Enter value for grade: null
old 1: insert into empl values(&eno ,’&ename','&dob',&basic,&hra,&namt,&grade)
new 1: insert into empl values(101,'adler','12-jan-88',6000,600,0,null)
1 row created.
Create Record- [Type-2]
insert into empl values(103,'sandy','14-may-78',7000,700,0,null);
1 row created.
Select a record
select * from empl;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- ----------------------------
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
103 sandy 14-MAY-78 7000 700 0
Table Rename
create table old(ename char(10),sal number(5));
Table created.
create table new as (select * from old);
Table created.
desc new;
Name Null? Type
----------------------------------------- -------- -------
ENAME CHAR(10)
SAL NUMBER(5)
desc old;
Name Null? Type
----------------------------------------- -------- -------
ENAME CHAR(10)
SAL NUMBER(5)
drop table old;
Table dropped.
Delete table
drop table empl;
Table dropped.
Delete Record
delete from empl where ename='sandy';
1 row deleted.
select * from empl;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- -------------------------
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
Roll back & Savepoint
delete from empl where hra=600;
1 row deleted.
1.Savepoint
savepoint c;
Savepoint created.
select * from empl;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
100 Scott 17-OCT-83 5000 500 0
2. Roll back
roll back to c;
Rollback complete.
select * from empl;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
Select Particular Column
select ename,dob from empl;
ENAME DOB
-------- ---------
Scott 17-OCT-83
adler 12-JAN-88
Record Update
update empl set ename='kumar' where eno=103;
1 row updated.
select * from empl;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------------------------------- --
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
103 kumar 14-MAY-78 7000 700 0
Chapter 5:Where clause
Common Comparison operators
a. Operator (=)
select * from empl where ename = 'sandy';
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
103 sandy 14-MAY-78 7000 700 0
b. Operator (! = (or) ^= (or) <>)
select * from empl where ename != 'sandy';
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
102 ravi 27-JUL-07 4500 400 0
c. Less than ( < )
select * from empl where basic < 5000;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
102 ravi 27-JUL-07 4500 400 0
d. Greater than ( > )
select * from empl where basic >5000;
ENO ENAME DOB BASIC HRA NAMT GR
---- ------ ------- --------- ---------- ---------- --
101 adler 12-JAN-88 6000 600 0
103 sandy 14-MAY-78 7000 700 0
e. Less then or equal to (<=)
select * from empl where basic <=5000;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- -------------------
100 Scott 17-OCT-83 5000 500 0
102 ravi 27-JUL-07 4500 400 0
f. Greater than or equal to
select * from empl where basic >= 5000;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- ---------------
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
103 sandy 14-MAY-78 7000 700 0
g. In
select * from empl where basic in (5000,7000);
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
100 Scott 17-OCT-83 5000 500 0
103 sandy 14-MAY-78 7000 700 0
h. Not in
select * from empl where basic not in (5000,7000);
ENO ENAME DOB BASIC HRA NAMT GR
---- -------- --------- ---------- ---------- ---------- --
101 adler 12-JAN-88 6000 600 0
102 ravi 27-JUL-07 4500 400 0
i. Between A and B
select * from empl where hra between 100 and 500;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- --
100 Scott 17-OCT-83 5000 500 0
102 ravi 27-JUL-07 4500 400 0
j. Not Between A and B
select * from empl where hra between 100 and 500;
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- -----------------
100 Scott 17-OCT-83 5000 500 0
102 ravi 27-JUL-07 4500 400 0
k. Like ‘%adler%’
select * from empl where ename like '%adler%';
ENO ENAME DOB BASIC HRA NAMT GR
----- -------- --------- ---------- ---------- ---------- ----------------------
101 adler 12-JAN-88 6000 600 0
Order by
select * from empl order by dob desc,ename;
ENO ENAME DOB BASIC HRA NAMT GR
-----------------------------------------------------------------------------------
101 adler 12-JAN-88 6000 600 0
100 Scott 17-OCT-83 5000 500 0
103 sandy 14-MAY-78 7000 700 0
Chapter 6: Functions
· Group Functions
· single Row function
Group function single Row function
 | |
Count a. number
Sum b. char
Avg c. date
Max d. Misellances
Min
Group Functions
a. Count:
select count(eno)from empl;
COUNT(ENO)
------------------
2
b.Sum:
select sum(Basic)from empl;
SUM(BASIC)
-----------------
11000
c. Avg:
select avg(hra)from empl;
AVG(HRA)
-----------------
550
d. Max:
select max(basic) from empl;
MAX(BASIC)
------------------
6000
e. Min:
select min(HRA)from empl;
MIN(HRA)
---------------
500
Single Row Function
Number Data Type
a. Addition (+)
select basic + hra from empl;
BASIC+HRA
------------------
5500
6600
7700
b. Subtraction (-)
select basic - hra from empl;
BASIC-HRA
------------------
4500
5400
6300
c. Multiplication (*)
select basic * hra from empl;
BASIC*HRA
-----------------
2500000
3600000
4900000
d. Division (/)
select basic /100 from empl;
BASIC/100
--------------
50
60
70
Number function
a. Abs
select abs(-24)from dual;
ABS(-24)
------------
24
b. Ceil
select ceil(25.17)from dual;
CEIL(25.17)
-----------
26
c. Floor
select floor(16.8)from dual;
FLOOR(16.8)
----------------
16
d. Mod
select mod(7,2)from dual;
MOD (7,2)
----------
1
e. Round
select round(25.7)from dual;
ROUND(25.7)
-----------
26
f . Sqrt
select sqrt(25)from dual;
SQRT(25)
----------
5
g. Power
select power(2,3)from dual;
POWER(2,3)
----------
8
h. Sign:
select sign(12) from dual;
SIGN(12)
----------
1
Character Data Type
a. Concat
select concat('Sandy','Kumar')from dual;
CONCAT('
-------------
SandyKumar
b. Initcap
select initcap('computer')from dual;
INITCAP(
-------------
Computer
c. Length
select length('Shanthi')from dual;
LENGTH('SHANTHI')
----------------------------
7
d. lower
select lower('HEENA')from dual;
LOWER
-----------
Heena
E. Replace
select replace('Scott','S','Boy')from dual;
REPLACE
-------
Boycott
6. Substr
select substr('WELCOME',2,3)from dual;
SUB
---
ELC
7. Upper
select upper('welcome')from dual;
UPPER('
-------
WELCOME
8.LTrim
select LTrim('dur ')from dual;
LTRIM('DUR')
------------
Dur
9.RTim
select RTrim(' dur')from dual;
RTRIM('DU
---------
Dur
10.LPad:
select LPAD('page1',15,'*')from dual;
LPAD('PAGE1',15
---------------
**********page1
11. ASCII
select ascii('R')from dual;
ASCII('R')
----------
82
Common Function Date Data Type
1.SYSdate
select sysdate from dual;
SYSDATE
---------
20-JUL-07
2.Last_day
select last_day(sysdate)from dual;
LAST_DAY(
---------
31-JUL-07
3. Add months
select add_months(sysdate,2)from dual;
ADD_MONTH
---------
20-SEP-07
4. Months_between
select months_between(sysdate,'12-mar-07')from dual;
MONTHS_BETWEEN(SYSDATE,'12-MAR-07')
-----------------------------------
4.27143407
5. Next_day
select next_day(sysdate, 'monday')from dual;
NEXT_DAY(
---------
23-JUL-07
Special format date data type
1.Y or YY or YYY:
select to_char(sysdate,'yyyy')from dual;
TO_C
----
2007
2. SYEAR or YEAR
select to_char(sysdate,'SYEAR')from dual;
TO_CHAR(SYSDATE,'SYEAR')
-------------------------------------------
TWO THOUSAND SEVEN
3.Q:
select to_char(sysdate,'Q')from dual;
T
-
3
4.MM:
select to_char(sysdate,'mm')from dual;
TO
--
07
5.RM(Roman Numarical month)
select to_char(sysdate,'RM'
2 )from dual;
TO_C
----
VII
6.Month
select to_char(sysdate,'month')from dual;
TO_CHAR(S
---------
July
7.WW(week of year)
select to_char(sysdate,'WW')from dual;
TO
--
29
8.W(week of the month)
select to_char(sysdate,'W')from dual;
T
-
3
9.DDD(day of the year)
select to_char(sysdate,'DDD')from dual;
TO_
---
201
10.DD (day of the month)
select to_char(sysdate,'DD')from dual;
TO
--
20
11.D (day of the week)
select to_char(sysdate,'D')from dual;
T
-
6
12.DY(abbreviated name of the day)
select to_char(sysdate,'DY')from dual;
TO_
---
FRI
13.HH or HH12(hour of day)
select to_char(sysdate,'HH')from dual;
TO
--
11
select to_char(sysdate,'HH12')from dual
TO
--
11
14.HH24(hour of day using)
select to_char(sysdate,'HH24')from dual
TO
--
11
15.MI(minutes)
select to_char(sysdate,'MI')from dual;
TO
--
06
16.SS(seconds)
select to_char(sysdate,'SS')from dual;
TO
--
48
Converting from one column type to another
1.To char
select to_char (8897) from dual;
TO_C
----
8897
2. To number
select to_number ('8897') from dual;
TO_NUMBER('8897')
-----------------
8897
3. To_date
select to_date('20-jul-07')from dual;
TO_DATE('
---------
20-JUL-07
Chapter 7: Sub query
select * from empl;
ENO ENAME DOB BASIC HRA NAMT GR
----- -------- --------- ---------- ---------- ---------- ----------------------
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
103 sandy 14-MAY-78 7000 700 0
102 ravi 27-JUL-07 4500 400 0
104 geetha 22-SEP-83 4500 200 0
105 geetha 13-AUG-91 5000 300 0
106 adlerk 28-JUN-87 6500 400 0
In :
select * from empl where eno in (select eno from empl where eno=101);
ENO ENAME DOB BASIC HRA NAMT GR
---------- -------- --------- ---------- ---------- ---------- ---------------------
101 adler 12-JAN-88 6000 600 0
Not in
select * from empl where eno not in (select eno from empl where eno=101);
ENO ENAME DOB BASIC HRA NAMT GR
---- -------- --------- ---------- ---------- ---------- -------------------------
100 Scott 17-OCT-83 5000 500 0
103 sandy 14-MAY-78 7000 700 0
102 ravi 27-JUL-07 4500 400 0
104 geetha 22-SEP-83 4500 200 0
105 geetha 13-AUG-91 5000 300 0
106 adlerk 28-JUN-87 6500 400 0
Greater than
select * from empl where basic > (select basic from empl where ename='adler');
ENO ENAME DOB BASIC HRA NAMT GR
----- -------- --------- ---------- ---------- ---------- ------------------------
103 sandy 14-MAY-78 7000 700 0
106 adlerk 28-JUN-87 6500 400 0
Greater than or equal to
select * from empl where basic >=(select basic from empl where ename='adler');
ENO ENAME DOB BASIC HRA NAMT GR
----- -------- --------- ---------- ---------- ---------- -------------------------
101 adler 12-JAN-88 6000 600 0
103 sandy 14-MAY-78 7000 700 0
106 adlerk 28-JUN-87 6500 400 0
Less than
select * from empl where basic < (select basic from empl where basic=6000);
ENO ENAME DOB BASIC HRA NAMT GR
------ -------- --------- ---------- ---------- ---------- ---------------------------
100 Scott 17-OCT-83 5000 500 0
102 ravi 27-JUL-07 4500 400 0
104 geetha 22-SEP-83 4500 200 0
105 geetha 13-AUG-91 5000 300 0
Less than or equal to
select * from empl where basic <= (select basic from empl where basic=6000);
ENO ENAME DOB BASIC HRA NAMT GR
------ -------- --------- ---------- ---------- ---------- ----------------------
100 Scott 17-OCT-83 5000 500 0
101 adler 12-JAN-88 6000 600 0
102 ravi 27-JUL-07 4500 400 0
104 geetha 22-SEP-83 4500 200 0
105 geetha 13-AUG-91 5000 300 0
Update condition
update emp1 set sal=sal*1.15 where job='clerk' and deptno=40;
1 row updated.
select * from emp1;
EMPNO ENAME JOB MGR HDATE SAL DEPTNO CITY PHONE
----------------------------------------------------------------------------------------------------------
100 john manager 1207 05-JAN-07 5000 10
102 mohan clerk 1980 17-JUL-07 3000 20
103 shan Gm 4576 21-JUN-07 7000 30
EMPNO ENAME JOB MGR HDATE SAL DEPTNO CITY PHONE
-------------------------------------------------------------------------------------------------------------------------------
104 guna Am 5689 12-JAN-07 6500 40 7 34566
104 badler clerk 2345 12-JAN-85 3450 40
105 shan Manager 45 23-JUL-87 6500 20 3 234563
Excises:
Create table salary:
Field:eno,ename,date,dest,deptno,loc,basic,hra,pf,da,loan,net
Insert 12 records
- calculate net salary
- find out the employees with the same job as name(eg:Ravi)
- List all the employees who earn more than all employees in sales department?
- List the employees with either the same job in all departments?
- List the employee in dept with the same job as anyone working in Chennai
- Calculate how many members greater than 10000 salary and list out details.
- Update to basic field increment RS. 500 in below 10000 salaries.
Chapter 8: Set of operators
Joins & subquries
Union
Union all
Intersect
Minus
|
| ||||
Union
Two output queries merge(duplicate elements discard)
select deptno from dept union select deptno from cust;
DEPTNO
----------
10
20
30
40
50
Union all
Duplicate elements display
select deptno from dept union all select deptno from cust;
DEPTNO
----------
10
20
30
10
20
30
Intersect
Common values display.
select deptno from dept intersect select deptno from cust;
DEPTNO
----------
10
20
30
Minus
[Different values display] Minus operators return all distinct rows. Selected only by first query and not by second query.
select deptno from dept minus select deptno from cust;
DEPTNO
----------
40
Chapter 9: Join Two Tables
Joining two tables together
Two table’s values displayed.
To define relationship between multiple table and with in a single command.
|
|
Create Object
Object –Redselect red .deptno,blue .deptno from dept red,cust blue where blue .deptno=red.deptno;
DEPTNO DEPTNO
---------- ----------
10 10
20 20
30 30
40 40
Chapter 10 Types of Joins
Simple join--- a. Equi join & Non equi join
b. Outer join
c. Self Join
Equi join:
select * from z;
AGE DNO
---------- ----------
16 10
18 20
21 30
15 40
select * from x;
NO NAME DNO
---------- -------- ----------
100 sandy 10
101 deepa 20
102 badler 30
104 kalai 50
select x1.dno,name,age,z1.dno from x x1,z z1 where x1.dno=z1.dno;
DNO NAME AGE DNO
---------- -------- ---------- ----------
10 sandy 16 10
20 deepa 18 20
30 badler 21 30
Non Equi join
Outer Join
(+) operator can appear only in the where cause, and can be applied only to a column of a table or view.
select x1.dno,name,z1.dno from x x1,y z1 where x1.dno(+)=z1.dno;
DNO NAME DNO
---------- -------- ----------
10
20 deepa 20
20 heena 20
20 deepa 20
20 heena 20
30 badler 30
40
Self Join
Type of join where the join operation takes place in the same table.
select s.name ||'works fro' || z.dname from x s ,y z;
S.NAME||'WORKSFRO'||Z.DNAME
---------------------------
sandyworks fromanager
deepaworks fromanager
badlerworks fromanager
adlerworks fromanager
vijworks fromanager
kalaiworks fromanager
heenaworks fromanager
sandyworks froclerk
deepaworks froclerk
badlerworks froclerk
adlerworks froclerk
Chapter 11
Views, Synonyms
Views :( an Exiting table copy of fields)
A view is a subset of data one or more table.
create view em as select name,dno from x;
View created.
select * from em;
NAME DNO
-------- ----------
deepa 20
badler 30
adler 60
vij 34
kalai 50
heena 20
6 rows selected.
insert into em values('mohan',10);
1 row inserted.
Synonyms
Synonym is a data base object. That is used as an alias name
for any object.
Advantages:
· Simplify SQL statements
· Hide the read identify of an object.
· Are useful in database links.
create synonym a2 for x;
Synonym created.
select * from a2;
NO NAME DNO
------- -------- ----------
101 deepa 20
102 badler 30
adler 60
3 vij 34
104 kalai 50
105 heena 20
mohan 10
7 rows selected.
Sequences
Sequence are a set of data base object which can generate sequential integer values.
create sequence aa // aa-sequence name
2 start with 2 // starting number
3 minvalue 1 // for loop initialize
4 increment by 1 // increment value
5 maxvalue 10 //maximum value up to 10
6 cycle //Looping
7 cache 5; //Memory Allocation (i.e) 5 records must be kept access
Fast
Sequence created.
select aa.nextval from dual;
NEXTVAL
----------
2
/
NEXTVAL
----------
3
:
:
/
NEXTVAL
----------
1
insert into x(no,name,dno)values(aa.nextval,'meena',56);
1 row created.
select * from x;
NO NAME DNO
---------- -------- ----------------
5 meena 56
101 deepa 20
102 badler 30
adler 60
3 vij 34
104 kalai 50
105 heena 20
mohan 10
2 meena 56
9 rows selected.
Clusters
Cluster are an optional method of storing table data
create cluster clum(dno number(3));
Cluster created.
create index red on cluster clum;
Index created.
create table m1(dno number(3),name varchar2(6)) cluster clum(dno);
Table created.
create table n(dno number(3),age number(5))cluster clum(dno);
Table created.
select rowid from cluster clum;
|
|
ROWID
------------------
AAAGFjAABAAAIV0AAA
AAAGFjAABAAAIV1AAA
AAAGFjAABAAAIV2AAA
AAAGFjAABAAAIV3AAA
AAAGFjAABAAAIV4AAA
select * from cluster clum;
DNO
----------
10
20
30
40
50
Chapter 10
Locking
Oracle automatically locks the resources on behalf of the transaction
1. Row level lock
2. Table level lock
Row level lock
select * from x where no='100' for update of dno;
NO NAME DNO
------- -------- ----------
100 ar 43
Table level lock
lock table x share update mode;
Table(s) Locked.
Chapter 11
Oracle partitioning
Partition is introduced in oracle. It is very fast.
Create table sales (oid number,oamt number(6,2))partition by range(oid)(partition p1
values less than(100),partition p2 values less than(200)));
Table created.
insert into sales values(&oid,&oamt);
Enter value for oid: 103
Enter value for oamt: 300
old 1: insert into sales values(&oid,&oamt)
new 1: insert into sales values(103,300)
1 row created.
/
Enter value for oid: 40
Enter value for oamt: 250
old 1: insert into sales values(&oid,&oamt)
new 1: insert into sales values(40,250)
1 row created.
select * from sales;
OID OAMT
---------- ----------
40 250
60 270
103 300
120 180
select * from sales partition(p1);
OID OAMT
---------- ----------
40 250
60 270
select * from sales partition(p2);
OID OAMT
---------- ----------
103 300
120 180
Collection of arrays
A collection is an order group of elements of the same data type. An element can be
determined by its unique subscript.
- Varray
create or Replace type b1 as VArray(5) of number(3);
2 /
Type created.
create table pen(pno number,rate b1);
Table created.
insert into pen values(2,b1(34,56,87,65));
1 row created.
insert into pen values(1,b1(23,45,65,32));
1 row created.
select * from pen;
PNO
----------
RATE
-----------------------------------------------------------------
2
B1(34, 56, 87, 65)
1
B1(23, 45, 65, 32)
Error
insert into pen values(4,b1(34,56,87,65,52,32));
insert into pen values(4,b1(34,56,87,65,52,32))
*
ERROR at line 1:
ORA-22909: exceeded maximum VARRAY limit
Chapter 12
PL-SQL
PL-SQL is a block structured language. The basic unit of pl-SQL is called a block which
contains declarative statements. Executable statements and error handling statements. The
blocks can be nested in one or more blocks.
1. Declarative
2. Executable
3. error handling
syntax:
[< Block header>]
[ declare
< Constants>
< Variables>
< Cursors>
< User defined exceptions>]
Begin
<PL/ SQL statements>
[ exception< Exception handling>]
end;
- Welcome statements
begin
2 dbms_output.put_line('Welcome');
3 end;
4 /
PL/SQL procedure successfully completed.
set serveroutput on;
/
Welcome
PL/SQL procedure successfully completed.
Lab Exercises
- print in your address
- print 5 countries name
2.
declare
2 s number default 10;
3 begin
4 dbms_output.put_line(s);
5 s:=20;
6 dbms_output.put_line(s);
7 end;
8 /
output:
10
20
PL/SQL procedure successfully completed.
3. Print the output
declare
2 x number;
3 begin
4 x:=35;
5 dbms_output.put_line(x);
6 end;
7 /
35
PL/SQL procedure successfully completed.
1. Enter the input
declare
2 x number;
3 begin
4 x:=&x;
5 dbms_output.put_line(x);
6 end;
7 /
Enter value for x: 24
old 4: x:=&x;
new 4: x:=24;
24
PL/SQL procedure successfully completed.
Lab Exercises
- Enter student marks (min 5 marks).
2. Add two numbers
declare
2 x number;
3 y number;
4 begin
5 x:=&x;
6 y:=&y;
7 dbms_output.put_line(x+y);
8 end;
9 /
Enter value for x: 20
old 12: &x;
new 12: 20;
Enter value for y: 10
old 13: y:=&y;
new 13: y:=10;
30
PL/SQL procedure successfully completed.
Lab Exercises
- Find out c values c=a+b,
c=a-b,
c=a*b, c=a/b ?
- calculate area?
- calculate l* b*h?
- calculate z=p*q - r + s / t *u ?
- calculate student marksheet,(tot,avg) ?
3.
|
declare
2 s number;
3 begin
4 s:=&s;
5 if s>0 then
6 dbms_output.put_line('The number is positive');
7 end if;
8 end;
9 /
Enter value for s: 36
old 4: s:=&s;
new 4: s:=36;
The number is positive
|
PL/SQL procedure successfully completed.
4. If…then …else condition
declare
2 s number;
3 begin
4 s:=&s;
5 if s>0 then
6 dbms_output.put_line('The number is positive');
7 else
8 dbms_output.put_line('The number is negative');
9 end if;
10 end;
11 /
Enter value for s: -23
old 4: s:=&s;
new 4: s:=-23;
The number is negative
PL/SQL procedure successfully completed.
Lab Exercises
- Find out the values greatest 2 numbers.
2. Create Mark Sheet? How to calculate the Total, Average, Result, Grade?
Chapter 13
Iterative control
- loop
- while
- for loop
Loop
declare
2 a number:=&a;
3 begin
|
5 dbms_output.put_line(a);
6 a:=a+1;
7 if a>10 then
8 exit;
9 end if;
10 end loop;
11 end;
12 /
Enter value for a: 2
old 2: a number:=&a;
new 2: a number:=2;
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.
Lab Exercises
- Calculate 1+2+3….n;
- Calculate square of 1+2+3….n;
While loop
declare
|
3 y integer:=0;
4 begin
5 while x<=5
6 loop
7 y:=y+x;
8 x:=x+1;
9 end loop;
10 dbms_output.put_line(y);
11 end;
12 /
15
PL/SQL procedure successfully completed.
FOR LOOP:
declare
2 x number :=50;
3 begin
4 dbms_output.put_line(x);
5 for x in 2..5 loop
6 dbms_output.put_line(x);
7 end loop;
8 dbms_output.put_line(x);
9 end;
10 /
50
2
3
4
5
50
PL/SQL procedure successfully completed.
Chapter 14
Run
Set serveroutput on;
/
| |
PL-SQL uses cursor for management of SQL select statement.
Cursor are chunks of memory allocated to process these statements
Type of cursors
- implement cursor
- Explicit cursor
Implement cursor
When the executable part of PL/SQL block issues a SQL command, PL/SQL create an implicit cursor which has the identifier SQL,PL/SQL manages this cursors.
Explicit cursor
The query is executed and the returned rows are manipulated within the cursor. Explicit cursor can be of two type.
- static cursor
- dynamic cursors.
static cursor
static cursor or a type of cursors when the select statements given at compile time itself.
dynamic cursors
dynamic cursors or the name suggests or a set of cursor where the records from the tables are selected at run time rather then compile time.
Attributes
- %Row count –the attribute yields the number of rows affected by an INSERT,UPDATE, OR
DELETE statements, or return by a SELECT into command.
- %Found- this attributes yields TRUE if ands INSERT,UPDATE, or DELETE statements
Affected one or more rows or SELECT INTO statement returned one or more rows.
Otherwise it false.
- %NotFound- This attributes is the logical opposite of %found. it yields true if and
INSERT, UPDATE, OR DELETE statements affected no rows, are a select
Into statement return no rows otherwise it yields false.
- % Isopen- this attributes always yields FALSE because closes the SQL cursor automatically after executing is associated SQL statements.
Example 1:
declare
2 n number(5);
|
4 delete emp1 where empno=103;
5 if sql%found then
6 dbms_output.put_line('Record deleted');
7 else
8 dbms_output.put_line('no matching rows');
9 end if;
10 end;
11 /
PL/SQL procedure successfully completed.
Cursor using for loop
declare
2 cursor c1 is select ename,sal from emp;
3 begin
4 for i in c1
5 loop
6 insert into final values(i.ename,i.sal); //final (table name) using two parameters.
7 end loop; name,salary.
8 end;
9 /
PL/SQL procedure successfully completed.
select * from final;
NAME SALARY
-------- ----------
badler 4000
heena 5000
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
NAME SALARY
-------- ----------
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
Cursor using Attributes
declare
2 cursor c1 is select ename,job from emp;
3 mname varchar2(10);
4 njob varchar2(12);
5 begin
6 open c1;
7 loop
8 fetch c1 into mname,njob;
9 exit when c1 %notfound;
10 dbms_output.put_line(mname ||' welcome '||njob);
11 end loop;
12 close c1;
13 end;
14 /
SMITH welcome CLERK
ALLEN welcome SALESMAN
WARD welcome SALESMAN
JONES welcome MANAGER
MARTIN welcome SALESMAN
BLAKE welcome MANAGER
CLARK welcome MANAGER
SCOTT welcome ANALYST
KING welcome PRESIDENT
TURNER welcome SALESMAN
ADAMS welcome CLERK
JAMES welcome CLERK
FORD welcome ANALYST
MILLER welcome CLERK
PL/SQL procedure successfully completed.
Chapter 15
Composite data type
declare
type tree is record(a varchar2(10),b number);
red tree;
cursor c1 is select pname,pcode from product;
begin
open c1;
loop
fetch c1 into red;
exit when c1 %notfound;
dbms_output.put_line(red.a ||'is a'||red.b);
end loop;
close c1;
end ;
/
honeyis a10
riceis a20
luxis a15
PL/SQL procedure successfully completed.
Chapter 16
Exception
An error is an up normal condition that arises during the exception of a program. Errors arise from design faults, Mistakes, hardware failures and other sources.
3 types
1. Predefined exception
2. user defined exception
3. un defined exception
1. Predefined exception
- Access_into_null
- Collection _is_null
- Cursor _Already_open
- Dup_val_on_index
- Invalid_cursor
- Invalid_number
- Login _denied
- No_data_found
- Program_error
- Too_many_rows
- Value_error
- Zero_divide
Error program
declare
2 x number:=&x;
3 y number:=&y;
4 begin
5 dbms_output.put_line('Results'||x/y);
6 end;
7 /
set serveroutput on;
/
Enter value for x: 7
old 2: x number:=&x;
new 2: x number:=7;
Enter value for y: 0
old 3: y number:=&y;
new 3: y number:=0;
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
Error Avoid Program
declare
2 x number:=&x;
3 y number:=&y;
4 begin
5 dbms_output.put_line('Results'||x/y);
6 exception
7 when Zero_divide then
8 dbms_output.put_line('Value is divided by zero');
9 end;
10 /
Enter value for x: 7
old 2: x number:=&x;
new 2: x number:=7;
Enter value for y: 0
old 3: y number:=&y;
new 3: y number:=0;
Value is divided by zero
PL/SQL procedure successfully completed.
Chapter 17
Procedures and Functions
PL/ SQL provide sophisticated language constructs to program procedures and functions as stand- alone PL/ SQL blocks. They can be called from other PL/ SQL blocks, other procedures and functions. The syntax for a procedure
create[ or replace] procedure< procedure name>[(< list of parameters>)] is
< declarations>
Begin
< sequence of statements>
[ exception< exception handling routines>]
End [< procedure name>];
Example for procedure1:
create or replace procedure disp_ename(eno in number)is mname varchar2(20);
begin
select name into mname from x where dno=eno;
dbms_output.put_line('the name is'||mname);
end;
/
Procedure created.
exec disp_ename(29);
the name isarun
PL/SQL procedure successfully completed.
Example for procedure 2:
declare
2 a varchar2(100):='hello';
3 b varchar2(100);
4 procedure red(a in varchar2,b out varchar2)as
5 begin
6 b:=a||'+ '||a;
7 exception
8 when value_error then
9 dbms_output.put_line('wrong');
10 end;
11 begin
12 red(a,b);
13 dbms_output.put_line(a ||'-'||b);
14 end;
15 /
hello-hello+hello
PL/SQL procedure successfully completed.
Create new table:
create table polic(eid number(3),ename varchar2(13),age number(2),dest varchar2(6),
2 doj date,salary number(6),expe number(2),awards varchar2(12));
Table created.
desc polic
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NUMBER(3)
ENAME VARCHAR2(13)
AGE NUMBER(2)
DEST VARCHAR2(6)
DOJ DATE
SALARY NUMBER(6)
EXPE NUMBER(2)
AWARDS VARCHAR2(12)
Create a procedure Insert
create or replace procedure inst is
2 eid number(3);
3 ename varchar2(13);
4 age number(2);
5 dest varchar2(6);
6 doj date;
7 salary number(6);
8 expe number(2);
9 awards varchar2(12);
10 begin
11 insert into polic values(&eno,'&ename',&age,'&dest','&doj',&salary,&expe,'&awards');
12 end;
13 /
Enter value for eno: 1
Enter value for ename: ganash
Enter value for age: 30
Enter value for dest: sp
Enter value for doj: 12-jan-02
Enter value for salary: 8000
Enter value for expe: 6
Enter value for awards: anna
old 11: insert into polic values(&eno,'&ename',&age,'&dest','&doj',&salary,&expe,'&awards');
new 11: insert into polic values(1,'ganash',30,'sp','12-jan-02',8000,6,'anna');
Procedure created.
/
Enter value for eno: 2
Enter value for ename: veena
Enter value for age: 29
Enter value for dest: pc
Enter value for doj: 22-mar-98
Enter value for salary: 9000
Enter value for expe: 7
Enter value for awards: chiefminster
old 11: insert into polic values(&eno,'&ename',&age,'&dest','&doj',&salary,&expe,'&awards');
new 11: insert into polic values(2,'veena',29,'pc','22-mar-98',9000,7,'chiefminster');
Procedure created.
Create a procedure display
create or replace procedure disp is
2 name1 varchar2(15);
3 ag1 number(2);
4 dest1 varchar2(6);
5 doj1 date;
6 sal1 number(5);
7 expe1 number(2);
8 award varchar2(12);
9 a number(3):=&a;
10 begin
11 select ename into name1 from polic where eid=a;
12 select age into ag1 from polic where eid=a;
13 select dest into dest1 from polic where eid=a;
14 select doj into doj1 from polic where eid=a;
15 select salary into sal1 from polic where eid=a;
16 select expe into expe1 from polic where eid=a;
17 select awards into award from polic where eid=a;
18 dbms_output.put_line('the name is:'||name1);
19 dbms_output.put_line('the age is:'||ag1);
20 dbms_output.put_line('the dest is:'||dest1);
21 dbms_output.put_line('the doj is:'||doj1);
22 dbms_output.put_line('the salary is:'||sal1);
23 dbms_output.put_line('the expe is:'||expe1);
24 dbms_output.put_line('the awards is:'||award);
25 end;
26 /
Enter value for a: 1
old 9: a number(3):=&a;
new 9: a number(3):=1;
Procedure created.
A function can be specified in an
create[ or replace] function< function name>[(< list of parameters>)]
return< data type> is
declare
2 function square(a number)
3 return number as b number;
4 begin
5 b:=a*a;
6 return b;
7 end;
8 begin
9 dbms_output.put_line(square(10));
10 end;
11 /
100
PL/SQL procedure successfully completed.
A procedure can be deleted
Syntax :
drop procedure< procedure name>
( drop function< function name>).
Package
A package is a database object that groups logically related PL/SQL types, objects, and subprograms.
Packages usually have two parts,
- A specification
- A body
Although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use.
Syntax:
CREATE PACKAGE name AS -- specification (visible part)
-- public type and object declarations
-- subprogram specifications
END [name];
CREATE PACKAGE BODY name AS -- body (hidden part)
-- private type and object declarations
-- subprogram bodies
[BEGIN
-- initialization statements]
END [name];
To create packages and store them permanentlyin an Oracle database, you use the CREATE PACKAGE and CREATE PACKAGE BODY statements, which you can execute interactively from SQL*Plus or Server Manager.
Advantages of Packages
Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
CREATE PACKAGE app1 as
2 TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
3 CURSOR desc_salary RETURN EmpRecTyp;
4 PROCEDURE hire_employee (empno number,
5 ename VARCHAR2,
6 job VARCHAR2,
7 mgr NUMBER,
8 sal NUMBER,
9 comm NUMBER,
10 deptno NUMBER);
11 end app1;
12 /
Package created.
create or replace package body app1 as
2 CURSOR desc_salary RETURN EmpRecTyp IS
3 SELECT empno, sal FROM emp ORDER BY sal DESC;
4 PROCEDURE hire_employee (empno number,
5 ename VARCHAR2,
6 job VARCHAR2,
7 mgr NUMBER,
8 sal NUMBER,
9 comm NUMBER,
10 deptno NUMBER) IS
11 BEGIN
12 INSERT INTO emp VALUES (empno,ename, job,
13 mgr, SYSDATE, sal, comm, deptno);
14 END hire_employee;
15 end app1;
16 /
Package body created.
Package is executed
EXECUTE app1.hire_employee(121,'malar','clerk',870,2000,67,10);
PL/SQL procedure successfully completed.
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
121 malar clerk 870 09-AUG-07 2000 67
10
15 rows selected.
Delete a package
drop package app1;
Package dropped.
Trigger
A database trigger is stored PL/SQL block. That is associated with a table .triggers are automatically executed when a specified SQL statement is issued against a table.
Triggers are mainly following purpose:
- To automatically generate a values.
- To provide auditing.
Types of triggers
Before or After
select * from z;
no rows selected
SQL> desc z;
Name Null? Type
----------------------------------------- -------- ----------------------------
AGE NUMBER(3)
DNO NUMBER(4)
insert into z values(29,75);
1 row created.
create or replace trigger red before insert on z
2 begin
3 dbms_output.put_line('inserting record');
4 end;
5 /
Trigger created.
insert into z values(52,90);
inserting record
1 row created.
Check Constraints
Often columns in a table must have values that are within a certain range or that satisfy certain conditions.
The syntax for a check constraint is
[constraint< name>]check(< condition>)
If a check constraint is specified as a column constraint, the condition can only refer that column.
EXAMPLE 1: you enter name field values only uppercase letters
SQL> create table samp(ename varchar2(16)constraint check_name check(ename=upper(ename)));
Table created.
// input is wrong
insert into samp values('&ename');
Enter value for ename: jaya
old 1: insert into samp values('&ename')
new 1: insert into samp values('jaya')
insert into samp values('jaya')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_NAME) violated
// input is correct
insert into samp values('ANAND');
1 row created.
EXAMPLE 2:
you enter sal field values minimum 500 .
you enter deptno field values between 10 to 100 .
create table samp1(sal number(5,2)constraint check_sal check(sal>=500),deptno number(3)
2 constraint check_deptno check(deptno between 10 and 100));
Table created.
number( o, d): Numeric data type for integers and reals.
o = overall number of digits,
d = number of digits to the right of the decimal point.
Examples: number( 8), number( 5,2)
Note that,e. g.,number( 5,2) cannot contain anything larger than 999.99 without resulting in an error. Data types derived from number are int[ eger], dec[ imal], smallint and real.
//Error
insert into samp1 values(4000,60);
insert into samp1 values(4000,60)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
//Error
SQL> insert into samp1 values(400,150);
insert into samp1 values(400,150)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_DEPTNO) violated
Correct statements
insert into samp1 values(600,60);
1 row created.
Foreign Key Constraints
A foreign key constraint( or referential integrity constraint) can be specified as a column constraint or as a table constraint:
[ constraint< name>][ foreign key(<column( s)>)]
references<table>[(<column( s)>)]
[on delete cascade]
This constraint specifies a column or a list of columns as a foreign key of the referencing table. The referencing table is called the child- table, and the referenced table is called the parent- table.
create table emp1(EMPNO number(4)constraint pk_emp1 primary key,ename varchar2(10),
2 deptno number(3));
Table created.
desc emp1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
DEPTNO NUMBER(3)
/
Enter value for empno: 101
Enter value for ename: sandyu
Enter value for deptno: 20
old 1: insert into emp1 values(&empno,'&ename',&deptno)
new 1: insert into emp1 values(101,'sandyu',20)
1 row created.
/
.
.
/ /// Error data because using primary key. Avoid duplicate values.
Enter value for empno: 101
Enter value for ename: feena
Enter value for deptno: 50
old 1: insert into emp1 values(&empno,'&ename',&deptno)
new 1: insert into emp1 values(101,'feena',50)
insert into emp1 values(101,'feena',50)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP1) violated
SQL> select * from emp1;
EMPNO ENAME DEPTNO
---------- ---------- ----------
100 kalai 10
101 sandyu 20
102 mani 30
103 heena 40
Oracle
No comments:
Post a Comment