2 Programming Features in PL/SQL in TimesTen

Conditional control

The IF-THEN-ELSE statement and CASE statement are examples of conditional control. In Example 2-7, the IF-THEN-ELSE statement is used to determine the salary raise of an employee based on the current salary. The CASE statement is also used to choose the course of action to take based on the job_id of the employee:

Command> DECLARE
       >   jobid employees.job_id%TYPE;
       >   empid employees.employee_id%TYPE := 115;
       >   sal employees.salary%TYPE;
       >   sal_raise NUMBER(3,2);
       > BEGIN
       >   SELECT job_id, salary INTO jobid, sal from employees
       >     WHERE employee_id = empid;
       >   CASE
       >     WHEN jobid = 'PU_CLERK' THEN
       >       IF sal < 3000 THEN sal_raise := .12;
       >       ELSE sal_raise := .09;
       >       END IF;
       >     WHEN jobid = 'SH_CLERK' THEN
       >       IF sal < 4000 THEN sal_raise := .11;
       >       ELSE sal_raise := .08;
       >       END IF;
       >     WHEN jobid = 'ST_CLERK' THEN
       >       IF sal < 3500 THEN sal_raise := .10;
       >       ELSE sal_raise := .07;
       >       END IF;
       >     ELSE
       >       BEGIN
       >         DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
       >       END;
       >   END CASE;
       > DBMS_OUTPUT.PUT_LINE ('Original salary ' || sal);
       > -- Update
       > UPDATE employees SET salary = salary + salary * sal_raise
       > WHERE employee_id = empid;
       > END;
       > /
Original salary 3100
 
PL/SQL procedure successfully completed.

Iterative control

An iterative control construct executes a sequence of statements repeatedly, as long as a specified condition is true. Loop constructs are used to perform iterative operations.

There are three loop types:

• Basic loop

• FOR loop

• WHILE loop

The basic loop performs repetitive actions without overall conditions. The FOR loop performs iterative actions based on a count. The WHILE loops perform iterative actions based on a condition.

Command> CREATE TABLE temp (tempid NUMBER(6),
       > tempsal NUMBER(8,2),
       > tempname VARCHAR2(25));
Command> DECLARE
       >   sal employees.salary%TYPE := 0;
       >   mgr_id employees.manager_id%TYPE;
       >   lname employees.last_name%TYPE;
       >   starting_empid employees.employee_id%TYPE := 120;
       > BEGIN
       >   SELECT manager_id INTO mgr_id
       >     FROM employees
       >     WHERE employee_id = starting_empid;
       >   WHILE sal <= 15000 LOOP -- loop until sal > 15000
       >     SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
       >       FROM employees WHERE employee_id = mgr_id;
       >   END LOOP;
       >   INSERT INTO temp VALUES (NULL, sal, lname);
       > -- insert NULL for tempid
       >   COMMIT;
       > EXCEPTION
       >   WHEN NO_DATA_FOUND THEN
       >     INSERT INTO temp VALUES (NULL, NULL, 'Not found');
       > -- insert NULLs
       >     COMMIT;
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM temp;
< <NULL>, 24000, King >
1 row found.

CONTINUE statement

The CONTINUE statement was added to the Oracle 11g release and is also supported by TimesTen. The CONTINUE statement enables you to transfer control within a loop back to a new iteration.

Command> DECLARE
       >   v_total  SIMPLE_INTEGER := 0;
       > BEGIN
       >   FOR i IN 1..10 LOOP
       >     v_total := v_total + i;
       >     DBMS_OUTPUT.PUT_LINE ('Total is : ' || v_total);
       >     CONTINUE WHEN i > 5;
       >     v_total := v_total + i;
       >     DBMS_OUTPUT.PUT_LINE ('Out of loop  Total is: ' || v_total);
       >   END LOOP;
       > END;
       > /
