|
Create PL/SQL to return department name
Hands-ON
introduction
In this
Hands-On, you write a PL/SQL Function to
return the department name (dname). You use
one input parameter to pass the department
number (deptno) and return its department
name.
Create a
PL/SQL function
Select
�Program Units.�
Click on the
�Create� icon.
Type the
function name �department_name.�
Then checkmark
�Function,� and click �OK.�
Define a
datatype for the Function return value.
Define an input parameter to pass the
department number. Declare a department name
variable.
In the body
section, use an implicit cursor to assign
the department name to the department name
variable where the department number is the
same as the input parameter. Make sure to
return the department name if the record was
found.
In the
exception section, write an exception to
return �no data found� message if there was
no match. Try always write the �Others�
exception in your PL/SQL.
(Procedure
Builder)
FUNCTION
dept_name
(p_deptno IN
dept.deptno%TYPE)
RETURN
VARCHAR2
IS
-- Define
dname variable
v_dname
dept.dname%TYPE;
BEGIN
-- Get
department name
SELECT dname
INTO v_dname
FROM dept
WHERE deptno =
p_deptno;
-- Return
department name.
RETURN v_dname
EXCEPTION
-- Error
messages�
WHEN
no_data_found THEN
RETRUN �NO
DATA FOUND��;
WHEN others
THEN
RETURN �Other
PROBLEM��;
END dept_name;
/
Compile and
save a PL/SQL function
Compile the
function. If you have any error; correct the
error and close the window.
Save the
function in the database server.
Test a PL/SQL
function
In the �PL/SQL
interpreter� section, use the �select�
statement and use the department number 10
to test the function.
PL/SQL> SELECT
dept_name(10) as �Department Name�
FROM dual;
To test the
exception, call the function again using the
department number that does not exist in the
department table.
PL/SQL> SELECT
dept_name(55) as �Department Name�
FROM dual;
Query the
department name function against the
employee table sorted by the employee name.
PL/SQL> SELECT
ename, dept_name(deptno) as �Department
Name�
FROM emp
ORDER
BY 1;
Notice that
you didn�t join the department table with
the employee table.
Questions:
Q: Write a
PL/SQL Function to return the department
name (dname). You use one input parameter to
pass the department number (DEPTNO) and
return its department name.
Q: In the
�PL/SQL interpreter� section, use the
�select� statement and use the department
number 10 to test the function.
Q: To test the
exception, call the function again using the
department number that does not exist in the
department table.
Q: Query the
department name function against the
employee table sorted by the employee name.
|
