Overview of SQLJ Executable Statements

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).

Rules for SQLJ Executable Statements

A SQLJ executable statement must follow these rules:

• It is permitted in Java code wherever Java block statements are permitted (in other words, it is permitted inside method definitions and static initialization blocks).

• Its embedded SQL must be enclosed in curly braces: {...}.

SQLJ Clauses

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.

Table 3-1 SQLJ Statement Clauses

Category	Functionality	More Information
SELECT INTO clause	select data into Java host expressions	"Single-Row Query Results--SELECT INTO Statements"
FETCH clause	fetch data from a positional iterator	"Using Positional Iterators"
COMMIT clause	commit changes to the database	"Using Manual COMMIT and ROLLBACK"
ROLLBACK clause	cancel changes to the database	"Using Manual COMMIT and ROLLBACK"
set transaction clause	use advanced transaction control--access mode and isolation level	"Advanced Transaction Control"
procedure clause	call a stored procedure	"Calling Stored Procedures"
assignment clause	assign values to Java host expressions	"Assignment Statements (SET)"
SQL clause	use standard SQL syntax and functionality: SELECT, UPDATE, INSERT, DELETE	Oracle8i SQL Reference

Table 3-2 SQLJ Assignment Clauses

Category	Functionality	More Information
query clause	select data into a SQLJ iterator	"Multi-Row Query Results--SQLJ Iterators"
function clause	call a stored function	"Calling Stored Functions"
iterator conversion clause	convert a JDBC result set to a SQLJ iterator	"Converting from Result Sets to Named or Positional Iterators"

Specifying Connection Context Instances and Execution Context Instances

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 };

Executable Statement Examples

Examples of elementary SQLJ executable statements appear below. More complicated statements are discussed later in this chapter.

Elementary INSERT

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) };

Elementary INSERT with Connection Context or Execution Context Instances

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) };

A Simple SQLJ Method

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) };
}

PL/SQL Blocks in Executable Statements

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; };

---