PL/SQL support in Oracle TimesTen is seamlessly integrated within the TimesTen database and is available from all supported TimesTen programming interfaces, ODBC, JDBC, OCI, Pro*C, and TTClasses (C++). TimesTen PL/SQL uses the same language syntax and semantics as supported in the Oracle database. In this release, a subset of the PL/SQL packages targeted for performance critical OLTP applications are available. For details of supported PL/SQL functionality, refer to the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
IMPORTANT PRE-REQUISITES
1. Set up sample database and user accounts
The following build_sampledb script should be run once to set up the sample database and user accounts
Windows: > cd quickstart/sample_scripts/createdb
> build_sampledb.batUnix/Linux: $ cd quickstart/sample_scripts/createdb
$ ./build_sampledb.sh2. Set up environment to compile and run the sample programs
The following script must be run for each of the terminal session:
Windows: > quickstart/ttquickstartenv.bat Unix/Linux: $ . quickstart/ttquickstartenv.sh OR
$ source quickstart/ttquickstartenv.csh
How to run the sample PL/SQL code
To run the PL/SQL sample code using ttIsql, do
$ttIsql "dsn=<name>"
> @<plsql_filename>;
OR
ttIsql -f <filename> <dsn>
Examples:
$ttIsql "dsn=sampledb_1121; uid=appuser"
> enter the password for appuser
> @basics.sql;
$ttIsql -f basics.sql "dsn=sampledb_1121; uid=appuser"
Procedures | Description |
basics.sql | This PL/SQL script gives an overview of the functionality of TimesTen PL/SQL via ttIsql.
Features shown include:
This PL/SQL script should be executed from ttIsql. |
case_procedures.sql | This PL /SQL block demonstrates the usage of the CASE statement and CASE expression. The PL/SQL CASE statement includes the simple CASE statement and the searched CASE statement. The CASE expression includes the simple CASE expression, the searched CASE expression, plus two syntactic shorthands, COALESCE and NULLIF.
For comparison, each stored procedure is also implemented using traditional language elements, such as a sequence of IF statements or the SQL DECODE function. This PL/SQL block should be executed from ttIsql and output is displayed using DBMS_OUTPUT to the console. |
cursor_loop.sql |
This PL /SQL block uses a CURSOR and a LOOP to find and conditionally insert all employees whose monthly wages (salary plus commission) are higher than $2000. A variable of type %ROWTYPE is used to FETCH a row into. EXIT is used to quit the LOOP.
This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program. |
cursor_loop_types.sql | This PL /SQL block show the use of the %TYPE datatype to declare variables of the same type as database columns. These types are then used in the CURSOR and some simple math is performed on the fetched data. WHEN %NOTFOUND is used to exit the LOOP.
This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program. |
cursor_loop_types2.sql | This PL/SQL block show the use of a FOR-LOOP based on a CURSOR. The CURSOR resultset is used to exit the LOOP.
This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program. |
cursor_update_logic.sql | This program modifies the ACCOUNT table based on instructions stored in the ACTION table. Each row of the ACTION table contains an account number to act upon, an action to be taken (insert, update, or delete), an amount by which to update the account, and a time tag.
On an insert, if the account already exists, an update is performed instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken. This program can be executed from ttIsql [or from a TimesTen API] and output goes to the ACCOUNT and ACTION tables. Check the values in the ACTION and ACCOUNT tables before and after running this program. |
inner_loop_block.sql | This PL/SQL block illustrates block structure and scope rules. An outer block declares two variables named X and COUNTER, and loops four times. Inside the loop is a sub-block that also declares a variable named X. The values inserted into the TEMP table show that the two Xs are indeed different.
This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program. |
loop_insert.sql | This sample PL/SQL block uses a simple FOR loop to insert 10 rows into a table. The values of a loop index, counter variable, and either of two character strings are inserted. The inserted string depends on the value of the loop index.
This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program. |
select_exception.sql | This sample block shows an example of PL/SQL EXCEPTION handling.
It calculates the ratio between the X and Y columns of the RATIO table. If the ratio is greater than 0.72, the block inserts the ratio into RESULT_TABLE. Otherwise, it inserts -1. If the denominator is zero, ZERO_DIVIDE is raised, and a zero is inserted into RESULT_TABLE. This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the RATIO table. Check the values in the RATIO table before and after running this program. |
update_inventory.sql | This PL/SQL block processes orders for tennis rackets. It decrements the quantity of rackets on hand only if there is at least one racket left in stock.
This program can be executed from ttIsql [or from a TimesTen API] and output goes to the INVENTORY2 and PURCHASE_RECORD tables. Check the values in the INVENTORY2 and PURCHASE_RECORD tables before and after running this program. |
For more information on PL/SQL support in TimesTen, refer to the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
Home | Site Map | Contact Us | Copyright © 2011 |