PRACTICAL NO.

1
AIM: To apply insertion, deletion, modification, database operation and queries on the table. SQL*Plus: Release 9.0.1.0.1 - Production on Thu Sep 10 09:54:38 2009 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production SQL> create table stud 2 (rollno varchar(20) primary key,name varchar(20),age varchar(20)); Table created. SQL> desc stud; Name Null? Type ----------------------------------------- -------- ---------------------ROLLNO NOT NULL VARCHAR2(20) NAME VARCHAR2(20) AGE VARCHAR2(20) SQL> insert into stud values(&rollno,&name,&age); Enter value for rollno: '1' Enter value for name: 'abc' Enter value for age: '21' old 1: insert into stud values(&rollno,&name,&age) new 1: insert into stud values('1','abc','21') 1 row created. SQL> / Enter value for rollno: '2' Enter value for name: 'def' Enter value for age: '22' old 1: insert into stud values(&rollno,&name,&age) new 1: insert into stud values('2','def','22') 1 row created.

SQL> / Enter value for rollno: '3' Enter value for name: 'ghi' Enter value for age: '23' old 1: insert into stud values(&rollno,&name,&age) new 1: insert into stud values('3','ghi','23') 1 row created. SQL> select * from stud; ROLLNO NAME AGE -------------------- -------------------- -------------------1 abc 21 2 def 22 3 ghi 23 SQL> select distinct * from stud; ROLLNO NAME AGE -------------------- -------------------- -------------------1 abc 21 2 def 22 3 ghi 23 SQL> update stud set age='24' where rollno=3; 1 row updated. SQL> select * from stud; ROLLNO NAME AGE -------------------- -------------------- -------------------1 abc 21 2 def 22 3 ghi 24 SQL> select * from stud; ROLLNO NAME AGE -------------------- -------------------- -------------------1 abc 21 2 def 22 3 ghi 24

SQL> rename stud to student; Table renamed. SQL> alter table student add(phn_no varchar(10)); Table altered. SQL> desc student; Name Null? Type ----------------------------------------- -------- ---------------------ROLLNO NOT NULL VARCHAR2(20) NAME VARCHAR2(20) AGE VARCHAR2(20) PHN_NO VARCHAR2(10) SQL> alter table student modify(age varchar(5)); Table altered. SQL> desc student; Name Null? Type ----------------------------------------- -------- ---------------------ROLLNO NOT NULL VARCHAR2(20) NAME VARCHAR2(20) AGE VARCHAR2(5) PHN_NO VARCHAR2(10) SQL> select rollno from student where age<23; ROLLNO -------------------1 2 SQL> select name from student where age between 21 and 25; NAME -------------------abc def ghi

SQL> select * from student order by name desc; ROLLNO NAME AGE PHN_NO -------------------- -------------------- ----- ---------3 ghi 24 2 def 22 1 abc 21 SQL> select * from student order by name desc; ROLLNO NAME AGE PHN_NO -------------------- -------------------- ----- --------3 ghi 24 2 def 22 1 abc 21 SQL> delete from student where age =21; 1 row deleted. SQL> select * from student; ROLLNO NAME AGE PHN_NO -------------------- -------------------- ----- --------2 def 22 3 ghi 24 SQL> drop table student; Table dropped. SQL> desc student; ERROR: ORA-04043: object student does not exist

PRACTICAL NO. 2
AIM: Create a view to display details of employees working on more than one project. SQL> create table emp1 (empno varchar(15),ename varchar(26),sal varchar(24),no_of_proj varchar(10),proj_name varchar(20),proj_loc varchar(10)); Table created. SQL> desc emp1; Name Null? Type ----------------------------------------- -------- ---------------------EMPNO VARCHAR2(15) ENAME VARCHAR2(26) SAL VARCHAR2(24) NO_OF_PROJ VARCHAR2(10) PROJ_NAME VARCHAR2(20) PROJ_LOC VARCHAR2(10) SQL> select * from emp1; no rows selected SQL> insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc); Enter value for empno: '1' Enter value for ename: 'abc' Enter value for sal: 20000 Enter value for no_of_proj: 2 Enter value for proj_name: 'java' Enter value for proj_loc: 'delhi' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('1','abc',20000,2,'java','delhi') 1 row created. SQL> / Enter value for empno: '2' Enter value for ename: 'def' Enter value for sal: 15000 Enter value for no_of_proj: 1 Enter value for proj_name: 'C++' Enter value for proj_loc: 'bangalore' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('2','def',15000,1,'C++','bangalore') 1 row created. SQL> /

