In this tutorial we will be covering the concept of stored procedures and functions in PL/SQL with examples.
Stored procedure and Function, both can be defined as a set of logically written statements, stored in the database and are executed when called, to perform a specific task.
Both function as well as stored procedure have a unique named block of code which is compiled and stored in the database.
Any stored procedure or function created, remains useless unless it is called. Therefore, after creating a stored procedure or function it is necessary to make a call for that stored procedure or function from another PL/SQL block in order to serve the purpose for which it has been created.
Whenever a block of code for stored procedure or function is written it is then, they are automatically compiled by the oracle engine. During compilation if any error occurs, we get a message on the screen saying that the procedure or function is created with compilation errors but actual error is not displayed.
In order to find out the compilation errors following statement can be executed:
SELECT * from USER_ERRORS;
Once it is compiled, it is then stored by the oracle engine in the database as a database object.
Stored Procedure or function's block of code in PL/SQL is made up of the following three sections:
Following are some advantages of stored procedure and function in PL/SQL:
1. Improves Database Performance
2. Provides Reusability and avoids redundancy
3. Maintains Integrity
4. Maintains Security
5. Saves Memory
Below we have listed down some of the differences between stored procedure and function in PL/SQL:
|May or may not returns a value to the calling part of program.||Returns a value to the calling part of the program.|
|Uses IN, OUT, IN OUT parameter.||Uses only IN parameter.|
|Returns a value using “ OUT” parameter.||Returns a value using “RETURN”.|
|Does not specify the datatype of the value if it is going to return after a calling made to it.||Necessarily specifies the datatype of the value which it is going to return after a calling made to it.|
|Cannot be called from the function block of code.||Can be called from the procedure block of code.|
Below we have the basic syntax for creating a stored procedure in oracle:
CREATE OR REPLACE PROCEDURE <procedure_name> (<variable_name>IN/OUT/IN OUT <datatype>, <variable_name>IN/OUT/IN OUT <datatype>,...) IS/AS variable/constant declaration; BEGIN -- PL/SQL subprogram body; EXCEPTION -- Exception Handling block ; END <procedure_name>;
Let's understand the above code,
CREATE or REPLACE PROCEDUREis a keyword used for specifying the name of the procedure to be created.
ENDare keywords used to indicate different sections of the procedure being created.
ENDstatement to end the procedure definition.
In the code example below we have create a simple program to demonstrate the use of stored procedure for adding two numbers:
set serveroutput on; CREATE OR REPACE PROCEDURE Sum(a IN number, b IN number) IS c number; BEGIN c := a+b; dbms_output.put_line('Sum of two nos= '|| c); END Sum;
For calling the procedure created following code will be executed:
set serveroutput on; DECLARE x number; y number; BEGIN x := &x; y := &y; Sum(x,y); END;
Enter value for x: 10 Enter value for y: 20 Sum of two nos= 30 PL/SQL procedure successfully created.
Now that we know how to create stored procedures and how to use them in another PL/SQL code block, it's time to understand how to create Functions in PL/SQL.
CREATE OR REPLACE FUNCTION <function_name> (<variable_name> IN <datatype>, <variable_name> IN <datatype>,...) RETURN <datatype> IS/AS variable/constant declaration; BEGIN -- PL/SQL subprogram body; EXCEPTION -- Exception Handling block ; END <function_name>;
Let's understand the above code,
CREATE or REPLACE FUNCTIONis a keyword used for specifying the name of the function to be created.
RETURNis a keyword followed by a datatype specifying the datatype of a value that the function will return.
Most part of the above code is similar to the one used for defining a stored procedure.
In the code below we have a program to demonstrate the use of function for adding two numbers.
set serveroutput on; CREATE OR REPLACE FUNCTION Sum(a IN number, b IN number) RETURN Number IS c number; BEGIN c := a+b; RETURN c; END;
For calling the function
Sum following code will be executed:
set serveroutput on; DECLARE no1 number; no2 number; result number; BEGIN no1 := &no1; no2 := &no2; result := Sum(no1,no2); dbms_output.put_line(‘Sum of two nos=’||result); END;
Enter value for no1:5 Enter value for no2:5 Sum of two nos=10 PL/SQL procedure successfully created.
With this, now we know how to define and use a stored procedure in PL/SQL and how to define a function and use the function in PL/SQL.