SEMESTER: INDEX Batch:

ORACLE LAB
Sl. Page
No. Date Name of the Program Staff Sign
No.
SQL & PL/SQL

1 Table Creation with Simple Queries

Constraints [Primary Key, Foreign Key,
2
NOT NULL, Referential Integrity]
3 Joins [Left, Right and Equi Joins]

4 Sub Queries
Built-In-Functions [Date & Time,
5
Mathematical Functions]
6 Procedures

7 Functions

8 Functions with Exception Handling

9 Cursors

10 Triggers

Perform the Following Using VB as Front End and Oracle as Back End
Library Management System
11 [ADODCConnection]
Employee Details for IT Company
12 [ADODC Connection]
Hospital Management System
13 [ADODCConnection]

Remarks: Completed Programs

PageNo:
Ex.No: Table Creation with Simple Queries
Date:

AIM:-

Procedure:-
EX. NO : 1 TABLE CREATION AND SIMPLE QUERIES

SQL> create table stud(sno number(5),sname varchar2(25),class varchar2(10),mark1

number(5),mark2 number(5),mark3 number(5),mark4 number(5),mark5 number(5));
Table created.

SQL>desc stud;

Name Null? Type

SNO NUMBER(5)
SNAME VARCHAR2(25)

CLASS VARCHAR2(10)
MARK1 NUMBER(5)

MARK2 NUMBER(5)

MARK3 NUMBER(5)

MARK4 NUMBER(5)
MARK5 NUMBER(5)

SQL> insert into stud values(&sno,'&sname','&class',&mark1,&mark2,&mark3,&mark4,&mark5);

Enter value for sno: 1

Enter value for sname: ram

Enter value for class: bca

Enter value for mark1: 78

Enter value for mark2: 80

Enter value for mark3: 90

Enter value for mark4: 95

Enter value for mark5: 99

old 1: insert into stud values(&sno,'&sname','&class',&mark1,&mark2,&mark3,&mark4,&mark5)

new 1: insert into stud values(1,'ram','bca',78,80,90,95,99)

1 row created.
SQL> /

Enter value for sno: 2

Enter value for sname: suresh

Enter value for class: bsc

Enter value for mark1: 89

Enter value for mark2: 78

Enter value for mark3: 90

Enter value for mark4: 95

Enter value for mark5: 87

old 1: insert into stud values(&sno,'&sname','&class',&mark1,&mark2,&mark3,&mark4,&mark5)

new 1: insert into stud values(2,'suresh','bsc',89,78,90,95,87)

1 row created.

SQL> /

Enter value for sno: 3

Enter value for sname: arun

Enter value for class: bba

Enter value for mark1: 35

Enter value for mark2: 45

Enter value for mark3: 56

Enter value for mark4: 46

Enter value for mark5: 47

old 1: insert into stud values(&sno,'&sname','&class',&mark1,&mark2,&mark3,&mark4,&mark5)

new 1: insert into stud values(3,'arun','bba',35,45,56,46,47)

1 row created.
SQL> /

Enter value for sno: 4

Enter value for sname: raj

Enter value for class: b.com

Enter value for mark1: 78

Enter value for mark2: 67

Enter value for mark3: 90

Enter value for mark4: 89

Enter value for mark5: 79

old 1: insert into stud values(&sno,'&sname','&class',&mark1,&mark2,&mark3,&mark4,&mark5)

new 1: insert into stud values(4,'raj','b.com',78,67,90,89,79)

1 row created.

SQL> /

Enter value for sno: 5

Enter value for sname: mani

Enter value for class: ba

Enter value for mark1: 78

Enter value for mark2: 90

Enter value for mark3: 99

Enter value for mark4: 89

Enter value for mark5: 98

old 1: insert into stud values(&sno,'&sname','&class',&mark1,&mark2,&mark3,&mark4,&mark5)

new 1: insert into stud values(5,'mani','ba',78,90,99,89,98)

1 row created.
SQL> select * from stud;

SNO SNAME CLASS MARK1 MARK2 MARK3 MARK4 MARK 5

1 ram bca 78 80 90 95 99

2 suresh bsc 89 78 90 95 87
3 arun bba 35 45 56 46 47

4 raj b.com 78 67 90 89 79
5 mani ba 78 90 99 89 98

SQL> alter table stud add(total number(10),average number(10,2),result varchar2(4),grade

varchar2(2));

Table altered.

SQL>desc stud;

Name Null? Type

SNO NUMBER(5)

SNAME VARCHAR2(25)
CLASS VARCHAR2(10)

MARK1 NUMBER(5)

MARK2 NUMBER(5)

MARK3 NUMBER(5)
MARK4 NUMBER(5)

MARK5 NUMBER(5)

TOTAL NUMBER(10)

AVERAGE NUMBER(10,2)
RESULT VARCHAR2(4)
GRADE VARCHAR2(2)
SQL> update stud set total=mark1+mark2+mark3+mark4+mark5;

5 rows updated.

SQL> update stud set average=total/5;

5 rows updated.

SQL> update stud set result='pass' where mark1>=40 and mark2>=40 and mark3>=40 and
mark4>=40 and mark5>=40;

4 rows updated.

SQL> update stud set result='fail' where mark1<40 or mark2<40 or mark3<40 or mark4<40 or
mark5<40;

1 row updated.

SQL> update stud set grade='a' where average>=90;

1 row updated.