Enter value for empno: '3' Enter value for ename: 'ghi' Enter value for sal: 12000 Enter value for no_of_proj: 4 Enter value for proj_name: 'C' Enter value for proj_loc: 'pune' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('3','ghi',12000,4,'C','pune') 1 row created. SQL> / Enter value for empno: '4' Enter value for ename: 'jkl' Enter value for sal: 17000 Enter value for no_of_proj: 3 Enter value for proj_name: 'Smalltalk' Enter value for proj_loc: 'noida' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('4','jkl',17000,3,'Smalltalk','noida') 1 row created. SQL> select * from emp1; EMPNO ENAME SAL NO_OF_PROJ PROJ_NAME ----- ------------ ------- ---------- -------------- ---------1 abc 20000 2 java delhi 2 def 15000 1 C++ bangalore 3 ghi 12000 4 C pune 4 jkl 17000 3 Smalltalk noida PROJ_LOC

SQL> create view vw as select empno,ename,sal,proj_name,proj_loc from emp1 where no_of_proj>1; View created. SQL> select * from vw; EMPNO ENAME SAL PROJ_NAME PROJ_LOC -------- ----- ------- ---------- ------------------1 3 4 abc 20000 java delhi ghi 12000 C pune jkl 17000 Smalltalk noida

PRACTICAL NO. 3
AIM: Create a view to display details of employees not working on any project. SQL> create table emp1 (empno varchar(15),ename varchar(26),sal varchar(24),no_of_proj varchar(10),proj_name varchar(20),proj_loc varchar(10)); Table created. SQL> desc emp1; Name Null? Type ----------------------------------------- -------- ---------------------EMPNO VARCHAR2(15) ENAME VARCHAR2(26) SAL VARCHAR2(24) NO_OF_PROJ VARCHAR2(10) PROJ_NAME VARCHAR2(20) PROJ_LOC VARCHAR2(10) SQL> select * from emp1; no rows selected SQL> insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc); Enter value for empno: '1' Enter value for ename: 'abc' Enter value for sal: 20000 Enter value for no_of_proj: 2 Enter value for proj_name: 'java' Enter value for proj_loc: 'delhi' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('1','abc',20000,2,'java','delhi') 1 row created. SQL> / Enter value for empno: '2' Enter value for ename: 'def' Enter value for sal: 15000 Enter value for no_of_proj: 1 Enter value for proj_name: 'C++' Enter value for proj_loc: 'bangalore' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('2','def',15000,1,'C++','bangalore')

1 row created. SQL> / Enter value for empno: '3' Enter value for ename: 'ghi' Enter value for sal: 12000 Enter value for no_of_proj: 4 Enter value for proj_name: 'C' Enter value for proj_loc: 'pune' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('3','ghi',12000,4,'C','pune') 1 row created. SQL> / Enter value for empno: '4' Enter value for ename: 'jkl' Enter value for sal: 17000 Enter value for no_of_proj: 3 Enter value for proj_name: 'Smalltalk' Enter value for proj_loc: 'noida' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('4','jkl',17000,3,'Smalltalk','noida') 1 row created. SQL> insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc); Enter value for empno: '5' Enter value for ename: 'mno' Enter value for sal: 19000 Enter value for no_of_proj: null Enter value for proj_name: 'java' Enter value for proj_loc: 'delhi' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('5','mno',19000,null,'java','delhi') 1 row created. SQL> / Enter value for empno: '6' Enter value for ename: 'pqr' Enter value for sal: 21000 Enter value for no_of_proj: null

