Small programs or subprograms within a program are known as

To call it, a programmer uses the function's identifier , the value to be passed into the function, and a variable for the function to return a value into, for example:. This would result in the value of Celsius being zero. Many languages include built-in, ready-made functions:. Additionally, some languages allow functions to be added in from external files called libraries.

Libraries contain pre-written, tested functions that extend the functionality of a language. Using subprograms to produce structured code Subprograms are small programs that are written within a larger, main program.

There are two types of subprogram: procedures functions Benefits of using subprograms Subprograms are usually small in size, meaning they are much easier to write, test and debug. They are also easy for someone else to understand. Subprograms can be saved separately as modules and used again in other programs. This saves time because the programmer can use code that has already been written, tested and debugged.

With the by-value method, the value of an actual parameter is passed to the subprogram. With the by-reference method, only a pointer to the value is passed, in which case the actual and formal parameters reference the same item. The NOCOPY compiler hint increases the possibility of aliasing that is, having two different names refer to the same memory location. This can occur when a global variable appears as an actual parameter in a subprogram call and then is referenced within the subprogram.

The result is indeterminate because it depends on the method of parameter passing chosen by the compiler. The result depends on the method of parameter passing chosen by the compiler.

So, changing one does not affect the other. Hence, the term "aliasing. Aliasing can also occur when the same actual parameter appears more than once in a subprogram call. In the example below, n2 is an IN OUT parameter, so the value of the actual parameter is not updated until the procedure exits. Because they are pointers, cursor variables also increase the possibility of aliasing. Consider the example below. So, both can alter its state. That is, you can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family.

Suppose you want to initialize the first n rows in two index-by tables that were declared as follows:. Because the processing in these two procedures is the same, it is logical to give them the same name.

You can place the two overloaded initialize procedures in the same block, subprogram, or package. Only local or packaged subprograms can be overloaded.

Therefore, you cannot overload stand-alone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:. Furthermore, you cannot overload two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family.

Likewise, you cannot overload two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family. Finally, you cannot overload two functions that differ only in return type the datatype of the result value even if the types are in different families. For example, you cannot overload the following functions:.

When the compiler encounters a procedure or function call, it tries to find a declaration that matches the call. The compiler searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler stops searching if it finds one or more subprogram declarations in which the subprogram name matches the name of the called subprogram.

To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. That is, they must match in number, order, and datatype unless some formal parameters were assigned default values.

If no match is found or if multiple matches are found, the compiler generates a syntax error. In the following example, you call the enclosing procedure swap from within the function valid. However, the compiler generates an error because neither declaration of swap within the current scope matches the procedure call:.

Redeclaring them locally is error prone because your local declaration overrides the global declaration. Consider the following example, in which you declare a function named sign , then within the scope of that declaration, try to call the built-in function SIGN :. To call the built-in function from inside the sub-block, you must use dot notation, as follows:. However, it is specialized for SQL transaction processing.

So, some tasks are more quickly done in a lower-level language such as C, which is very efficient at machine-precision calculations.

Other tasks are more easily done in a fully object-oriented, standardized language such as Java. This makes the strengths and capabilities of those languages available to you. No longer are you restricted to one language with its inherent limitations. Suppose you store the following Java class in the database:. The class Adjuster has one method, which raises the salary of an employee by a given percentage. Because raiseSalary is a void method, you publish it as a procedure using this call spec:.

Typically, external C routines are used to interface with embedded systems, solve engineering problems, analyze data, or control real-time devices and processes. Moreover, external C routines enable you to extend the functionality of the database server, and to move computation-bound programs from client to server, where they will execute faster. By default, stored procedures and SQL methods execute with the privileges of their definer, not their invoker.

Such definer-rights routines are bound to the schema in which they reside. For example, assume that duplicates of table dept reside in schema scott and schema blake , and that the following stand-alone procedure resides in schema scott :. Also, the unqualified reference to table dept is resolved in schema scott.