SQL> update stud set grade='b' where average>=75 and average<=89.9;

3 rows updated.

SQL> update stud set grade='c' where average>=50 and average<=74.9;

0 rows updated.

SQL> update stud set grade='d' where average<=49.9;

1 row updated.
SQL> select * from stud;

SNO SNAME CLASS MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERA RESU GR
-

1 ram bca 78 80 90 95 99 442 88.4 pass b

2 suresh bsc 89 78 90 95 87 439 87.8 pass b
3 arun bba 35 45 56 46 47 229 45.8 fail d

4 raj b.com 78 67 90 89 79 403 80.6 pass b

5 mani ba 78 90 99 89 98 454 90.8 pass a

SQL>savepoint s1;

Savepoint created.

SQL> delete from stud where sno=5;

1 row deleted.

SQL> select * from stud;

SNO SNAME CLASS MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERA RESU GR
-

1 ram bca 78 80 90 95 99 442 88.4 pass b

2 suresh bsc 89 78 90 95 87 439 87.8 pass b

3 arun bba 35 45 56 46 47 229 45.8 fail d

4 raj b.com 78 67 90 89 79 403 80.6 pass b
SQL> rollback to s1;

Rollback complete.

SQL> select * from stud;

SNO SNAME CLASS MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERA RESU GR
-

1 ram bca 78 80 90 95 99 442 88.4 pass b

2 suresh bsc 89 78 90 95 87 439 87.8 pass b
3 arun bba 35 45 56 46 47 229 45.8 fail d

4 raj b.com 78 67 90 89 79 403 80.6 pass b

5 mani ba 78 90 99 89 98 454 90.8 pass a

SQL> commit;

Commit complete.

SQL> drop table stud;

Table dropped.
Results
Ex.No:-02 Page No:
Date: Constraints [Primary Key, Foreign Key,
Not Null, Referential Integrity]
EX.NO : 2 CREATING TABLE WITH CONSTRAINTS

(NOT NULL)

SQL> create table stud(rollno number(5) not null,name varchar2(25),branch varchar2(10));

Table created.

SQL> desc stud;

Name Null? Type

ROLLNO NOT NULL NUMBER(5)

NAME VARCHAR2(25)
BRANCH VARCHAR2(10)

SQL> insert into stud values(&rollno,'&name','&branch');

Enter value for rollno: 101

Enter value for name: ram

Enter value for branch: cse

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(101,'ram','cse')

1 row created.

SQL> /

Enter value for rollno: 102

Enter value for name: abishek

Enter value for branch: cse

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(102,'abishek','cse')

1 row created.
SQL> select * from stud;

ROLLNO NAME BRANCH

101 ram cse

102 abishek cse

SQL> insert into stud values(&rollno,'&name','&branch');

Enter value for rollno:

Enter value for name: suresh

Enter value for branch: cse

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(,'suresh','cse')

insert into stud values(,'suresh','cse')

ERROR : CONNOT INSERT NULL INTO(“SCOTT”,’STUD’,ROLLNO)

(UNIQUE)

SQL> create table stud(rollno number(25) unique,name varchar2(25),branch varchar2(10));

Table created.

SQL> insert into stud values(&rollno,'&name','&branch');

Enter value for rollno: 1

Enter value for name: ram

Enter value for branch: cse

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(1,'ram','cse')

1 row created.
SQL> /

Enter value for rollno: 2

Enter value for name: suresh

Enter value for branch: cse

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(2,'suresh','cse')

1 row created.

SQL> /

Enter value for rollno: 2

Enter value for name: ganesh

Enter value for branch: cse

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(2,'ganesh','cse')

insert into stud values(2,'ganesh','cse')

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C003007) violated

(PRIMARY KEY)

SQL> create table stud(rollno number(5) primary key,name varchar2(25),branch varchar2(10));

Table created.

SQL> insert into stud values(&rollno,'&name','&branch');

Enter value for rollno: 1

Enter value for name: govind

Enter value for branch: mca

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(1,'govind','mca')

1 row created.
SQL> /

Enter value for rollno: 2

Enter value for name: parthi

Enter value for branch: mca

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(2,'parthi','mca')

1 row created.

SQL> /

Enter value for rollno: 2

Enter value for name: rajan

Enter value for branch: mca

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(2,'rajan','mca')

insert into stud values(2,'rajan','mca')

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C003008) violated

SQL> /

Enter value for rollno:

Enter value for name: rajan

Enter value for branch: mca

old 1: insert into stud values(&rollno,'&name','&branch')

new 1: insert into stud values(,'rajan','mca')

insert into stud values(,'rajan','mca')

*

ERROR : CONNOT INSERT NULL INTO(“SCOTT”,’STUD’,ROLLNO)

SQL> select * from stud;

ROLLNO NAME BRANCH

1 govind mca

2 parthi mca

(FOREIGN KEY)

SQL> create table admin(id number(5) constraint id_pk primary key,name varchar2(25),percentage
number(5));

Table created.

SQL> insert into admin values(&id,'&name',&percentage);

Enter value for id: 1

Enter value for name: arun

Enter value for percentage: 75

old 1: insert into admin values(&id,'&name',&percentage)

new 1: insert into admin values(1,'arun',75)

1 row created.

SQL> /

Enter value for id: 2

Enter value for name: bala

Enter value for percentage: 90

old 1: insert into admin values(&id,'&name',&percentage)

new 1: insert into admin values(2,'bala',90)

