PL/SQL queries(MG university)

These were my PL/SQL queries during my fifth semester. If you have any doubts, please let me know.

PL\SQL

1)<HAS>

DECLARE

RADIUS NUMBER(10);

AREA NUMBER(10);

BEGIN

RADIUS:=&RADIUS;

AREA:=3.14*RADIUS*RADIUS;

DBMS_OUTPUT. PUT_LINE(‘AREA: ’||AREA);

END;

2)

DECLARE

BASIC NUMBER(10,3);

GROSS NUMBER(10,3);

DA NUMBER(10,3);

HRA NUMBER(10,3);

PF NUMBER(10,3);

BEGIN

BASIC:=&BASIC;

DA:=0.4*BASIC;

HRA:=0.2*BASIC;

PF:=0.12*BASIC;

GROSS:=BASIC+DA+HRA-PF;

DBMS_OUTPUT. PUT_LINE(‘GROSS: ’||GROSS);

END;

3)

DECLARE

NUM NUMBER(10);

NUM1 NUMBER(10);

SUM1 NUMBER(10);

BEGIN

NUM:=&NUM;

SUM1:=0;

WHILE NUM<>0

LOOP

NUM1:=NUM MOD 10;

SUM1:=(SUM1 + NUM1);

NUM:=TRUNC(NUM/10);

END LOOP;

DBMS_OUTPUT.PUT_LINE (‘SUM: ‘||SUM1);

END;

4)

 CREATE TABLE ACCMASTER(ACCNO VARCHAR(10) PRIMARY KEY, NAME VARCHAR(10),BALANCE NUMBER(10,3));

CREATE TABLE ACCTRAN(ACCNO VARCHAR(10) REFERENCES ACCMASTER ON DELETE CASCADE,TRANSDATE DATE,DEB_CRED VARCHAR2(10) CHECK (DEB_CRED IN(‘DEBIT’,’CREDIT’)),AMOUNT NUMBER(10,3),PROCESSED VARCHAR2(10) CHECK(PROCESSED IN(‘YES’,’NO’)));

insert into accmaster values(‘10101A’,'NAMITHA',500.00);

insert into accmaster values(‘10201B’,'SANJANA',300.00);

insert into accmaster values(‘10302C’,'RAM',260.00);

insert into acctran values(‘10101A’,'14-JUN-13','DEBIT',700,'YES');

insert into acctran values(‘10201B’,'03-MAR-13','CREDIT',400,'NO');

insert into acctran values(‘10302C’,'1-SEP-13','DEBIT',2300,'NO');

set serveroutput on;

declare

   act acctran.accno%type;

   act1 acctran.accno%type;

    amt acctran.amount%type;

  bal accmaster.balance%type;

   begin

   select accno into act from acctran where deb_cred='DEBIT' and processed='NO';

   select amount into amt from acctran where accno=act;

  select balance into bal from accmaster where accno=act;

  update accmaster set balance=bal-amt where accno=act;

  select accno into act1 from acctran where deb_cred='CREDIT' and processed='NO';

  select amount into amt from acctran where accno=act1;

   select balance into bal from accmaster where accno=act1;

  update accmaster set balance=bal+amt where accno=act1;

 end;

SQL> select * from acctran;

ACCNO      TRANSDATE DEB_CRED       AMOUNT PROCESSED

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

10101A     14-JUN-13 DEBIT             700 YES

10201B     03-MAR-13 CREDIT            400 NO

10302C     01-SEP-13 DEBIT            2300 NO

5)

CREATE  TABLE EMPLOYEE(NAME VARCHAR(6),EMPNO NUMBER(5) PRIMARY KEY,SALARY NUMBER(5),DEPTNO NUMBER(5));

Table created.

CREATE TABLE EMP_RAISE(EMP_NO VARCHAR2(6),RAISE_DATE DATE,RAISE_AMT NUMBER(10));

Table created.

SQL> INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO');

Enter value for name: POOJA

Enter value for empno: 30

Enter value for salary: 1200

Enter value for deptno: 2

old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')

new   1: INSERT INTO EMPLOYEE VALUES('POOJA','30','1200','2')

1 row created.

Commit complete.

SQL> /

Enter value for name: ABC

Enter value for empno: 12

Enter value for salary: 5600

Enter value for deptno: 1

old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')

new   1: INSERT INTO EMPLOYEE VALUES('ABC','12','5600','1')

1 row created.

Commit complete.

SQL> /

Enter value for name: RAM

Enter value for empno: 8

Enter value for salary: 3000

Enter value for deptno: 2

old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')

new   1: INSERT INTO EMPLOYEE VALUES('RAM','8','3000','2')

1 row created.

Commit complete.

SQL> /

Enter value for name: JOHN

Enter value for empno: 64

Enter value for salary: 8000

Enter value for deptno: 3

old   1: INSERT INTO EMPLOYEE VALUES('&NAME','&EMPNO','&SALARY','&DEPTNO')

new   1: INSERT INTO EMPLOYEE VALUES('JOHN','64','8000','3')

1 row created.

Commit complete.

declare

  cursor c1 is select salary,empno from employee;

 

  sal employee.salary%type;

 emp employee.empno%type;

begin                                             

 open c1;

  loop

  fetch c1 into sal ,emp;

insert  into emp_raise  values(emp,sysdate,sal*0.05);

update employee set salary=(salary +(salary*0.05)) where employee.empno=emp;

exit when c1%notfound;

end loop;

close c1;

end;

LIBRARY:

CREATE TABLE STUDENT(REGNO NUMBER(10),NAME VARCHAR(10),DEPT NUMBER(3),CLASS NUMBER(5),FINE NUMBER(10,3),NO_OF_BOOKS NUMBER(5));