So, the procedure updates the dept table in schema scott , not schema blake. How can routines in one schema manipulate objects in another schema? One way is to fully qualify references to the objects, as in. However, that hampers portability. Another way is to copy the routines into the other schema. However, that hampers maintenance. Such invoker-rights routines are not bound to a particular schema. They can be run by a variety of users, and their definer need not know who those users will be.

Also, the unqualified reference to table dept is resolved in the schema of the invoker, not the definer. Invoker-rights routines let you centralize data retrieval. They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base. Consider a company that uses a definer-rights procedure to analyze sales.

To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, as Figure shows, the procedure must also reside at each regional site.

This causes a maintenance problem. To solve the problem, the company installs an invoker-rights version of procedure analyze at headquarters. Now, as Figure shows, all regional sites can use the same procedure to query their own sales tables.

Invoker-rights routines also let you restrict access to sensitive data. Suppose headquarters would like procedure analyze to calculate sales commissions and update a central payroll table. That presents a problem because invokers of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data.

To implement invoker rights, use the AUTHID clause, which specifies whether a routine executes with the privileges of its definer or its invoker. It also specifies whether external references that is, references to objects outside the routine are resolved in the schema of the definer or the invoker.

The header syntax follows:. However, this applies only to external references in. PARSE For all other statements, the privileges of the definer are checked at compile time, and external references are resolved in the schema of the definer. For example, the assignment statement below refers to a packaged function. This external reference is resolved in the schema of the definer of procedure reconcile.

External references in an invoker-rights routine are resolved in the schema of the invoker at run time. So, the definer must create template objects in his or her schema beforehand. At run time, the template objects and the actual objects must match. Otherwise, you get an error or unexpected results.

For example, suppose user scott creates the following database table and stand-alone procedure:. Now, suppose user blak e creates a similar database table, then calls procedure evaluate , as follows:.

That is because the actual table in the schema of the invoker does not match the template table used at compile time. Occasionally, you might want to override the default invoker-rights behavior.

Suppose user scott defines the stand-alone procedure below. Normally, this external reference would be resolved in the schema of the invoker. This works unless the invoker has defined a function or private synonym named median. This works unless the invoker has defined a package named scott that contains a function named median.

By granting the privilege, you allow a user to. Now, user app can compile functions and procedures that call routine fft. At run time, no privilege checks on the calls are done. Notice that routine util. When util. For invoker-rights routines executed within a view expression, the view owner, not the view user, is considered to be the invoker. For example, suppose user scott creates a view, as follows:. The function layout always executes with the privileges of user scott , who is the view owner.

External references to database links are resolved in the usual way. However, the link determines which user is connected to the remote database. With named links, the username specified in the link is used. Suppose a routine owned by user blake references the database link below. No matter who calls the routine, it connects to the Chicago database as user scott.

With anonymous links, the session username is used. If user scott logs on, and then calls the routine, it connects to the Atlanta database as user scott. With privileged links, the username of the invoker is used. Suppose an invoker-rights routine owned by user blake references the database link below.

If global user scott calls the routine, it connects to the Dallas database as user scott , who is the current user. If it were a definer-rights routine, the current user would be blake. So, the routine would connect to the Dallas database as global user blake. An invoker-rights instance method executes with the privileges of the invoker, not the creator of the instance.

Suppose that Person is an invoker-rights object type, and that user scott creates p1 , an object of type Person. Consider the following example:. Recursion is a powerful technique for simplifying the design of algorithms.

Basically, recursion means self-reference. In a recursive mathematical sequence, each term is derived by applying a formula to preceding terms. For example, a procedure may be written to reset all the values of an array to zero, or add some values together. A procedure is created using the following pseudo-code syntax :.

A parameter allows a value to be passed in to the procedure. The name of each parameter can be used inside the procedure to access the value the procedure was called with. This is illustrated in the next example. The following procedure - called add2numbers - takes two parameters called number1 and number2 , adds the values assigned to them together and displays the answer:.

A procedure is run by calling it. To call it, programmers use the procedure name and include any parameter values that the procedure needs, eg:. This prints the number 7 on the screen. A function works in the same way as a procedure, except that it processes data and returns a result back to the main program.

For example, a function might be written to turn Fahrenheit into Celsius, eg:.