1 row created.
SQL> /

Enter value for id: 3

Enter value for name: hari

Enter value for percentage: 60

old 1: insert into admin values(&id,'&name',&percentage)

new 1: insert into admin values(3,'hari',60)

1 row created.

SQL> /

Enter value for id: 4

Enter value for name: ramesh

Enter value for percentage: 90

old 1: insert into admin values(&id,'&name',&percentage)

new 1: insert into admin values(4,'ramesh',90)

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: murugan

Enter value for percentage: 89

old 1: insert into admin values(&id,'&name',&percentage)

new 1: insert into admin values(5,'murugan',89)

1 row created.
SQL> select * from admin;

ID NAME PERCENTAGE

1 arun 75
2 bala 90

3 hari 60
4 ramesh 90
5 murugan 89

SQL> create table course(id number(5) constraint id_fk references admin(id),branch varchar2(10),sect

ion varchar2(5));

Table created.

SQL> insert into course values(&id,'&branch','&section');

Enter value for id: 1

Enter value for branch: cse

Enter value for section: a

old 1: insert into course values(&id,'&branch','&section')

new 1: insert into course values(1,'cse','a')

1 row created.

SQL> /

Enter value for id: 6

Enter value for branch: cse

Enter value for section: b

old 1: insert into course values(&id,'&branch','&section')

new 1: insert into course values(6,'cse','b')

insert into course values(6,'cse','b')

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.ID_FK) violated - parent key not found

SQL> delete from admin where id=1;

delete from admin where id=1

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.ID_FK) violated - child record found

SQL> delete from course where id=1;

1 row deleted.

SQL> delete from admin where id=1;

1 row deleted.

SQL> select * from admin;

ID NAME PERCENTAGE

2 bala 90
3 hari 60

4 ramesh 90

5 murugan 89
Result:
Ex.No: 03 JOINS Page No:
Date:

AIM:-

Procedure:-
EX. NO: 3 JOINS

SQL> create table emp3(id number(5),name varchar2(25),designation varchar2(20));

Table created.

SQL> insert into emp3 values(&id,'&name','&designation');

Enter value for id: 1

Enter value for name: ram

Enter value for designation: manager

old 1: insert into emp3 values(&id,'&name','&designation')

new 1: insert into emp3 values(1,'ram','manager')

1 row created.

SQL> /

Enter value for id: 2

Enter value for name: arun

Enter value for designation: programmer

old 1: insert into emp3 values(&id,'&name','&designation')

new 1: insert into emp3 values(2,'arun','programmer')

1 row created.

SQL> /

Enter value for id: 3

Enter value for name: sathish

Enter value for designation: programmer

old 1: insert into emp3 values(&id,'&name','&designation')

new 1: insert into emp3 values(3,'sathish','programmer')

1 row created.
SQL> /

Enter value for id: 4

Enter value for name: guna

Enter value for designation: designer

old 1: insert into emp3 values(&id,'&name','&designation')

new 1: insert into emp3 values(4,'guna','designer')

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: govind

Enter value for designation: designer

old 1: insert into emp3 values(&id,'&name','&designation')

new 1: insert into emp3 values(5,'govind','designer')

1 row created.

SQL> create table package(id number(5),salary number(10));

Table created.

SQL> insert into package values(&id,&salary);

Enter value for id: 1

Enter value for salary: 50000

old 1: insert into package values(&id,&salary)

new 1: insert into package values(1,50000)

1 row created.
SQL> /

Enter value for id: 3

Enter value for salary: 30000

old 1: insert into package values(&id,&salary)

new 1: insert into package values(3,30000)

1 row created.

SQL> /

Enter value for id: 5

Enter value for salary: 30000

old 1: insert into package values(&id,&salary)

new 1: insert into package values(5,30000)

1 row created.

(LEFT JOIN)

SQL> select emp3.id,emp3.name,package.salary from emp3 left join package on emp3.id=package.id;

ID NAME SALARY

1 ram 50000
3 sathish 30000

5 govind 30000
4 guna
2 arun
(RIGHT JOIN)

SQL> select emp3.id,emp3.name,package.salary from emp3 right join package on

emp3.id=package.id;

ID NAME SALARY

1 ram 50000
3 sathish 30000

5 govind 30000

(EQUI JOIN)

SQL> select emp3.id,emp3.name,package.salary from emp3,package where emp3.id=package.id;

ID NAME SALARY

1 ram 50000
3 sathish 30000
5 govind 30000
Result:-
Ex.No 04 SUB QUERIES Page No:
Date:

AIM:-

Procedure:-
EX. NO: 4 SUB QUERIES

SQL> create table emp4(id number(5),name varchar2(25),job varchar2(15),salary

number(10),dept_no number(5));

Table created.

SQL> insert into emp4 values(&id,'&name','&job',&salary,&dept_no);

Enter value for id: 1

Enter value for name: ram

Enter value for job: manager

Enter value for salary: 50000

Enter value for dept_no: 101

old 1: insert into emp4 values(&id,'&name','&job',&salary,&dept_no)

new 1: insert into emp4 values(1,'ram','manager',50000,101)

1 row created.

SQL> /

Enter value for id: 2

Enter value for name: arun

Enter value for job: clerk

Enter value for salary: 15000

Enter value for dept_no: 103

old 1: insert into emp4 values(&id,'&name','&job',&salary,&dept_no)

new 1: insert into emp4 values(2,'arun','clerk',15000,103)

