10.8 Parameters and Modes

Procedures are subprograms that generally perform some servicethey act on or change something. A function generally just returns information. All subprogram parameters have a mode that is syntactically coded between the variable and the type definition. PL/SQL programs have three modes:

• IN (default)

• IN OUT

• OUT

IN	An IN mode parameter is passed to a subprogram with the understanding that the subprogram uses the parameter as a constant, read-only value. A parameter passed with IN mode can be a literal expression, constant declaration, or variable declaration. In the case of a variable, this mode provides some measure of security on the part of the calling program. The caller knows that upon completion of the call, the variable is unchanged. The compiler enforces this security. There are two checks. First, if the compiler detects an IN mode parameter to the left of an assignment, the compile fails. Second, if the variable is passed to a subsequent procedure that uses it as an OUT or IN OUT parameter, the compile fails.
IN OUT	Parameters passed in this mode can only be variables , not literals or constants. The assumption is that the called procedure will change the content passed. The called procedure will read as well as write to the structure content. An example is a procedure that passes a string to a data-scrubbing procedure. The scrubbing procedure runs the string through a loop checking for invalid characters and removes unwanted bytes. Upon completion of the scrubbing, the procedure that made the call has a clean string.
OUT	Parameters passed in this mode can only be variables, not literals or constants. Within a subprogram, the initial value of an OUT mode parameter is NULL. The intent of using an OUT mode parameter is to convey something about the interfacethe caller has no obligation to pass data. The called procedure can write to the data structure; it can read the structure. For example, a sort procedure might be passed as an OUT mode structure. The process of sorting requires an iterative process of read/write operations. The called procedure only reads the structure to perform the sort . Any OUT mode parameter can change to IN OUT without any impact. However, this may not be consistent with the original design. If parameters arbitrarily change from OUT to IN OUT, this misrepresents the program interface and confuses the programmer who wonders, "What data is supposed to be passed?"

10.8.1 IN Mode (Default) is a Constant

An IN mode parameter is a constant or must be treated as a constant. A procedure that accepts an IN mode argument is restricted from writing to that argument. The following procedure will not compile because line 3 writes to the IN mode variable.

 1  PROCEDURE print_next_value(v_data IN INTEGER) IS 2  BEGIN 3      v_data := v_data+1;  -- compile error 4      dbms_output.put_line(v_data); 5  END; 

The following procedure, which performs the same service, does compile. The constant can be in an expression. This is the correct use of an IN mode parameter.

 1  PROCEDURE print_next_value(v_data IN INTEGER) IS 2  BEGIN 3      dbms_output.put_line(v_data+1); 4  END; 

In the following PL/SQL block, all calls to the previous PRINT_NEXT_VALUE are valid. Line 7, in the PL/SQL block, passes a variable. Following the call to PRINT_NEXT_VALUE, the block retains the original value of MY_DATA. The author of the print procedure declares the mode as IN, which informs users, "I will not change your data."

 1  DECLARE 2      zero CONSTANT INTEGER := 0; 3      my_data INTEGER := 2; 4  BEGIN 5      print_next_value( 123 ); 6      print_next_value( zero ); 7      print_next_value( my_data ); 8  END; 

10.8.2 IN OUT Mode

There is an assumption drawn from looking at a procedure that takes an IN OUT mode parameter. That assumption is that the caller must provide data. This would be the IN part of an IN OUT argument. The following procedure, on line 4, reads from and writes to the data passed. This parameter must be IN OUT. It truly modifies the data passed.

 1  PROCEDURE change_data(v_data IN OUT INTEGER) IS 2  BEGIN 3      for i in 1..10 loop 4          v_data := v_data + 1; 5      end loop; 6  END; 

The caller of CHANGE_DATA knows that the before and after values of the IN OUT parameter may differ .

• MY_DATA before the call is 0.

• The value of MY_DATA after the block execution is 10.

 1  DECLARE 2      my_data INTEGER := 0; 3  BEGIN 4      print_data(my_data); 5      dbms_output.put_line('block print: 'my_data); 6  END; 

If this PL/SQL block declares MY_DATA as a constant, block execution fails.

