Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
Release 11.2.1

Part Number E13076-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 Programming Features in PL/SQL in TimesTen

One of the advantages of PL/SQL in TimesTen is the ability to integrate PL/SQL procedural constructs with the flexible and powerful TimesTen SQL language.

This chapter surveys the main PL/SQL programming features described in "Overview of PL/SQL" in Oracle Database PL/SQL Language Reference. Working from simple examples, you will learn how to use PL/SQL in TimesTen. Unless otherwise noted, the examples have the same results in TimesTen as in Oracle.

See the end of the chapter for TimesTen-specific considerations. See "TimesTen PL/SQL components and operations" for an overview of how applications interact with TimesTen in general and PL/SQL in particular.

The following are the main topics of this chapter:

Note:

Except where stated otherwise, the examples in this guide use the TimesTen ttIsql utility. In order to display output in the examples, the setting SET SERVEROUTPUT ON is used. For more information on ttIsql, see "ttIsql" in Oracle TimesTen In-Memory Database Reference.

PL/SQL blocks

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

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. Example 2-1 shows the basic structure of a PL/SQL block.

Note:

If you use Oracle In-Memory Database Cache: A PL/SQL block cannot be passed through to Oracle.

Example 2-1 PL/SQL block structure

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.

PL/SQL variables and constants

You can define variables and constants in PL/SQL and then use them in procedural statements and in SQL anywhere an expression can be used.

For example:

Command> DECLARE
       >   v_hiredate DATE;
       >   v_deptno   NUMBER (2) NOT NULL := 10;
       >   v_location VARCHAR2 (13) := 'San Francisco';
       >   c_comm     CONSTANT NUMBER := 1400;

You can use the %TYPE attribute to declare a variable according to either a TimesTen column definition or another declared variable. For example, use %TYPE to create variables emp_lname and min_balance:

Command> DECLARE
       >   emp_lname  employees.last_name%TYPE;
       >   balance    NUMBER (7,2);
       >   min_balance  balance%TYPE:= 1000;
       > BEGIN
       >   SELECT last_name INTO emp_lname FROM employees WHERE employee_id = 100;
       >   DBMS_OUTPUT.PUT_LINE (emp_lname);
       >   DBMS_OUTPUT.PUT_LINE (min_balance);
       > END;
       > /
King
1000
 
PL/SQL procedure successfully completed.

You can assign a value to a variable in the following ways.

Note:

The DBMS_OUTPUT package used in these examples is supplied with TimesTen. For information on this and other supplied packages, refer to Chapter 8, "TimesTen Supplied PL/SQL Packages".

Example 2-2 Assigning values to variables with the assignment operator

Command> DECLARE -- Assign values in the declarative section 
       >   wages NUMBER;
       >   hours_worked NUMBER := 40; -- Assign 40 to hours_worked
       >   hourly_salary NUMBER := 22.50; -- Assign 22.50 to hourly_salary
       >   bonus NUMBER := 150; -- Assign 150 to bonus
       >   country VARCHAR2(128);
       >   counter NUMBER := 0; -- Assign 0 to counter
       >   done BOOLEAN;
       >   valid_id BOOLEAN;
       >   emp_rec1 employees%ROWTYPE;
       >   emp_rec2 employees%ROWTYPE;
       >   TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
       >   comm_tab commissions;
       > BEGIN -- Assign values in the executable section
       >   wages := (hours_worked * hourly_salary) + bonus;
       >   country := 'France';
       >   country := UPPER('Canada');
       >   done := (counter > 100);
       >   valid_id := TRUE;
       >   emp_rec1.first_name := 'Theresa';
       >   emp_rec1.last_name := 'Bellchuck';
       >   emp_rec1 := emp_rec2;
       >   comm_tab(5) := 20000 * 0.15;
       > END;
       > /
 
PL/SQL procedure successfully completed.

Note:

This example uses records, which are composite data structures that have fields with different data types. You can use the %ROWTYPE attribute, as shown, to declare a record that represents a row in a table or a row from a query result set. Records are further discussed under "PL/SQL composite data types".

Example 2-3 Using SELECT INTO to assign values to variables

