13.2. EDB-SPL Programs

EDB-SPL is a procedural, block-structured language. There are three different types of programs that can be created using EDB-SPL, namely procedures, functions, and triggers.

Procedures and functions are discussed in more detail later in this section. Triggers are discussed in Chapter 14.

13.2.1. EDB-SPL Block Structure

Regardless of whether the program is a procedure, function, or trigger, an EDB-SPL program has the same block structure. A block consists of up to three sections - an optional declaration section, a mandatory executable section, and an optional exception section. Minimally, a block has an executable section that consists of one or more EDB-SPL statements within the keywords, BEGIN and END.

There may be an optional declaration section that is used to declare variables, cursors, and types that are used by the statements within the executable and exception sections. Declarations appear just prior to the BEGIN keyword of the executable section. Depending upon the context of where the block is used, the declaration section may begin with the keyword DECLARE.

Finally, there may be an optional exception section which appears within the BEGIN - END block. The exception section begins with the keyword, EXCEPTION, and continues until the end of the block in which it appears. If an exception is thrown by a statement within the block, program control goes to the exception section where the thrown exception may or may not be handled depending upon the exception and the contents of the exception section.

The following is the general structure of a block:

[ [ DECLARE ]
    declarations ]
BEGIN
    statements
[ EXCEPTION
    WHEN exception_condition THEN
        statements
        ... ]
END;

declarations are one or more variable, cursor, or type declarations that are local to the block. Each declaration must be terminated by a semicolon. The use of the keyword DECLARE depends upon the context in which the block appears.

statements are one or more EDB-SPL statements. Each statement must be terminated by a semicolon. The end of the block denoted by the keyword END must also be terminated by a semicolon.

If present, the keyword EXCEPTION marks the beginning of the exception section. exception_condition is a conditional expression testing for one or more types of exceptions. If a thrown exception matches one of the exceptions in exception_condition, the statements following the WHEN exception_condition clause are executed. There may be one or more WHEN exception_condition clauses, each followed by statements.

The following is the simplest possible block consisting of the NULL statement within the executable section. The NULL statement is an executable statement that does nothing.

BEGIN
    NULL;
END;

The following block contains a declaration section as well as the executable section.

DECLARE
    v_numerator     NUMBER(2);
    v_denominator   NUMBER(2);
    v_result        NUMBER(5,2);
BEGIN
    v_numerator := 75;
    v_denominator := 14;
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
        ' is ' || v_result);
END;

In this example, three numeric variables are declared of data type NUMBER. In the executable section, values are assigned to two of the variables and then one number is divided by the other, storing the results in a third variable which is then displayed. If this block is executed the output would be as follows.

75 divided by 14 is 5.36

The following block consists of all three sections - the declaration, executable, and exception sections.

DECLARE
    v_numerator     NUMBER(2);
    v_denominator   NUMBER(2);
    v_result        NUMBER(5,2);
BEGIN
    v_numerator := 75;
    v_denominator := 0;
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
        ' is ' || v_result);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('An exception occurred');
END;

The following output shows that the statement within the exception section is executed as a result of the division by zero.

An exception occurred

13.2.2. Anonymous Blocks

The preceeding section demonstrated the basic structure of a block. A block can simply be executed in an EnterpriseDB programming environment such as EDB-Studio.

A block of this type is called an anonymous block. An anonymous block is unnamed and is not stored in the database. Once the block has been executed and erased from the application buffer, it cannot be re-executed unless the block code is re-entered into the application.

Anonymous blocks are useful for quick, one-time programs such as for testing.

Typically, however, the same block of code would be re-executed many times. In order to run a block of code repeatedly without the necessity of re-entering the code each time, with some simple modifications, an anonymous block can be turned into a procedure or function. The following sections discuss how to create a procedure or function that can be stored in the database and invoked repeatedly by another procedure, function, or application program.

13.2.3. Procedures Overview

Procedures are EDB-SPL programs that are invoked or called as an individual EDB-SPL program statement. When called, procedures may optionally receive values from the caller in the form of input parameters and optionally return values to the caller in the form of output parameters.

13.2.3.1. CREATE PROCEDURE

The CREATE PROCEDURE command defines and names a procedure that will be stored in the database.

CREATE [ OR REPLACE ] PROCEDURE name
[ ( parameters ) ]
{ IS | AS }
    [ declarations ]
BEGIN
    statements
END;

name is the identifier of the procedure. If [ OR REPLACE ] is specified and a procedure with the same name already exists in the schema, the new procedure replaces the existing one. If [ OR REPLACE ] is not specified, the new procedure will not be allowed to replace an existing one with the same name in the same schema. parameters is a list of formal parameters. declarations are variable, cursor, or type declarations. statements are EDB-SPL program statements. The BEGIN - END block may contain an EXCEPTION section.

