column, employee_id, and a last_name
column. I can then see the last name of the
employee with ID 138, as follows:
SELECT last_name
FROM employees
WHERE employee_id = 138
Now I would like to run this same query
inside my PL/SQL block and display the
name. To do this, I need to “copy” the name
from the table into a local variable, which I
can do with the INTO clause:
DECLARE
l_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_name
FROM employees
WHERE employee_id = 138;
DBMS_OUTPUT.put_line (l_name);
END;
First I declare a local variable and in
doing so introduce another elegant feature
of PL/SQL: the ability to anchor the datatype of my variable back to a table’s column.
(Anchoring is covered in more detail later in
this series.)
I then execute a query against the
database, retrieving the last name for the
employee and depositing it directly into the
l_name variable.
Of course, you will want to do more than
run SELEC T statements in PL/SQL—you will
want to be able to insert into and update
tables and delete from them in PL/SQL as
well. Here are examples of each type of data
manipulation language (DML) statement:
Delete all employees in department 10 •
and show how many rows were deleted:
DECLARE
l_dept_id
employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = l_dept_id;
DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;
Directly inside the DELETE statement,
I reference the PL/SQL variable. When
the block is executed, the variable name
is replaced with the actual value, 10, and
the DELETE is run by the SQL engine.
SQL%RO WCOUNT is a special cursor
attribute that returns the number of rows
modified by the most recently executed DML
statement in my session.
Update all employees in department 10 •
with a 20 percent salary increase.
DECLARE
l_dept_id
employees.department_id%TYPE;
BEGIN
UPDATE employees
SET salary = salary 1. 2
WHERE department_id = l_dept_id;
DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;
Insert a new employee into the table. •
BEGIN
INSERT INTO employees (employee_id
, department_id
, salary)
VALUES (100
, 'Feuerstein'
, 10
, 200000);
DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;
In this block, I supply all the column
values as literals, rather than variables,
directly inside the SQL statement.
ResouRces;foR;PL/sQL;DeveLoPeRs
If you are just getting started with PL/SQL,
you should be aware of and take advantage
of a multitude of free online resources.
Here are some of the most popular and
useful ones:
Oracle Technology Network’s PL/SQL page, •
at oracle.com/technetwork/database/
features/plsql: an excellent starting point
for exploring PL/SQL resources on the
popular Oracle Technology Network Website
PL/SQL Obsession, at Toad World.com/SF: •
a collection of the author’s resources for
PL/SQL development, including training
materials, presentations, sample code,
standards, best-practice videos, and more
Next:;coNtRoLLiNg;BLock;executioN
In this article, you learned about how
PL/SQL fits into the wider world of Oracle
Database. You also learned how to define
blocks of code that will execute PL/SQL
statements and to name those blocks so
that your application code can be more
easily used and maintained. Finally, you
were introduced to the execution of SQL
statements inside PL/SQL.
The online version of this article, at
bit.ly/h1f1f7, includes additional content
that is not necessarily for the beginning
PL/SQL developer. For this article, the
more advanced content section is called
“Why Nest Blocks?” and it includes information and examples on the value of
nesting PL/SQL blocks.
Next in this article series, I will show you
how to control the flow of execution in your
block: conditional logic with IF and CASE;
iterative logic with FOR, WHILE, and simple
loops; and raising and handling exceptions.
Steven Feuerstein
(steven.feuerstein@
quest.com) is Quest
Software’s PL/SQL
evangelist. He has
published 10 books on
Oracle PL/SQL (O’Reilly Media) and is an Oracle
ACE director. More information is available at
stevenfeuerstein.com.
NEXT STEPS
READ
online-only article content
bit.ly/h1f1f7
PL/SQL column archive
oracle.com/technetwork/issue-archive/
index-087690.html
DISCUSS PL/SQL
forums.oracle.com/forums/forum
.jspa?forumID= 75
