The value of the parameter can not be overwritten by the procedure or function. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
Example Let's look at an example of how to create a procedure in Oracle. Drop Procedure Once you have created your procedure in Oracle, you might find that you need to remove it from the database. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.
Figure illustrates a package that encapsulates a number of procedures used to manage an employee database. Database applications explicitly call packaged procedures as necessary. Packages offer several development and performance advantages over standalone stored procedures see "Packages" on page Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values.
Procedures and functions allow you to combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself.
When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure but not the privileges to query, update, or delete from the underlying tables can invoke the procedure, but they cannot manipulate table data in any other way. Because stored procedures take advantage of the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users.
Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications. Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.
For example, procedures can be written to insert, update, or delete rows from the EMP table. These procedures can then be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data management change, only the procedures need to be modified, not all of the applications that use the procedures.
Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors. For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, only the procedure needs to be recompiled; applications that call the procedure do not necessarily require any modifications.
A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.
Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package. See "Packages" on page for information on the advantages of packages. A stored procedure is dependent on the objects referenced in its body. Oracle automatically tracks and manages such dependencies. The call spec tells Oracle Database which Java method to invoke when a call is made.
It also tells the database 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. Oracle Database Application Developer's Guide - Fundamentals for more information on stored procedures, including how to call stored procedures and for information about registering external procedures.
You can also create a procedure as part of a package. The exact name and location of this script depend on your operating system. 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, then Oracle Database recompiles it.
Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges. Specify the schema to contain the procedure.
0コメント