1 row created.

SQL> /

Enter value for id: 3

Enter value for name: suresh

Enter value for job: clerk

Enter value for salary: 17000

Enter value for dept_no: 103

old 1: insert into emp4 values(&id,'&name','&job',&salary,&dept_no)

new 1: insert into emp4 values(3,'suresh','clerk',17000,103)

1 row created.

SQL> /

Enter value for id: 4

Enter value for name: raghu

Enter value for job: salesman

Enter value for salary: 10000

Enter value for dept_no: 104

old 1: insert into emp4 values(&id,'&name','&job',&salary,&dept_no)

new 1: insert into emp4 values(4,'raghu','salesman',10000,104)

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: govind

Enter value for job: analyst

Enter value for salary: 15000

Enter value for dept_no: 103

old 1: insert into emp4 values(&id,'&name','&job',&salary,&dept_no)

new 1: insert into emp4 values(5,'govind','analyst',15000,103)

1 row created.

SQL> /

Enter value for id: 6

Enter value for name: parthi

Enter value for job: president

Enter value for salary: 20000

Enter value for dept_no: 102

old 1: insert into emp4 values(&id,'&name','&job',&salary,&dept_no)

new 1: insert into emp4 values(6,'parthi','president',20000,102)

1 row created.

SQL> create table dept1(dept_no number(5),dname varchar2(25),loc varchar2(15));

Table created.

SQL> insert into dept1 values(&dept_no,'&dname','&loc');

Enter value for dept_no: 101

Enter value for dname: sales

Enter value for loc: canada

old 1: insert into dept1 values(&dept_no,'&dname','&loc')

new 1: insert into dept1 values(101,'sales','canada')

1 row created.

SQL> /

Enter value for dept_no: 102

Enter value for dname: acconting

Enter value for loc: newyork

old 1: insert into dept1 values(&dept_no,'&dname','&loc')

new 1: insert into dept1 values(102,'acconting','newyork')

1 row created.

SQL> /

Enter value for dept_no: 103

Enter value for dname: research

Enter value for loc: dallas

old 1: insert into dept1 values(&dept_no,'&dname','&loc')

new 1: insert into dept1 values(103,'research','dallas')

1 row created.
SQL> /

Enter value for dept_no: 104

Enter value for dname: operations

Enter value for loc: boston

old 1: insert into dept1 values(&dept_no,'&dname','&loc')

new 1: insert into dept1 values(104,'operations','boston')

1 row created.

SQL> /

Enter value for dept_no: 105

Enter value for dname: executive

Enter value for loc: landon

old 1: insert into dept1 values(&dept_no,'&dname','&loc')

new 1: insert into dept1 values(105,'executive','landon')

1 row created.

SQL> select * from emp4;

ID NAME JOB SALARY DEPT_NO

1 ram manager 50000 101

2 arun clerk 15000 103

3 suresh clerk 17000 103

4 raghu salesman 10000 104

5 govind analyst 15000 103

6 parthi president 20000 102

6 rows selected.
SQL> select * from dept1;

DEPT_NO DNAME LOC

101 sales canada

102 acconting newyork

103 research dallas

104 operations boston
105 executive landon

(IN)
SQL> select * from emp4 where salary in(15000,25000);

ID NAME JOB SALARY DEPT_NO

2 arun clerk 15000 103

5 govind analyst 15000 103

SQL> select * from emp4 where salary in(select max(salary) from emp4);

ID NAME JOB SALARY DEPT_NO

1 ram manager 50000 101

SQL> select * from emp4 where salary in(select max(salary) from emp4 group by dept_no);

ID NAME JOB SALARY DEPT_NO

-

4 raghu salesman 10000 104

3 suresh clerk 17000 103

6 parthi president 20000 102
1 ram manager 50000 101
(ALL)
SQL> select * from emp4 where salary>all(dept_no);

ID NAME JOB SALARY DEPT_NO

1 ram manager 50000 101

2 arun clerk 15000 103

3 suresh clerk 17000 103

4 raghu salesman 10000 104

5 govind analyst 15000 103

6 parthi president 20000 102

6 rows selected.

(ANY)

SQL> select * from emp4 where salary>any(select salary from emp4 where job='clerk');

ID NAME JOB SALARY DEPT_NO

1 ram manager 50000 101

3 suresh clerk 17000 103

6 parthi president 20000 102

SQL> select * from emp4 where salary<any(select salary from emp4 where job='clerk');

ID NAME JOB SALARY DEPT_NO

2 arun clerk 15000 103

4 raghu salesman 10000 104
5 govind analyst 15000 103
SQL> select * from emp4 where salary=any(select salary from emp4 where job='clerk');

ID NAME JOB SALARY DEPT_NO

2 arun clerk 15000 103

5 govind analyst 15000 103
3 suresh clerk 17000 103

(EXISTS)
SQL> select dept_no,dname from dept1 d where exists(select * from emp4 e where
e.dept_no=d.dept_no);

DEPT_NO DNAME

101 sales
102 acconting
103 research
104 operations

(NOT EXISTS)
SQL> select dept_no,dname from dept1 d where not exists(select * from emp4 e where
e.dept_no=d.dept_no);

DEPT_NO DNAME

105 executive

(UNION)

SQL> select dept_no from emp4 union select dept_no from dept1;

DEPT_NO

101

102

103

104

105
(INTERSECT)

