Oracle PL/SQL Cursor: Implicit, Explicit, Cursor FOR Loop [Example]

 What is CURSOR in PL/SQL?

A Cursor is a pointer to this context area. Oracle creates context area for processing an SQL statement which contains all information about the statement.

PL/SQL allows the programmer to control the context area through the cursor. A cursor holds the rows returned by the SQL statement. The set of rows the cursor holds is referred as active set. These cursors can also be named so that they can be referred from another place of the code.

The cursor is of two types.

• Implicit Cursor
• Explicit Cursor

Implicit Cursor

Whenever any DML operations occur in the database, an implicit cursor is created that holds the rows affected, in that particular operation. These cursors cannot be named and, hence they cannot be controlled or referred from another place of the code. We can refer only to the most recent cursor through the cursor attributes.

Explicit Cursor

Programmers are allowed to create named context area to execute their DML operations to get more control over it. The explicit cursor should be defined in the declaration section of the PL/SQL block, and it is created for the 'SELECT' statement that needs to be used in the code.

Below are steps that involved in working with explicit cursors.

• Declaring the cursor

  Declaring the cursor simply means to create one named context area for the 'SELECT' statement that is defined in the declaration part. The name of this context area is same as the cursor name.

• Opening Cursor

  Opening the cursor will instruct the PL/SQL to allocate the memory for this cursor. It will make the cursor ready to fetch the records.

• Fetching Data from the Cursor

  In this process, the 'SELECT' statement is executed and the rows fetched is stored in the allocated memory. These are now called as active sets. Fetching data from the cursor is a record-level activity that means we can access the data in a record-by-record way.

  Each fetch statement will fetch one active set and holds the information of that particular record. This statement is same as 'SELECT' statement that fetches the record and assigns to the variable in the 'INTO' clause, but it will not throw any exceptions.

• Closing the Cursor

  Once all the record is fetched now, we need to close the cursor so that the memory allocated to this context area will be released.

Syntax:

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

• In the above syntax, the declaration part contains the declaration of the cursor and the cursor variable in which the fetched data will be assigned.
• The cursor is created for the 'SELECT' statement that is given in the cursor declaration.
• In execution part, the declared cursor is opened, fetched and closed.

Cursor Attributes

Both Implicit cursor and the explicit cursor has certain attributes that can be accessed. These attributes give more information about the cursor operations. Below are the different cursor attributes and their usage.

Cursor Attribute	Description
%FOUND	It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE.
%NOTFOUND	This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record.
%ISOPEN	It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE'
%ROWCOUNT	It returns the numerical value. It gives the actual count of records that got affected by the DML activity.

Example 1: In this example, we are going to see how to declare, open, fetch and close the explicit cursor.

We will project all the employee's name from emp table using a cursor. We will also use cursor attribute to set the loop to fetch all the record from the cursor.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;

LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/

Output

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

Code Explanation:

• Code line 2: Declaring the cursor guru99_det for statement 'SELECT emp_name FROM emp'.
• Code line 3: Declaring variable lv_emp_name.

• Code line 5: Opening the cursor guru99_det.
• Code line 6: Setting the Basic loop statement to fetch all the records in the 'emp' table.
• Code line 7: Fetches the guru99_det data and assign the value to lv_emp_name.
• Code line 9: Using the cursor attribute '%NOTFOUND' to find whether all the record in the cursor is fetched. If fetched then it will return 'TRUE' and control will exit from the loop, else the control will keep on fetching the data from the cursor and print the data.
• Code line 11: EXIT condition for the loop statement.
• Code line 12: Print the fetched employee name.
• Code line 14: Using the cursor attribute '%ROWCOUNT' to find the total number of records that got affected/fetched in the cursor.
• Code line 15: After exiting from the loop the cursor is closed and the memory allocated is set free.

FOR Loop Cursor statement

"FOR LOOP" statement can be used for working with cursors. We can give the cursor name instead of range limit in the FOR loop statement so that the loop will work from the first record of the cursor to the last record of the cursor. The cursor variable, opening of cursor, fetching and closing of the cursor will be done implicitly by the FOR loop.

Syntax:

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR I IN <cursor_name>
  LOOP
  .
  .
  END LOOP;
END;

• In the above syntax, the declaration part contains the declaration of the cursor.
• The cursor is created for the 'SELECT' statement that is given in the cursor declaration.
• In execution part, the declared cursor is setup in the FOR loop and the loop variable 'I' will behave as cursor variable in this case.

Example 1: In this example, we will project all the employee name from emp table using a cursor-FOR loop.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/

Output

