CREATE PROCEDURE

Create a stored stand-alone procedure.

Syntax:

   CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options)
      invoker_rights AS plsql_sub_program_body

   CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options)
      invoker_rights AS LANGUAGE JAVA NAME ('string')

   CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options)
      invoker_rights AS LANGUAGE C NAME name LIBRARY lib_name 
        [AGENT IN (argument)] [WITH CONTEXT][PARAMETERS (parameters)]

options: 
      argument IN [NOCOPY] datatype
      argument OUT [NOCOPY] datatype
      argument IN OUT [NOCOPY] datatype
     (The procedure can have several arguments separated with commas)

invoker_rights:
      AUTHID CURRENT_USER
      AUTHID DEFINER

Oracle does not allow a specific precision for function parameters, only type of the variable. This means that a parameter defined as integer or number(38) can accept fractional values. if you want to have an integer passed to a procedure/function, use pls_integer instead of integer.

Alternatively reassigning to a variable within the procedure will force Oracle to implicitly convert the datatype and precision to match the variable, thie requires a slightly different definition, e.g. number(37) .

AUTHID DEFINER will execute with the privileges of the procedure schema/owner.

NOCOPY will instruct Oracle to pass the argument as fast as possible. This can significantly enhance performance when passing a large value.

"All the world's a stage,
and all the men and women merely players:
They have their exits and their entrances;
and one man in his time plays many parts" ~ William Shakespeare (As You Like It)


Related Commands:

ALTER PROCEDURE
DROP PROCEDURE
LIBRARY - CREATE LIBRARY

Related Views:

 CODE_PIECES
 CODE_SIZE
 DBA_SOURCE      ALL_SOURCE      USER_SOURCE


© Copyright SS64.com 1999-2016
Some rights reserved