PL/SQL Procedures and Functions
Procedures and functions are PL/SQL blocks that have been defined with a specified name.
This section covers the following:
Creating Procedures and Functions
In TimesTen, you can create standalone subprograms (stored procedures or functions) at the database level with the CREATE PROCEDURE
or CREATE FUNCTION
statement.
Optionally use CREATE OR REPLACE PROCEDURE
or CREATE OR REPLACE FUNCTION
if you want the subprogram to be replaced if it already exists.
Use ALTER PROCEDURE
or ALTER FUNCTION
to explicitly compile a procedure or function or modify the compilation options. (To recompile a procedure or function that is part of a package, recompile the package using the ALTER PACKAGE
statement.)
In TimesTen, syntax for CREATE PROCEDURE
and CREATE FUNCTION
is a subset of what is supported in Oracle Database. For information on these statements and the ALTER PROCEDURE
and ALTER FUNCTION
statements in TimesTen, see SQL Statements in Oracle TimesTen In-Memory Database SQL
Reference.
Note:
ALTER
is not supported for PL/SQL objects in TimesTen Scaleout.
Executing Procedures and Functions
TimesTen supports execution of PL/SQL from client applications using ODBC, OCI, Pro*C/C++, ODP.NET, JDBC, or TimesTen TTClasses (for C++).
As noted earlier, a block is the basic unit of a PL/SQL source program. Anonymous blocks were also discussed earlier. By contrast, procedures and functions are PL/SQL blocks that have been defined with a specified name.
See PL/SQL Procedures and Functions for how to define and create them.
In TimesTen, a PL/SQL procedure or function that is standalone (created with CREATE PROCEDURE
or CREATE FUNCTION
) or part of a package can be executed using an anonymous block or a CALL
statement. (See CALL in Oracle TimesTen In-Memory Database SQL
Reference for details about CALL
syntax.)
Consider the following function:
create or replace function mytest return number is
begin
return 1;
end;
/
In TimesTen, you can execute mytest
in either of the following ways.
-
In an anonymous block:
Command> variable n number; Command> begin :n := mytest(); end; / PL/SQL procedure successfully completed. Command> print n; N : 1
-
In a
CALL
statement: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.
-
In a
SELECT
statement: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, but it is best to avoid such naming conflicts.
This example creates a procedure that uses OUT
parameters, executes the procedure in an anonymous block, then displays the OUT
values. The procedure takes an employee ID as input then outputs the salary and job ID for the employee.
Command> CREATE OR REPLACE PROCEDURE get_employee
(p_empid in employees.employee_id%TYPE,
p_sal OUT employees.salary%TYPE,
p_job OUT employees.job_id%TYPE) IS
BEGIN
SELECT salary,job_id
INTO p_sal, p_job
FROM employees
WHERE employee_id = p_empid;
END;
/
Procedure created.
Command> VARIABLE v_salary NUMBER;
Command> VARIABLE v_job VARCHAR2(15);
Command> BEGIN
GET_EMPLOYEE (120, :v_salary, :v_job);
END;
/
PL/SQL procedure successfully completed.
Command> PRINT
V_SALARY : 8000
V_JOB : ST_MAN
Command> SELECT salary, job_id FROM employees WHERE employee_id = 120;
< 8000, ST_MAN >
1 row found.
Note:
Instead of using the anonymous block shown in the preceding example, you could use a CALL
statement:
Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job);
The next example creates a function that returns the salary of the employee whose employee ID is specified as input, then calls the function and displays the result that was returned.
Command> CREATE OR REPLACE FUNCTION get_sal
(p_id employees.employee_id%TYPE) RETURN NUMBER IS
v_sal employees.salary%TYPE := 0;
BEGIN
SELECT salary INTO v_sal FROM employees
WHERE employee_id = p_id;
RETURN v_sal;
END get_sal;
/
Function created.
Command> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N : 24000
Note:
Instead of using the CALL
statement shown in the preceding example, you could use an anonymous block:
Command> begin
:n := get_sal(100);
end;
/
Using Synonyms for Procedures and Functions
TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.
Create a private synonym for procedure foo
in your schema as follows:
CREATE SYNONYM synfoo FOR foo;
Create a public synonym as follows:
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 Privileges for PL/SQL Statements and Operations in Oracle TimesTen In-Memory Database Security Guide.
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 the user's
schema and creates a public synonym for it. Then USER2
executes the procedure through the public synonym. Assume the
following:
-
USER1
has been grantedCREATE SESSION
,CREATE PROCEDURE
, andCREATE PUBLIC SYNONYM
privileges. -
USER2
has been grantedCREATE SESSION
andEXECUTE ANY PROCEDURE
privileges. -
Both users have connected to the database.
-
USER2
employs theSET SERVEROUTPUT ON
setting.
USER1:
Command> create or replace procedure test is
begin
dbms_output.put_line('Running the test');
end;
/
Procedure created.
Command> create public synonym pubtest for test;
Synonym created.
USER2:
Command> begin
pubtest;
end;
/
Running the test
PL/SQL procedure successfully completed.
Usage Notes for Procedures and Functions in TimesTen
Be aware of these usage notes for using PL/SQL procedures and functions in TimesTen.
-
If you use replication: PL/SQL DDL statements, such as
CREATE
statements for PL/SQL functions, procedures, and packages, are not replicated. See Creating a New PL/SQL Object in an Existing Active Standby Pair and Adding a PL/SQL Object to an Existing Classic Replication Scheme in Oracle TimesTen In-Memory Database Replication Guide for steps to address this. -
If you use cache: A PL/SQL procedure or function resident in Oracle Database cannot be called in TimesTen by passthrough. Procedures and functions must be defined in TimesTen to be executable in TimesTen. (Also see TimesTen PL/SQL with Cache.)
-
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. Refer to Definer's Rights and Invoker's Rights (AUTHID Clause) in Oracle TimesTen In-Memory Database Security Guide.
-
See Showing Errors in ttIsql for how to get information when you encounter errors in compiling a procedure or function.