CREATE TABLE LIBRARY_BOOKS(BID NUMBER(10),TITLE VARCHAR(20),AUTHOR VARCHAR(15),EDITION NUMBER(2),FINE NUMBER(10,3),NO_OF_COPIES NUMBER(5));

CREATE TABLE STATUS (REGNO NUMBER(10),BID NUMBER(10),STATUS VARCHAR(15),AUTHOR VARCHAR(10),ISSUE_DATE DATE,RETURN_DATE DATE);

INSERT INTO STUDENT VALUES(‘&REGNO’,’&NAME’,’&DEPT’,’&CLASS’,’&FINE’,’&NO_OF_BOOKS’);

SQL> INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS');

Enter value for regno: 3

Enter value for name: NAMITHA

Enter value for dept: 12

Enter value for class: 5

Enter value for fine:

Enter value for no_of_books: 1

old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')

new   1: INSERT INTO STUDENT VALUES('3','NAMITHA','12','5','','1')

1 row created.

Commit complete.

SQL> /

Enter value for regno: 4

Enter value for name: POOJA

Enter value for dept: 10

Enter value for class: 6

Enter value for fine:

Enter value for no_of_books: 0

old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')

new   1: INSERT INTO STUDENT VALUES('4','POOJA','10','6','','0')

1 row created.

Commit complete.

Enter value for regno: 6

Enter value for name: RAM

Enter value for dept: 12

Enter value for class: 6

Enter value for fine:

Enter value for no_of_books: 2

old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')

new   1: INSERT INTO STUDENT VALUES('6','RAM','12','6','','2')

1 row created.

Commit complete.

SQL> /

Enter value for regno: 9

Enter value for name: NAVYA

Enter value for dept: 12

Enter value for class: 5

Enter value for fine:

Enter value for no_of_books: 1

old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&DEPT','&CLASS','&FINE','&NO_OF_BOOKS')

new   1: INSERT INTO STUDENT VALUES('9','NAVYA','12','5','','1')

1 row created.

Commit complete.

INSERT INTO LIBRARY_BOOKS VALUES(‘&BID’,’&TITLE’,’&AUTHOR’,’&EDITION’,’&NO_OF_COPIES’);

create or replace procedure issuebk(reg in varchar,bd in varchar,isdate in date)

 is

 aut varchar2(30);

b number(5);

cursor c1 is select  bid from status where bid=bd and regno=reg and status=’issued’;

begin

open c1;

fetch c1 into b;

if(c1%notfound) then

update student  set no_of_books=no_of_books+1 where regno=reg;

update library_books set no_of_copies=no_of_copies-1 where bid=bd;

select author into aut from library_books where bid=bd;

insert into status values(reg,bd,'issued',aut,isdate,'');

else

dbms_output.put_line(‘Book is already issued’);

end if;

close c1;              

end;

Procedure created.

 exec issuebk('3','401','02-sep-13');

SQL>  exec bissue('3','401','02-sep-13');

PL/SQL procedure successfully completed.

Commit complete.

SQL>

SQL> select * from status;

     REGNO        BID STATUS          AUTHOR     ISSUE_DAT RETURN_DA

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

         3        401 issued          DAN BROWN  02-SEP-13

return:

create or replace procedure returnbk(reg2 in varchar,bidno in varchar)

is

famt number(6);

issdate date;

begin

update student set no_of_books=no_of_books-1 where REGNO=reg2;

update library_books set no_of_copies=no_of_copies+1 where BID=bidno;

select ISSUE_DATE into issdate from status where REGNO=reg2;

if((sysdate-issdate)>10)

then

famt:=(sysdate-issdate-10)*10;

else

famt:=0;

end if;

 update student set fine=famt where regno=reg2;

update status set return_date=sysdate where regno=reg2;

update status set status=’returned’ where regno=reg2;

end;

Procedure created.

exec returnbk(‘3’,’401’);

PL/SQL procedure successfully completed.

Commit complete.

SQL> select * from status;

     REGNO        BID STATUS          AUTHOR     ISSUE_DAT RETURN_DA

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

         3        401 issued          DAN BROWN  02-SEP-13 09-OCT-13

SQL> select * from status;

     REGNO        BID STATUS          AUTHOR     ISSUE_DAT RETURN_DA

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

         3        401 returned        DAN BROWN  02-SEP-13 09-OCT-13

         2        403 issued          STEFFANI   07-OCT-13

MAXIMUM FINE

create or replace function maximum(maxm out number)

 return number

 is

 begin

 select max(fine) into maxm from student;

 dbms_output.put_line('Maximum fine is '||maxm);

 return maxm;

 end;

Function created.

set  serveroutput on;

declare

a number(5);

begin

a:=maximum(a);

end;

Maximum fine is 376

PL/SQL procedure successfully completed.

Commit complete.

TRIGGER 1:

create or replace trigger due after update of return_date on status for each row

begin

 if(:new.return_date>(:old.issue_date+10)) then

dbms_output.put_line('Returndate exceeds the due date');

end if;

end;

              Trigger created.

SQL> update status set return_date='25-oct-13' where regno=2;

Returndate exceeds the due date

1 row updated.

Commit complete.

TRIGGER 2:

              create or replace trigger more before update of no_of_books on student for each row

                 begin

                 if(:new.no_of_books>5) then

                 raise_application_error(-20000,'No of books exceeded five');

                 end if;

                 end;

Trigger created.

SQL> update student set no_of_books=7 where regno=3;

update student set no_of_books=7 where regno=3

       *

ERROR at line 1:

ORA-20000: No of books exceeded five

ORA-06512: at "U1103079.MORE", line 3

ORA-04088: error during execution of trigger 'U1103079.MORE'