SQL> select dept_no from emp4 intersect select dept_no from dept1;

DEPT_NO

101

102

103

104

(MINUS)
SQL> select dept_no from emp4 minus select dept_no from dept1;

no rows selected

SQL> select dept_no from dept1 minus select dept_no from emp4;

DEPT_NO

105
Result
Ex.No:05 BUILT-IN-FUNCTIONS Page No:
EX. NO: 5 BUILT-IN-FUNCTIONS

DATE AND TIME FUNCTIONS

SQL> select sysdate from dual;

SYSDATE

16-JUL-08

SQL> select sysdate,add_months(sysdate,4) result from dual;

SYSDATE RESULT

16-JUL-08 16-NOV-08

SQL> select sysdate, last_day(sysdate) result from dual;

SYSDATE RESULT

16-JUL-08 31-JUL-08

SQL> select sysdate, next_day(sysdate,'sunday') result from dual;

SYSDATE RESULT

16-JUL-08 20-JUL-08

SQL> select months_between('09-aug-91','11-mar-90') result from dual;

RESULT

16.935484

SQL> select least(’10-jan-14’,’12-feb-14’) result from dual;

RESULT

10-jan-14

SQL> select greatest('10-jan-14','12-feb-14') result from dual;

RESULT

12-feb-14

SQL> select to_char(sysdate,'dd-mon-yyyy') result from dual;

RESULT
12-feb-14
SQL> select sessiontimezone, current_date from dual;

SESSIONTIMEZONE CURRENT_DATE

+00:00 20-SEP-2014

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
+00:00

NUMERIC FUNCTIONS

SQL> select round(12.36),round(14.63) from dual;

ROUND(12.36) ROUND(14.63)

12 15

SQL> select floor(12.87),floor(11.23) from dual;

FLOOR(12.87) FLOOR(11.23)

12 11

SQL> select ceil(16.23),ceil(12.8) from dual;

CEIL(16.23) CEIL(12.8)

17 13

SQL> select mod(11,4) from dual;

MOD(11,4)

SQL> select sign(0),sign(34),sign(-56) from dual;

SIGN(0) SIGN(34) SIGN(-56)

0 1 -1
SQL> select abs(15),abs(-90) from dual;

ABS(15) ABS(-90)

15 90

SQL> select abs(-20) result from dual;

RESULT

20

SQL> select power (2,10) result from dual;

RESULT

1024

SQL> select round(15.359,2) result from dual;

RESULT

15.36

SQL> select sqrt (36) result from dual;

RESULT

6
Results
Ex.No:06 PROCEDURES Page No
Date:

AIM:-

Procedure:-
EX. NO: 6 PROCEDURES

TO DISPLAY HELLO MESSAGE

SQL> set serveroutput on;

SQL> declare
2 a varchar2(20);
3 begin
4 a:='Hello';
5 dbms_output.put_line(a);
6 end;
7/

Hello

PL/SQL procedure successfully completed.

(CREATE ITEMS TABLE)

SQL> create table items(itemid number(3), actualprice number(5), ordid number(4), prodid
number(4));

Table created.

SQL> insert into items values(&itemid,&actualprice,&ordid,&prodid);

Enter value for itemid: 1

Enter value for actualprice: 5000

Enter value for ordid: 101

Enter value for prodid: 1001

old 1: insert into items values(&itemid,&actualprice,&ordid,&prodid)

new 1: insert into items values(1,5000,101,1001)

1 row created.
SQL> /

Enter value for itemid: 2

Enter value for actualprice: 6000

Enter value for ordid: 102

Enter value for prodid: 1002

old 1: insert into items values(&itemid,&actualprice,&ordid,&prodid)

new 1: insert into items values(2,6000,102,1002)

1 row created.

SQL> /

Enter value for itemid: 3

Enter value for actualprice: 7500

Enter value for ordid: 103

Enter value for prodid: 1003

old 1: insert into items values(&itemid,&actualprice,&ordid,&prodid)

new 1: insert into items values(3,7500,103,1003)

1 row created.

SQL> select * from items;

ITEMID ACTUALPRICE ORDID PRODID

1 5000 101 1001

2 6000 102 1002
3 7500 103 1003
PROCEDURE FOR ‘IN’ PARAMETER – CREATION, EXECUTION
SQL> set serveroutput on;

SQL> create procedure zzz (a IN number) is price number;

2 begin

3 select actualprice into price from items where itemid=a;

4 dbms_output.put_line('Actual price is ' || price);

5 if price is null then

6 dbms_output.put_line('price is null');

7 end if;

8 end;

9/

Procedure created.

SQL> exec zzz(2);

Actual price is 6000

PL/SQL procedure successfully completed.

PROCEDURE FOR ‘OUT’ PARAMETER – CREATION, EXECUTION

SQL> set serveroutput on;

SQL> create procedure ddd (a in number, b out number) is identity number;

2 begin

3 select ordid into identity from items where itemid=a;

4 if identity<1000 then

5 b:=100;

6 end if;

7 end;

8/

Procedure created.
SQL> declare

2 a number;

3 b number;

4 begin

5 ddd(1,b);

6 dbms_output.put_line('The value of b is '|| b);

7 end;

8/

The value of b is 100

PL/SQL procedure successfully completed.

PROCEDURE FOR ‘INOUT’ PARAMETER – CREATION, EXECUTION

SQL> set serveroutput on;

SQL> create procedure itit ( a in out number) is