Total is : 1
Out of loop  Total is: 2
Total is : 4
Out of loop  Total is: 6
Total is : 9
Out of loop  Total is: 12
Total is : 16
Out of loop  Total is: 20
Total is : 25
Out of loop  Total is: 30
Total is : 36
Total is : 43
Total is : 51
Total is : 60
Total is : 70
 
PL/SQL procedure successfully completed.

Using bind variables from an application

You can use ":var" notation for bind variables to be passed between your application (such as a C or Java application) and PL/SQL. The term bind variable (or sometimes host variable) is used equivalently to how the term parameter has historically been used in TimesTen, and bind variables from an application would correspond to the parameters declared in a PL/SQL procedure or function specification.

Here is a simple example using ttIsql in calling a PL/SQL procedure that retrieves the name and salary of the employee corresponding to a specified employee ID. In this example, ttIsql essentially acts as the calling application, and the name and salary are output from PL/SQL:

Command> VARIABLE b_name VARCHAR2 (25)
Command> VARIABLE b_sal  NUMBER
Command> BEGIN
       >  query_emp (171, :b_name, :b_sal);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_name
B_NAME               : Smith
Command> PRINT b_sal
B_SAL                : 7400

See "Examples using input and output parameters and bind variables" for the complete example.

See "PL/SQL procedures and functions" for how to create and define procedures and functions.

See "Binding parameters" in Oracle TimesTen In-Memory Database C Developer's Guide and "Preparing SQL statements and setting input parameters" in Oracle TimesTen In-Memory Database Java Developer's Guide for additional information and examples for those languages.

IN, OUT, and IN OUT parameter modes

Parameter modes define whether parameters declared in a PL/SQL subprogram specification are used for input, output, or both. The three parameter modes are IN (the default), OUT, and IN OUT.

An IN parameter lets you pass a value to the subprogram being invoked. Inside the subprogram, an IN parameter acts like a constant and cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter.

An OUT parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it.

An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. Typically, an IN OUT parameter is a string buffer or numeric accumulator that is read inside the subprogram and then updated. The actual parameter that corresponds to an IN OUT formal parameter must be a variable, not a constant or an expression.

See "Examples using input and output parameters and bind variables".

Static SQL in PL/SQL for queries and DML statements

From within PL/SQL, you can execute the following as static SQL:

• DML statements: INSERT, UPDATE, DELETE, and MERGE

• Queries: SELECT

• Transaction control: COMMIT and ROLLBACK

For information on these SQL statements, refer to "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Example 2-10 below shows how to execute a query. For additional examples using TimesTen SQL in PL/SQL, see Chapter 5, "Examples Using TimesTen SQL in PL/SQL".

Command> run selectinto.sql
 
DECLARE
   v_emp_hiredate employees.hire_date%TYPE;
   v_emp_salary   employees.salary%TYPE;
BEGIN
   SELECT hire_date, salary
   INTO   v_emp_hiredate, v_emp_salary
   FROM   employees
   WHERE  employee_id = 100;
   DBMS_OUTPUT.PUT_LINE(v_emp_hiredate || ' ' || v_emp_salary);
END;
/
 
1987-06-17 24000
 
PL/SQL procedure successfully completed.

Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)

You can use native dynamic SQL for any of the following:

• Use a DML statement such as INSERT, UPDATE, or DELETE.

• Use a DDL statement such as CREATE or ALTER. (For example, you can use ALTER SESSION to change a PL/SQL connection attribute.)

• Call a TimesTen built-in procedure.

In particular, one use case is if you do not know the full text of your SQL statement until execution time. For example, during compilation, you may not know the name of the column to use in the WHERE clause of your SELECT statement. In such a situation, you can use the EXECUTE IMMEDIATE statement.

Another use case for dynamic SQL is for DDL, which cannot be executed in static SQL from within PL/SQL.

Example 2-11 provides an example of EXECUTE IMMEDIATE. For additional examples, see "Examples using EXECUTE IMMEDIATE".

