INDEX

SLNO
1

Contents
Program to find roots of the quadratic equation Program to perform arithmetic operations Program to create employee table with empno and ename columns if emp table already exits then drop it and again create it 4 5 6 7 8 9 Program to display employee details of an empno entered Trigger to ensure that deptno is maintained as foreign key in emp table Trigger to ensure that sal is always greater than comm. In emp table Program to show example of exceptions no_data_found and too_many_rows Program to print jones experience Program to print nth Fibonacci number Program to display 7th row of emp table using explicit cursor Program to print sql report from emp table Program to manipulate sports table

Pgno

2 3

10 11 12 13

Program to update balance in stock table for all items in the master table, the trans table qty has to be accumulated and 14 added to bal of that item Program to insert values from pl/sql tables to database 15 tables Program to fetch and print empno,empname from emp table into pl/sql tables pl_empno and pl_ename

Program 1: /*PROGRAM TO FIND ROOTS OF THE QUADRATIC EQUATION*/ (Tell output for this)
SET SERVEROUT ON declare a number; b number; c number; r1 number(30,9); r2 number(30,9); dis number; begin a:=&a; b:=&b; c:=&c; dis:=round(sqrt((b*b)-(4*a*c))); dbms_output.put_line('dis is ' ||dis); r1:=(-b+dis)/(2*a); r2:=(-b-dis)/(2*a); dbms_output.put_line('ROOTS ARE: '); dbms_output.put_line('root1 is '||r1); dbms_output.put_line('root2 is '||r2); exception when zero_divide then dbms_output.put_line('sorry, your second input cannot be zero'); when value_error then

dbms_output.put_line('value is too large to fit into the variable'); when others then dbms_output.put_line('error code is '||sqlcode); dbms_output.put_line('error message is '||sqlerrm); end; /

OUTPUT:
Enter value for a: 1 old 9: a:=&a; new 9: a:=1; Enter value for b: 2 old 10: b:=&b; new 10: b:=2; Enter value for c: 1 old 11: c:=&c; new 11: c:=1; dis is0 ROOTS ARE: root1 is -1 root2 is -1

Program 2: /*PROGRAM TO PERFORM ARITHMETIC OPERATIONS*/ (tell output for this)

SET SERVEROUT ON declare a number(4); b number(4); sum1 number(8,2); diff number(8,2); prod number(8,2); div number(8,2); rem number(8,2); begin a:=&a; b:=&b; sum1:=a+b; dbms_output.put_line(the sum of a and b is|| ||sum1); diff:=a-b; dbms_output.put_line(the diff erence of a and b is|| ||diff); prod:=a*b; dbms_output.put_line(the product of a and b is|| ||prod); div:=a/b; dbms_output.put_line(the division of a and b is|| ||div); rem:=mod(a,b); dbms_output.put_line(the remainder of a and b is|| ||rem); exception when zero_divide then

dbms_output.put_line(cannot divide by zero); end; /

OUTPUT
Enter value for a: 3 old 10: a:=&a; new 10: a:=3; Enter value for b: 4 old 11: b:=&b; new 11: b:=4; the sum of a and b is 7 the diff erence of a and b is -1 the product of a and b is 12 the division of a and b is .75 the remainder of a and b is 3 PL/SQL procedure successfully completed.

Program 3: /*PROGRAM TO CREATE EMPLOYEE TABLE WITH EMPNO AND ENAME COLUMNS IF EMP TABLE ALREADY EXITS THEN DROP IT AND AGAIN CREATE IT*/
SET SERVEROUT ON declare a number; ans number; s varchar2(100); cid number; begin cid:=DBMS_SQL.open_cursor; select count(*) into a from tab where upper(tname)=EMP; if a=1 then s:=drop table emp; DBMS_SQL.parse(cid,s,dbms_sql.v7); ans:=DBMS_SQL.execute(cid); end if; s:=create table emp(empno number(4),ename varchar2(20)); DBMS_SQL.parse(cid,s,dbms_sql.v7); ans:=dbms_sql.execute(cid); DBMS_SQL.close_cursor(cid); end; /

OUTPUT: PL/SQL procedure successfully completed.

Program 4: /*PROGRAM TO DISPLAY EMPLOYEE DETAILS OF AN EMPNO ENTERED*/

SET SERVEROUT ON declare uempno number:=&enum; tempno emp.empno%type; tename emp.ename%type; tsal emp.sal%type; tjob emp.job%type; cursor c1 is select empno,ename,sal,job from emp; begin open c1; loop fetch c1 into tempno,tename,tsal,tjob; if c1%found and tempno=uempno then dbms_output.put_line('emp details are..'); dbms_output.put_line('employee number is: '||tempno); dbms_output.put_line('employee name is: '||tename); dbms_output.put_line('employee salary is: '||tsal); dbms_output.put_line('employee job is: '||tjob); elsif c1%notfound then raise no_data_found; exit; end if; end loop;

close c1; exception when no_data_found then dbms_output.put_line('record not found'); end; /