2 begin

3 a:=a+1;

4 end;

5/

Procedure created.

SQL> declare

2 a number:=7;

3 begin

4 itit(a);

5 dbms_output.put_line('The updated value is ' ||a);

6 end;

7/

The updated value is 8

PL/SQL procedure successfully completed.

Result:
Ex.No:07 FUNCTIONS Page No:
Date:

AIM:-

Procedure:-
EX. NO: 7 FUNCTIONS

SQL> create table train ( tno number(10), tfare number(10));

Table created.

SQL> insert into train values(&tno,&tfare);

Enter value for tno: 12345

Enter value for tfare: 500

old 1: insert into train values(&tno,&tfare)

new 1: insert into train values(12345,500)

1 row created.

SQL> /

Enter value for tno: 12346

Enter value for tfare: 600

old 1: insert into train values(&tno,&tfare)

new 1: insert into train values(12346,600)

1 row created.

SQL> /

Enter value for tno: 12347

Enter value for tfare: 700

old 1: insert into train values(&tno,&tfare)

new 1: insert into train values(12347,700)

1 row created.
SQL> select * from train;

TNO TFARE

12345 500
12346 600

12347 700

PROGRAM FOR FUNCTION AND IT’S EXECUTION

SQL> create function aaa (trainnumber number) return number is

2 trainfunction train.tfare % type;

3 begin

4 select tfare into trainfunction from train where tno=trainnumber;

5 return(trainfunction);

6 end;

7/

Function created.

SQL> set serveroutput on;

SQL> declare

2 amt number;

3 begin

4 amt:=aaa (12345);

5 dbms_output.put_line('Train fare is Rs. '||amt);

6 end;

7/
Train fare is Rs. 500

PL/SQL procedure successfully completed.

FACTORIAL OF A NUMBER USING FUNCTION — PROGRAM AND EXECUTION

SQL> create function fact (a number) return number is

2 fact number:=1;

3 b number;

4 begin
5 b:=a;

6 while b>0

7 loop

8 fact:=fact*b;

9 b:=b-1;

10 end loop;

11 return(fact);

12 end;

13 /

Function created.

SQL> set serveroutput on;

SQL> declare

2 a number:=5;

3 f number(10);

4 begin

5 f:=fact(a);

6 dbms_output.put_line('The factorial of the given number is '||f);

7 end;

8/

The factorial of the given number is 120

PL/SQL procedure successfully completed.

Ex.No:08 FUNCTION WITH EXCEPTION HANDLING Page No:

Date:

AIM:-

Procedure:-
EX. NO: 8 FUNCTION WITH EXCEPTION HANDLING

USER DEFINED EXCEPTION

SQL> set serveroutput on;

SQL> DECLARE
2 quantity1 NUMBER := -2;
3 quantity2 NUMBER := 4;
4 total NUMBER := 0;
5 quantity_must_positive EXCEPTION;
6 FUNCTION find_cost (quant NUMBER) RETURN NUMBER IS
7 BEGIN
8 IF (quant > 0)
9 THEN
10 RETURN(quant * 20);
11 ELSE
12 RAISE quantity_must_positive;
13 END IF;
14 END find_cost;
15 BEGIN
16 total := find_cost (quantity2);
17 total := total + find_cost(quantity1);
18 EXCEPTION
19 WHEN quantity_must_positive
20 THEN
21 dbms_output.put_line('Total until now: ' || total);
22 dbms_output.put_line('Tried to use negative quantity ');
23 END;
24 /
Total until now: 80
Tried to use negative quantity

PL/SQL procedure successfully completed.

(CREATE ITEMS TABLE)

SQL> create table items(itemid number(3), actualprice number(5), ordid number(4), prodid
number(4));
Table created.

SQL> insert into items values(&itemid,&actualprice,&ordid,&prodid);

Enter value for itemid: 1
Enter value for actualprice: 5000
Enter value for ordid: 101
Enter value for prodid: 1001
old 1: insert into items values(&itemid,&actualprice,&ordid,&prodid)
new 1: insert into items values(1,5000,101,1001)
1 row created.

SQL> /
Enter value for itemid: 2
Enter value for actualprice: 6000
Enter value for ordid: 102
Enter value for prodid: 1002
old 1: insert into items values(&itemid,&actualprice,&ordid,&prodid)
new 1: insert into items values(2,6000,102,1002)
1 row created.

SQL> /
Enter value for itemid: 3
Enter value for actualprice: 7500
Enter value for ordid: 103
Enter value for prodid: 1003
old 1: insert into items values(&itemid,&actualprice,&ordid,&prodid)
new 1: insert into items values(3,7500,103,1003)

1 row created.
SQL> select * from items;
ITEMID ACTUALPRICE ORDID PRODID

1 5000 101 1001

2 6000 102 1002
3 7500 103 1003

PROGRAM FOR GENERAL PROCEDURE – SELECTED RECORD’S PRICE IS

INCREMENTED BY 1000 , EXECUTING THE PROCEDURE CREATED AND DISPLAYING
THE UPDATED TABLE

SQL> set serveroutput on;

SQL> create procedure itsum(identity number, total number) is price number;
2 null_price exception;
3 begin
4 select actualprice into price from items where itemid=identity;
5 if price is null then
6 raise null_price;
7 else
8 update items set actualprice=actualprice+total where itemid=identity;
9 end if;
10 exception
11 when null_price then
12 dbms_output.put_line('price is null');
13 end;
14 /
Procedure created.

