|
“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:
PLS012(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?
|