Enter value for proj_name: 'C++' Enter value for proj_loc: 'pune' old 1: insert into emp1 values(&empno,&ename,&sal,&no_of_proj,&proj_name,&proj_loc) new 1: insert into emp1 values('6','pqr',21000,null,'C++','pune') 1 row created. SQL> select * from emp1; EMPNO ENAME SAL NO_OF_PROJ PROJ_NAME PROJ_LOC ------ ----------- ------- ----------- ----------- ---------1 2 3 4 5 6 abc def ghi jkl mno pqr 20000 2 15000 1 12000 4 17000 3 19000 null 21000 null java delhi C++ bangalore C pune Smalltalk noida java delhi C++ pune

6 rows selected. SQL> create view vw5 as select empno,ename,sal,proj_name,proj_loc from emp1 where no_of_proj='null'; View created. SQL> select * from vw5; EMPNO ENAME SAL PROJ_NAME -----------delhi pune PROJ_LOC

------ -------5 mno 6 pqr

-----------------19000 java 21000 C++

PRACTICAL NO. 4
AIM: Create a view to display employees name and project name for employees working on projects <p1 and p3> or <p2 and p4>. SQL>create table company(ssn varchar2(10) primary key,name varchar2(10),age varchar2(10)); Table created. SQL> insert into company values('&ssn','&name','&age'); Enter value for ssn: 11 Enter value for name:smith Enter value for age: 19 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('11','smith','19') 1 row created. SQL> / Enter value for ssn: 17 Enter value for name: john Enter value for age: 19 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('17','john','19') 1 row created SQL> / Enter value for ssn: 19 Enter value for name:james Enter value for age: 25 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('19','james','25') 1 row created. SQL> / Enter value for ssn: 23 Enter value for name:jack Enter value for age: 22 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('23','jack','22') 1 row created.

10

SQL> / Enter value for ssn: 29 Enter value for name:tom Enter value for age: 20 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('29','tom','20') 1 row created. SQL> / Enter value for ssn: 87 Enter value for name:daniel Enter value for age: 47 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('87','sagita','80') 1 row created. SQL> / Enter value for ssn:91 Enter value for name:michael Enter value for age:42 old 1: insert into company values('&ssn','&name','&age') new 1: insert into company values('91','michael','91') 1 row created. SQL> select * from company; SSN NAME AGE ---------- ---------- ---------11 smith 19 17 john 19 19 james 25 23 jack 22 29 tom 20 87 daniel 47 91 michael 42 7 rows selected. SQL>create table project(ssn varchar2(10) references company,project_no varchar2(10),project_name varchar2(10)); Table created.

11

SQL> insert into project values('&ssn','&project_no','&project_name'); Enter value for ssn: 11 Enter value for project_no: 1 Enter value for project_name: security old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('11','1','security') 1 row created. SQL> / Enter value for ssn: 29 Enter value for project_no: 2 Enter value for project_name: programming old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('29','2','programming') 1 row created. SQL> / Enter value for ssn: 17 Enter value for project_no: 3 Enter value for project_name: gaming old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('17','3','gaming') 1 row created. SQL> / Enter value for ssn: 29 Enter value for project_no: 4 Enter value for project_name: hacking old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('29','4','hacking') 1 row created. SQL> / Enter value for ssn: 19 Enter value for project_no: 3 Enter value for project_name: gaming old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('19','3','gaming') 1 row created.

12

SQL> / Enter value for ssn: 29 Enter value for project_no: 3 Enter value for project_name: gaming old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('29','3','gaming') 1 row created. SQL> / Enter value for ssn: 19 Enter value for project_no: 1 Enter value for project_name: security old 1: insert into project values('&ssn','&project_no','&project_name') new 1: insert into project values('19','1','security') 1 row created. SQL> select * from project; SSN project_no project_name ---------- -------------- ----------------------11 1 security 29 2 programming 17 3 gaming 29 4 hacking 19 3 gaming 29 3 gaming 19 1 security SQL>create view working1 as select name,project_name from company,project where project_no=(1 AND 2) OR (3 AND 4); View created. SQL> select * from working1; NAME -----------James James Tom Tom PROJECT_NAME -----------------------gaming security programming hacking

13

