|Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
Part Number E13076-06
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
ttIsqlutility. In order to display output in the examples, the setting
SET SERVEROUTPUT ONis used. For more information on
ttIsql, 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
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.
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.
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 following).
By passing the variable as an
IN OUT parameter to a subprogram (procedure or function) and then assigning the value inside the subprogram (Example 2-4 following).
DBMS_OUTPUTpackage 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".
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
%ROWTYPEattribute, 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".
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.
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
PROCEDUREstatement is distinct from the
CREATE PROCEDUREstatement 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.
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.
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 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:
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.
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.
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 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.
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 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
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 an anonymous block.
Command> variable n number; Command> begin > :n := mytest(); > end; > / PL/SQL procedure successfully completed. Command> print n; N : 1
Command> variable n number; Command> call mytest() into :n; Command> print n; N : 1
In Oracle Database, you could also execute
mytest through a SQL statement, as follows. This execution mechanism is not supported in TimesTen.
SQL> select mytest from dual; MYTEST ---------- 1
Note:A user's own procedure takes precedence over a TimesTen built-in procedure with the same name.
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 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.
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),
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
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.
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:
You must use dynamic SQL to execute DDL statements in PL/SQL. See the next section, "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 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".
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 to accomplish any of the following:
Execute a DML statement such as
Execute a DDL statement such as
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.
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.
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".
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 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
DELETE operations multiple times. Oracle TimesTen In-Memory Database supports bulk binding, which can result in significant performance improvement.
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
SAVE EXCEPTIONS allows an
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.
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
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.
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".
Also see "Examples using RETURNING INTO".
Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for additional information about DML returning.
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.)
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
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
nrows, DML on AWT cache groups, DML on non-propagated cache groups (user managed cache groups without
SELECTon 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.
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
ROLLBACKstatement. 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.
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.
Procedures and functions are PL/SQL blocks that have been defined with a specified name.
Standalone subprograms (stored procedures or functions) are created at the database level with the
CREATE PROCEDURE or
CREATE FUNCTION statement.
CREATE OR REPLACE PROCEDURE or
CREATE OR REPLACE FUNCTION if you want the subprogram to be replaced if it already exists.
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.
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.
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
Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job);
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
CALLstatement shown in the preceding example, you could use an anonymous block:
Command> begin > :n := get_sal(100); > end; > /
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.
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 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.
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 Database; however, while Oracle documentation mentions that you must run a script named
DBMSSTDX.SQL, this does not apply to TimesTen.
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:
Create the package specification with the
CREATE PACKAGE statement.
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, 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.
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 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.
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.
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 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 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 DDL statement in PL/SQL
This is because when PL/SQL is enabled (the
PLSQL first connection attribute is set to 1), the TimesTen
DDLCommitBehavior general 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, 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; /