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;
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_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;
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.
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.
No comments:
Post a Comment