|
Basic PL/SQL
Introduction
PL/SQL is a
language that was provided by Oracle. Stored
procedure is a collection of PL/SQL. Stored
procedure is like a program module in
Oracle. It is available for developers to
code stored procedures that easily integrate
with database objects via the SQL statements
such as INSERT, UPDATE, DELETE, and SELECT.
This language offers variable DECLARATION,
LOOP, IF-THEN-ELSE-END IF, EXCEPTION an
advanced error handling, cursor, and more.
This e-book will provide an overview of
PL/SQL syntax, constructs, and usage. This
language is not just used by developers.
DBAs use this language to perform some of
their task such as automatic online or
offline backups. You will learn how to write
a PL/SQL procedure to perform automatic hot
or cold backup in the Backup and Recovery
e-book.
PL/SQL is a
straightforward language. PL/SQL is very
popular with its robust error handling and
its interface with the database. It can be
stored on the Oracle database (server side)
or in a library that was created in the PC
(client side). It interfaces with the Oracle
database natively. As you can centralize
your application modules by storing them in
the server side on the database, or
decentralize them by storing them on each
client side. Remember that when you use
decentralize structure, if you change a
module then you need to apply your changes
to all your clients. But on the other hand,
you will increase the Oracle performance.
Now, let�s
start and do some practical hands-on
exercises.
Go to
�MS-DOS.�
Change
directory to your directory and login to
�sqlplus� as "oracle/learning"
MS-DOS> cd ..
MS-DOS>
sqlplus oracle/learning
PL/SQL Body
Write a PL/SQL
block to use only the "body" section with
PL/SQL statement. A BEGIN and END are
keywords. You write the PL/SQL statement to
be executed in the body. In this example,
you tell PL/SQL do nothing for you (null);
SQL> BEGIN
null;
END;
/
Use the slash
(/) to compile and run the block.
PL/SQL
Declaration
The
DECLARATION statement will be used to define
program�s variables, cursor, type, etc.
Add the
"declaration" section with no variables.
Then compile and run the block. In this
declaration, you have no variable or cursor
defined.
SQL> declare
-- no variable
begin
null;
end;
/
SET
SERVEROUTPUT
When you use
the DBMS_OUTPUT.PUT_LINE procedure, the
procedure will write the passing string into
the Oracle buffer. In order to print the
content of the Oracle buffer, you should use
the SET SERVEROUTPUT command to display the
content of the Oracle buffer into your
screen. You can also increase the size of
the buffer.
Use the �Set
serveroutput on� to display the buffer used
by dbms_output.
SQL> SET SERVEROUTPUT ON
DBMS_OUTPUPT.PUT_LINE procedure
Write a PL/SQL
block, to output the "Hello OracleLearning"
message.
SQL> BEGIN
dbms_output.put_line('Hello
OracleLearning');
END;
/
Now, let's see
how we define a variable in the DECLARATION
section and write PL/SQL statements in the
BODY section.
First, we take
a look at the department columns. So,
describe the department table.
SQL> DESC dept
Define
Variables in PL/SQL
Write a PL/SQL
block, to declare a department name variable
with the same datatype of the department
name. Then assign "HR" to the variable and
output the variable.
SQL> DECLARE
v_dname
VARCHAR2(14);
BEGIN
v_dname :=
'HR';
dbms_output.put_line(v_dname);
END;
/
Save the
PL/SQL block
The following
is one of the ways that you can save your
stored procedure (PL/SQL program).
Save the
PL/SQL block as �test_myblock.�
SQL> SAVE c:_myblock.sql
Then go to
notepad and open the file from the
directory.
%TYPE keyword
If you are
using a variable in the stored procedure
that deals with a table column, it should
have the same datatype and length as the
column itself. Instead of look it up, you
use the %TYPE keyword. The advantage of
doing that is: if a DBA changes the column
datatype or length, you don�t have to modify
your stored procedure.
The
tablename.column%TYPE
syntax means use the same datatype of such
column in such table.
Use the %type
keyword, to declare a variable as the same
datatype and size of the department name
column of the dept table. Then save the
file.
(In notepad
change)
DECLARE
v_dname
dept.dname%TYPE;
BEGIN
v_dname :=
'HR';
dbms_output.put_line(v_dname);
END;
/
Get a PL/SQL
block
By now, you
have learned how to save your stored
procedure. Now, let�s see how to can get it
back.
Go to
�SQLPLUS.� Get the file. And run it.
SQL> GET
c:_myblock.sql
SQL> /
- OR -
Run a PL/SQL
block
You can also
use the START statement to run or execute a
SQL script. Use the START statement to
execute your SQL script.
SQL> START c:_myblock.sql
The same
output!
%ROWTYPE
keyword
The %ROWTYPE
keyword creates a composite datatype in
which all the columns of a row are pieced
together into a record. For example: if I
have in the test table three columns (c1,
c2, and c3) and you need to put them in a
variable, you should use %ROWTYPE (v_abc
test%ROWTYPE). Now, the v_abc variable
contains three columns (c1, c2, and c3). The
following shows how this work.
The test.c1
corresponds with v_abc.c1.
The test.c2
corresponds with v_abc.c2.
The test.c3
corresponds with v_abc.c3.
Go back to
�notepad,� use the %rowtype keyword to
declare a variable row type.
Assign �HR� to
the �department name� item of the defined
variable row. In this example: we know the
dept table contains three columns (deptno,
dname, and loc), therefore the v_drec
variable contains three columns as well
(deptno, dname, and loc). These variable�s
datatype and length are the same as the dept
table�s columns.
The following
PL/SQL block will output the v_drec.dname
value.
(in notepad
change)
DECLARE
v_drec
dept%rowtype;
BEGIN
v_drec.dname
:= 'HR';
dbms_output.put_line(v_drec.dname);
END;
/
Then save the
file. Go to �SQLPLUS.� Get the file. And run
it.
SQL> GET
c:_myblock.sql
SQL> /
The same
output!
Implicit
Cursor
If you define
your cursor in the PL/SQL body, it will be
called an implicit cursor. In the following
example, you see how %ROWTYPE works.
Go back to
�Notepad.� Use the implicit cursor to query
the department table information where
deptno = 30. Check if no record was found
then print �Record was not found.� else
print the department name only. The
SQL%NOTFOUND reserved word returns the FALSE
value if there are records to read from
cursor and returns the TRUE value if there
are not record exist to read from cursor.
(in notepad
change)
>> declare
v_drec dept% rowtype;
begin
select
deptno, dname, loc into
v_drec.deptno,v_drec.dname,
v_drec.loc
from
dept
where
deptno = 30;
if
sql%notfound then
dbms_output.put_line('Record
was not found.');
else
dbsm_output.put_line(v_drec.dname);
end if;
end;
/
Then save the
file. Go to �SQLPLUS.� Get the file. And run
it.
SQL> GET
c:_myblock.sql
SQL> /
Modify a
PL/SQL block
Back to
�Notepad� Modify the PL/SQL block to move
the entire record into the variable row.
(in notepad
change)
>> declare
v_drec dept% rowtype;
begin
select
*
into v_drec
from
dept
where
deptno = 30;
if
sql%notfound then
dbms_output.put_line('Record
was not found.');
else
dbsm_output.put_line(v_drec.dname);
end if;
end;
/
Then save the
file. Go to �SQLPLUS.� Get the file. And run
it.
SQL> get
c:_myblock.sql
SQL> /
The same
result!
Questions:
Q: What is
PL/SQL?
Q: Where can
you store a PL/SQL procedure?
Q: What is the
PL/SQL body section?
Q: What is the
PL/SQL declaration section?
Q: What does
the SET SERVEROUTPUT command?
Q: What does
the DBMS_OUTPUT.PUT_LINE procedure?
Q: How do you
define a variable or variables in the PL/SQL
declaration section?
Q: How do you
save a PL/SQL block in the client
environment?
Q: How do you
use the %TYPE keyword?
Q: How do you
open a saved PL/SQL block?
Q: How do you
run a saved PL/SQL block?
Q: What does
the %ROWTYPE keyword in the PL/SQL language?
Q: What is an
implicit cursor in the PL/SQL language?
Q: An implicit
cursor must have _________ on its SELECT SQL
statement?
Q: What does
the SQL%NOTFOUND reserved PL/SQL word?
Q: What does
the �SET SERVEROUTPUT ON?�
Q: Write a
PL/SQL block, to output the "Hello
OracleLearning" message.
Q: Use the
%TYPE keyword, to declare a variable as the
same datatype and size of the department
name column of the dept table.
Q: Use the
implicit cursor to query the department
table information where deptno is 30. Check,
if no record was found then print �Record
was not found.� Else print the department
name only. |