Select 10% of an employee's salary into the bonus variable:

Command>  DECLARE
       >    bonus NUMBER(8,2);
       >    emp_id NUMBER(6) := 100;
       >  BEGIN
       >    SELECT salary * 0.10 INTO bonus FROM employees
       >      WHERE employee_id = emp_id;
       >    DBMS_OUTPUT.PUT_LINE (bonus);
       >  END;
       >  /
2400
 
PL/SQL procedure successfully completed.

Example 2-4 Assigning values to variables as parameters of a subprogram

Declare the variable new_sal and then pass the variable as a parameter (sal) to procedure adjust_salary. Procedure adjust_salary computes the average salary for employees with job_id='ST_CLERK' and then updates sal. After the procedure is executed, the value of the variable is displayed to verify that the variable was correctly updated.

Command> DECLARE
       >   new_sal NUMBER(8,2);
       >   emp_id NUMBER(6) := 126;
       > PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
       >   emp_job VARCHAR2(10);
       >   avg_sal NUMBER(8,2);
       > BEGIN
       >   SELECT job_id INTO emp_job FROM employees
       >     WHERE employee_id = emp_id;
       >   SELECT AVG(salary) INTO avg_sal FROM employees
       >     WHERE job_id = emp_job;
       >   DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job
       >     || ' employees: ' || TO_CHAR(avg_sal));
       >   sal := (sal + avg_sal)/2;
       >   DBMS_OUTPUT.PUT_LINE ('New salary is ' || sal);
       > END;
       > BEGIN
       >   SELECT AVG(salary) INTO new_sal FROM employees;
       >   DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: '
       >     || TO_CHAR(new_sal));
       >   adjust_salary(emp_id, new_sal);
       >   DBMS_OUTPUT.PUT_LINE ('Salary should be same as new salary ' ||
       >     new_sal);
       > END;
       > /
The average salary for all employees: 6461.68
The average salary for ST_CLERK employees: 2785
New salary is 4623.34
Salary should be same as new salary 4623.34
 
PL/SQL procedure successfully completed.

Note:

This example illustrates the ability to nest PL/SQL blocks within blocks. The outer anonymous block contains an enclosed procedure. This PROCEDURE statement is distinct from the CREATE PROCEDURE statement documented in "PL/SQL procedures and functions", which creates a subprogram that will remain stored in the user's schema.

SQL function calls from PL/SQL

Most SQL functions are supported for calls directly from PL/SQL. In the first example that follows, the function RTRIM is used as a PL/SQL function in a PL/SQL assignment statement. In the second example, it is used as a SQL function in a static SQL statement.

Example 2-5 Using the RTRIM function from PL/SQL

Use the TimesTen PL/SQL RTRIM built-in function to remove the right-most "x" and "y" characters from the string. Note that RTRIM is used in a PL/SQL assignment statement.

Command> DECLARE p_var VARCHAR2(30);
       > BEGIN
       >   p_var := RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy');
       >   DBMS_OUTPUT.PUT_LINE (p_var);
       > END;
       > /
RTRIM Example
 
PL/SQL procedure successfully completed.

Example 2-6 Using the RTRIM function from SQL

Use the TimesTen SQL function RTRIM to remove the right-most "x" and "y" characters from the string. Note that RTRIM is used in a static SQL statement.

Command>  DECLARE tt_var VARCHAR2 (30);
       >  BEGIN
       >    SELECT RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy')
       >      INTO tt_var FROM DUAL;
       >    DBMS_OUTPUT.PUT_LINE (tt_var);
       > END;
       > /
RTRIM Example
 
PL/SQL procedure successfully completed.

You can refer to information about SQL functions in TimesTen under "Expressions" in Oracle TimesTen In-Memory Database SQL Reference. See "SQL Functions in PL/SQL Expressions" in Oracle Database PL/SQL Language Reference for information about support for SQL functions in PL/SQL.

PL/SQL control structures

Control structures are among the PL/SQL extensions to SQL. Oracle TimesTen In-Memory Database supports the same control structures as Oracle Database.

The following control structures are discussed here:

Conditional control

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

