Why procedures in oracle

Today, most Oracle databases have only a small amount of code in stored procedures, but this is rapidly changing. There are many compelling benefits to putting all Oracle SQL inside stored procedures. These include:. One of the foremost reasons why stored procedures and triggers function faster than traditional code is related to the Oracle System Global Area SGA. After a procedure has been loaded into the SGA, it will remain there until it is paged-out of memory. Items are paged-out based on a least-recently-used algorithm.

Once loaded into the RAM memory of the shared pool, the procedure will execute very quickly, and the trick is to prevent pool-thrashing while many procedures compete for a limited amount of shared-pool memory. When tuning Oracle, there are two init. These two parameters define the size of the in-memory region that Oracle consumes on startup and determine the amount of storage available to cache data blocks, SQL and stored procedures.

Oracle also provides a construct called a "package. For example, functions and stored procedures for employees can be logically grouped together in an employee package:. Here we have encapsulated all employee "behaviors" into a single package that will be added into Oracle's data dictionary.

If we force our programmers to use stored procedures, the SQL moves out of the external programs, and the application programs become nothing more than a series of calls to Oracle stored procedures.

As systems evolve and the majority of process code resides in stored procedures, Oracle? The shared pool consists of the following sub-pools:. The shared pool utilizes a "least recently used" algorithm to determine which objects get paged-out of the shared pool. As this paging occurs, "fragments," or discontiguous chunks of memory, are created within the shared pool. Skip to content. Report a Bug. Previous Prev. Next Continue. Home Testing Expand child menu Expand. SAP Expand child menu Expand.

Web Expand child menu Expand. Must Learn Expand child menu Expand. Big Data Expand child menu Expand. So, since we have already gone through the syntax of procedures.

Let us now discuss actually how procedures get executed. So one important point to keep in mind since procedures is a named subprogram is stored in the schema level in the database. So, it is stored in the database until we drop the procedure. A procedure consists of three parts. The first part is declarative, the second one is executable and the third one is an exception.

The control first goes to the declarative part contains the declaration of cursors, types, constants, expressions and also nested subprograms. The declared items are local to the procedure and ceased to exist as soon as the procedure completes execution.

After that, we have the executable part in which we actually manipulate the data by executing the statements. If we get any error while execution than we will have an exception handling section where the exception is handled during run-time.

One point to keep in mind is that both the declarative and Exception Handling section is optional. It is a good practiced to handle run-time exceptions in procedures. 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.