|
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;
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? |
