Stored Procedures and Functions in PL/SQL

Procedures and Functions

In my earlier posts, we have seen anonymous blocks were the only examples of PL/SQL code. As the name indicates, anonymous blocks are unnamed executable PL/SQL blocks. Because they are unnamed, they can be neither reused nor stored for later use.

Procedures and functions are named PL/SQL blocks that are also known as subprograms. These subprograms are compiled and stored in the database. The block structure of the subprograms is similar to the structure of anonymous blocks. Subprograms can be declared not only at the schema level but also within any other PL/SQL block. A subprogram contains the following sections:

• Declarative section: Subprograms can have an optional declarative section. However,unlike anonymous blocks, the declarative section of a subprogram does not start with the DECLARE keyword. The optional declarative section follows the IS or AS keyword in the subprogram declaration.
• Executable section: This is the mandatory section of the subprogram, which contains the implementation of the business logic. Looking at the code in this section, you can easily determine the business functionality of the subprogram. This section begins and ends with the BEGIN and END keywords, respectively.
• Exception section: This is an optional section that is included to handle exceptions.

Differences Between Anonymous Blocks and Subprograms

The table below not only shows the differences between anonymous blocks and subprograms but also highlights the general benefits of subprograms.

Anonymous blocks are not persistent database objects. They are compiled every time they are to be executed. They are not stored in the database for reuse. If you want to reuse them, you must rerun the script that creates the anonymous block, which causes recompilation and execution.

Procedures and functions are compiled and stored in the database in a compiled form. They are recompiled only when they are modified. Because they are stored in the database, any application can make use of these subprograms based on appropriate permissions. The calling application can pass parameters to the procedures if the procedure is designed to accept parameters. Similarly, a calling application can retrieve a value if it invokes a function or a procedure.

Procedure: Syntax

Below is the syntax for creating procedures:

CREATE [OR REPLACE] PROCEDURE procedure_name
  [(argument1 [mode1] datatype1,
  argument2 [mode2] datatype2,
  ...)]
IS|AS
procedure_body;

In the syntax:

• procedure_name
• argument – Is the name given to the procedure parameter. Every argument is associated with a mode and data type. You can have any number of arguments separated by commas.
• mode – Mode of argument:
  IN (default)
  OUT
  IN OUT
• datatype – Is the data type of the associated parameter. The data type of parameters cannot have explicit size; instead, use %TYPE.
• Procedure_body – Is the PL/SQL block that makes up the code.

The argument list is optional in a procedure declaration.

Creating a Procedure

Consider the example shown below:

...
CREATE TABLE dept AS SELECT * FROM departments;
CREATE PROCEDURE add_dept IS
  v_dept_id dept.department_id%TYPE;
  v_dept_name dept.department_name%TYPE;
BEGIN
  v_dept_id:=280;
  v_dept_name:='ST-Curriculum';
  INSERT INTO dept(department_id,department_name)
  VALUES(v_dept_id,v_dept_name);
  DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT
  ||' row ');
END; 

In the code example, the add_dept procedure inserts a new department with department ID 280 and department name ST-Curriculum. In addition, the example shows the following:

• The declarative section of a procedure starts immediately after the procedure declaration and does not begin with the DECLARE keyword.
• The procedure declares two variables, dept_id and dept_name.
• The procedure uses the implicit cursor attribute or the SQL%ROWCOUNT SQL attribute to verify that the row was successfully inserted. A value of 1 should be returned in this case.

Note:
When you create any object, the entries are made to the user_objects table. When the code in the slide is executed successfully, you can check the user_objects table for the new objects by issuing the following command:

SELECT object_name,object_type FROM user_objects;

The source of the procedure is stored in the user_source table. You can check the source for the procedure by issuing the following command:

SELECT * FROM user_source WHERE name='ADD_DEPT';

Invoking a Procedure

Consider the example shown below:

...
BEGIN
  add_dept;
END;
/
SELECT department_id, department_name FROM dept
WHERE department_id=280;

Output:

anonymous block completed
  Inserted 1 row.

DEPARTMET_ID   DEPARTMENT_NAME
------------   ---------------
         280   ST-Curriculum

The example above, shows how to invoke a procedure from an anonymous block. You must include the call to the procedure in the executable section of the anonymous block. Similarly, you can invoke the procedure from any application, such as a Forms application or a Java application. The SELECT statement in the code checks to see whether the row was successfully inserted.

