PL-SQL
Oracle PL/SQL Object Types Tutorial with EXAMPLES
What is Object Type in PL/SQL? Object-Oriented Programming is especially suited for building...
In this tutorial, you are going to see the detailed description on how to create and execute the named blocks (procedures and functions).
Procedures and Functions are the subprograms which can be created and saved in the database as database objects. They can be called or referred inside the other blocks also.
Apart from this, we will cover the major differences between these two subprograms. Also, we are going to discuss the Oracle built-in functions.
In this Oracle Stored Procedure tutorial, you will learn-
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.
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.
Based on their purpose parameters are classified as
These parameter type should be mentioned at the time of creating the subprograms.
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.
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:
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameterl IN/OUT <datatype> .. . ) [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
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:
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.
CREATE OR REPLACE FUNCTION <procedure_name> ( <parameterl IN/OUT <datatype> ) RETURN <datatype> [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
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:
Procedure | Function |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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.
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 |
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.
| 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.
| 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 |
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 |
In this chapter, we have learned the following.
What is Object Type in PL/SQL? Object-Oriented Programming is especially suited for building...
What is While Loop? WHILE loop statement works similar to the Basic loop statement except the EXIT...
What are Decision-Making Statements? Decision making statements are those who will decide the...
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
What is PL/SQL Datatypes? A data type is associated with the specific storage format and range...
Download PDF 1) What is PL SQL ? PL SQL is a procedural language which has interactive SQL, as well as...