Example 2-7 Using the IF-THEN-ELSE and CASE constructs

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.

Example 2-8 Using a WHILE loop

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 Oracle Database in the Oracle 11g release and is also supported by TimesTen. It enables you to transfer control within a loop back to a new iteration.

Example 2-9 Using the CONTINUE statement

In this example, the first v_total assignment is executed for each of the 10 iterations of the loop. The second v_total assignment is executed for the first five iterations of the loop. The CONTINUE statement transfers control within a loop back to a new iteration, so for the last five iterations of the loop, the second v_total assignment is not executed. The end v_total value is 70.

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.

How to execute PL/SQL procedures and functions

TimesTen supports execution of PL/SQL from client applications using ODBC, OCI, Pro*C/C++, JDBC, or TimesTen TTClasses (for C++).

As noted earlier, a block is the basic unit of a PL/SQL source program. Anonymous blocks were also discussed earlier. By contrast, procedures and functions are PL/SQL blocks that have been defined with a specified name. See "PL/SQL procedures and functions" for how to define and create them.

In TimesTen, a PL/SQL procedure or function that is standalone (created with CREATE PROCEDURE or CREATE FUNCTION) or part of a package can be executed using an anonymous block or a CALL statement. (See "CALL" in Oracle TimesTen In-Memory Database SQL Reference for details about CALL syntax.)

Consider the following function:

create or replace function mytest return number is
begin
  return 1;
end;
/

In TimesTen, you can execute mytest in either of the following ways.

In Oracle Database, you could also execute mytest through a SQL statement, as follows. This execution mechanism is not supported in TimesTen.

How to pass data between an application and PL/SQL

This section covers the following topics for passing data between an application and PL/SQL:

Refer to "Bind Arguments" in Oracle Database PL/SQL Language Reference for additional information.

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 to call 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 and executing statements" 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.

Note:

For duplicate parameters, the implementation in PL/SQL in TimesTen is no different than the implementation in PL/SQL in Oracle Database.

IN, OUT, and IN OUT parameter modes

Parameter modes define whether parameters declared in a PL/SQL subprogram (procedure or function) 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".

Use of SQL in PL/SQL programs

PL/SQL is tightly integrated with the TimesTen database through the SQL language. This section covers use of the following SQL features in PL/SQL:

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

Notes:

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

Example 2-10 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".

Example 2-10 Retrieving data with SELECT...INTO

Use the SELECT... INTO statement to retrieve exactly one row of data. TimesTen returns an error for any query that returns no rows or multiple rows.

This example retrieves hire_date and salary for the employee with employee_id=100 from the employees table of the HR schema.

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 to accomplish any of the following:

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

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

  • Call a TimesTen built-in procedure. (See "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.)

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.

To call a TimesTen built-in procedure that returns a result set, create a record type and use EXECUTE IMMEDIATE with BULK COLLECT to fetch the results into an array.

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

Notes:

  • See "Differences in TimesTen: transaction behavior" for important information.

  • You cannot use EXECUTE IMMEDIATE to execute PL/SQL (either to execute an anonymous block or to call a PL/SQL stored procedure or function.)

  • When parsing a DDL statement to drop a procedure or a package, a timeout can occur if you're still using the procedure in question or a procedure in the package in question. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock times out after a short time.

  • You can also use the DBMS_SQL package for dynamic SQL. See "DBMS_SQL".

Example 2-11 Using the EXECUTE IMMEDIATE statement to create a table

Consider a situation where you do not know your table definition at compilation. By using the EXECUTE IMMEDIATE statement, you can create your table at execution time. This example creates a procedure that creates a table using the EXECUTE IMMEDIATE statement. The procedure is executed with the table name and column definitions passed as parameters, then creation of the table is verified.

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.

Execute the procedure and verify the table is 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 to move 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 FETCH, INSERT, UPDATE, and DELETE operations 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 an UPDATE, INSERT, or DELETE statement to continue executing after it 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 indicate 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".

Example 2-12 Using the FORALL statement

In the following example, the PL/SQL program increases the salary for employees with IDs 100, 102, 104, or 110. The FORALL statement bulk-binds the collection.

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.
 

Find out salaries before executing the raise_salary procedure:

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.

Execute the procedure and verify results as follows.

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) or 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 DML returning.

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 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 Database if any accessed table does not exist in TimesTen. In PL/SQL, however, 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, statements executed on a TimesTen connection will be routed to Oracle Database in most circumstances. 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.)

