Oracle9i SQLJ Developer's Guide and Reference Release 1 (9.0.1) Part Number A90212-01 |
|
This chapter discusses basic SQLJ language features and constructs that you use in coding your application.
SQLJ statements always begin with a #sql
token and can be broken into two main categories: 1) declarations, used for creating Java classes for iterators (similar to JDBC result sets) or connection contexts (designed to help you strongly type your connections according to the sets of SQL entities being used); and 2) executable statements, used to execute embedded SQL operations.
For more advanced topics, see Chapter 7, "Advanced Language Features".
This chapter discusses the following topics.
A SQLJ declaration consists of the #sql
token followed by the declaration of a class. SQLJ declarations introduce specialized Java types into your application. There are currently two kinds of SQLJ declarations, iterator declarations and connection context declarations, defining Java classes as follows:
(SQLJ includes the predefined sqlj.runtime.DefaultContext
connection context class. If you only require one connection context class, you can use DefaultContext
, which does not require a connection context declaration.)
In any iterator or connection context declaration, you may optionally include the following clauses:
implements
clause--Specifies one or more interfaces that the generated class will implement.
with
clause--Specifies one or more initialized constants to be included in the generated class.
These are described in "Declaration IMPLEMENTS Clause" and in "Declaration WITH Clause".
SQLJ declarations are allowed in your SQLJ source code anywhere that a class definition would be allowed in standard Java. The only limitation is that you cannot have a declaration inside a method block under JDK 1.1.x. For example:
SQLJ declaration; // OK (top level scope) class Outer { SQLJ declaration; // OK (class level scope) class Inner { SQLJ declaration; // OK (nested class scope) } void func() { SQLJ declaration; // OK in JDK 1.2.x; ILLEGAL in JDK 1.1.x (method block) } }
An iterator declaration creates a class that defines a kind of iterator for receiving query data. The declaration will specify the column types of the iterator instances, which must match the column types being selected from the database table.
Basic iterator declarations use the following syntax:
#sql <modifiers> iterator iterator_classname (type declarations);
Modifiers are optional and can be any standard Java class modifiers such as public
, static
, etc. Type declarations are separated by commas.
There are two categories of iterators--named iterators and positional iterators. For named iterators, you specify column names and types; for positional iterators, you specify only types.
The following is an example of a named iterator declaration:
#sql public iterator EmpIter (String ename, double sal);
This statement results in the SQLJ translator creating a public EmpIter
class with a String
attribute ename
and a double
attribute sal
. You can use this iterator to select data from a database table with corresponding employee name and salary columns of matching names (ENAME
and SAL
) and datatypes (CHAR
and NUMBER
).
Declaring EmpIter
as a positional iterator, instead of a named iterator, would be done as follows:
#sql public iterator EmpIter (String, double);
For more information about iterators, see "Multi-Row Query Results--SQLJ Iterators".
A connection context declaration creates a connection context class, whose instances are typically used for database connections that use a particular set of SQL entities.
Basic connection context declarations use the following syntax:
#sql <modifiers> context context_classname;
As for iterator declarations, modifiers are optional and can be any standard Java class modifiers. The following is an example:
#sql public context MyContext;
As a result of this statement, the SQLJ translator creates a public MyContext
class. In your SQLJ code you can use instances of this class to create database connections to schemas that include a desired set of entities, such as tables, views, and stored procedures. Different instances of MyContext
might be used to connect to different schemas, but each schema might be expected, for example, to include an EMP
table, a DEPT
table, and a TRANSFER_EMPLOYEE
stored procedure.
Declared connection context classes are an advanced topic and are not necessary for basic SQLJ applications that use only one interrelated set of SQL entities. In basic scenarios, you can use multiple connections by creating multiple instances of the sqlj.runtime.ref.DefaultContext
class, which does not require any connection context declarations.
See "Connection Considerations" for an overview of connections and connection contexts.
For information about creating additional connection contexts, see "Connection Contexts".
When you declare any iterator class or connection context class, you can specify one or more interfaces to be implemented by the generated class.
Use the following syntax for an iterator class:
#sql <modifiers> iterator iterator_classname implements intfc1,..., intfcN (type declarations);
The portion implements
intfc1,..., intfcN
is known as the implements
clause. Note that in an iterator declaration, the implements
clause precedes the iterator type declarations.
Here is the syntax for a connection context declaration:
#sql <modifiers> context context_classname implements intfc1,..., intfcN;
The implements
clause is potentially useful in either an iterator declaration or a connection context declaration, but is more likely to be useful in iterator declarations--particularly in implementing the sqlj.runtime.Scrollable
or sqlj.runtime.ForUpdate
interface. Scrollable iterators are supported in Oracle SQLJ (see "Scrollable Iterators"); positioned updates or deletes are not currently supported.
For more information about the implements
clause, see "Use of the IMPLEMENTS Clause in Iterator Declarations" and "Use of the IMPLEMENTS Clause in Connection Context Declarations".
The following example uses an implements
clause in declaring a named iterator class (presume you have created a package, mypackage
, that includes an iterator interface, MyIterIntfc
).
#sql public iterator MyIter implements mypackage.MyIterIntfc (String ename, int empno);
The declared class MyIter
will implement the mypackage.MyIterIntfc
interface.
This next example declares a connection context class that implements an interface named MyConnCtxtIntfc
(presume it, too, is in the package mypackage
).
#sql public context MyContext implements mypackage.MyConnCtxtIntfc;
In declaring a connection context class or iterator class, you can use a with
clause to specify and initialize one or more constants to be included in the definition of the generated class. Most of this usage is standard, although Oracle adds one kind of extended functionality for iterator declarations.
In using a with
clause, the constants that are produced are always public static final
. Use the following syntax for an iterator class:
#sql <modifiers> iterator iterator_classname with (var1=value1,..., varN=valueN) (type declarations);
The portion with
(
var1=value1,..., varN=valueN
) is the with
clause. Note that in an iterator declaration, the with
clause precedes the iterator type declarations.
Where there is both a with
clause and an implements
clause, the implements
clause must come first. Note that parentheses are used to enclose with
lists, but not implements
lists.
Here is the syntax for a connection context declaration using a with
clause:
#sql <modifiers> context context_classname with (var1=value1,..., varN=valueN);
And here is an example:
#sql public context MyContext with (typeMap="MyPack.MyClass");
The declared class MyContext
will define the attribute typeMap
that will be public static final
of the type String
and initialized to the value "MyPack.MyClass". This value is the fully qualified class name of a ListResourceBundle
implementation that provides the mapping between SQL and Java types for statements executed on instances of the MyContext
class.
Here is another example (see the note about sensitivity
below):
#sql public iterator MyAsensitiveIter with (sensitivity=ASENSITIVE) (String ename, int empno);
This declaration sets the cursor sensitivity
to ASENSITIVE
for a named iterator class.
The following example uses both an implements
clause and a with
clause (see the note about holdability
immediately below).
#sql public iterator MyScrollableIterator implements sqlj.runtime.Scrollable with (holdability=true) (String ename, int empno);
The implements
clause must precede the with
clause.
This declaration implements the interface sqlj.runtime.Scrollable
and enables the cursor holdability
for a named iterator class (but holdability
is not currently meaningful to Oracle9i).
The following standard constants on iterator declarations are not supported in Oracle SQLJ. They mostly involve cursor states and can take only particular values, as follows:
holdability
(true
/false
)
updateColumns
(a String
literal containing a comma-separated list of column names)
An iterator declaration having a with
clause that specifies updateColumns
must also have an implements
clause that specifies the sqlj.runtime.ForUpdate
interface.
Oracle SQLJ supports the following standard constants on connection context declarations.
sensitivity
(SENSITIVE
/ASENSITIVE
/INSENSITIVE
, to define the sensitivity of a scrollable iterator)
returnability
(true
/false
, to define whether an iterator can be returned from a Java stored procedure or function)
typeMap
(
a String
literal defining the name of a type map properties resource)
dataSource
(a String
literal defining the name under which a data source is looked up in the InitialContext
See "Data Source Support" for information about SQLJ support for data sources)
The following standard constants on connection context declarations are not currently supported in Oracle SQLJ:
path
(
a String
literal defining the name of a path to be prepended for resolution of Java stored procedures and functions)
transformGroup
(
a String
literal defining the name of a SQL transformation group that may be applied to SQL types)
A predefined set of standard SQLJ constants can be defined in a
Note:
with
clause; however, not all of these constants are meaningful to Oracle9i or to the Oracle SQLJ runtime. Attempts to define constants other than the standard constants (as in the example above) is legal with Oracle9i, but may not be portable to other SQLJ implementations and will generate a warning if you have the -warn=portable
flag enabled. For information about this flag, see "Translator Warnings (-warn)".
In addition to standard with
clause usage in a connection context declaration to associate a type map with the connection context class, Oracle allows you to use a with
clause to associate a type map with the iterator class in an iterator declaration. Here is an example:
#sql iterator MyIterator with (typeMap="MyTypeMap") (Person pers, Address addr);
If you use Oracle-specific code generation (through the translator setting -codegen=oracle
), and you use type maps in your application, then your iterator and connection context declarations must use the same type map(s). See "Coding Considerations and Limitations with Oracle-Specific Code Generation" for more information.
A SQLJ executable statement consists of the #sql
token followed by a SQLJ clause, which uses syntax that follows a specified standard for embedding executable SQL statements in Java code. The embedded SQL operation of a SQLJ executable statement can be any SQL operation supported by your JDBC driver (such as DML, DDL, and transaction control).
A SQLJ executable statement must follow these rules:
{...}.
Notes:
SELECT
, UPDATE
, INSERT
, and DELETE
) can be parsed and checked for syntax and semantics by the SQLJ translator using a database connection. DDL operations (such as CREATE...
, or ALTER...
), transaction-control operations (such as COMMIT
and ROLLBACK
), or any other kinds of SQL operations cannot.
A SQLJ clause is the executable part of a statement (everything to the right of the #sql
token). This consists of embedded SQL inside curly braces, preceded by a Java result expression if appropriate (such as result
below):
#sql { SQL operation }; // For a statement with no output, like INSERT ... #sql result = { SQL operation }; // For a statement with output, like SELECT
A clause without a result expression, such as in the first example, is known as a statement clause. A clause that does have a result expression, such as in the second example, is known as an assignment clause.
A result expression can be anything from a simple variable that takes a stored-function return value, to an iterator that takes several columns of data from a multi-row SELECT
(where the iterator can be an instance of an iterator class or subclass).
A SQL operation in a SQLJ statement can use standard SQL syntax only, or can use a clause with syntax specific to SQLJ (see Table 3-1 and Table 3-2 below).
For reference, Table 3-1 lists supported SQLJ statement clauses, and Table 3-2 lists supported SQLJ assignment clauses. Details of how to use the various kinds of clauses are discussed elsewhere, as indicated. The two entries in Table 3-1 are general categories for statement clauses that use standard SQL syntax or Oracle PL/SQL syntax, as opposed to SQLJ-specific syntax.
Category | Functionality | More Information |
---|---|---|
|
Select data into Java host expressions. |
|
|
Fetch data from a positional iterator. |
|
|
Commit changes to the data. |
|
|
Cancel changes to the data. |
|
|
Use advanced transaction control for access mode and isolation level. |
|
procedure clause |
Call a stored procedure. |
|
assignment clause |
Assign values to Java host expressions. |
|
SQL clause |
Use standard SQL syntax and functionality: |
Oracle9i SQL Reference |
PL/SQL block |
Use |
"PL/SQL Blocks in Executable Statements" PL/SQL User's Guide and Reference |
Category | Functionality | More Information |
---|---|---|
query clause |
Select data into a SQLJ iterator. |
|
function clause |
Call a stored function. |
|
iterator conversion clause |
Convert a JDBC result set to a SQLJ iterator. |
"Converting from Result Sets to Named or Positional Iterators" |
If you have defined multiple database connections and want to specify a particular connection context instance for an executable statement, use the following syntax:
#sql [conn_context_instance] { SQL operation };
"Connection Considerations" discusses connection context instances.
If you have defined one or more execution context instances (of the class sqlj.runtime.ExecutionContext
) and want to specify one of them for use with an executable statement, use the following syntax (similar to that for connection context instances):
#sql [exec_context_instance] { SQL operation };
You can use an execution context instance to provide status or control of the SQL operation of a SQLJ executable statement. (This is an advanced topic.) For example, you can use execution context instances in multithreading situations where multiple operations are occurring on the same connection. See "Execution Contexts" for information.
You can also specify both a connection context instance and an execution context instance:
#sql [conn_context_instance, exec_context_instance] { SQL operation };
Examples of elementary SQLJ executable statements appear below. More complicated statements are discussed later in this chapter.
The following example demonstrates a basic INSERT
. The statement clause does not require any syntax specific to SQLJ.
Consider a subset of the standard EMP
table:
CREATE TABLE EMP ( ENAME VARCHAR2(10), SAL NUMBER(7,2) );
Use the following SQLJ executable statement (that uses only standard SQL syntax) to insert Joe as a new employee into the EMP
table, specifying his name and salary.
#sql { INSERT INTO emp (ename, sal) VALUES ('Joe', 43000) };
The following examples use ctx
as a connection context instance (an instance of either the default sqlj.runtime.ref.DefaultContext
or a class that you have previously declared in a connection context declaration) and execctx
as an execution context instance:
#sql [ctx] { INSERT INTO emp (ename, sal) VALUES ('Joe', 43000) }; #sql [execctx] { INSERT INTO emp (ename, sal) VALUES ('Joe', 43000) }; #sql [ctx, execctx] { INSERT INTO emp (ename, sal) VALUES ('Joe', 43000) };
This example demonstrates a simple method using SQLJ code, demonstrating how SQLJ statements interrelate with and are interspersed with Java statements. The SQLJ statement uses standard INSERT INTO
table
VALUES
syntax supported by Oracle SQL. The statement also uses Java host expressions, marked by colons (:), to define the values. (Host expressions are used to pass data between your Java code and SQL instructions. They are discussed in "Java Host Expressions, Context Expressions, and Result Expressions".)
public static void writeSalesData (int[] itemNums, String[] itemNames) throws SQLException { for (int i =0; i < itemNums.length; i++) #sql { INSERT INTO sales VALUES(:(itemNums[i]), :(itemNames[i]), SYSDATE) }; }
Notes:
|
PL/SQL blocks can be used within the curly braces of a SQLJ executable statement just as SQL operations can, as in the following example:
#sql { DECLARE n NUMBER; BEGIN n := 1; WHILE n <= 100 LOOP INSERT INTO emp (empno) VALUES(2000 + n); n := n + 1; END LOOP; END };
This example goes through a loop that inserts new employees in the EMP
table, creating employee numbers 2001-2100. (It presumes data other than the employee number will be filled in later.)
Simple PL/SQL blocks can also be coded in a single line:
#sql { <DECLARE ...> BEGIN ... END };
Using PL/SQL anonymous blocks within SQLJ statements is one way to use dynamic SQL in your application. (See "Dynamic SQL--DynamicDemo.sqlj" for a sample.) You can also use dynamic SQL directly through Oracle SQLJ extensions (see "Support for Dynamic SQL"), or through JDBC code within a SQLJ application (see "SQLJ and JDBC Interoperability").
This section discusses three categories of Java expressions used in SQLJ code: host expressions, context expressions, and result expressions. Host expressions are the most frequently used and merit the most discussion. (Another category of expressions, called meta bind expressions, are used specifically for dynamic SQL operations and use syntax similar to that of host expressions. See "Support for Dynamic SQL".)
SQLJ uses Java host expressions to pass arguments between your Java code and your SQL operations. This is how you pass information between Java and SQL. Host expressions are interspersed within the embedded SQL operations in SQLJ source code.
The most basic kind of host expression, consisting of only a Java identifier, is referred to as a host variable.
A context expression specifies a connection context instance or execution context instance to be used for a SQLJ statement.
A result expression specifies an output variable for query results or a function return.
(Result expressions and the specification of connection context instances and execution context instances were first introduced in "Overview of SQLJ Executable Statements".)
Any valid Java expression can be used as a host expression. In the simplest case, which is typical, the expression consists of just a single Java variable. Other kinds of host expressions include the following:
a ? b : c
)
Java identifiers used as host variables or in host expressions can represent any of the following:
myclass.myfield
)
Local variables used in host expressions can be declared anywhere that other Java variables can be declared. Fields can be inherited from a superclass.
Java variables that are legal in the Java scope where the SQLJ executable statement appears can be used in a host expression in a SQL statement, presuming its type is convertible to or from a SQL datatype.
Host expressions can be input, output, or input-output.
See "Supported Types for Host Expressions" for information about data conversion between Java and SQL during input and output operations.
A host expression is preceded by a colon (":"). If the desired mode of the host expression (input, output, or input-output) is not the default, then the colon must be followed (before the host expression itself) by IN
, OUT
, or INOUT
, as appropriate. These are referred to as mode specifiers. The default is OUT
if the host expression is part of an INTO-list or is the assignment expression in a SET
statement. Otherwise, the default is IN
. (When using the default, you can still include the mode specifier if desired.)
Any OUT
or INOUT
host expression must be assignable (an l-value, meaning something that can logically appear on the left side of an equals sign).
The SQL code that surrounds a host expression can use any vendor-specific SQL syntax; therefore, no assumptions can be made about the syntax when parsing the SQL operations and determining the host expressions. To avoid any possible ambiguity, any host expression that is not a simple host variable (in other words, that is more complex than a non-dotted Java identifier) must be enclosed in parentheses.
To summarize the basic syntax:
:hostvar
:INOUT hostvar
The white space is required to distinguish between the mode specifier and the variable name.
:IN(hostvar1+hostvar2) :(hostvar3*hostvar4) :(index--)
White space is not required after the mode specifier in the above example, because the parenthesis is a suitable separator, but it is allowed.
An outer set of parentheses is needed even if the expression already starts with a begin-parenthesis, as in the following examples:
:((x+y).z)
:(((y)x).myOutput())
or in the Java namespace:
IN
, OUT
, and INOUT
syntax used for host variables and expressions is not case sensitive; these tokens can be uppercase, lowercase, or mixed.
IN
, OUT
, and INOUT
syntax of SQLJ host expressions with similar IN
, OUT
, and IN OUT
syntax used in PL/SQL declarations to specify the mode of parameters passed to PL/SQL stored functions and procedures.
OUT
or INOUT
variables, as applicable):
-warn=portability
flag is set. SQLJ runtime behavior in this situation is vendor-specific. The Oracle SQLJ runtime uses value semantics (as opposed to reference semantics) for all occurrences of the host variable. For information about the -warn=portability
flag, see "Translator Warnings (-warn)".
For examples of Oracle SQLJ runtime evaluation of host expressions, see "Examples of Evaluation of Java Expressions at Runtime".
The following examples will help clarify the preceding syntax discussion. (Some of these examples use SELECT INTO
statements, which are described in "Single-Row Query Results--SELECT INTO Statements".)
WHERE
clause, and one to contain new data to be sent to the database.
Presume you have a database employee table EMP
with an ENAME
column for employee names and a SAL
column for employee salaries.
The relevant Java code that defines the host variables is also shown in the example.
String empname = "SMITH"; double salary = 25000.0; ... #sql { UPDATE emp SET sal = :salary WHERE ename = :empname };
IN
is the default, but you can state it explicitly as well:
#sql { UPDATE emp SET sal = :IN salary WHERE ename = :IN empname };
As you can see, ":" can immediately precede the variable when not using the IN
token, but ":IN" must be followed by white space before the host variable.
SELECT INTO
statement, where you want to find out the name of employee number 28959.
String empname; ... #sql { SELECT ename INTO :empname FROM emp WHERE empno = 28959 };
OUT
is the default for an INTO-list, but you can state it explicitly as well:
#sql { SELECT ename INTO :OUT empname FROM emp WHERE empno = 28959 };
This looks in the EMPNO
column of the EMP
table for employee number 28959, selects the name in the ename
column of that row, and outputs it to the empname
output host variable, which is a Java string.
balance
and minPmtRatio
are multiplied, and the result is used to update the minPayment
column of the creditacct
table for account number 537845.
float balance = 12500.0; float minPmtRatio = 0.05; ... #sql { UPDATE creditacct SET minPayment = :(balance * minPmtRatio) WHERE acctnum = 537845 };
Or, to use the IN
token:
#sql { UPDATE creditacct SET minPayment = :IN (balance * minPmtRatio) WHERE acctnum = 537845 };
getNewSal()
to update the SAL
column in the EMP
table for the employee (in the ENAME
column) who is specified by the Java empname
variable. Java code initializing the host variables is also shown.
String empname = "SMITH"; double raise = 0.1; ... #sql {UPDATE emp SET sal = :(getNewSal(raise, empname)) WHERE ename = :empname};
A context expression is an input expression that specifies the name of a connection context instance or an execution context instance to be used in a SQLJ executable statement. Any legal Java expression that yields such a name can be used.
A result expression is an output expression used for query results or a function return. It can be any legal Java expression that is assignable, meaning that it can logically appear on the left side of an equals sign (this is sometimes referred to as an l-value).
The following examples can be used for either result expressions or context expressions:
Result expressions and context expressions appear lexically in the SQLJ space, unlike host expressions, which appear lexically in the SQL space (inside the curly brackets of a SQLJ executable statement). Therefore, a result expression or context expression must not be preceded by a colon.
This section discusses the evaluation of Java host expressions, connection context expressions, execution context expressions, and result expressions when your application executes.
Here is a simplified representation of a SQLJ executable statement that uses all these kinds of expressions:
#sql [connctxt_exp, execctxt_exp] result_exp = { SQL with host expression };
Java expressions can be used as any of the following:
For standard SQLJ generated code, evaluation of Java expressions does have side effects in a Java program because they are evaluated by Java, not by the SQL engine. Furthermore, the order of evaluation of these expressions can be critical if any of the expressions have side effects.
The following is a summary (for standard generated code) of the overall order of evaluation, execution, and assignment of Java expressions for each statement that executes during runtime.
Note:
The following discussion does not apply to Oracle-specific generated code, produced through the SQLJ translator |
Each host expression is evaluated once and only once.
IN
and INOUT
parameters are passed to SQL, and the SQL operation is executed.
OUT
and INOUT
host expressions--are assigned output in order from left to right as they appear in the SQL operation.
Each output host expression is assigned once and only once.
"Examples of Evaluation of Java Expressions at Runtime", has a series of examples that clarifies this sequence and discusses a number of special considerations.
Once the expressions in a statement have been evaluated, input and input-output host expressions are passed to SQL and then the SQL operation is executed. After execution of the SQL operation, assignments are made to Java output host expressions, input-output host expressions, and result expressions as follows:
OUT
and INOUT
host expressions are assigned output in order from left to right.
Note that during runtime all host expressions are treated as distinct values, even if they share the same name or reference the same object. The execution of each SQL operation is treated as if invoking a remote method, and each host expression is taken as a distinct parameter. Each input or input-output parameter is evaluated and passed as it is first encountered, before any output assignments are made for that statement, and each output parameter is also taken as distinct and is assigned exactly once.
It is also important to remember that each host expression is evaluated only once. An INOUT
expression is evaluated when it is first encountered. When the output assignment is made, the expression itself is not re-evaluated, nor are any side-effects repeated.
In discussing the evaluation order of host expressions, several points must be highlighted, as covered in "Examples of Evaluation of Java Expressions at Runtime" below.
This section discusses some of the subtleties of how Java expressions are evaluated when your application executes, providing examples. (Some of these examples use SELECT INTO
statements, which are described in "Single-Row Query Results--SELECT INTO Statements"; some use assignment statements, which are described in "Assignment Statements (SET)"; and some use stored procedure and function calls, which are described in "Stored Procedure and Function Calls".)
When a Java expression contains a Java postfix increment or decrement operator, the incrementation or decrementation occurs after the expression has been evaluated. Similarly, when a Java expression contains a Java prefix increment or decrement operator, the incrementation or decrementation occurs before the expression is evaluated.
This is equivalent to how these operators are handled in standard Java code.
Consider the following examples.
Example 1: postfix operator
int indx = 1; ... #sql { ... :OUT (array[indx]) ... :IN (indx++) ... };
This example is evaluated as follows:
#sql { ... :OUT (array[1]) ... :IN (1) ... };
The variable indx
is incremented to 2 and will have that value the next time it is encountered, but not until after :IN (indx++)
has been evaluated.
Example 2: postfix operators
int indx = 1; ... #sql { ... :OUT (array[indx++]) ... :IN (indx++) ... };
This example is evaluated as follows:
#sql { ... :OUT (array[1]) ... :IN (2) ... };
The variable indx
is incremented to 2 after the first expression is evaluated, but before the second expression is evaluated. It is incremented to 3 after the second expression is evaluated and will have that value the next time it is encountered.
Example 3: prefix and postfix operators
int indx = 1; ... #sql { ... :OUT (array[++indx]) ... :IN (indx++) ... };
This example is evaluated as follows:
#sql { ... :OUT (array[2]) ... :IN (2) ... };
The variable indx
is incremented to 2 before the first expression is evaluated. It is incremented to 3 after the second expression is evaluated and will have that value the next time it is encountered.
Example 4: postfix operator
int grade = 0; int count1 = 0; ... #sql { SELECT count INTO :count1 FROM staff WHERE grade = :(grade++) OR grade = :grade };
This example is evaluated as follows:
#sql { SELECT count INTO :count1 FROM staff WHERE grade = 0 OR grade = 1 };
The variable grade
is incremented to 1 after :(grade++)
is evaluated and has that value when :grade
is evaluated.
Example 5: postfix operators
int count = 1; int[] x = new int[10]; int[] y = new int[10]; int[] z = new int[10]; ... #sql { SET :(z[count++]) = :(x[count++]) + :(y[count++]) };
This example is evaluated as follows:
#sql { SET :(z[1]) = :(x[2]) + :(y[3]) };
The variable count
is incremented to 2 after the first expression is evaluated, but before the second expression is evaluated; it is incremented to 3 after the second expression is evaluated, but before the third expression is evaluated; it is incremented to 4 after the third expression is evaluated and will have that value the next time it is encountered.
Example 6: postfix operator
int[] arr = {3, 4, 5}; int i = 0; ... #sql { BEGIN :OUT (arr[i++]) := :(arr[i]); END };
This example is evaluated as follows:
#sql { BEGIN :OUT (a[0]) := :(a[1]); END };
The variable i
is incremented to 1 after the first expression is evaluated, but before the second expression is evaluated; therefore, output will be assigned to arr[0]
. Specifically, arr[0]
will be assigned the value of arr[1]
, which is 4. After execution of this statement, array arr
will have the values {4, 4, 5}
.
Host expressions are evaluated from left to right. Whether an expression is IN
, INOUT
, or OUT
makes no difference in when it is evaluated; all that matters is its position in the left-to-right order.
Example 7: IN
versus INOUT
versus OUT
int[5] arry; int n = 0; ... #sql { SET :OUT (arry[n]) = :(++n) };
This example is evaluated as follows:
#sql { SET :OUT (arry[0]) = 1 };
One might expect input expressions to be evaluated before output expressions, but that is not the case. The expression :OUT (arry[n])
is evaluated first because it is the left-most expression. Then n
is incremented prior to evaluation of ++n
, because it is being operated on by a prefix operator. Then ++n
is evaluated as 1. The result will be assigned to arry[0]
, not arry[1]
, because 0 was the value of n
when it was originally encountered.
Host expressions in a PL/SQL block are all evaluated in one sequence, before any have been executed.
Example 8: evaluation of expressions in a PL/SQL block
int x=3; int z=5; ... #sql { BEGIN :OUT x := 10; :OUT z := :x; END }; System.out.println("x=" + x + ", z=" + z);
This example is evaluated as follows:
#sql { BEGIN :OUT x := 10; :OUT z := 3; END };
Therefore, it would print "x=10, z=3".
All expressions in a PL/SQL block are evaluated before any are executed. In this example, the host expressions in the second statement, :OUT z
and :x
, are evaluated before the first statement is executed. In particular, the second statement is evaluated while x
still has its original value of 3, before it has been assigned the value 10.
Example 9: evaluation of expressions in a PL/SQL block (with postfix)
int x=1, y=4, z=3; ... #sql { BEGIN :OUT x := :(y++) + 1; :OUT z := :x; END };
This example is evaluated as follows:
#sql { BEGIN :OUT x := 4 + 1; :OUT z := 1; END };
The postfix increment operator is executed after :(y++)
is evaluated, so the expression is evaluated as 4 (the initial value of y
). The second statement, :OUT z := :x
, is evaluated before the first statement is executed, so x
still has its initialized value of 1. After execution of this block, x
will have the value 5 and z
will have the value 1.
Example 10: statements in one block versus separate SQLJ executable statements
This example demonstrates the difference between two statements appearing in a PL/SQL block in one SQLJ executable statement, and the same statements appearing in separate (consecutive) SQLJ executable statements.
First, consider the following, where two statements are in a PL/SQL block.
int y=1; ... #sql { BEGIN :OUT y := :y + 1; :OUT x := :y + 1; END };
This example is evaluated as follows:
#sql { BEGIN :OUT y := 1 + 1; :OUT x := 1 + 1; END };
The :y
in the second statement is evaluated before either statement is executed, so y
has not yet received its output from the first statement. After execution of this block, both x
and y
have the value 2.
Now, consider the situation where the same two statements are in PL/SQL blocks in separate SQLJ executable statements.
int y=1; #sql { BEGIN :OUT y := :y + 1; END }; #sql { BEGIN :OUT x := :y + 1; END };
The first statement is evaluated as follows:
#sql { BEGIN :OUT y := 1 + 1; END };
Then it is executed and y
is assigned the value 2.
After execution of the first statement, the second statement is evaluated as follows:
#sql { BEGIN :OUT x := 2 + 1; END };
This time, as opposed to the PL/SQL block example above, y
has already received the value 2 from execution of the previous statement; therefore, x is assigned the value 3 after execution of the second statement.
Each host expression is evaluated once, and only once, regardless of program flow and logic.
Example 11: evaluation of host expression in a loop
int count = 0; ... #sql { DECLARE n NUMBER BEGIN n := 1; WHILE n <= 100 LOOP :IN (count++); n := n + 1; END LOOP; END };
The Java variable count
will have the value 0 when it is passed to SQL (because it is operated on by a postfix operator, as opposed to a prefix operator), then will be incremented to 1 and will hold that value throughout execution of this PL/SQL block. It is evaluated only once as the SQLJ executable statement is parsed and then is replaced by the value 1 prior to SQL execution.
Example 12: evaluation of host expressions in conditional blocks
This example demonstrates how each expression is always evaluated, regardless of program flow. As the block is executed, only one branch of the IF...THEN...ELSE
construct can be executed. Before the block is executed, however, all expressions in the block are evaluated, in the order that the statements appear.
int x; ... (operations on x) ... #sql { DECLARE n NUMBER BEGIN n := :x; IF n < 10 THEN n := :(x++); ELSE n := :x * :x; END LOOP; END };
Say the operations performed on x
resulted in x
having a value of 15. When the PL/SQL block is executed, the ELSE
branch will be executed and the IF
branch will not; however, all expressions in the PL/SQL block are evaluated before execution, regardless of program logic or flow. So x++
is evaluated, then x
is incremented, then each x
is evaluated in the (x * x)
expression. The IF...THEN...ELSE
block is, therefore, evaluated as follows:
IF n < 10 THEN n := 15; ELSE n := :16 * :16; END LOOP;
After execution of this block, given an initial value of 15 for x
, n
will have the value 256.
Remember that OUT
and INOUT
host expressions are assigned in order from left to right, and then the result expression, if there is one, is assigned last. If the same variable is assigned more than once, then it will be overwritten according to this order, with the last assignment taking precedence.
Note: Some of these examples use stored procedure and function calls, whose syntax is explained in "Stored Procedure and Function Calls". |
Example 13: multiple output host expressions referencing the same variable
#sql { CALL foo(:OUT x, :OUT x) };
If foo()
outputs the values 2 and 3, respectively, then x
will have the value 3 after the SQLJ executable statement has finished executing. The right-hand assignment will be performed last, thereby taking precedence.
Example 14: multiple output host expressions referencing the same object
MyClass x = new MyClass(); MyClass y = x; ... #sql { ... :OUT (x.field):=1 ... :OUT (y.field):=2 ... };
After execution of the SQLJ executable statement, x.field
will have a value of 2, not 1, because x
is the same object as y
, and field
was assigned the value of 2 after it was assigned the value of 1.
Example 15: results assignment taking precedence over host expression assignment
This example demonstrates the difference between having the output results of a function assigned to a result expression and having the results assigned to an OUT
host expression.
Consider the following function, with an input invar
, an output outvar
, and a return value:
CREATE FUNCTION fn(invar NUMBER, outvar OUT NUMBER) RETURN NUMBER AS BEGIN outvar := invar + invar; return (invar * invar); END fn;
Now consider an example where the output of the function is assigned to a result expression:
int x = 3; #sql x = { VALUES(fn(:x, :OUT x)) };
The function will take 3 as the input, will calculate 6 as the output, and will return 9. After execution, the :OUT x
will be assigned first, giving x
a value of 6. But finally the result expression is assigned, giving x
the return value of 9 and overwriting the value of 6 previously assigned to x. So x
will have the value 9 the next time it is encountered.
Now consider an example where the output of the function is assigned to an OUT
host variable instead of to a result expression:
int x = 3; #sql { BEGIN :OUT x := fn(:x, :OUT x); END };
In this case, there is no result expression and the OUT
variables are simply assigned left to right. After execution, the first :OUT x
, on the left side of the equation, is assigned first, giving x
the function return value of 9. Proceeding left to right, however, the second :OUT x
, on the right side of the equation, is assigned last, giving x
the output value of 6 and overwriting the value of 9 previously assigned to x
. So x
will have the value 6 the next time it is encountered.
Do not use "in", "out", and "inout" as identifiers in host expressions unless they are enclosed in parentheses. Otherwise, they might be mistaken for mode specifiers. This is case-insensitive.
For example, you could use an input host variable called "in" as follows:
:(in)
or:
:IN(in)
When only a single row of data is being returned, SQLJ allows you to assign selected items directly to Java host expressions inside SQL syntax. This is done using the SELECT INTO
statement.
The syntax for a SELECT INTO
statement is as follows:
#sql { SELECT expression1,..., expressionN INTO :host_exp1,..., :host_expN FROM table <optional clauses> };
where:
expression1
through expressionN
are expressions specifying what is to be selected from the database. These can be any expressions valid for any SELECT
statement. This list of expressions is referred to as the SELECT-list.
In a simple case, these would be names of columns from a database table.
It is also legal to include a host expression in the SELECT-list (see the examples below).
host_exp1
through host_expN
are target host expressions, such as variables or array elements. This list of host expressions is referred to as the INTO-list.
table
is the name of the database table, view, or snapshot from which you are selecting the data.
optional clauses
are any additional clauses you want to include that are valid in a SELECT
statement, such as a WHERE
clause.
A SELECT INTO
statement must return one, and only one, row of data, otherwise an error will be generated at runtime.
The default is OUT
for a host expression in an INTO-list, but you can optionally state this explicitly:
#sql { SELECT column_name1, column_name2 INTO :OUT host_exp1, :OUT host_exp2 FROM table WHERE condition };
Trying to use an IN
or INOUT
token in the INTO-list will result in an error at translation time.
The examples below use a subset of the standard EMP
table:
CREATE TABLE EMP ( EMPNO NUMBER(4), ENAME VARCHAR2(10), HIREDATE DATE );
The first example is a SELECT INTO
statement with a single host expression in the INTO-list:
String empname; #sql { SELECT ename INTO :enpname FROM emp WHERE empno=28959 };
The second example is a SELECT INTO
statement with multiple host expressions in the INTO-list:
String empname; Date hdate; #sql { SELECT ename, hiredate INTO :empname, :hdate FROM emp WHERE empno=28959 };
It is legal to use Java host expressions in the SELECT-list as well as in the INTO-list.
For example, you can select directly from one host expression into another (though this is of limited usefulness):
... #sql { SELECT :name1 INTO :name2 FROM emp WHERE empno=28959 }; ...
More realistically, you might want to perform an operation or concatenation on the data selected, as in the following examples (assume Java variables were previously declared and assigned, as necessary):
... #sql { SELECT sal + :raise INTO :newsal FROM emp WHERE empno=28959 }; ... ... #sql { SELECT :(firstname + " ") || emp_last_name INTO :name FROM myemp WHERE empno=28959 }; ...
In the second example, presume MYEMP
is a table much like the standard EMP
table but with an EMP_LAST_NAME
column instead of an ENAME
column. In the SELECT
statement, firstname
is prepended to " " (a single space), using a Java host expression and Java string concatenation (the +
operator). This result is then passed to the SQL engine, which uses SQL string concatenation (the ||
operator) to append the last name.
Remember that SELECT INTO
statements are intended for queries that return exactly one row of data only.
A SELECT INTO
query that finds zero rows or multiple rows will result in an exception, as follows:
SELECT INTO
finding now rows will return an exception with a SQL state of 2000, representing a "no data" condition.
SELECT INTO
finding multiple rows will return an exception with a SQL state of 21000, representing a cardinality violation.
These exceptions are listed under "Runtime Messages". You can retrieve the SQL state through the getSQLState()
method of the java.sql.SQLException
class, as described in "Retrieving SQL States and Error Codes".
This is vendor-independent behavior that is specified in the SQLJ ISO standard. There is no vendor-specific error code in these cases (the error code equals 0).
A large number of SQL operations are multi-row queries. Processing multi-row query-results in SQLJ requires a SQLJ iterator, which is generally a strongly typed version of a JDBC result set and is associated with the underlying database cursor. SQLJ iterators are used first and foremost to take query results from a SELECT
statement.
Additionally, Oracle SQLJ offers extensions that allow you to use SQLJ iterators and result sets in the following ways:
OUT
host variables in executable SQL statements
SELECT INTO
statement
For information about usage as stored function returns, see "Using Iterators and Result Sets as Stored Function Returns", after stored procedures and stored functions have been discussed. The other uses listed above are documented later in this section.
For information about advanced iterator topics, see "Iterator Class Implementation and Advanced Functionality". This section discusses how iterator classes are implemented and what advanced functionality is available, such as interoperability with JDBC result sets and subclassing of iterators.
Using a SQLJ iterator declaration, as described in "Overview of SQLJ Declarations", results in a strongly typed iterator. This is the typical usage for iterators, and takes particular advantage of SQLJ semantics-checking features during translation.
It is also possible, and at times advantageous, to use weakly typed iterators. There are generic classes you can instantiate to use a weakly typed iterator.
This section primarily introduces features of strongly typed iterators, but concludes with a brief introduction to weakly typed iterators.
Before using a strongly typed iterator object, you must declare an iterator class. An iterator declaration specifies a Java class that SQLJ constructs for you, where the class attributes define the types (and, optionally, the names) of the columns of data in the iterator.
A SQLJ iterator object is an instantiation of such a specifically declared iterator class, with a fixed number of columns of predefined type. This is as opposed to a JDBC result set object, which is a standard java.sql.ResultSet
instance and can, in principle, contain any number of columns of any type.
When you declare an iterator, you specify either just the datatypes of the selected columns, or both the datatypes and the names of the selected columns:
The datatypes (and names, if applicable) that you declare determine how query results will be stored in iterator objects you instantiate from that class. SQL data retrieved into an iterator object are converted to the Java types specified in the iterator declaration.
When you query to populate a named iterator object, the names and datatypes of the SELECT-fields must match the names and types of the iterator columns (case-insensitive). The order of the SELECT-fields is irrelevant--all that matters is that each SELECT-field name matches an iterator column name. In the simplest case, the database column names directly match the iterator column names. For example, data from an ENAME
column in a database table can be selected and put into an iterator ename
column. Alternatively, you can use an alias to map a database column name to an iterator column name if the names differ. Furthermore, in a more complicated query, you can perform an operation between two columns and alias the result to match the corresponding iterator column name. (These last two cases are discussed in "Instantiating and Populating Named Iterators".)
Because SQLJ iterators are strongly typed, they offer the benefit of Java type-checking during the SQLJ semantics-checking phase.
As an example, consider the following table:
CREATE TABLE EMPSAL ( EMPNO NUMBER(4), ENAME VARCHAR2(10), OLDSAL NUMBER(10), RAISE NUMBER(10) );
Given this table, you can declare and use a named iterator as follows.
Declaration:
#sql iterator SalNamedIter (int empno, String ename, float raise);
Executable code:
class MyClass { void func() throws SQLException { ... SalNamedIter niter; #sql niter = { SELECT ename, empno, raise FROM empsal }; ... process niter ... } }
This is a simple case where the iterator column names match the table column names. Note that the order of items in the SELECT
statement does not matter when you use a named iterator--data is matched by name, not position.
When you query to populate a positional iterator object, the data is retrieved according to the order in which you select the columns. Data from the first column selected from the database table is placed into the first column of the iterator, and so on. The datatypes of the table columns must be convertible to the types of the iterator columns, but the names of the database columns are irrelevant, as the iterator columns have no names.
Given the EMPSAL
table above, you can declare and use a positional iterator as follows.
Declaration:
#sql iterator SalPosIter (int, String, float);
Executable code:
class MyClass { void func() throws SQLException { ... SalPosIter piter; #sql piter = { SELECT empno, ename, raise FROM empsal }; ... process piter ... } }
Note that the order of the data items in the SELECT
statement must be the same as in the iterator.
The processing differs between named iterators and positional iterators, as described in "Accessing Named Iterators" and "Accessing Positional Iterators".
In addition to the preceding concepts, be aware of the following general notes about iterators:
SELECT *
syntax is allowed in populating an iterator, but is not recommended. In the case of a positional iterator, this requires that the number of columns in the table be equal to the number of columns in the iterator, and that the types match in order. In the case of a named iterator, this requires that the number of columns in the table be greater than or equal to the number of columns in the iterator and that the name and type of each iterator column match a database table column. (If the number of columns in the table is greater, however, a warning will be generated unless the translator -warn=nostrict
flag is set. For information about this flag, see "Translator Warnings (-warn)".)
SELECT
statement that populated it. Subsequent UPDATE
, INSERT
, DELETE
, COMMIT
, or ROLLBACK
operations have no effect on the iterator or its contents. This is further discussed in "Effect of Commits and Rollbacks on Iterators and Result Sets".
The exception to this is if you declare an iterator to be scrollable and "sensitive" to changes in the data. See "Declaring Scrollable Iterators" and "Scrollable Iterator Sensitivity".
In case you would rather not declare an iterator class, Oracle SQLJ permits you to use a weakly typed kind of iterator. Such iterators are known as result set iterators. To use a plain (non-scrollable) result set iterator, instantiate the sqlj.runtime.ResultSetIterator
class. To use a scrollable result set iterator, instantiate the sqlj.runtime.ScrollableResultSetIterator
class. (Scrollable iterators are described in "Scrollable Iterators".)
The drawback to using result set iterators, compared to strongly typed iterators, is that SQLJ cannot perform as much semantics-checking for your queries.
For more information, see "Result Set Iterators".
Five general steps are involved in using SQLJ named or positional iterator:
SELECT
statement.
There are advantages and appropriate situations for each kind of SQLJ iterator.
Named iterators allow greater flexibility. Because data selection into a named iterator matches SELECT-fields to iterator columns by name, you need not be concerned about the order in your query. This is less prone to error, as it is not possible for data to be placed into the wrong column. If the names do not match, the SQLJ translator will generate an error when it checks your SQL statements against the database.
Positional iterators offer a familiar paradigm and syntax to developers who have experience with other embedded-SQL languages. With named iterators you use a next()
method to retrieve data, while with positional iterators you use FETCH INTO
syntax similar to that of Pro*C, for example. (Each fetch implicitly advances to the next available row of the iterator before retrieving the next set of values.)
Positional iterators do, however, offer less flexibility than named iterators, because you are selecting data into iterator columns by position, instead of by name. You must be certain of the order of items in your SELECT
statement. You also must select data into all columns of the iterator, and it is possible to have data written into the wrong iterator column if the type of that column happens to match the datatype of the table column being selected.
Access to individual data elements is also less convenient with positional iterators. Named iterators, because they store data by name, are able to have convenient accessor methods for each column (for example, there would be an ename()
method to retrieve data from an ename
iterator column). With positional iterators, you must fetch data directly into Java host expressions with your FETCH INTO
statement, and the host expressions must be in the correct order.
Finally, if you do not want to declare strongly typed iterator classes for your queries, you can choose the alternative of using weakly typed result set iterators. Result set iterators are most convenient when converting JDBC code to SQLJ code. You must balance this consideration against the fact that result set iterators (either ResultSetIterator
instances or ScrollableResultSetIterator
instances) do not allow complete SQLJ semantics-checking during translation. With named or positional iterators, SQLJ verifies that SELECT-list types match the Java types into which the data will be materialized. With result set iterators, this is not possible. See "Result Set Iterators" for more information.
Be aware of the following notes regarding SQLJ iterators:
-warn=nostrict
flag set. Unmatched columns are ignored in this case. For information about this flag, see "Translator Warnings (-warn)".
FETCH INTO
statement for a positional iterator does not necessarily involve a round trip to the server, depending on the row-prefetch value. This is because you are fetching data from the iterator, not the database. If the row-prefetch value is 1, however, then each fetch does involve a separate trip to the database. (The row-prefetch value determines how many rows are retrieved with each trip to the database. See "Row Prefetching".)
FETCH INTO
syntax used with positional iterators, and are subject to the same restriction at runtime--the size (number of data items) of the SELECT-list must match the number of variables that are assigned data in the FETCH
statement.
When you declare a named iterator class, you declare the name as well as the datatype of each column of the iterator.
When you select data into a named iterator, the SELECT-fields must match the iterator columns in two ways:
ename
would match ENAME
).
The order in which attributes are declared in your named iterator class declaration is irrelevant. Data is selected into the iterator based on name alone.
A named iterator has a next()
method to retrieve data row by row, and an accessor method for each column to retrieve the individual data items. The accessor method names are identical to the column names. (Unlike most accessor method names in Java, accessor method names in named iterator classes do not start with "get".) For example, a named iterator object with a column sal
would have a sal()
accessor method.
Note: The following restrictions apply in naming the columns of a named iterator:
|
Use the following syntax to declare a named iterator class:
#sql <modifiers> iterator classname <implements clause> <with clause> ( type-name-list );
In this syntax, modifiers
is an optional sequence of legal Java class modifiers, classname
is the desired class name for the iterator, and type-name-list
is a list of the Java types and names equivalent to (convertible from) the column types and column names in a database table.
The implements
clause and with
clause are optional, specifying interfaces to implement and variables to define and initialize, respectively. These are discussed in "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause".
Now consider the following table:
CREATE TABLE PROJECTS ( ID NUMBER(4), PROJNAME VARCHAR(30), START_DATE DATE, DURATION NUMBER(3) );
You might declare the following named iterator for use with this table:
#sql public iterator ProjIter (String projname, int id, Date deadline);
This will result in an iterator class with columns of data accessible using the following provided accessor methods: projname()
, id()
, and deadline()
.
Declare a variable of the ProjIter
positional iterator type from the preceding section and populate it with a SELECT
statement.
Continuing to use the PROJECTS
table and ProjIter
iterator defined in the preceding section, note that there are columns in the table whose names and datatypes match the id
and projname
columns of the iterator, but you must use an alias and perform an operation to populate the deadline
column of the iterator. Here is an example:
ProjIter projsIter; #sql projsIter = { SELECT start_date + duration AS deadline, projname, id FROM projects WHERE start_date + duration >= sysdate };
This calculates a deadline for each project by adding its duration to its start date, then aliases the results as deadline
to match the deadline
iterator column. It also uses a WHERE
clause so that only future deadlines are processed (deadlines beyond the current system date in the database).
Similarly, you must create an alias if you want to use a function call. Suppose you have a function MAXIMUM()
that takes a DURATION
entry and an integer as input and returns the maximum of the two. (For example, you could input a 3 to make sure each project has at least a three-month duration in your application.)
Now presume you are declaring your iterator as follows:
#sql public iterator ProjIter2 (String projname, int id, float duration);
You could use the MAXIMUM()
function in your query, with an alias for the result, as follows:
ProjIter2 projsIter2; #sql projsIter2 = { SELECT id, projname, maximum(duration, 3) AS duration FROM projects };
Generally, you must use an alias in your query for any SELECT-field whose name is not a legal Java identifier or does not match a column name in your iterator.
Remember that in populating a named iterator, the number of columns you select from the database can never be less than the number of columns in the iterator. The number of columns you select can be greater than the number of columns in the iterator (unmatched columns are ignored), but this will generate a warning unless you have the SQLJ -warn=nostrict
option set.
Use the next()
method of the named iterator object to step through the data that was selected into it. To access each column of each row, use the accessor methods generated by SQLJ, typically inside a while
loop.
Whenever next()
is called:
next()
retrieves the row and returns true
.
next()
returns false
.
The following is an example of how to access the data of a named iterator, repeating the declaration, instantiation, and population used under "Instantiating and Populating Named Iterators".
Presume the following iterator class declaration:
#sql public iterator ProjIter (String projname, int id, Date deadline);
Populate and then access an instance of this iterator class as follows:
// Declare the iterator variable ProjIter projsIter; // Instantiate and populate iterator; order of SELECT doesn't matter #sql projsIter = { SELECT start_date + duration AS deadline, projname, id FROM projects WHERE start_date + duration >= sysdate }; // Process the results while (projsIter.next()) { System.out.println("Project name is " + projsIter.projname()); System.out.println("Project ID is " + projsIter.id()); System.out.println("Project deadline is " + projsIter.deadline()); } // Close the iterator projsIter.close(); ...
Note the convenient use of the projname()
, id()
, and deadline()
accessor methods to retrieve the data. Note also that the order of the SELECT
items does not matter, nor does the order in which the accessor methods are used.
Remember, however, that accessor method names are created with the case exactly as in your declaration of the iterator class. The following will generate compilation errors.
Declaration:
#sql iterator Cursor1 (String NAME);
Executable code:
... Cursor1 c1; #sql c1 = { SELECT NAME FROM TABLE }; while (c1.next()) { System.out.println("The name is " + c1.name()); } ...
The Cursor1
class has a method called NAME()
, not name()
. You would have to use c1.NAME()
in the System.out.println
statement.
For a complete sample of using a named iterator, see "Named Iterator--NamedIterDemo.sqlj".
When you declare a positional iterator class, you declare the datatype of each column but not the column name. The Java types into which the columns of the SQL query results are selected must be compatible with the datatypes of the SQL data. The names of the database columns or SELECT-fields are irrelevant.
Because names are not used, the order in which you declare your positional iterator Java types must exactly match the order in which the data is selected.
To retrieve data from a positional iterator once data has been selected into it, use a FETCH INTO
statement followed by an endFetch()
method call to determine if you have reached the end of the data (as detailed under "Accessing Positional Iterators").
Use the following syntax to declare a positional iterator class:
#sql <modifiers> iterator classname <implements clause> <with clause> ( position-list );
In this syntax, modifiers
is an optional sequence of legal Java class modifiers, and the
position-list
is a list of Java types compatible with the column types in a database table.
The implements
clause and with
clause are optional, specifying interfaces to implement and variables to define and initialize, respectively. These are discussed in "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause".
Now consider the following table, a subset of the standard EMP
table:
CREATE TABLE EMP ( EMPNO NUMBER(4), ENAME VARCHAR2(10), SAL NUMBER(7,2) );
And consider the following positional iterator declaration:
#sql public iterator EmpIter (String, int, float);
This example defines Java class EmpIter
with unnamed String
, int
, and float
columns. Note that the table columns and iterator columns are in a different order--the String
corresponds to ENAME
and the int
corresponds to EMPNO
. The order of the iterator columns determines the order in which you must select the data, as shown in "Instantiating and Populating Positional Iterators" below.
Declare a variable of the EmpIter
positional iterator type from the preceding section and populate it with a SELECT
statement.
Instantiating and populating a positional iterator is no different than doing so for a named iterator, except that you must be certain that your SELECT-fields are in the proper order.
The three datatypes in the EmpIter
iterator class are compatible with the types of the EMP
table, but be careful how you select the data, because the order is different. The following will work, because the SELECT-fields are in the same order as the iterator columns, as declared above in "Declaring Positional Iterator Classes":
EmpIter empsIter; #sql empsIter = { SELECT ename, empno, sal FROM emp };
Remember that in populating a positional iterator, the number of columns you select from the database must equal the number of columns in the iterator.
Access the columns defined by a positional iterator using SQL FETCH INTO
syntax.
The INTO
part of the command specifies Java host variables that receive the results columns. The host variables must be in the same order as the corresponding iterator columns. Use the endFetch()
method provided with all positional iterator classes to determine whether the last fetch reached the end of the data.
The following is an example, repeating the declaration, instantiation, and population used under "Instantiating and Populating Positional Iterators" above.
Note that the Java host variables in the SELECT
statement are in the same order as the columns of the positional iterator, which is mandatory.
First, presume the following iterator class declaration:
#sql public iterator EmpIter (String, int, float);
Populate and then access an instance of this iterator class as follows:
// Declare and initialize host variables int empnum=0; String empname=null; float salary=0.0f; // Declare an iterator instance EmpIter empsIter; #sql empsIter = { SELECT ename, empno, sal FROM emp }; while (true) { #sql { FETCH :empsIter INTO :empnum, :empname, :salary }; if (empsIter.endFetch()) break; // This test must be AFTER fetch, // but before results are processed. System.out.println("Name is " + empname); System.out.println("Employee number is " + empnum); System.out.println("Salary is " + salary); } // Close the iterator empsIter.close(); ...
The empname
, empnum
, and salary
variables are Java host variables whose types must match the types of the iterator columns.
Do not use the next()
method for a positional iterator. A FETCH
operation calls it implicitly to move to the next row.
For a complete sample of using a positional iterator, see "Positional Iterator--PosIterDemo.sqlj".
The positional iterator FETCH
clause discussed in the previous section performs a movement--an implicit next()
call--before it populates the host variables (if any). As an alternative, Oracle SQLJ supports using a special FETCH
syntax in conjunction with explicit next()
calls in order to use the same movement logic as with JDBC result sets and SQLJ named iterators. Using this special FETCH
syntax, the semantics differ--there is no implicit next()
call before the INTO-list is populated.
See "From JDBC Result Sets to SQLJ Iterators -- FETCH CURRENT Syntax" for more information.
SQLJ supports SQLJ iterators and JDBC result sets as host variables, as illustrated in the examples below.
Notes:
|
As you will see from the following examples, using iterators and result sets is fundamentally the same, with differences in declarations and in accessor methods to retrieve the data.
For the examples in this section, consider the following tables--subsets of the standard DEPT
and EMP
tables:
CREATE TABLE DEPT ( DEPTNO NUMBER(2), DNAME VARCHAR2(14) ); CREATE TABLE EMP ( EMPNO NUMBER(4), ENAME VARCHAR2(10), SAL NUMBER(7,2), DEPTNO NUMBER(2) );
This example uses a JDBC result set as an output host variable.
... ResultSet rs; ... #sql { BEGIN OPEN :OUT rs FOR SELECT ename, empno FROM emp; END }; while (rs.next()) { String empname = rs.getString(1); int empnum = rs.getInt(2); } rs.close(); ...
This example opens the result set rs
in a PL/SQL block to receive data from a SELECT
statement, selects data from the ENAME
and EMPNO
columns of the EMP
table, then loops through the result set to retrieve data into local variables.
This example uses a named iterator as an output host variable.
Declaration:
#sql public <static> iterator EmpIter (String ename, int empno);
(The public
modifier is required, and static
may be advisable if your declaration is at class level or nested-class level.)
Executable code:
... EmpIter iter; ... #sql { BEGIN OPEN :OUT iter FOR SELECT ename, empno FROM emp; END }; while (iter.next()) { String empname = iter.ename(); int empnum = iter.empno(); ...process/output empname and empnum... } iter.close(); ...
This example opens the iterator iter
in a PL/SQL block to receive data from a SELECT
statement, selects data from the ENAME
and EMPNO
columns of the EMP
table, then loops through the iterator to retrieve data into local variables.
This example uses a named iterator as an output host variable, taking data through a SELECT INTO
statement. (OUT
is the default for host variables in an INTO-list. For information about SELECT INTO
statements and syntax, see "Single-Row Query Results--SELECT INTO Statements".)
Declaration:
#sql public <static> iterator ENameIter (String ename);
(The public
modifier is required, and static
may be advisable if your declaration is at class level or nested-class level.)
Executable code:
... ENameIter enamesIter; String deptname; ... #sql { SELECT dname, cursor (SELECT ename FROM emp WHERE deptno = dept.deptno) INTO :deptname, :enamesIter FROM dept WHERE deptno = 20 }; System.out.println(deptname); while (enamesIter.next()) { System.out.println(enamesIter.ename()); } enamesIter.close(); ...
This example uses nested SELECT
statements to accomplish the following:
DEPT
table, selecting it into the output host variable deptname
.
EMP
table to select all employees whose department number is 20, selecting the resulting cursor into the output host variable enamesIter
, which is a named iterator.
In most cases, using SELECT INTO
is more convenient than using nested iterators if you are retrieving a single row in the outer SELECT
, although that option is also available as discussed below (such as in "Example: Named Iterator Column in a Positional Iterator"). Also, with nested iterators, you would have to process the data to determine how many rows there are in the outer SELECT
. With SELECT INTO
you are assured of just one row.
Oracle SQLJ includes extensions that allow iterator declarations to specify columns of type ResultSet
or columns of other iterator types declared within the current scope. In other words, iterators and result sets can exist within iterators in Oracle SQLJ. These column types are used to retrieve a column in the form of a cursor. This is useful for nested SELECT
statements that return nested table information.
The following examples are functionally identical--each uses a nested result set or iterator (result sets or iterators in a column within an iterator) to print all the employees in each department in the DEPT
table. The first example uses result sets within a named iterator, the second example uses named iterators within a named iterator, and the third example uses named iterators within a positional iterator.
Here are the steps:
DNAME
(department name) from the DEPT
table.
SELECT
into a cursor to get all employees from the EMP
table for each department.
iter
), which has a name column and an iterator column. The cursor with the employee information for any given department goes into the iterator column of that department's row of the outer iterator.
This example uses a column of type ResultSet
in a named iterator.
Declaration:
#sql iterator DeptIter (String dname, ResultSet emps);
Executable code:
... DeptIter iter; ... #sql iter = { SELECT dname, cursor (SELECT ename FROM emp WHERE deptno = dept.deptno) AS emps FROM dept }; while (iter.next()) { System.out.println(iter.dname()); ResultSet enamesRs = iter.emps(); while (enamesRs.next()) { String empname = enamesRs.getString(1); System.out.println(empname); } enamesRs.close(); } iter.close(); ...
This example uses a named iterator that has a column whose type is that of a previously defined named iterator (nested iterators).
Declarations:
#sql iterator ENameIter (String ename); #sql iterator DeptIter (String dname, ENameIter emps);
Executable code:
... DeptIter iter; ... #sql iter = { SELECT dname, cursor (SELECT ename FROM emp WHERE deptno = dept.deptno) AS emps FROM dept }; while (iter.next()) { System.out.println(iter.dname()); ENameIter enamesIter = iter.emps(); while (enamesIter.next()) { System.out.println(enamesIter.ename()); } enamesIter.close(); } iter.close(); ...
This example uses a positional iterator that has a column whose type is that of a previously defined named iterator (nested iterators). This uses the FETCH INTO
syntax of positional iterators. This example is functionally equivalent to the previous two.
Note that because the outer iterator is a positional iterator, there does not have to be an alias to match a column name, as was required when the outer iterator was a named iterator in the previous example.
Declarations:
#sql iterator ENameIter (String ename); #sql iterator DeptIter (String, ENameIter);
Executable code:
... DeptIter iter; ... #sql iter = { SELECT dname, cursor (SELECT ename FROM emp WHERE deptno = dept.deptno) FROM dept }; while (true) { String dname = null; ENameIter enamesIter = null; #sql { FETCH :iter INTO :dname, :enamesIter }; if (iter.endFetch()) break; System.out.println(dname); while (enamesIter.next()) { System.out.println(enamesIter.ename()); } enamesIter.close(); } iter.close(); ...
SQLJ allows you to assign a value to a Java host expression inside a SQL operation. This is known as an assignment statement and is accomplished using the following syntax:
#sql { SET :host_exp = expression };
The host_exp
is the target host expression, such as a variable or array index. The expression
could be a number, host expression, arithmetic expression, function call, or other construct that yields a valid result into the target host expression.
The default is OUT
for a target host expression in an assignment statement, but you can optionally state this explicitly:
#sql { SET :OUT host_exp = expression };
Trying to use an IN
or INOUT
token in an assignment statement will result in an error at translation time.
The preceding statements are functionally equivalent to the following PL/SQL code:
#sql { BEGIN :OUT host_exp := expression; END };
Here is a simple example of an assignment statement:
#sql { SET :x = foo1() + foo2() };
This statement assigns to x
the sum of the return values of foo1()
and foo2()
and assumes that the type of x
is compatible with the type of the sum of the outputs of these functions.
Consider the following additional examples:
int i2; java.sql.Date dat; ... #sql { SET :i2 = TO_NUMBER(substr('750 etc.', 1, 3)) + TO_NUMBER(substr('250 etc.', 1, 3)) }; ... #sql { SET :dat = sysdate }; ...
The first statement will assign to i2
the value 1000 (750 + 250). (The substr()
calls take the first three characters of the strings, or '750' and '250'. The TO_NUMBER()
calls convert the strings to the numbers 750 and 250.)
The second statement will read the database system date and assign it to dat
.
An assignment statement is especially useful when you are performing operations on return variables from functions stored in the database. You do not need an assignment statement to simply assign a function result to a variable, because you can accomplish this using normal function call syntax as explained in "Stored Procedure and Function Calls". You also do not need an assignment statement to manipulate output from Java functions, because you can accomplish that in a normal Java statement. So you can presume that foo1()
and foo2()
above are stored functions in the database, not Java functions.
SQLJ provides convenient syntax for calling stored procedures and stored functions in the database. These procedures and functions could be written in Java, PL/SQL, or any other language supported by the database.
A stored function requires a result expression in your SQLJ executable statement to accept the return value, and can optionally take input, output, or input-output parameters as well.
A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.
Note: Remember that instead of using the following procedure-call and function-call syntax, you can optionally use JPublisher to create Java wrappers for PL/SQL stored procedures and functions, then call the Java wrappers as you would any other Java methods. JPublisher is discussed in "JPublisher and the Creation of Custom Java Classes". For additional information, see the Oracle9i JPublisher User's Guide. |
Stored procedures do not have a return value but can take a list with input, output, and input-output parameters. Stored procedure calls use the CALL
token, as shown below. The word "CALL" is followed by white space and then the procedure name. There must be a space after the CALL
token to differentiate it from the procedure name. There cannot be a set of outer parentheses around the procedure call (this differs from the syntax for function calls, as explained in "Calling Stored Functions").
#sql { CALL PROC(<PARAM_LIST>) };
PROC
is the name of the stored procedure, which can optionally take a list of input, output, and input-output parameters. PROC
can include a schema or package name as well, such as SCOTT.MYPROC()
.
Presume that you have defined the following PL/SQL stored procedure:
CREATE OR REPLACE PROCEDURE MAX_DEADLINE (deadline OUT DATE) IS BEGIN SELECT MAX(start_date + duration) INTO deadline FROM projects; END;
This reads the table PROJECTS
, looks at the START_DATE
and DURATION
columns, calculates start_date + duration
in each row, then takes the maximum START_DATE + DURATION
total and selects it into DEADLINE
, which is an output parameter of type DATE
.
In SQLJ, you can call this MAX_DEADLINE
procedure as follows:
java.sql.Date maxDeadline; ... #sql { CALL MAX_DEADLINE(:out maxDeadline) };
For any parameters, you must use the host expression tokens IN
(optional/default), OUT
, and INOUT
appropriately to match the input, output, and input-output designations of the stored procedure. Additionally, the types of the host variables you use in the parameter list must be compatible with the parameter types of the stored procedure.
Stored functions have a return value and can also take a list of input, output, and input-output parameters. Stored function calls use the VALUES
token, as shown below. This syntax consists of the word "VALUES" followed by the function call. In standard SQLJ, the function call must be enclosed in a set of outer parentheses, as shown. In Oracle SQLJ, the outer parentheses are optional. When using the outer parentheses, it does not matter if there is white space between the VALUES
token and the begin-parenthesis. (A VALUES
token can also be used in INSERT INTO
table
VALUES
syntax supported by Oracle SQL, but these situations are unrelated semantically and syntactically.)
#sql result = { VALUES(FUNC(<PARAM_LIST>)) };
In this syntax, result
is the result expression, which takes the function return value. FUNC
is the name of the stored function, which can optionally take a list of input, output, and input-output parameters. FUNC
can include a schema or package name, such as SCOTT.MYFUNC()
.
Referring back to the example in "Calling Stored Procedures", consider defining the stored procedure as a stored function instead, as follows:
CREATE OR REPLACE FUNCTION GET_MAX_DEADLINE RETURN DATE IS deadline DATE; BEGIN SELECT MAX(start_date + duration) INTO deadline FROM projects; RETURN deadline; END;
In SQLJ, you can call this GET_MAX_DEADLINE
function as follows:
java.sql.Date maxDeadline; ... #sql maxDeadline = { VALUES(GET_MAX_DEADLINE) };
The result expression must have a type compatible with the return type of the function.
In Oracle SQLJ, the following syntax (outer parentheses omitted) is also allowed:
#sql maxDeadline = { VALUES GET_MAX_DEADLINE };
For stored function calls, as with stored procedures, you must use the host expression tokens IN
(optional/default), OUT
, and INOUT
appropriately to match the input, output, and input-output parameters of the stored function. Additionally, the types of the host variables you use in the parameter list must be compatible with the parameter types of the stored function.
SQLJ supports assigning the return value of a stored function to an iterator or result set variable, provided that the function returns a REF CURSOR type.
The following example uses an iterator to take a stored function return. Using a result set is similar.
This example uses an iterator as a return type for a stored function, using a REF CURSOR type in the process. (REF CURSOR types are described in "Support for Oracle REF CURSOR Types".)
Presume the following function definition:
CREATE OR REPLACE PACKAGE sqlj_refcursor AS TYPE EMP_CURTYPE IS REF CURSOR; FUNCTION job_listing (j varchar2) RETURN EMP_CURTYPE; END sqlj_refcursor; CREATE OR REPLACE PACKAGE BODY sqlj_refcursor AS FUNCTION job_listing (j varchar) RETURN EMP_CURTYPE IS DECLARE rc EMP_CURTYPE; BEGIN OPEN rc FOR SELECT ename, empno FROM emp WHERE job = j; RETURN rc; END; END sqlj_refcursor;
Use this function as follows.
Declaration:
#sql public <static> iterator EmpIter (String ename, int empno);
(The public
modifier is required, and static
may be advisable if your declaration is at class level or nested-class level.)
Executable code:
EmpIter iter; ... #sql iter = { VALUES(sqlj_refcursor.job_listing('SALES')) }; while (iter.next()) { String empname = iter.ename(); int empnum = iter.empno(); ... process empname and empnum ... } iter.close(); ...
This example calls the job_listing()
function to return an iterator that contains the name and employee number of each employee whose job title is "SALES". It then retrieves this data from the iterator.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|