10.8.3 OUT Mode

OUT mode parameters convey critical information about the interface. The intent is that the caller has no obligation to provide content to the caller. An OUT mode variable, upon entry to the procedure, is NULL.

In the following procedure, prior to the execution of line 4, V_DATA is NULL. A value of 100 is assigned. During execution, the procedure reads and writes to the parameter. From the beginning of program logic, PROVIDE_DATA makes no assumption about the content of V_DATA.

 1  PROCEDURE provide_data(v_data OUT INTEGER) 2  IS 3  BEGIN 4      v_data := 100; 5      FOR i IN 1..10 LOOP 6          v_data := v_data +1; 7      END LOOP; 8  END; 

A user of PROVIDE_DATA is the following PL/SQL block. The first INSERT into TEMP will be the value 0. The second insert will be the value 110.

 1  DECLARE 2      my_data INTEGER := 0; 3  BEGIN 4      insert into temp values (my_data); 5      provide_data(my_data); 6      insert into temp values (my_data); 7  END; 

10.8.4 Functions and Modes

The default mode for procedures and functions is IN mode. A general practice, to make code clearer, is to always type the mode, even when using the default. The convention with a function is that they are selectorsthey return a specific piece of information. They also perform a processing-type service where data is passed in, changed, and returned with an IN OUT mode argument. Examples are built-in string functions: REPLACE and TRANSLATE.

Functions are often named using a noun; procedures are named with a verb. The large percentage of functions in any application will have all arguments passed with the IN mode, but functions can be declared with parameters that have all three modes. Functions with parameters other than IN mode should be rare.

The use of OUT mode arguments with a function provides a technique for some simple interfaces, especially when the function truly is a selector but must provide dual pieces of information. A function design can require that it fetch a record, but also returns the success of that fetch. Certainly a procedure can accomplish this, but it may be desirable, from an API perspective, to provide a function that follows this design.

The decision of function or procedure should, foremost, consider the readability and simplicity of the code that will use this interface. The following illustrates a function design that returns data plus a status. For this interface, assume ARG_1 is a primary key value used to identify the precise record to fetch. The argument NEXT_REC is the wanted data.

 FUNCTION next_rec(arg1 IN type, next_record OUT type) RETURN BOOLEAN; 

This design allows the user to write code as follows:

 WHILE (next_rec(arg1, my_record_structure)) LOOP  process my_record_structure;  END LOOP; 

An alternative to the function NEXT_REC is a procedure. The procedure solution would be the followingwe rename the subprogram with a verb.

 PROCEDURE get_next_rec(arg1 IN type,                        next_record OUT type,                        status OUT BOOLEAN); 

From a user's perspective, we can use this procedure as wella slightly different interface. The code block still uses a loop to fetch all records.

 LOOP     get_next_rec(arg1, my_record_structure, status)     EXIT WHEN NOT status;  process my_record_structure;  END LOOP; 

From the PL/SQL block perspective, there is little difference. In concept, the function is a selector that evaluates to the next record, but optionally provides status about that fetch. The procedure behaves as a service, fetching a record, and returning the record and a status.

10.8.5 Named versus Positional Notation

Consider a procedure with the following interface definition.

 PROCEDURE proc_name (arg1  mode and type  , arg2  mode and type  ); 

A user has two syntax options. The first is POSITIONAL notation, the second is NAMED notation:

 1. proc_name(variable_1, variable_2); 2. proc_name(arg1 => variable_1, arg2 => variable_2); 

What does the term Formal Parameter Name refer to? The formal parameter name refers to the name used in the interface definition of the procedure or function. For the preceding procedure, PROC_NAME, the formal parameter names are ARG1 and ARG2.

Formal parameter names should be generic and still convey what the parameter is used for. The following procedure definition uses formal names that convey to the user the intent of each parameterthe formal parameter names are FILE_ID and RECORD_READ:

 PROCEDURE get_record (file_id     IN  INTEGER,                       record_read OUT VARCHAR2); 

