Scribd
Upload
30 views11 pages

6 - 10 Edited Programs-4

The program creates a payroll table with attributes like employee ID, name, department, designation, salary components etc. Values are inserted into the table. Savepoints are created for each row inserted. The rollback command is used to delete the last row inserted. Grant and revoke commands are used to manage user permissions. Finally, the payroll table is displayed using a select query, thus executing and verifying the program successfully.

Uploaded by

Sandy Rokey
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
30 views11 pages

6 - 10 Edited Programs-4

The program creates a payroll table with attributes like employee ID, name, department, designation, salary components etc. Values are inserted into the table. Savepoints are created for each row inserted. The rollback command is used to delete the last row inserted. Grant and revoke commands are used to manage user permissions. Finally, the payroll table is displayed using a select query, thus executing and verifying the program successfully.

Uploaded by

Sandy Rokey
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 11

DEVELOPING A PROGRAM USING CURSOR

6. Develop a program using CURSOR, FOR UPDATE CURSOR, WHERE CURRENT of clause and
CURSOR variables.
AIM:
To develop a program using cursor ,for update cursor where current of clause and cursor variables.

ALGORITHM:

STEP 1: start the process

STEP 2: create a table and use the following attributes such as eid, ename, depid, deptname, age,
gender and salary.

STEP 3: Insert values for above attributes.

STEP 4: create a cursor value for salary update.

STEP 5: Use update Query for updating the particular row .

STEP 6: Use open and close query to update the row.

STEP 7: Stop the process.

PROGRAM:

SQL> create table employ(eid number(4),ename varchar(20),deptid number(3),deptname


varchar(20),age number(3),gender varchar(1),salary decimal(8,5));
Table created.
SQL> insert into employ values('&eid','&ename','&deptid','&deptname','&age','&gender','&salary');
Enter value for eid: 101
Enter value for ename: uma
Enter value for deptid: 1
Enter value for deptname: testing
Enter value for age: 25
Enter value for gender: f
Enter value for salary: 250
old 1: insert into employ values('&eid','&ename','&deptid','&deptname','&age','&gender','&salary')
new 1: insert into employ values('101','uma','1','testing','25','f','250')
1 row created.
SQL> /
Enter value for eid: 102
Enter value for ename: kalai
Enter value for deptid: 1
Enter value for deptname: testing
Enter value for age: 28
Enter value for gender: f
Enter value for salary: 350
old 1: insert into employ values('&eid','&ename','&deptid','&deptname','&age','&gender','&salary')
new 1: insert into employ values('102','kalai','1','testing','28','f','350')
1 row created.
SQL> /
Enter value for eid: 102
Enter value for ename: ravi
Enter value for deptid: 2
Enter value for deptname: design
Enter value for age: 30
Enter value for gender: m
Enter value for salary: 500
old 1: insert into employ values('&eid','&ename','&deptid','&deptname','&age','&gender','&salary')
new 1: insert into employ values('102','ravi','2','design','30','m','500')
1 row created.
SQL>select salary from employ where deptid=1;
Salary
250
350

SQL> declare
2 cursor salaryupdate is
3 select eid,ename,deptid,deptname,age,salary from employ
4 where deptid=33
5 for update of salary nowait;
6 sal salaryupdate%rowtype;
7 begin
8 open salaryupdate;
9 loop
10 fetch salaryupdate into sal;
11 exit when salaryupdate%notfound;
12 if sal.deptid=1 then
13 update employ
14 set salary=sal.salary+100
15 where current of salaryupdate;
16 end if;
17 end loop;
18 commit;
19 close salaryupdate;
20 end;
21 /
PL/SQL procedure successfully completed.

SQL> select salary from employ where deptid=1;


Salary
350
450
RESULT:Thus the above program has been executed and verified successful.

CREATING A DATABASE TRIGGER


7. Create a database trigger.

AIM:

To write a database trigger.

ALGORITHM:

STEP 1: Start the process.


STEP 2: create table and use following attributes such as routeid, busname, originate, terminate,
distance, fare, booking day.

STEP 3: Insert value for above attributes.

STEP 4: create a trigger value row before insert the value.

STEP 5: Use the trigger delete row query to delete but the trigger not deleted.