Command> CREATE OR REPLACE PROCEDURE create_table
       >   (p_table_name VARCHAR2, p_col_specs VARCHAR2) IS
       > BEGIN
       >   EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name
       >
       > || ' (' || p_col_specs|| ' )';
       > END;
       > /
 
Procedure created.

Command> BEGIN
       > create_table ('EMPLOYEES_NAMES', 'id NUMBER (4)
       >  PRIMARY KEY, name VARCHAR2 (40)');
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> DESCRIBE employees_names;
 
Table USER.EMPLOYEES_NAMES:
  Columns:
   *ID                              NUMBER (4) NOT NULL
    NAME                            VARCHAR2 (40) INLINE
 
1 table found.
(primary key columns are indicated with *)

FORALL and BULK COLLECT operations

Bulk binding is a powerful feature used in the execution of SQL statements from PL/SQL, used in moving large amounts of data between SQL and PL/SQL. (Do not confuse this with binding parameters from an application program to PL/SQL.) With bulk binding, you bind arrays of values in a single operation rather than using a loop to perform a FETCH, INSERT, UPDATE, and DELETE operation multiple times. Oracle TimesTen In-Memory Database supports bulk binding, which can result in significant performance improvement.

Use the FORALL statement to bulk-bind input collections before sending them to the SQL engine. Use BULK COLLECT to bring back batches of results from SQL. You can bulk-collect into any type of PL/SQL collection, such as a varray, nested table, or associative array (index-by table). (For additional information on collections, refer to "Using collections".)

You can use the %BULK_EXCEPTIONS cursor attribute and the SAVE EXCEPTIONS clause with FORALL statements. SAVE EXCEPTIONS allows a statement to continue executing even when an insert or update statement issues an exception (for example, a constraint error). Exceptions are collected into an array that you can examine using %BULK_EXCEPTIONS after the statement has executed. When you use SAVE EXCEPTIONS, if exceptions are encountered during the execution of the FORALL statement, then all rows in the collection are processed. When the statement finishes, an error is issued to signal that at least one exception occurred. If you do not use SAVE EXCEPTIONS, then when an exception is issued during a FORALL statement, the statement returns the exception immediately and no other rows are processed.

Refer to "Using FORALL and BULK COLLECT Together" in Oracle Database PL/SQL Language Reference for more information on these features.

Example 2-12 shows basic use of bulk binding and the FORALL statement. For more information and examples on bulk binding, see "Examples using FORALL and BULK COLLECT".

Command> CREATE OR REPLACE PROCEDURE raise_salary (p_percent NUMBER) IS
       >   TYPE numlist_type IS TABLE OF NUMBER
       >    INDEX BY BINARY_INTEGER;
       >   v_id  numlist_type;  -- collection
       > BEGIN
       >   v_id(1) := 100; v_id(2) := 102; v_id (3) := 104; v_id (4) := 110;
       >   -- bulk-bind the associative array
       >   FORALL i IN v_id.FIRST .. v_id.LAST
       >    UPDATE employees
       >     SET salary = (1 + p_percent/100) * salary
       >     WHERE employee_id = v_id (i);
       > END;
       > /
 
Procedure created.
 

Command> SELECT salary FROM employees WHERE employee_id = 100 OR employee_id = 
102 OR employee_id = 104 OR employee_id = 100;
< 24000 >
< 17000 >
< 6000 >
3 rows found.

Command> EXECUTE raise_salary (10);
 
PL/SQL procedure successfully completed.
 
Command> SELECT salary FROM employees WHERE employee_id = 100 or employee_id = 
102 OR employee_id = 104 OR employee_id = 100;
< 26400 >
< 18700 >
< 6600 >
3 rows found.
Command> ROLLBACK;

RETURNING INTO clause

You can use a RETURNING INTO clause (sometimes referred to as DML returning), with an INSERT, UPDATE, or DELETE statement to return specified columns or expressions, optionally including rowids, from rows that were affected by the action. This eliminates the need for a subsequent SELECT statement and separate round trip, in case, for example, you want to confirm what was affected or want the rowid after an insert or update.

A RETURNING INTO clause can be used with dynamic SQL (with EXECUTE IMMEDIATE) as well as static SQL.

Through the PL/SQL BULK COLLECT feature, the clause can return items from a single row into either a set of parameters or a record, or can return columns from multiple rows into a PL/SQL collection such as a varray, nested table, or associative array (index-by table). Parameters in the INTO part of the clause must be output only, not input/output. For information on collections, refer to "Using collections". For BULK COLLECT, see "FORALL and BULK COLLECT operations" and "Examples using FORALL and BULK COLLECT".

SQL syntax and restrictions for the RETURNING INTO clause in TimesTen are documented as part of the "INSERT", "UPDATE", and "DELETE" documentation in Oracle TimesTen In-Memory Database SQL Reference.

Also see "Examples using RETURNING INTO".

Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for additional information about RETURNING INTO.

TimesTen PL/SQL with In-Memory Database Cache

When PL/SQL programs execute SQL statements, the SQL statements are processed by TimesTen in the same manner as when SQL is executed from applications written in other programming languages. All standard behaviors of TimesTen SQL apply. In an IMDB Cache environment, this includes the ability to use all cache features from PL/SQL. When PL/SQL accesses tables in cache groups, the normal rules for those tables apply. For example, issuing a SELECT statement against a cache instance in a dynamic cache group may cause the instance to be automatically loaded into TimesTen from the Oracle database.

In particular, the following points should be made about this functionality:

• When you use static SQL in PL/SQL, any tables accessed must exist in TimesTen or the PL/SQL will not compile successfully. In the following example, ABC must exist in TimesTen:

  begin
    insert into abc values(1, 'Y');
  end;
  

• In an IMDB Cache environment, there is the capability to use the TimesTen passthrough facility to automatically route SQL statements from TimesTen to Oracle Database. (See "Setting a passthrough level" in Oracle In-Memory Database Cache User's Guide for details of the passthrough facility.)

  With passthrough=1, a statement can be passed through to Oracle if any accessed table does not exist in TimesTen. But in PL/SQL, the statement would have to be executed using dynamic SQL.

  Updating the preceding example, the following TimesTen PL/SQL block could be used to access ABC in Oracle Database with passthrough=1:

  begin
    execute immediate 'insert into abc values(1, 'Y')';
  end;
  

  In this case, TimesTen PL/SQL can compile the block because the SQL statement is not examined at compile time.

• While PL/SQL can be executed in TimesTen, in the current release the TimesTen passthrough facility cannot be used to route PL/SQL blocks from TimesTen to Oracle Database. For example, when using IMDB Cache with passthrough=3, all statements executed on a TimesTen connection will be routed to Oracle Database. In this scenario, you may not execute PL/SQL blocks from your application program, because TimesTen would attempt to forward them to Oracle Database, which is not supported. (In the passthrough=1 example, it is just the SQL statement being routed to Oracle, not the block as a whole.)

Package concepts

A package is a database object that groups logically related PL/SQL types, variables, and subprograms. You specify the package and then define its body in separate steps.

The package specification is the interface to the package, declaring the public types, variables, constants, exceptions, cursors, and subprograms that are visible outside the immediate scope of the package. The body defines the objects declared in the specification, as well as queries for the cursors, code for the subprograms, and private objects that are not visible to applications outside the package.

TimesTen stores the package specification separately from the package body in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body.

Creating and using packages

To create packages and store them permanently in a TimesTen database, use the CREATE PACKAGE and CREATE PACKAGE BODY statements.

To create a new package, do the following:

1. Create the package specification with the CREATE PACKAGE statement.

   You can declare program objects in the package specification. Such objects are called public objects and can be referenced outside the package, as well as by other objects in the package.

   Optionally use CREATE OR REPLACE PACKAGE if you want the package specification to be replaced if it already exists.

2. Create the package body with the CREATE PACKAGE BODY (or CREATE OR REPLACE PACKAGE BODY) statement.

   You can declare and define program objects in the package body.

   • You must define public objects declared in the package specification.

   • You can declare and define additional package objects, called private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package. They cannot be referenced outside the package.

Use ALTER PACKAGE if you want to explicitly compile the member procedures and functions of a package or modify the compilation options.

For more information on the CREATE PACKAGE, CREATE PACKAGE BODY, and ALTER PACKAGE statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Command> CREATE OR REPLACE PACKAGE emp_actions AS
       >   PROCEDURE hire_employee (employee_id NUMBER,
       >     last_name VARCHAR2,
       >     first_name VARCHAR2,
       >     email VARCHAR2,
       >     phone_number VARCHAR2,
       >     hire_date DATE,
       >     job_id VARCHAR2,
       >     salary NUMBER,
       >     commission_pct NUMBER,
       >     manager_id NUMBER,
       >     department_id NUMBER);
       >   PROCEDURE remove_employee (emp_id NUMBER);
       >   FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
       > END emp_actions;
       > /
 
Package created.
 
Command> -- Package body:
       > CREATE OR REPLACE PACKAGE BODY emp_actions AS
       > -- Code for procedure hire_employee:
       >   PROCEDURE hire_employee (employee_id NUMBER,
       >     last_name VARCHAR2,
       >     first_name VARCHAR2,
       >     email VARCHAR2,
       >     phone_number VARCHAR2,
       >     hire_date DATE,
       >     job_id VARCHAR2,
       >     salary NUMBER,
       >     commission_pct NUMBER,
       >     manager_id NUMBER,
       >     department_id NUMBER) IS
       >   BEGIN
       >     INSERT INTO employees VALUES (employee_id,
       >       last_name,
       >       first_name,
       >       email,
       >       phone_number,
       >       hire_date,
       >       job_id,
       >       salary,
       >       commission_pct,
       >       manager_id,
       >       department_id);
       >   END hire_employee;
       > -- Code for procedure remove_employee:
       >   PROCEDURE remove_employee (emp_id NUMBER) IS
       >   BEGIN
       >     DELETE FROM employees WHERE employee_id = emp_id;
       >   END remove_employee;
       > -- Code for function num_above_salary:
       >   FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
       >     emp_sal NUMBER(8,2);
       >     num_count NUMBER;
       >   BEGIN
       >     SELECT salary INTO emp_sal FROM employees
       >     WHERE employee_id = emp_id;
       >     SELECT COUNT(*) INTO num_count FROM employees
       >     WHERE salary > emp_sal;
       >     RETURN num_count;
       >   END num_above_salary;
       > END emp_actions;
       > /
 
Package body created.
 
Command> BEGIN
       > /* call function to return count of employees with salary
       >    greater than salary of employee with employee_id = 120
       > */
       >   DBMS_OUTPUT.PUT_LINE
       >     ('Number of employees with higher salary: ' ||
       >       TO_CHAR(emp_actions.num_above_salary(120)));
       > END;
       > /
Number of employees with higher salary: 33
 
PL/SQL procedure successfully completed.

Command> SELECT salary FROM employees WHERE employee_id = 120;
< 8000 >
1 row found.

Command> SELECT COUNT (*) FROM employees WHERE salary > 8000;
< 33 >
1 row found.

Command> BEGIN 
       >   emp_actions.hire_employee(300,
       >     'Belden',
       >     'Enrique',
       >     'EBELDEN',
       >     '555.111.2222',
       >     '31-AUG-04',
       >     'AC_MGR',
       >     9000,
       >     .1,
       >     101,
       >     110);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
< 300, Belden, Enrique, EBELDEN, 555.111.2222, 2004-08-31 00:00:00, AC_MGR, 9000
, .1, 101, 110 >
1 row found.
Command> BEGIN
       >   emp_actions.remove_employee (300);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
0 rows found.