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 10

“I happen to feel that the degree of a person's intelligence is directly reflected by the number of conflicting attitudes she can bring to bear on the same topic.” Lisa Alther, Kinflicks, 1975

 

Read first then play the video:

   PLS-VIDEO -Create PL/SQL to concatenate customer's name

  

Create PL/SQL to concatenate customer’s name

 

Hands-On Introduction

In this hands-on you write a PL/SQL Function to concatenate the customer's last name and first name to be separated by a comma. For example: Kazerooni, John.

 

CREATE FUNCTION

Since we need only on output therefore you should use FUNCTION. Create a "Function" to concatenate the customer's last name and first name to be separated by a comma.

 

Why FUNCTION?

Remember! A "Function" can have none or many input parameters, but it returns one and only one value.

Select “Program Units” and click “create.”

 

Name the function "Full_Name,” then select "Function" and click “OK.” Declare a datatype for the Function return value. Declare a first name and last name input parameters. Their datatypes should match with the datatype of the firstname and lastname in the customers table.

 

In the PL/SQL body, return the customers’ concatenated name. Write the exception. In the exception section, do nothing in the case of an error handling exception.

 

(Procedure Builder)

 

FUNCTION full_name

    (p_fname IN customers.first_name%TYPE,

    p_lname IN customers.last_name%TYPE)

    RETURN VARCHAR2

 

IS

    -- No variables

 

BEGIN

 

    -- Full name concatenation…

    RETURN p_lname || ‘, ‘ || p_fname;

 

EXCEPTION

 

    WHEN others THEN

        -- Do nothing…

        NULL;

 

END full_name;

/

 

Compile and Save a PL/SQL function

Compile the function. You should not have any error. If have any error; then correct the syntax and try again. You should get a message “Successfully compiled.”

 

Save the function in the database server. Choose the “Program” option and select the "PL/SQL interpreter."

 

The PL/SQL interpreter is a module that allows the developers to run and debug their stored procedures. It reads PL/SQL statements interactively.

 

Use the SELECT statement to test the function and pass two input parameters to the full_name function from the dummy table.

 

Test the PL/SQL function

Make the first parameter "John" and second "Kazerooni".

PL/SQL> SELECT full_name(‘John’,’Kazerooni’)

                    FROM dual;

 

Query the fist and last name of the customers table.

PL/SQL> SELECT first_name, last_name

                    FROM customers;

 

Then, query the customers’ concatenated full name using the full_name function.

PL/SQL> SELECT full_name(first_name, last_name) as “FULL NAME”

                    FROM customers;

 

“Nearly all men can stand adversity, but if you want to test a man's character, give him power.” Abraham Lincoln (1809 - 1865)

 

Questions:

Q: What is the PL/SQL function?

Q: What are the differences between the PL/SQL function and procedure?

Q: When do you create the PL/SQL function?

Q: write a PL/SQL Function to concatenate the customer's last name and first name to be separated by a comma. For example: Kazerooni, John. Name the function "Full_Name,” and declare a datatype for the Function return value. Declare a first name and last name input parameters. Their datatypes should match with the datatype of the firstname and lastname in the customers table.

In the PL/SQL body, return the customers’ concatenated name. Write the exception. In the exception section, do nothing in the case of an error handling exception.

Q: How do you execute the above created PL/SQL function in the SQLPLUS tool?

Q: What is the PL/SQL interpreter?

Q: How do you execute a PL/SQL procedure in the PL/SQL interpreter?