Procedures in Oracle

ORACLE PROCEDURE

A procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database.it can be executed as a unit to solve a specific problem or perform a set of related tasks. Procedures permit the caller to provide parameters that can be input only, output only, or input and output values.

Procedures and functions in oracle are identical except that functions always return a single value to the caller but procedures can return many values

The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

Eg :

create or replace procedure acc_bal

(

loancd in varchar2,

accno in number,

bal out number

)

is

ln_bal number;

tr_bal number;

begin

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into ln_bal from loan_tran_dtl where loan_no=loancd and account_no=accno;

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into tr_bal from tran_detail where ref_id=loancd and

account_no=accno;

bal:=ln_bal+tr_bal;

end;