User-Defined Functions


Even though many functions come prewritten and packaged with the default installation of the Oracle software, sometimes you need some functionality that cannot be provided by those built-in functions. Oracle’s programming language, PL/SQL, which stands for Programming Language SQL, can come to the rescue.

The advanced techniques on how functions, procedures, and packages are constructed and used are beyond the scope of this book. Here, you’ll get an introduction to user-defined functions, including a look at how you could write a custom function that’s available to all database users.

user-defined function

A function that is written by an analyst, user, or database adminstrator and does not come as part of the default installation of the Oracle server software.

Using PL/SQL, a database analyst, database user, or a database administrator can construct a user-defined function. A user-defined function has the same characteristics as a built-in function. It will take zero, one, or more values and return a single value as its result. Functions in Oracle, whether they are built-in or written by a developer or DBA, are often known as stored functions, since the source code and the compiled code are both stored in the database.

stored function

A sequence of PL/SQL variable declarations and statements that can be called as a unit, passing zero or more arguments and returning a single value of a specified datatype. Built-in stored functions are created when the database software is installed. Customized or user-defined functions are defined by application developers or DBAs.

As an example, let’s once again consider Scott’s burgeoning widget company. Since the company is still small, Scott must perform the duties of both an application developer and a DBA. The HR department appears to frequently run queries that combine the employee name, job, and department into a formatted string for display on both web pages and corporate documents. To standardize the format of this string throughout the organization, Scott wrote a function called FORMAT_EMP that can be used by any department to display the employee name, job, and department, as follows:

Department: 10     Employee: Smith     Title: Shipping

Scott creates his stored function like this:

create or replace function   FORMAT_EMP (DeptNo IN number,               EmpName IN varchar2,               Title IN varchar2) return varchar2 is   concat_rslt   varchar2(100); begin   concat_rslt :=     ‘Department: ‘ || to_char(DeptNo) ||     ‘   Employee: ‘ || initcap(EmpName) ||     ‘   Title: ‘ || initcap(Title);   return (concat_rslt); end;

The first line of this command will create the function if it doesn’t exist or replace it if it already exists. The next three lines define what kinds of values are going to be provided as input to the function, as well as what kind of value will be returned. In this example, Scott will provide the FORMAT_EMP function with a number and two strings, and he expects a string to be returned. He needs to create the function only once. By default, only the user who created the function can use it.

Line 6 declares a local variable called concat_rslt, which will temporarily hold the formatted string result. In a stored procedure or function, all of the actual processing occurs between the begin and the end keywords. In lines 8 to 11, the variable concat_rslt is assigned the formatted value using some of the Oracle built-in functions. Finally, in line 12, the function returns the result to the calling program, which, in this case, is a SQL statement similar to the following:

select format_emp(deptno,ename,job) from emp; Department: 20   Employee: Smith   Title: Clerk Department: 30   Employee: Allen   Title: Salesman Department: 30   Employee: Ward   Title: Salesman Department: 20   Employee: Jones   Title: Manager Department: 30   Employee: Martin   Title: Salesman Department: 30   Employee: Blake   Title: Manager Department: 10   Employee: Clark   Title: Manager Department: 20   Employee: Scott   Title: Analyst Department: 10   Employee: King   Title: President Department: 30   Employee: Turner   Title: Salesman Department: 20   Employee: Adams   Title: Clerk Department: 30   Employee: James   Title: Clerk Department: 20   Employee: Ford   Title: Analyst Department: 10   Employee: Miller   Title: Clerk 14 rows selected.

Note that the names you give for the parameters in the function need not be the same as the names of the columns in the table you’re using. In fact, you could use this function just as well with some values that aren’t even in a table:

select format_emp(189,’JOHNSEN’,’OP MGR’) from dual; Department: 189   Employee: Johnsen   Title: Op Mgr 1 row selected.

Notice how you can use objects such as stored functions for standardization within an organization. An Accounting department employee does not need to remember how to format the employee information, because the formatting is kept in a common location via the stored function.

Scott can grant rights for other departments to use this function also. As an added bonus for the DBA, only a single copy of this function is stored in the shared pool for use by an unlimited number of users. This reduces the overall memory requirements for the database and can improve the response time for a query.

shared pool

An area of memory within the total amount of memory allocated for the Oracle database that can hold recently executed SQL statements, PL/SQL procedures and packages, as well as cached information from the system tables.

Note

It’s important for the DBA to keep track of how many stored procedures and functions are running during the course of a business day, because there are memory and performance implications for the objects that share space in the database’s shared pool. If there are too many other SQL statements and frequent accesses to database control structures, then the stored functions and procedures may be temporarily removed from the shared pool, thus affecting the response time the next time the user calls the stored function or procedure because it must be re-read from disk.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net