PRACTICAL NO. 5
AIM: Using two tables create view which shall perform equijoin. SQL>create table project41(projid varchar2(10) ,projname varchar2(20)); Table created. SQL> desc project41; Name Null? Type ----------------------------------------- -------- ---------------------PROJID VARCHAR2(10) PROJNAME VARCHAR2(20) SQL>create table emp41(empname varchar2(20) ,empid varchar2(10),projectid varchar2(10)); Table created. SQL> desc emp41; Name Null? Type ----------------------------------------- -------- ----------------------EMPNAME VARCHAR2(20) EMPID VARCHAR2(10) PROJECTID VARCHAR2(10) SQL> insert into emp41 values('abc',01,1); 1 row created. SQL> insert into emp41 values('def',02,2); 1 row created. SQL> insert into emp41 values('ghi',03,3); 1 row created. SQL> select * from emp41; EMPNAME EMPID PROJECTID -------------------- ---------- ---------abc 1 1 def 2 2

14

ghi

SQL> insert into project41 values(1,'a'); 1 row created. SQL> insert into project41 values(7,'b'); 1 row created. SQL> insert into project41 values(2,'c'); 1 row created. SQL> select * from project41; PROJID PROJNAME ---------- -------------------1 a 7 b 2 c SQL> select emp41.empname,project41.projname,emp41.projectid from project41,emp41 where emp41.projec tid=project41.projid; EMPNAME PROJNAME -------------------- -------------------abc a 1 def c PROJECTID ---------2

15

PRACTICAL NO. 6
AIM: Create trigger for before and after insertion. Before SQL> create table students1(name varchar2(20),roll_no varchar2(9) not null primary key,balance varchar2(10)); Table created. SQL> desc students1; Name Null? Type --------------------------- ---------------------- ---------------------NAME VARCHAR2(20) ROLL_NO NOT NULL VARCHAR2(9) BALANCE VARCHAR2(10) SQL> insert into students1 values('pradeep','1207234','156'); 1 row created. SQL> insert into students1 values('pradeep','1207233','250'); 1 row created. SQL> insert into students1 values('rahul','1207239','195'); 1 row created. SQL> insert into students1 values('neeraj','1207215','400'); 1 row created. SQL> insert into students1 values('nitin','1207223','450'); 1 row created. SQL> select * from students1; NAME ROLL_NO BALANCE -------------------- --------- ---------pradeep 1207234 156 pradeep 1207233 250

16

rahul neeraj nitin

1207239 195 1207215 400 1207223 450

SQL> create or replace trigger tri before insert or update on students1 for each row 2 begin 3 if :new.balance<=0 then 4 raise_application_error(-20000,'salary can not be less than zero'); 5 end if; 6 end; 7 / Trigger created. SQL> insert into students1 values 2 ('rohit','1207938','-256'); insert into students1 values * ERROR at line 1: ORA-20000: salary can not be less than zero ORA-06512: at "SCOTT.TRI", line 3 ORA-04088: error during execution of trigger 'SCOTT.TRI' SQL> commit; Commit complete. After SQL> create table students2(name varchar2(9),roll_no varchar2(8) primary key,balance varchar2(3)); Table created. SQL> desc students2; Name Null? Type --------------------------------------- ---------- ----------------------NAME VARCHAR2(9) ROLL_NO NOT NULL VARCHAR2(8) BALANCE VARCHAR2(3) SQL> insert into students2 values('pradeep','1207234','250'); 1 row created.

17

SQL> insert into students2 values('pradeep','1207233','200'); 1 row created. SQL> insert into students2 values('rahul','1207239','600'); 1 row created. SQL> insert into students2 values('nitin','1207223','600'); 1 row created. SQL> select * from students2; NAME ROLL_NO BAL --------- -------- --pradeep 1207234 250 pradeep 1207233 200 rahul 1207239 600 nitin 1207223 600 SQL> create table new_student(name varchar2(9),roll_no varchar2(9),balance varchar2(8),operator varc har2(10)); Table created. SQL> desc new_student; Name Null? Type ----------------------------------------- -------- ----------------------- NAME VARCHAR2(9) ROLL_NO VARCHAR2(9) BALANCE VARCHAR2(8) OPERATOR VARCHAR2(10) SQL> create or replace trigger tri6 after insert or delete or update on students2 2 for each row 3 declare 4 operator varchar2(10); 5 begin 6 if updating then 7 operator:='update'; 8 end if; 9 if inserting then