One can look at this procedure definition and be fairly certain how to use this code. Additional information may be included in the procedure or package documentation. The user of this procedure has two calling styles. The first is positional notation.

 DECLARE     file_id             INTEGER;     next_payroll_record VARCHAR2(100); BEGIN     get_record(file_id, next_payroll_record); END; 

Is this PL/SQL block clearas far as what the code does? The PL/SQL block uses variable names that convey their usethis makes it fairly clear what is happening in this code. The PL/SQL block can use named notation, shown next:

 DECLARE     file_id             INTEGER;     next_payroll_record VARCHAR2(100); BEGIN     get_record         (file_id => file_id,          record_read => next_payroll_record); END; 

Is this block any clearer? The call to GET_RECORD is correct but the information is redundant. Because of well- chosen variable names, formal parameter notation, in this case, is not necessary and, to most readers, makes the code wordy and more difficult to read. When do you use named notation? There are several cases when named notation is useful.

1. Use named notation when the variable names you choose do not completely convey their use. For example, you may pass a literal value and use named notation as a code documentation tool.

2. Use named notation when the subprogram is coded with default values and you are only using some of the defaults.

The first condition, passing a literal in a procedure, can occur with IN mode parameters. It may not be clear what the literal is used for. This is more likely to occur when your code interfaces with packages that are outside the present schema. Examples are other applications and Oracle packages. The following is a PL/SQL block that submits a job to the Oracle DBMS_JOB package. This block will dispatch a request to the job queue. The request is to run, asynchronously, the stored procedure PROCESS_DATA. The program PROCESS_DATA will run independent of the caller. Its execution does not block the caller. The following is correct but the developer should consider named notation as a means to clearify the data passed.

 DECLARE     id BINARY_INTEGER; BEGIN     dbms_job.submit(id, 'process_data;', SYSDATE); END; 

Even a developer familiar with the DBMS_JOB package would like to see a few formal parameter names in this case. The last argument is a built-in function, SYSDATE, and someone less familiar with DBMS_JOB might want to look at this code to see what this data means. The use of formal names makes the procedure call slightly more readable.

 DECLARE     id integer; BEGIN     dbms_job.submit(job => id, what => 'process_data;',         next_date=> SYSDATE); END; 

