This chapter is all about how to keep track of variables in a PL/SQL program. Before this chapter ends, you’ll learn about PL/SQL data types, variables, scope, types, and parameters. Let’s get started.
Given that PL/SQL is the Procedural Language extension for SQL, you would think that it supports and uses the same data types as SQL does for the database, right? Right! It turns out that is almost the case, but not quite. PL/SQL can handle any database data type and also some data types of its own.
Do you remember the “Data Definition Language (DDL)” section in Chapter 1? In it, I talked about the three basic data types that you will use all the time: character (VARCHAR2), numeric (NUMBER), and time (DATE). Here’s how those types work in PL/SQL:
- VARCHAR2: Just as in the database, most of the time you’ll work with character strings in PL/SQL using the data type VARCHAR2. However, unlike the database VARCHAR2 type, a PL/SQL VARCHAR2 can hold as many as 32,767 characters, whereas the database type prior to Oracle12c only holds up to 4,000.
- NUMBER: Just as in the database, most of the time you’ll work with numbers in PL/SQL using the data type NUMBER. And, just like the database, PL/SQL has additional numeric data types available. For example, you can use the type PLS_INTEGER, which has an integer range from –2147483648 to 2147483647. PLS_INTEGER also uses the computer’s hardware to do its calculations instead of library routines, so it’s faster. However, until you’re comfortable writing stored procedures with PL/SQL, I don’t think you need to bother with them. You can always take a look at them in Oracle’s PL/SQL User’s Guide and Reference when you’re ready.
- DATE: Just as in the database, most of the time you’ll work with dates and times in PL/SQL using data type DATE. Like NUMBER, PL/SQL has additional time-related data types. Check them out in Oracle’s reference once you’re up and coding confidently.
PL/SQL also has a BOOLEAN data type that allows you to store the logical values FALSE or TRUE in a variable. SQL has no equivalent. So you can pass BOOLEAN values between methods and use them as variables in your PL/SQL program units, but you can’t store them in the database.
Of course, there are many more Oracle SQL and PL/SQL data types, but the ones I just covered are the ones you’ll use most often.
Variables
Variables are named temporary storage locations that support a particular data type in your PL/SQL program. You must declare them in the declaration section of a PL/SQL block.
By “named,” I mean that you give each of your variables a name. They are temporary because the values you assign to variables typically exist only in memory (or are accessible in memory) while the PL/SQL block in which they are declared is executing. They are storage locations in memory. And they are declared to store a particular data type so PL/SQL knows how to create, store, access, and destroy them.
Let’s start by looking at how to name your variables.
Like a SQL or database data type, PL/SQL variables must follow the identifier naming rules:
- A variable name must be less than 31 characters in length.
- A variable name must start with an uppercase or lowercase ASCII letter: A–Z or a–z. PL/SQL is not case-sensitive.
- A variable name may be composed of 1 letter, followed by up to 29 letters, numbers, or the underscore (_) character. You can also use the number (#) and dollar sign ($) characters.
As a matter of fact, all PL/SQL identifiers, like stored function and procedure names, must follow these naming rules!
I like to use the following conventions when naming variables:
- Use the two-character prefix for each data type, as defined in Table 3-1.
- Make its purpose obvious. If the variable will hold a value from the database, use the same name as in the database, but with the appropriate prefix.
Table 3-1. My PL/SQL Variable Naming Prefixes
Prefix | Data Type |
|---|---|
c_ | CURSOR |
d_ | DATE |
n_ | NUMBER |
r_ | ROW |
t_ | TABLE |
v_ | VARCHAR2 |
For example, if I were to declare variables to hold data from the columns in the AUTHORS table, I would use the following identifier names:
- n_id
- v_name
- d_birth_date
- v_gender
The advantage of using these prefixes is that you’ll always know the variable’s data type and its scope. (I’ll cover scope shortly.) In addition, since you made the name obvious, you’ll also know where it comes from or where it’s going.
By “obvious,” I mean that you shouldn’t use a synonym for an already existing identifier. For example, don’t create variables named d_born, d_date_of_birth, and d_bday for a value from the birth_date column in the database. Why? All those name variations will just make what you’re referring to unclear, and all professional PL/SQL programmers want to make it absolutely clear what is going on in their code!
Now that you know how to name your variables, let’s look at how to declare them.
To declare a variable, type the variable name (identifier) followed by the data type definition, as you would use in SQL, terminated by a semicolon (;). The data type definition is the name of the data type, possibly followed by length constraints in a set of parentheses. For example, to declare variables to hold the data from columns in the AUTHORS table, I could code the following in a PL/SQL block’s declaration section:
declare
n_id number;
v_name varchar2(100);
d_birth_date date;
v_gender varchar2(30);
begin
...
end;
The DDL syntax used to declare the preceding variables is as follows:
<variable_name> <data_type>;
where <variable_name> is the name of the variable (or identifier), and <data_type> is one of the PL/SQL data types.
You’ll notice that if you get rid of the data type prefixes, variable declarations look just like the SQL in the table definition for AUTHORS in Chapter 1, and that’s the whole point. They should! It wouldn’t make much sense to declare varchar2 variables that are smaller than the ones in the database because that could cause an error. And it doesn’t make much sense to name them differently than what they’re named in the database because that would just make things confusing. That’s why I use those prefixes shown in Table 3-1.
Most of the time, you’ll end up declaring variables that temporarily hold values from or that are going into the database, and PL/SQL has a keyword for simplifying just that situation: %TYPE.
An anchor refers to the use of the keyword %TYPE to “anchor” a PL/SQL data type definition in a PL/SQL variable to the corresponding SQL data type definition of a column in a table. Steven Feuerstein coined the term anchor, and I’ve always liked it, so I use it whenever I talk about the keyword %TYPE.
Here’s an example of the PL/SQL variables for the table AUTHORS declared using column anchors (the %TYPE keyword):
n_id AUTHORS.id%TYPE;
v_name AUTHORS.name%TYPE;
d_birth_date AUTHORS.birth_date%TYPE;
v_gender AUTHORS.gender%TYPE;
The syntax used to declare the preceding variables is as follows:
<variable_name> <table_name>.<column_name>%TYPE;
where <variable_name> is the name of the variable (or identifier), <table_name> is the name of the table used to anchor the data type, and <column_name> is the name of the column used to anchor the data type.
Programming in PL/SQL just keeps getting better, doesn’t it? By using anchors, you now know that the variables use the same data types and sizes as the table that will be the source of, or permanent storage for, each variable’s value. Even when I use anchors, I still use data type prefixes in order to remind me of the variables’ data types.
Now that you’ve seen how to declare variables, let’s talk about how to assign values to them.
To assign a literal value to a variable in PL/SQL, you use the assignment operator, which is a colon (:) followed by an equal sign (=): :=. For example, I can make the following assignments:
declare
...
begin
n_id := 400;
v_name := 'STEVEN FEUERSTEIN';
d_birth_date := to_date('19800101', 'YYYYMMDD'),
v_gender := 'M';
end;
What do I mean by “a literal value”? OK, let’s back up a second.
- A numeric literal is just a number without any formatting, such as 400.
- A character literal is just a string of characters enclosed in a pair of tick (') characters (or single quotes), such as 'STEVEN FEUERSTEIN'.
There is no such thing as a date literal. You can assign a character literal and hope the format you decided to use matches the current NLS_DATE_FORMAT (automatic type conversion—dangerous, very dangerous), or you can use the built-in function,
to_date(aiv_date in varchar2, aiv_date_format in varchar2)
A second way to assign a value to a variable is to use an INTO clause in a SQL SELECT statement. Here’s an example:
select id,
name,
birth_date,
gender
into n_id
v_name,
d_birth_date,
v_gender
from AUTHORS
where AUTHORS.id = (
select AUTHOR_PUBLICATIONS.author_id
from AUTHRO_PUBLICATIONS
where title = 'ORACLE PL/SQL PROGRAMMING'),
In this example, the PL/SQL keyword INTO moves the values from the SELECT statement’s column list into the corresponding PL/SQL variables. I’ll talk a lot more about this in Chapter 4.
By default, variables are uninitialized and hence are NULL. You can initialize them to a value when they are declared by simply assigning them a value in the declaration section. For example, you could initialize the AUTHORS variables as follows:
declare
n_id AUTHORS.id%TYPE := 400;
v_name AUTHORS.name%TYPE := 'STEVEN FEUERSTEIN';
d_birth_date AUTHORS.birth_date%TYPE := to_date('19800101', 'YYYYMMDD'),
v_gender AUTHORS.gender%TYPE := NULL;
begin
...
end;
The syntax used to declare the preceding variables is as follows:
<variable_name> <table_name>.<column_name>%TYPE := <value>;
where <variable_name> is the name of the variable (or identifier), <table_name> is the name of the table used to anchor the data type, <column_name> is the name of the column used to anchor the data type, and <value> is the initial value for the variable. But what exactly is NULL?
The term NULL value is a bit of a misnomer. The keyword NULL means I don’t know! The keyword NULL means you don’t know! The keyword NULL means it is not possible to know what the value is, so how can there be something such as a NULL value? Let’s further define NULL:
- NULL is not equal to anything, not even NULL.
- NULL is not less than or greater than anything else, not even NULL.
- NULL means nothing knows, not even NULL.
You can test for NULL values in a SQL statement or PL/SQL code by using one of two phrases:
- is NULL
- is not NULL
Although the PL/SQL compiler may let you, you cannot use a logical operator with NULL, like
= NULL
or
<> NULL
and get the logical results you’re seeking.
You must use is NULL and is not NULL. Remember this and you will save yourself many hours of troubleshooting erratic behavior.
It’s Your Turn to Declare Variables
Now that you’re familiar with the declaration of variables, let’s put you to work. Create an anonymous PL/SQL procedure where you declare variables using anchors, and with default values, for the columns in the WORKERS table. Follow these steps.
- Code your anonymous procedure.
- Save it with the file name workers_variables.sql.
- Execute your script in SQL*Plus: SQL> @workers_variables.sql.
Listing 3-1 is my solution for this problem.
Listing 3-1. An Anonymous PL/SQL Procedure with Variable Declarations, workers_variables.sql
01 declare
02 n_id WORKERS.id%TYPE := 1;
03 n_worker_type_id WORKERS.worker_type_id%TYPE := 3;
04 v_external_id WORKERS.external_id%TYPE := '6305551212';
05 v_first_name WORKERS.first_name%TYPE := 'JANE';
06 v_middle_name WORKERS.middle_name%TYPE := 'E';
07 v_last_name WORKERS.last_name%TYPE := 'DOE';
08 v_name WORKERS.name%TYPE := 'JANEDOEE';
09 d_birth_date WORKERS.birth_date%TYPE :=
10 to_date('19800101', 'YYYYMMDD'),
11 n_gender_type_id WORKERS.gender_type_id%TYPE := 1;
12 begin
13 null;
14 end;
15 /
Let’s break Listing 3-1 down line by line:
- Line 1 uses the keyword DECLARE to start an anonymous PL/SQL procedure.
- Lines 2 through 11 declare variables to hold the contents of the columns from the WORKERS table.
- Line 12 starts the executable section of the procedure with the keyword BEGIN.
- On line 13, I coded a no operation (NULL) so the block will compile.
- Line 14 ends the procedure with the keyword END.
- On line 15, I tell Oracle to compile and execute the procedure.
Now that you’re an expert at declaring variables, let’s take a look at when they are in scope.
Scope
In this context, scope refers to when a declared item can be seen by another PL/SQL block. And yes, I’m not just talking about variables here; I’m talking about any kind of declared item: a constant, cursor, function, procedure, or variable.
The following is a list of rules for scope. As you go through the list, it may be helpful for you to examine Figure 3-1.
- Any item declared in the declaration section of a function or procedure is visible only within the same function or procedure.
- Any item declared in the declaration section of a package body is visible only within any other item in the same package body.
- Any item declared in a package specification is visible to any other stored function, stored procedure, and package for which the owner of the calling method has execute privileges.
Figure 3-1. Where an item is declared detemines its scope
Listings 3-2 and 3-3 are a package spec and package body, respectively, to test scope. Listing 3-2 declares a global function, procedure, and variable in the package spec. Listing 3-3 declares an instance function, a procedure, and a variable. And, in the function and procedure implementations, I’ve declared local variables.
Listing 3-2. A Package Spec to Test Scope, scope.pks
01 create or replace package SCOPE as
02 /*
03 scope.pks
04 by Donald J. Bales on 2014-10-20
05 A package to test scope
06 */
07
08 -- Here's a global variable declaration
09 gv_scope varchar2(80) :=
10 'I''m a global (or package spec) variable';
11
12 -- Here's a global (or package spec) function declaration
13 FUNCTION my_scope_is_global
14 return varchar2;
15
16 -- Here's a global (or package spec) procedure declaration
17 PROCEDURE my_scope_is_global;
18
19
20 end SCOPE;
21 /
22 @se.sql SCOPE
Listing 3-3. A Package Body to Test Scope, scope.pkb
01 create or replace package body SCOPE as
02 /*
03 scope.pkb
04 by Donald J. Bales on 2014-10-20
05 A package to test scope
06 */
07
08 -- Here's an instance (or package body) variable declaration
09 iv_scope varchar2(80) :=
10 'I''m an instance (or package body) variable';
11
12
13 -- Here's an instance (or package body) function declaration
14 FUNCTION my_scope_is_instance
15 return varchar2 is
16 v_answer_1 varchar2(3) := 'Yes';
17 begin
18 pl(chr(9)||'Can function my_scope_is_instance see variable gv_scope?'),
19 pl(chr(9)||gv_scope);
20 return v_answer_1;
21 end my_scope_is_instance;
22
23
24 -- Here's a global (or package spec) function declaration
25 FUNCTION my_scope_is_global
26 return varchar2 is
27 v_answer_2 varchar2(3) := 'Yes';
28 begin
29 pl(chr(9)||'Can function my_scope_is_global see variable iv_scope?'),
30 pl(chr(9)||iv_scope);
31 return v_answer_2;
32 end my_scope_is_global;
33
34
35 -- Here's an instance (or package body) procedure declaration
36 PROCEDURE my_scope_is_instance is
37 v_answer_3 varchar2(3) := 'Yes';
38 begin
39 pl(chr(9)||'Can procedure my_scope_is_instance see variable gv_scope?'),
40 pl(chr(9)||gv_scope);
41 pl(v_answer_3);
42 end my_scope_is_instance;
43
44
45 -- Here's a global (or package spec) procedure declaration
46 PROCEDURE my_scope_is_global is
47 v_answer_4 varchar2(3) := 'Yes';
48 begin
49 pl(chr(9)||'Can procedure my_scope_is_global see variable iv_scope?'),
50 pl(chr(9)||iv_scope);
51 pl(v_answer_4);
52 end my_scope_is_global;
53
54
55 end SCOPE;
56 /
57 @se.sql SCOPE
Take some time to look over the two code listings. At this point, you should be able to understand the PL/SQL block structure of the package, and the methods declared and implemented in it, along with the variable declarations. The point now is to understand when a given function, procedure, or variable is in scope.
Listing 3-4 is an anonymous PL/SQL procedure that I wrote as a test unit for package SCOPE, specifically to help you understand when a declared item is in scope.
Listing 3-4. A Test Unit for Package SCOPE, scope.sql
01 rem scope.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem Test unit for package scope
04
05 declare
06
07 -- ANONYMOUS PL/SQL BLOCK'S DECLARATION SECTION --
08
09 v_scope varchar2(40) :=
10 'I''m a local variable';
11
12 -- This is a local (or embedded) function
13 FUNCTION my_scope_is_local
14 return varchar2 is
15 v_answer_0 varchar2(3) := 'Yes';
16 begin
17 return v_answer_0;
18 end my_scope_is_local;
19
20 -- This is a local (or embedded) procedure
21 PROCEDURE my_scope_is_local is
22 v_answer varchar2(3) := 'Yes';
23 begin
24 pl(v_answer);
25 end my_scope_is_local;
26
27 begin
28
29 -- ANONYMOUS PL/SQL BLOCK'S EXECUTABLE SECTION --
30
31 pl('Can I access my local variable?'),
32 pl(v_scope);
33 pl('Can I access SCOPE'' global variable?'),
34 pl(SCOPE.gv_scope);
35 pl('Can I access SCOPE'' instance variable?'),
36 --pl(SCOPE.iv_scope);
37 pl('No!'),
38
39 pl('Can I access my local function?'),
40 pl(my_scope_is_local());
41 pl('Can I access SCOPE'' global function?'),
42 pl(SCOPE.my_scope_is_global());
43 pl('Can I access SCOPE'' instance function?'),
44 --pl(SCOPE.my_scope_is_instance());
45 pl('No!'),
46
47 pl('Can I access my local procedure?'),
48 my_scope_is_local();
49 pl('Can I access SCOPE'' global procedure?'),
50 SCOPE.my_scope_is_global();
51 pl('Can I access SCOPE'' instance procedure?'),
52 --SCOPE.my_scope_is_instance();
53 pl('No!'),
54
55 end;
56 /
After I have compile scope.pks and scope.pkb, when I execute scopes.sql from Listing 3-4, I get the following output from SQL*Plus:
SQL> @scopes.sql
Can I access my local variable?
I'm a local variable
Can I access SCOPE' global variable?
I'm a global (or package spec) variable
Can I access SCOPE' instance variable?
No!
Can I access my local function?
Yes
Can I access SCOPE' global function?
Can function my_scope_is_global see variable iv_scope?
I'm an instance (or package body) variable
Yes
Can I access SCOPE' instance function?
No!
Can I access my local procedure?
Yes
Can I access SCOPE' global procedure?
Can procedure my_scope_is_global see variable iv_scope?
I'm an instance (or package body) variable
Yes
Can I access SCOPE' instance procedure?
No!
PL/SQL procedure successfully completed.
If you examine all three code listings and the SQL*Plus output carefully, you’ll see that you can access any item
- Declared in a package specification, granted you have access to it.
- Declared in a package body, if the calling declared item also exists in the same package body.
- Declared in a function or procedure, if you’re trying to access it from the same function or procedure.
Perhaps you would like to try testing the scope rules yourself?
It’s Your Turn to Scope Things Out
There’s nothing like hacking to see how things work. So put on your hacker’s hat and take the anonymous procedure in scope.sql for a ride.
- Compile scope.pks.
- Compile scope.pkb.
- Try alternatively removing the single-line comments characters on lines 36, 44, and 52 in scopes.sql, and then execute the script.
Each time you try to access a declared item that is out of scope, PL/SQL will let you know exactly where the coding problem exists in you source code.
Now that you have a firm understanding of scope, let’s step it up a notch.
Types
No discussion of declaring variables would be complete, nor would any programming language itself be complete, without mentioning arrays. PL/SQL supports three kinds of arrays, or as they are known in PL/SQL jargon, collections. I will discuss only one kind here: associative arrays. Originally called PL/SQL tables, associative arrays provide you with the means to create a single-dimension array. Associative arrays can be based on almost any data type. However, you’ll typically use one of the data types already covered.
By table TYPEs, I’m not referring to SQL tables that are used to permanently store data, but PL/SQL tables (or associative arrays), which are used to temporarily store data in memory. Listing 3-5 is an anonymous PL/SQL procedure that demonstrates how to declare an associative array (or PL/SQL table). You’ll see that declaring an associative array consists of two steps.
- Declare the new TYPE.
- Declare a new variable of that TYPE.
Listing 3-5. Declaring a PL/SQL Table with a Column Data Type, table.sql
01 rem table.sql
02 rem by Don Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to demonstrate
04 rem the elementary use of PL/SQL tables
05
06 declare
07
08 TYPE name_table IS TABLE OF WORKERS.name%TYPE
09 INDEX BY BINARY_INTEGER;
10
11 t_name name_table;
12
13 n_name binary_integer;
14
15 begin
16 t_name(1) := 'DOE, JOHN';
17 t_name(10) := 'DOE, JANE';
18
19 pl(t_name(1));
20 pl(t_name(10));
21 pl('There are '||t_name.count()||' elements.'),
22 n_name := t_name.first();
23 pl('The first element is '||n_name||'.'),
24 n_name := t_name.next(n_name);
25 pl('The next element is '||n_name||'.'),
26 n_name := t_name.last();
27 pl('The last element is '||n_name||'.'),
28 n_name := t_name.prior(n_name);
29 pl('The prior element is '||n_name||'.'),
30 if t_name.exists(1) then
31 pl('Element 1 exists.'),
32 end if;
33 pl('I''m deleting element 10'),
34 t_name.delete(10);
35 pl('There are '||t_name.count()||' elements.'),
36 if not t_name.exists(10) then
37 pl('Element 10 no longer exists.'),
38 end if;
39 pl('There are '||t_name.count()||' elements.'),
40 pl('I''m deleting all elements'),
41 t_name.delete();
42 pl('There are '||t_name.count()||' elements.'),
43 end;
44 /
The syntax used in Listing 3-5 on lines 8 and 9 to declare the associative array is as follows:
TYPE <plsql_table_type_name> IS TABLE OF <data_type>
INDEX BY BINARY_INTEGER;
where <plsql_table_type_name> is the name you are giving to the new PL/SQL table TYPE, and <data_type> is the data type to use for the elements in the table (or associative array).
Then, on line 11, I declare an associative array based on the new type with the following syntax:
<variable_name> <plsql_table_type_name>;
where <variable_name> is an identifier for the PL/SQL table, and <plsql_table_type_name> is the name of the TYPE.
One of the unique characteristics of an associative array is that it can be sparsely populated. That means you don’t need to add items consecutively to the array. Instead, you can add them to any index value between –2,147,483,647 and 2,147,483,647. Did you notice how I did just that in Listing 3-5, on lines 16 and 17? This can be very handy when copying values from a database table into a PL/SQL table because database tables are generally sparsely populated. You can then use the commands FIRST and NEXT to iterate through the list consecutively, or look up a value using the index randomly. (You can find more information about the FIRST and NEXT commands in the PL/SQL User’s Guide and Reference.)
Table 3-2 lists the PL/SQL table built-in functions and procedures. With PL/SQL tables, it’s also possible to use a varchar2 data type as the index value. So anywhere you see a reference to binary_integer, you can replace it with varchar2 (up to 32,767 characters long).
Table 3-2. PL/SQL Table (Associative Array) Built-in Functions and Procedures
Method | Description |
|---|---|
count() | Returns the number of elements |
delete(ain_index in binary_integer) | Deletes the specified element |
delete() | Deletes all elements |
exists(ain_index in binary_integer) | Returns TRUE if the element exists; otherwise, FALSE |
first() | Returns the index of the first element |
last() | Returns the index of the last element |
prior(ain_index in binary_integer) | Returns the index of the first element before the specified element |
next(ain_index in binary_integer) | Returns the index of the first element after the specified element |
The following is the output from SQL*Plus when I execute table.sql from Listing 3-5:
SQL> @table.sql
DOE, JOHN
DOE, JANE
There are 2 elements.
The first element is 1.
The next element is 10.
The last element is 10.
The prior element is 1.
Element 1 exists.
I'm deleting element 10
There are 1 elements.
Element 10 no longer exists.
There are 1 elements.
I'm deleting all elements
There are 0 elements.
PL/SQL procedure successfully completed.
As you can see from the output, the procedure in the table.sql script exercises each of the associative array’s methods.
Do you remember that I said it was possible to declare a PL/SQL table of almost any data type? Let’s look at creating one with a composite data type next. Listing 3-6 is an example of a PL/SQL table (associative array) based on a row-level anchor.
Listing 3-6. Declaring a PL/SQL Table with a Row Type Anchor, row.sql
01 rem row.sql
02 rem by Don Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to demonstrate
04 rem the elementary use of PL/SQL tables
05
06 declare
07
08 TYPE name_table IS TABLE OF WORKERS%ROWTYPE
09 INDEX BY BINARY_INTEGER;
10
11 t_name name_table;
12
13 n_name binary_integer;
14
15 begin
16 t_name(1).name := 'DOE, JOHN';
17 t_name(10).name := 'DOE, JANE';
18 pl(t_name(1).name);
19 pl(t_name(10).name);
20 pl('There are '||t_name.count()||' elements.'),
21 n_name := t_name.first();
22 pl('The first element is '||n_name||'.'),
23 n_name := t_name.next(n_name);
24 pl('The next element is '||n_name||'.'),
25 n_name := t_name.last();
26 pl('The last element is '||n_name||'.'),
27 n_name := t_name.prior(n_name);
28 pl('The prior element is '||n_name||'.'),
29 if t_name.exists(1) then
30 pl('Element 1 exists.'),
31 end if;
32 pl('I''m deleting element 10'),
33 t_name.delete(10);
34 pl('There are '||t_name.count()||' elements.'),
35 if not t_name.exists(10) then
36 pl('Element 10 no longer exists.'),
37 end if;
38 pl('There are '||t_name.count()||' elements.'),
39 pl('I''m deleting all elements'),
40 t_name.delete();
41 pl('There are '||t_name.count()||' elements.'),
42 end;
43 /
A couple of new things are happening in Listing 3-6. First, line 8 uses the keyword %ROWTYPE to anchor to a composite record type based on the columns in the WORKERS table. Second, on lines 16 and 17 I appended the composite record’s field name name with a dot operator (.) to the PL/SQL table’s name and index, in order to store the name value in the associative array. Other than those items, the procedures in Listing 3-5 and Listing 3-6 are identical. I did this to point out how to address the fields in a PL/SQL record in a PL/SQL table, and also to show that the use of the PL/SQL table operators remains the same, regardless of which data type is used.
Perhaps you would like to declare your own composite record type. Let’s see how you can do that next.
Just as you can use the TYPE keyword to declare a new PL/SQL table, you can also use it to declare a PL/SQL record. Listing 3-7 is an example of doing just that.
Listing 3-7. Declaring a PL/SQL Record, record.sql
01 rem record.sql
02 rem by Don Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to demonstrate
04 rem the use of PL/SQL records
05
06 declare
07
08 TYPE name_record is record (
09 first_name WORKERS.first_name%TYPE,
10 middle_name WORKERS.middle_name%TYPE,
11 last_name WORKERS.last_name%TYPE );
12
13 TYPE name_table is table of name_record
14 index by binary_integer;
15
16 t_name name_table;
17
18 begin
19 t_name(1).first_name := 'JOHN';
20 t_name(1).last_name := 'DOE';
21 t_name(2).first_name := 'JANE';
22 t_name(2).last_name := 'DOE';
23
24 pl(t_name(1).last_name||', '||t_name(1).first_name);
25 pl(t_name(2).last_name||', '||t_name(2).first_name);
26 end;
27 /
The syntax used in Listing 3-7 to declare a PL/SQL record is as follows:
TYPE <plsql_record_type_name> IS RECORD (
<field_name_1> <data_type_1>,
<field_name_2> <data_type_2>,...
<field_name_N> <data_type_N>);
where <plsql_record_type_name> is the name for the new PL/SQL record type, <field_name> is the name of a field in the record, and <data_type> is the data type for the corresponding field.
As with the program in Listing 3-6, on lines 19 through 25 in Listing 3-7 I use the dot operator (.) followed by the name of the field in the record to address the composite data type values in the PL/SQL table.
WHAT’S WITH THIS NOMENCLATURE INCONSISTENCY ANYWAY?
I like things, especially programming code, to be consistent. Consistency makes things obvious. Obvious makes program code easy to maintain. Along those lines, I would feel much better if Oracle had called the syntax for creating a composite data type in a PL/SQL block a row instead of a record. Then we could refer to a field as column. The syntax would then be as follows:
TYPE <plsql_row_type_name> IS ROW (
<column_name_1> <data_type_1>,
<column_name_2> <data_type_2>,...
<column_name_N> <data_type_N>);
OK, now I feel better. I just needed to share how much that irritates me!
Earlier, I said that associative arrays will support only a single dimension. In other words, you can’t declare a multidimensional array and address it like this: t_name(1, 7).name. Well, there’s a way around that limitation. Every good problem deserves a good hack. Listing 3-8 demonstrates how to use a PL/SQL table inside a PL/SQL record in order to work around the one-dimension limit.
Listing 3-8. A Hack to Work Around the PL/SQL Table One-Dimension Limit, multidimensional.sql
01 rem multidimensional.sql
02 rem Copyright by Don Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to demonstrate
04 rem the use of nested PL/SQL tables
05
06 declare
07
08 TYPE name_table is table of WORKERS.name%TYPE
09 index by binary_integer;
10
11 TYPE name_record is record (
12 dim2 name_table );
13
14 TYPE dim1 is table of name_record
15 index by binary_integer;
16
17 t_dim1 dim1;
18
19 begin
20 t_dim1(1).dim2(1) := 'DOE, JOHN';
21 t_dim1(1).dim2(2) := 'DOE, JANE';
22
23 t_dim1(2).dim2(1) := 'DOUGH, JAYNE';
24 t_dim1(2).dim2(2) := 'DOUGH, JON';
25
26 pl(t_dim1(1).dim2(1));
27 pl(t_dim1(1).dim2(2));
28 pl(t_dim1(2).dim2(1));
29 pl(t_dim1(2).dim2(2));
30 end;
31 /
Here’s how Listing 3-8 works:
- Lines 8 and 9 declare a table type, name_table, to hold a list of names.
- Lines 11 and 12 declare a record type, name_record, with one field named dim2, where the data type is name_table, a PL/SQL table.
- Lines 14 and 15 declare a second table type, dim1, which is based on record type dim2.
- On lines 20 through 29, with the pseudo-two-dimension table in place, I exercise accessing it using two indexes.
Since the use of arrays is a rather advanced topic, I’m going to skip the exercise here.
Next, let’s look at the most temporary of variables, which aren’t really variables at all: parameters.
Parameters
Parameters allow you to pass values into (IN), pass values out of (OUT), or pass values into and out of (IN OUT) a cursor, function, or procedure. (Cursors are covered in Chapter 5.) You’ve already seen parameters used in earlier code listings. I’m covering parameters here because they use declaration syntax similar to variables, and they themselves are variables.
Parameters follow the same PL/SQL identifier naming rules as variables. In practice, however, I like to use a couple of additional prefix characters on parameter names so I can also tell their scope, along with the data type. Table 3-3 is a list of those parameter prefix values.
Table 3-3. My PL/SQL Parameter Naming Prefixes
Prefix | Description |
|---|---|
ai | Argument IN |
ao | Argument OUT |
aio | Argument IN OUT |
For example, if I were to declare parameters to pass data from the columns in the AUTHORS table into and/or out of a method, I would use the identifier names in Table 3-4.
Table 3-4. Parameter Name Examples Using My Prefixing Scheme
Scope | ||
|---|---|---|
IN | OUT | IN OUT |
ain_id | aon_id | aion_id |
aiv_name | aov_name | aiov_name |
aid_birth_date | aod_birth_date | aiod_birth_date |
aiv_gender | aov_gender | aiov_gender |
The advantage of using these prefixes is that you’ll always know the scope and data type of the parameter with which you’re working.
Listing 3-9 and 3-10 create a package named PARAMETER, which I’ll use to explain the parameter declaration syntax and parameter scope.
Listing 3-9. A Package Spec to Test Parameter Scope, parameter.pks
01 create or replace package PARAMETER as
02 /*
03 parameter.pks
04 by Donald J. Bales on 2014-10-20
05 A packge to test parameter scope
06 */
07
08 -- A function that execises the scope of parameters
09 FUNCTION in_out_inout(
10 aiv_in in varchar2,
11 aov_out out varchar2,
12 aiov_inout in out varchar2)
13 return varchar2;
14
15
16 -- A procedure that execises the scope of parameters
17 PROCEDURE in_out_inout(
18 aiv_in in varchar2,
19 aov_out out varchar2,
20 aiov_inout in out varchar2);
21
22
23 end PARAMETER;
24 /
25 @se.sql PARAMETER
The syntax used to declare the parameters in the function and procedure in Listing 3-9 is as follows:
(
<parameter_name_1> [IN][OUT] <data_type_1>,
<parameter_name_2> [IN][OUT] <data_type_2>,...
<parameter_name_N> [IN][OUT] <data_type_N>)
where <parameter_name> is the name of the parameter; the scope is IN, OUT, or IN OUT; and <data_type> is the data type of the parameter. As you have already seen in previous listings, you can use column or row anchors to specify the data type (you know—%TYPE or %ROWTYPE). However, the value of the parameter will not be constrained by the specified size in the anchor. Only the data type is used from the anchor.
The parameter keywords IN and OUT determine the accessibility, or scope, of the parameters.
- IN makes your parameters’ data available to the called cursor, function, or procedure.
- OUT allows the called function or procedure to set the parameter’s value within the called PL/SQL block.
- The combination of IN and OUT allows both levels of accessibility.
Seeing is believing, so take some time to study Listings 3-10 and 3-11, and the output of Listing 3-11.
Listing 3-10. A Package Body to Test Parameter Scope, parameter.pkb
01 create or replace package body PARAMETER as
02 /*
03 parameter.pkb
04 by Donald J. Bales on 2014-10-20
05 A packge to test parameter scope
06 */
07
08 FUNCTION in_out_inout(
09 aiv_in in varchar2,
10 aov_out out varchar2,
11 aiov_inout in out varchar2)
12 return varchar2 is
13 begin
14 pl(chr(9)||'Before assignments...'),
15 pl(chr(9)||'Inside function in_out_inout, aiv_in = '||aiv_in);
16 pl(chr(9)||'Inside function in_out_inout, aov_out = '||aov_out);
17 pl(chr(9)||'Inside function in_out_inout, aiov_inout = '||aiov_inout);
18 -- You can only assign a value (write) to an OUT
19 -- parameter, you can't read it!
20 aov_out := 'OUT';
21
22 -- You can only read an IN parameter
23 aiov_inout := aiv_in;
24
25 -- You can read and write an IN OUT parameter
26 aiov_inout := aiov_inout||'OUT';
27
28 pl(chr(9)||'After assignments...'),
29 pl(chr(9)||'Inside function in_out_inout, aiv_in = '||aiv_in);
30 pl(chr(9)||'Inside function in_out_inout, aov_out = '||aov_out);
31 pl(chr(9)||'Inside function in_out_inout, aiov_inout = '||aiov_inout);
32 return 'OK'; -- a function must return a value!
33 end in_out_inout;
34
35
36 PROCEDURE in_out_inout(
37 aiv_in in varchar2,
38 aov_out out varchar2,
39 aiov_inout in out varchar2) is
40 begin
41 pl(chr(9)||'Before assignments...'),
42 pl(chr(9)||'Inside procedure in_out_inout, aiv_in = '||aiv_in);
43 pl(chr(9)||'Inside procedure in_out_inout, aov_out = '||aov_out);
44 pl(chr(9)||'Inside procedure in_out_inout, aiov_inout = '||aiov_inout);
45 -- You can only assign a value (write) to an OUT
46 -- parameter, you can't read it!
47 aov_out := 'OUT';
48
49 -- You can only read an IN parameter
50 aiov_inout := aiv_in;
51
52 -- You can read and write an IN OUT parameter
53 aiov_inout := aiov_inout||'OUT';
54 pl(chr(9)||'After assignments...'),
55 pl(chr(9)||'Inside procedure in_out_inout, aiv_in = '||aiv_in);
56 pl(chr(9)||'Inside procedure in_out_inout, aov_out = '||aov_out);
57 pl(chr(9)||'Inside procedure in_out_inout, aiov_inout = '||aiov_inout);
58 end in_out_inout;
59
60
61 end PARAMETER;
62 /
63 @be.sql PARAMETER
Listing 3-11 is an anonymous PL/SQL procedure to test the scope defined by the use of IN, OUT, or IN OUT.
Listing 3-11. A Test Unit for Package PARAMETER, parameter.sql
01 rem parameter.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem A test unit for package PARAMETER
04
05 declare
06
07 v_in varchar2(30) := 'IN';
08 v_out varchar2(30) :=
09 'Na na, you can''t see me!';
10 v_inout varchar2(30) :=
11 'But you can see me!';
12 v_return varchar2(30);
13
14 begin
15 pl('Before calling the function...'),
16 pl('Inside test unit parameter v_in = '||v_in);
17 pl('Inside test unit parameter v_out = '||v_out);
18 pl('Inside test unit parameter v_inout = '||v_inout);
19 pl('Test function PARAMETER.in_out_inout(v_in, v_out, v_inout).'),
20 v_return := PARAMETER.in_out_inout(v_in, v_out, v_inout);
21 pl(v_return);
22 pl('After calling the function...'),
23 pl('Inside test unit parameter v_in = '||v_in);
24 pl('Inside test unit parameter v_out = '||v_out);
25 pl('Inside test unit parameter v_inout = '||v_inout);
26 pl('Resetting initial values...'),
27 v_out := 'Na na, you can''t see me!';
28 v_inout := 'But you can see me!';
29 pl('Before calling the procedure...'),
30 pl('Inside test unit parameter v_in = '||v_in);
31 pl('Inside test unit parameter v_out = '||v_out);
32 pl('Inside test unit parameter v_inout = '||v_inout);
33 pl('Test procedure PARAMETER.in_out_inout(v_in, v_out, v_inout).'),
34 PARAMETER.in_out_inout(v_in, v_out, v_inout);
35 pl('OK'),
36 pl('After calling the procedure...'),
37 pl('Inside test unit parameter v_in = '||v_in);
38 pl('Inside test unit parameter v_out = '||v_out);
39 pl('Inside test unit parameter v_inout = '||v_inout);
40 end;
41 /
Here’s the SQL*Plus output from the parameter.sql script (Listing 3-11):
SQL> @parameter.sql
Before calling the function...
Inside test unit parameter v_in = IN
Inside test unit parameter v_out = Na na, you can't see me!
Inside test unit parameter v_inout = But you can see me!
Test function PARAMETER.in_out_inout(v_in, v_out, v_inout).
Before assignments...
Inside function in_out_inout, aiv_in = IN
Inside function in_out_inout, aov_out =
Inside function in_out_inout, aiov_inout = But you can see me!
After assignments...
Inside function in_out_inout, aiv_in = IN
Inside function in_out_inout, aov_out = OUT
Inside function in_out_inout, aiov_inout = INOUT
OK
After calling the function...
Inside test unit parameter v_in = IN
Inside test unit parameter v_out = OUT
Inside test unit parameter v_inout = INOUT
Resetting initial values...
Before calling the procedure...
Inside test unit parameter v_in = IN
Inside test unit parameter v_out = Na na, you can't see me!
Inside test unit parameter v_inout = But you can see me!
Test procedure PARAMETER.in_out_inout(v_in, v_out, v_inout).
Before assignments...
Inside procedure in_out_inout, aiv_in = IN
Inside procedure in_out_inout, aov_out =
Inside procedure in_out_inout, aiov_inout = But you can see me!
After assignments...
Inside procedure in_out_inout, aiv_in = IN
Inside procedure in_out_inout, aov_out = OUT
Inside procedure in_out_inout, aiov_inout = INOUT
OK
After calling the procedure...
Inside test unit parameter v_in = IN
Inside test unit parameter v_out = OUT
Inside test unit parameter v_inout = INOUT
PL/SQL procedure successfully completed.
As you can verify from studying Listing 3-11 and the output from test unit parameter.sql,
- An IN parameter can be used to pass a value into a cursor, function, or procedure.
- An OUT parameter can be used to pass a value out of a function or procedure.
- An IN OUT parameter can be used to do both.
It’s Your Turn to Declare Parameters
You’ll have plenty of practice declaring parameters in the coming chapters. So I want you to do this instead:
- Start on Chapter 4.
Summary
At this point, you should be a master of variable and parameter declarations. And you should understand the scope in which they are accessible. Next, let’s start working with some SQL in your PL/SQL.