STEP 6: Stop the process.

PROGRAM:

SQL> CREATE TABLE ROUTE(ROUTEID NUMBER(3),BUSNAME


VARCHAR(20),ORIGINATE VARCHAR(20),TERMINATE VARCHAR(20),DISTANCE
NUMBER(5),FARE DECIMAL(5,2),BOOKINGDAY VARCHAR(15));
Table created.
SQL> create or replace trigger VALUE_ROW
2 before insert on route
3 for each row
4 begin
5 if(:new.bookingday='saturday' or :new.bookingday='sunday')then
6 raise_application_error (-20010,'cannot have transcation on week ends');
7 end if;
8 end;
9 /
Trigger created.
SQL> insert into route values('&routeid','&busname','&originate','&terminate','&distance','&fare','&
bookingday');
Enter value for routeid: 11
Enter value for busname: sks
Enter value for originate: erode
Enter value for terminate: covai
Enter value for distance: 100
Enter value for fare: 150.00
Enter value for bookingday: saturday
old 1: insert into route values('&routeid','&busname','&originate','&terminate','&distance','&fare
new 1: insert into route values('11','sks','erode','covai','100','150.00','saturday')
insert into route values('11','sks','erode','covai','100','150.00','saturday')
*
ERROR at line 1:
ORA-20010: cannot have transcation on week ends
ORA-06512: at "IT267.VALUE_ROW", line 3
ORA-04088: error during execution of trigger 'IT267.VALUE_ROW'

SQL> /
Enter value for routeid: 13
Enter value for busname: srpk
Enter value for originate: sakthi
Enter value for terminate: erode
Enter value for distance: 50
Enter value for fare: 45.00
Enter value for bookingday: friday
old 1: insert into route values('&routeid','&busname','&originate','&terminate','&distance','&fare
new 1: insert into route values('13','srpk','sakthi','erode','50','45.00','friday')
1 row created.
SQL>/
Enter value for routeid:12
Enter value for busname:pkp
Enter value for originate:salem
Enter value for terminate:erode
Enter value for distance:65
Enter value for fare:60.00
Enter value for bookingday:Thursday
Old 1:insert into route values(‘&routeid’,’&busname’,’&originate’,’&terminate’,’&distance’,;&fare
New 1: insert into route vaues(‘12’,’pkp’,’salem’,’erode’,’65’,’60.00’,’thursday’)
1 row created.

SQL>delete from route where routeid=12;


1 row deleted.

SQL> create trigger delete_row before delete on


2 route for each row
3 begin
4 raise_application_error
5 (-20010,'Deletion not allowed');
6 end;
7 /
Trigger created.

SQL> delete from route where routeid=13;


delete from route where routeid=13
*
ERROR at line 1:

ORA-20010: Deletion not allowed

ORA-06512: at "IT267.DELETE_ROW", line 2

ORA-04088: error during execution of trigger 'IT267.DELETE_ROW'

RESULT:Thus the above program has been executed and verified successful.

CREATING A PAYROLL TABLE AND TCL COMMANDS


8. Create a payroll table and use ROLL BACK, COMMIT, SAVEPOINT, and GRANT and
REVOKE commands.
Aim: To create a payroll table and use rollback ,commit,sawpoint and grant revoke commands.

Algorithm:.
Step1: Start the process.
Step2: Create table and use following attributes such as empid,empname, department,
designation,salary etc.
Step3: Insert values for above attributes.
Step4: Create a save point for each row using the query save point object name.
Step5: Use rollback query to delete last row.
Step6: Use grent and revoke query.
Step7: Display the table using select query.
Step8: Stop the process .

PROGRAM:
SQL> create table emp(empid number(4),empname varchar(20),dept varchar(20),designation
varchar(20),dateofjoining date,daysworked number(2),basicpay decimal(10,2),da decimal(10,2),hra
decimal(10,2),medicalallo decimal(10,2),grosspay decimal(10,2),pf decimal(10,2),tax
decimal(10,2),totaldeduction decimal(10,2),netpay decimal(10,2));

Table created.

SQL> insert into emp values


('&empid','&empname','&dept','&designation','&dateofjoining','&daysworked','&basicpay','&da','&hra
','&medicalallo','&grosspay','&pf','&tax','&totaldeduction','&nenetpay');
Enter value for empid: 101
Enter value for empname: uma
Enter value for dept: ctit
Enter value for designation: asst.pro
Enter value for dateofjoining: 11 jan 2010
Enter value for daysworked: 25
Enter value for basicpay: 10000
Enter value for da: 1500
Enter value for hra: 1500
Enter value for medicalallo: 1500
Enter value for grosspay: 114500
Enter value for pf: 200
Enter value for tax: 200
Enter value for totaldeduction: 400
Enter value for nenetpay: 11000
old 1: INSERT INTO EMP values
('&EMPID','&EMPNAME','&DEPT','&DESIGNATION','&DATEOFJOINING','&
new 1: INSERT INTO EMP values ('101','uma','ctit','asst.pro','11 jan 2010','25','10000','1500
1 row created.
SQL> /
Enter value for empid: 102
Enter value for empname: sindhu
Enter value for dept: ctit
Enter value for designation: asst.pro
Enter value for dateofjoining: 15 jun 2010
Enter value for daysworked: 25
Enter value for basicpay: 15000
Enter value for da: 1000
Enter value for hra: 1000
Enter value for medicalallo: 1000
Enter value for grosspay: 18000
Enter value for pf: 1000
Enter value for tax: 1000
Enter value for totaldeduction: 2000
Enter value for nenetpay: 16000
old 1: INSERT INTO EMP values
('&EMPID','&EMPNAME','&DEPT','&DESIGNATION','&DATEOFJOINING','&
new 1: INSERT INTO EMP values ('102','sindhu','ctit','asst.pro','15 jun 2010','25','15000','1
1 row created.
SQL> savepoint a;
Savepoint created.
SQL> INSERT INTO EMP values
('&EMPID','&EMPNAME','&DEPT','&DESIGNATION','&DATEOFJ
WORKED','&BASICPAY','&DA','&HRA','&MEDICALALLO','&GROSSPAY','&PF','&TAX','&TO
TALDEDUCTY');
Enter value for empid: 103
Enter value for empname: sakthi
Enter value for dept: ctit
Enter value for designation: asst.pro
Enter value for dateofjoining: 25 jun 2010
Enter value for daysworked: 25
Enter value for basicpay: 15000
Enter value for da: 1000
Enter value for hra: 1000
Enter value for medicalallo: 1000
Enter value for grosspay: 18000
Enter value for pf: 1000
Enter value for tax: 1000
Enter value for totaldeduction: 2000
Enter value for nenetpay: 16000
old 1: INSERT INTO EMP values ('&EMPID','&EMPNAME','&DEPT','&DESIGNATION','&DAT
new 1: INSERT INTO EMP values ('103','sakthi','ctit','asst.pro','25 jun 2010','
1 row created.
SQL> savepoint b;
Savepoint created.
SQL> INSERT INTO EMP values
('&EMPID','&EMPNAME','&DEPT','&DESIGNATION','&DATEOF
SWORKED','&BASICPAY','&DA','&HRA','&MEDICALALLO','&GROSSPAY','&PF','&TAX','&T
OTALDEDUCAY');
Enter value for empid: 104
Enter value for empname: dharani
Enter value for dept: ctit
Enter value for designation: asst.prof
Enter value for dateofjoining: 20 jan 2010
Enter value for daysworked: 25
Enter value for basicpay: 18000
Enter value for da: 1000
Enter value for hra: 1000
Enter value for medicalallo: 1000
Enter value for grosspay: 21000
Enter value for pf: 1000
Enter value for tax: 1000
Enter value for totaldeduction: 2000
Enter value for nenetpay: 19000
old 1: INSERT INTO EMP values ('&EMPID','&EMPNAME','&DEPT','&DESIGNATION','&DA
new 1: INSERT INTO EMP values ('104','dharani','ctit','asst.prof','20 jan 2010
1 row created.
SQL> savepoint c;
Savepoint created.
SQL> select * from emp;
EMPID EMPNAME DEPT DESIGNATION DATEOFJOI DAYSWORKED BASICPAY
DA HRA MEDICALALLO GROSSPAY PF TAX TOTALDEDUCTION NETPAY
101 uma ctit asst.pro 11-JAN-10 25 10000 1500 1500 1500
114500 200 200 400 11000
102 sindhu ctit asst.pro 15-JUN-10 25 15000 1000 1000 1000
18000 1000 1000 2000 16000
103 sakthi ctit asst.pro 25-JUN-10 25 15000 1000 1000 1000
18000 1000 1000 2000 16000
104 dharani ctit asst.prof 20-JAN-10 25 18000 1000 1000 1000
21000 1000 1000 2000 19000
SQL> rollback to b;
Rollback complete.
SQL> select * from emp;
EMPID EMPNAME DEPT DESIGNATION DATEOFJOI DAYSWORKED
BASICPAY DA HRA MEDICALALLO GROSSPAY PF TAX
TOTALDEDUCTION NETPAY
101 uma ctit asst.pro 11-JAN-10 25 10000 1500 1500 1500
114500 200 200 400 11000
102 sindhu ctit asst.pro 15-JUN-10 25 15000 1000 1000 1000
18000 1000 1000 2000 16000
103 sakthi ctit asst.pro 25-JUN-10 25 15000 1000 1000 1000
18000 1000 1000 2000 16000
SQL> rollback to a;
Rollback complete.
SQL> select * from emp;
EMPID EMPNAME DEPT DESIGNATION DATEOFJOI DAYSWORKED BASICPAY
DA HRA MEDICALALLO GROSSPAY PF TAX TOTALDEDUCTION NETPAY
101 uma ctit asst.pro 11-JAN-10 25 10000 1500 1500 1500
114500 200 200 400 11000
102 sindhu ctit asst.pro 15-JUN-10 25 15000 1000 1000 1000
18000 1000 1000 2000 16000
SQL> grant select on emp to public;
Grant succeeded.
SQL> revoke select on emp from public;
Revoke succeeded.

RESULT:Thus the above program has been executed and verified successful.
__________________________________________________________________________________

CREATING A PL\SQL PROCEDURE AND FUNCTION.


9. Create a PL/SQL procedure and functions.
Aim:
To create a PL/SQL procedure function.
Algorithm:.
Step1: Start the process.
Step2: Create table and use following attributes such as regno,name,dob, programme, branch,
semester,Java,DBMS,mp, total percentage.
Step3: Create a function.
Step4: Insert values for above attributes.
Step5: Select maximum percentage from table using max percentage.
Step 6: Stop the process .
PROGRAM:
SQL> create table marksheet(regno number(6),name varchar(20),dob date,programme
varchar(20),branch varchar(20),sem varchar(10),java number(3),dbms number(3),mp number(3),total
number(4),percentage decimal(5,2));
Table created.
SQL> insert into marksheet
values('&regno','&name','&dob','&programme','&branch','&sem','&java','&db
ms','&mp','&total','&percentage');
Enter value for regno: 11
Enter value for name: uma
Enter value for dob: 11 jan 2000
Enter value for programme: b.sc
Enter value for branch: it
Enter value for sem: i
Enter value for java: 50
Enter value for dbms: 50
Enter value for mp: 50
Enter value for total: 150
Enter value for percentage: 50
old 1: insert into marksheet values('&regno','&name','&dob','&programme','&branch','&sem','&java',
new 1: insert into marksheet values('11','uma','11 jan 2000','b.sc','it','i','50','50','50','150',

1 row created.

SQL> /
Enter value for regno: 12
Enter value for name: dharani
Enter value for dob: 15 jan 2005
Enter value for programme: b.com
Enter value for branch: commerce
Enter value for sem: II
Enter value for java: 80
Enter value for dbms: 80
Enter value for mp: 80
Enter value for total: 240
Enter value for percentage: 80
old 1: insert into marksheet values('&regno','&name','&dob','&programme','&branch','&sem','&java',
new 1: insert into marksheet values('12','dharani','15 jan 2005','b.com','commerce','II','80','80'

1 row created.
SQL> /
Enter value for regno: 13
Enter value for name: poorani
Enter value for dob: 8 feb 2000
Enter value for programme: b.sc
Enter value for branch: ct
Enter value for sem: iii
Enter value for java: 80
Enter value for dbms: 85
Enter value for mp: 85
Enter value for total: 250
Enter value for percentage: 83
old 1: insert into marksheet values('&regno','&name','&dob','&programme','&branch','&sem','&
new 1: insert into marksheet values('13','poorani','8 feb 2000','b.sc','ct','iii','80','85',

1 row created.

SQL> select * from marksheet ;


REGNO NAME DOB PROGRAMME BRANCH SEM JAVA DBMS MP
TOTAL PERCENTAGE
11 uma 11-JAN-00 b.sc it i 50 50 50
150 50
12 dharani 15-JAN-05 b.com commerce II 80 80 80
240 80
13 poorani 08-FEB-00 b.sc ct iii 80 85 85
250 83
SQL> create or replace function per
2 return decimal is
3 maxpercent decimal(5,2)
4 begin
5 select max(percentage) into maxpercent
6 from marksheet
7 return maxpercent;
8 end;
9 /
Function Created
SQL> set serveroutput on
SQL> declare
2 c decimal(5,2);
3 c:=maxpercent();
4 dbms_output.put_line('maximum percentage is:'||c);
5 end;
6 /
Maximum percentage is:83

RESULT:Thus the above program has been executed and verified successful.

CREATING A PL\SQL BLOCK TO HANDLE THE EXCEPTION.


10.
Aim:
To write PL/SQL block to handle the exception.

Algorithm:.
Step 1: Start the process.
Step 2: Create table and use the following attributes such as empno,empname, salary.
Step 3: Insert values for above attributes.
Step 4: Create on object for the attributes id and salary as v- empid and v -sal.
Step5: Stop the process.

PROGRAM:
SQL> create table emp(empno number(3),empname varchar(20),salary decimal(10,5))

Table created.

SQL> insert into emp values('&empno','&empname','&salary');


Enter value for empno: 101
Enter value for empname: uma
Enter value for salary: 10000
old 1: insert into emp values('&empno','&empname','&salary')
new 1: insert into emp values('101','uma','10000')

1 row created.

SQL> /
Enter value for empno: 102
Enter value for empname: sindhu
Enter value for salary: 20000
old 1: insert into emp values('&empno','&empname','&salary')
new 1: insert into emp values('102','sindhu','20000')
1 row created.

SQL> /
Enter value for empno: 103
Enter value for empname: dharani
Enter value for salary: 20000
old 1: insert into emp values('&empno','&empname','&salary')
new 1: insert into emp values('103','dharani','20000')

1 row created.

SQL> select * from emp;

EMPNO EMPNAME SALARY


---------- -------------------- ----------
101 uma 10000
102 sindhu 20000
103 dharani 20000

SQL> set serveroutput on


SQL> declare
2 v_empid emp.empno%TYPE;
3 v_sal emp.salary%TYPE;
4 v_raise number(3,2):=&p_raise;
5 begin
6 select empno,salary into v_empid,v_sal from emp
7 where empno=&p_empid for UPDATE NOWAIT;
8 UPDATE emp
9 set salary=salary+salary*v_RAISE
10 where empno=v_empid;
11 DBMS_OUTPUT.PUT_LINE('salary updated for employee'||v_empid);
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE IN TABLE');
15 END;
16 /
Enter value for p_raise: 0.30
old 4: v_raise number(3,2):=&p_raise;
new 4: v_raise number(3,2):=0.30;
Enter value for p_empid: 101
old 7: where empno=&p_empid for UPDATE NOWAIT;
new 7: where empno=101 for UPDATE NOWAIT;
salary updated for employee101

PL/SQL procedure successfully completed.

SQL> /
Enter value for p_raise: 0.20
old 4: v_raise number(3,2):=&p_raise;
new 4: v_raise number(3,2):=0.20;
Enter value for p_empid: 111
old 7: where empno=&p_empid for UPDATE NOWAIT;
new 7: where empno=111 for UPDATE NOWAIT;
NO SUCH EMPLOYEE IN TABLE

PL/SQL procedure successfully completed.


RESULT:Thus the above program has been executed and verified successful.

You might also like