BANK PROJECT AND PL/SQL
KCB_ACC_TAB
------
1 create table kcb_acc_tab
2 (
3 accno number primary key,
4 name varchar2(20) constraint name_nn not null,
5 actype char check(actype in('s','c','fd')),
6 doo date default sysdate,
7 bal number(8,2) not null
8* )
QL> /
Table created.
QL> insert into kcb_acc_tab values(37002167543,'srinivas','s',sysdate,15000)
2 /
row created.
QL> commit
2 /
commit complete.
KCB_TRAN_TAB
------
create table kcb_tran_tab
(
tid number,
accno number(20) references kcb_acc_tab(accno),
trtype char(10) check(trtype in('d','w')),
dot date default sysdate,
amt number(7,2) check(amt>100)
)
SEQUENCE
------
create sequence s1
start with 1
increment by 1
maxvalue 1000
minvalue 0
nocache
nocycle
1) Write a PL/SQL program to modify the balance after deposite the amt and insert the
transaction details also.
declare
i kcb_acc_tab%rowtype;
k kcb_tran_tab%rowtype;
begin
i.accno:=&accno;
k.trtype:='&trtype';
k.amt:=&amount;
select bal into i.bal from kcb_acc_tab
where accno=i.accno;
if k.trtype='D' then
i.bal:=i.bal+k.amt;
end if;
update kcb_acc_tab set bal=i.bal where accno=i.accno;
insert into kcb_tran_tab values(s1.nextval,i.accno,k.trtype,sysdate,k.amt);
commit;
end;
2) write a PL/SQL program for enter the transaction details perform the validation
i)if it is deposite update the bal and insert the transaction details
ii) if it is withdraw before withdraw
check the current bal if validationcontrol satisfy then only
perform the withdraw
declare
i kcb_acc_tab%rowtype;
k kcb_tran_tab%rowtype;
begin
i.accno:=&accno;
k.trtype:='&trtype';
k.amt:=&amt;
select actype,bal into i.atype,i.balance from kcb_acc_tab where accno=i.accno;
if k.trtype='D' then
i.bal:=i.bal+k.amt;
else
i.bal:=i.bal-k.amt;
if i.actype='s' and i.bal<5000 then
Raise_application_error(-20456,'the bal is too low to perform transaction');
endif;
update kcb_acc_tab set bal=i.bal
where accno=i.accno;
insert into kcb_tran_tab values(s1.nextval,i.accno,k.trtype,sysdate,k.amt);
commit;
end;
PROCEDURE
------
create or replace procedure upd_bal
(paccno kcb_acc_tab.accno%type,
pamt kcb_tran_tab.amt%type)
is
cbal kcb_acc_tab.bal%type;
begin
select bal into cbal from kcb_acc_tab where accno=paccno;
cbal:=cbal+pamt;
update kcb_acc_tab set bal=cbal where accno=paccno;
insert into kcb_tran_tab values(1001,paccno,'d',sysdate,pamt);
commit;
exception
when no_data_found then
display(paccno||'is not exists');
end upd_bal;
create or replace procedure upd_bal
(paccno kcb_acc_tab.accno%type,
pamt kcb_tran_tab.amt%type)
is
cbal kcb_acc_tab.bal%type;
vatype kcb_acc_tab.atype%type;
begin
select acctype,bal into vatype,cbal from kcb_acc_tab where accno=paccno;
if upper(pttype)='d' then
cbal:=cbal+pamt;
elsif upper(pttype)='w' then
cbal:=cbal-pamt;
if value='s' and cbal<5000 then
Raise_application_error(-20456,'there is insufficient balance so we cannot do the transaction:');
end if;
end if;
update kcb_acc_tab set bal =cbal
where accno=paccno;
insert into kcb_tran_tab
values(101,paccno,ptrtype,sysdate,pamt);
commit;
exception
when no_data_found then
display(paccno||'is not exist');
end upd_bal;
FUNCTIONS
------
write a function the account holder is eligible for the withdraw or not
create or replace function chk_bal
(paccno kcb_acc_tab.accno%type,
pamt kcb_tran_tab.amt%type)
return boolean
is
cbal kcb_acc_tab.bal%type;
vatype cb_acc_tab.acctype%type;
begin
select acctype,bal into vacctype,cbal from kcb_acc_tab where
accno=paccno;
cbal:=cbal-pamt;
if vacctype='s' and cbal<5000 then
return(false);
elsif vatype='c'and cbal<10000 then
return(false);
else
return(true);
end if;
end chk_bal;
call this function with another pl/sql pgm with appropriate msg.
begin
if chk_bal(&accno,&amt)then
display('it is validate');
else
display('it is not validate');
end if;
end;
call this function in a procedure for the validation
create or replace procedure upd_bal
(paccno kcb_acc_tab.accno%type,
ptrtype kcb_tran_tab.trtype%type,
pamt kcb_acc_tab.amt%type)
is
cbal kcb_acc_tab.bal%type;
begin
select bal into cbal
from kcb_acc_Tab
where accno=paccno;
if upper(ptrtype)='D' then
cbal:=cbal+pamt;
elsif upper(ptrtype)='w' then
if chk_bal(paccno,pamt)then
cbal:=cbal-pamt;
else
Raise_application_error(-20456,'There IB so we cannot do the transaction:');
end if;
end if;
update kcb_acc_tab set bal=cbalwhere accno=paccno;
insert into kcb_tran_tab values(101,paccno,ptrtype,sysdate,pamt);
commit;
exception
when no_data_found then
display(paccno||'is not exist');
end upd_bal;
PACKAGES
------
PACKAGE SPECIFICATION
------
create or replace package pack_updbal
as
cbal bankmaster.curr_bal%type;
procedure upd_bal(vaccno kc b_acc_tab.accno%type,
vtype kcb_tran_tab.ttype%type,
vamt kcb_tran_tab.amt%type);
function chk_bal(vaccno kcb_acc_tab.accno%type,
vamt kcb_tran_tab.amt%type)
return boolean;
cbal.kcb_acc_tab.bal%type;
end pack_updbal;
PACKAGE BODY
------
create or replace package body pack_updbal
as
procedure upd_bal(vaccno kcb_acc_tab.accno%type,
vtrtype kcb_tran_tab.trtype%type,
vamt kcb_tran_tab.amt%type)
is
begin
select bal into cbal
from kcb_acc_tab
where accno=vaccno;
if upper(vtype)='w' then
cbal:=cbal_vamt;
end if;
update kcb_acc_tab set sal=cbal where accno=vaccno;
commit;
end upd_bal;
function chk_bal(vaccno kcb_acc_tab.accno%type,
vamt kcb_tran_tab.amt%type)
return boolean
is
vatype kcb_acc_tab.acctype%type;
begin
select acctype,bal into vatype,cbal from kcb_acc_tab where accno=vaccno;
cbal:=cbal-vamt; (global variable)
if vatype='s' and cbal<5000 then
return(false);
elsif vatype='c' and cbal<10000 then
return(false);
else
return(true);
end if;
end chk_bal;
end pack_updbal;
Triggers
------
create or replace trigger trg_bal
before insert
on kcb_tran_tab
for each row
begin
if :new.trtype='d' then
pack_updbal.upd_bal(:new.accno,:new.trtype,:new.amt);
elsif :new.trtype='w' then
if pack_updbal.chk_bal(:new.accno,:new.amt)then
pack_updbal.upd_bal(:new.accno,:new.trtype,:new.amt);
else
Raise_application_error(-20451,'the bal is too low so no transaction:');
end if;
end if;
exception
when no_data_found then
display(:new.accno||'is not exists');
end;
PL/SQL:
It is a programming language which is developed by oracle company.
It is a procedural language it is used to process only a row at a time where
as non procedural laguage process a set of rows at a time.
It support to execute a bloc of stmts at once.
Block: collection of executable statements.
struture of block:
Declare
[variable Declaration];
Begin
<executable statements>;
[exception
executable statements];
End;
There are two types of blocks
I) Anonoums block
II) named block
Anonmous Blcok:
The Block which is having no name called as anonmous Block
This block cannot call any other programs.
used in D2K forms.
Named Block:
The Block which is having a named called as named block.
This block can call in other PL/SQL programs.
eg: procedure
function
Trigger
package
PL/SQL supports the variables&constraints
SQL will supports the Bind variables only.
eg\; var a number
exec a:=1000
print :a
PL/SQL will support bind variables &list variables.
It support the Error handlings.
In SQL we can see the errors on the program Or select stmt,
But we cannot handle&provide the solution.
Where as in PL/SQL we can handle that errors and provides the
Appropriate actions.
It supports conditional constructs.
It supports the Iteration controls
i)simple loop
ii)while loop
iii)for loop
It supports the sub programs
There are Two types of sub programs:
i)function
ii)proedure
EG:
declare
Begin
null;
end;
Data types in PL/SQL:
Scalar
Composite
Eg: Table
Record
varray
Reference:
Ref cursor
Ref object_type
LOB
Variable:
variables are used to store datavalues that are used by pl/sql
variables are represents memory locations used to store user or database data.
variables supports the simple data types and composite data types.
Host variables are supports the Boolean Datatypes where as
Bind variables are not supports the Boolean Datatypes.
Syntax: <variable name> datatype(size);
declaration part only u declare the variables.
eg: declare
v_empno number(4):=7902;
v_name varchar2(20) not null;
note: we should not assign the null values.
Assignment operators:
Into: This operator for internal values
:= This operator any external values.
Executable sub languages are:
DQL
DML
TCL
We cannot use DDL,DCL directely in PL/SQL by using dynamic SQL.
Syntax of Select statement:
Select <column list>into <variable list>
from <table name>
where <condition>;
Comments in PL/SQL:
There are Two types of comments:
i)- -single line comment
ii) /* multi line comment */
DBMS_OUT.PUT_LINE('Message'||Variable);
it is used to print the msg and variable value on the screen.
Set serveroutput on
It is environment command used to activates DBMS Statemens.
SQL> declare
2 v_sal number(7,2);
3 v_comm number(7,2);
4 net number(7,2);
5 begin
6 v_sal:=&salary;
7 v_comm:=&comm;
8 net:=v_sal+nvl(v_comm,0);
9 dbms_output.put_line('the net sal is:'||net);
10 end;
1 declare
2 v_sal number(7,2);
3 v_comm number(7,2);
4 net number(7,2);
5 begin
6 dbms_output.put_line('the net sal is:'||(&sal+nvl(&comm,0)));
7* end;
1 declare
2 vempno number(4):=&empno;
3 vename varchar2(20);
4 vsal number(7,2);
5 vcomm number(7,2);
6 netsal number(7,2);
7 begin
8 select ename,sal,comm into vename,vsal,vcomm from emp
9 where empno=vempno;
10 netsal:=vsal+nvl(vcomm,0);
11 dbms_output.put_line('ename'||' '||'sal'||' '||'comm'||' '||'netsal');
12 dbms_output.put_line(rpad(vename,7)||' '||rpad(vsal,7)||' '||rpad(vcomm,7)||' '||n
13* end;
Nested Block:
PL/SQL block can be nested the block which is declarew in another
Block called as nested block or inner block or child block.
Declare
Begin
Declare
Begin
end;
end;
note: variable forward Reference is possible the backword reference may not possible.
1 declare
2 m number:=100;
3 begin
4 m:=500;
5 declare
6 n number:=400;
7 total number;
8 begin
9 m:=600;
10 total:=m+n;
11 dbms_output.put_line('the sum of m,n is:'||total);
12 end; --end the inner block
13 dbms_output.put_line('the m value is:'||m);
14* end;
Variable Attributes:
There are Two types of variable attributes.
By using this variable attributes we can Make the Datatype,size
independentely for a variable..
Column Type Attribute:
Syntax:
<variable name> <table name>.<column name>%type;
Percentile type(%):- used to declare column type variables.
eg: vename emp.ename%type;
declare
2 vname emp.ename%type;
3 begin
4 select ename into vname from emp
5 where empno=&eno;
6 dbms_output.put_line('the ename:'||vname);
7 end;
declare
2 vname emp.ename%type;
3 vdeptno emp.deptno%type;
4 begin
5 select ename,deptno into vname,vdeptno from emp
6 where empno=&eno;
7 dbms_output.put_line('the ename,deptno:'||vname||vdeptno);
8* end;
Row Type variable attribute:
It is used to declare a record type variable
Syntax:
<variable name> <table name>%row type;
eg: i emp%row type;
note:in emp table how many columns are there all are represent or available in i variable.
1 declare
2 i emp%rowtype;
3 begin
4 i.empno:=&eno;
5 select ename,sal,comm,deptno into i.ename,i.sal,i.comm,i.deptno from emp
6 where empno=i.empno;
7 dbms_output.put_line('the emp details are:'||i.ename||' '||i.sal||' '||i.comm||i.deptno);
8* end;
create or replace procedure
display(s varchar2)
is
begin
dbms_output.put_line(s);
end;
Flow control statements:
By using flow control statements we can manipulates and process oracle data.
The categery of flow control statements are:
conditional control
iterative control
conditional controls:
sequences of statements can be executed based on certain conditions using the if stmt.
There are three form of if statements.
i)IF_THEN_ENDIF
II)IF_THEN-ELSE-ENDIF
III)IF-THEN-ELSIF-ENDIF
Syntax:
IF condition then
seuence of statements;
end if;
declare
begin
if ascii('Allen')=65 then
Display('it is true');
end if;
end;
syntax:
If condition then
sequence of statements;
else
sequence of statements;
end if;
declare
v_num number(2):=&eno;
begin
if mod(v_num,2)=0 then
display(v_num||'is an even number');
else
display(v_num||'is an odd number');
end if;
end;
syntax:
if <condition>then
<executable stmts>
elsif<condition>then
<executstmts>
elsif<condition>then
<exstmts>
else
<exstmts>
end if;
declare
avg_marks number:=&avgmark;
grade varchar2(10);
begin
if avg_marks between 80 and 100 then
grade:='A';
elsif
avg_marks between 60 and 79 then
grade:='B';
elsif
avg_marks between 40 and 59 then
grade:='C';
elsif
avg_marks between 0 and 39 then
grade:='D';
else
grade:='unknown';
end if;
Iterative controls:
iterative statements enable you to execute a group of statements many times.
Three Types;
simple loop
for loop
while loop
loop
<executable stmts>;
end loop;
declare
n number:=1;
begin
loop
display(n);
n:=n+1;
end loop;
end;
declare
n number:=1;
begin
loop
display(n);
exit when (n>=10);
n:=n+1;
end loop;
end;
while<conditio>
loop
<executable stmts>;
end loop;
declare
n number(3):=1;
begin
while(n<=10)
loop
display(n);
n:=n+1;
end loop;
end;
FOR loop_counter IN[Reverse]
lower_bound...higer_bound
loop
<excut stmts>;
end loop;
declare
begin
for i in 1..10
loop
display(i);
end loop;
end;
Cursors:
cursors are variables that we can define and declare section of pl/sql Block.
A cursor is apl/sql construct and allows you to
name of the work area
access their stored information
process the multiple Records
note: manipulate the more than one record that time used cursor stmts.
Types of cursors:
two types:
1)Static cursor
a)Explicit cursor
b)Implicit cursor
2)Dynamic cursor (Ref cursor)
Explicit cursor:
It is declared by the user and manipulated by theuser and valide only in pl/sql.
the life cycle of Explicit cursor goes through four stages
i) declare ii)open iii)fetch iv)close
i)Declare: The cursor stmt is declared explicitely in declarative section
syntax: Declare cursor<cursor name> is
select<list of columns>
from <table name>
[where condition];
Begin
----
----
End;
SQL> declare
2 cursor c1 is
3 select empno,ename,sal,deptno
4 from emp
5 where deptno=20;
6 begin
7 null;
8 end;
ii) open: Here the query execution done now we can fetch the data.
syntax: open<cursor name>
iii) fetch: The retrieves of data into the pl/sql variables or host variable is done through the fetch stmt.
The fetch stmt is a simple loop to fetch the all the cursor stmt.
syntax: fetch<cursor name> into <variables>;
iv) close: Explicitely closes the cursor
syntax: close <cursor name>
Cursor Attributes:
i) % found: it rerurns true if fetch is success
ii) % not found: it rerurns true if fetch is not success
iii) % is open: it returns true if cursor is open successfully
iv) % row count: it returns no.of rows fetched
Eg:
1 declare cursor c1 is
2 select empno,ename,sal
3 from emp
4 where deptno=30;
5 vempno emp.empno%type;
6 vename emp.ename%type;
7 vsal emp.sal%type;
8 begin
9 open c1;
10 loop
11 fetch c1 into vempno,vename,vsal;
12 exit when c1%notfound;
13 dbms_output.put_line(vempno||' '||vename||' '||vsal);
14 end loop;
15 close c1;
16* end;
1 declare
2 cursor c_sal is
3 select empno,ename,sal,job,deptno
4 from emp;
5 i emp%rowtype;
6 begin
7 open c_sal;
8 loop
9 fetch c_sal into i.empno,i.ename,i.sal,i.job,i.deptno;
10 exit when c_sal%notfound;
11 if i.job='clerk'then
12 i.sal:=i.sal+i.sal*0.25;
13 elsif i.job='manager'then
14 i.sal:=i.sal+i.sal*0.35;
15 else
16 i.sal:=i.sal+i.sal*0.15;
17 end if;
18 update emp set sal=i.sal
19 where empno=i.empno;
20 dbms_output.put_line(rpad(i.ename,8)||''||rpad(i.sal,6)||''||rpad(i.deptno,10));
Types of explicit cursor:
Explicit cursor with simple loop
Explicit cursor with for loop
Advantages:
no need to declare variables
no need to open the cursor explicitely
no need to fetch the data
no need to explicitely stop the loop
no need to close the cursor
syntax: for <variable> in <cursor name>
loop
stmts;
end loop;
1 declare
2 cursor
3 comm_cur is
4 select empno,ename,sal,comm,deptno
5 from emp;
6 begin
7 for k in comm_cur
8 loop
9 if k.comm is null then
10 k.comm:=300;
11 elsif k.comm=0 then
12 k.comm:=250;
13 else
14 k.comm:=k.comm+k.sal*0.15;
15 end if;
16 update emp set comm=k.comm
17 where empno=k.empno;
18 dbms_output.put_line(rpad(k.empno,8)||''||k.comm||
19 ''||k.deptno);
20 end loop;
21* end;
1 declare
2 cursor dnoc is
3 select deptno,min(sal) low_pay,
4 max(sal) high_pay,sum(sal) tot_pay,
5 count(empno)noe
6 from emp
7 group by deptno;
8 begin
9 for i in dnoc
10 loop
11 dbms_output.put_line(i.deptno||''||i.low_pay||''||i.high_pay||''||i.tot_pay||''||i.noe);
12 end loop;
13* end;
Implicit cursor:
Define by the oracle when everDml is performed in pl/sql program.
Implicit cursor:
An implicit cursor craete and erased by automatically.
---> During the process of an implicit cursor oracle automatically perform the operations like open , fetch and close.
---> Attributes are
i) Sql % found
ii) Sql % not found
iii) Sql % is open
iv) Sql % row count
1 begin
2 for i in(select empno, ename,sal,deptno from emp)
3 loop
4 if i.deptno=10 then
5 i.sal:=i.sal+500;
6 elsif
7 i.deptno=20 then
8 i.sal:=i.sal+600;
9 else
10 i.sal:=i.sal+700;
11 end if;
12 update emp set sal=i.sal
13 where empno=i.empno;
14 dbms_output.put_line(rpad(i.empno,8)||''||i.ename||''||i.sal||''||i.deptno);
15 end loop;
16* end;
Ref cursor:
Dynamic cursor is ref cursor it refers different work area in memory.
It is used to declare a cursor w/o select stmt.
A Ref cursor can be reused if it is declared in package.
A Ref cursor can support to return more than one row from subprogram.
Two types of dynamic cursors:
I) Weak Ref cursor: The cursor variable w/o return type called as Weak Ref cursor.
syntax: >type<typename> is Ref cursor;
II) Strong Ref cursor: The cursor variable with return type called as Strong Ref cursor.
syntax: >type<typename> is Ref cursor
[return <tablename>% rowtype]
note: Explicit cursor is a static cursor
Explicit cursor refers always one work area associated with the cursor.
features of cursors:
cursor with parameter
for update as { of column name
now wait }
---> where current of <cursor>
---> row id
---> sub queries
parametric cursor:
A cursor define with parameter is called parametric cursor.
the default mode of parameter is " in ".
syntax:
cursor <cursor name> (parameter name dtype,---)
is
select stmt;
where current of clause to refuse the current record and fetched from the explicit cursor.
for update clause explicitely focus the records stored in the private work area.
Composite data types (collections) :
These are two types which cannot hold any data physically
i) PL/SQL Record
ii) PL/SQL Table
i) PL/SQL Record:
A PL/SQL Record is allows you to treat sevaral variables as a unit.
PL/SQL Records are similar to strutures in C
syntax:
Type <Type name> is record (field name1, [field name2]---);
ii) PL/SQL Table:
It holds the elements of the similar datatypes.
it is similar like a array concept in C
syntax:
type <table name> is table of <data type> index by binary-integer;
here table name is collection name
data type---> what type of data can plce in a index
index by---> perform the no.of indexes which can hold the data temporarly
binary-integer---> it is a system datatype
Exception:
An exception in pl/sql block is raised during exception of block.
---> it terminates the main body of the action means a block always terminates when pl/sql raised an exception.
---> if the exception is handled then the pl/sql block terminates successfully.
Types of Exceptions:
Raised implicity: (predefined exceptions)
The exceptions which are declared by the oracle and raised implicity these exceptions are called Raised implicity.