 |
|
Late Binding and
Runtime Binding in PL/SQL
Oracle Tips by Burleson |
Late Binding and Runtime Binding in PL/SQL
In the examples so far we have declared variables
as a separate object using one of PL/SQL’s standard datatypes.
Normally a PL/SQLvariable is closely tied to a database object and uses
the same datatype. For example, if the
variable will hold the book title from the book table, the variable definition must be of the exact same type and size as
the table column. But what happens when the DBA changes the book
table so that book_title is defined as a varchar2(60) instead
of a varchar2(40)? It’s important to program in such a way that when
the tables are changed, the PL/SQL code does not always need to be
changed. To get this independence, Oracle has provided a method to
declare a variable using the database object the data is based on. To
execute this use the %type declaration.
v_auth_name
author.author_last_name%type;
The above declaration creates a variable called
v_auth_name that has the same type definition as the
author_last_name column in the
author table. Since the PL/SQL variable is
declared when the PL/SQL is executed (a process called “late binding”
or “runtime binding”), the variable will always match the type
definition of the author_last_name column in the
database. In plain English, all changes to the type definition in the
database are automatically applied to the PL/SQL variable. You can
also create records using the %rowtype declaration.
r_auth author%rowtype;
The variable r_auth ( r_ is for record) is
declared as a record that will hold one row from the author table. If the DBA adds a column to the author table in the database, the
record r_auth will automatically include the new column. The
PL/SQL block continues to function correctly
after the change. Of course, if my code needs to use the new column,
then the code will need to be updated. The example below selects data
from the database and stores it in the variables defined.
SQL> declare
2 r_auth author%rowtype;
3 v_last_name author.author_last_name%type;
4 begin
5 select * into r_auth
6 from author
7 where author_key = 'A101';
8
9 select author_last_name into v_last_name
10 from author
11 where author_key = 'A101';
12
13 dbms_output.put_line ('Name: '||
14 r_auth. author_last_name);
15 dbms_output.put_line ('Name: '||v_last_name);
16 end;
17 /
Name: jones
Name: jones
Notice that if the DBA adds a column to the
author table, the block still executes without
problem because the row is defined using the %rowtype declaration. If the DBA changes the size of the author_last_name
column, the block picks up the change when the v_last_name
variable is declared because it used %type.
The internal variables in a record are accessed
using the “dot” notation. In the example a row was retrieved into the
record r_auth. To get the author last name from the record,
use the “record.column_name”.
dbms_output.put_line (‘Name: ‘||
r_auth. author_last_name);
Since the record is defined as author%rowtype,
it contains an entry for every column in the author table. If the column is null in the table, it will be null in the record.
Anytime variables interact with the data in the database, they should
be defined using %type or %rowtype
Follow the link for more information on
EXECUTE IMMEDIATE.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