OUTPUT:
Enter value for enum: 1 old 2: uempno number:=&enum; new 2: uempno number:=1; emp details are.. employee number is: 7369 employee name is: Smith employee salary is: 200 employee job is: Clerk PL/SQL procedure successfully completed.

Program 5: /*TRIGGER TO ENSURE THAT DEPTNO IS MAINTAINED AS FOREIGN KEY IN EMP TABLE*/
SET SERVEROUT ON create or replace trigger t4 before insert or update of deptno on emp for each row declare a number; begin select count(*) into a from dept where deptno=:new.deptno; if a=0 then raise_application_error(-20000,the deptno is not found in master table); end if; end; /

OUTPUT:
Trigger created

Program 6: /*TRIGGER TO ENSURE THAT SAL IS ALWAYS GREATER THAN COMM IN EMP TABLE*/

SET SERVEROUT ON create or replace trigger t5 before insert or update of sal,comm on emp for each row declare begin if :new.comm>:new.sal then raise_application_error(-20000, commission greater than salary not allowed); end if; end; /

OUTPUT:
Trigger created

Program 7: /*PROGRAM TO SHOW EXAMPLE OF EXCEPTIONS NO_DATA_FOUND AND TOO_MANY_ROWS*/

SET SERVEROUT ON declare vename emp.ename%type; vjob emp.job%type; begin select ename,job into vename,vjob from emp where hiredate between 01-jan-92 and 31-dec-92; exception when no_data_found then dbms_output.put_line(no employees joined in the year 1992); when too_many_rows then dbms_output.put_line(more than one employee joined in the year 1992); when others then dbms_output.put_line(error code is ||SQLCODE); dbms_output.put_line(error message is ||SQLERRM); end; /

OUTPUT:
more than one employee joined in the year 1992 PL/SQL procedure successfully completed.

Program 8: /*PROGRAM TO PRINT JONES EXPERIENCE*/

SET SERVEROUT ON declare mn number(8,2); y number; m number; d number; a_rec emp%rowtype; begin select * into a_rec from emp where upper(ename)=JONES; mn:=months_between(sysdate,a_rec.hiredate); y:=floor(mn/12); m:=mod(floor(mn),12); d:=floor((mn-floor(mn))*30); dbms_output.put_line(jones experience is||y|| ||years|| ||m|| ||months|| || d|| ||days); exception when no_data_found then dbms_output.put_line(jones experience related data does not exist); end; /

OUTPUT:
jones experience is30 years 7 months 14 days PL/SQL procedure successfully completed.

Program 9: /* PROGRAM TO PRINT Nth FIBONACCI NUMBER */(tell output for this)
SET SERVEROUT ON declare a number:=0; b number:=1; c number; n number:=&no; i number; begin if n=1 then dbms_output.put_line(n|| st fibonacci number is ||a); elsif n=2 then dbms_output.put_line(n|| nd fibonacci number is ||b); else for i in 3..n loop c:=a+b; a:=b; b:=c; end loop; dbms_output.put_line(n|| th fibonacci number is ||c); end if; end; /

OUPUT:
Enter value for no: 6 old 5: n number:=&no; new 5: n number:=6; 6th fibonacci number is 5 PL/SQL procedure successfully completed.

Program 10:/*PROGRAM TO DISPLAY 7th ROW OF EMP TABLE USING EXPLICIT CURSOR*/
SET SERVEROUT ON declare cursor c1 is select * from emp; begin for a_rec in c1 loop if c1%rowcount=7 then dbms_output.put_line(a_rec.empno|| ||a_rec.ename|| ||a_rec.sal|| || a_rec.comm|| ||a_rec.job|| ||a_rec.hiredate|| ||a_rec.deptno); exit; end if; end loop; end; /

OUPUT:
7782 CLARK 2450 MANAGER 09-JUN-81 10 PL/SQL procedure successfully completed.

Program 11: /*PROGRAM TO PRINT SQL REPORT FOR EMP TABLE*/

SET SERVEROUT ON ttitle on btitle on ttitle HCL COMPANY LTD|L.POOL|HYD-29 btitle confidential --break on report skip 2 break on deptno skip 2 dup break on job skip 3 dup select * from emp order by deptno,job; column sal format $9,99,999.99 heading salary ttitle off btitle off /

OUPUT:
HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7782 CLARK 09-JUN-81 7839 KING salary JOB COMM MGR DEPTNO 7839 10 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------MANAGER $2,450.00 PRESIDENT confidential

HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 17-NOV-81 7788 SCOTT 19-APR-87 salary $5,000.00 ANALYST $3,000.00 confidential HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7902 FORD 03-DEC-81 salary JOB COMM MGR DEPTNO 7566 20 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------ANALYST $3,000.00 confidential HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7369 SMITH 17-DEC-80 7876 ADAMS 23-MAY-87 salary JOB COMM MGR DEPTNO 7902 200 20 7788 0 20 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------CLERK $800.00 0 20 JOB COMM MGR DEPTNO 10 7566 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ----------

CLERK $1,100.00 confidential

HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7566 JONES 02-APR-81 salary JOB COMM MGR DEPTNO 7839 20 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------MANAGER $2,975.00 confidential HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7900 JAMES 03-DEC-81 salary JOB COMM MGR DEPTNO 7698 0 30 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------CLERK $950.00 confidential HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7698 BLAKE 01-MAY-81 7499 ALLEN salary JOB COMM MGR DEPTNO 7839 30 7698 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------MANAGER $2,850.00 SALESMAN confidential HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 20-FEB-81 salary $1,600.00 JOB COMM 300 MGR DEPTNO 30 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ----------

7654 MARTIN 28-SEP-81 7844 TURNER 08-SEP-81

SALESMAN $1,250.00 1400 30

7698

SALESMAN $1,500.00 confidential 0 30

7698

HCL COMPANY LTD|L.POOL|HYD-29 EMPNO ENAME HIREDATE 7521 WARD 22-FEB-81 salary JOB COMM MGR DEPTNO 7698 30 ---------- -------------------- -------------------- ----------------------------- ------------- ---------- ---------SALESMAN $1,250.00 confidential 500

13 rows selected.

Program 12: ./*PROGRAM TO MANIPULATE SPORTS TABLE*/

SET SERVEROUT ON declare a sports.prodname%type; b sports.qty%type; a_rec sports%rowtype; begin a:=&prodname; b:=&qty; select * into a_rec from sports where upper(prodname)=upper(a); if a_rec.qty>=b then update sports set qty=a_rec.qty-b where upper(prodname)=upper(a); commit; dbms_output.put_line(b|| ||a||are issued); else dbms_output.put_line(wait for sometime not enough qty); end if; exception when no_data_found then dbms_output.put_line(the requested product is not maintained in the table); when value_error then

dbms_output.put_line(large no.of string cannot be stored in a variable of smal size); when others then dbms_output.put_line(error code is ||sqlcode); dbms_output.put_line(error message is ||sqlerrm); end; /

OUTPUT: (Tell me the tables to make in this one) make sports table with prodid,prodname,qty
PL/SQL procedure successfully completed

Program 13: /*PROGRAM TO UPDATE BAL IN STOCKTABLE FOR ALL ITEMS IN THE MASTER TABLE, THE TRANS TABLE QTY HAS TO BE ACCUMULATED AND ADDED TO BAL OF THAT ITEM*/
SET SERVEROUT ON declare cursor c1 is select * from master; cursor c2(p1 varchar2) is select * from trans where icode=p1; cursor c3 is select * from stock; sum1 number; a_rec master%row type; b_rec trans%row type; c_rec stock%row type; begin for a_rec in c1 loop sum1:=0; for b_rec in c2(a_rec.icode) loop sum1:=sum1+b_rec.qty; end loop; update stock set bal=bal+sum1 where icode=a_rec.icode; end loop; for c_rec in c3 loop dbms_output.put_line(c_rec.icode|| ||c_rec.bal);

end loop; exception when cursor_already_open then dbms_output.put_line(cursor is already open and you are trying to open it again);

when invalid_cursor then dbms_output.put_line(cursor name is misspelt or cursor not opened); when others then dbms_output.put_line(error code is ||sqlcode); dbms_output.put_line(error message is ||sqlerrm); end; /

OUTPUT: Make 3 tables: 1. stock table with icode,bal 2. trans table with icode qty 3. master table with icode iname
PL/SQL procedure successfully completed

Program 14:/*PROGRAM TO INSERT VALUES FROM PL/SQL TABLES TO DATABASE TABLES*/

SET SERVEROUT ON declare TYPE int_array IS TABLE OF number INDEX BY BINARY_INTEGER; TYPE char_array IS TABLE OF varchar2(15) INDEX BY BINARY_INTEGER; rows BINARY_INTEGER; p1_empno int_array; p1_ename char_array; p1_deptno int_array; begin rows:=&rows; for I in 1..rows loop p1_empno(i):=&eno; p1_ename(i):=&name; p1_deptno(i):=&dno; end loop; for I in 1..rows loop insert into emp1(empno,ename,deptno) values (p1_empno(i),p1_ename(i),p1_deptno(i)); end loop; end; /

OUTPUT: CREATE EMP1 table with empno,ename,deptno

PL/SQL procedure successfully completed

Program 15: /*PROGRAM TO FETCH AND PRINT EMPNO,ENAME FROM EMP TABLE INTO PL/SQL TABLES PL_EMPNO AND PL_ENAME*/
SET SERVEROUT ON declare TYPE int_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE char_array IS TABLE OF char(15) INDEX BY BINARY_INTEGER; rows BINARY_INTEGER; pl_empno int_array; pl_ename char_array; begin rows:=0; for emp_rec in (select * from emp) loop rows:=rows+1; pl_empno(rows):=emp_rec.empno; pl_ename(rows):=emp_rec.ename; end loop; for emp_rec in 1..rows loop dbms_output.put_line(pl_empno(emp_rec)|| ||pl_ename(emp_rec)); end loop; end; /

OUTPUT:
7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD PL/SQL procedure successfully completed.