Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 19 of 20
Java_declaration::=
C_declaration::=
To create a standalone stored procedure or a call specification.
A procedure is a group of PL/SQL statements that you can call by name. A call specification ("call spec") declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle which Java method to invoke when a call is made. It also tells Oracle what type conversions to make for the arguments and return value.
Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.
See Also:
|
Before creating a procedure, the user SYS must run the SQL script DBMSSTDX.SQL
. The exact name and location of this script depends on your operating system.
To create a procedure in your own schema, you must have the CREATE
PROCEDURE
system privilege. To create a procedure in another user's schema, you must have CREATE
ANY
PROCEDURE
system privilege. To replace a procedure in another schema, you must have the ALTER
ANY
PROCEDURE
system privilege.
To invoke a call spec, you may need additional privileges (for example, EXECUTE
privileges on the C library for a C call spec).
To embed a CREATE
PROCEDURE
statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC
followed by the embedded SQL statement terminator for the specific language.
See Also:
PL/SQL User's Guide and Reference or Oracle8i Java Stored Procedures Developer's Guide for more information on such prerequisites. |
|
re-creates the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, Oracle recompiles it. For information on recompiling procedures, see "ALTER PROCEDURE". |
|
|
Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.
If any function-based indexes depend on the package, Oracle marks the indexes |
|
schema |
is the schema to contain the procedure. If you omit schema, Oracle creates the procedure in your current schema. |
|
procedure |
is the name of the procedure to be created.
If creating the procedure results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SQL*Plus command |
|
argument |
is the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name. |
|
|
specifies that you must specify a value for the argument when calling the procedure. |
|
|
specifies that the procedure passes a value for this argument back to its calling environment after execution. |
|
|
specifies that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution. |
|
|
If you omit |
|
|
instructs Oracle to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an |
|
|
|
|
|
These effects may or may not occur on any particular call. You should use |
|
datatype |
is the datatype of the argument. An argument can have any datatype supported by PL/SQL. |
|
|
Datatypes cannot specify length, precision, or scale. For example, |
|
invoker_rights_clause |
lets you specify whether the procedure executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the procedure. See Also: PL/SQL User's Guide and Reference. |
|
|
|
specifies that the procedure executes with the privileges of
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of |
|
|
specifies that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default. |
pl/sql_subprogram_body |
declares the procedure in a PL/SQL subprogram body. See Also: Oracle8i Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms. |
|
call_spec |
maps a Java or C method name, parameter types, and return type to their SQL counterparts.
See Also: Oracle8i Java Stored Procedures Developer's Guide. |
|
|
|
is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the |
The following statement creates the procedure CREDIT
in the schema SAM
:
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no; END;
The CREDIT
procedure credits a specified bank account with a specified amount. When you call the procedure, you must specify the following arguments:
|
is the number of the bank account to be credited. The argument's datatype is |
|
is the amount of the credit. The argument's datatype is |
The procedure uses an UPDATE
statement to increase the value in the BALANCE
column of the ACCOUNTS
table by the value of the argument AMOUNT
for the account identified by the argument ACC_NO
.
In the following example, external procedure C_FIND_ROOT
expects a pointer as a parameter. Procedure FIND_ROOT
passes the parameter by reference using the BY REF
phrase:
CREATE PROCEDURE find_root ( x IN REAL ) IS LANGUAGE C NAME "c_find_root" LIBRARY c_utils PARAMETERS ( x BY REF );
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|