14.2. SPL Programs

14.2.1. SPL Block Structure

Regardless of whether the program is a procedure, function, or trigger, an 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 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 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

14.2.2. Anonymous Blocks

The preceeding section demonstrated the basic structure of a block. A block can simply be executed in EnterpriseDB.

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.

14.2.3. Procedures Overview

Procedures are SPL programs that are invoked or called as an individual 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.

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

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

name [ ( parameters ) ];

BEGIN
    simple_procedure;
END;

That's all folks!

      CREATE OR REPLACE PROCEDURE manager_check(pEmpNo NUMBER) 
      IS
          vEname          emp.ename%TYPE;
          vJob            emp.job%TYPE;

      BEGIN

          SELECT ename,job
          INTO vEname,vJob
          FROM emp
          WHERE empno=pEmpNo;

              IF vJob in ('MANAGER','PRESIDENT') THEN
                  DBMS_OUTPUT.PUT_LINE('Employee details shown below:');
              ELSE
                 DBMS_OUTPUT.PUT_LINE('Terminating Procedure');
              RETURN; -- to terminate execution
              END IF;

          DBMS_OUTPUT.PUT_LINE('Employee Name :' || vEname);
          DBMS_OUTPUT.PUT_LINE('Designation :' || vJob);

          EXCEPTION
              WHEN NO_DATA_FOUND THEN
                  DBMS_OUTPUT.PUT_LINE('Exception, terminating Procedure');
                  RETURN;
      END;
      

      CREATE PROCEDURE
     
      -- 7369 is clerk so execution with terminate
      exec manager_check(7369);
      INFO:  Terminating Procedure
      EDB-SPL Procedure successfully complete
      

DROP PROCEDURE name;

DROP PROCEDURE simple_procedure;

14.2.4. Functions Overview

Functions are 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.

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

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

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 emp_comp;

name [ ( parameters ) ]

BEGIN
    DBMS_OUTPUT.PUT_LINE(simple_function);
END;

That's All Folks!

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

DROP FUNCTION name;

DROP FUNCTION simple_function;

14.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. An IN parameter can also be initialized to a default value. 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 DEFAULT 10,
    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 which has been initialized with a default value of 10, p_empno and p_ename are IN OUT formal parameters, and p_job, p_hiredate, and p_sal are OUT formal parameters.

The emp_query procedure can be called by another program, passing it the actual parameters. The following is an example of another 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