Important:

PL/SQL procedures and functions can use any of the following cache operations with either definer's rights or invoker's rights: loading or refreshing a cache group with commit every n rows, DML on AWT cache groups, DML on non-propagated cache groups (user managed cache groups without PROPAGATE enabled), SELECT on cache group tables that do not invoke passthrough or dynamic load, or UNLOAD CACHE GROUP.

PL/SQL procedures or functions that use any of the following cache operations must use invoker's rights (AUTHID CURRENT_USER): passthrough, dynamic loading of a cache group, loading or refreshing a cache group using WITH ID, DDL on cache groups, DML on SWT cache groups, or FLUSH CACHE GROUP.

See "Definer's rights and invoker's rights".

Use of cursors in PL/SQL programs

A cursor, either explicit or implicit, is used to handle the result set of a SELECT statement. As a programmer, you can declare an explicit cursor to manage queries that return multiple rows of data. PL/SQL declares and opens an implicit cursor for any SELECT statement that is not associated with an explicit cursor.

Important:

Be aware that in TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes any COMMIT or ROLLBACK statement. This also includes any DDL statement executed within PL/SQL, because the DDLCommitBehavior connection must be set to 0 if PL/SQL is enabled, resulting in autocommits of DDL statements.

Example 2-13 shows basic use of a cursor. See "Examples using cursors" for additional information and examples. Also see "PL/SQL REF CURSORs".

Example 2-13 Using a cursor to retrieve information about an employee

Declare a cursor c1 to retrieve the last name, salary, hire date, and job class for the employee whose employee ID is 120.

Command> DECLARE
       >   CURSOR c1 IS
       >     SELECT last_name, salary, hire_date, job_id FROM employees
       >     WHERE employee_id = 120;
       > --declare record variable that represents a row
       > --fetched from the employees table
       >   employee_rec c1%ROWTYPE;
       > BEGIN
       > -- open the explicit cursor
       > -- and use it to fetch data into employee_rec
       >   OPEN c1;
       >   FETCH c1 INTO employee_rec;
       >   DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
       >   CLOSE c1;
       > END;
       > /
Employee name: Weiss
 
PL/SQL procedure successfully completed.

PL/SQL procedures and functions

Procedures and functions are PL/SQL blocks that have been defined with a specified name.

Creating and using procedures and functions

Standalone subprograms (stored procedures or functions) are created at the database level with the CREATE PROCEDURE or CREATE FUNCTION statement.

Optionally use CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION if you want the subprogram to be replaced if it already exists.

Use ALTER PROCEDURE or ALTER FUNCTION to explicitly compile a procedure or function or modify the compilation options. (To recompile a procedure or function that is part of a package, recompile the package using the ALTER PACKAGE statement.)

In TimesTen, syntax for CREATE PROCEDURE and CREATE FUNCTION is a subset of what is supported in Oracle Database. For information on these statements and the ALTER PROCEDURE and ALTER FUNCTION statements in TimesTen, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Also see "How to execute PL/SQL procedures and functions".

Notes:

  • If you use replication: As is the case with a CREATE statement for any database object, CREATE statements for PL/SQL functions and procedures are not replicated.

  • If you use Oracle In-Memory Database Cache: An Oracle-resident PL/SQL procedure or function cannot be called in TimesTen by passthrough. Procedures and functions must be defined in TimesTen to be executable in TimesTen.

  • PL/SQL and database object names: TimesTen does not support non-ASCII or quoted non-uppercase names of PL/SQL objects (procedures, functions, and packages). Also, trailing spaces in the quoted names of PL/SQL objects are not supported. In addition, trailing spaces in the quoted names of objects such as tables and views that are passed to PL/SQL are silently removed.

  • Definer's rights or invoker's rights determines access to SQL objects used by a PL/SQL procedure or function. For information, refer to "Definer's rights and invoker's rights".

  • See "Showing errors in ttIsql" for how to get information when you encounter errors in compiling a procedure or function.

