Procedure and Function in PL/SQL
Contents
- What is SQL ?
- Procedural Language
- Need for PL/SQL
- PL/ SQL
- Features
- Advantages / Disadvantages
- Benefits
- Procedures
- Example with a code
- Functions
- Example with a code
In this blog we are going to discuss on PL/SQL procedures and functions but before that , we have given a brief explanation about the basis of SQL and their syntax. Understanding why SQL is used and how exactly the SQL statements get processed internally have been explained in this blog.
Further we have written a comparison between SQL and PL/SQL. Concepts of Procedure and Functions within PL/SQL have been explained with examples and sample code for a better understanding of the topic. After reading this blog, the readers will be able to create their own procedures and functions easily as per their requirements working on the database
Brief Introduction to SQL
What is SQL ?
Structured Query language which is abbreviated as SQL is a standard language which is used to deal with relational databases. With the use of SQL DDL and DML queries such as INSERT, UPDATE, DELETE, CREATE, ALTER users can make changes in the database . It can also be used to perform a number of other operations such as optimising and maintaining the database such as to provide security to it.
SQL is a language which can be used to communicate with the relational database, through which users can easily write commands using high level language. Sybase, Microsoft SQL Server,Access, Ingres , oracle, etc these database management systems uses SQL to handle operations.
SQL Statement Example
If we want to create a table in the database , we need to write the below query which creates a table with columns such as PersonID,LastName,FirstName,Address and City.
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
SQL uses
Here are the important reasons for using SQL
- Easy retrieval of data from the RDBMS system.
- Describe the data
- Define as well as manipulate the data
- Create and Drop tables from Database
- Set permission on tables, procedures, and views.to enhance security of database using
Types of SQL Queries
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Data Query Language (DQL)
How does the SQL statement get executed internally ?
Procedural Language (PL/SQL)
PL/SQL is a procedural language which is written in SQL in the block format. It is termed as a highly integrated as well as high performance database languageThe PL/SQL program are logical blocks that contain any number of nested sub-blocks.
It is an extension of SQL and is only used in Oracle Database to make the task of the user simplified.As compared to any other programming language when it comes to interacting with the Oracle Database, writing code in PL/SQL is very efficient, easier and understandable.
PL/SQL architecture
The PL/SQL architecture consists of following components:
- PL/SQL Block
- PL/SQL Engine
- Database Server
PL/SQL block
In PL/SQL , statements are divided into units which are referred to as blocks. The block can include SQL statements , variables , loops, constants , statement and exception handling. Blocks can be used to build user defined functions and stored procedures
Below Image depicts how a block should be written inside SQL.
Evey PL/SQL unit is treated individually as one block. It consists of three sections which need to be written in a proper order which are the Declaration section , Execution ection and Exception Section.
Declaration Section
We should use keyword Declare along with variables, cursors, subprograms, and other elements to be used in the program which we want to initialize (This is an optional section)
Executable Section
This section is enclosed between BEGIN and END where the executable PL/SQL statements of the program are written .It should have at least one executable line of code, which may be a NULL command to simplifies that nothing should be executed.
(This is a mandatory section)
Exception Section
It has all of the exceptions which would be triggered during the execution of this block. (This is optional section)
The different types of PL/SQL units are:
- Anonymous Block
- Function
- Library
- Procedure
- Package Body
- Package Specification
- Trigger
- Type
- Type Body
Internal Working of PL/SQL query
PL/SQL Engine
In the PL/SQL engine the actual processing of the queries/code takes place. It seggreates the Pl/SQL blocks and SQL part in the input where the separated part is handled by engine itself and SQL part is sent to the database server where the actual interaction with database takes place.
Database Server
It is the most important component of Pl/SQL which stores the data.It consists of SQL executor which parses the input SQL statements and executes the same.
Features of PL/SQL
- PL/SQL can be used along with SQL
- It offers extensive error checking
- There are numerous data types
- Various types of programming structures
- It supports structured programming using functions and procedures
- It supports OOP concepts
- Web apps and server pages development
Advantages of PL/SQL
- Better performance
- High Productivity
- Tight integration with SQL
- Full Portability
- Tight Security
- Supports Object Oriented Programming concepts.
- Scalability and Manageability
- Supports Web Application Development
- Supports Server Page Development
Disadvantages of PL/SQL
- Stored Procedures in PL/SQL uses high memory
- Lacks functionality debugging in stored procedures
- Presentation layer needs to be modified if there are changes in underlying database
- Complex interface
Difference between SQL and PL/SQL
PL/SQL Procedures
PL/SQL stored procedure or simply a procedure is a block which performs one or more specific tasks as written inside the BEGIN part of the block. It is similar to procedures which are used in other programming languages.
The procedure contains a header and a body.
Header: It contains the name of the procedure and the parameters/variables passed to the procedure by he caller
Example
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter ,parameter) ]
Body: It consists of a declaration section, execution section and exception section similar to a general PL/SQL block.
Example
[declaration_section]
BEGIN
executable_section
[EXCEPTION exception_section]
END [procedure_name];
Parameters
Most of us gets confused when to use IN and when to use OUT while writing stored Procedures To easily understand this please refer to the below table
Stored procedure examples
Lets create a procedure in PL/SQL to Display the name of student from Students database by taking the student Id as input
IN Parameter
The procedure name is dispName which is taking one input parameter from the calling procedure. After IS , we have declared a variable to store the name of the student
In the Begin block we have written select statements and fetched the name and stored it into the nm variable . Using the dbms output syntax we have printed the name of the student on the console .
CREATE OR REPLACE PROCEDURE dispName (x IN number)
IS
nm students.stdName%type;
BEGIN
SELECT stdName INTO nm FROM students WHERE stdId = x ;
dbms_output.put_line(‘name of student with ‘ || ‘ id ‘ || x ||’ is ‘|| nm );
END;
/
Calling the dispName procedure by passing student ID 120 as input parameter.
BEGIN
dispName(120);
END;
/
OUTPUT
OUT parameter
To give an example of Procedure using OUT parameter , getName procedure is created whose output parameter is the name of the student who has scored marks between 90 to 100 . So whenveen the procedure is called , the procedures fetches the name from the student table whose marks is between 90 to 100 and return to the calling procedure with the help of OUT parameter
CREATE OR REPLACE PROCEDURE getName (nm OUT VARCHAR)
IS
BEGIN
SELECT stdName INTO nm FROM students WHERE stdMarks between 90 AND 100;
END;
/
DECLARE
name2 students.stdName%type;
BEGIN
getName(name2);
dbms_output.put_line(‘name of student with ‘ || ‘ markes between 90–100 is ‘ || name2);
END;
/
OUTPUT
IN OUT Parameter
Lets understand the IN OUT parameter with a simple example by creating updateMarks which takes student id and marks to add as input and gives the updated marks as output. If the teacher wants to increamer the marks of a particular student they can do so using this procedure.
In the BEGIN block of this procedure , old marks of the student of the particular id is fetched and stored into old_marks and then old_marks is added with the passed input parameter inc new_marks are stored into new_marks
Statement
CREATE OR REPLACE PROCEDURE updateMarks (std_id IN number , inc IN number , z OUT number)
IS
old_marks students.stdMarks%type;
new_marks students.stdMarks%type;
BEGIN
SELECT stdMarks into old_marks FROM students WHERE stdId = std_id;
new_marks:= old_marks + inc;
UPDATE students SET stdMarks = new_marks WHERE stdId = std_id;
z:=new_marks;
END;
/
DECLARE
newmrks students.stdMarks%type;
BEGIN
updateMarks(212 , 10 , newmrks);
dbms_output.put_line(‘Updated marks for student with ID 212 is ‘ || newmrks);
END;
/
OUTPUT
PL/SQL User Defined Functions
PL/SQL user defined functions are very similar to that of Procedures. Functions are reusable programs which are stored as a schema object in the Oracle Database. The main difference between them is that a function must always return a value and on the other hand for procedure they may or may not return a value. A function consists of a header and body.
Header : It has the function name , parameters along with the RETURN clause which specifies the return type of the data . Each parameter of the function can be either in the IN, OUT, or INOUT
Example :
CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
RETURN return_type
Body : The function body is the same as the procedure body which has three sections which are declarative section, executable section, and exception-handling section.
Example :
[declarative section]
BEGIN
[executable section]
[EXCEPTION]
[exception-handling section]
RETURN (value)
END;
Creating Function
A function is created using the CREATE FUNCTION syntax.
Syntax :
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, …])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
RETURN (value)
END [function_name];
Where,
Function-name : name of the function
[OR REPLACE] : allows the modification of an existing function
parameter list : mode and types of the parameters (IN,OUT,INOUT)
return _datatype : Data type of return value
value : The value which needs to be returned to the calling function.
Example
The following example illustrates how to create and call a standalone function. This function returns the percentage of marks scored by the student from the students table. The user needs to pass the student id , and this id is taken as input parameter by the perC function , then percentage is calculated and returned as perc to the calling function.
CREATE OR REPLACE FUNCTION perC(std_id IN number)
return number
IS
perc number;
BEGIN
SELECT (stdMarks/100)*100 INTO perc from students where stdId= std_id;
return perc;
END;
/
OUTPUT
Calling Function
So once you have created a function, it needs to be called to execute the specified logic/statements written inside the function . So therefore a user needs to epilictically call the function from another block of PL/SQL.
When a program calls a function , the control goes to the called function , then it executes the statemnts defined inside the function , returns certain value to the calling function and again the control is back to the calling function
Syntax :
function_name(<parameters>);
Example
So let’s call the function which we had created above and see the output
BEGIN
dbms_output.put_line(‘Percentage Scored by ID 118 is ‘ || perC(212)|| ‘%’);
END;
/
OUTPUT
Recursive Function
When a function calls itself it is referred to as recursive function and the process is called as recursion
To understand this, let us calculate the factorial of a number.
DECLARE
num number;
factorial number;
FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= 11;
factorial := fact(num);
dbms_output.put_line(‘ Factorial ‘|| num || ‘ is ‘ || factorial);
END;
/
OUTPUT
Dropping Function
To delete the function use the below syntax
DROP function <function_name>
DROP function perC ;
OUTPUT
To summarise in short about the blog , In this blog we have explained the basic definition of SQL , PL/SQL as internal working of PL/SQL query . Further we have briefly explained about function and stored procedures by giving simple examples such that you can understand this topic more clearly.
We would appreciate it if you leave us a comment !
—
References
Contributors : Gaurav Gaikwad , Samiksha Hiran , Abhishek Ikhar , Hrushabh Jadhav
