techtopicsbyme: August 2016

Summary:

In this tutorial, we will introduce you to PL/SQL function. We will show you how to develop a PL/SQL function and call it from an anonymous block and SELECT statement. And we will also show you how to drop a function.

Introducing to PL/SQL function:

A PL/SQL function is a named block that returns a value. A PL/SQL function is also known as a subroutine or a subprogram.

Creating a Function:

     PL/SQL functions block creation using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure is function return always value where as procedure may or may not return value.

To create a PL/SQL function, you use the following syntax:

CREATE [OR REPLACE] FUNCTION function_name [(
   parameter_1 [IN] [OUT] data_type,
   parameter_2 [IN] [OUT] data_type,
   ...............................
   parameter_N [IN] [OUT] data_type ]

   RETURN return_data_type { IS | AS }

    -- the declaration statements

    BEGIN

    -- the executable statements < function_body >

    return return_data_type;EXCEPTION

     -- the exception-handling statements

END [function_name];

Let’s examine the syntax of creating a function in greater detail:

You specify the function name  function_name after the  FUNCTION keyword. By convention, the function name should start with a verb, for example convert_number() .

A function may have zero or more than one parameter. You specify the parameter names in the  parameter_1 ,  parameter_2 , etc. You must specify the data type of each parameter explicitly in the  data_type . Each parameter has one of three modes: IN , OUT and IN OUT.

When you create a function or procedure, you have to define IN/OUT/INOUT parameters.

IN : The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.

i.e. An  IN parameter is a read-only parameter. If the function tries to change the value of the IN parameters, the compiler will issue an error message. You can pass a constant, literal, initialized variable, or expression to the function as the  IN parameter.

OUT : The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

i.e. An  OUT parameter is a write-only parameter. The OUT parameters are used to return values back to the calling program. An  OUT parameter is initialized to a default value of its type when the function begins regardless of its original value before being passed to the function.

IN OUT : The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

i.e. An  IN OUT parameter is read and write parameter. It means the function reads the value from an IN OUT parameter, change its value and return it back to the calling program.

The function must have at least one  RETURN statement in the execution section. The  RETURN clause in the function header specifies the data type of returned value.

The block structure of a function is similar to an anonymous block with an additional function header section.

PL/SQL Function Example:

     We are going to create a function named convert_to_number() that parses a string and returns a number if the input string is a number or  NULL if it cannot be converted to a number.

SQL> CREATE OR REPLACE FUNCTION convert_to_number(
             input_number IN VARCHAR2)

       RETURN NUMBER IS

  BEGIN

       RETURN to_number(input_number);

EXCEPTION

WHEN others THEN
RETURN NULL;

END convert_to_number;

Function created.

The input_number is an IN parameter whose data type is VARCHAR2 so that you can pass any string to the convert_to_number() function.

Inside the function, we used the built-in PL/SQL function named to_number() to convert a string into a number. If any exception occurs, the function returns NULL in the exception section, otherwise, it returns a number.

How to call above PL/SQL Function

The PL/SQL function returns a value so you can use it on the right-hand side of an assignment or in a SELECT statement.

Let’s create an anonymous block to use the convert_to_number() function.

SQL> SET SERVEROUTPUT ON SIZE 1000000;

SQL> DECLARE

v_num1 number;

v_num2 number;

v_num3 number;

BEGIN

        v_num3 := convert_to_number('574');
        v_num2 := convert_to_number('12.21');
        v_num3 := convert_to_number('abcd');

        DBMS_OUTPUT.PUT_LINE(v_num1);
        DBMS_OUTPUT.PUT_LINE(v_num2);
        DBMS_OUTPUT.PUT_LINE(v_num3);

     END;

574
     12.21

     PL/SQL procedure successfully completed.

Use of "SET SERVEROUTPUT ON":

By default, SQL*Plus doesn't display output from PL/SQL. I will take small example explained below:

SQL> BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');

END;

PL/SQL procedure successfully completed.

The same block is executed again after issuing a  SET SERVEROUTPUT ON command:

SQL> SET SERVEROUTPUT ON

    Hello World

    PL/SQL procedure successfully completed.

We can also use the convert_number()   function in a  SELECT statement as follows:

SQL> SELECT convert_to_number('574') FROM dual;

SQL> SELECT convert_to_number('12.21') FROM dual;

SQL> SELECT convert_to_number('abcd') FROM dual;

Notice that dual table a special one-row table that is used for selecting pseudo-column like our examples above.

Drop Function

Once you have created your function in Oracle, you might find that you need to remove it from the database.

Syntax:

The syntax to a drop a function in Oracle is:

DROP FUNCTION function_name;

function_name

The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in Oracle.

For example:

DROP FUNCTION convert_to_number;

This example would drop the function called convert_to_number() .

In this tutorial, you’ve learned how to develop a custom PL/SQL function and call it in anonymous blocks, in an SQL SELECT statement and dropping a function from database.

When our page is idle for x amount of time, the view will expire and throw javax.faces.application.ViewExpiredException. To prevent this from happening one solution is to create CustomViewHandler that extends ViewHandler and override restoreView method. All the other methods are being delegated to the Parent.

import java.io.IOException;
import javax.faces.FacesException;
import javax.faces.application.ViewHandler;
import javax.faces.component.UIViewRoot;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletRequest;

public class CustomViewHandler extends ViewHandler {

 private ViewHandler parent;
 
 public CustomViewHandler(ViewHandlerparent) {
  //System.out.println("CustomViewHandler.CustomViewHandler():ParentViewHandler:" + parent.getClass());
  this.parent = parent;
 }

 /**
 *@linkjavax.faces.application.ViewExpiredException}.Thishappensonlywhenwetrytologoutfromtimedoutpages.
 */
 @Override
 public UIViewRoot restoreView(FacesContext facesContext,String viewId) {
  UIViewRoot root = null;
  root = parent.restoreView(facesContext,viewId);
  if(root == null) {
   root = createView(facesContext,viewId);
  }
  return root;
 }
 
 @Override
 public Locale calculateLocale(FacesContext facesContext) {
  return parent.calculateLocale(facesContext);
 }
 
 @Override
 public String calculateRenderKitId(FacesContext facesContext) {
  String renderKitId = parent.calculateRenderKitId(facesContext);
  //System.out.println("CustomViewHandler.calculateRenderKitId(): RenderKitId: " + renderKitId);
  return renderKitId;

 }
 @Override
 public UIViewRoot createView(FacesContext facesContext, String viewId) {

  return parent.createView(facesContext,viewId);

 }

 @Override
 public String getActionURL(FacesContext facesContext, String actionId) {
  return parent.getActionURL(facesContext,actionId);
 }

 @Override
 public String getResourceURL(FacesContext facesContext, String resId) {
  return parent.getResourceURL(facesContext, resId);
 }

 @Override
 public void renderView(FacesContext facesContext, UIViewRoot viewId) throws IOException, FacesException {
  parent.renderView(facesContext, viewId);
 }
 
 @Override
 public void writeState(FacesContext facesContext) throws IOException {
  parent.writeState(facesContext);
 }

 public ViewHandler getParent(){
  return parent;
 }

}

Then you need to add it to your faces-config.xml

<view-handler>com.demo.CustomViewHandler</view-handler>

This will prevent you from getting ViewExpiredException’s

techtopicsbyme

Tuesday, August 30, 2016

PL/SQL - Function