|Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
Part Number E13076-04
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.
Main features include:
Note:Except where stated otherwise, the examples in this guide use the TimesTen
ttIsqlutility. In order to display output in the examples, the setting SET SERVEROUTPUT ON is used. For more information on the
ttIsqlutility, see "ttIsql" in Oracle TimesTen In-Memory Database Reference.
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 in the TimesTen database 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.
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.
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
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.
With the assignment operator (
:=) (Example 2-2).
By selecting or fetching values into it (Example 2-3).
By passing the variable as an OUT or IN OUT parameter to a subprogram and then assigning the value inside the subprogram (Example 2-4).
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
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 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.
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:
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.
Control structures are among the most important 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:
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.
Example 2-7 Using the IF-THEN-ELSE and CASE statements
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.
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:
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.
The CONTINUE statement was added to 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.
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, also called subprograms, 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 must be executed in an anonymous block.
Consider the following function:
create or replace function mytest return number is begin return 1; end; /
In TimesTen, you can execute
mytest as follows:
Command> declare > n number; > begin > n := mytest; > end; > / PL/SQL procedure successfully completed.
In Oracle, you could also execute
mytest through a CALL statement or from a SQL statement, as follows. These execution mechanisms are not supported in TimesTen.
In a CALL statement:
SQL> variable n number; SQL> call mytest() into :n; Call completed.
In a SELECT statement:
SQL> select mytest from dual; MYTEST ---------- 1
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.
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 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.
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.
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:
From within PL/SQL, you can execute the following as static SQL:
DML statements: INSERT, UPDATE, DELETE, and MERGE
Transaction control: COMMIT and ROLLBACK
You must use dynamic SQL to execute DDL statements in PL/SQL. See "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".
See "Differences in TimesTen: transaction behavior" for important information.
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".
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
salary for the employee with
employee_id=100 from the
employees table of the
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.
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. (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.
See "Differences in TimesTen: transaction behavior" for important information.
When parsing a DDL statement to drop a procedure or a package, a deadlock 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.
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 *)
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 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
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;
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".
Also see "Examples using RETURNING INTO".
Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for additional information about RETURNING INTO.
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.)
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
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.)
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 more than one row 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
DDLCommitBehaviorconnection must be set to 0 if PL/SQL is enabled, resulting in autocommits of DDL statements.
Example 2-13 Using a cursor to retrieve information about an employee
Declare a cursor
c1 to retrieve 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.
Procedures and functions are PL/SQL blocks that have been defined with a specified name. They are also called subprograms.
Standalone subprograms are created at the database level with the CREATE PROCEDURE or CREATE FUNCTION statements.
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 if you want 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. 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.
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.
An example of creating a procedure using OUT parameters follows.
Example 2-14 Create a procedure with OUT parameters
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.
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
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-15 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 has SET SERVEROUTPUT ON.
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.
Command> begin > pubtest; > end; > / Running the test PL/SQL procedure successfully completed.
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."
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; however, while Oracle documentation mentions that you must run a script called
DBMSSTDX.SQL, this does not apply to TimesTen.
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:
Create the package specification with the CREATE PACKAGE statement.
Optionally use CREATE OR REPLACE PACKAGE if you want the package specification to be replaced if it already exists.
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.
Note:See "Showing errors in ttIsql" for how to get information when you encounter errors in compiling a package.
Example 2-16 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
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.
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
CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;
Note:You cannot create synonyms for individual member subprograms of a package.
create or replace public synonym pubtestpkg for testpkg;
create or replace public synonym pubtestproc for testpkg.testproc;
Wrapping is the process of hiding PL/SQL source code. You can wrap PL/SQL source code with the
wrap utility. 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 called
wrap_test.sql, the purpose of which is to create a procedure called
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 220.127.116.11.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.
In TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes the following:
Any COMMIT or ROLLBACK statement
Any DDL statement in PL/SQL
This is because when PL/SQL is enabled (
PLSQL connection attribute is set to 1), the TimesTen
DDLCommitBehavior connection attribute must be set to 0 for Oracle mode (autocommit DDL).
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. This 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; /