PR-22
XI
1.
DECLARE
a NUMBER;
b NUMBER;
result NUMBER;
BEGIN
BEGIN
A:=&a;
B:=&b;
result := a / b;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;
END;
2.
Declare
C_id customer.id%type;
C_name Customer.name%type;
C_addr customer.addr%type;
C_sal customer.Sal%type;
Begin
C_id:=&C_id;
Select ID, Name, Addr, Sal into C_id, C_name , C_addr, c_Sal from Customer where
Id=C_id;
Dbms_Output.Put_line('ID: '|| C_id);
Dbms_Output.Put_line('Name: '|| C_Name);
Dbms_Output.Put_line('Addr: '|| C_addr);
Dbms_Output.Put_line('Sal: '|| C_Sal);
Exception
When no_data_found Then
Dbms_Output.Put_line(' Employee Id Not Found');
When Others then
Dbms_Output.Put_line('Errors');
End;
/
PR-23
XI
1.
2.
Declare
C_id customer.id%type:=&CC_id;
C_name Customer.name%type;
C_addr customer.addr%type;
C_sal customer.Sal%type;
ex_invalid_id Exception;
Begin
If C_id <=0 then
Raise Ex_invalid_id;
Else
Select ID, Name, Addr, Sal into C_id, C_name , C_addr, c_Sal from
Customer where Id=C_id;
Dbms_Output.Put_line('ID: '|| C_id);
Dbms_Output.Put_line('Name: '|| C_Name);
Dbms_Output.Put_line('Addr: '|| C_addr);
Dbms_Output.Put_line('Sal: '|| C_Sal);
End if;
Exception
When ex_invalid_id then
Dbms_Output.Put_line(' ID must be greater than zero! ');
When no_data_found then
Dbms_Output.Put_line(' Employee Id not Exist');
When Others then
Dbms_Output.Put_line('Errors');
End;
/
PR-24
XI
1.
Create procedure emp_count (E In number,S out number) is
Begin
select count(ID) into S from emp where Dept_No=E;
end;
Sql> Variable k number;
Sql> exec emp_count(10,:K);
Sql> print k;
2.
Create or replace procedure Greet_User (Name in varchar2)
is
Begin
Dbms_Output.Put_Line ('Welcome'||Name);
End;
/
PR-25
XI
1.
Create or Replace function Max_of_Two(val1 in number,val2 in number) Return Number
Is
Begin
If val1> val2 then
Return val1;
Else
Return val2;
End if;
End;
/
Select Max_of_Two(5,7) from dual;
2.
Create or replace function GetFactorial( No int) return varchar2
Is
Fact int :=1;
Begin
For I in 1 .. No
Loop
Fact:=Fact*I;
End loop;
Return 'Factorial of ' || No ||' is='|| Fact ;
End;
/
SQL> select getfactorial(5) from dual;
PR-26
XI
create table account (Accno number, Name varchar2(10),Balance number);
insert into account values(1,'a',1000);
insert into account values(2,'b',2000);
insert into account values(3,'c',3000);
insert into account values(4,'d',4000);
select * from account;
1.
create or replace trigger tr2 before update on account for each row
when (new.balance <5000)
begin
dbms_output.put_line('Can Not Withdraw as Balance is Less Than 2000');
end;
2.
create table transaction(TID number,ACCno number,TDate date,Amount number,Balance
number);
select * from transaction;
create sequence ac_seq;
create or replace trigger actr1
before update on account for each row
begin
insert into transaction
values(ac_seq.nextval,:new.accno,sysdate,abs(:new.balance-:old.balance),:new.balance);
end;
update account set balance=balanace+5000 where accno=2;