Employee Fetched:BBB 
Employee Fetched:XXX
Employee Fetched:YYY

Code Explanation:

• Code line 2: Declaring the cursor guru99_det for statement 'SELECT emp_name FROM emp'.
• Code line 4: Constructing the 'FOR' loop for the cursor with the loop variable lv_emp_name.
• Code line 5: Printing the employee name in each iteration of the loop.
• Code line 8: Exit the loop

Note: In Cursor-FOR loop, cursor attributes cannot be used since opening, fetching and closing of the cursor is done implicitly by FOR loop.

Oracle PL/SQL Insert, Update, Delete & Select Into [Example]

 DML Transactions in PL/SQL

DML stands for Data Manipulation Language. These statements are mainly used to perform the manipulation activity. It deals with the below operations.

• Data Insertion
• Data Update
• Data Deletion
• Data Selection

In PL/SQL, we can do the data manipulation only by using the SQL commands.

Data Insertion

In PL/SQL, we can insert the data into any table using the SQL command INSERT INTO. This command will take the table name, table column and column values as the input and insert the value in the base table.

The INSERT command can also take the values directly from another table using 'SELECT' statement rather than giving the values for each column. Through 'SELECT' statement, we can insert as many rows as the base table contains.

Syntax:

BEGIN
  INSERT INTO <table_name>(<column1 >,<column2>,...<column_n>)
     VALUES(<valuel><value2>,...:<value_n>);
END;

• The above syntax shows the INSERT INTO command. The table name and values are a mandatory fields, whereas column names are not mandatory if the insert statements have values for all the column of the table.
• The keyword 'VALUES' is mandatory if the values are given separately as shown above.

Syntax:

BEGIN
  INSERT INTO <table_name>(<columnl>,<column2>,...,<column_n>)
     SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;

• The above syntax shows the INSERT INTO command that takes the values directly from the <table_name2> using the SELECT command.
• The keyword 'VALUES' should not be present in this case as the values are not given separately.

Data Update

Data update simply means an update of the value of any column in the table. This can be done using 'UPDATE' statement. This statement takes the table name, column name and value as the input and updates the data.

Syntax:

BEGIN	
  UPDATE <table_name>
  SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n> 
  WHERE <condition that uniquely identifies the record that needs to be update>; 
END;

• The above syntax shows the UPDATE. The keyword 'SET' instruct that PL/SQL engine to update the value of the column with the value given.
• 'WHERE' clause is optional. If this clause is not given, then the value of the mentioned column in the entire table will be updated.

Data Deletion

Data deletion means to delete one full record from the database table. The 'DELETE' command is used for this purpose.

Syntax:

BEGIN
  DELETE
  FROM
  <table_name>
  WHERE <condition that uniquely identifies the record that needs to be update>; 
END;

• The above syntax shows the DELETE command. The keyword 'FROM' is optional and with or without 'FROM' clause the command behaves in the same way.
• 'WHERE' clause is optional. If this clause is not given, then the entire table will be deleted.

Data Selection

Data projection/fetching means to retrieve the required data from the database table. This can be achieved by using the command 'SELECT' with 'INTO' clause. The 'SELECT' command will fetch the values from the database, and 'INTO' clause will assign these values to the local variable of the PL/SQL block.

Below are the points that need to be considered in 'SELECT' statement.

• 'SELECT' statement should return only one record while using 'INTO' clause as one variable can hold only one value. If the 'SELECT' statement returns more than one value than 'TOO_MANY_ROWS' exception will be raised.
• 'SELECT' statement will assign the value to the variable in the 'INTO' clause, so it needs to get at least one record from the table to populate the value. If it didn't get any record, then the exception 'NO_DATA_FOUND' is raised.
• The number of columns and their datatype in 'SELECT' clause should match with the number of variables and their datatypes in the 'INTO' clause.
• The values are fetched and populated in the same order as mentioned in the statement.
• 'WHERE' clause is optional that allows to having more restriction on the records that are going to be fetched.
• 'SELECT' statement can be used in the 'WHERE' condition of other DML statements to define the values of the conditions.
• The 'SELECT' statement when using 'INSERT', 'UPDATE', 'DELETE' statements should not have 'INTO' clause as it will not populate any variable in these cases.

Syntax:

BEGIN
  SELECT <columnl>,..<column_n> INTO <vanable 1 >,. .<variable_n> 
   FROM <table_name>
   WHERE <condition to fetch the required records>;
END;

• The above syntax shows the SELECT-INTO command. The keyword 'FROM' is mandatory that identifies the table name from which the data needs to be fetched.
• 'WHERE' clause is optional. If this clause is not given, then the data from the entire table will be fetched.

