iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 11

“Misfortune shows those who are not really friends.” Aristotle (384 BC - 322 BC), Eudemian Ethics

 

Read first then play the video:

   PLS-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.