The questions can come in any order, so make sure you are selecting right option for all questions.
1. Please go through the below Function and Exception spec: (a) Function: A Function must be created that will accept the dept_id as input. Based on this input, the function must return the department name. Function name : find_dept_name, Input Parameter : dept_id in int Output Parameter : A VARIABLE WITH varchar TYPE Design rules: 1)If department id(ie dept_id) passed as input, matches with the department_id in the department table,then it returns the department_name of the given dept_id. 2)If the deparment id passed as input, does not match with the department_id in the department table,then it throws 'no_data_found' exception and display it as 'No such department' Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line' ie: umpire_name := 'No such umpire'; Note: Do not change the function name Do not change the argument count and order Do not change the output text. Instructions: 1. Create the function successfully 2. Once the function is created, check the functionality of the function. 3. Submit only the CREATE FUNCTION query.
create or replace function find_dept_name(dept_id in int) return varchar is name varchar(255); begin select department_name into name from department where department_id = dept_id; return name; exception when no_data_found then name := 'No such department'; return name; end; /
2. Please go through the below Function and Exception spec: (a) Function: Create a function named 'find_staff_name_by_dept' thatl accepts the dept_id as input. Based on this input, the function must return the staff name. ie the function returns the names of staff belonging to this department. Hints: Function name : find_staff_name_by_dept, Input Parameter : dept_id in number Outputs : A VARIABLE WITH varchar TYPE Design rules: 1)If department id(ie dept_id) passed as input, matches with the department_id in the department table,then it returns the staff_name of the given dept_id. 2)If the deparment id(ie dept_id) passed as input, does not match with the department_id in the department table,then it throws 'no_data_found' exception and display it as 'No Such Department'. 3)If the deparment has more than one staff ,then it throws an exeption 'TOO_MANY_ROWS' and display it as 'Multiple Rows Returned' . Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line' ie: umpire_name := 'No such umpire'; Note: Do not change the procedure name Do not change the argument count and order Do not change the output text. Instructions: 1. Create the procedure successfully 2. Once the procedure is created, check the functionality of the procedure using different anonymous block call. 3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.
create or replace function find_staff_name_by_dept(dept_id in int)
return varchar is
name varchar(255);
begin
select staff_name into name
from staff
where department_id = dept_id;
return name;
exception
when no_data_found then
return 'No Such Department';
when too_many_rows then
return 'Multiple Rows Returned';
end;
/
3. Please go through the below Exception spec: (a) Procedure: Create a procedure named 'department_proc' that will insert the dept_id, dep_name and dep_bloc_num which is passed as input parameter. Hints: Procedure name : department_proc, Input Parameters: dept_id number, dep_name varchar and dep_bloc_num in number Design rules: 1)In the normal scenario, the new record with the given input values is inserted in the department table. 2)If the department table contains any duplicate values, then it throws 'DUP_VAL_ON_INDEX' exception and inserts a new record in the table with the value of department id set as 1000. Note: DO NOT CHANGE the given error code or error message description in your solution. Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line' ie: umpire_name := 'No such umpire';
create or replace procedure department_proc
(dept_id in number,dep_name in varchar, dep_bloc in number)
is
begin
insert into department
values(dept_id,dep_name,dep_bloc);
exception
when dup_val_on_index then
insert into department
values(1000,dep_name,dep_bloc);
end;
/
4. Please go through the below Function / Exception spec: Function: A Function must be created that will accept the dept_id as input. Based on this input, the function must retrieve the block id (block number) from the department table. Based on the block id, the fnction must return the following. If block_id is 1 then return 'NORTH' If block_id is 2 then return 'SOUTH' If block_id is 3 then return 'EAST' If block_id is 4 then return 'WEST' Function name : disp_block_name, Input Parameter : dept_id number Output Parameter : A VARIABLE WITH varchar TYPE Design rules: 1)If block_id obtained from the dept_id in input, matches with the any of the cases given above, then it returns the block_name of the given block_id. 2)If block_id obtained from the dept_id in input, matches with the any of the cases given above,then it throws 'CASE_NOT_FOUND' exception and returns the message 'No Such Block'. Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line' ie: umpire_name := 'No such umpire';
CREATE OR REPLACE FUNCTION disp_block_name(dept_id IN NUMBER)
RETURN VARCHAR2
IS
block_id NUMBER;
block_name VARCHAR2(20);
BEGIN
SELECT department_block_number
INTO block_id
FROM department
WHERE department_id = dept_id;
CASE block_id
WHEN 1 THEN block_name := 'NORTH';
WHEN 2 THEN block_name := 'SOUTH';
WHEN 3 THEN block_name := 'EAST';
WHEN 4 THEN block_name := 'WEST';
ELSE RAISE CASE_NOT_FOUND;
END CASE;
RETURN block_name;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
RETURN 'No Such Block';
END;
/
5. Create a procedure named 'department_procedure' that will accept dept_id as an input. This procedure will display the name of the department corresponding to the dept_id. Declare one user defined exception named ‘ex_invalid_id’ which will be raised when the input dept_id is less than 0. Hints: Procedure name : department_procedure Input Parameters: dept_id number Exception name: ex_invalid_id Design rules: 1)If the input dept_id is greater than zero and if it is existing in the department table, then display the corresponding dept_name. 2)If the input dept_id is greater than zero and if it is not existing in the department table, then it throws ‘NO_DATA_FOUND’ exception and display ‘No Such Department’. 3)If the input dept_id is less than zero, then raise an ex_invalid_id user defined exception and display ‘ID field must be greater than 0’. Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line' ie: umpire_name := 'No such umpire'; Note: Do not change the procedure name Do not change the argument count and order Do not change the output text. Instructions: 1. Create the procedure successfully 2. Once the procedure is created, check the functionality of the procedure using different anonymous block call. 3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.
create or replace procedure department_procedure(dept_id in number)
is
dept_name varchar(30);
ex_invalid_id exception;
begin
if dept_id < 0 then
raise ex_invalid_id;
else
select department_name
into dept_name
from department
where department_id = dept_id;
end if;
exception
when no_data_found then
dbms_output.put_line('No Such Department');
when ex_invalid_id then
dbms_output.put_line('ID field must be greater than 0');
end;
/