SQL> exec itsum(1,1000);

PL/SQL procedure successfully completed.

SQL> select * from items;

ITEMID ACTUALPRICE ORDID PRODID

1 6000 101 1001

2 6000 102 1002
3 7500 103 1003

SQL> insert into items(itemid,ordid,prodid) values (&itemid,&ordid,&prodid);

Enter value for itemid: 4
Enter value for ordid: 104
Enter value for prodid: 1004
old 1: insert into items(itemid,ordid,prodid) values (&itemid,&ordid,&prodid)
new 1: insert into items(itemid,ordid,prodid) values (4,104,1004)

1 row created.
SQL> exec itsum(4,1000);
price is null

PRE DEFINED EXCEPTION

SQL> set serveroutput on;

SQL> declare
2 price ssitems.actualprice % type;

3 begin

4 select actualprice into price from ssitems where quantity=10;

5 exception
6 when no_data_found then
7 dbms_output.put_line ('ssitems missing');
8 end;
9/
ssitems missing

PL/SQL procedure successfully completed.

Result
EX. NO: 9 CURSORS

SQL> create table empp( eid number(10), ename varchar2(20), job varchar2(20), sal number
(10),deptno number(5));

Table created.

SQL> insert into empp values(&eid,'&ename','&job',&sal,&deptno);

Enter value for eid: 1

Enter value for ename: ram

Enter value for job: lecturer

Enter value for sal: 10000

Enter value for deptno: 11

old 1: insert into empp values(&eid,'&ename','&job',&sal,&deptno)

new 1: insert into empp values(1,'ram','lecturer',10000,11)

1 row created.

SQL> /

Enter value for eid: 2

Enter value for ename: suresh

Enter value for job: professor

Enter value for sal: 25000

Enter value for deptno: 12

old 1: insert into empp values(&eid,'&ename','&job',&sal,&deptno)

new 1: insert into empp values(2,'suresh','professor',25000,12)

1 row created.
SQL> /

Enter value for eid: 3

Enter value for ename: raghu

Enter value for job: lecturer

Enter value for sal: 11000

Enter value for deptno: 11

old 1: insert into empp values(&eid,'&ename','&job',&sal,&deptno)

new 1: insert into empp values(3,'raghu','lecturer',11000,11)

1 row created.

SQL> /

Enter value for eid: 4

Enter value for ename: govind

Enter value for job: professor

Enter value for sal: 30000

Enter value for deptno: 12

old 1: insert into empp values(&eid,'&ename','&job',&sal,&deptno)

new 1: insert into empp values(4,'govind','professor',30000,12)

1 row created.

SQL> /

Enter value for eid: 5

Enter value for ename: parthi

Enter value for job: lecturer

Enter value for sal: 12000

Enter value for deptno: 11

old 1: insert into empp values(&eid,'&ename','&job',&sal,&deptno)

new 1: insert into empp values(5,'parthi','lecturer',12000,11)

1 row created.
SQL> select * from empp;

EID ENAME JOB SAL DEPTNO

1 ram lecturer 10000 11

2 suresh professor 25000 12

3 raghu lecturer 11000 11

4 govind professor 30000 12
5 parthi lecturer 12000 11

TO WRITE A PL/SQL BLOCK TO DISPLAY THE EMPOYEE ID AND EMPLOYEE NAME

USING CURSOR FOR LOOP

SQL> set serveroutput on;

SQL> declare

2 begin

3 for emy in (select eid,ename from empp)

4 loop

5 dbms_output.put_line('Employee id and employee name are '|| emy.eid ||' and ' || emy.ename);

6 end loop;

7 end;

8/

Employee id and employee name are 1 and ram

Employee id and employee name are 2 and suresh

Employee id and employee name are 3 and raghu

Employee id and employee name are 4 and govind

Employee id and employee name are 5 and parthi

PL/SQL procedure successfully completed.

TO WRITE A PL/SQL BLOCK TO UPDATE THE SALARY OF ALL EMPLOYEES WHERE
DEPARTMENT NO IS 11 BY 5000 USING CURSOR FOR LOOP AND TO DISPLAY THE
UPDATED TABLE

SQL> set serveroutput on;

SQL> declare

2 cursor cem is select eid,ename,sal,deptno from empp where deptno=11;

3 begin

4 --open cem;

5 for rem in cem

6 loop

7 update empp set sal=rem.sal+5000 where eid=rem.eid;

8 end loop;

9 --close cem;

10 end;

11 /

PL/SQL procedure successfully completed.

SQL> select * from empp;

EID ENAME JOB SAL DEPTNO

1 ram lecturer 15000 11

2 suresh professor 25000 12

3 raghu lecturer 16000 11

4 govind professor 30000 12

5 parthi lecturer 17000 11

TO WRITE A PL/SQL BLOCK TO DISPLAY THE EMPLOYEE ID AND EMPLOYEE
NAME WHERE DEPARTMENT NUMBER IS 11 USING EXPLICIT CURSORS

SQL> set serveroutput on;

SQL> declare

2 cursor cenl is select eid,sal from empp where deptno=11;

3 ecode empp.eid%type;

4 esal empp.sal%type;

5 begin

6 open cenl;

7 loop

8 fetch cenl into ecode,esal;

9 exit when cenl%notfound;

10 dbms_output.put_line(' Employee code and employee salary are' || ecode || ' and '|| esal);

