 |
|
Oracle Concepts by Burleson
Consulting |
Create a Simple Anonymous
PL/SQL Code Block
The following exercise is for you to measure
your level of understanding of basic PL/SQL. It is not graded. After
completing the exercise you should be able to create and execute
PL/SQL on your PC.
Your task is to create a script called
mailing_label.sql. This script will be a PL/SQL anonymous code
snippet that will perform the following functions:
Step 1 – Declare variables for store_address,
store_city, store_state, and store_zip
set
serveroutput on
DECLARE
v_store_address store.store_address%type;
Step 2 – Populate the variables with a SELECT INTO, where
store_name = ‘borders’. Here is a partial query:
BEGIN
select
store_address,
into
v_store_address,
from
store
where
store_name = 'borders';
Step 3 – Change your SQL to change the case of the address data
using the initcap and upper SQL BIFs. Here is a partial query.
BEGIN
select
initcap(store_address),
into
v_store_address,
from
store
where
store_name = 'borders';
Step 4 – Display the address using dbms_output.put_line as
follows:
Borders
Bookstore
2300 West Winters St
Durham, NC 27666
ANSWER
set
serveroutput on
DECLARE
v_store_address store.store_address%type;
v_store_city store.store_city%type;
v_store_state store.store_state%type;
v_store_zip store.store_zip%type;
BEGIN
select
initcap(store_address),
initcap(store_city),
upper(store_state),
store_zip
into
v_store_address,
v_store_city,
v_store_state,
v_store_zip
from
store
where
store_name = 'borders';
dbms_output.put_line('Borders Bookstore');
dbms_output.put_line(v_store_address);
dbms_output.put_line(v_store_city||', '||v_store_state||' '||v_store_zip);
dbms_output.put_line(' '||v_store_zip);
END;
/
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