Example 2-14 Create and execute a procedure with OUT parameters

This example creates a procedure that uses OUT parameters, executes the procedure in an anonymous block, then displays the OUT values. The procedure takes an employee ID as input then outputs the salary and job ID for the employee.

Command>  CREATE OR REPLACE PROCEDURE get_employee
       >    (p_empid in employees.employee_id%TYPE,
       >     p_sal OUT employees.salary%TYPE,
       >     p_job OUT employees.job_id%TYPE) IS
       > BEGIN
       >   SELECT salary,job_id
       >   INTO p_sal, p_job
       >   FROM employees
       >   WHERE employee_id = p_empid;
       > END;
       > /
 
Procedure created.
 
Command> VARIABLE v_salary NUMBER;
Command> VARIABLE v_job VARCHAR2(15);
Command> BEGIN
       >   GET_EMPLOYEE (120, :v_salary, :v_job);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT
V_SALARY             : 8000
V_JOB                : ST_MAN

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

Note:

Instead of using the anonymous block shown in the preceding example, you could use a CALL statement:
Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job);

Example 2-15 Create and call a function

This example creates a function that returns the salary of the employee whose employee ID is specified as input, then calls the function and displays the result that was returned.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >    v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.
 
Command> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N                    : 24000

Note:

Instead of using the CALL statement shown in the preceding example, you could use an anonymous block:
Command> begin
       >   :n := get_sal(100);
       > end;
       > /

Using synonyms for procedures and functions

TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.

To create a private synonym for procedure foo in your schema:

CREATE SYNONYM synfoo FOR foo;

To create a public synonym for foo:

CREATE PUBLIC SYNONYM pubfoo FOR foo;

A private synonym exists in the schema of a specific user and shares the same namespace as database objects such as tables, views, and sequences. A private synonym cannot have the same name as a table or other object in the same schema.

A public synonym does not belong to any particular schema, is accessible to all users, and can have the same name as any private object.

To use a synonym you must have appropriate privileges to access the underlying object. For required privileges to create or drop a synonym, see "Required privileges for PL/SQL statements and operations".

For general information about synonyms, see "Understanding synonyms" in Oracle TimesTen In-Memory Database Operations Guide. For information about the CREATE SYNONYM and DROP SYNONYM statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Example 2-16 Use a synonym for a procedure

In the following example, USER1 creates a procedure in his schema and creates a public synonym for it. Then USER2 executes the procedure through the public synonym. Assume the following:

  • USER1 has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE PUBLIC SYNONYM privileges.

  • USER2 has been granted CREATE SESSION and EXECUTE ANY PROCEDURE privileges.

  • Both users have connected to the database.

  • USER2 employs the SET SERVEROUTPUT ON setting.

USER1:

Command> create or replace procedure test is
       > begin
       > dbms_output.put_line('Running the test');
       > end;
       > /
 
Procedure created.
 
Command> create public synonym pubtest for test;
 
Synonym created.

USER2:

Command> begin
       > pubtest;
       > end;
       > /
Running the test
 
PL/SQL procedure successfully completed.

PL/SQL packages

This section discusses how to create and use PL/SQL packages.

For information about PL/SQL packages provided with TimesTen, refer to Chapter 8, "TimesTen Supplied PL/SQL Packages."

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.

Note:

The syntax for creating packages and package bodies is the same as in Oracle Database; however, while Oracle documentation mentions that you must run a script named DBMSSTDX.SQL, this does not apply to TimesTen.

Creating and using packages

To create packages and store them permanently in the 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 referred to as public objects and can be referenced outside the package, and 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, referred to as 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 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.

Note:

See "Showing errors in ttIsql" for how to get information when you encounter errors in compiling a package.

Example 2-17 Create and use a package

Consider the case where you want to add a row to the employees tables when you hire a new employee and delete a row from the employees table when an employee leaves your company. The following example creates two procedures to accomplish these tasks and bundles the procedures in a package. The package also contains a function to return the count of employees with a salary greater than that of a specific employee. The example then executes the function and procedures and verifies the results.

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.

