Sample of StoredProcedure

sample_storedprocedure.txt

CallableStatement cstmt = conn.prepareCall(
	"{call StoredProcedureName ( [Parameter Definition] ) }"
)

Types of parameters allowed include:
1) Input(Default)
2) Output
3) Input Output

To Set the Parameters we use:
setXxx(Position, value)

Create or Replace Procedure AddNewEmployee
	( eid in NewEmployees.EMployee_ID%Type,
	  ename in NewEmployees.Employee_Name%ype,
          email in NewEmployees.Email%Type,
	  doj in NewEmployees.Hire_Date%Type,
	  sal in NewEmployees.Salary%Type
	)
is 
begin
insert into NewEmployees values (eid, ename, email, doj, sal);
commit;
end;

# StoredProcedure that produces an OUT Value to be used
Create or Replace Procedure GetToatalEMployeesByDepartment
	( depno in Employees.Department_IdType,
	  totalEmployees out number
	)
as
begin
select count(*) into totalEmployees from Employees
where Department_Id = depno;
end;


# IN OUT StoredPrecedure
Create or Replace Procedure GetCourseFeesByStudentId
	( cid in Courses.Course_Id%Type,
	  cfees out Courses.Fees%Type
	)
is begin
select fees into cfees from Courses
where CourseId = cid
end;

Create or Replace Procedure EnrollStudent
	( mo in Students.RollNumber%Type,
	  sname in Students.StudentName%Type,
	  cin in Students.CourseId%Type,
	  cfees in out Students.Fees%Type,
	  spercent in Students.Percentage%Type
	)
as discount number;
begin
discount := spercent * 5;
cfees := cfees - discount;
insert into Students values (mo, sname, cid, cfees, spercent);
commit;
end;