18

10 11 12 13 14 15 16 17

operator:='insert'; end if; if deleting then operator:='delete'; end if; insert into new_student values(:old.roll_no,:old.name,:old.balance,operator); end; /

Trigger created. SQL> update students2 set balance='700' where name='rahul'; 1 row updated. SQL> delete from students2 where roll_no='1207234'; 1 row deleted. SQL> select * from students2; NAME ROLL_NO BAL --------- -------- --pradeep 1207233 200 rahul 1207239 700 nitin 1207223 600 SQL> select * from new_student; NAME ROLL_NO BALANCE OPERATOR --------- --------- -------- ---------1207239 rahul 600 update 1207234 pradeep 250 delete

19

PRACTICAL NO. 7
AIM: Write a procedure to give incentive to employees working on project p1. SQL> create table server1 (name varchar(9),salary varchar(10),HRA varchar(10),DA varchar(18),projid varchar(10),incentive varchar(10)); Table created. SQL> desc server1; Name Null? ----------------------------------- ----------------- --------------------NAME SALARY HRA DA PROJID INCENTIVE SQL> ed Wrote file afiedt.buf 1 create or replace procedure inc(name in varchar, salary in varchar, HRA in varchar, DA in varchar, projid in varchar) 2 is 3 incentive number(10,3); 4 t_salary number(100; 5 begin 6 inc:=2000; 7 if projid=p1 then 8 t_salary:=salary+HRA+DA+inc; 9 incentive:=inc; 10 salary:=t_salary; 11 else 12 t_salary:=salary+HRA+DA; 13 incentive:=0; 14 salary:=t_salary; 15 end if; 16 insert into server1 values(name,salary,HRA,DA,projid,incentive); 17 end; 18 / Procedure created. Type VARCHAR2(9) VARCHAR2(10) VARCHAR2(10) VARCHAR2(18) VARCHAR2(10) VARCHAR2(10)

20

SQL> execute inc(pradeep,15000,8000,5000,p1); PL/SQL procedure successfully completed. SQL> execute inc(rahul,46000,1234,123,p2); PL/SQL procedure successfully completed. SQL> execute inc(vishal,30000,890,120,p3); PL/SQL procedure successfully completed. SQL> select * from server1; NAME SALARY HRA DA PROJID ----------- ----------- ----------- ----------- ----------- --------------pradeep 30000 8000 5000 p1 rahul 47357 1234 123 p2 vishal 31010 890 120 p3 0 INCENTIVE 2000 0

21

PRACTICAL NO. 8
AIM: Write a procedure for computing amount of telephone bill. SQL> create table telephbill(name varchar(16),ph_no number(10),units number(9),bill number(10,2)); Table created. SQL> ed Wrote file afiedt.buf 1 create procedure pr35(name in varchar,ph_no in number,units in number)is 2 unit number(12); 3 bill number(12,2); 4 begin 5 bill:=205; 6 unit:=units-105; 7 if unit>50 and unit<500 then 8 bill:=bill+(unit*0.80); 9 else 10 bill:=bill+(unit*1.20); 11 end if; 12 insert into telephbill values(name,ph_no,units,bill); 13* end; SQL> / Procedure created. SQL> exec pr35('sumit',9994372140,270); PL/SQL procedure successfully completed. SQL> select * from telephbill; NAME PH_NO UNITS BILL ---------------- ---------- ---------- ---------sumit 9994372140 270 337

22

PRACTICAL NO. 9
AIM: Write a procedure for creating view on income tax. SQL> create table server1(name varchar2(9),salary varchar2(10),HRA varchar2(10),DA varchar2(18),tax number(10)); Table created. SQL> desc server1; Name Null? Type ----------------------------------------- ---------------- --------------NAME VARCHAR2(9) SALARY VARCHAR2(10) HRA VARCHAR2(10) DA VARCHAR2(18) TAX NUMBER(10) SQL> ed Wrote file afiedt.buf 1 create or replace procedure inc_tax(name in varchar2,salary in varchar2,HRA in varchar2,DA in varchar2) 2 is 3 tax number(10,3); 4 t_salary number(10); 5 begin 6 t_salary:=salary+HRA+DA; 7 if(t_salary<=40000) then 8 tax:=0.000; 9 end if; 10 if(t_salary>40000 and t_salary<=60000) then 11 tax:=(10*t_salary)/100; 12 end if; 13 if(t_salary>60000 and t_salary<=100000) then 14 tax:=(20*t_salary)/100; 15 end if; 16 if(t_salary>100000) then 17 tax:=(30*t_salary)/100; 18 end if; 19 insert into server values(name,salary,HRA,DA,tax); 20 end; 21 /

23

Procedure created. SQL> execute inc_tax('pradeep','150000','8000','5000'); PL/SQL procedure successfully completed. SQL> execute inc_tax('rahul','46000','1234','123'); PL/SQL procedure successfully completed. SQL> execute inc_tax('pradeep','30000','890','120'); PL/SQL procedure successfully completed. SQL> select * from server1; NAME SALARY HRA DA ----------- ----------------- ----------- ----------- --------------------pradeep 150000 8000 5000 rahul 46000 1234 123 pradeep 30000 890 120 TAX 48900 4736 0

24

PRACTICAL NO. 10
AIM: The hr manager has decided to raise the salary of employees by 15%. Write a pl/sql block to accept the employee no. & update the salary of the employees. Display appropriate message based on the existence of records in the table. SQL> create table employee(eid number(10),ename varchar(20),esal number(9,2)); Table created. SQL> desc employee; Name Null? Type ----------------------------------------- ----------------- --------------EID ENAME VARCHAR2(20) ESAL SQL> insert into employee values(1,aaa,3000); 1 row created. SQL> insert into employee values(2,bbb,4000); 1 row created. SQL> insert into employee values(3,ccc,5000); 1 row created. SQL> insert into employee values(4,ddd,6000); 1 row created. SQL> insert into employee values(5,eee,7000); 1 row created. SQL> select * from employee; EID ENAME ----------------- ----------------- ---------------1 aaa 2 bbb 3 ccc 4 ddd 5 eee SQL> set serveroutput on; ESAL 3000 4000 5000 6000 7000

NUMBER(10) NUMBER(9,2)

25

SQL> ed empsal1 declare a number; x number; x:=a*0.15; begin update employee set salary=salary*x where empid between 1 and 5; dbms_output.put_line(Salary updated); EXCEPTION WHEN INVALID_NUMBER THEN dbms_output.put_line(Updation failed); end; Enter the value for empid: 1 Salary updated PL/SQL procedure successfully completed. SQL> select * from employee; EID ENAME ----------------- ----------------- ---------------1 aaa 2 bbb 3 ccc 4 ddd 5 eee ESAL 3450 4000 5000 6000 7000

26

PRACTICAL NO. 11
AIM: Create a procedure that debits certain amount from customer account if minimum balance constraint is preserved. SQL> create table customer(accnum number(10),cname varchar(20),balance number(9,2)); Table created. SQL> desc customer; Name Null? Type ----------------------------------------- ----------------- --------------ACCNUM CNAME VARCHAR2(20) BALANCE SQL> set serveroutput on; SQL> ed cust 1 create procedure db(accnum in number,cname in varchar,balance in number) 2 is 3 bal number=1000; 4 begin 5 bal:=bal-200; 6 update customer set balance=bal where balance<1000; 7 dbms_output.put_line(amount deducted due to minimum balance); 8 end; 9/ Procedure created. SQL> execute db(1,aa,1100); PL/SQL procedure successfully completed. SQL> select * from customer; ACCNUM CNAME ----------------------- ----------------------- -------------------1 aa SQL> execute db(2,bb,900); BALANCE 1100

NUMBER(10) NUMBER(9,2)

27

PL/SQL procedure successfully completed. SQL> execute db(3,cc,500); PL/SQL procedure successfully completed. SQL> select * from customer; ACCNUM CNAME ----------------------- ----------------------- -------------------1 aa 2 bb 3 cc BALANCE 1100 700 300

28