|
“Misfortune shows
those who are not really friends.” Aristotle (384 BC - 322
BC), Eudemian Ethics |
Read
first then play the video:
PLS013(VIDEO)-Create
PL/SQL to return department name
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.
|
“Not a shred of
evidence exists in favor of the idea that life is serious.”
Brendan Gill |
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.
|