The following is an example of a simple procedure that takes no parameters.

CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END;

The procedure is stored in the database by entering the procedure code in an EnterpriseDB programming environment such as EDB-Studio.

13.2.3.2. Calling a Procedure

The procedure can be invoked from another EDB-SPL program by simply specifying the procedure name followed by its parameters, if any, followed by a semicolon.

name [ ( parameters ) ];

name is the identifier of the procedure. parameters is a list of actual parameters.

Note: If there are no actual parameters to be passed, the procedure can be called with an empty parameter list or the opening and closing parenthesis may be omitted entirely.

The following is an example of calling the procedure from an anonymous block:

BEGIN
    simple_procedure;
END;

That's all folks!

Note: Each application has its own unique way to call a procedure. In a Java application, the application programming interface, JDBC, is used.

In EDB-Studio, use the EXEC command: EXEC simple_procedure;

13.2.3.3. DROP PROCEDURE

A procedure can be deleted from the database using the DROP PROCEDURE command.

DROP PROCEDURE name;

name is the name of the procedure to be dropped.

The previously created procedure is dropped in this example:

DROP PROCEDURE simple_procedure;

13.2.4. Functions Overview

Functions are EDB-SPL programs that are invoked as expressions. When evaluated, a function returns a value that is substituted in the expression in which the function is embedded. Functions may optionally take values from the calling program in the form of input parameters. In addition to the fact that the function, itself, returns a value, a function may optionally return additional values to the caller in the form of output parameters. The usage of output parameters in functions, however, is not an encouraged programming practice.

13.2.4.1. CREATE FUNCTION

The CREATE FUNCTION command defines and names a function that will be stored in the database.

CREATE [ OR REPLACE ] FUNCTION name
[ ( parameters ) ]
RETURN data_type
{ IS | AS }
    [ declarations ]
BEGIN
    statements
END;

name is the identifier of the function. If [ OR REPLACE ] is specified and a function with the same name already exists in the schema, the new function replaces the existing one. If [ OR REPLACE ] is not specified, the new function will not be allowed to replace an existing one with the same name in the same schema. parameters is a list of formal parameters. data_type is the data type of the value that is returned by the function. declarations are variable, cursor, or type declarations. statements are EDB-SPL program statements. The BEGIN - END block may contain an EXCEPTION section.

The following is an example of a simple function that takes no parameters.

CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
    RETURN 'That''s All Folks!';
END;

The following is another function that takes two input parameters. Parameters will be discussed in more detail in subsequent sections.

