For information about the dependencies of procedures, functions, and packages, and how Oracle manages these dependencies, see Chapter 19, "Oracle Dependency Management". It extends SQL with flow control and other statements that make it possible to write complex programs in it. This engine is a special component of many Oracle products, including the Oracle server. Procedures and functions are created in a user's schema and stored in a database for continued use.
Figure illustrates a simple procedure that is stored in the database and called by several different database applications.
Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. For simplicity, the term "procedure" as used in the remainder of this chapter means "procedure or function". The stored procedure in Figure , which inserts an employee record into the EMP table, is shown in Figure A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit.
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. This means that a large procedure that initially fit into memory, may not fit into contiguous memory when it is reloaded after paging-out.
Consider a problem that occurs when the body of a package has been paged out of the instance? Fragmentation then occurs, and the server cannot find enough contiguous memory to re-load the package body, resulting in an ORA error. To prevent paging, packages can be marked as non-swappable, telling the database that after their initial load, they must always remain in memory.
This is called "pinning," or "memory fencing. NOTE: Only packages can be pinned. Stored procedures should be placed into a package if they are to be pinned. The choice of whether to "pin" a procedure in memory is a function of the size of the object and the frequency that it is used.
Very large procedures that are called frequently might benefit from pinning, but you might never notice any difference because the frequent calls to the procedure have kept it loaded into memory. Therefore, since the object never pages-out, the pinning has no effect.
Reality, however, dictates that the shared pool cannot grow indefinitely, and wise choices must be made regarding which objects are fenced. A standard procedure can be written to pin all of the recommended Oracle packages into the shared pool. Here is the script:. This ensures that all packages are re-pinned with each bounce of the box.
The DBA also needs to remember to run pin. Here is a handy script to look at pinned packages in the SGA:. There is an easy way to tell the number of times that a non-pinned stored procedure was swapped-out of memory and required a reload. This parameter allows to give input to the subprograms and to extract from these subprograms. Based on their purpose parameters are classified as.
RETURN is the keyword that instructs the compiler to switch the control from the subprogram to the calling statement.
Normally, parent or main block will call the subprograms, and then the control will shift from those parent block to the called subprograms. The datatype of this value is always mentioned at the time of function declaration. This subprogram unit in the Oracle database is stored as a database object. Note: Subprogram is nothing but a procedure, and it needs to be created manually as per the requirement. Once created they will be stored as database objects.
In this example, we are going to create an Oracle procedure that takes the name as input and prints the welcome message as output. Stored procedures increase scalability by isolating application processing on the server. In addition, automatic dependency tracking for stored procedures helps in developing scalable applications. After a stored procedure is validated, you can use it with confidence in any number of applications. If its definition changes, then only the procedure is affected, not the applications that call it.
This simplifies maintenance and enhancement. Also, maintaining a procedure on the server is easier than maintaining copies on different client computers. Java in Oracle Database fully conforms to the Java Language Specification JLS and furnishes all the advantages of a general-purpose, object-oriented programming language. With Oracle Advanced Replication, you can replicate stored procedures from one Oracle Database instance to another.
This enables you to use stored procedures to implement a central set of business rules.
0コメント