6.1.1 Procedures

A procedure is a modular block of code with the following general structure:

PROCEDURE 
name
 (
   
parameter1
 IN | OUT | IN OUT AS 
datatype
, 
   
parameter2
 IN | OUT | IN OUT AS 
datatype
,
   ... )
IS
   
Local variable declarations

BEGIN
   
Program instructions

EXCEPTION
   WHEN 
exception1
 THEN
      
Handler 1

   WHEN 
exception2
 THEN
      
Handler
 2
   WHEN OTHERS THEN
      
Default error handler

END;  

The name of the procedure should reflect the task it performs. The task should be fairly simple; a procedure shouldn't be more than a few hundred lines long. If it is, you probably haven't broken your problem down sufficiently. Here's a fairly simple procedure:[ 3 ]

[3] This listing, and the others throughout this chapter, begin with the line "CREATE OR REPLACE...," which is a SQL command and not part of the actual procedure. Since procedures (as well as functions and packages) are database objects, you must use SQL to CREATE and compile them. The REPLACE option allows you to run these scripts over and over without having to first drop the procedure.

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PROCEDURE give_raise (
   emp_id IN VARCHAR2 DEFAULT NULL,
   raise_pct IN VARCHAR2 DEFAULT NULL
   )
IS
   
   monthly_salary   NUMBER DEFAULT 0;

BEGIN
   -- Fetch current salary using a SELECT...INTO;
   SELECT  sal
      INTO  monthly_salary
      FROM  emp
      WHERE id = emp_id;
   -- Decide what to do
   IF monthly_salary > 10000
   THEN
      HTP.print ('You are rich enough already!');
   ELSE
      UPDATE emp
      SET    sal = sal * (1 + raise_pct)
      WHERE  id = emp_id;
      COMMIT ;
      HTP.print ('Your wish is my command');
   END IF;
   HTP.print ('All done.');
EXCEPTION
   WHEN OTHERS
   THEN
      HTP.print ('Sorry, no raise for now.');
END;

6.1.2 Functions

A function , the second kind of modular block has the following general structure:

FUNCTION 
name
 (
   
parameter1
 IN | OUT | IN OUT AS 
datatype
,
   
parameter2
 IN | OUT | IN OUT AS 
datatype
 )

RETURN 
return_datatype 
IS
   
Local variable declarations

BEGIN
   
Function instructions

   RETURN 
return_value

EXCEPTION
   WHEN 
exception1
 THEN
      
Handler1

   WHEN 
exception2
 THEN
      
Handler2

   WHEN OTHERS THEN
      
Default error handler

END;  

A function computes and returns a single value (its return value) of the datatype defined in its header section. The RETURN command, which can appear in the executable or exception section (or both), sends the return value back to the program that called the function. The RETURN command terminates the function immediately.

You can use functions to perform common computations or return special values. For example, you might want to include some descriptive information at the end of each page, like your company's name and the date the page was created. You can write a simple function that you can call inside each program to avoid hard-coding. Here, for example, is such a function:

CREATE OR REPLACE FUNCTION get_web_tag_line
   RETURN VARCHAR2

   Ret_val VARCHAR2(500);

IS
BEGIN
   ret_val := 'Copyright ACME Incorporated, ';
   RETURN ret_val || TO_CHAR (SYSDATE, 'DD-MON-YY');
END;

Even this trivial example points out one of the main advantages of modularizing your code: it helps you avoid problems down the road. For example, what if a big German conglomerate buys your company? You certainly don't want to have to insert a bunch of umlauts into your programs. You probably also noticed that the date format shows only the last two digits of the year. Had you hardcoded the date format into each program, millennium fever would force you to change every occurrence to display the full four-digit year. Calling a function lets you change the code in just one place.

6.1.3 Parameters

You can include parameters in a procedure or function header to better control how it works. A parameter is similar to a local variable, but it acts more like a placeholder for a value that will be passed to the procedure by some future program. This allows a procedure to handle general situations, rather than specific instances of a given problem. These symbols are called formal parameters . The values provided by the calling program are called actual parameters because they represent actual, concrete values.

A procedure or function can accept any number of parameters, or even omit them entirely. For example, the give_raise procedure needed two parameters: one to pass the employee ID and one to pass the amount of the raise.

Parameter declarations follow this general format:

PROCEDURE/FUNCTION 
name
 (
  
Name1 mode datatype
 DEFAULT 
defaul_val
,
  
Name2 mode datatype
 DEFAULT 
default_val
,
  ...
  
Name3 mode datatype
 DEFAULT 
default_val
) IS

Each parameter must have a name, a mode, a datatype, and (optionally) a default value, as defined in the following list:

Parameter name

The name for the parameter as it is used in the body of the block. Each name must be unique. Parameter names should be reasonably meaningful.

Mode

