Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## 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 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 operation must be enclosed in curly braces: `{...}.`

Notes: It is recommended that you not close the SQL operation with a semi-colon. The parser will detect the end of the operation when it encounters the closing curly brace of the SQLJ clause. Everything inside the curly braces of a SQLJ executable statement is treated as SQL syntax and must follow SQL rules, with the exception of Java host expressions (which are described in "Java Host Expressions, Context Expressions, and Result Expressions"). During examination of SQL operations, only DML operations (such as `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.

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

##### 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 for 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
PL/SQL block | Use BEGIN...END anonymous block inside SQLJ statement. | "PL/SQL Blocks in Executable Statements" PL/SQL User's Guide and 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"

Note: A SQLJ statement is referred to by the same name as the clause that makes up the body of that statement. For example, an executable statement consisting of `#sql` followed by a `SELECT INTO` clause is referred to as a `SELECT INTO` statement.

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

Notes: Include the square brackets around connection context instances and execution context instances--they are part of the syntax. If you specify both a connection context instance and an execution context instance, the connection context instance must come first.

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

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

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

Notes: The `throws SQLException` is required. For information about exception-handling, see "Exception-Handling Basics". SQLJ function calls also use a `VALUES` token, but these situations are not related semantically.

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

Notes: It is recommended that you not close a PL/SQL block with a semi-colon after the `END`. The parser will detect the end of the block when it encounters the closing curly brace of the SQLJ clause. Remember that using PL/SQL in your SQLJ code would prevent portability to other platforms, because PL/SQL is Oracle-specific. As an alternative to using JDBC for dynamic SQL, you can use PL/SQL anonymous blocks within SQLJ statements. See "PL/SQL in SQLJ for Dynamic SQL--DynamicDemo.sqlj" for a sample.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index