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.

You will:

    1- Write a PL/SQL function to return a department name.

    2- Use department number as an input parameter.

    3- Run the stored procedure.

    4- Test the stored procedure.

    5- Save the function in the local library,

    6- Save the function in the database server.

 

	More Resources by Google:

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.

=

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.

Always write the “Others” exception.

=

Compile the function.

=

Close the window.

=

Save the function in the database server.

In the “PL/SQL interpreter” section, use the “select” statement and use the department number 10 to test the function.

To test the exception, call the function again using the department number that does not exist in the department table.

Query the department name function against the employee table sorted by the employee name.

Notice that you didn’t join the department table with the employee table.

=

Now, you should practice this over and over, until you become a master at it.

Good Luck.