There are three modes: IN, OUT, and IN OUT. The IN mode means that the parameter is read only; the block can see the value (i.e., reference) of the parameter but cannot change it. The OUT mode is write only; the block can set, but not reference, the value of the parameter. This mode is used to return values from the procedure back to the calling program. The IN OUT mode means the parameter can be both referenced and updated.

Datatype

The datatype specifies the parameter's type. These types are unrestrained; the size of the formal parameter is determined by the size of the corresponding actual parameter.

Default value

The default value specifies the value of the parameter if no corresponding actual parameter is provided.

Here are some sample declarations:

PROCEDURE give_raise (
   emp_id IN NUMBER,
   job_code IN VARCHAR2 DEFAULT 'CEO'
   );
   
PROCEDURE print_emp_info (
   dpt_name_parm IN VARCHAR2 DEFAULT 'HUMAN RESOURCES'
   );
   
FUNCTION get_emp_name (emp_id IN NUMBER)
   RETURN VARCHAR2;

The last example is worth commenting on. It's a function that, given a primary key, returns an employee's name. What if you wanted to modify the function to return more information, like the employee's job code and department? Since a function can return only one value, we can't modify the get_emp_name function to return several different things. Instead, we can convert it into a procedure and use OUT parameters to pass the new values back. Here's an example:

PROCEDURE get_emp_info (
   emp_id IN NUMBER,
   emp_number OUT VARCHAR2,
   emp_name OUT VARCHAR2,
   emp_dept_id OUT NUMBER
   )

6.1.4 Calling Procedures and Functions

You call a procedure or function by name. You must also pass actual parameters for its formal parameters (if a module does not have any parameters, then the name alone is sufficient). Here are some examples of how to call a procedure or function from inside another PL/SQL program:

delete_all_customers;

dbms_sql.put_line (todays_date);

today_string := todays_date;

The first example calls the delete_all_customers procedure. This is the simplest type of call, since no parameters are passed to the procedure. As you can see, the ability to represent a complex sequence of actions with a single command makes for much more readable programs. The second example prints the results returned by the todays_date function. You can use a function call anywhere you can use a literal or a variable, as long as its return type is appropriate. The third example assigns a local variable to the value returned by todays_date.

You must supply actual parameters to procedures or functions that have a formal parameter list. There are two notations for doing this: positional notation and named notation.

6.1.5 Overloading

Overloading allows you to create multiple versions of a procedure or function. Each version has the same name but a different signature , the technical term for the full set of declarations in a parameter list. The compiler uses a procedure or function call's name and signature to find a corresponding overloaded function.

Overloading is a powerful technique that makes procedures and functions easier to use. Say you want to create a generic function that returns any passed date in DD-MON-YYYY format. The input should allow the caller to pass a date in a variety of formats. For example, he could pass a DATE variable, a VARCHAR2 string that represents a date, or even numbers that represent the month, day, and year of the date. Here is the example without overloading:

FUNCTION get_nice_date_date (dt IN DATE)
   RETURN VARCHAR2;
  
FUNCTION get_nice_date_vchar (dt IN VARCHAR2)
   RETURN VARCHAR2;
   
FUNCTION get_nice_date_month (
   m IN NUMBER,
   d IN NUMBER,
   y IN NUMBER
   )
   RETURN VARCHAR2;

With overloading, however, you can use the same function name over and over as long as each version has a unique signature. All the programmer has to do to call the function is supply the data in one of the overloaded formats; the compiler automatically does the dirty work of mapping the call to the correct signature. Here are the three specifications required by the get_nice_date function:

FUNCTION get_nice_date (dt IN DATE)
   RETURN VARCHAR2;
 
FUNCTION get_nice_date (dt IN VARCHAR2)
   RETURN VARCHAR2;
   
FUNCTION get_nice_date (
   m IN NUMBER,
   d IN NUMBER,
   y IN NUMBER
   )
   RETURN VARCHAR2;

Without overloading, you had to create three different versions of the same function and leave the programmer to call the correct version. This is a real nuisance because the programmer must know (or, more likely, look up) the applicable name. This flexibility helps ensure that the procedure and functions you develop are easy to use and understand.

6.1.6 Permissions

In this section we'll look at how to share procedures and functions among a number of different schemas. Like all database objects, procedures and functions (as well as packages, which are covered later in this chapter) are owned by a single schema. Consequently, only that schema can execute them unless other database schemas are explicitly granted EXECUTE permission. When you want to write generic modules that can be used by a large number of developers, you can use the GRANT EXECUTE command. This command should be executed within the owner's account, and it has the following syntax:

SQL> GRANT EXECUTE ON 
program_or_package_name
 TO 
schema;

This grant highlights an extremely important aspect of Oracle's security rules. By default, procedures and functions execute with all the permissions of their owner , and not those of the account in which they are executed. For example, you can execute a complex procedure that hits sensitive tables from a minimally privileged account by selectively using GRANT EXECUTE. Oracle8 i has a second model, called invoker's rights , which requires that the user (the invoker ) has the necessary underlying privileges.