PL/SQL Blocks

The basic unit of a PL/SQL source program is the block, or anonymous block, which groups related declarations and statements. TimesTen supports PL/SQL blocks.

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. The example below shows the basic structure of a PL/SQL block.

Note:

If you use cache, a PL/SQL block cannot be passed through to Oracle Database. (Also see "TimesTen PL/SQL with Cache".)

DECLARE   -- (optional)
          -- Variables, cursors, user-defined exceptions
BEGIN     -- (mandatory)
          -- PL/SQL statements
EXCEPTION -- (optional)
          -- Actions to perform when errors occur
END       -- (mandatory)

You can define either anonymous or named blocks in your PL/SQL programs. This example creates an anonymous block that queries the employees table and returns the data in a PL/SQL variable:

Command> SET SERVEROUTPUT ON;
Command>  DECLARE
            v_fname VARCHAR2 (20);
          BEGIN
            SELECT first_name
            INTO v_fname
            FROM employees
            WHERE employee_id = 100;
          DBMS_OUTPUT.PUT_LINE (v_fname);
          END;
          /
Steven
 
PL/SQL procedure successfully completed.