Oracle Applications: Oracle SQL and PL/SQL

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 ).

Basic SQL

Table :1

outlines the main Oracle SQL data types

Table 1: The main SQL data types.

Type description	Oracle SQL	MySQL SQL
variable-length char. string	VARCHAR2(l)	VARCHAR(l)
fixed-length char. string	CHAR(l)	CHAR(l)
Number	NUMBER(p,s)	NUMERIC(p,s)
Currency	NUMBER(10,2)	NUMERIC(10,2)
Date	DATE	DATE

1 length.

2 precision, scale.

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)

------------------

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

--------------

Number function

a. Abs

select abs(-24)from dual;

ABS(-24)

------------

b. Ceil

select ceil(25.17)from dual;

CEIL(25.17)

-----------

c. Floor

select floor(16.8)from dual;

FLOOR(16.8)

----------------

d. Mod

select mod(7,2)from dual;

MOD (7,2)

----------

e. Round

select round(25.7)from dual;

ROUND(25.7)

-----------

f . Sqrt

select sqrt(25)from dual;

SQRT(25)

----------

g. Power

select power(2,3)from dual;

POWER(2,3)

----------

h. Sign:

select sign(12) from dual;

SIGN(12)

----------

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')

----------------------------

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')

----------

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;

4.MM:

select to_char(sysdate,'mm')from dual;

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;

8.W(week of the month)

select to_char(sysdate,'W')from dual;

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;

11.D (day of the week)

select to_char(sysdate,'D')from dual;

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;

select to_char(sysdate,'HH12')from dual

14.HH24(hour of day using)

select to_char(sysdate,'HH24')from dual

15.MI(minutes)

select to_char(sysdate,'MI')from dual;

16.SS(seconds)

select to_char(sysdate,'SS')from dual;

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

select * from cust;

CNO CNAME DEPTNO

---------- -------- ----------

101 ramesh 10

102 suresh 20

103 feena 30

104 durga 20

105 heena 30

106 sudha 10

107 fg 50

select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Union

Two output queries merge(duplicate elements discard)

select deptno from dept union select deptno from cust;

DEPTNO

----------

Union all

Duplicate elements display

select deptno from dept union all select deptno from cust;

DEPTNO

----------

Intersect

Common values display.

select deptno from dept intersect select deptno from cust;

DEPTNO

----------

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

----------

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.

select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

select * from cust;

CNO CNAME DEPTNO

---------- -------- ----------

101 ramesh 10

102 suresh 20

103 feena 30

104 kala 40

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

---------- -------- ----------

20 deepa 20

20 heena 20

20 deepa 20

20 heena 20

30 badler 30

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

----------

NEXTVAL

----------

NEXTVAL

----------

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;

select * from m1;

DNO NAME

---------- ------

10 anu

20 deepa

30 banu

40 ravi

select * from n;

DNO AGE

---------- ----------

10 23

20 17

30 18

50 22

ROWID

------------------

AAAGFjAABAAAIV0AAA

AAAGFjAABAAAIV1AAA

AAAGFjAABAAAIV2AAA

AAAGFjAABAAAIV3AAA

AAAGFjAABAAAIV4AAA

select * from cluster clum;

DNO

----------

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

-----------------------------------------------------------------

B1(34, 56, 87, 65)

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

[ 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

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:

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 /

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;

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;

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) ?

If condition

Syntax:

If condition then

//statements;

End if;

If 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 end if;

8 end;

9 /

Enter value for s: 36

old 4: s:=&s;

new 4: s:=36;

The number is positive

If.. then ..else condition

Syntax:

If condition then

//statements;

Else

//statements;

End if;

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

Loop:

Syntax:

Loop

// statements

End loop

4 loop

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;

PL/SQL procedure successfully completed.

Lab Exercises

Calculate 1+2+3….n;
Calculate square of 1+2+3….n;

While loop

declare

While Loop:

Syntax:

While Condition Loop

// statements

End loop

2 x integer:=1;

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 /

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 /

PL/SQL procedure successfully completed.

Chapter 14

Run

Set serveroutput on;

Table link

Cursors

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);

Output:

In the emp1 table 1 record was deleted.

3 begin

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.

The body fully defines cursors and subprograms, and so implements the specification.

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 permanently in 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

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7698 BLAKE MANAGER 7839 01-MAY-81 2850

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7782 CLARK MANAGER 7839 09-JUN-81 2450

7788 SCOTT ANALYST 7566 19-APR-87 3000

7839 KING PRESIDENT 17-NOV-81 5000

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7876 ADAMS CLERK 7788 23-MAY-87 1100

7900 JAMES CLERK 7698 03-DEC-81 950

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7902 FORD ANALYST 7566 03-DEC-81 3000

7934 MILLER CLERK 7782 23-JAN-82 1300

121 malar clerk 870 09-AUG-07 2000 67

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

Pages

Sunday, August 19, 2012

Oracle SQL and PL/SQL

No comments:

Post a Comment