Solved PL/SQL Programs - With Database

 

Write a program that will accept an account number and the amount from the user. Credit the account with given amount.

ano	Name	Balance
A01	SURESH	5000
A02	RAJ	3000
A03	REAL	2000

declare

            acc_no number := &acc_no;

            amt number := &amt;

            bal number;

begin

select balance into bal from account where ano=acc_no;

                        update account set balance = bal + amt

                        where ano = acc_no;

            dbms_output.put_line('the account is updated');

end;

 OUTPUT:

SQL> Enter value for acc_no: A02                      

SQL> Enter value for amount: 500                      

SQL> account update                                

Write a program that will accept an account number and the debit amount from the user. Debit the balance if the resulting balance is greater than zero.

ano	Name	Balance
A01	SURESH	5000
A02	RAJ	3000
A03	REAL	2000

declare

            acc_no account.ano%type;

            bal account.balance%type;

            newbal account.balance%type;

            amount number;

begin

            acc_no := '&acc_no';

            amount := &amount;

            select balance into bal from account where ano = acc_no;

            newbal := bal-amount;

            if newbal >= 0 then

                        update account

                        set balance = newbal

                        where ano = acc_no;

                        dbms_output.put_line('account debited');

            else

                        dbms_output.put_line('account does not have sufficeint amount');

            end if;

end;

 OUTPUT:

SQL> Enter value for acc_no: A02                       SQL> Enter value for acc_no: A03

SQL> Enter value for amount: 500                       SQL> Enter value for amount: 2500

SQL> account debited                                           SQL> account does not have sufficient amount

 Write a program that will accept an account number and check the balance for it. If the balance is less than or equal to 5000 then deduct a fine of Rs. 100/- from the balance.

ano	Name	Balance
A01	SURESH	5000
A02	RAJ	3000
A03	REAL	2000

declare

            acc_no account.ano%type;

            bal account.balance%type;

            fine number := 100;

begin

            acc_no := '&acc_no';

            select balance into bal from account where ano = acc_no;

            if bal <= 5000 then

                        update account

                        set balance = bal - fine

                        where ano = acc_no;

                        dbms_output.put_line('Account fined and rupees 100 deducted');

            else

                        dbms_output.put_line('account does not have sufficeint amount');

            end if;

end;

 OUTPUT:

SQL> Enter value for acc_no: A02                      

Account fined and rupees 100 deducted.

 Write a program to debit the given account number with amount specified by user. If resultant balance is negative then rollback operation else commit.

ano	Name	Balance
A01	SURESH	5000
A02	RAJ	3000
A03	REAL	2000

 

declare

            acc_no account.ano%type:=&acc_no;

            bal account.balance%type;

            amt number:=&amt;

begin

            update account

            set balance = balance - amt

            where ano = acc_no;

            select balance into bal from account where ano = acc_no;

            if bal < 0 then

                        rollback;

            else

                        commit;

            end if;

end;

 OUTPUT:

Enter value for acc_no: 1

Enter value for amt: 2000

PL/SQL procedure successfully completed.

 Write a program that will read an account number for two different accounts. Find the highest balance from them and display its account number and balance.

ano	Name	Balance
A01	SURESH	5000
A02	RAJ	3000
A03	REAL	2000

 

declare

            acc_no1 account.ano%type := &acc_no1;

            acc_no2 account.ano%type := &acc_no2;

            bal1 account.balance%type;

            bal2 account.balance%type;

begin

            select balance into bal1 from account where ano = acc_no1;

            select balance into bal2 from account where ano = acc_no2;

            if bal1 > bal2 then

                        dbms_output.put_line ('Higest balance');

                        dbms_output.put_line ('Account no is :'||acc_no1);

                        dbms_output.put_line ('Balance is :'|| bal1);

            else

                        dbms_output.put_line ('Higest balance');

                        dbms_output.put_line ('Account no is :'||acc_no2);

                        dbms_output.put_line ('Balance is :'|| bal2);

             end if;                        

end;

Output:

Enter value for acc_no1: A01

Enter value for acc_no2: A03

Higest balance

Account no is : A01

Balance is : 5000

Write a program that will accept an employee number from the user and the deduct a salary by a Rs. 2000/- from that employee. If employee has a salary less than 10000 after the salary is deducted, it display message ‘SALARY less than 10000’.

empno	Name	Salary
E01	SURESH	15000
E02	RAJ	12000
E03	REAL	13500

declare

eno emp.empno%type;

            debit_amt number(4):= 2000;

            sal emp.salary%type;

            newsal emp.salary%type;

begin

            eno:=&eno;

            select salary into sal from emp where empno=eno;

            newsal := sal-debit_amt;

            if newsal > 10000 then

                        update emp

                        set salary = newsal

                        where empno = eno;

                        dbms_output.put_line('Salary deducted');

            else

                        dbms_output.put_line('Salary is less than 10000');

            end if;

end;

 OUTPUT:

SQL> Enter value for eno: A02                             SQL> Enter value for eno: A02

SQL> Salary deducted                                           SQL> Salary is less than 10000

Trigger Solved Programs

 Write a trigger that will not allow to change the data of emp table after office hours i.e. From Monday to Saturday( from 8:00 AM to 6:00 PM). There is no restriction on viewing the data

create or replace trigger business_time

before

insert or update or delete

on account

for each row

declare

day1 char(12);

            time1 char(2);

begin

            select to_char(sysdate, 'DY'), to_char(sysdate,'HH24') into day1,time1 from dual;

                        if (day1='SAT' or day1 = 'SUN') or (time1 < 8 or time1 > 18) then

                                    raise_application_error(-20001,'No operation allowed on saturday and

                        sunday. it is allowed between 8:00AM to 6:00PM only’);

                       end if;

end;

OUTPUT:(day is Sunday & time is 9:00PM)

SQL> insert into account (name) values('scc');

insert into account (name) values('scc')

            *

ERROR at line 1:

ORA-20001: No operation allowed on saturday and sunday. it is allowed between

8-6

ORA-06512: at "SCOTT.BUSINESS_TIME", line 7

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

 Write a program using cursor that inserts the records from student table to stud_backup table. For every insertion a trigger should be called. If student is fail in any of the three subjects that students record should not be written in stud_backup table.

Student                                                                                  Stud_backup(after insert)

Rollno	Name	Eng	Math	Sci		rollno	Name	Eng	Math	Sci
E01	SURESH	80	80	80		E01	SURESH	80	80	80
E02	RAJ	34	78	76
E03	REAL	67	30	66

create or replace trigger pass_stud_backup

before insert

on stud_backup

for each row

begin

            if (:new.eng < 35 or :new.sci < 35 or :new.math < 35) then

                        raise_application_error(-20003,'student is not fully passed');

            end if;

end;

/ 

Trigger Created.

 declare

            student_no student.rno%type:=&student_no;

            cursor stud_insert is select * from student where rno=student_no;

begin

            for stud_rect in stud_insert

            loop

                        insert into stud_backup

                        values(stud_rect.rno,stud_rect.name,

           stud_rect.eng,stud_rect.math,stud_rect.sci);

            end loop;

   commit;

end;

 OUTPUT:

SQL> Enter value for student_no: E01                           

SQL> PL/SQL procedure successfully completed.

 Enter value for student_no: 2

old   2: student_no student.rno%type:=&student_no;

new   2: student_no student.rno%type:=2;

 declare

*

ERROR at line 1:

ORA-20003: student is not fully passed

ORA-06512: at "SCOTT.PASS_STUD_BACKUP", line 3

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

ORA-06512: at line 7          

 Create trigger that does not allows the changes to emp table only for user “scott’ and “real”. If other user tries to change then allow them.

create or replace trigger give_right

before

insert or update or delete

on emp

for each row

            begin

            if user='scott' or user = 'SCOTT' or user = 'Real' or user = 'REAL' then

                        raise_application_error(-20001, 'Operation not allowed to you');

            end if;

end;

 OUTPUT:

SQL> /

insert into emp (empno, sal)

            *

ERROR at line 1:

ORA-20001: Operation not allowed to you

ORA-06512: at "SCOTT.GIVE_RIGHT", line 3

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

 Create trigger that allows the changes to emp table only for user “scott’ . If other user tries to change then they should not be allowed.

 create or replace trigger give_right1

before

insert or update or delete

on emp

for each row

            begin

                        if user not like 'scott' or user not like 'SCOTT' then

                        raise_application_error(-20001, 'Operation not allowed to you');

            end if;

end;

 OUTPUT: 

if other user login and tries to insert then output is below:   

SQL>  insert into scott.emp

  2* (empno,sal) values (33, 44)

SQL> /

insert into scott.emp

                  *

ERROR at line 1:

ORA-20001: Operation not allowed to you

ORA-06512: at "SCOTT.GIVE_RIGHT1", line 3

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