Oracle SQL Objective: Associates would need to develop stored procedures (or) fu

Question

Oracle SQL

Objective: Associates would need to develop stored procedures (or) functions for the below-mentioned problem statements.

NOTE: Students should create all the stored procedures and functions inside package mentioned in the problem statement. Also, all the procedure names should be appended with the employee id.

Create a package with name UMS_Package_<employee_id>.

Create a stored procedure Update_Student_Contact_Info _<employee_id> in the package which takes registration number, new email, and contact # as inputs and updates that student’s contact number, email id.

Create a stored procedure which accepts a character input and displays the name and the phone numbers of students which starts with the given character input.

Create a function which accepts the student registration id, returns the age of a student in number based on the DOB and the current date.

Create a function which accepts the student registration id and returns the numbers of years has passed since the student joined the college. Hint: Use the data of joining of the student.

Create a package with name UMS_Package_<employee_id>.

Create a stored procedure Update_Student_Contact_Info _<employee_id> in the package which takes registration number, new email, and contact # as inputs and updates that student’s contact number, email id.

Create a stored procedure which accepts a character input and displays the name and the phone numbers of students which starts with the given character input.

Create a function which accepts the student registration id, returns the age of a student in number based on the DOB and the current date.

Create a function which accepts the student registration id and returns the numbers of years has passed since the student joined the college. Hint: Use the data of joining of the student.

Explanation / Answer

here i created a table 'students' with columns ' regno, name, dob, email,phone,doj'. according to this table i created the procedures and functions.

create or replace package UMS_Package is
procedure Update_student_contact_info (reg int, newemail varchar2(50),phonenum int);
procedure studentdetails(ch char);
function studentage(reg int) return int;
function no_of_years(reg int) return int;
end UMS_Package_<employee_id>;

create or replace package body UMS_Package_<employee_id> is
procedure Update_student_contact_info_<employee_id>(reg int, newemail varchar2(50),phonenum int) as
v_reg int,
e_excep exception;
begin
select regno into v_reg from students where regno = reg;
if(v_reg==reg) then
update students
set email= newemail, phone = phonenum where regno = reg;
dbms_output.put_line(sql%ROWCOUNT ||' ' || 'updated');
else;
raise e_excep;
end if;
exception
when e_excep then
dbms_output.put_line("invalid reg num");
end Update_student_contact_info_<employee_id>;
procedure studentdetails(ch char) as
v_name varchar2(50),
v_phone int;
begin
select name,phone into v_name,v_phone from students where name like '%ch%';
dbms_output.put_line(v_name ||' ' || v_phone);
end studentdetails;
function studentage(reg int) return int is
v_age int;
begin
select (sysdate-dob) into v_age from students where regno=reg;
return v_age;
end studentage;
function no_of_years(reg int) return int is
v_years int;
begin
select (sysdate-doj) into v_years from students where regno=reg;
return v_years;
end no_of_years;
end UMS_Package_<employee_id>;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.