Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

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

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

CREATE PROCEDURE

The CREATE PROCEDURE statement creates a standalone stored procedure.

Required privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner)

SQL syntax

CREATE [OR REPLACE] PROCEDURE [Owner.]ProcedureName 
     [(argument [IN|OUT|IN OUT][NOCOPY] datatype [DEFAULT expr][,...])]
     [invoker_rights_clause] [DETERMINISTIC]
     {IS|AS} plsql_procedure_body

The syntax for the invoker_rights_clause:

AUTHID {CURRENT_USER|DEFINER}

You can specify invoker_rights_clause or DETERMINISTIC in any order.

Parameters

The CREATE PROCEDURE statement has the parameters:

Parameter Description
OR REPLACE Specify OR REPLACE to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping and recreating it. When you re-create a procedure, TimesTen recompiles it.
ProcedureName Name of procedure.
argument Name of argument/parameter. You can specify 0 or more parameters for the procedure. If you specify a parameter, you must specify a data type for the parameter. The data type must be a PL/SQL data type.

For more information on PL/SQL data types, see Oracle Database PL/SQL Language Reference.

[IN| OUT |IN OUT] Parameter modes.

IN is a read-only parameter. You can pass the parameter's value into the procedure but the procedure cannot pass the parameter's value out of the procedure and back to the calling PL/SQL block.The value of the parameter cannot be changed.

OUT is a write-only parameter. Use an OUT parameter to pass a value back from the procedure to the calling PL/SQL block. You can assign a value to the parameter.

IN OUT is a read/write parameter. You can pass values into the procedure and return a value back to the calling program (either the original, unchanged value or a new value set within the procedure.

IN is the default.

NOCOPY Specify NOCOPY to instruct TimesTen to pass the parameter as fast as possible. Can enhance performance when passing a large value such as a record, an index-by-table, or a varray to an OUT or IN OUT parameter. IN parameters are always passed NOCOPY.

For more information on NOCOPY, see Oracle Database SQL Language Reference.

DEFAULT expr Use this clause to specify a DEFAULT value for the parameter. You can specify := in place of the keyword DEFAULT.
invoker_rights_clause Lets you specify whether the procedure executes with the privileges and in the database of the user who owns it or with the privileges and in the database of the CURRENT_USER.

Specify CURRENT_USER to indicate that the procedure executes with the privileges of the CURRENT_USER.

Specify DEFINER to indicate that the procedure executes with the privileges of the owner of the database in which the procedure resides.

DEFINER is the default.

For more information, see Oracle Database SQL Language Reference.

DETERMINISTIC Specify DETERMINISTIC to indicate that the procedure should return the same result value whenever it is called with the same values for its parameters.

For more information on the DETERMNISTIC clause, see Oracle Database SQL Language Reference.

IS|AS Specify either IS or AS to declare the body of the procedure.
plsql_procedure_body Specifies the procedure body.

Restrictions

TimesTen does not support:

The CREATE PROCEDURE statement is not replicated.

Description

Examples

Create a procedure query_emp to retrieve information about an employee. Pass the employee_id 171 to the procedure and retrieve the last_name and salary into two OUT parameters.

Command> CREATE OR REPLACE PROCEDURE query_emp
       >           (p_id IN employees.employee_id%TYPE,
       >            p_name  OUT employees.last_name%TYPE,
       >            p_salary OUT employees.salary%TYPE) IS
       >         BEGIN
       >           SELECT last_name, salary INTO p_name, p_salary
       >           FROM employees
       >           WHERE employee_id = p_id;
       >         END query_emp;
       >         /
 
Procedure created.

See also

Oracle Database PL/SQL Language Reference and Oracle Database SQL Language Reference