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 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:

call_spec clause
AS EXTERNAL clause

The CREATE PROCEDURE statement is not replicated.

Description

The namespace for PL/SQL procedures is distinct from the TimesTen built-in procedures. You can create a PL/SQL procedure with the same name as a TimesTen built-in procedure.
When you create or replace a procedure, the privileges granted on the procedure remain the same. If you drop and re-create the object, the object privileges that were granted on the original object are revoked.

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.