You can also invoke a procedure with the SQL statement CALL <procedure_name>.

Function: Syntax

Shown below is the syntax for creating a function:

CREATE [OR REPLACE] FUNCTION function_name
  [(argument1 [mode1] datatype1,
  argument2 [mode2] datatype2,
  ...)]
RETURN datatype
IS|AS
function_body;

In the syntax:

• function_name – Is the name of the function to be created.
• argument – Is the name given to the function parameter (Every argument is associated with a mode and data type. You can have any number of arguments separated by a comma. You pass the argument when you invoke the function.)
• mode – Is the type of parameter (Only IN parameters should be declared.)
• datatype – Is the data type of the associated parameter.
• RETURN datatype – Is the data type of the value returned by the function.
• function_body – Is the PL/SQL block that makes up the function code.

The argument list is optional in the function declaration. The difference between a procedure and a function is that a function must return a value to the calling program. Therefore, the syntax contains return_type, which specifies the data type of the value that the function returns. A procedure may return a value via an OUT or IN OUT parameter.

Creating a Function

Consider the example shown below:

CREATE FUNCTION check_sal RETURN Boolean IS
  v_dept_id employees.department_id%TYPE;
  v_empno employees.employee_id%TYPE;
  v_sal employees.salary%TYPE;
  v_avg_sal employees.salary%TYPE;
BEGIN
  v_empno:=205;
  SELECT salary,department_id INTO v_sal,v_dept_id FROM employees
  WHERE employee_id= v_empno;
  SELECT avg(salary) INTO v_avg_sal FROM employees WHERE
  department_id=v_dept_id;
  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

The check_sal function is written to determine whether the salary of a particular employee is greater than or less than the average salary of all employees working in the same department. The function returns TRUE if the salary of the employee is greater than the average salary of the employees in the department; if not, it returns FALSE. The function returns NULL if a NO_DATA_FOUND exception is thrown.

Note that the function checks for the employee with the employee ID 205. The function is hard-coded to check only for this employee ID. If you want to check for any other employees, you must modify the function itself. You can solve this problem by declaring the function such that it accepts an argument. You can then pass the employee ID as parameter.

Invoking a Function

Consider the example shown below:

BEGIN
  IF (check_sal IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('The function returned
      NULL due to exception');
  ELSIF (check_sal) THEN
    DBMS_OUTPUT.PUT_LINE('Salary > average');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salary < average');
  END IF;
END;
/

Output:

anonymous block completed
Salary > average

You include the call to the function in the executable section of the anonymous block.The function is invoked as a part of a statement. Remember that the check_sal function returns Boolean or NULL. Thus the call to the function is included as the conditional expression for the IF block.

Note: You can use the DESCRIBE command to check the arguments and return type of the function, as in the following example:

DESCRIBE check_sal;

Passing a Parameter to the Function

Consider the example shown below:

DROP FUNCTION check_sal;
CREATE FUNCTION check_sal(p_empno employees.employee_id%TYPE)
RETURN Boolean IS
  v_dept_id employees.department_id%TYPE;
  v_sal employees.salary%TYPE;
  v_avg_sal employees.salary%TYPE;
BEGIN
  SELECT salary,department_id INTO v_sal,v_dept_id FROM employees
    WHERE employee_id=p_empno;
  SELECT avg(salary) INTO v_avg_sal FROM employees
    WHERE department_id=v_dept_id;
  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
EXCEPTION
...

Remember that the function was hard-coded to check the salary of the employee with employee ID 205. The code shown in the slide removes that constraint because it is rewritten to accept the employee number as a parameter. You can now pass different employee numbers and check for the employee’s salary.

The output of the code example in the slide is as follows:

function CHECK_SAL dropped.
FUNCTION check_sal compiled

Invoking the Function with a Parameter

Consider the example shown below:

BEGIN
DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205');
  IF (check_sal(205) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('The function returned
      NULL due to exception');
  ELSIF (check_sal(205)) THEN
    DBMS_OUTPUT.PUT_LINE('Salary > average');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salary < average');
  END IF;
DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70');
  IF (check_sal(70) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('The function returned
      NULL due to exception');
  ELSIF (check_sal(70)) THEN
  ...
  END IF;
END;
/

The code in the slide invokes the function twice by passing parameters. The output of the code is as follows:

anonymous block completed
Checking for employee with id 205
Salary > average
Checking for employee with id 70
The function returned NULL due to exception