Verify the count of 33.

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.

Now add an employee and verify results. Then, remove the employee and verify that the employee was deleted from the employees table.

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.

Using synonyms for packages

TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.

To create a private synonym for package foopkg in your schema:

CREATE SYNONYM synfoopkg FOR foopkg;

To create a public synonym for foopkg:

CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;

Also see "Using synonyms for procedures and functions" and "Required privileges for PL/SQL statements and operations".

Note:

You cannot create synonyms for individual member subprograms of a package.

This is valid:

create or replace public synonym pubtestpkg for testpkg;

This is not valid:

create or replace public synonym pubtestproc for testpkg.testproc;

Wrapping PL/SQL source code

Wrapping is the process of hiding PL/SQL source code. You can wrap PL/SQL source code with the wrap utility, processes an input SQL file and wraps only the PL/SQL units in the file, such as a package specification, package body, function, or procedure.

Consider the following example that creates a file named wrap_test.sql, the purpose of which is to create a procedure named wraptest. It then uses the wrap utility to process wrap_test.sql. The procedure is created with the source code hidden and executes successfully. As a final step, the ALL_OBJECTS view is queried to see the wrapped source code.

$ cat wrap_test.sql
CREATE OR REPLACE PROCEDURE wraptest IS
 TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
 all_emps emp_tab;
BEGIN
 SELECT * BULK COLLECT INTO all_emps FROM employees;
 FOR i IN 1..10 
   LOOP
     DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id);
   END LOOP;
END;
/

$ wrap iname=wrap_test.sql
 
PL/SQL Wrapper: Release 11.1.0.7.0- Production on Thu Sep 11 23:27:04 2008
 
Copyright (c) 1993, 2009, Oracle. All rights reserved.
 
Processing wrap_test.sql to wrap_test.plb

$ cat wrap_test.plb
CREATE OR REPLACE PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
109 124
88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D
LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY
Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM
xKpGTRsHj7Al9eLe4pAutkqgVVDBveT5RrLRnKoGp79VjbFXinShf9huGTE9mnPh2CJgUw==
 
 
/
$ ttIsql SampleDatabase
 
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
 
 
connect "DSN=SampleDatabase";
Connection successful: ... PermSize=32;TypeMode=0;PLSQL_MEMORY_ADDRESS=20000000;
(Default setting AutoCommit=1)
Command> @wrap_test.plb
 
CREATE OR REPLACE PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
109 124
88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D
LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY
Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM
Command> SET SERVEROUTPUT ON;
Command> BEGIN
       >  wraptest ();
       > END;
       > /
Emp Id: 100
Emp Id: 101
Emp Id: 102
Emp Id: 103
Emp Id: 104
Emp Id: 105
Emp Id: 106
Emp Id: 107
Emp Id: 108
Emp Id: 109
 
PL/SQL procedure successfully completed.
 

Command> SELECT text FROM all_source WHERE name = 'WRAPTEST';
< PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
109 124
88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D
LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY
Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM
xKpGTRsHj7Al9eLe4pAutkqgVVDBveT5RrLRnKoGp79VjbFXinShf9huGTE9mnPh2CJgUw==
 >
1 row found.

Differences in TimesTen: transaction behavior

In TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes the following:

For example, consider the following scenario, where you want to recompile a set of procedures. This would not work, because the first time ALTER PROCEDURE is executed, the cursor (pnamecurs) would be closed:

declare
   cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
begin
   for rec in pnamecurs loop
     execute immediate 'alter procedure ' || rec.object_name || ' compile';
   end loop;
end;
/

Instead, you can do something like the following, which fetches all the procedure names into an internal table then executes ALTER PROCEDURE on them with no active cursor:

declare
   cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
   type tbl is table of c%rowtype index by binary_integer;
   myprocs tbl;

begin
   open pnamecurs;
   fetch pnamecurs bulk collect into myprocs;
   close pnamecurs;
   for i in 1..myprocs.count loop
     execute immediate 'alter procedure ' || myprocs(i).object_name || ' compile';
   end loop;
end;
/