PL/SQL User's Guide and Reference | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Ching Hao
The previous chapter provided an overview of PL/SQL. This chapter focuses on the small-scale aspects of the language. Like every other programming language, PL/SQL has a character set, reserved words, punctuation, datatypes, rigid syntax, and fixed rules of usage and statement formation. You use these basic elements of PL/SQL to represent real-world objects and operations.
bonus := salary * 0.10; -- compute bonus
contains the following lexical units:
IF x > y THEN high := x; ENDIF; -- illegal
However, you cannot embed spaces in lexical units except for string literals and comments. For example, the following line is illegal because the compound symbol for assignment (:=) is split:
count : = count + 1; -- illegal
To show structure, you can divide lines using carriage returns and indent lines using spaces or tabs. Compare the following IF statements for readability:
IF x>y THEN max:=x;ELSE max:=y;END IF; | IF x > y THEN | max := x; | ELSE | max := y; | END IF;
X t2 phone# credit_limit LastName oracle$number
An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are illegal, as the following examples show:
mine&yours -- illegal ampersand debit-amount -- illegal hyphen on/off -- illegal slash user id -- illegal space
The next examples show that adjoining and trailing dollar signs, underscores, and number signs are legal:
money$$$tree SN## try_again_
You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows:
lastname LastName -- same as lastname LASTNAME -- same as lastname and LastName
The length of an identifier cannot exceed 30 characters. But, every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:
lastname last_name
Identifiers should be descriptive. So, use meaningful names such as credit_limit and cost_per_thousand. Avoid obscure names such as cr_lim and cpm.
DECLARE end BOOLEAN; -- illegal; causes compilation error
However, you can embed reserved words in an identifier, as the following example shows:
DECLARE end_of_game BOOLEAN; -- legal
Often, reserved words are written in upper case to promote readability. However, like other PL/SQL identifiers, reserved words can be written in lower or mixed case. For a list of reserved words, see Appendix E.
"X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***"
The maximum length of a quoted identifier is 30 characters not counting the double quotes.
Using PL/SQL reserved words as quoted identifiers is allowed but not recommended. It is poor programming practice to reuse reserved words.
Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word TYPE in a CREATE TABLE statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows:
SELECT acct, type, bal INTO ... -- causes compilation error
To prevent the error, enclose the uppercase column name in double quotes, as follows:
SELECT acct, "TYPE", bal INTO ...
The column name cannot appear in lower or mixed case (unless it was defined that way in the CREATE TABLE statement). For example, the following statement is invalid:
SELECT acct, "type", bal INTO ... -- causes compilation error
Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.
030 6 -14 0 +32767
A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:
6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.
PL/SQL considers numbers such as 12.0 and 25. to be reals even though they have integral values.
Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer. A few examples follow:
2E5 1.0E-7 3.14159e0 -1E38 -9.5e-3
E stands for "times ten to the power of." As the next example shows, the number after E is the power of ten by which the number before E must be multiplied:
5E3 = 5 X 103 = 5 X 1000 = 5000
The number after E also corresponds to the number of places the decimal point shifts. In the last example, the implicit decimal point shifted three places to the right; in the next example, it shifts three places to the left:
5E-3 = 5 X 10-3 = 5 X 0.001 = 0.005
'Z' '%' '7' ' ' 'z' '('
Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Z' and 'z' to be different.
The character literals '0' .. '9' are not equivalent to integer literals, but can be used in arithmetic expressions because they are implicitly convertible to integers.
'Hello, world!' 'XYZ Corporation' '10-NOV-91' 'He said "Life is like licking honey from a thorn."' '$1,000,000'
All string literals except the null string ('') have datatype CHAR.
Given that apostrophes (single quotes) delimit string literals, how do you represent an apostrophe within a string? As the next example shows, you write two single quotes, which is not the same as writing a double quote:
'Don''t leave without saving your work.'
PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:
'baker' 'Baker'
-- begin processing SELECT sal INTO salary FROM emp -- get current salary WHERE empno = emp_id; bonus := salary * 0.15; -- compute bonus amount
Notice that comments can appear within a statement at the end of a line.
While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:
-- DELETE FROM emp WHERE comm IS NULL;
/* Compute a 15% bonus for top-rated employees. */ IF rating > 90 THEN bonus := salary * 0.15 /* bonus is based on salary */ ELSE bonus := 0; END IF;
The next three examples illustrate some popular formats:
/* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. */ area := pi * radius**2;
/************************************************************** * The following line computes the area of a circle using pi, * * which is the ratio between the circumference and diameter. * **************************************************************/ area := pi * radius**2;
/* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. */ area := pi * radius**2;
You can use multi-line comment delimiters to comment-out whole sections of code, as the following example shows:
/* OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ... END LOOP; CLOSE c1; */
Figure 2 - 1 shows the predefined datatypes available for your use. An additional scalar type, MLSLABEL, is available with Trusted Oracle, a specially secured version of Oracle. The scalar types fall into four families, which store number, character, date/time, or Boolean data, respectively.
Figure 2 - 1. Predefined Datatypes
This section discusses the scalar types; the composite types are discussed; the reference type is discussed in "Using Cursor Variables"
.
NUMBER[(precision, scale)]
You cannot use constants or variables to specify precision and scale; you must use integer literals.
The maximum precision of a NUMBER value is 38 decimal digits; the magnitude range is 1.0E-129 .. 9.99E125. If you do not specify the precision, it defaults to the maximum value supported by your system.
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.
You can use these subtypes for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than NUMBER.
Although PLS_INTEGER and BINARY_INTEGER are both integer types with the same magnitude range, they are not fully compatible. When a PLS_INTEGER calculation overflows, an exception is raised. However, when a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable.
Because of this small semantic difference, you might want to continue using BINARY_INTEGER in old applications for compatibility. In new applications, always use PLS_INTEGER for better performance.
The CHAR datatype takes an optional parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:
CHAR[(maximum_length)]
You cannot use a constant or variable to specify the maximum length; you must use an integer literal. If you do not specify the maximum length, it defaults to 1.
Remember, you specify the maximum length of a CHAR(n) variable in bytes, not characters. So, if a CHAR(n) variable stores multi-byte characters, its maximum length is less than n characters.
Although the maximum length of a CHAR(n) variable is 32767 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. You can insert any CHAR(n) value into a LONG database column because the maximum width of a LONG column is 2147483647 bytes or 2 gigabytes. However, you cannot select a value longer than 32767 bytes from a LONG column into a CHAR(n) variable.
You can insert any LONG value into a LONG database column because the maximum width of a LONG column is 2147483647 bytes. However, you cannot select a value longer than 32760 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, INSERT, and (most) SELECT statements, but not in expressions, SQL function calls, or certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY. For more information, see Oracle7 Server SQL Reference.
The RAW datatype takes a required parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:
RAW(maximum_length)
You cannot use a constant or variable to specify the maximum length; you must use an integer literal.
Although the maximum length of a RAW variable is 32767 bytes, the maximum width of a RAW database column is 255 bytes. Therefore, you cannot insert values longer than 255 bytes into a RAW column. You can insert any RAW value into a LONG RAW database column because the maximum width of a LONG RAW column is 2147483647 bytes. However, you cannot select a value longer than 32767 bytes from a LONG RAW column into a RAW variable.
You can insert any LONG RAW value into a LONG RAW database column because the maximum width of a LONG RAW column is 2147483647 bytes. However, you cannot select a value longer than 32760 bytes from a LONG RAW column into a LONG RAW variable.
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
BBBBBBBB.RRRR.FFFF
where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file.
These numbers are hexadecimal. For example, the rowid
0000000E.000A.0007
points to the 11th row in the 15th block in the 7th database file.
Typically, ROWID variables are compared to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor. For an example, see "Fetching Across Commits" .
The VARCHAR2 datatype takes a required parameter that specifies a maximum length up to 32767 bytes. The syntax follows:
VARCHAR2(maximum_length)
You cannot use a constant or variable to specify the maximum length; you must use an integer literal.
Remember, you specify the maximum length of a VARCHAR2(n) variable in bytes, not characters. So, if a VARCHAR2(n) variable stores multi-byte characters, its maximum length is less than n characters.
Although the maximum length of a VARCHAR2(n) variable is 32767 bytes, the maximum width of a VARCHAR2 database column is 2000 bytes. Therefore, you cannot insert values longer than 2000 bytes into a VARCHAR2 column. You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483647 bytes. However, you cannot select a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable.
Some important semantic differences between the CHAR and VARCHAR2 base types are described in Appendix C.
The BOOLEAN datatype takes no parameters. Only the values TRUE and FALSE and the non-value NULL can be assigned to a BOOLEAN variable. You cannot insert the values TRUE and FALSE into a database column. Furthermore, you cannot select or fetch column values into a BOOLEAN variable.
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 midnight.
You can use the MLSLABEL datatype to define a database column. Also, you can use the %TYPE and %ROWTYPE attributes to reference the column. However, with standard Oracle, such columns can store only nulls.
With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL. If the label is in text format, Trusted Oracle converts it to a binary value automatically. The text string can be up to 255 bytes long. However, the internal length of an MLSLABEL value is between 2 and 5 bytes.
With Trusted Oracle, you can also select values from a MLSLABEL column into a character variable. Trusted Oracle converts the internal binary value to a VARCHAR2 value automatically.
PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefines the subtype CHARACTER, as follows:
SUBTYPE CHARACTER IS CHAR;
The subtype CHARACTER specifies the same set of values as its base type CHAR. Thus, CHARACTER is an unconstrained subtype.
Subtypes can increase reliability, provide compatibility with ANSI/ISO and IBM types, and improve readability by indicating the intended use of constants and variables.
SUBTYPE subtype_name IS base_type;
where subtype_name is a type specifier used in subsequent declarations and base_type stands for the following syntax:
{ cursor_name%ROWTYPE | cursor_variable_name%ROWTYPE | plsql_table_name%TYPE | record_name%TYPE | scalar_type_name | table_name%ROWTYPE | table_name.column_name%TYPE | variable_name%TYPE}
For example, all of the following subtype definitions are legal:
DECLARE SUBTYPE EmpDate IS DATE; -- based on DATE type SUBTYPE Counter IS NATURAL; -- based on NATURAL subtype TYPE NameTab IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; SUBTYPE EnameTab IS NameTab; -- based on TABLE type TYPE TimeTyp IS RECORD (minute INTEGER, hour INTEGER); SUBTYPE Clock IS TimeTyp; -- based on RECORD type SUBTYPE ID_Num IS emp.empno%TYPE; -- based on column type CURSOR c1 IS SELECT * FROM dept; SUBTYPE Dept_Rec IS c1%ROWTYPE; -- based on cursor rowtype
However, you cannot specify a constraint on the base type. For example, the following definitions are illegal:
DECLARE SUBTYPE Accumulator IS NUMBER(7,2); -- illegal; must be NUMBER SUBTYPE Delimiter IS CHAR(1); -- illegal; must be CHAR SUBTYPE Word IS VARCHAR2(15); -- illegal
Although you cannot define constrained subtypes directly, you can use a simple workaround to define size-constrained subtypes indirectly. Simply declare a size-constrained variable, then use %TYPE to provide its datatype, as shown in the following example:
DECLARE temp VARCHAR2(15); SUBTYPE Word IS temp%TYPE; -- maximum size of Word is 15
Likewise, if you define a subtype using %TYPE to provide the datatype of a database column, the subtype adopts the size constraint (if any) of the column. However, the subtype does not adopt other kinds of constraints such as NOT NULL.
DECLARE SUBTYPE Counter IS NATURAL; rows Counter; employees Counter;
The following example shows that you can constrain a user-defined subtype when declaring variables of that type:
DECLARE SUBTYPE Accumulator IS NUMBER; total Accumulator(7,2);
Subtypes can increase reliability by detecting out-of-range values. In the example below, you restrict the subtype Scale to storing integers in the range -9 .. 9. If your program tries to store a number outside that range in a Scale variable, PL/SQL raises an exception.
DECLARE temp NUMBER(1,0); SUBTYPE Scale IS temp%TYPE; x_axis Scale; -- magnitude range is -9 .. 9 y_axis Scale; BEGIN x_axis := 10; -- raises VALUE_ERROR
DECLARE SUBTYPE Accumulator IS NUMBER; amount NUMBER(7,2); total Accumulator; BEGIN ... total := amount; ... END;
Different subtypes are interchangeable if they have the same base type. For instance, given the following declarations, the value of finished can be assigned to debugging:
DECLARE SUBTYPE Sentinel IS BOOLEAN; SUBTYPE Switch IS BOOLEAN; finished Sentinel; debugging Switch; BEGIN ... debugging := finished; ... END;
Different subtypes are also interchangeable if their base types are in the same datatype family. For example, given the following declarations, the value of verb can be assigned to sentence:
DECLARE SUBTYPE Word IS CHAR; SUBTYPE Text IS VARCHAR2; verb Word; sentence Text; BEGIN ... sentence := verb; ... END;
To | |||||
From | CHAR | DATE | NUMBER | RAW | ROWID |
CHAR | TO_DATE | TO_NUMBER | HEXTORAW | CHARTOROWID | |
DATE | TO_CHAR | ||||
NUMBER | TO_CHAR | TO_DATE | |||
RAW | RAWTOHEX | ||||
ROWID | ROWIDTOCHAR | ||||
DECLARE start_time CHAR(5); finish_time CHAR(5); elapsed_time NUMBER(5); BEGIN /* Get system time as seconds past midnight. */ SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual; -- do something /* Get system time again. */ SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual; /* Compute elapsed time in seconds. */ elapsed_time := finish_time - start_time; INSERT INTO results VALUES (elapsed_time, ...); END;
Before assigning a selected column value to a variable, PL/SQL will, if necessary, convert the value from the datatype of the source column to the datatype of the variable. This happens, for example, when you select a DATE column value into a VARCHAR2 variable. Likewise, before assigning the value of a variable to a database column, PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column.
If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use a datatype conversion function. Table 2 - 2 shows which implicit conversions PL/SQL can do.
To | |||||||||
From | BIN_INT | CHAR | DATE | LONG | NUMBER | PLS_INT | RAW | ROWID | VARCHAR2 |
BIN_INT | _/ | _/ | _/ | _/ | _/ | ||||
CHAR | _/ | _/ | _/ | _/ | _/ | _/ | _/ | _/ | |
DATE | _/ | _/ | _/ | ||||||
LONG | _/ | _/ | _/ | ||||||
NUMBER | _/ | _/ | _/ | _/ | _/ | ||||
PLS_INT | _/ | _/ | _/ | _/ | _/ | ||||
RAW | _/ | _/ | _/ | ||||||
ROWID | _/ | _/ | |||||||
VARCHAR2 | _/ | _/ | _/ | _/ | _/ | _/ | _/ | _/ | |
It is your responsibility to ensure that values are convertible. For instance, PL/SQL can convert the CHAR value '02-JUN-92' to a DATE value, but PL/SQL cannot convert the CHAR value 'YESTERDAY' to a DATE value. Similarly, PL/SQL cannot convert a VARCHAR2 value containing alphabetic characters to a NUMBER value.
A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a DATE column. So, PL/SQL calls the function TO_DATE, which expects the default date format. To insert dates in other formats, you must call TO_DATE with a format mask.
A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a RAW or LONG RAW column. Each pair of characters in the variable must represent the hexadecimal equivalent of a binary byte. If either character does not represent the hexadecimal equivalent of a nibble, PL/SQL raises an exception.
You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it. A couple of examples follow:
birthday DATE; emp_count SMALLINT := 0;
The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator (:=) to assign an initial value of zero to the variable.
The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:
pi REAL := 3.14159; radius REAL := 1; area REAL := pi * radius**2;
By default, variables are initialized to NULL. For example, the following declarations are equivalent:
birthday DATE; birthday DATE := NULL;
In constant declarations, the keyword CONSTANT must precede the type specifier, as the following example shows:
credit_limit CONSTANT REAL := 5000.00;
This declaration names a constant of type REAL and assigns an initial (also final) value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, you get a compilation error when the declaration is elaborated. (The processing of a declaration by the PL/SQL compiler is called elaboration.)
tax_year SMALLINT := 95; valid BOOLEAN := FALSE;
can be rewritten as follows:
tax_year SMALLINT DEFAULT 95; valid BOOLEAN DEFAULT FALSE;
You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.
acct_id INTEGER(4) NOT NULL := 9999;
You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR. The NOT NULL constraint must be followed by an initialization clause. For example, the following declaration is illegal:
acct_id INTEGER(5) NOT NULL; -- illegal; not initialized
Recall that the subtypes NATURALN and POSITIVEN are predefined as NOT NULL. For instance, the following declarations are equivalent:
emp_count NATURAL NOT NULL := 0; emp_count NATURALN := 0;
In NATURALN and POSITIVEN declarations, the type specifier must be followed by an initialization clause. Otherwise, you get a compilation error. For example, the following declaration is illegal:
line_items POSITIVEN; -- illegal; not initialized
credit REAL(7,2); debit credit%TYPE;
Variables declared using %TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable.
The next example shows that a %TYPE declaration can include an initialization clause:
balance NUMBER(7,2); minimum_balance balance%TYPE := 10.00;
The %TYPE attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in
my_dname scott.dept.dname%TYPE;
Using %TYPE to declare my_dname has two advantages. First, you need not know the exact datatype of dname. Second, if the database definition of dname changes, the datatype of my_dname changes accordingly at run time.
Note, however, that a NOT NULL column constraint does not apply to variables declared using %TYPE. In the next example, even though the database column empno is defined as NOT NULL, you can assign a null to the variable my_empno:
DECLARE my_empno emp.empno%TYPE; ... BEGIN my_empno := NULL; -- this works
DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;
Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you select column values into a record named emp_rec:
DECLARE emp_rec emp%ROWTYPE; ... BEGIN SELECT * INTO emp_rec FROM emp WHERE ...
The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:
IF emp_rec.deptno = 20 THEN ...
Also, you can assign the value of an expression to a specific field, as the following examples show:
emp_rec.ename := 'JOHNSON'; emp_rec.sal := emp_rec.sal * 1.15;
DECLARE dept_rec1 dept%ROWTYPE; dept_rec2 dept%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec3 c1%ROWTYPE; BEGIN ... dept_rec1 := dept_rec2;
However, because dept_rec2 is based on a table and dept_rec3 is based on a cursor, the following assignment is illegal:
dept_rec2 := dept_rec3; -- illegal
Second, you can assign a list of column values to a record by using the SELECT or FETCH statement, as the example below shows. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement.
DECLARE dept_rec dept%ROWTYPE; ... BEGIN SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE deptno = 30;
However, you cannot assign a list of column values to a record by using an assignment statement. So, the following syntax is illegal:
record_name := (value1, value2, value3, ...); -- illegal
Although you can retrieve entire records, you cannot insert or update them. For example, the following statement is illegal:
INSERT INTO dept VALUES (dept_rec); -- illegal
-- available online in file EXAMP4 DECLARE CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.wages > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END;
For more information about database column aliases, see Oracle7 Server SQL Reference.
maxi INTEGER := 2 * mini; -- illegal mini INTEGER := 15;
However, PL/SQL does allow the forward declaration of subprograms. For more information, see "Forward Declarations" .
Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. For example, the following declaration is illegal:
i, j, k SMALLINT; -- illegal
The legal version follows:
i SMALLINT; j SMALLINT; k SMALLINT;
raise_salary(...); -- simple emp_actions.raise_salary(...); -- qualified raise_salary@newyork(...); -- remote emp_actions.raise_salary@newyork(...); -- qualified and remote
In the first case, you simply use the procedure name. In the second case, you must qualify the name using dot notation because the procedure is stored in a package called emp_actions. In the third case, you reference the database link newyork because the (standalone) procedure is stored in a remote database. In the fourth case, you qualify the procedure name and reference a database link.
DECLARE valid_id BOOLEAN; valid_id VARCHAR2(5); -- illegal duplicate identifier FUNCTION bonus (valid_id IN INTEGER) RETURN REAL IS ... -- illegal triplicate identifier
For the scoping rules that apply to identifiers, see "Scope and Visibility" .
DECLARE zip_code INTEGER; Zip_Code INTEGER; -- same as zip_code ZIP_CODE INTEGER; -- same as zip_code and Zip_Code
FOR emp IN 1..5 LOOP ... UPDATE emp SET bonus = 500 WHERE ... END LOOP;
Likewise, the following SELECT statement fails because PL/SQL assumes that emp refers to the formal parameter:
PROCEDURE calc_bonus (emp NUMBER, bonus OUT REAL) IS avg_sal REAL; BEGIN SELECT AVG(sal) INTO avg_sal FROM emp WHERE ...
In such cases, you can prefix the table name with a username, as follows, but it is better programming practice to rename the variable or formal parameter.:
PROCEDURE calc_bonus (emp NUMBER, bonus OUT REAL) IS avg_sal REAL; BEGIN SELECT AVG(sal) INTO avg_sal FROM scott.emp WHERE ...
Unlike the names of tables, the names of columns take precedence over the names of local variables and formal parameters. For example, the following DELETE statement removes all employees from the emp table, not just KING, because Oracle assumes that both enames in the WHERE clause refer to the database column:
DECLARE ename VARCHAR2(10) := 'KING'; BEGIN DELETE FROM emp WHERE ename = ename;
In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with my_, as follows:
DECLARE my_ename VARCHAR2(10);
Or, use a block label to qualify references, as in
<<main>> DECLARE ename VARCHAR2(10) := 'KING'; BEGIN DELETE FROM emp WHERE ename = main.ename;
The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:
FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS job CHAR(10); BEGIN ... SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job; -- refers to formal parameter and local variable
For a full discussion of name resolution, see Oracle7 Server Application Developer's Guide.
Figure 2 - 2. Scope and Visibility
Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both identifiers remain in scope. Within the sub-block, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.
Although you cannot declare an identifier twice in the same block, you can declare the same identifier in two different blocks. The two objects represented by the identifier are distinct, and any change in one does not affect the other.
However, a block cannot reference identifiers declared in other blocks nested at the same level because those identifiers are neither local nor global to the block. The following example illustrates the scope rules:
DECLARE a CHAR; b REAL; BEGIN -- identifiers available here: a (CHAR), b DECLARE a INTEGER; c REAL; BEGIN -- identifiers available here: a (INTEGER), b, c END; DECLARE d REAL; BEGIN -- identifiers available here: a (CHAR), b, d END; -- identifiers available here: a (CHAR), b END;
Recall that global identifiers can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier unless you use a qualified name. The qualifier can be the label of an enclosing block, as the following example shows:
<<outer>> DECLARE birthdate DATE; BEGIN DECLARE birthdate DATE; BEGIN ... IF birthdate = outer.birthdate THEN ...
As the next example shows, the qualifier can also be the name of an enclosing subprogram:
PROCEDURE check_credit (...) IS rating NUMBER; FUNCTION valid (...) RETURN BOOLEAN IS rating NUMBER; BEGIN ... IF check_credit.rating < 3 THEN ...
However, within the same scope, a label and a subprogram cannot have the same name.
DECLARE count INTEGER; ... BEGIN count := count + 1; -- assigns a null to count
Therefore, never reference a variable before you assign it a value.
You can use assignment statements to assign values to a variable. For example, the following statement assigns a new value to the variable bonus, overwriting its old value:
bonus := salary * 0.15;
The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.
DECLARE done BOOLEAN;
the following statements are legal:
BEGIN done := FALSE; WHILE NOT done LOOP ...
When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:
done := (count > 500);
SELECT ename, sal + comm INTO last_name, wages FROM emp WHERE empno = emp_id;
However, you cannot select column values into a Boolean variable.
-X / 2 + 3
Unary operators such as the negation operator (-) operate on one operand; binary operators such as the division operator (/) operate on two operands. PL/SQL has no ternary operators.
The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates (finds the current value of) an expression by combining the values of the operands in ways specified by the operators. This always yields a single value and datatype. PL/SQL determines the datatype by examining the expression and the context in which it appears.
Operator | Operation |
**, NOT | exponentiation, logical negation |
+, - | identity, negation |
*, / | multiplication, division |
+, -, || | addition, subtraction, concatenation |
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN | comparison |
AND | conjunction |
OR | inclusion |
Operators with higher precedence are applied first. For example, both of the following expressions yield 8 because division has a higher precedence than addition:
5 + 12 / 4 12 / 4 + 5
Operators with the same precedence are applied in no particular order.
You can use parentheses to control the order of evaluation. For example, the following expression yields 7, not 11, because parentheses override the default operator precedence:
(8 + 6) / 2
In the next example, the subtraction is done before the division because the most deeply nested subexpression is always evaluated first:
100 + (20 / 5 + (7 - 3))
The following example shows that you can always use parentheses to improve readability, even when they are not needed:
(salary * 0.05) + (commission * 0.25)
NOT | TRUE | FALSE | NULL |
FALSE | TRUE | NULL | |
AND | TRUE | FALSE | NULL |
TRUE | TRUE | FALSE | NULL |
FALSE | FALSE | FALSE | FALSE |
NULL | NULL | FALSE | NULL |
OR | TRUE | FALSE | NULL |
TRUE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | NULL |
NULL | TRUE | NULL | NULL |
As the truth tables show, AND returns the value TRUE only if both its operands are true. On the other hand, OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For example, NOT TRUE returns FALSE.
NOT NULL returns NULL because nulls are indeterminate. It follows that if you apply the NOT operator to a null, the result is also indeterminate. Be careful. Nulls can cause unexpected results; see "Handling Nulls" .
NOT (valid AND done) | NOT valid AND done
If the Boolean variables valid and done have the value FALSE, the first expression yields TRUE. However, the second expression yields FALSE because NOT has a higher precedence than AND; therefore, the second expression is equivalent to
(NOT valid) AND done
In the following example, notice that when valid has the value FALSE, the whole expression yields FALSE regardless of the value of done:
valid AND done
Likewise, in the next example, when valid has the value TRUE, the whole expression yields TRUE regardless of the value of done:
valid OR done
Usually, PL/SQL stops evaluating a logical expression as soon as the result can be determined. This allows you to write expressions that might otherwise cause an error. Consider the following OR expression:
DECLARE ... on_hand INTEGER; on_order INTEGER; BEGIN .. IF (on_hand = 0) OR (on_order / on_hand < 5) THEN ... END IF; END;
When the value of on_hand is zero, the left operand yields TRUE, so PL/SQL need not evaluate the right operand. If PL/SQL were to evaluate both operands before applying the OR operator, the right operand would cause a division by zero error.
Operator | Meaning |
= | is equal to |
<>, !=, ~= | is not equal to |
< | is less than |
> | is greater than |
<= | is less than or equal to |
>= | is greater than or equal to |
IF variable = NULL THEN ...
Instead, use the following statement:
IF variable IS NULL THEN ...
The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is 'JOHNSON', the following expression yields TRUE:
ename LIKE 'J%SON'
45 BETWEEN 38 AND 44
DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD');
Furthermore, expressions of the form
value NOT IN set
yield FALSE if the set contains a null. For example, instead of deleting rows in which the ename column is not null and not 'KING', the following statement deletes no rows:
DELETE FROM emp WHERE ename NOT IN (NULL, 'KING');
'suit' || 'case'
returns the value 'suitcase'.
If both operands have datatype CHAR, the concatenation operator returns a CHAR value. Otherwise, it returns a VARCHAR2 value.
In a SQL statement, Boolean expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, Boolean expressions are the basis for conditional control. There are three kinds of Boolean expressions: arithmetic, character, and date.
number1 := 75; number2 := 70;
the following expression yields TRUE:
number1 > number2
string1 := 'Kathy'; string2 := 'Kathleen';
the following expression yields TRUE:
string1 > string2
However, there are semantic differences between the CHAR and VARCHAR2 base types that come into play when you compare character values. For more information, refer to Appendix C.
date1 := '01-JAN-91'; date2 := '31-DEC-90';
the following expression yields TRUE:
date1 > date2
count := 1; IF count = 1.0 THEN ...
It is a good idea to use parentheses when doing comparisons. For example, the following expression is illegal because 100 < tax yields TRUE or FALSE, which cannot be compared with the number 500:
100 < tax < 500 -- illegal
The debugged version follows:
(100 < tax) AND (tax < 500)
A Boolean variable is itself either true or false. So, comparisons with the Boolean values TRUE and FALSE are redundant. For example, assuming the variable done has the datatype BOOLEAN, the IF statement
IF done = TRUE THEN ...
can be simplified as follows:
IF done THEN ...
x := 5; y := NULL; ... IF x != y THEN -- yields NULL, not TRUE sequence_of_statements; -- not executed END IF;
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.
a := NULL; b := NULL; ... IF a = b THEN -- yields NULL, not TRUE sequence_of_statements; -- not executed END IF;
IF x > y THEN | IF NOT x > y THEN high := x; | high := y; ELSE | ELSE high := y; | high := x; END IF; | END IF;
The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. So, if either or both x and y are null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high. If neither x nor y is null, both IF statements assign the same value to high.
null_string := TO_VARCHAR2(''); zip_code := SUBSTR(address, 25, 0); valid := (name != '');
So, use the IS NULL operator to test for null strings, as follows:
IF my_string IS NULL THEN ...
'apple' || NULL || NULL || 'sauce'
returns the value 'applesauce'.
The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:
SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000) INTO credit_limit FROM accts WHERE acctno = my_acctno;
The function NVL returns the value of its second argument if its first argument is null. In the example below, if hire_date is null, NVL returns the value of SYSDATE. Otherwise, NVL returns the value of hire_date:
start_date := NVL(hire_date, SYSDATE);
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For instance, after the assignment
new_string := REPLACE(old_string, NULL, my_string);
the values of old_string and new_string are the same.
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, after the assignments
syllabified_name := 'Gold-i-locks'; name := REPLACE(syllabified_name, '-', NULL);
the value of name is 'Goldilocks'.
If its second and third arguments are null, REPLACE simply returns its first argument.
You can use all the functions in SQL statements except the error- reporting functions SQLCODE and SQLERRM. Also, you can use all the functions in procedural statements except the miscellaneous functions DECODE, DUMP, and VSIZE.
Note: The SQL group functions AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE are not built into PL/SQL. Nevertheless, you can use them in SQL statements (but not in procedural statements).
For descriptions of the error-reporting functions, see Chapter 10. For descriptions of the other functions, see Oracle7 Server SQL Reference.
Error | Number | Character | Conversion | Date | Misc |
SQLCODE | ABS | ASCII | CHARTOROWID | ADD_MONTHS | DECODE |
SQLERRM | ACOS | CHR | CONVERT | LAST_DAY | DUMP |
ASIN | CONCAT | HEXTORAW | MONTHS_BETWEEN | GREATEST | |
ATAN | INITCAP | RAWTOHEX | NEW_TIME | GREATEST_LB | |
ATAN2 | INSTR | ROWIDTOCHAR | NEXT_DAY | LEAST | |
CEIL | INSTRB | TO_CHAR | ROUND | LEAST_LB | |
COS | LENGTH | TO_DATE | SYSDATE | NVL | |
COSH | LENGTHB | TO_LABEL | TRUNC | UID | |
EXP | LOWER | TO_MULTI_BYTE | USER | ||
FLOOR | LPAD | TO_NUMBER | USERENV | ||
LN | LTRIM | TO_SINGLE_BYTE | VSIZE | ||
LOG | NLS_INITCAP | ||||
MOD | NLS_LOWER | ||||
POWER | NLS_UPPER | ||||
ROUND | NLSSORT | ||||
SIGN | REPLACE | ||||
SIN | RPAD | ||||
SINH | RTRIM | ||||
SQRT | SOUNDEX | ||||
TAN | SUBSTR | ||||
TANH | SUBSTRB | ||||
TRUNC | TRANSLATE | ||||
UPPER | |||||
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |