facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Stored Procedures and Functions

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

The focus of this lesson is to describe the basic creation and maintenance of subprograms. All of the PL/SQL you have learned up to this point can be used in the context of such programs, but the examples will be simple demonstration programs that focus on the distinctive characteristics of Oracle functions and stored procedures.

Subprograms serve as independent programming units but can be coupled together to produce complex applications. In order for subprograms to be useful and to interact with people and programs they need to be able to receive data, process it and send back results.

Lesson Goals

  • Learn how to create subprograms
  • Learn how to modify subprograms
  • Learn how to remove subprograms
  • Learn about application maintenance
  • Learn about parameters
  • Learn how to retrieve data from subprograms
  • Learn subprogram development techniques
  • Learn about handling compilation errors

Creating Subprograms

It takes very little effort to transform the "Hello World" anonymous block into a stored procedure. This stored procedure is added to the database and is accessible to the database user who created it and any other user granted appropriate permissions.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_create_hello_world.sql
create procedure test1
is 
begin
 dbms_output.put_line('Hello from a stored procedure');
end;
/

exec test1

begin
  test1;
end;
/

Code Explanation

This simple example demonstrates the creation of a stored procedure named test1. The familiar BEGIN and END keywords mark the body of the procedure. The DECLARE keyword does not appear, any variables that would be declared would appear after the IS keyword.

In most cases, you will have variables in your subprogram that need to be declared prior to the BEGIN keyword.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_create_hello_someone.sql
create procedure test3
is 
  v_name VARCHAR2(25); 
begin
  v_name := 'Larry';
  dbms_output.put_line('Hello ' ||
                       v_name ||
                       ' from a stored procedure');
end;
/

exec test3

begin
  test3;
end;
/

Code Explanation

Although the DECLARE keyword does not appear explicitly in stored procedures and functions, variables can be declared in the same section of the block (immediately prior to the BEGIN keyword).

Modifying Subprograms

Once a subprogram is created in the database, it might require changes due to new or modified requirements, changes in data, programmer errors or refactoring to improve the quality of a code base. In most situations, a stored procedure (or other subprogram) will require changes over time.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_create_or_replace_hello_world.sql
create or replace procedure test1
is 
begin
 dbms_output.put_line('Hello from a replaced stored procedure');
end;
/

exec test1

begin
  test1;
end;
/

Code Explanation

In order to avoid explicitly dropping a stored procedure and re-creating it after each change, the Oracle proprietary REPLACE keyword can be used so that an existing object is replaced by a new one with the same name and type.

Code Sample:

Stored-Procedures-and-Functions/Demos/function_create_or_replace_hello_world.sql
create or replace function test2
return varchar2
is
begin
  return 'Hello world from a function!';
end;
/

select test2
from dual;

begin
  dbms_output.put_line(test2());
end;
/

drop function test2;

Code Explanation

A function is distinguished from a stored procedure in that it returns a value. The type (but not the length or precision) of the return value must be specified, and the RETURN keyword is used to return the value in the body of the function. The function can be called from a query - a stored procedure cannot.

Removing Subprograms

If a subprogram is no longer in direct use and is also not being used by any dependent PL/SQL objects or external programs, it can be removed from the database altogether. Depending upon the configuration of your database, the object might be available to be recovered from the recycle bin (as of Oracle 10g). However, once the recycle bin has been purged, the object is no longer available within the database itself.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_drop_hello_world.sql
drop procedure test1;

Code Explanation

A stored procedure (or other subprogram) can be dropped using the DROP keyword. Thus the CREATE and DROP keywords that are used to make and remove tables also are used when working with subprograms.

Application Maintenance

The fact that Oracle stores subprograms in the database has a number of interesting implications. Subprograms are covered by Oracle security and access to them can be controlled using standard Oracle Database functionality. As mentioned above a dropped object might be recoverable if it exists in the user's recyclebin. However, this storage is not a replacement for a standard version control system such as Subversion, CVS, Source Safe, or Git. These products provide an array of features that allow identification of every change made to a file over time. The storage of objects in the database should not replace ordinary coding practices that are standard and well accepted regardless of the technology involved.

In many environments, developers never deploy code to production databases. In such cases, PL/SQL subprograms must be coded in coherent scripts that contain all dependent objects and can be run using a standard process across the organization. This is sometimes a surprise to junior programmers who believe that their work is complete once they have successfully created working PL/SQL code in a development environment. In order to maintain a stable and efficient development process clear guidelines and communication between DBAs and developers is essential for projects to succeed.

Code Sample:

Stored-Procedures-and-Functions/Demos/debugging_subprograms.sql
CREATE OR REPLACE PROCEDURE my_proc
IS
BEGIN
  dbms_output.put_line('In: ' || $$PLSQL_UNIT);
  dbms_output.put_line('First line number: ' || $$PLSQL_LINE);
  dbms_output.put_line('Second line number: ' || $$PLSQL_LINE);
  dbms_output.put_line('Third line number: ' || $$PLSQL_LINE);
  RAISE VALUE_ERROR;
 EXCEPTION
    WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

Code Explanation

Debugging subprograms is an extensive topic. There are a number of Oracle tools as well as third party tools that can assist in this work. This example demonstrates a few features added in Oracle 10g that can be helpful when constructing PL/SQL code that facilitates efficient troubleshooting and support. The $$PLSQL_UNIT returns the program unit executing the code, and the $$PLSQL_LINE returns the line number being executed. The DBMS_UTILITY.format_error_backtrace outputs the call stack at the point where the exception was raised.

Challenge

  1. Create a function called format_phone. It will format the following string 123.456.7890 so that it looks like a U.S. phone number (123) 456-7890. The function will be expanded upon in later lessons.
  2. Add two variables of type VARCHAR2 and length 50. The first variable will hold an unformatted phone number and the second a formatted phone number.
  3. Initialize the first variable to 123.456.7890.
  4. Add PL/SQL code to format the string.
  5. Return the formatted string.
  6. Compile, test and debug your function.
  7. For an additional challenge, see if you can come up with a solution that requires even less code. (Hint: using the regexp_replace function can eliminate several lines of code).
  8. Compile, test and debug your function.

Challenge Solution:

Stored-Procedures-and-Functions/Solutions/phone_number_function1.sql
create or replace function format_phone
return varchar2
is 
  v_phone_number VARCHAR2(50) := '123.456.7890';
  v_format_phone_no VARCHAR2(50);
begin

  v_format_phone_no := '(' || v_phone_number;
  
  v_format_phone_no := replace(v_format_phone_no, '.', ') ');
  
  v_format_phone_no := substr(v_format_phone_no, 1, 9) ||
                              '-' ||
                              substr(v_format_phone_no, 12);
  
  return v_format_phone_no;

END;
/

-- test it
begin
  dbms_output.put_line(format_phone());
end;
/

Code Explanation

This solution uses the built in replace and substring functions to format the phone number.

Using Procedures

Duration: 15 to 20 minutes.
  1. Create a procedure called "divide_by_zero".
  2. Declare two variables named x and y of type number. Assign x the value of one and y the value of zero.
  3. In the body of the procedure assign x the value of x divided by y. (This will result in a divide by zero error - expected for this exercise).
  4. Add a line that outputs We will not get here that demonstrates that the divide by zero exception raised in the previous line will bypass the following lines.
  5. Add an exception section to handle the divide by zero error.
  6. Add a line to the exception section that outputs "USER MESSAGE: Error in: program unit name line line number".
  7. Add a line that prints out the call stack at the point the error occurred.
  8. Compile, run, and test and debug the procedure.

Solution:

Stored-Procedures-and-Functions/Solutions/divide_by_zero_procedure_solution.sql
CREATE OR REPLACE PROCEDURE divide_by_zero
IS
 x NUMBER:=1;
 y NUMBER:=0;
BEGIN
   x:=x/y;
   DBMS_OUTPUT.put_line('We will not get here.');
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.put_line('USER MESSAGE: Error in: ' || $$PLSQL_UNIT
                         || ' line ' || $$PLSQL_LINE);
    DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

-- Test it. Only the exception messages should be displayed.
begin
  divide_by_zero;
end;
/

Code Explanation

Parameters

The following demonstrations consist of stored procedures and calls to them from PL/SQL anonymous blocks.

Stored procedures and functions can be passed information through parameters. Parameters appear in the subprogram declaration and identify the name and data type of the parameter being passed.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_params.sql
CREATE OR REPLACE PROCEDURE in_params
( p_varchar2 IN VARCHAR2, 
  p_number IN NUMBER, 
  p_date IN DATE
) 
IS
BEGIN
  dbms_output.put_line('p_varchar2: ' || p_varchar2);
  dbms_output.put_line('p_number: ' || p_number);
  dbms_output.put_line('p_date: ' || p_date);
END in_params;
/

Code Explanation

In this example, three parameters of different types are passed in to the procedure and then printed out.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_params_call.sql
BEGIN
  IN_PARAMS(P_VARCHAR2 => 'Ray Bradbury', 
            P_NUMBER => 451,
            P_DATE => to_date('22-AUG-1920', 'dd-mm-yyyy')
           );
END;
/

-- This next demo illustrates the advantage of calling
-- a stored procedure with named notation:
-- the parameters do not have to be in the declared order
-- when the stored procedure is called.
BEGIN
  IN_PARAMS(P_NUMBER => 451,
            P_VARCHAR2 => 'Ray Bradbury', 
            P_DATE => to_date('22-AUG-1920', 'dd-mm-yyyy')
           );
END;
/

Code Explanation

This anonymous block can be used to call the procedure above.

The DEFAULT keyword allows you to specify a value for an input parameter that will be used if one is not supplied by the invoking program.

Code Sample:

Stored-Procedures-and-Functions/Demos/default_value.sql
create or replace PROCEDURE default_value
( p_varchar2 IN VARCHAR2 DEFAULT 'This is a default value'
) 
IS
BEGIN
  dbms_output.put_line(p_varchar2);
END default_value;
/

Code Explanation

Input parameters can be assigned default values using the default keyword.

Code Sample:

Stored-Procedures-and-Functions/Demos/default_value_call.sql
BEGIN

  dbms_output.put_line('*** Parameter not specified...');  
  DEFAULT_VALUE();
  
  dbms_output.put_line('*** With a value assigned...');  
  DEFAULT_VALUE('Hey a value');

  dbms_output.put_line('*** With a null value...');  
  DEFAULT_VALUE(NULL);
  dbms_output.put_line('*** end.');  
  
END;
/

Code Explanation

This anonymous block can be used to call the procedure. It illustrates the behavior of the default keyword. If a parameter is specified in the subprogram call, it is assigned to the parameter. If the parameter is not referenced, a default value is used. Note that explicitly assigning a parameter with a default value to null results in the parameter being null. The default keyword is not used in this case.

So a fundamental way that information gets passed into a subprogram is through parameters. However, parameters can also be used to pass data out of a subprogram. Although - strictly speaking - a function differs from a stored procedure in that it can return a value, values can effectively be "returned" from subprograms using default values.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_params.sql
CREATE OR REPLACE PROCEDURE out_params
( p_varchar2 OUT VARCHAR2, 
  p_number OUT NUMBER, 
  p_date OUT DATE
)
IS
BEGIN

  p_varchar2 := 'George Orwell';
  p_number   := 1984;
  p_date     := to_date('25-Jun-1903','dd-mon-yyyy');
  
END out_params;
/

Code Explanation

An OUT parameter must be declared to modify a parameter within a subprogram so that its value is available to the calling program.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_params_call.sql
DECLARE
  v_varchar2 VARCHAR2(200);
  v_number NUMBER;
  v_date DATE;
BEGIN

  out_params(p_varchar2 => v_varchar2,
             p_number => v_number,
             p_date => v_date
            );
  DBMS_OUTPUT.PUT_LINE('v_varchar2 = ' || v_varchar2);
  DBMS_OUTPUT.PUT_LINE('v_number = ' || v_number);
  DBMS_OUTPUT.PUT_LINE('v_date = ' || v_date);
END;
/

Code Explanation

Another anonymous block to call the OUT parameter example procedure above.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_out_param.sql
CREATE OR REPLACE PROCEDURE in_out_param
( p_in_out IN OUT VARCHAR2
)
IS
BEGIN
  p_in_out := 'This value was set within the procedure.';
END in_out_param;
/

Code Explanation

Parameters can be declared as both IN and OUT parameters.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_out_param_call.sql
DECLARE
 V_IN_OUT varchar2(50) := 'This is the value going in...';
BEGIN
  IN_OUT_PARAM(V_IN_OUT);
  DBMS_OUTPUT.PUT_LINE('Upon return, V_IN_OUT = ' || V_IN_OUT);
END;
/

Code Explanation

This anonymous block can be used to call the procedure above.

Cursors Used as Parameters and Variables

As mentioned earlier, a cursor references a private area in memory which holds information for processing a given SQL statement. An implicit cursor is used every time you execute a SQL statement within PL/SQL. PL/SQL also allows you to declare explicit cursors. Cursors can also be passed as parameters. This is a standard way that sets of data are retrieved from stored procedures.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_param_cursor.sql
create or replace procedure return_a_cursor
( p_cursor out sys_refcursor) 
is
begin
  open p_cursor for 
                select * 
                from employees;
end return_a_cursor;
/

Code Explanation

In this example, the cursor passed in is opened using a query against the employees table.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_param_cursor_call.sql
DECLARE
  c sys_refcursor;
  rec employees%rowtype;
BEGIN

  RETURN_A_CURSOR(P_CURSOR => c);

  loop
    fetch c into rec;
    exit when c%notfound;
    dbms_output.put_line('Name: ' ||
                         rec.first_name ||
                         ' ' ||
                         rec.last_name);
  end loop;
  close c; 
END;
/

Code Explanation

This anonymous block handles the cursor returned by the procedure by populating a record and displaying the first and last name of each employee. The cursor could be populated in many different ways. There is no problem as long as the record used when fetching the cursor has corresponding columns.

Subprogram Development Techniques

Addressing Compilation Errors

The following are some areas to be aware of to increase your ability to write, support, and debug PL/SQL code.

Any mistake in PL/SQL syntax will result in a compilation error. The SHOW ERROR command can be used in PL/SQL to obtain information about compilation errors generated during the current connection session.. In addition, the USER_ERRORS view can be consulted, and errors are displayed along with detail information in SQL Developer.

Directives for Debugging

As of version 10g, Oracle includes a number of features that assist in writing code that is more easily supported and maintained.

Code Sample:

Stored-Procedures-and-Functions/Demos/debug_output.sql
BEGIN
  DBMS_OUTPUT.put_line ('Line number: '
                         || $$plsql_line);
  DBMS_OUTPUT.put_line ('Unit: ' || $$plsql_unit);
  DBMS_OUTPUT.put_line ('Unit: ' ||
                        COALESCE ($$plsql_unit, 'anonymous block'));
  RAISE VALUE_ERROR;
EXCEPTION
  WHEN VALUE_ERROR
      THEN
        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

Code Explanation

In this example, special directives are used to identify the line number and the programming unit being executed. Additional error information is also displayed by calling DBMS_UTILITY.format_error_backtrace.

Issues with Booleans

In general, PL/SQL is closely related to SQL so no conversion of data types is needed. One significant exception to this rule is the Boolean type which exists in PL/SQL but not in SQL. There are a variety of ways to map Boolean data types (0 and 1, TRUE and FALSE). There is also an Oracle Supplied function (DIUTIL.INT_TO_BOOL) that can be used to convert integers to Boolean values.

Code Sample:

Stored-Procedures-and-Functions/Demos/Boolean_test.sql
declare
 -- Problem: SQL does not have a BOOLEAN datatype
 i NUMBER := 1;
 b BOOLEAN := TRUE;
 -- Other variations
 -- Constant NUMBER - TRUE, String - True
begin
 /*
   Null or zero evaluates to false, 
   1 is true, 
   other values cause errors
 */
 IF sys.diutil.int_to_bool(i) THEN
   dbms_output.put_line('diutil.int_to_bool(i) : TRUE for '||i);
 ELSE
   dbms_output.put_line('diutil.int_to_bool(i) : FALSE for '||i);
 END IF;
 
 --Illegal in this context
 --select 0 into b from dual;
 
 /*
 However this works! - 0 or null is false, 
                       all other numbers are true, 
                       characters/sysdates cause errors
 */
 execute immediate 'select 1 from dual' into b; 

 IF b THEN
   dbms_output.put_line('b : TRUE');
 ELSE
   dbms_output.put_line('b : FALSE');
 END IF;

end;

Code Explanation

The code in this demonstration illustrates some surprising behavior related to Booleans.

If possible, settle on one consistent way to represent Boolean values stored in the database for your code base and make sure the practice is clear to all developers.

Integrated Development Environments

SQL Developer (as well as other 3rd party tools such as Toad for Oracle from Quest or PL/SQL Developer from Allround Automations) are Integrated Development Environments (IDE) specifically designed to facilitate development of PL/SQL code. These tools provide features such as a debugger which allows you to set breakpoints and step through code to trace execution interactively.

IDEs typically include templates or "wizards" that allow you to choose the configuration of subprograms without requiring you to recall and manually type in the associated details. These can be helpful for encouraging productivity, but make sure that you understand the code that is being generated and have chosen the correctwizard/template for your specific situation.

Many IDEs include a debugger which allows you to step through code a line at a time and evaluate variables interactively. These also can be helpful tools, but these debuggers do not step inside individual SQL statements, which are frequently the most challenging part of a program to fix.

Because PL/SQL executes within the database, some database configuration is needed (specifically granting appropriate privileges) to use a PL/SQL debugger.

Code Sample:

Stored-Procedures-and-Functions/Demos/permissions.sql
GRANT debug any procedure, debug connect session 
TO <user_name>;

Code Explanation

PL/SQL debugging requires special "debug" permissions that must be granted from a privileged user.

Positional vs Named Parameter Notation

When calling subprograms that take parameters, you can identify parameters by their position. In some contexts, you can instead identify parameters by their name. These techniques are referred to positional and named notation respectively.

PL/SQL Subprograms with Parameters

Duration: 10 to 50 minutes.
  1. Recall the first format_phone function you created in the previous challenge exercise. Review it. NOTE: This is the format_phone function that does NOT use regular expressions. Or, if you did not complete the challenge exercise, review the provided function in the exercises folder.
  2. Modify the function to use an input parameter instead of a hard coded variable value. Compile and test the function using '123.456.7890' as the input parameter.
  3. This function will be used with phone numbers provided in the employees.phone_number field. Modify the input parameter and variables to reflect this.
  4. Some values in the employee phone number field are for international phone numbers. Modify the function so that it only processes U.S. phone numbers in the format reflected in the test value specified previously. If a phone number is not in U.S. format, simply return the original value.
  5. Write a new stored procedure called employee_report that takes a sys_refcursor as an out parameter.
  6. Modify the procedure so that it opens a cursor for a query that returns the employee first name, last name, and formatted phone number from the employees table (sort by the last name and first name).
  7. Write an anonymous block to test the stored procedure.

Code Sample:

Stored-Procedures-and-Functions/Exercises/phone_number_function3.sql
create or replace function format_phone
return varchar2
is 
  v_phone_number VARCHAR2(50) := '123.456.7890';
  v_format_phone_no VARCHAR2(50);
begin

  v_format_phone_no := '(' || v_phone_number;
  
  v_format_phone_no := replace(v_format_phone_no, '.', ') ');
  
  v_format_phone_no := substr(v_format_phone_no, 1, 9) ||
                              '-' ||
                              substr(v_format_phone_no, 12);
  
  return v_format_phone_no;

END;
/

Code Explanation

Solution:

Stored-Procedures-and-Functions/Solutions/phone_number_function3.sql
create or replace function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is 
  v_phone employees.phone_number%type;
begin

  /*
    If   the length of the string is 12
    and  the 4th character (one based) is a period
    and  the 8th character (one based) is a period,
    then it's a U.S. format phone number.
  */
  IF length(p_phone_number)        = 12
  and instr(p_phone_number,'.')    = 4
  and instr(p_phone_number,'.', 5) = 8
  THEN
    v_phone := '(' || p_phone_number;
    v_phone := replace(v_phone, '.', ') ');
    v_phone := substr(v_phone, 1, 9) || '-' || substr(v_phone, 12);
    return v_phone;
  ELSE
    return p_phone_number;
  END IF;
END;
/

-- Here's a quick way to test that the function works as
-- expected:
select phone_number, format_phone(phone_number)
from employees;

Code Explanation

Solution:

Stored-Procedures-and-Functions/Solutions/employee_report_procedure.sql
create or replace procedure employee_report
(c in out sys_refcursor
)
is
begin
  
  open c for 
  select last_name, 
         first_name, 
         format_phone(phone_number) phone 
  from employees 
  order by last_name, first_name;
  
end;
/

Code Explanation

Solution:

Stored-Procedures-and-Functions/Solutions/anonymous_block.sql
DECLARE
  c1    sys_refcursor;
  fname employees.first_name%type;
  lname employees.last_name%type;
  phone employees.phone_number%type;
  tab   CHAR(1) := CHR(9);
BEGIN
 employee_report(c1);
 loop
    fetch c1 into fname, lname, phone;
    exit when c1%notfound;
    dbms_output.put_line(fname || ', ' || lname || tab || phone);
  end loop;
  close c1;
END;
/

Code Explanation

Challenge

  1. Replace the function created above with one that uses regular expressions. If you wrote the additional challenge for the last challenge exercise, you can use the regular expression code from that. Otherwise you'll need to write the regular expression code now.
  2. Test the replaced function with the same anonymous block of code you wrote above.

Challenge Solution:

Stored-Procedures-and-Functions/Solutions/phone_number_function4.sql
create or replace function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is
begin

  IF regexp_instr(p_phone_number, '(\d{3})\.(\d{3})\.(\d{4})') = 1
  THEN
    return regexp_replace(p_phone_number, 
                          '(\d{3})\.(\d{3})\.(\d{4})', 
                          '(\1) \2-\3');
  ELSE
    return p_phone_number;
  END IF;

END;
/

Code Explanation

This solution is a bit more robust in that it also validates that specified characters are digits.