Use named notation when you want to pick and choose specific parameters to passskipping some formal parameters for where there is a default value. (Default parameters are covered in Section 10.8.6, "Default Parameters."

Consider the following procedure that computes an aggregate salary. This subprogram includes default values for each parameter: MONTHLY_BASE and NO_OF_MONTHS. Calling the AGGREGATE_SALARY function with default values returns the computed salary: 10,000 times 12.

 CREATE OR REPLACE FUNCTION aggregate_salary     (monthly_base   NUMBER  := 10000,      no_of_months   INTEGER := 12) RETURN NUMBER IS BEGIN     return (monthly_base * no_of_months); END; 

To code with the default for NO_OF_MONTHS, write the following:

 DECLARE     monthly_base NUMBER := 9000;     aggregate    NUMBER; BEGIN     -- salary for one year.     aggregate := aggregate_salary(monthly_base); END; 

You cannot use positional notation and call this procedure using the default for MONTHLY_BASE and still pass the number of months. The number of months passed would be positional, interpreted as a monthly base and the result would be, for the salary, twice the number of months. Using defaults in this case requires named notation:

 DECLARE     no_of_months INTEGER := 10;     aggregate    NUMBER; BEGIN     -- salary for 10 months.     aggregate := aggregate_salary(no_of_months=>no_of_months); 

10.8.6 Default Parameters

A procedure or function specification can define a default value for parameters that have IN or IN OUT mode. There are two syntax forms illustrated :

 PROCEDURE name     (argument  mode datatype  := a_default_value); PROCEDURE name     (argument  mode datatype  DEFAULT a_default_value); 

The following function definitions each return the area of a circle with a default radius of onethe only difference between these functions is the style of the default.

 FUNCTION circle     (radius IN NUMBER := 1) RETURN NUMBER IS BEGIN     RETURN 3.14 * radius**2; END; FUNCTION circle     (radius IN NUMBER DEFAULT 1) RETURN NUMBER IS BEGIN     RETURN 3.14 * radius**2; END; 

The following PL/SQL block contains four invocations of the function CIRCLEall are valid.

 DECLARE    radius NUMBER := 1;    area   NUMBER; BEGIN    area := circle();    area := circle(radius);    area := circle(radius+1);    area := circle; END; 

When a subprogram contains several default parameters, the user can pick and choose any of the defaults but may have to use named notation. The following procedure updates a row in the PROFESSOR (DDL for this table is in Chapter 4). This table is:

 SQL> desc professors Name                              Null?     Type ---------------------------- -------- ----------- PROF_NAME  (primary ke  y)           NOT NULL  VARCHAR2(10) SPECIALTY                         NOT NULL  VARCHAR2(20) HIRE_DATE                         NOT NULL  DATE SALARY                            NOT NULL  NUMBER(5) TENURE                            NOT NULL  VARCHAR2(3) DEPARTMENT                        NOT NULL  VARCHAR2(10) 

The following update procedure requires a professor's namethis is the primary key. The caller can pass a SALARY to update and/or a TENURE (YES/NO CHECK constraint).

The purpose of this procedure is for a user to update tenure or salary. The caller passes a tenure value, passes a salary value, or passes two valuesthis updates both columns . The procedure returns immediately if it is called with a NULL for each paremeter.

When the default is used, the UPDATE statement uses the NVL function to set the column to the current row column value.

 CREATE OR REPLACE PROCEDURE update_professor    (v_prof_name professors.prof_name%TYPE,     v_salary    professors.salary%TYPE := NULL,     v_tenure    professors.tenure%TYPE := NULL) IS BEGIN     IF         v_salary IS NULL AND v_tenure IS NULL     THEN         RETURN; -- nothing to change     END IF;     UPDATE professors     set    salary = NVL(v_salary, salary),            tenure = NVL(v_tenure, tenure)     WHERE  prof_name = v_prof_name; END; 

A caller can use positional notation except when they are using the default for SALARY but wish to supply a new TENURE. The following PL/SQL block contains several valid examples of calling UPDATE_PROFESSOR. The one statement that must use positional notation is on line 11.

 1  DECLARE  2      new_salary  professors.salary%TYPE := 13000;  3      new_tenure  professors.tenure%TYPE := 'YES';  4  BEGIN  5      -- Named notation examples  6      update_professor('Blake', 12000, 'YES');  7      update_professor('Blake', 12000);  8      update_professor('Blake', new_salary, new_tenure);  9 10      -- Positional notation examples. 11      update_professor('Blake', v_tenure => new_tenure); 12 13      update_professor('Blake', v_salary => new_salary, 14                                v_tenure => new_tenure); 15 16      update_professor(v_prof_name=> 'Blake', 17                       v_salary => new_salary, 18                       v_tenure => new_tenure); 19  END; 

When coding procedures and functions, place defaults last in the calling sequence. The preceding example illustrates one case that requires positional notationline 11. Defaults intermixed with nondefault parameters, or placed left of nondefault arguments, increase the number of scenarios where the user must code to named notation.

10.8.7 Scaling Code with Defaults

You can enhance the functionality of existing code by adding defaults to the end of a procedure or function specification. Because the defaults are to the right, all existing code is not affected. The existing code, as written, will use the default value assigned. New code may or may not use the parameter. New code can be written to pass a value of the default.

Consider the following procedure. It updates a professor record. Within an application there could be wide use of this procedure, yet we can extend it functionally without impacting existing code.

 CREATE OR REPLACE PROCEDURE update_professor    (v_prof_name professors.prof_name%TYPE,     v_salary    professors.salary%TYPE := NULL,     v_tenure    professors.tenure%TYPE := NULL); 

For this scenario, assume the procedure must be modified to support email notification. To implement this we add a parameter, V_NOTIFY. This flag, when set to true, will send an email to the professor that the salary has changed. The following shows the changed code. A parameter has been added that has a default.

 CREATE OR REPLACE PROCEDURE update_professor    (v_prof_name professors.prof_name%TYPE,     v_salary    professors.salary%TYPE := NULL,     v_tenure    professors.tenure%TYPE := NULL,     v_notify    BOOLEAN := FALSE); 

With this change, old code is not affected. New code can pass a TRUE/FALSE value or not pass anything, using the default. For this scenario, there are two options to consider.

1. The old code plays no part in the new functionality. Perform the following steps if only new coded will use this feature.

   • Set the default for V_NOTIFY to FALSE.

   • Modify UPDATE_PROFESSORS to send an email when the V_NOTIFY is TRUE.

   • Ensure that all new code specifies a TRUE/FALSE (or default) value depending on whether an email should be sent.

   • There is no impact to existing code.

2. The old code, or some of it, will use the new functionality. To make this adjustment, do the following.

   • Set the default for V_NOTIFY, the most common situation. For example, assume most old code will send an email. In this case, set default to TRUE.

   • Modify UPDATE_PROFESSORS to send an email when the V_NOTIFY is TRUE.

   • Ensure that all new code specifies a TRUE (or default)/FALSE value in the call to UPDATE_PROFESSORS.

   • Modify the old code, which will not send an email and add a new FALSE argument in the call to UPDATE_PROFESSOR. Most old code will not change because it will use the default of TRUE.

10.8.8 %TYPE

This %TYPE syntax is a means to declare a variable whose type is derived from a particular column type in a database table. The syntax for this type definition is:

 variable_name  table_name.column_name%TYPE; 

Procedures that return data to a calling program must be concerned with the size or dimension of the variable to which they are writing. When the called program writes data that is larger than what the caller is expecting, the result is an overflow condition. Declaring variables using a %TYPE syntax can minimize this problem.

Consider the procedure that returns an OUT mode variable, a professors salary.

 CREATE OR REPLACE PROCEDURE get_professor_salary    (v_prof_name IN professors.prof_name%TYPE,     v_salary    OUT professors.salary%TYPE); 

Now consider the PL/SQL that uses this procedure. It must declare a salary variable. What should that type be? The following is not a good choice:

 DECLARE     SAL NUMBER(5,2); BEGIN     get_professor_salary('Milton', SAL); END; 

This code block will fail if the dimension of the salary column in the professors table increases . Suppose the salary recorded in the table changes from a monthly to an annual salary. The column dimension in the database would increase. Execution of the block would fail with a VALUE_ERROR exception. This would occur as the GET_PROFESSOR_SALARY attempts to write a number larger than NUMBER(5,2).

The preceding block should be written as:

 DECLARE     SAL professors.salary%type BEGIN     get_professor_salary('Milton', SAL); END; 

Using %TYPE with procedure and function specifications provides additional information to the user. It conveys specific information about the data expected. A procedure that updates a student record could be defined with the following:

 PROCEDURE update_student(student IN VARCHAR2, etc); 

The user of this procedure has to dig into the code to see whether the parameter is a student name of a student ID. Someone familiar with the database schema might understand that the parameter must be a primary key and, therefore, this procedure requires a STUDENT_ID. The mystery of the interface is easily eliminated with %TYPE.

 PROCEDURE update_student     (student IN students.student_ID%TYPE, etc); 

You should not use a %TYPE when performing an aggregation. If the result of a subprogram call is the sum of all salaries, the computed number could exceed the precision of the %TYPE. The following returns a NUMBER with no precision or scale.

 CREATE OR REPLACE PROCEDURE get_professor_salaries    (salaries OUT NUMBER) IS BEGIN     SELECT SUM(salary) INTO salaries FROM professors; END get_professor_salaries; 

Two Oracle exceptions can occur when a datatype does not match the assigned data.

VALUE_ERROR	This exception is raised in PL/SQL when the size of the target is smaller than needed. You get this when you assign 100 to a variable declared with NUMBER(2). This is raised when you assign a 3-character string, such as "100," to a field declared as NUMBER(2). If you SELECT INTO a variable with too small a precision, you get this error.
INVALID_NUMBER	This exception can occur when a SQL statement selects character data into a NUMBER type. This implicit conversion will fail if the character string does not represent a valid number.