CREATE OR REPLACE FUNCTION emp_comp (
    p_sal           NUMBER,
    p_comm          NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END;

13.2.4.2. Calling a Function

A function can be used anywhere that an expression can appear within an EDB-SPL statement. A function is invoked by simply specifying its name followed by its parameters enclosed in parenthesis, if any.

name [ ( parameters ) ]

name is the name of the function. parameters is a list of actual parameters.

Note: If there are no actual parameters to be passed, the function can be called with an empty parameter list or the opening and closing parenthesis may be omitted entirely.

The following shows how the function can be called from another EDB-SPL program.

BEGIN
    DBMS_OUTPUT.PUT_LINE(simple_function);
END;

That's All Folks!

A function is typically used within a SQL statement as shown in the following.

SELECT empno, ename, sal, comm, emp_comp(sal, comm) AS "YEARLY COMPENSATION" FROM emp;

     EMPNO ENAME             SAL       COMM YEARLY COMPENSATION
---------- ---------- ---------- ---------- -------------------
      7369 SMITH             800                          19200
      7499 ALLEN            1600        300               45600
      7521 WARD             1250        500               42000
      7566 JONES            2975                          71400
      7654 MARTIN           1250       1400               63600
      7698 BLAKE            2850                          68400
      7782 CLARK            2450                          58800
      7788 SCOTT            3000                          72000
      7839 KING             5000                         120000
      7844 TURNER           1500          0               36000
      7876 ADAMS            1100                          26400
      7900 JAMES             950                          22800
      7902 FORD             3000                          72000
      7934 MILLER           1300                          31200

13.2.4.3. DROP FUNCTION

A function can be deleted from the database using the DROP FUNCTION command.

DROP FUNCTION name;

name is the name of the function to be dropped.

The previously created function is dropped in this example:

DROP FUNCTION simple_function;

13.2.5. Procedure and Function Parameters

An important aspect of using procedures and functions is the capability to pass data from the calling program to the procedure or function and to receive data back from the procedure or function. This is accomplished by using parameters.

Parameters are declared in the procedure or function definition, enclosed within parenthesis following the procedure or function name. Parameters declared in the procedure or function definition are known as formal parameters. When the procedure or function is invoked, the calling program supplies the actual data that is to be used in the called program's processing as well as the variables that are to receive the results of the called program's processing. The data and variables supplied by the calling program when the procedure or function is called are referred to as the actual parameters.

The following is the general format of a formal parameter declaration.

( name [ IN | OUT | IN OUT ] data_type )

name is an identifier assigned to the formal parameter. If specified, IN defines the parameter for receiving input data into the procedure or function. If specified, OUT defines the parameter for returning data from the procedure or function. If specified, IN OUT allows the parameter to be used for both input and output. If all of IN, OUT, and IN OUT are omitted, then the parameter acts as if it were defined as IN by default. Whether a parameter is IN, OUT, or IN OUT is referred to as the parameter's mode. data_type defines the data type of the parameter.

The following is an example of a procedure that takes parameters:

CREATE OR REPLACE PROCEDURE emp_query (
    p_deptno        IN     NUMBER,
    p_empno         IN OUT NUMBER,
    p_ename         IN OUT VARCHAR2,
    p_job           OUT    VARCHAR2,
    p_hiredate      OUT    DATE,
    p_sal           OUT    NUMBER
)
IS
BEGIN
    SELECT empno, ename, job, hiredate, sal
        INTO p_empno, p_ename, p_job, p_hiredate, p_sal
        FROM emp
        WHERE deptno = p_deptno
          AND (empno = p_empno
           OR  ename = UPPER(p_ename));
END;

In this example, p_deptno is an IN formal parameter, p_empno and p_ename are IN OUT formal parameters, and p_job, p_hiredate, and p_sal are OUT formal parameters.

Note: In the previous example, no maximum length was specified on the VARCHAR2 parameters and no precision or scale were specified on the NUMBER parameters. It is illegal to specify a length, precision, scale or other constraints on parameter declarations. These constraints are automatically inherited from the actual parameters that are used when the procedure or function is called.

It is permissible to use %TYPE for the data type when declaring a parameter. In this case, the constraints of the parameter are inherited from the column definition used in the %TYPE clause, not from the actual parameter of the calling program.

The emp_query procedure can be called by another program, passing it the actual parameters. The following is an example of another EDB-SPL program that calls emp_query.

DECLARE
    v_deptno        NUMBER(2);
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER;
BEGIN
    v_deptno := 30;
    v_empno  := 7900;
    v_ename  := '';
    emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal);
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
END;

In this example, v_deptno, v_empno, v_ename, v_job, v_hiredate, and v_sal are the actual parameters.

The output from the preceeding example is shown as follows:

Department : 30
Employee No: 7900
Name       : JAMES
Job        : CLERK
Hire Date  : 03-DEC-81
Salary     : 950

13.2.5.1. Parameter Modes

As previously discussed, a parameter has one of three possible modes - IN, OUT, or IN OUT. The initialized value of a formal parameter, when a procedure or function is called, whether or not a procedure or function can modify its formal parameters, how the actual parameter is passed to the called program, and what happens when an unhandled exception occurs in the called program all vary depending upon the parameter's mode.

The following table summarizes the behavior of parameters according to their mode.

Table 13-1. Parameter Modes

Mode PropertyININ OUTOUT
Formal parameter initialized to:Actual parameter valueActual parameter valueActual parameter value
Formal parameter modifiable by the called program?NoYesYes
Actual parameter contains: (after normal called program termination)Original actual parameter value prior to the callLast value of the formal parameterLast value of the formal parameter
Actual parameter contains: (after a handled exception in the called program)Original actual parameter value prior to the callLast value of the formal parameterOriginal actual parameter value prior to the call
Actual parameter contains: (after an unhandled exception in the called program)Original actual parameter value prior to the callOriginal actual parameter value prior to the callOriginal actual parameter value prior to the call

As shown by the table, an IN formal parameter is initialized to the actual parameter with which it is called. The IN parameter may be referenced within the called program, however, the called program may not assign a new value to the IN parameter. After control returns to the calling program, the actual parameter always contains the same value as it was set to prior to the call.

The OUT formal parameter is initialized to the actual parameter with which it is called. The called program may reference and assign new values to the formal parameter. If the called program terminates without an exception, the actual parameter takes on the value last set in the formal parameter. If either a handled or unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.

Like an IN parameter, an IN OUT formal parameter is initialized to the actual parameter with which it is called. Like an OUT parameter, an IN OUT formal parameter is modifiable by the called program and the last value in the formal parameter is passed to the calling program's actual parameter if the called program terminates without an exception. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.