Example 1: In this example, we are going to see how to perform DML operations in PL/SQL. We are going to insert the below four records into emp table.

EMP_NAME	EMP_NO	SALARY	MANAGER
BBB	1000	25000	AAA
XXX	1001	10000	BBB
YYY	1002	10000	BBB
ZZZ	1003	7500	BBB

Then we are going to update the salary of 'XXX' to 15000, and we are going to delete the employee record 'ZZZ'. Finally, we are going to project the details of the employee 'XXX'.

DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER; 
l_manager VARCHAR2(250);
BEGIN	
INSERT INTO emp(emp_name,emp_no,salary,manager) 
VALUES(‘BBB’,1000,25000,’AAA’);
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,’BBB);
INSERT INTO emp(emp_name,emp_no,salary,managed 
VALUES(‘YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager) 
VALUES(‘ZZZ',1003,7500,'BBB'):‭
COMMIT;
Dbms_output.put_line(‘Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line(‘Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT:
Dbms_output.put_line('Values Deleted );
SELECT emp_name,emp_no,salary,manager INTO l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE emp_name='XXX';

Dbms output.put line(‘Employee Detail’);
Dbms_output.put_line(‘Employee Name:‘||l_emp_name);
Dbms_output.put_line(‘Employee Number:‘||l_emp_no);
Dbms_output.put_line(‘Employee Salary:‘||l_salary);
Dbms output.put line(‘Emplovee Manager Name:‘||l_manager):
END;
/

Output:

Values Inserted
Values Updated
Values Deleted
Employee Detail 
Employee Name:XXX 
Employee Number:1001 
Employee Salary:15000 
Employee Manager Name:BBB

Code Explanation:

• Code line 2-5: Declaring the variable.
• Code line 7-14: Inserting the records into emp table.
• Code line 15: Committing the insert transactions.
• Code line 17-19: Updating the salary of the employee 'XXX' to 15000
• Code line 20: Committing the update transaction.
• Code line 22: Deleting the record of 'ZZZ'
• Code line 23: Committing the delete transaction.
• Code line 25-27: Selecting the record of 'XXX' and populating into the variable l_emp_name, l_emp_no, l_salary, l_manager.
• Code line 28-32: Displaying the fetched records value.

Oracle PL/SQL Stored Procedure & Functions with Examples

Terminologies in PL/SQL Subprograms

Before we learn about PL/SQL subprograms, we will discuss the various terminologies that are the part of these subprograms. Below are the terminologies that we are going to discuss.

Parameter:

The parameter is variable or placeholder of any valid PL/SQL datatype through which the PL/SQL subprogram exchange the values with the main code. This parameter allows to give input to the subprograms and to extract from these subprograms.

• These parameters should be defined along with the subprograms at the time of creation.
• These parameters are included n the calling statement of these subprograms to interact the values with the subprograms.
• The datatype of the parameter in the subprogram and the calling statement should be same.
• The size of the datatype should not mention at the time of parameter declaration, as the size is dynamic for this type.

Based on their purpose parameters are classified as

1. IN Parameter
2. OUT Parameter
3. IN OUT Parameter

IN Parameter:

• This parameter is used for giving input to the subprograms.
• It is a read-only variable inside the subprograms. Their values cannot be changed inside the subprogram.
• In the calling statement, these parameters can be a variable or a literal value or an expression, for example, it could be the arithmetic expression like '5*8' or 'a/b' where 'a' and 'b' are variables.
• By default, the parameters are of IN type.

OUT Parameter:

• This parameter is used for getting output from the subprograms.
• It is a read-write variable inside the subprograms. Their values can be changed inside the subprograms.
• In the calling statement, these parameters should always be a variable to hold the value from the current subprograms.

IN OUT Parameter:

• This parameter is used for both giving input and for getting output from the subprograms.
• It is a read-write variable inside the subprograms. Their values can be changed inside the subprograms.
• In the calling statement, these parameters should always be a variable to hold the value from the subprograms.

These parameter type should be mentioned at the time of creating the subprograms.

RETURN

RETURN is the keyword that instructs the compiler to switch the control from the subprogram to the calling statement. In subprogram RETURN simply means that the control needs to exit from the subprogram. Once the controller finds RETURN keyword in the subprogram, the code after this will be skipped.

Normally, parent or main block will call the subprograms, and then the control will shift from those parent block to the called subprograms. RETURN in the subprogram will return the control back to their parent block. In the case of functions RETURN statement also returns the value. The datatype of this value is always mentioned at the time of function declaration. The datatype can be of any valid PL/SQL data type.

What is Procedure in PL/SQL?

A Procedure in PL/SQL is a subprogram unit that consists of a group of PL/SQL statements that can be called by name. Each procedure in PL/SQL has its own unique name by which it can be referred to and called. This subprogram unit in the Oracle database is stored as a database object.

Note: Subprogram is nothing but a procedure, and it needs to be created manually as per the requirement. Once created they will be stored as database objects.

Below are the characteristics of Procedure subprogram unit in PL/SQL:

• Procedures are standalone blocks of a program that can be stored in the database.
• Call to these PLSQL procedures can be made by referring to their name, to execute the PL/SQL statements.
• It is mainly used to execute a process in PL/SQL.
• It can have nested blocks, or it can be defined and nested inside the other blocks or packages.
• It contains declaration part (optional), execution part, exception handling part (optional).
• The values can be passed into Oracle procedure or fetched from the procedure through parameters.
• These parameters should be included in the calling statement.
• A Procedure in SQL can have a RETURN statement to return the control to the calling block, but it cannot return any values through the RETURN statement.
• Procedures cannot be called directly from SELECT statements. They can be called from another block or through EXEC keyword.

Syntax:

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;

• CREATE PROCEDURE instructs the compiler to create new procedure in Oracle. Keyword 'OR REPLACE' instructs the compile to replace the existing procedure (if any) with the current one.
• Procedure name should be unique.
• Keyword 'IS' will be used, when the stored procedure in Oracle is nested into some other blocks. If the procedure is standalone then 'AS' will be used. Other than this coding standard, both have the same meaning.

Example1: Creating Procedure and calling it using EXEC

In this example, we are going to create an Oracle procedure that takes the name as input and prints the welcome message as output. We are going to use EXEC command to call procedure.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) 
IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXEC welcome_msg (‘Guru99’);

Code Explanation:

• Code line 1: Creating the procedure with name 'welcome_msg' and with one parameter 'p_name' of 'IN' type.
• Code line 4: Printing the welcome message by concatenating the input name.
• Procedure is compiled successfully.
• Code line 7: Calling the procedure using EXEC command with the parameter 'Guru99'. Procedure is executed, and the message is printed out as "Welcome Guru99".

What is Function?

Functions is a standalone PL/SQL subprogram. Like PL/SQL procedure, functions have a unique name by which it can be referred. These are stored as PL/SQL database objects. Below are some of the characteristics of functions.

• Functions are a standalone block that is mainly used for calculation purpose.
• Function use RETURN keyword to return the value, and the datatype of this is defined at the time of creation.
• A Function should either return a value or raise the exception, i.e. return is mandatory in functions.
• Function with no DML statements can be directly called in SELECT query whereas the function with DML operation can only be called from other PL/SQL blocks.
• It can have nested blocks, or it can be defined and nested inside the other blocks or packages.
• It contains declaration part (optional), execution part, exception handling part (optional).
• The values can be passed into the function or fetched from the procedure through the parameters.
• These parameters should be included in the calling statement.
• A PLSQL function can also return the value through OUT parameters other than using RETURN.
• Since it will always return the value, in calling statement it always accompanies with assignment operator to populate the variables.

Syntax

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END; 

• CREATE FUNCTION instructs the compiler to create a new function. Keyword 'OR REPLACE' instructs the compiler to replace the existing function (if any) with the current one.
• The Function name should be unique.
• RETURN datatype should be mentioned.
• Keyword 'IS' will be used, when the procedure is nested into some other blocks. If the procedure is standalone then 'AS' will be used. Other than this coding standard, both have the same meaning.

Example1: Creating Function and calling it using Anonymous Block

In this program, we are going to create a function that takes the name as input and returns the welcome message as output. We are going to use anonymous block and select statement to call the function.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
IS
BEGIN
RETURN (‘Welcome ‘|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func (‘Guru99’);
dbms_output.put_line(lv_msg);
END;
SELECT welcome_msg_func(‘Guru99:) FROM DUAL;

Code Explanation:

• Code line 1: Creating the Oracle function with name 'welcome_msg_func' and with one parameter 'p_name' of 'IN' type.
• Code line 2: declaring the return type as VARCHAR2
• Code line 5: Returning the concatenated value 'Welcome' and the parameter value.
• Code line 8: Anonymous block to call the above function.
• Code line 9: Declaring the variable with datatype same as the return datatype of the function.
• Code line 11: Calling the function and populating the return value to the variable 'lv_msg'.
• Code line 12: Printing the variable value. The output you will get here is "Welcome Guru99"
• Code line 14: Calling the same function through SELECT statement. The return value is directed to the standard output directly.

Similarities between Procedure and Function

• Both can be called from other PL/SQL blocks.
• If the exception raised in the subprogram is not handled in the subprogram exception handling section, then it will propagate to the calling block.
• Both can have as many parameters as required.
• Both are treated as database objects in PL/SQL.

Procedure Vs. Function: Key Differences

Procedure	Function
Used mainly to a execute certain process	Used mainly to perform some calculation
Cannot call in SELECT statement	A Function that contains no DML statements can be called in SELECT statement
Use OUT parameter to return the value	Use RETURN to return the value
It is not mandatory to return the value	It is mandatory to return the value
RETURN will simply exit the control from subprogram.	RETURN will exit the control from subprogram and also returns the value
Return datatype will not be specified at the time of creation	Return datatype is mandatory at the time of creation

Built-in Functions in PL/SQL

PL/SQL contains various built-in functions to work with strings and date datatype. Here we are going to see the commonly used functions and their usage.

Conversion Functions

These built-in functions are used to convert one datatype to another datatype.

Function Name	Usage	Example
TO_CHAR	Converts the other datatype to character datatype	TO_CHAR(123);
TO_DATE ( string, format )	Converts the given string to date. The string should match with the format.	TO_DATE('2015-JAN-15', 'YYYY-MON-DD'); Output: 1/15/2015
TO_NUMBER (text, format)	Converts the text to number type of the given format. Informat '9' denotes the number of digits	Select TO_NUMBER('1234','9999') from dual; Output: 1234 Select TO_NUMBER('1,234.45','9,999.99') from dual; Output: 1234

String Functions

These are the functions that are used on the character datatype.

Function Name	Usage	Example
INSTR(text, string, start, occurance)	Gives the position of particular text in the given string. text – Main string string – text that need to be searched start – starting position of the search (optional) accordance – occurrence of the searched string (optional)	Select INSTR('AEROPLANE','E',2,1) from dual Output: 2 Select INSTR('AEROPLANE','E',2,2) from dual Output: 9 (2nd occurance of E)
SUBSTR ( text, start, length)	Gives the substring value of the main string. text – main string start – starting position length – length to be sub stringed	select substr('aeroplane',1,7) from dual Output: aeropla
UPPER ( text )	Returns the uppercase of the provided text	Select upper('guru99') from dual; Output: GURU99
LOWER ( text )	Returns the lowercase of the provided text	Select lower ('AerOpLane') from dual; Output: aeroplane
INITCAP ( text)	Returns the given text with the starting letter in upper case.	Select ('guru99') from dual Output: Guru99 Select ('my story') from dual Output: My Story
LENGTH ( text )	Returns the length of the given string	Select LENGTH ('guru99') from dual; Output: 6
LPAD ( text, length, pad_char)	Pads the string in the left side for the given length (total string) with the given character	Select LPAD('guru99', 10, '$') from dual; Output: $$$$guru99
RPAD (text, length, pad_char)	Pads the string in the right side for the given length (total string) with the given character	Select RPAD('guru99',10,'-') from dual Output: guru99----
LTRIM ( text )	Trims the leading white space from the text	Select LTRIM(' Guru99') from dual; Output: Guru99
RTRIM ( text )	Trims the trailing white space from the text	Select RTRIM('Guru99 ') from dual; Output; Guru99

Date Functions

These are functions that are used for manipulating with dates.

Function Name	Usage	Example
ADD_MONTHS (date, no.of months)	Adds the given months to the date	ADD_MONTH('2015-01-01',5); Output: 05/01/2015
SYSDATE	Returns the current date and time of the server	Select SYSDATE from dual; Output: 10/4/2015 2:11:43 PM
TRUNC	Round of the date variable to the lower possible value	select sysdate, TRUNC(sysdate) from dual; Output: 10/4/2015 2:12:39 PM 10/4/2015
ROUND	Rounds the date to the nearest limit either higher or lower	Select sysdate, ROUND(sysdate) from dual Output: 10/4/2015 2:14:34 PM 10/5/2015
MONTHS_BETWEEN	Returns the number of months between two dates	Select MONTHS_BETWEEN (sysdate+60, sysdate) from dual Output: 2

Summary

In this chapter, we have learned the following.

• How to create Procedure and different ways of calling it
• How to create Function and different ways of calling it
• Similarities and differences between Procedure and Function
• Parameters and RETURN common terminologies in PL/SQL subprograms
• Common built-in functions in Oracle PL/SQL