HOW PL/SQL GETS EXECUTED
Every time an anonymous PL/SQL
block is executed, the code is sent to
the PL/SQL engine on the server
where it is compiled. The named PL/SQL
block is compiled only at the
time of its creation, or if it has been
changed. The compilation
process includes syntax checking, binding,
and p-code generation.
Syntax checking involves
checking PL/SQL code for syntax or compila-tion
errors. Syntax error occurs
when a statement does not exactly corre-spond
to the syntax of the
programming language. Errors such as
misspelled keyword, missing
semicolon at the end of the statement, or
undeclared variable are
examples of syntax errors.
Once the programmer corrects
syntax errors, the compiler can assign a
storage address to program
variables that are used to hold data for Oracle.
This process is called binding. It allows Oracle to
reference storage ad-dresses
when the program is run. At the
same time, the compiler checks
references to the stored
objects such as table name or column name in
the SELECT statement, or a call
to a named PL/SQL block.
Next, p-code is generated for
the PL/SQL block. P-code
is a list of
instruc-tions
to the PL/SQL engine. For named
blocks, p-code is stored in the
database, and it is used the
next time the program is executed. Once the
process of compilation has
completed successfully, the status for a named
PL/SQL block is set to VALID,
and also stored in the database. If the com-pilation
process was not successful, the
status of a named PL/SQL block is
set to INVALID.
It is important to remember that successful
compilation of the named
PL/SQL block does not guarantee successful
execution of this block in the
future. If, at the time of the execution,
any one of the stored objects ref-erenced
by the block is not present in the database
or not accessible to
the block, the execution will fail. The
status of the named PL/SQL block
will be changed
to INVALID.
DBMS_OUTPUT.PUT_LINE
You already have
seen some examples of how the DBMS_OUTPUT.PUT_
LINE statement can
be used. This statement is used to display informa-tion
on the screen. It
is very helpful when you want to see how your
PL/SQL block is
executed. For example, you might want to see how vari-ables
change their values
throughout the program or debug it.
The
DBMS_OUTPUT.PUT_LINE is a call to the procedure PUT_LINE. This
procedure is a part
of the DBMS_OUTPUT package that is owned by the
Oracle user SYS.
DBMS_OUTPUT.PUT_LINE
writes information to the buffer for storage.
Once a program has
been completed, the information from the buffer is
displayed on the
screen. The size of the buffer can be set between 2000
and 1,000,000
bytes. Before you can see the output printed on the screen,
one of the
following statements must be entered before the PL/SQL block.
SET SERVEROUTPUT
ON;
or
SET SERVEROUTPUT
ON SIZE 5000;
The first SET
statement enables the DBMS_OUTPUT.PUT_LINE statement,
and the default
value for the buffer size is used. The second SET state-ment
not only enables
the DBMS_OUTPUT.PUT_LINE statement. The
buffer size is
changed from its default value to 5000 bytes.
Similarly, if you
do not want information to be displayed on the screen
by the
DBMS_OUTPUT.PUT_LINE statement, following SET command
can be issued prior
to the PL/SQL block.
SET SERVEROUTPUT
OFF;
CHARACTER TYPES
The PL/SQL engine accepts four
types of characters: letters, digits, sym-bols
(*, +, -, = ..etc), and white
space. When elements from one of these
character types are joined
together, they will create a lexical unit (these
lexical units can be a
combination of character types). The lexical units
are the words of the PL/SQL
language. First you need to learn the PL/SQL
vocabulary, and then you will
move on to the syntax or grammar. Soon
you can start talking in
PL/SQL.
LEXICAL UNITS
A language like English
contains different parts of speech. Each part of
speech, such as a verb or noun,
behaves in a different way and must be
used accordingly. Likewise, a
programming language has lexical units that
are the building blocks of the
language. PL/SQL lexical units fall within
one of the following five
groups:
1. Identifiers. Identifiers must begin with
a letter and may be up to
30 characters long. See a
PL/SQL manual for a more detailed list
of restrictions; generally, if
you stay with characters, numbers,
and and avoid reserved
words, you will not run into problems.
2. Reserved Words. Reserved words are words
that PL/SQL saves for
its own use (e.g., BEGIN, END,
SELECT).
3. Delimiters. These are characters that
have special meaning to
PL/SQL, such as arithmetic
operators and quotation marks.
4. Literals. A literal is any value
(character, numeric, or Boolean
[true/false]) that is not an
identifier. 123, Declaration of Inde-pendence
and FALSE are examples of
literals.
5. Comments. These can be either
single-line comments (i.e., --) or
multi-line
comments (i.e., /* */).
VARCHAR2(maximum_length)
Stores variable-length
character data.
Takes a required parameter
that specifies a maximum length up to
32767 bytes.
Does not use a constant or
variable to specify the maximum length;
an integer literal must be
used.
Although the maximum length
of a VARCHAR2(n) variable is
32,767 bytes, the maximum width
of a VARCHAR2 database col-umn
is 2000 bytes; therefore,
values longer than 2000 bytes can-not
be inserted into a VARCHAR2
column.
CHAR[(maximum_length)]
Stores fixed-length
(blank-padded if necessary) character data.
Takes an optional parameter
that specifies a maximum length up
to 32,767 bytes.
Does not use a constant or
variable to specify the maximum
length; an integer literal must
be used. If maximum length is not
specified, it defaults to 1.
Although the maximum length
of a CHAR(n) variable is 32,767
bytes, the maximum width of a
CHAR database column is 255
bytes; therefore, you cannot
insert values longer than 255 bytes
into a CHAR column.
NUMBER[(precision, scale)]
Stores fixed or
floating-point numbers of virtually any size.
Precision is the total number
of digits.
Scale determines where
rounding occurs.
It is possible to specify
precision and omit scale, in which case
scale is 0 and only integers
are allowed.
Constants or variables cannot
be used to specify precision and
scale; integer literals must be
used.
Maximum precision of a NUMBER
value is 38 decimal digits.
Scale can range from .84 to 127.
For instance, a scale of 2
rounds to the nearest hundredth (3.456
becomes 3.46).
Scale can be negative, which
causes rounding to the left of the
decimal point. For example, a
scale of .3 rounds to the nearest
thousand (3456 becomes 3000). A
scale of zero rounds to the
nearest whole number. If you do
not specify the scale, it defaults
to zero.
BINARY_INTEGER
Stores signed integer
variables.
Compares to the NUMBER
datatype. BINARY_INTEGER variables
are stored in the binary
format, which takes less space.
Calculations are faster.
Can store any integer value
in the range .2,147,483,747 through
2,147,483,747.
DATE
Stores fixed-length date
values.
Valid dates for DATE
variables include January 1, 4712 BC to De-cember
31, 4712 AD.
When stored in a database
column, date values include the time
of day in seconds since midnight.
The date portion defaults to the
first day of the current month;
the time portion defaults to mid-night.
Dates are actually stored in
binary format and will be displayed
according to the default
format.
BOOLEAN
Stores the values TRUE and
FALSE and the non-value NULL. Re-call
that NULL stands for a missing,
unknown, or inapplicable
value.
Only the values TRUE and
FALSE and the non-value NULL can be
assigned to a BOOLEAN variable.
The values TRUE and FALSE
cannot be inserted into a database
column.
LONG
Stores variable-length
character strings.
The LONG datatype is like the
VARCHAR2 datatype, except that
the maximum length of a LONG
value is 32,760 bytes.
Any LONG value can be
inserted into a LONG database column
because the maximum width of a
LONG column is 2,147,483,647
bytes. However, you cannot
select a value longer than 32,760
bytes from a LONG column into a
LONG variable.
LONG columns can store text,
arrays of characters, or even short
documents. You can reference
LONG columns in UPDATE, IN-SERT,
and (most) SELECT statements,
but not in expressions, SQL
function calls, or certain SQL
clauses such as WHERE, GROUP BY,
and CONNECT BY.
ROWID
Internally, every Oracle
database table has a ROWID pseudocol-umn,
which stores binary values called
rowids.
Rowids uniquely identify rows
and provide the fastest way to ac-cess
particular rows.
Use the ROWID datatype to
store rowids in a readable format.
When you select or fetch a
rowid into a ROWID variable, you can
use the function ROWIDTOCHAR,
which converts the binary
value to an 18-byte character
string and returns it in the format.
Row ID in Oracle 8i is as
follows: OOOOOOFFFBBBBBBRRR
OOOOOO: This number signifies
the database segment. FFF: This
number indicates the
tablespace-relative datafile number of the
datafile that contains the row.
BBBBBB: This number is the data
block that contains the row.
RRR: This number is the row in the
block (keep in mind that this
may change in future versions of
Oracle).
Operators(Delimiters)the Separators
in an Expression
Arithmetic ( ** , * , / , + ,
-)
Comparison( =, <> , != ,
< , > , <= , >= , LIKE , IN , BETWEEN , IS
NULL )
Logical ( AND, OR, NOT )
String ( ||, LIKE )
Expressions
Operator Precedence
** , NOT
+, - ( arithmetic identity and
negation ) *, / + , - , || =, <>, != , <= ,
>= , < , > , LIKE,
BETWEEN, IN, IS NULL
ANDlogical conjunction
ORlogical
inclusion
VARIABLE INITIALIZATION
WITH SELECT INTO
In PL/SQL, there are two main
methods of giving value to variables in a
PL/SQL block. The first one,
which you learned in Chapter 2, PL/SQL
Concepts, is initialization
with the := syntax. In this lab we will learn
how to initialize a variable
with a select statement by making use of
SELECT INTO syntax.
A variable that has been
declared in the declaration section of the PL/SQL
block can later be given a
value with a SELECT statement. The correct
syntax is as follows:
SELECT item_name
INTO variable_name
FROM table_name;
It is important to note that
any single row function can be performed on
the item
to give the variable a calculated value.
Make Use of COMMIT,
ROLLBACK, and SAVEPOINT
in a PL/SQL Block
Transactions are a means to
break programming code into manageable
units. Grouping transactions
into smaller elements is a standard practice
that ensures an application
will save only correct data. Initially, any ap-plication
will have to connect to the
database in order to access the data.
It is important to point out
that when a user is issuing DML statements in
an application, the changes are
not visible to other users until a COMMIT
or ROLLBACK has been issued.
Oracle guarantees a read-consistent view
of the data. Until that point,
all data that has been inserted or updated
will be held in memory and only
be available to the current user. The
rows that have been changed
will be locked by the current user and will
not be available for updating
to other users until the locks have been re-leased.
A COMMIT or a ROLLBACK
statement will release these locks.
Transactions can be more
readily controlled by marking points of the
transaction with the SAVEPOINT
command.
For more details on transaction control
(such as row locking issues), see
the companion volume, Oracle DBA Interactive Workbook, by
Douglas Scherer and Melanie Caffrey
(Prentice Hall, 2000).
The definition of a transaction is simply a logical unit of
work. The set of
SQL statements comprising a
transaction either succeed or fail as a unit.
The
PL/SQL block for one transaction ends with COMMIT or ROLLBACK.
COMMIT Makes events within a
transaction permanent.
ROLLBACK Erases events within a
transaction.
Additionally, one can use a
SAVEPOINT to control transactions. Transac-tions
are defined in the PL/SQL block
from one SAVEPOINT to another.
The use of the SAVEPOINT
command allows you to break your SQL state-ments
into units so that in a given
PL/SQL block, some units can be com-mitted
(saved to the database), some
can be rolled
back (undone),
and so
forth.
Note that there is a distinction between
transaction and a PL/SQL block.
The start and end of a PL/SQL block do not
necessarily mean the start
and end of a transaction.
A Single PL/SQL Block Can Contain
Multiple Transactions
For Example:
Declare
v_Counter NUMBER;
BEGIN
v_counter := 0;
FOR i IN 1..100
LOOP
v_counter := v_counter + 1;
IF v_counter = 10
THEN
COMMIT;
v_counter := 0;
END IF;
END LOOP;
END;
In this example, as soon as
value of v_counter becomes equal to
10, the work is committed. So,
there will be a total of 10 transac-tions
contained
in this one PL/SQL block.