Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
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 that is supported by your JDBC driver (such as DML, DDL, and transaction control).
A SQLJ executable statement must follow these rules:
{...}.
Notes:
|
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
.
A SQL operation in a SQLJ statement can use standard SQL syntax only, or can use a clause with syntax that is 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 last entry in Table 3-1 is a general category for statement clauses that use standard SQL syntax, as opposed to SQLJ-specific syntax.
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 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 that is specific to SQLJ.
Assume this table has been created:
CREATE TABLE EMP ( EMPNAME CHAR(30), SALARY NUMBER );
Use the following SQLJ executable statement (simply using standard SQL syntax) to insert Joe as a new employee into the EMP
table, specifying his name and salary.
#sql { INSERT INTO emp (empname, salary) 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 (empname, salary) VALUES ('Joe', 43000) }; #sql [execctx] { INSERT INTO emp (empname, salary) VALUES ('Joe', 43000) }; #sql [ctx, execctx] { INSERT INTO emp (empname, salary) 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 goes through a loop that inserts new employees in the emp
table, creating employee numbers 2001-2100 (this example presumes that 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; };
Note: Remember that using PL/SQL in your SQLJ code would prevent portability to other platforms, because PL/SQL is Oracle-specific. |