[Link] a PL/SQL Procedure to display the branchname and amount of a given loan no.
[Link]
set serveroutput on;
create or replace procedure dispbranchamt(lno [Link]%type)
is
amt [Link]%type;
bname [Link]%type;
begin
select amount,branchname into amt,bname from loan where loanno=lno;
dbms_output.put_line('Amount is ' ||amt);
dbms_output.put_line('Branch is ' ||bname);
end;
/
SQL> start e:\plsql\[Link];
Procedure created.
[Link]
declare
l [Link]%type;
begin
l:='&loanno';
dispbranchamt(l);
end;
/
SQL> start e:\plsql\[Link];
Enter value for loanno: L-22
old 4: l:='&loanno';
new 4: l:='L-22';
Amount is 2000
Branch is Redwood
PL/SQL procedure successfully completed.
2. Create a PL/SQL Procedure to display the customer name on entering the loan number, handling
exceptions in the following cases. (i) more than one customers with the specified loan number (ii) no
customer with specified loan number.
[Link]
set serveroutput on;
create or replace procedure loan_cust(lno in [Link]%type) is
cname [Link]%type;
begin
select customername into cname from borrower where loanno=lno;
dbms_output.put_line('Customer Name is '||cname);
exception
when too_many_rows then raise_application_error(-20004,'Duplicate rows exist with same loan
number');
when no_data_found then raise_application_error(-20005,'Record not found');
end;
/
SQL> start e:\plsql\[Link];
Procedure created.
declare
l [Link]%type;
begin
l:='&loanno';
loan_cust(l);
end;
/
SQL> start e:\plsql\[Link];
Enter value for loanno: L-22
old 4: l:='&loanno';
new 4: l:='L-22';
Customer Name is Smith
PL/SQL procedure successfully completed.
SQL> insert into borrower values('Jerry','L-17');
1 row created.
SQL> start e:\plsql\[Link];
Enter value for loanno: L-17
old 4: l:='&loanno';
new 4: l:='L-17';
declare
*
ERROR at line 1:
ORA-20004: Duplicate rows exist with same loan number
ORA-06512: at "SYSTEM.LOAN_CUST", line 7
ORA-06512: at line 5
SQL> start e:\plsql\[Link];
Enter value for loanno: L-24
old 4: l:='&loanno';
new 4: l:='L-24';
declare
*
ERROR at line 1:
ORA-20005: Record not found
ORA-06512: at "SYSTEM.LOAN_CUST", line 8
ORA-06512: at line 5
3. Write a PL/SQL block which uses cursor to display the department details.
[Link]
set serveroutput on;
declare
cursor c1 is
select deptid,deptname from dept;
did [Link]%type;
dname [Link]%type;
begin
open c1;
loop
fetch c1 into did,dname;
exit when c1%notfound;
dbms_output.put_line(did||' '||dname);
end loop;
close c1;
end;
/
SQL> start e:\plsql\[Link]
d1 HR
d2 production
PL/SQL procedure successfully completed.
4. Write a function in Pl/SQL to retrieve the salary of an employee on accepting employee id.
[Link]
create or replace function sal(id in [Link]%type)
return [Link]%type is
sal [Link]%type;
begin
select salary into sal from employee where empid=id;
return(sal);
end;
/
SQL> start e:\plsql\[Link]
Function created.
set serveroutput on;
declare
id [Link]%type;
s [Link]%type;
begin
id:='&EmployeeID';
s:=sal(id);
dbms_output.put_line('Salary of '||id||'is '||s);
end;
/
SQL> start e:\plsql\[Link]
Enter value for employeeid: 101
old 5: id:='&EmployeeID';
new 5: id:='101';
Salary of 101is 16000
PL/SQL procedure successfully completed.
5. Create a PL/SQL Package with a procedure to display the department details and a function to
retrieve salary of employee with given empid.
[Link]
create or replace package pack1 as
procedure deptdetails(n in [Link]%type);
procedure dispsal(id in [Link]%type);
end;
/
create or replace package body pack1 as
procedure deptdetails(n in [Link]%type) is
name [Link]%type;
begin
select deptname into name from dept where deptid=n;
dbms_output.put_line('Department ID is'||n||'and Department name is '||name);
end deptdetails;
procedure dispsal(id in [Link]%type) is
sal [Link]%type;
begin
select salary into sal from emp where empid=id;
dbms_output.put_line('Emp ID is'||id||'and Salary is '||sal);
end dispsal;
end pack1;
/
set serveroutput on;
declare
did [Link]%type;
eid [Link]%type;
begin
did:='&Dept_ID';
eid:='&Emp_ID';
[Link](did);
[Link](eid);
end;
/
SQL> start e:\plsql\[Link]
Package created.
Package body created.
Enter value for dept_id: d1
old 6: did:='&Dept_ID';
new 6: did:='d1';
Enter value for emp_id: e1
old 7: eid:='&Emp_ID';
new 7: eid:='e1';
Department ID isd1and Department name is HR
Emp ID ise1and Salary is 300000
PL/SQL procedure successfully completed.
6. Create a PL/SQL trigger to insert employee id and salary of an employee to a table named TRACK,
when a new record is inserted into the employee table.
[Link]
SQL> create table track(id varchar(5),sal number(10));
Table created.
create or replace trigger trig1
after
insert on emp
for each row
begin
insert into track values(:[Link],:[Link]);
end;
/
SQL> start e:\plsql\[Link]
Trigger created.
SQL> insert into emp values('e6','manasi','varun','p2','s1','21-jan-2006','13-mar-
1985',40000,300,'d2','q2');
1 row created.
SQL> select * from track;
ID SAL
----- ----------
e6 40000