11 end loop;

12 close cenl;

13 end;

14 /

Employee code and employee salary are1 and 15000

Employee code and employee salary are3 and 16000

Employee code and employee salary are5 and 17000

PL/SQL procedure successfully completed.

TO WRITE A PL/SQL BLOCK TO UPDATE THE SALARY BY 10000 WHERE THE JOB IS
LECTURER , TO CHECK IF UPDATES ARE MADE USING IMPLICIT CURSORS AND TO
DISPLAY THE UPDATED TABLE

SQL> set serveroutput on;

SQL> declare

2 county number;

3 begin

4 update empp set sal=sal+10000 where job='lecturer';

5 county:= sql%rowcount;

6 if county > 0 then

7 dbms_output.put_line('The number of rows are '|| county);

8 end if;

9 if sql %found then

10 dbms_output.put_line('Employee record modification successful');

11 else if sql%notfound then

12 dbms_output.put_line('Employee record is not found');

13 end if;

14 end if;

15 end;

16 /

The number of rows are 3

Employee record modification successful

PL/SQL procedure successfully completed.

SQL> select * from empp;

EID ENAME JOB SAL DEPTNO

1 ram lecturer 25000 11

2 suresh professor 25000 12

3 raghu lecturer 26000 11

4 govind professor 30000 12

5 parthi lecturer 27000 11

Ex.No: 10 TRIGGERS PageNo:

Date:

AIM:-

Procedure:-
EX. NO: 10 TRIGGERS

SQL> create table itempls (ename varchar2(10), eid number(5), salary number(10));

Table created.

SQL> insert into itempls values('&ename',&eid,&salary);

Enter value for ename: xxx

Enter value for eid: 11

Enter value for salary: 10000

old 1: insert into itempls values('&ename',&eid,&salary)

new 1: insert into itempls values('xxx',11,10000)

1 row created.

SQL> /

Enter value for ename: yyy

Enter value for eid: 12

Enter value for salary: 10500

old 1: insert into itempls values('&ename',&eid,&salary)

new 1: insert into itempls values('yyy',12,10500)

1 row created.

SQL> /

Enter value for ename: zzz

Enter value for eid: 13

Enter value for salary: 11000

old 1: insert into itempls values('&ename',&eid,&salary)

new 1: insert into itempls values('zzz',13,11000)

1 row created.
SQL> select * from itempls;

ENAME EID SALARY

Xxx 11 10000
Yyy 12 10500
Zzz 13 11000

TO CREATE A SIMPLE TRIGGER THAT DOES NOT ALLOW INSERT UPDATE AND
DELETE OPERATIONS ON THE TABLE

SQL> create trigger ittrigg before insert or update or delete on itempls for each row

2 begin

3 raise_application_error(-20010,'You cannot do manipulation');

4 end;

5/

Trigger created.

SQL> insert into itempls values('aaa',14,34000);

insert into itempls values('aaa',14,34000)

ERROR at line 1:

ORA-20010: You cannot do manipulation

ORA-06512: at "SCOTT.ITTRIGG", line 2

ORA-04088: error during execution of trigger 'SCOTT.ITTRIGG'

SQL> delete from itempls where ename='xxx';

delete from itempls where ename='xxx'

ERROR at line 1:

ORA-20010: You cannot do manipulation

ORA-06512: at "SCOTT.ITTRIGG", line 2

ORA-04088: error during execution of trigger 'SCOTT.ITTRIGG'

SQL> update itempls set eid=15 where ename='yyy';

update itempls set eid=15 where ename='yyy'

ERROR at line 1:

ORA-20010: You cannot do manipulation

ORA-06512: at "SCOTT.ITTRIGG", line 2

ORA-04088: error during execution of trigger 'SCOTT.ITTRIGG'

TO DROP THE CREATED TRIGGER

SQL> drop trigger ittrigg;

Trigger dropped.

TO CREATE A TRIGGER THAT RAISES AN USER DEFINED ERROR MESSAGE AND

DOES NOT ALLOW INSERTION

SQL> create trigger ittriggs before insert on itempls for each row

2 declare

3 triggsal itempls.salary%type;

4 begin

5 select salary into triggsal from itempls where eid=12;

6 if(:new.salary>triggsal or :new.salary<triggsal) then

7 raise_application_error(-20100,'Salary has not been changed');

8 end if;

9 end;

10 /

Trigger created.
SQL> insert into itempls values ('bbb',16,45000);

insert into itempls values ('bbb',16,45000)

ERROR at line 1:

ORA-20100: Salary has not been changed

ORA-06512: at "SCOTT.ITTRIGGS", line 6

ORA-04088: error during execution of trigger 'SCOTT.ITTRIGGS'

SQL> update itempls set eid=18 where ename='zzz';

1 row updated.

SQL> select * from itempls;

ENAME EID SALARY

Xxx 11 10000
Yyy 12 10500

Zzz 18 11000

SQL> insert into itempls values ('bbb',16,10500);

1 row created.

SQL> select * from itempls;

ENAME EID SALARY

Xxx 11 10000
Yyy 12 10500

Zzz 18 11000
Bbb 16 10500
SQL> update itempls set eid=20 where ename='bbb';

1 row updated.

SQL> select * from itempls;

ENAME EID SALARY

Xxx 11 10000
Yyy 12 10500

Zzz 18 11000
Bbb 20 10500
Result
VB as Front End and Oracle as Back End